SCHAPI: Error -23197 Database locale information m

Hello, we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in database db1 (COLLATE is sk_SK.912): create table abc ( id serial, c clob); There are some data in this table. I need to schedule custom task for periodicaly unload data from this table to filesystem. So I have created external table like this: CREATE EXTERNAL TABLE abc_ext SAMEAS abc USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE "/tmp/UNLOAD/abc.rej"); When I run following sql command: insert into db1:abc_test select * from db1:abc;The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. When I create new task (test) in scheduler with command : insert into db1:abc_test select * from db1:abc;I get this error in online: SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. and no record are unloaded. Can you please help me with this issue? Thanks for your assistance.

Hello. Have you tried (or can you) try to use utf8 in your collation? Did you try to run the statement through a dbaccess connection? Does it works? It must do... just in case. I think that issue is happening because your sysadmin database is en_us.819, which is different from your db1 locale. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: peter.lempochner@dignitas.sk > Subject: SCHAPI: Error -23197 Database locale informati.... [35909] > Date: Tue, 20 Oct 2015 13:33:12 -0400 > > Hello, > > we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in > database db1 (COLLATE is sk_SK.912): > > create table abc ( id serial, c clob);> > There are some data in this table. > I need to schedule custom task for periodicaly unload data from this > table to filesystem. > So I have created external table like this: > > CREATE EXTERNAL TABLE abc_ext SAMEAS abc > USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE > "/tmp/UNLOAD/abc.rej"); > > When I run following sql command: > insert into db1:abc_test select * from db1:abc;> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. > > When I create new task (test) in scheduler with command : > insert into db1:abc_test select * from db1:abc;> I get this error in online: > SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. > and no record are unloaded. > > Can you please help me with this issue? > Thanks for your assistance. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hello, thanks for reaction. Collation must be sk_SK.912 this is customer request and production database use it. So I haven`t tried other collation. My sql statement have no problem if I run it through dbaccess from session connected to sysadmin database. Sysadmin database have en_us.819 - confirmed. Question is: Is there any way how to unload table to FS through IDS scheduler if There is difference between collation of sysadmin and source database? I want to schedule this through IDS rather then through shell script scheduled in cron :( Peter Dòa 20.10.2015 o 20:00 Alexandre Marini napísal(a): > Hello. > Have you tried (or can you) try to use utf8 in your collation? > Did you try to run the statement through a dbaccess connection? Does it works? > It must do... just in case. > I think that issue is happening because your sysadmin database is en_us.819, > which is different from your db1 locale. > > Alexandre Marini > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > IBM Information Management Informix Technical Professional > > IBM Certified Developer - Informix Genero > BRIUG website administrator > Informix independent consultant > >> To: ids@iiug.org >> From: peter.lempochner@dignitas.sk >> Subject: SCHAPI: Error -23197 Database locale informati.... [35909] >> Date: Tue, 20 Oct 2015 13:33:12 -0400 >> >> Hello, >> >> we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in >> database db1 (COLLATE is sk_SK.912): >> >> create table abc ( id serial, c clob);>> >> There are some data in this table. >> I need to schedule custom task for periodicaly unload data from this >> table to filesystem. >> So I have created external table like this: >> >> CREATE EXTERNAL TABLE abc_ext SAMEAS abc >> USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE >> "/tmp/UNLOAD/abc.rej"); >> >> When I run following sql command: >> insert into db1:abc_test select * from db1:abc;>> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. >> >> When I create new task (test) in scheduler with command : >> insert into db1:abc_test select * from db1:abc;>> I get this error in online: >> SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. >> and no record are unloaded. >> >> Can you please help me with this issue? >> Thanks for your assistance. >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2015.0.6172 / Virus Database: 4447/10855 - Release Date: 10/20/15 > >

Does the task live in the db1 database or in sysdmin? Usually problems like this happen when you put the procedure into sysadmin which has a different locale than the database that contains the table. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Oct 20, 2015 at 1:33 PM, Peter Lempochner < peter.lempochner@dignitas.sk> wrote: > Hello, > > we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in > database db1 (COLLATE is sk_SK.912): > > create table abc ( id serial, c clob);> > There are some data in this table. > I need to schedule custom task for periodicaly unload data from this > table to filesystem. > So I have created external table like this: > > CREATE EXTERNAL TABLE abc_ext SAMEAS abc > USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE > "/tmp/UNLOAD/abc.rej"); > > When I run following sql command: > insert into db1:abc_test select * from db1:abc;> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. > > When I create new task (test) in scheduler with command : > insert into db1:abc_test select * from db1:abc;> I get this error in online: > SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. > and no record are unloaded. > > Can you please help me with this issue? > Thanks for your assistance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1140421ad4c2a205228dd31d

Put the procedure into database db1 NOT sysadmin and set the ph_task.tk_dbs to "dbs1" when you schedule the task. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Oct 20, 2015 at 2:21 PM, Peter Lempochner < peter.lempochner@dignitas.sk> wrote: > Hello, thanks for reaction. > > Collation must be sk_SK.912 this is customer request and production > database use it. > So I haven`t tried other collation. > My sql statement have no problem if I run it through dbaccess from > session connected to sysadmin database. > Sysadmin database have en_us.819 - confirmed. > Question is: > Is there any way how to unload table to FS through IDS scheduler if > There is difference between collation of sysadmin and source database? > > I want to schedule this through IDS rather then through shell script > scheduled in cron :( > > Peter > > Da 20.10.2015 o 20:00 Alexandre Marini napísal(a): > > Hello. > > Have you tried (or can you) try to use utf8 in your collation? > > Did you try to run the statement through a dbaccess connection? Does it > works? > > It must do... just in case. > > I think that issue is happening because your sysadmin database is > en_us.819, > > which is different from your db1 locale. > > > > Alexandre Marini > > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > > > IBM Information Management Informix Technical Professional > > > > IBM Certified Developer - Informix Genero > > BRIUG website administrator > > Informix independent consultant > > > >> To: ids@iiug.org > >> From: peter.lempochner@dignitas.sk > >> Subject: SCHAPI: Error -23197 Database locale informati.... [35909] > >> Date: Tue, 20 Oct 2015 13:33:12 -0400 > >> > >> Hello, > >> > >> we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in > >> database db1 (COLLATE is sk_SK.912): > >> > >> create table abc ( id serial, c clob);> >> > >> There are some data in this table. > >> I need to schedule custom task for periodicaly unload data from this > >> table to filesystem. > >> So I have created external table like this: > >> > >> CREATE EXTERNAL TABLE abc_ext SAMEAS abc > >> USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE > >> "/tmp/UNLOAD/abc.rej"); > >> > >> When I run following sql command: > >> insert into db1:abc_test select * from db1:abc;> >> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. > >> > >> When I create new task (test) in scheduler with command : > >> insert into db1:abc_test select * from db1:abc;> >> I get this error in online: > >> SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. > >> and no record are unloaded. > >> > >> Can you please help me with this issue? > >> Thanks for your assistance. > >> > >> > >> > > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ----- > > No virus found in this message. > > Checked by AVG - www.avg.com > > Version: 2015.0.6172 / Virus Database: 4447/10855 - Release Date: > 10/20/15 > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11349cee2cdda905228de0d0

I think your question must be answered by some of our IBMers friends. Even though I think this might be a bug, have your tried to call support? Maybe it's already fixed. Somebody could help??? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: peter.lempochner@dignitas.sk > Subject: Re: SCHAPI: Error -23197 Database locale infor.... [35911] > Date: Tue, 20 Oct 2015 14:21:11 -0400 > > Hello, thanks for reaction. > > Collation must be sk_SK.912 this is customer request and production > database use it. > So I haven`t tried other collation. > My sql statement have no problem if I run it through dbaccess from > session connected to sysadmin database. > Sysadmin database have en_us.819 - confirmed. > Question is: > Is there any way how to unload table to FS through IDS scheduler if > There is difference between collation of sysadmin and source database? > > I want to schedule this through IDS rather then through shell script > scheduled in cron :( > > Peter > > Dòa 20.10.2015 o 20:00 Alexandre Marini napísal(a): > > Hello. > > Have you tried (or can you) try to use utf8 in your collation? > > Did you try to run the statement through a dbaccess connection? Does it > works? > > It must do... just in case. > > I think that issue is happening because your sysadmin database is en_us.819, > > which is different from your db1 locale. > > > > Alexandre Marini > > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > > > IBM Information Management Informix Technical Professional > > > > IBM Certified Developer - Informix Genero > > BRIUG website administrator > > Informix independent consultant > > > >> To: ids@iiug.org > >> From: peter.lempochner@dignitas.sk > >> Subject: SCHAPI: Error -23197 Database locale informati.... [35909] > >> Date: Tue, 20 Oct 2015 13:33:12 -0400 > >> > >> Hello, > >> > >> we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in > >> database db1 (COLLATE is sk_SK.912): > >> > >> create table abc ( id serial, c clob);> >> > >> There are some data in this table. > >> I need to schedule custom task for periodicaly unload data from this > >> table to filesystem. > >> So I have created external table like this: > >> > >> CREATE EXTERNAL TABLE abc_ext SAMEAS abc > >> USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE > >> "/tmp/UNLOAD/abc.rej"); > >> > >> When I run following sql command: > >> insert into db1:abc_test select * from db1:abc;> >> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. > >> > >> When I create new task (test) in scheduler with command : > >> insert into db1:abc_test select * from db1:abc;> >> I get this error in online: > >> SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. > >> and no record are unloaded. > >> > >> Can you please help me with this issue? > >> Thanks for your assistance. > >> > >> > >> > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ----- > > No virus found in this message. > > Checked by AVG - www.avg.com > > Version: 2015.0.6172 / Virus Database: 4447/10855 - Release Date: 10/20/15 > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

In short you are not allowed to access two database of different locale at the same time in the database scheduler, but you can do what you want. In order to run you script below, you need to change your local database to db1. In order to do this set the column "tk=5Fdbs" in the ph=5Ftask table to "db1". This w= ill setup the locale and make db1 the current database. So you can do work on any database but the trick is to setup tk=5Fdbs column with the desired database name. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/20/2015 10:33:12 AM: > From: "Peter Lempochner" <peter.lempochner@dignitas.sk> > To: ids@iiug.org > Date: 10/20/2015 10:33 AM > Subject: SCHAPI: Error -23197 Database locale informati.... [35909] > Sent by: ids-bounces@iiug.org > > Hello, > > we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in > database db1 (COLLATE is sk=5FSK.912): > > create table abc ( id serial, c clob);> > There are some data in this table. > I need to schedule custom task for periodicaly unload data from this > table to filesystem. > So I have created external table like this: > > CREATE EXTERNAL TABLE abc=5Fext SAMEAS abc > USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE > "/tmp/UNLOAD/abc.rej"); > > When I run following sql command: > insert into db1:abc=5Ftest select * from db1:abc;> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. > > When I create new task (test) in scheduler with command : > insert into db1:abc=5Ftest select * from db1:abc;> I get this error in online: > SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. > and no record are unloaded. > > Can you please help me with this issue? > Thanks for your assistance. > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

IBM told me this is not a bug but expected behavior, as Art suggests put the SQL in the actual database Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Alexandre Marini Sent: Tuesday, October 20, 2015 2:04 PM To: ids@iiug.org Subject: RE: SCHAPI: Error -23197 Database locale infor.... [35914] I think your question must be answered by some of our IBMers friends. Even though I think this might be a bug, have your tried to call support? Maybe it's already fixed. Somebody could help??? Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: peter.lempochner@dignitas.sk > Subject: Re: SCHAPI: Error -23197 Database locale infor.... [35911] > Date: Tue, 20 Oct 2015 14:21:11 -0400 > > Hello, thanks for reaction. > > Collation must be sk_SK.912 this is customer request and production > database use it. > So I haven`t tried other collation. > My sql statement have no problem if I run it through dbaccess from > session connected to sysadmin database. > Sysadmin database have en_us.819 - confirmed. > Question is: > Is there any way how to unload table to FS through IDS scheduler if > There is difference between collation of sysadmin and source database? > > I want to schedule this through IDS rather then through shell script > scheduled in cron :( > > Peter > > Dòa 20.10.2015 o 20:00 Alexandre Marini napísal(a): > > Hello. > > Have you tried (or can you) try to use utf8 in your collation? > > Did you try to run the statement through a dbaccess connection? Does it > works? > > It must do... just in case. > > I think that issue is happening because your sysadmin database is en_us.819, > > which is different from your db1 locale. > > > > Alexandre Marini > > IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 > > > > IBM Information Management Informix Technical Professional > > > > IBM Certified Developer - Informix Genero > > BRIUG website administrator > > Informix independent consultant > > > >> To: ids@iiug.org > >> From: peter.lempochner@dignitas.sk > >> Subject: SCHAPI: Error -23197 Database locale informati.... [35909] > >> Date: Tue, 20 Oct 2015 13:33:12 -0400 > >> > >> Hello, > >> > >> we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in > >> database db1 (COLLATE is sk_SK.912): > >> > >> create table abc ( id serial, c clob);> >> > >> There are some data in this table. > >> I need to schedule custom task for periodicaly unload data from this > >> table to filesystem. > >> So I have created external table like this: > >> > >> CREATE EXTERNAL TABLE abc_ext SAMEAS abc > >> USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE > >> "/tmp/UNLOAD/abc.rej"); > >> > >> When I run following sql command: > >> insert into db1:abc_test select * from db1:abc;> >> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. > >> > >> When I create new task (test) in scheduler with command : > >> insert into db1:abc_test select * from db1:abc;> >> I get this error in online: > >> SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. > >> and no record are unloaded. > >> > >> Can you please help me with this issue? > >> Thanks for your assistance. > >> > >> > >> > > > **************************************************************************** *** > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > > **************************************************************************** *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ----- > > No virus found in this message. > > Checked by AVG - www.avg.com > > Version: 2015.0.6172 / Virus Database: 4447/10855 - Release Date: 10/20/15 > > > > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Hello, thanks for explanations to all. I have tried some combinations without success :-( I`m sending info about my small test environment: -------------------------------- dbschema -d db1 DBSCHEMA Schema Utility INFORMIX-SQL Version 11.70.UC3IE grant dba to "informix"; { TABLE "informix".abc row size = 80 number of columns = 2 index size = 0 } create table "informix".abc ( id serial not null , c "informix".clob ); revoke all on "informix".abc from "public" as "informix"; { TABLE "informix".abc_ext row size = 80 number of columns = 2 } create external table "informix".abc_ext ( id serial, c "informix".clob ) using ( datafiles ( "DISK:/tmp/UNLOAD/abc.dat" ), format "delimited", rejectfile "/tmp/UNLOAD/abc.rej" ); revoke all on "informix".abc_ext from "public"; grant select on "informix".abc to "public" as "informix"; grant update on "informix".abc to "public" as "informix"; grant insert on "informix".abc to "public" as "informix"; grant delete on "informix".abc to "public" as "informix"; grant index on "informix".abc to "public" as "informix"; grant select on "informix".abc_ext to "public" as "informix"; grant insert on "informix".abc_ext to "public" as "informix"; create procedure "informix".abcunl () insert into abc_ext select * from abc;end procedure; grant execute on procedure "informix".abcunl () to "public" as "informix"; revoke usage on language SPL from public ; grant usage on language SPL to public ; ------------------------- task in SYSADMIN database: ------------------------- tk_id 36 tk_name test tk_description execute procedure abcunl(); tk_type TASK tk_sequence 379 tk_result_table tk_create tk_dbs db1 tk_execute insert into abc_ext select * from abc tk_delete 0 01:00:00 tk_start_time 00:23:00 tk_stop_time tk_frequency 0 00:01:00 tk_next_execution 2015-10-21 00:40:23 tk_total_executio+ 372 tk_total_time 0,328075723364 tk_monday t tk_tuesday t tk_wednesday t tk_thursday t tk_friday t tk_saturday t tk_sunday t tk_attributes 912 tk_group TABLES tk_enable t tk_priority 0 Result: ------------------------ 00:39:10 SCHAPI: Started 2 dbWorker threads. 00:39:10 SCHAPI: [test 36-377] Error -206 The specified table (abc_ext) is not in the database. 00:39:10 SCHAPI: [test 36-377] Error -111 ISAM error: no record found. ---------------------------------- If I use tk_execute='insert into db1:abc_ext select * from db1:abc' the result is: SCHAPI: [test 36-380] Error -23197 Database locale information mismatch. If I use tk_execute='execute procedure abcunl();' the result is : SCHAPI: [test 36-383] Error -674 Routine (abcunl) can not be resolved. I am not able to see what is wrong :-( Dòa 20.10.2015 o 20:57 Art Kagel napísal(a): > Does the task live in the db1 database or in sysdmin? Usually problems > like this happen when you put the procedure into sysadmin which has a > different locale than the database that contains the table. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Tue, Oct 20, 2015 at 1:33 PM, Peter Lempochner < > peter.lempochner@dignitas.sk> wrote: > >> Hello, >> >> we are runnig on IDS 11.70.UC3. I have table with CLOB - for example in >> database db1 (COLLATE is sk_SK.912): >> >> create table abc ( id serial, c clob);>> >> There are some data in this table. >> I need to schedule custom task for periodicaly unload data from this >> table to filesystem. >> So I have created external table like this: >> >> CREATE EXTERNAL TABLE abc_ext SAMEAS abc >> USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE >> "/tmp/UNLOAD/abc.rej"); >> >> When I run following sql command: >> insert into db1:abc_test select * from db1:abc;>> The result is expected: in /tmp/UNLOAD/abc.dat are unloaded data. >> >> When I create new task (test) in scheduler with command : >> insert into db1:abc_test select * from db1:abc;>> I get this error in online: >> SCHAPI: [test 36-104] Error -23197 Database locale information mismatch. >> and no record are unloaded. >> >> Can you please help me with this issue? >> Thanks for your assistance. >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a1140421ad4c2a205228dd31d > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2015.0.6172 / Virus Database: 4447/10855 - Release Date: 10/20/15 > >

Here is an example of how to create a sensor to unload all th= e user defined table names in systables to an external table. Ple= ase run this sql as user informix and it should do everything. I u= sed a sensor as it will automatically create the external table. = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D DROP DATABASE IF EXISTS test=5Fdbs1; CREATE DATABASE test=5Fdbs1 = WITH LOG; create table tab1(c1 char(20)); create table tab2(c1 char(= 20)); DATABASE sysadmin; DELETE FROM ph=5Ftask WHERE tk=5Fnam= e =3D "systables list"; INSERT INTO ph=5Ftask ( tk=5Fname, tk= =5Ftype, tk=5Fgroup, tk=5Fdescription, tk=5Fdbs, tk=5Fcreate,tk=5Fexecute, tk=5Fstart=5Ftime, tk=5Fstop=5Ftime, tk=5Ffrequenc= y, tk=5Fdelete ) VALUES ( "systables list", "SENSOR", = "PERFORMANCE", "Create an external list of user table names", "test= =5Fdbs1", "create external table tablist ( name char(20) ) using ( dataf= iles('DISK:/tmp/tab.dat'),format 'delimited', rejectfile '/tmp/tab.rej' );"= , "insert into tablist select tabname from systables where tabid > 99= ", NULL, NULL, INTERVAL ( 1 ) MINUTE TO MINUTE, INTERVAL ( 1 ) = DAY TO DAY ); John F. Miller III STSM, Lead = ;Architect [1]miller3@us.ibm.com<= br>503-747-1366 IBM Informix Dynamic Server (IDS) [2]-----ids-bounces@iiug.org wrote: ----- >To: [3]ids@iiug.org >Fro= m: "Peter Lempochner" >Sent by: [4]ids-bounces@iiug.org >Date: 10/20/2015 03= :59PM >Subject: Re: SCHAPI: Error -23197 Database locale infor.... [= 35917] > >Hello, > >thanks for explanations to all= . > >I have tried some combinations without success :-( &g= t;I`m sending info about my small test environment: >---------------= ----------------- >dbschema -d db1 > >DBSCHEMA Schema U= tility INFORMIX-SQL Version 11.70.UC3IE >grant dba to "informix"; > >{ TABLE "informix".abc row size =3D 80 number of columns =3D = 2 index size >=3D 0 } >create table "informix".abc > >( > >id serial not null , > >c "informix".cl= ob > >); > >revoke all on "informix".abc from "pu= blic" as "informix"; > >{ TABLE "informix".abc=5Fext row size = =3D 80 number of columns =3D 2 } >create external table "informix".a= bc=5Fext > >( > >id serial, > >c "in= formix".clob > >) >using > >( > = >datafiles > >( > >"DISK:/tmp/UNLOAD/abc.dat" = > >), > >format "delimited", > >reje= ctfile "/tmp/UNLOAD/abc.rej" > >); >revoke all on "info= rmix".abc=5Fext from "public"; > >grant select on "informix".a= bc to "public" as "informix"; >grant update on "informix".abc to "pu= blic" as "informix"; >grant insert on "informix".abc to "public" as = "informix"; >grant delete on "informix".abc to "public" as "informix= "; >grant index on "informix".abc to "public" as "informix"; >= ; >grant select on "informix".abc=5Fext to "public" as "informix"; >grant insert on "informix".abc=5Fext to "public" as "informix"; &= gt; >create procedure "informix".abcunl () >insert into abc=5F= ext select * from abc; >end procedure; > >grant execute= on procedure "informix".abcunl () to "public" as >"informix"; &g= t; >revoke usage on language SPL from public ; > >grant = usage on language SPL to public ; > >-------------------------= >task in SYSADMIN database: >------------------------- &= gt;tk=5Fid 36 >tk=5Fname test >tk=5Fdescription execute proce= dure abcunl(); >tk=5Ftype TASK >tk=5Fsequence 379 >tk= =5Fresult=5Ftable >tk=5Fcreate >tk=5Fdbs db1 >tk=5Fexe= cute insert into abc=5Fext select * from abc >tk=5Fdelete 0 01:00:00= >tk=5Fstart=5Ftime 00:23:00 >tk=5Fstop=5Ftime >tk=5Ff= requency 0 00:01:00 >tk=5Fnext=5Fexecution 2015-10-21 00:40:23 &= gt;tk=5Ftotal=5Fexecutio+ 372 >tk=5Ftotal=5Ftime 0,328075723364 = >tk=5Fmonday t >tk=5Ftuesday t >tk=5Fwednesday t >t= k=5Fthursday t >tk=5Ffriday t >tk=5Fsaturday t >tk=5Fs= unday t >tk=5Fattributes 912 >tk=5Fgroup TABLES >tk=5F= enable t >tk=5Fpriority 0 > >Result: >----------= -------------- >00:39:10 SCHAPI: Started 2 dbWorker threads. >= ;00:39:10 SCHAPI: [test 36-377] Error -206 The specified table >(abc= =5Fext) >is not in the database. > >00:39:10 SCHAPI: [t= est 36-377] Error -111 ISAM error: no record >found. > >= ---------------------------------- > >If I use tk=5Fexecute=3D= 'insert into db1:abc=5Fext select * from db1:abc' >the result is: >SCHAPI: [test 36-380] Error -23197 Database locale information >= ;mismatch. > >If I use tk=5Fexecute=3D'execute procedure abcun= l();' >the result is : >SCHAPI: [test 36-383] Error -674 Rout= ine (abcunl) can not be >resolved. > >I am not able to s= ee what is wrong :-( > >D=C3=B2a 20.10.2015 o 20:57 Art Kagel = nap=C3=ADsal(a): >> Does the task live in the db1 database or in = sysdmin? Usually >problems >> like this happen when you put= the procedure into sysadmin which has >a >> different loca= le than the database that contains the table. >> >> Art= >> >> Art S. Kagel, President and Principal Consultant= >> ASK Database Management >> www.askdbmgt.com >> >&= gt; Blog: [5]http://informix-myview.blogspot.com/ >> >> Disc= laimer: Please keep in mind that my own opinions are my own >opinions= >> and do not reflect on the IIUG, nor any other

Hello John, it`s work for me. Many thanks for advice. Best regards Peter Dòa 21.10.2015 o 8:22 John Miller iii napísal(a): > Here is an example of how to create a sensor to unload all th= e user > > defined table names in systables to an external table. Ple= ase run > > this sql > > as user informix and it should do everything. I u= sed a sensor as it > > will > > automatically create the external table. > > = > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= > > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > DROP DATABASE IF EXISTS test=5Fdbs1;> > CREATE DATABASE test=5Fdbs1 = WITH LOG;> > create table tab1(c1 char(20));> > create table tab2(c1 char(= 20));> > DATABASE sysadmin;> > DELETE FROM ph=5Ftask WHERE tk=5Fnam= e =3D "systables list";> > INSERT INTO ph=5Ftask> > ( > > tk=5Fname, > > tk= =5Ftype, > > tk=5Fgroup, > > tk=5Fdescription, > > tk=5Fdbs, > > tk=5Fcreate,tk=5Fexecute, > > tk=5Fstart=5Ftime, > > tk=5Fstop=5Ftime, > > tk=5Ffrequenc= y, > > tk=5Fdelete > > ) > > VALUES > > ( > > "systables list", > > "SENSOR", > > = "PERFORMANCE", > > "Create an external list of user table names", > > "test= =5Fdbs1", > > "create external table tablist ( name char(20) ) using ( dataf= > > iles('DISK:/tmp/tab.dat'),format 'delimited', rejectfile > > '/tmp/tab.rej' );"= , > > "insert into tablist select tabname from systables where tabid > 99= > > ", > > NULL, > > NULL, > > INTERVAL ( 1 ) MINUTE TO MINUTE, > > INTERVAL ( 1 ) = DAY TO DAY > > ); > > John F. Miller III > > STSM, Lead = ;Architect > > [1]miller3@us.ibm.com<= br>503-747-1366 > > IBM Informix Dynamic Server (IDS) > > [2]-----ids-bounces@iiug.org wrote: ----- > >> To: [3]ids@iiug.org >> Fro= m: "Peter Lempochner" >> Sent by: [4]ids-bounces@iiug.org >> Date: 10/20/2015 03= :59PM >> Subject: Re: SCHAPI: Error -23197 Database locale infor.... [= 35917] >> Hello, >> thanks for explanations to all= . >> I have tried some combinations without success :-( > &g= t;I`m sending info about my small test environment: > >> ---------------= ----------------- >> dbschema -d db1>> DBSCHEMA Schema U= tility INFORMIX-SQL Version 11.70.UC3IE >> grant dba to "informix"; >>> { TABLE "informix".abc row size =3D 80 number of columns =3D = 2 > index size > >> =3D 0 } >> create table "informix".abc >> ( >> id serial not null , >> c "informix".cl= ob >> ); >> revoke all on "informix".abc from "pu= blic" as "informix";>> { TABLE "informix".abc=5Fext row size = =3D 80 number of columns =3D > 2 } > >> create external table "informix".a= bc=5Fext >> ( >> id serial, >> c "in= formix".clob >> ) >> using >> ( > = >datafiles > >> ( >> "DISK:/tmp/UNLOAD/abc.dat" = >> ), >> format "delimited", >> reje= ctfile "/tmp/UNLOAD/abc.rej" >> ); >> revoke all on "info= rmix".abc=5Fext from "public"; >> grant select on "informix".a= bc to "public" as "informix"; >> grant update on "informix".abc to "pu= blic" as "informix"; >> grant insert on "informix".abc to "public" as = "informix"; >> grant delete on "informix".abc to "public" as "informix= "; >> grant index on "informix".abc to "public" as "informix";>> = ; >> grant select on "informix".abc=5Fext to "public" as "informix"; >> grant insert on "informix".abc=5Fext to "public" as "informix";> &= gt; > >> create procedure "informix".abcunl () >> insert into abc=5F= ext select * from abc;>> end procedure; >> grant execute= on procedure "informix".abcunl () to "public" as>> "informix"; > &g= t; > >> revoke usage on language SPL from public ;>> grant = usage on language SPL to public ; >> -------------------------= >> task in SYSADMIN database: >> ------------------------- > &= gt;tk=5Fid 36 > >> tk=5Fname test >> tk=5Fdescription execute proce= dure abcunl(); >> tk=5Ftype TASK >> tk=5Fsequence 379 >> tk= =5Fresult=5Ftable >> tk=5Fcreate >> tk=5Fdbs db1 >> tk=5Fexe= cute insert into abc=5Fext select * from abc >> tk=5Fdelete 0 01:00:00= >> tk=5Fstart=5Ftime 00:23:00 >> tk=5Fstop=5Ftime >> tk=5Ff= requency 0 00:01:00 >> tk=5Fnext=5Fexecution 2015-10-21 00:40:23 > &= gt;tk=5Ftotal=5Fexecutio+ 372 > >> tk=5Ftotal=5Ftime 0,328075723364 > = >tk=5Fmonday t > >> tk=5Ftuesday t >> tk=5Fwednesday t >> t= k=5Fthursday t >> tk=5Ffriday t >> tk=5Fsaturday t >> tk=5Fs= unday t >> tk=5Fattributes 912 >> tk=5Fgroup TABLES >> tk=5F= enable t >> tk=5Fpriority 0 >> Result: >> ----------= -------------- >> 00:39:10 SCHAPI: Started 2 dbWorker threads. >> = ;00:39:10 SCHAPI: [test 36-377] Error -206 The specified table >> (abc= =5Fext) >> is not in the database. >> 00:39:10 SCHAPI: [t= est 36-377] Error -111 ISAM error: no record >> found. >> = ---------------------------------- >> If I use tk=5Fexecute=3D= 'insert into db1:abc=5Fext select * from > db1:abc' > >> the result is: >SCHAPI: [test 36-380] Error -23197 Database locale > information > >> = ;mismatch. >> If I use tk=5Fexecute=3D'execute procedure abcun= l();' >> the result is : >> SCHAPI: [test 36-383] Error -674 Rout= ine (abcunl) can not be >> resolved. >> I am not able to s= ee what is wrong :-( >> D=C3=B2a 20.10.2015 o 20:57 Art Kagel = nap=C3=ADsal(a): >>> Does the task live in the db1 database or in = sysdmin? Usually >> problems >>> like this happen when you put= the procedure into sysadmin which > has > >> a >>> different loca= le than the database that contains the table. >>> Art= >>> Art S. Kagel, President and Principal Consultant= >>> ASK Database Management >>> www.askdbmgt.com >> &= gt; Blog: [5]http://informix-myview.blogspot.com/ >>> Disc= laimer: Please keep in mind that my own opinions are my own >> opinions= >>> and do not reflect on the IIUG, nor any other organization wi= th >> which I am >>> associated either explicitly, implicitly,= or by inference. Neither >> do >>> those opinions reflect tho= se of other individuals affiliated with >> any >>> entity with= which I am affiliated nor those of the entities >> themselves. >> = ;> >>> On Tue, Oct 20, 2015 at 1:33 PM, Peter Lempochner < <= br>>> > [6]peter.lempochner@dignitas.sk> wrote: > >>> &= gt; Hello, >>>> we are runnig on IDS 11.70.UC3= . I have table with CLOB - for >> example in >>>> data