External tables not reading all rows
Mike loaded a 3M-row pipe-delimited file via an external table split into seven files (xaa-xag), but only ~1.5M rows landed in the target table, and it turned out only every other file (xaa, xac, xae, xag) was being read. Martin and Art suspected the real issue was the long transaction, and Art advised loading into a RAW or unlogged table. Adding WITH NO LOG to the temp table fixed the long-transaction problem and all 3,091,792 rows loaded. The odd skipping of duplicated DATAFILES entries was never explained — no resolution recorded for that part.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
I am experimenting with external tables to assist in the loading (or
accessing) of data into a large comparison function.
The input file is '|' delimited with just over 3 million rows. Accessing this
file as an external table was my first thought, but the lack of Indexing makes
the 'matching query' quite slow.
So I thought I would load the file into a table. Ran into Long Transaction
issues with just a single file.
To fix this, and possibly get PDQ working (separate email), I "split" the file
into separate files of 500,000 rows each. 7 files created.
However, when I loaded the files, only 1.5 million rows were loaded. Huh?
What did I do wrong? The 2 final count(*)s come out equal, 1.5+million rows,
so at least the insert is working. But why isn't the external table reading
all the rows?
Thanks,
Mike
Here's my SQL:
create table mthly_info
( cou varchar(50),
dal varchar(50),
vr_id varchar(50),
lg varchar(50),
kg varchar(50),
dot varchar(50),
dv varchar(50),
sr varchar(50)
);
create external table mthly_ld
SAMEAS mthly_info
USING (
FORMAT 'DELIMITED',
DELIMITER '|',
DATAFILES ( "DISK:/soft/devdba/citizen/db/sql/xaa",
"DISK:/soft/devdba/citizen/db/sql/xab",
"DISK:/soft/devdba/citizen/db/sql/xac",
"DISK:/soft/devdba/citizen/db/sql/xad",
"DISK:/soft/devdba/citizen/db/sql/xae",
"DISK:/soft/devdba/citizen/db/sql/xaf",
"DISK:/soft/devdba/citizen/db/sql/xag"
),
EXPRESS
);
insert into mthly_info
select * from mthly_ld;
select count(*) from mthly_ld;
select count(*) from mthly_info;
Hi,
I'm not sure, how you 'fixed' the long transaction issue by
splitting the underlying file of the external table. The
statement that you then issue to insert the data from the
external table into the normal table still is a single
insert statement.
You may be right about the splitting into seven file helping
to parallelize the read from the external table. But for the
long transaction ?
I guess that's the reason why not all rows got inserted.
Regards, Martin
--
Martin Fuerderer
IBM Informix Development Munich, Germany
Information Management
Read about the Informix Warehouse Accelerator:
http://tinyurl.com/the-iwa-blog
IBM Deutschland Research & Development GmbH
Chairman of the Supervisory Board: Martina Koederitz
Board of Management: Dirk Wittkopp
Corporate Seat: Boeblingen, Germany
Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294
From: "MICHAEL HOFFMAN" <mrh@panix.com>
To: ids@iiug.org,
Date: 07/17/2013 01:19
Subject: External tables not reading all rows [30866]
Sent by: ids-bounces@iiug.org
Hi All,
I am experimenting with external tables to assist in the loading (or
accessing) of data into a large comparison function.
The input file is '|' delimited with just over 3 million rows. Accessing
this
file as an external table was my first thought, but the lack of Indexing
makes
the 'matching query' quite slow.
So I thought I would load the file into a table. Ran into Long Transaction
issues with just a single file.
To fix this, and possibly get PDQ working (separate email), I "split" the
file
into separate files of 500,000 rows each. 7 files created.
However, when I loaded the files, only 1.5 million rows were loaded. Huh?
What did I do wrong? The 2 final count(*)s come out equal, 1.5+million
rows,
so at least the insert is working. But why isn't the external table
reading
all the rows?
Thanks,
Mike
Here's my SQL:
create table mthly_info
( cou varchar(50),
dal varchar(50),
vr_id varchar(50),
lg varchar(50),
kg varchar(50),
dot varchar(50),
dv varchar(50),
sr varchar(50)
);
create external table mthly_ld
SAMEAS mthly_info
USING (
FORMAT 'DELIMITED',
DELIMITER '|',
DATAFILES ( "DISK:/soft/devdba/citizen/db/sql/xaa",
"DISK:/soft/devdba/citizen/db/sql/xab",
"DISK:/soft/devdba/citizen/db/sql/xac",
"DISK:/soft/devdba/citizen/db/sql/xad",
"DISK:/soft/devdba/citizen/db/sql/xae",
"DISK:/soft/devdba/citizen/db/sql/xaf",
"DISK:/soft/devdba/citizen/db/sql/xag"
),
EXPRESS
);
insert into mthly_info
select * from mthly_ld;
select count(*) from mthly_ld;
select count(*) from mthly_info;
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I think that the LTC problem went away because the OP has enough logs for
the 1.5million rows that ultimately loaded but not for the full 3million
rows.
To the OP, the ultimate solution to the long transaction problem is to make
the table you load the external table data into be a RAW mode, unlogged,
table or an unlogged temp table. Why only half of the rows loaded, I have
no idea.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Jul 17, 2013 at 3:43 AM, Martin Fuerderer <MARTINFU@de.ibm.com>wrote:
> Hi,
>
> I'm not sure, how you 'fixed' the long transaction issue by
> splitting the underlying file of the external table. The
> statement that you then issue to insert the data from the
> external table into the normal table still is a single
> insert statement.
> You may be right about the splitting into seven file helping
> to parallelize the read from the external table. But for the
> long transaction ?
>
> I guess that's the reason why not all rows got inserted.
>
> Regards, Martin
> --
> Martin Fuerderer
> IBM Informix Development Munich, Germany
> Information Management
>
> Read about the Informix Warehouse Accelerator:
> http://tinyurl.com/the-iwa-blog
>
> IBM Deutschland Research & Development GmbH
> Chairman of the Supervisory Board: Martina Koederitz
> Board of Management: Dirk Wittkopp
> Corporate Seat: Boeblingen, Germany
> Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294
>
> From: "MICHAEL HOFFMAN" <mrh@panix.com>
> To: ids@iiug.org,
> Date: 07/17/2013 01:19
> Subject: External tables not reading all rows [30866]
> Sent by: ids-bounces@iiug.org
>
> Hi All,
> I am experimenting with external tables to assist in the loading (or
> accessing) of data into a large comparison function.
>
> The input file is '|' delimited with just over 3 million rows. Accessing
> this
> file as an external table was my first thought, but the lack of Indexing
> makes
> the 'matching query' quite slow.
>
> So I thought I would load the file into a table. Ran into Long Transaction
>
> issues with just a single file.
>
> To fix this, and possibly get PDQ working (separate email), I "split" the
> file
> into separate files of 500,000 rows each. 7 files created.
>
> However, when I loaded the files, only 1.5 million rows were loaded. Huh?
> What did I do wrong? The 2 final count(*)s come out equal, 1.5+million
> rows,
> so at least the insert is working. But why isn't the external table
> reading
> all the rows?
>
> Thanks,
> Mike
>
> Here's my SQL:
> create table mthly_info
> ( cou varchar(50),
> dal varchar(50),
> vr_id varchar(50),
> lg varchar(50),
> kg varchar(50),
> dot varchar(50),
> dv varchar(50),
> sr varchar(50)
> )> ;
> create external table mthly_ld
> SAMEAS mthly_info
> USING (
> FORMAT 'DELIMITED',
> DELIMITER '|',
> DATAFILES ( "DISK:/soft/devdba/citizen/db/sql/xaa",
>
> "DISK:/soft/devdba/citizen/db/sql/xab",
>
> "DISK:/soft/devdba/citizen/db/sql/xac",
>
> "DISK:/soft/devdba/citizen/db/sql/xad",
>
> "DISK:/soft/devdba/citizen/db/sql/xae",
>
> "DISK:/soft/devdba/citizen/db/sql/xaf",
>
> "DISK:/soft/devdba/citizen/db/sql/xag"
>
> ),
> EXPRESS
> );
> insert into mthly_info
> select * from mthly_ld> ;
> select count(*) from mthly_ld;
> select count(*) from mthly_info;>
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3ca1428e0df04e1b43fa6
Thanks Art & Martin. I realize that I should have added some more detail: Informix 11.5.FC6 running on sparc 10. I did expect a long transaction, but thought PDQ would kick in and speed up the failure. That never happened. So, FAIL #1. (Set PDQPriority 1) Most likely due to only having the table in one dbspace. (Also only 1 Temp dbspace in this instance). The 1.5M load and no error is quite interesting, though. An Informix bug? Turns out, every *other* file is being loaded. xaa, xac, xae, and xag. Ideas?
Hmm, no ideas. Try a wildcard - external tables have their own wildcarding format, documented in the manuals. You'll have to rename the files xa1, xa2, xa3, ... See if that is handled better. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Jul 17, 2013 at 12:39 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > Thanks Art & Martin. > > I realize that I should have added some more detail: > Informix 11.5.FC6 running on sparc 10. > > I did expect a long transaction, but thought PDQ would kick in and speed up > the failure. That never happened. So, FAIL #1. (Set PDQPriority 1) Most > likely > due to only having the table in one dbspace. (Also only 1 Temp dbspace in > this > instance). > > The 1.5M load and no error is quite interesting, though. An Informix bug? > > Turns out, every *other* file is being loaded. xaa, xac, xae, and xag. > Ideas? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133e41455c31d04e1b7ca96
I changed the DATAFILES section, doubling-up all entries except the first. DATAFILES ( "DISK:/soft/devdba/citizen/db/sql/xaa", "DISK:/soft/devdba/citizen/db/sql/xab", "DISK:/soft/devdba/citizen/db/sql/xab", "DISK:/soft/devdba/citizen/db/sql/xac", "DISK:/soft/devdba/citizen/db/sql/xac", "DISK:/soft/devdba/citizen/db/sql/xad", "DISK:/soft/devdba/citizen/db/sql/xad", "DISK:/soft/devdba/citizen/db/sql/xae", "DISK:/soft/devdba/citizen/db/sql/xae", "DISK:/soft/devdba/citizen/db/sql/xaf", "DISK:/soft/devdba/citizen/db/sql/xaf", "DISK:/soft/devdba/citizen/db/sql/xag", "DISK:/soft/devdba/citizen/db/sql/xag" ), Long Transaction! -- as hoped for! Informix now tries to read all the xa* files. How strange that it skips every other file. Can't believe no one has seen this before. (BTW, I'd rather not get into renaming the files since we're trying to automate this load process. "split" is bad enough, but "mv"ing files without knowing how many will be created is a nightmare of coding.)
Art, as far as the "load to" table goes, I tried using RAW, however once the data is loaded, I need to index it, so I need to convert to STANDARD. This requires a level-0 archive. Can't perform that inside of stored procedure or SQL script. Ah... I am *now* using an unlogged temp table. Forgot the "with no log" at the end of the "create temp table" statement. This should make a huge difference. :-) Yep! 3091792 loaded! Yippee! Now here's the kicker --- I'm still using the new DATAFILES section with the duplicated files. If that section was fully being read, I should have 5683584 rows (3091792 * 2 - 500000 (for the non-duped first file)). Mike
Very very strange. FYI, to keep in the back of your mind, and for general consumption of the forum: Temp tables are only unlogged by default IFF the TEMPTAB_NOLOG parameter is set to '1' in the ONCONFIG file, otherwise you DO have to include the WITH NO LOG option to get them to be unlogged and written to the temp dbspaces. If they are logged they will be written to a 'normal' dbspace listed in DBSPACETEMP or to the ROOT dbspace if only temp dbspaces are listed there. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Jul 17, 2013 at 1:18 PM, MICHAEL HOFFMAN <mrh@panix.com> wrote: > Art, > as far as the "load to" table goes, I tried using RAW, however once the > data > is loaded, I need to index it, so I need to convert to STANDARD. This > requires > a level-0 archive. Can't perform that inside of stored procedure or SQL > script. > > Ah... I am *now* using an unlogged temp table. Forgot the "with no log" at > the > end of the "create temp table" statement. > This should make a huge difference. :-) > > Yep! 3091792 loaded! Yippee! > > Now here's the kicker --- I'm still using the new DATAFILES section with > the > duplicated files. If that section was fully being read, I should have > 5683584 > rows (3091792 * 2 - 500000 (for the non-duped first file)). > > Mike > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0149338008cd0104e1bc10da