External table problem

Hi, I have a non logging database in wich I want to load from an external table composed from 2 files of 7gb each one, but the problem is when I launch insert command on dbaccess it seems to be blocked, when I chack the onstat -, I have CKPT inp all the time, I think is not processing because from yesterday I did no inserted row, when I carte a primary key on the table and repeat the inserting it works but it's switching to deluxe mode which is too slow, why informix ios blocked when I launch in express mode? thanks

Which Informix version? What does onstat -g ckp give? What is in the online log / os log? Are enough cleaners configured? Is this loading all the data into 1 chunk? David -----Original Message----- From: "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> Sent: =E2=80=8E29/=E2=80=8E02/=E2=80=8E2016 23:20 To: "ids@iiug.org" <ids@iiug.org> Subject: External table problem [36659] Hi, I have a non logging database in wich I want to load from an external=20 table composed from 2 files of 7gb each one, but the problem is when I laun= ch=20 insert command on dbaccess it seems to be blocked, when I chack the onstat = -,=20 I have CKPT inp all the time, I think is not processing because from yester= day=20 I did no inserted row, when I carte a primary key on the table and repeat t= he=20 inserting it works but it's switching to deluxe mode which is too slow, why= =20 informix ios blocked when I launch in express mode?=20 thanks=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

If you are using external tables in non-logging mode there is a very good chance you are using express mode loading. This means rows are not being inserted, but rather pages are being created and written to disk directly. The rows loaded is not set until the table is complete an archived. I believe there is an onstat option to show this light append actions. onstat -g lap 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 03/01/2016 02:05:23 AM: > From: "David Williams" <david@smooth1.co.uk> > To: ids@iiug.org > Date: 03/01/2016 02:06 AM > Subject: RE: External table problem [36664] > Sent by: ids-bounces@iiug.org > > Which Informix version? > What does onstat -g ckp give? > What is in the online log / os log? > Are enough cleaners configured? > Is this loading all the data into 1 chunk? > > David > > -----Original Message----- > From: "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> > Sent: =3DE2=3D80=3D8E29/=3DE2=3D80=3D8E02/=3DE2=3D80=3D8E2016 23:20 > To: "ids@iiug.org" <ids@iiug.org> > Subject: External table problem [36659] > > Hi, I have a non logging database in wich I want to load from an external=3D20 > table composed from 2 files of 7gb each one, but the problem is when I laun=3D > ch=3D20 > insert command on dbaccess it seems to be blocked, when I chack the onstat =3D > -,=3D20 > I have CKPT inp all the time, I think is not processing because from yester=3D > day=3D20 > I did no inserted row, when I carte a primary key on the table and repeat t=3D > he=3D20 > inserting it works but it's switching to deluxe mode which is too slow, why=3D > =3D20 > informix ios blocked when I launch in express mode?=3D20 > > thanks=3D20 > > ***************************************************************************= =3D > ****=3D20 > Forum Note: Use "Reply" to post a response in the discussion forum.=3D20 > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

Which Informix version? 11.70 FC5 What does onstat -g ckp give? it stacked in the last checkpoint done by plog What is in the online log / os log? it's normal and does not move Are enough cleaners configured? 8 Is this loading all the data into 1 chunk? Yes onstat - is stacked in (CKPT INP)

I'm in a non logging database yes and I load in express mode, but I think is not doing anything no load is in progress its simply blocked by something, the onstat - is in CKPT INP, I will check the onstat -lap and give you return thanks

One small little know trick to make loading run faster is aft= er you create your tables is to do a checkpoint before you start loading= . This will reduce the amount of data that must be logged in the phy= sical and logical logs. The reasoning is quite complex. = John F. Miller III STSM, Lead Architect [1]miller3@us.ibm.com 503-747-1366 IBM Infor= mix Dynamic Server (IDS) [2]-----ids-bounces@iiug.= org wrote: ----- >To: [3]ids@iiug.org >From: "CHALLENGER212 ABDERRAFI"= >Sent by: [4]ids-bounces@iiug.org >Date: 03/03/2016 10:47PM >Subject:= Re: RE: External table problem [36702] > >Which Informix vers= ion? 11.70 FC5 >What does onstat -g ckp give? it stacked in the last= checkpoint done >by plog >What is in the online log / os log?= it's normal and does not move >Are enough cleaners configured? 8 >Is this loading all the data into 1 chunk? Yes > >onstat= - is stacked in (CKPT INP) > > >***********************= ********************************************** >********** > = Forum Note: Use "Reply" to post a response in the discussion forum. >= > > References 1. file://localhost/tmp/3D"mai= 2. 3D"mailto:-----ids-bounces@iiug.org" 3. file://localhost/tmp/3D"m= 4. 3D"mailto:ids-bounces@iiug.or=

Hi, I have found the proplem I receate the external table without "Espace" parameter and now it create the reject files, there is some rows with "enter: carriage field" in some fields and other rows with '|' in some fields but before the pipe and the cariage return tehre is '\\\\' character but why it does not work???

Are you reading in a file or writing one out? Art On Mar 6, 2016 09:18, "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> wrote: > Hi, I have found the proplem > > I receate the external table without "Espace" parameter and now it create > the > reject files, there is some rows with "enter: carriage field" in some > fields > and other rows with '|' in some fields but before the pipe and the cariage > return tehre is '\\\\' character but why it does not work??? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bfea0b6a9c8b6052d625be8

I'm reading from a file and for the rejectfile of course informix should write

You don't specify what version of Informix you are using. In version 12.10 the default for ESCAPE is ON in earlier versions it was OFF. So, if you are using v12.10, unless you specified ESCAPE OFF in the defiition of the external table then it was turned on. That may be your problem. 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 Sun, Mar 6, 2016 at 8:14 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > I'm reading from a file and for the rejectfile of course informix should > write > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ee4fcf12375052d6d5533

There are conditions where express can't be used - only deluxe. Been so long I can't remember them - indexes exist, rows larger than a page, and a few others. Do what John suggested during the load to verify express ... onstat -g lap. A "light append" (lap) bypasses the buffer cache, goes through virtual and onto disk. The onstat -g lap shows the activity in the light append buffers in the virtual portion. I also believe the flushing of the light append buffers are shown in the message log as a checkpoint. If you're not getting light appends, that could explain some of the issues. Also - do an "onstat -br" and watch the pages getting dirty (or not). Should be minimal. If large, you are using the BUFFERS and not getting light appends. Thanks - Mark Scranton The Mark Scranton Group "All Informix ... all the time." mark@markscranton.com

Yep! Thanks! Rhonda Hackenburg Sent from my iPhone > On Mar 7, 2016, at 4:48 PM, MARK SCRANTON <mark@markscranton.com> wrote: > > There are conditions where express can't be used - only deluxe. Been so long I > can't remember them - indexes exist, rows larger than a page, and a few > others. Do what John suggested during the load to verify express ... onstat -g > lap. A "light append" (lap) bypasses the buffer cache, goes through virtual > and onto disk. The onstat -g lap shows the activity in the light append > buffers in the virtual portion. I also believe the flushing of the light > append buffers are shown in the message log as a checkpoint. If you're not > getting light appends, that could explain some of the issues. Also - do an > "onstat -br" and watch the pages getting dirty (or not). Should be minimal. If > large, you are using the BUFFERS and not getting light appends. > > Thanks - > Mark Scranton > The Mark Scranton Group > "All Informix ... all the time." > mark@markscranton.com > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > Email secured by Check Point

11.70 FC5, I have got the informix support its a bug solved in earlier versions. I have solved the problem by using '\\\\006' as a delimter and '\\\\012' as recordend and no escape, now I it works well. thanks