IDS Forum

Re: SCHAPI: Error -23197 Database locale infor....

Posted By: John Miller iii
Date: Wednesday, 21 October 2015, at 2:22 a.m.

In Response To: Re: SCHAPI: Error -23197 Database locale infor.... (Peter Lempochner)

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

>>> database = db1 (COLLATE is sk=5FSK.912):

>>>

>>> create tabl= e abc ( id serial, c clob);

>>>

>>> There are som= e data in this table.

>>> I need to schedule custom task for p= eriodicaly unload data from

>this

>>> table to filesyste= m.

>>> So I have created external table like this:

>>= ;>

>>> CREATE EXTERNAL TABLE abc=5Fext SAMEAS abc

>&= gt;> USING (DATAFILES ("DISK:/tmp/UNLOAD/abc.dat"),REJECTFILE

>&g= t;> "/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:a= bc=5Ftest select * from db1:abc;

>>> I get this error in onlin= e:

>>> SCHAPI: [test 36-104] Error -23197 Database locale info= rmation

>mismatch.

>>> and no record are unloaded.

&= gt;>>

>>> Can you please help me with this issue?

&g= t;>> Thanks for your assistance.

>>>

>>> >>>

>>>

>>

>**********************=

***********************************************

>**********

>&= gt;> Forum Note: Use "Reply" to post a response in the discussion

>= ;forum.

>>>

>>>

>> --001a1140421ad4c2a2= 05228dd31d

>>

>>

>>

>*****************=

****************************************************

>**********

= >> Forum Note: Use "Reply" to post a response in the discussion

forum= .

>

>>

>>

>>

>> -----

>= > No virus found in this message.

>> Checked by AVG - [7]www.avg.com

>> Ver= sion: 2015.0.6172 / Virus Database: 4447/10855 - Release Date:

>10/20= /15

>>

>>

>

>

>*********************=

************************************************

>**********

>= Forum Note: Use "Reply" to post a response in the discussion forum.

&g= t;

>

>

References

1. 3D"mailto:miller3@us.ibm.com"

2. 3D"mailto:-----ids-bounces@iiug=

3. 3D"mailto:ids@iiug.org"

4. file://localhost/tmp/3D"mailt=

5. 3D"http://informix-myview.blogspot.co=/

6. 3D"mailto:peter.lempochner@dignitas=

7. 3D"http://www.avg.com"/

Messages In This Thread

• SCHAPI: Error -23197 Database locale information m
  Peter Lempochner -- Tuesday, 20 October 2015, at 1:33 p.m.
  • RE: SCHAPI: Error -23197 Database locale informati
    Alexandre Marini -- Tuesday, 20 October 2015, at 2:00 p.m.
    • Re: SCHAPI: Error -23197 Database locale infor....
      Peter Lempochner -- Tuesday, 20 October 2015, at 2:21 p.m.
      • Re: SCHAPI: Error -23197 Database locale infor....
        Art Kagel -- Tuesday, 20 October 2015, at 3:00 p.m.
      • RE: SCHAPI: Error -23197 Database locale infor....
        Alexandre Marini -- Tuesday, 20 October 2015, at 3:03 p.m.
        • RE: SCHAPI: Error -23197 Database locale infor....
          Paul Watson -- Tuesday, 20 October 2015, at 3:13 p.m.
  • Re: SCHAPI: Error -23197 Database locale informati
    Art Kagel -- Tuesday, 20 October 2015, at 2:57 p.m.
    • Re: SCHAPI: Error -23197 Database locale infor....
      Peter Lempochner -- Tuesday, 20 October 2015, at 6:59 p.m.
      • Re: SCHAPI: Error -23197 Database locale infor....
        John Miller iii -- Wednesday, 21 October 2015, at 2:22 a.m.
        • Re: SCHAPI: Error -23197 Database locale infor....
          Peter Lempochner -- Wednesday, 21 October 2015, at 3:10 a.m.
  • Re: SCHAPI: Error -23197 Database locale informati
    John Miller iii -- Tuesday, 20 October 2015, at 3:05 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.