IDS Forum

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

Posted By: Peter Lempochner
Date: Wednesday, 21 October 2015, at 3:10 a.m.

In Response To: Re: SCHAPI: Error -23197 Database locale infor.... (John Miller iii)

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
>>>> 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"/
>
>
> *******************************************************************************
> 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: 4450/10860 - Release Date: 10/20/15
>
>

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.