Loading tables in XPS
A DBA on XPS 8.21 was reloading three unload files (200-300 MB) with dbaccess "LOAD FROM ... INSERT INTO", and the loads had run nearly 48 hours with no visible progress; systables row counts aren't updated and SELECT COUNT(*) was blocked by the exclusive locks. Replies said progress can be checked via nrows in sysmaster:sysptprof, that FET_BUF_SIZE=32000 helps a little, and that the real fix is to define an external table over the .unl file and do INSERT INTO real_table SELECT * FROM ext_table, which should take minutes. His proposed CREATE EXTERNAL TABLE ... SAME AS ... USING DATAFILES syntax was called roughly right, with a pointer to the XPS manual; no final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
We have a very old server running XPS 8.21.UD2X2. I am currently running a
load of three tables from files using the following format:
load from <tablename>.unlinsert into <tablename>
These loads are approaching 48 hours of being run, even though two of the
unload files are around 200 MB, and the other is just over 300 MB. I am trying
to see if there is a way to view the number of rows that have been loaded, but
the systables won't be updated until update statistics is run, and I can't run
a Status through dbaccess or a "select (*) from <tablename>." These tables
have taken a while to load, with a table that was 73 MB taking several hours.
The CPU doesn't look constrained most of the time, and I can see there are
exclusive locks on the table, but I'm not sure how to look how the I/O
performace is doing.
Is there a way to see if there is a way to view the progress of these table
loads, or even make sure that these tables are loading? Should I cancel the
run of two of the tables to see if it helps the run of the other table, even
though the rollback might take a while?
Any insights would be appreciated.
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry:
2242108358@messaging.sprintpcs.com<mailto:2242108358@messaging.sprintpcs.com>
Page: 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Management#Dat
abaseManagement<http://wiki.intra.sears.com/confluence/display/TechStrag/Databas
e+Management>
This message, including any attachments, is the property of Sears Holdings
Corporation and/or one of its subsidiaries. It is confidential and may contain
proprietary or legally privileged information. If you are not the intended
recipient, please delete it without reading the contents. Thank you.
Ow.
Buy a Maserati, then take the tires off and drive it on the rims.
Loading XPS you should be using external tables. 100MB should load in a =
matter of minutes or seconds even, not 48 hours.
You can check sys master:sysptprof to see what nrows is for the table.
j.
On Oct 22, 2012, at 1:35 PM, Schleicher, Keith wrote:
> We have a very old server running XPS 8.21.UD2X2. I am currently =
running a=20
> load of three tables from files using the following format:=20
>=20
> load from <tablename>.unl=20> insert into <tablename>=20
>=20
> These loads are approaching 48 hours of being run, even though two of =
the=20
> unload files are around 200 MB, and the other is just over 300 MB. I =
am trying=20
> to see if there is a way to view the number of rows that have been =
loaded, but=20
> the systables won't be updated until update statistics is run, and I =
can't run=20
> a Status through dbaccess or a "select (*) from <tablename>." These =
tables=20
> have taken a while to load, with a table that was 73 MB taking several =
hours.=20
> The CPU doesn't look constrained most of the time, and I can see there =
are=20
> exclusive locks on the table, but I'm not sure how to look how the I/O=20=
> performace is doing.=20
>=20
> Is there a way to see if there is a way to view the progress of these =
table=20
> loads, or even make sure that these tables are loading? Should I =
cancel the=20
> run of two of the tables to see if it helps the run of the other =
table, even=20
> though the rollback might take a while?=20
>=20
> Any insights would be appreciated.=20
>=20
> Keith Schleicher=20
> IT Database Administrator=20
> Cell: 224-210-8358=20
> Blackberry:=20
> =
2242108358@messaging.sprintpcs.com<mailto:2242108358@messaging.sprintpcs.c=
om>=20
> Page: =
2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>=20
>=20
> For more information, use our DBA Wiki page link below:=20
>=20
> =
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Manageme=
nt#DatabaseManagement<http://wiki.intra.sears.com/confluence/display/TechS=
trag/Database+Management>=20
>=20
> This message, including any attachments, is the property of Sears =
Holdings=20
> Corporation and/or one of its subsidiaries. It is confidential and may =
contain=20
> proprietary or legally privileged information. If you are not the =
intended=20
> recipient, please delete it without reading the contents. Thank you.=20=
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Use an external table. It takes two sql statements, but is much much much
much more powerful/faster.
You can get a small improvement in load by setting FET_BUF_SIZE environment
variable to 32000
before starting dbaccess.
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 10/22/2012 10:35:42 AM:
> From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> To: ids@iiug.org,
> Date: 10/22/2012 10:37 AM
> Subject: Loading tables in XPS [28598]
> Sent by: ids-bounces@iiug.org
>
> We have a very old server running XPS 8.21.UD2X2. I am currently running
a
> load of three tables from files using the following format:
>
> load from <tablename>.unl> insert into <tablename>
>
> These loads are approaching 48 hours of being run, even though two of the
> unload files are around 200 MB, and the other is just over 300 MB. Iam
trying
> to see if there is a way to view the number of rows that have been
> loaded, but
> the systables won't be updated until update statistics is run, and Ican't
run
> a Status through dbaccess or a "select (*) from <tablename>." These
tables
> have taken a while to load, with a table that was 73 MB taking several
hours.
> The CPU doesn't look constrained most of the time, and I can see there
are
> exclusive locks on the table, but I'm not sure how to look how the I/O
> performace is doing.
>
> Is there a way to see if there is a way to view the progress of these
table
> loads, or even make sure that these tables are loading? Should I cancel
the
> run of two of the tables to see if it helps the run of the other table,
even
> though the rollback might take a while?
>
> Any insights would be appreciated.
>
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry:
>
2242108358@messaging.sprintpcs.com<mailto:2242108358@messaging.sprintpcs.com>
> Page: 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
>
> For more information, use our DBA Wiki page link below:
>
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database
> +Management#DatabaseManagement<http://wiki.intra.sears.com/
> confluence/display/TechStrag/Database+Management>
>
> This message, including any attachments, is the property of Sears
Holdings
> Corporation and/or one of its subsidiaries. It is confidential and
> may contain
> proprietary or legally privileged information. If you are not the
intended
> recipient, please delete it without reading the contents. Thank you.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Here's what I did:
Got the dbschema of the tables.
Updated the extent sizes.
Unloaded the tables to files.
Dropped the tables.
Run the load of the tables from the files.
I'm guessing that using an external table isn't going to help loading the f=
ile now. While I will consider this for the future, I need to know what ca=
n be done at this point in time to ensure the tables are being loaded.
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com=A0
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
t#DatabaseManagement
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John =
Miller iii
Sent: Monday, October 22, 2012 12:58 PM
To: ids@iiug.org
Subject: Re: Loading tables in XPS [28600]
Use an external table. It takes two sql statements, but is much much much m=
uch more powerful/faster.=20
You can get a small improvement in load by setting FET_BUF_SIZE environment=
variable to 32000 before starting dbaccess.=20
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)=20
ids-bounces@iiug.org wrote on 10/22/2012 10:35:42 AM:=20
> From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> To: ids@iiug.org,
> Date: 10/22/2012 10:37 AM
> Subject: Loading tables in XPS [28598] Sent by: ids-bounces@iiug.org
>=20
> We have a very old server running XPS 8.21.UD2X2. I am currently=20
> running
a=20
> load of three tables from files using the following format:=20
>=20
> load from <tablename>.unl> insert into <tablename>
>=20
> These loads are approaching 48 hours of being run, even though two of=20
> the
> unload files are around 200 MB, and the other is just over 300 MB. Iam
trying=20
> to see if there is a way to view the number of rows that have been=20
> loaded, but the systables won't be updated until update statistics is=20
> run, and Ican't
run=20
> a Status through dbaccess or a "select (*) from <tablename>." These
tables=20
> have taken a while to load, with a table that was 73 MB taking several
hours.=20
> The CPU doesn't look constrained most of the time, and I can see there
are=20
> exclusive locks on the table, but I'm not sure how to look how the I/O=20
> performace is doing.
>=20
> Is there a way to see if there is a way to view the progress of these
table=20
> loads, or even make sure that these tables are loading? Should I=20
> cancel
the=20
> run of two of the tables to see if it helps the run of the other=20
> table,
even=20
> though the rollback might take a while?=20
>=20
> Any insights would be appreciated.=20
>=20
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry:=20
>=20
2242108358@messaging.sprintpcs.com<mailto:2242108358@messaging.sprintpcs.co=
m>=20
> Page:=20
> 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
>=20
> For more information, use our DBA Wiki page link below:=20
>=20
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database
> +Management#DatabaseManagement<http://wiki.intra.sears.com/
> confluence/display/TechStrag/Database+Management>
>=20
> This message, including any attachments, is the property of Sears
Holdings=20
> Corporation and/or one of its subsidiaries. It is confidential and may=20
> contain proprietary or legally privileged information. If you are not=20
> the
intended=20
> recipient, please delete it without reading the contents. Thank you.=20
>=20
>=20
>=20
***************************************************************************=
****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.=20
This message, including any attachments, is the property of Sears Holdings =
Corporation and/or one of its subsidiaries. It is confidential and may cont=
ain proprietary or legally privileged information. If you are not the inten=
ded recipient, please delete it without reading the contents. Thank you.
Defining an external table on the 'load' file and loading from that table
into the 'real' table by using INSERT INTO real_table SELECT * FROM
external_table; will be MUCH MUCH faster as John stated.
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 Mon, Oct 22, 2012 at 2:15 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> Here's what I did:
>
> Got the dbschema of the tables.
> Updated the extent sizes.
> Unloaded the tables to files.
> Dropped the tables.
> Run the load of the tables from the files.
>
> I'm guessing that using an external table isn't going to help loading the
> f=
> ile now. While I will consider this for the future, I need to know what ca=
> n be done at this point in time to ensure the tables are being loaded.
>
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com=A0
>
> For more information, use our DBA Wiki page link below:
>
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
> t#DatabaseManagement
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> John =
> Miller iii
> Sent: Monday, October 22, 2012 12:58 PM
> To: ids@iiug.org
> Subject: Re: Loading tables in XPS [28600]
>
> Use an external table. It takes two sql statements, but is much much much
> m=
> uch more powerful/faster.=20
>
> You can get a small improvement in load by setting FET_BUF_SIZE
> environment=
> variable to 32000 before starting dbaccess.=20
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)=20
>
> ids-bounces@iiug.org wrote on 10/22/2012 10:35:42 AM:=20
>
> > From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> > To: ids@iiug.org,
> > Date: 10/22/2012 10:37 AM
> > Subject: Loading tables in XPS [28598] Sent by: ids-bounces@iiug.org
> >=20
> > We have a very old server running XPS 8.21.UD2X2. I am currently=20
> > running
> a=20
> > load of three tables from files using the following format:=20
> >=20
> > load from <tablename>.unl> > insert into <tablename>
> >=20
> > These loads are approaching 48 hours of being run, even though two of=20
> > the
>
> > unload files are around 200 MB, and the other is just over 300 MB. Iam
> trying=20
> > to see if there is a way to view the number of rows that have been=20
> > loaded, but the systables won't be updated until update statistics is=20
> > run, and Ican't
> run=20
> > a Status through dbaccess or a "select (*) from <tablename>." These
> tables=20
> > have taken a while to load, with a table that was 73 MB taking several
> hours.=20
> > The CPU doesn't look constrained most of the time, and I can see there
> are=20
> > exclusive locks on the table, but I'm not sure how to look how the I/O=20
> > performace is doing.
> >=20
> > Is there a way to see if there is a way to view the progress of these
> table=20
> > loads, or even make sure that these tables are loading? Should I=20
> > cancel
> the=20
> > run of two of the tables to see if it helps the run of the other=20
> > table,
> even=20
> > though the rollback might take a while?=20
> >=20
> > Any insights would be appreciated.=20
> >=20
> > Keith Schleicher
> > IT Database Administrator
> > Cell: 224-210-8358
> > Blackberry:=20
> >=20
> 2242108358@messaging.sprintpcs.com<mailto:
> 2242108358@messaging.sprintpcs.co=
> m>=20
>
> > Page:=20
> > 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
> >=20
> > For more information, use our DBA Wiki page link below:=20
> >=20
> > http://wiki.intra.sears.com/confluence/display/TechStrag/Database
> > +Management#DatabaseManagement<http://wiki.intra.sears.com/
> > confluence/display/TechStrag/Database+Management>
> >=20
> > This message, including any attachments, is the property of Sears
> Holdings=20
> > Corporation and/or one of its subsidiaries. It is confidential and may=20
> > contain proprietary or legally privileged information. If you are not=20
> > the
> intended=20
> > recipient, please delete it without reading the contents. Thank you.=20
> >=20
> >=20
> >=20
>
>
> ***************************************************************************=
> ****=20
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=20
> >=20
>
>
> ***************************************************************************=
> ****
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>
> This message, including any attachments, is the property of Sears Holdings
> =
> Corporation and/or one of its subsidiaries. It is confidential and may
> cont=
> ain proprietary or legally privileged information. If you are not the
> inten=
> ded recipient, please delete it without reading the contents. Thank you.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340ef1e33c2a04ccaa210e
So I would these statements:
create external table unload_table same as <tablename> using datafiles exte=
rnal <tablename>.unl;
insert into <tablename> select * from unload_table;
Or is there something wrong in my syntax?
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com=A0
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
t#DatabaseManagement
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art K=
agel
Sent: Monday, October 22, 2012 1:37 PM
To: ids@iiug.org
Subject: Re: Loading tables in XPS [28602]
Defining an external table on the 'load' file and loading from that table i=
nto the 'real' table by using INSERT INTO real_table SELECT * FROM external=
_table; will be MUCH MUCH faster as John stated.=20
Art=20
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/=20
Disclaimer: Please keep in mind that my own opinions are my own opinions an=
d do not reflect on my employer, Advanced DataTools, the IIUG, nor any othe=
r 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 ent=
ities themselves.=20
On Mon, Oct 22, 2012 at 2:15 PM, Schleicher, Keith < Keith.Schleicher@sears=
hc.com> wrote:=20
> Here's what I did:=20
>=20
> Got the dbschema of the tables.=20
> Updated the extent sizes.=20
> Unloaded the tables to files.=20
> Dropped the tables.=20
> Run the load of the tables from the files.=20
>=20
> I'm guessing that using an external table isn't going to help loading=20
> the f=3D ile now. While I will consider this for the future, I need to=20
> know what ca=3D n be done at this point in time to ensure the tables are=
=20
> being loaded.
>=20
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com=3DA0
>=20
> For more information, use our DBA Wiki page link below:=20
>=20
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Mana
> gemen=3D
> t#DatabaseManagement
>=20
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=20
> John =3D Miller iii
> Sent: Monday, October 22, 2012 12:58 PM
> To: ids@iiug.org
> Subject: Re: Loading tables in XPS [28600]
>=20
> Use an external table. It takes two sql statements, but is much much=20
> much m=3D uch more powerful/faster.=3D20
>=20
> You can get a small improvement in load by setting FET_BUF_SIZE=20
> environment=3D variable to 32000 before starting dbaccess.=3D20
>=20
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)=3D20
>=20
> ids-bounces@iiug.org wrote on 10/22/2012 10:35:42 AM:=3D20
>=20
> > From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> > To: ids@iiug.org,
> > Date: 10/22/2012 10:37 AM
> > Subject: Loading tables in XPS [28598] Sent by: ids-bounces@iiug.org
> >=3D20
> > We have a very old server running XPS 8.21.UD2X2. I am currently=3D20=
=20=20
> >running
> a=3D20
> > load of three tables from files using the following format:=3D20
> >=3D20
> > load from <tablename>.unl> > insert into <tablename>
> >=3D20
> > These loads are approaching 48 hours of being run, even though two=20
> >of=3D20 the
>=20
> > unload files are around 200 MB, and the other is just over 300 MB.=20
> > Iam
> trying=3D20
> > to see if there is a way to view the number of rows that have=20
> > been=3D20 loaded, but the systables won't be updated until update=20
> > statistics is=3D20 run, and Ican't
> run=3D20
> > a Status through dbaccess or a "select (*) from <tablename>." These
> tables=3D20
> > have taken a while to load, with a table that was 73 MB taking=20
> > several
> hours.=3D20
> > The CPU doesn't look constrained most of the time, and I can see=20
> > there
> are=3D20
> > exclusive locks on the table, but I'm not sure how to look how the=20
> >I/O=3D20 performace is doing.
> >=3D20
> > Is there a way to see if there is a way to view the progress of=20
> >these
> table=3D20
> > loads, or even make sure that these tables are loading? Should I=3D20=20
> > cancel
> the=3D20
> > run of two of the tables to see if it helps the run of the other=3D20=20
> > table,
> even=3D20
> > though the rollback might take a while?=3D20
> >=3D20
> > Any insights would be appreciated.=3D20
> >=3D20
> > Keith Schleicher
> > IT Database Administrator
> > Cell: 224-210-8358
> > Blackberry:=3D20
> >=3D20
> 2242108358@messaging.sprintpcs.com<mailto:=20
> 2242108358@messaging.sprintpcs.co=3D
> m>=3D20
>=20
> > Page:=3D20
> > 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
> >=3D20
> > For more information, use our DBA Wiki page link below:=3D20
> >=3D20
> > http://wiki.intra.sears.com/confluence/display/TechStrag/Database
> > +Management#DatabaseManagement<http://wiki.intra.sears.com/
> > confluence/display/TechStrag/Database+Management>
> >=3D20
> > This message, including any attachments, is the property of Sears
> Holdings=3D20
> > Corporation and/or one of its subsidiaries. It is confidential and=20
> > may=3D20 contain proprietary or legally privileged information. If you=
=20
> > are not=3D20 the
> intended=3D20
> > recipient, please delete it without reading the contents. Thank=20
> >you.=3D20
> >=3D20
> >=3D20
> >=3D20
>=20
>=20
> **********************************************************************
> *****=3D
> ****=3D20
>=20
> > Forum Note: Use "Reply" to post a response in the discussion=20
> >forum.=3D20
> >=3D20
>=20
>=20
> **********************************************************************
> *****=3D
> ****
> Forum Note: Use "Reply" to post a response in the discussion forum.=3D20
>=20
> This message, including any attachments, is the property of Sears=20
> Holdings =3D Corporation and/or one of its subsidiaries. It is=20
> confidential and may cont=3D ain proprietary or legally privileged=20
> information. If you are not the inten=3D ded recipient, please delete it=
=20
> without reading the contents. Thank you.
>=20
>=20
>=20
>=20
***************************************************************************=
****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20
>=20
>=20
--14dae9340ef1e33c2a04ccaa210e=20
***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.=20
This message, including any attachments, is the property of Sears
I don't remember the XPS syntax which is subtly different from the curreht
IDS syntax, so check the manual for details so i don't steer you wrong. You
are not far off.
Art
On Oct 22, 2012 11:49 AM, "Schleicher, Keith" <Keith.Schleicher@searshc.com>
wrote:
> So I would these statements:
>
> create external table unload_table same as <tablename> using datafiles
> exte=
> rnal <tablename>.unl;
> insert into <tablename> select * from unload_table;
>
> Or is there something wrong in my syntax?
>
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com=A0
>
> For more information, use our DBA Wiki page link below:
>
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
> t#DatabaseManagement
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> K=
> agel
> Sent: Monday, October 22, 2012 1:37 PM
> To: ids@iiug.org
> Subject: Re: Loading tables in XPS [28602]
>
> Defining an external table on the 'load' file and loading from that table
> i=
> nto the 'real' table by using INSERT INTO real_table SELECT * FROM
> external=
> _table; will be MUCH MUCH faster as John stated.=20
>
> Art=20
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/=20
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> an=
> d do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> othe=
> r 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 ent=
> ities themselves.=20
>
> On Mon, Oct 22, 2012 at 2:15 PM, Schleicher, Keith < Keith.Schleicher@sears
> =
> hc.com> wrote:=20
>
> > Here's what I did:=20
> >=20
> > Got the dbschema of the tables.=20
> > Updated the extent sizes.=20
> > Unloaded the tables to files.=20
> > Dropped the tables.=20
> > Run the load of the tables from the files.=20
> >=20
> > I'm guessing that using an external table isn't going to help loading=20
> > the f=3D ile now. While I will consider this for the future, I need to=20
> > know what ca=3D n be done at this point in time to ensure the tables are=
> =20
> > being loaded.
> >=20
> > Keith Schleicher
> > IT Database Administrator
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com=3DA0
> >=20
> > For more information, use our DBA Wiki page link below:=20
> >=20
> > http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Mana
> > gemen=3D
> > t#DatabaseManagement
> >=20
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=20
> > John =3D Miller iii
> > Sent: Monday, October 22, 2012 12:58 PM
> > To: ids@iiug.org
> > Subject: Re: Loading tables in XPS [28600]
> >=20
> > Use an external table. It takes two sql statements, but is much much=20
> > much m=3D uch more powerful/faster.=3D20
> >=20
> > You can get a small improvement in load by setting FET_BUF_SIZE=20
> > environment=3D variable to 32000 before starting dbaccess.=3D20
> >=20
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)=3D20
> >=20
> > ids-bounces@iiug.org wrote on 10/22/2012 10:35:42 AM:=3D20
> >=20
> > > From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> > > To: ids@iiug.org,
> > > Date: 10/22/2012 10:37 AM
> > > Subject: Loading tables in XPS [28598] Sent by: ids-bounces@iiug.org
> > >=3D20
> > > We have a very old server running XPS 8.21.UD2X2. I am currently=3D20=
> =20=20
> > >running
> > a=3D20
> > > load of three tables from files using the following format:=3D20
> > >=3D20
> > > load from <tablename>.unl> > > insert into <tablename>
> > >=3D20
> > > These loads are approaching 48 hours of being run, even though two=20
> > >of=3D20 the
> >=20
> > > unload files are around 200 MB, and the other is just over 300 MB.=20
> > > Iam
> > trying=3D20
> > > to see if there is a way to view the number of rows that have=20
> > > been=3D20 loaded, but the systables won't be updated until update=20
> > > statistics is=3D20 run, and Ican't
> > run=3D20
> > > a Status through dbaccess or a "select (*) from <tablename>." These
> > tables=3D20
> > > have taken a while to load, with a table that was 73 MB taking=20
> > > several
> > hours.=3D20
> > > The CPU doesn't look constrained most of the time, and I can see=20
> > > there
> > are=3D20
> > > exclusive locks on the table, but I'm not sure how to look how the=20
> > >I/O=3D20 performace is doing.
> > >=3D20
> > > Is there a way to see if there is a way to view the progress of=20
> > >these
> > table=3D20
> > > loads, or even make sure that these tables are loading? Should
> I=3D20=20
> > > cancel
> > the=3D20
> > > run of two of the tables to see if it helps the run of the
> other=3D20=20
> > > table,
> > even=3D20
> > > though the rollback might take a while?=3D20
> > >=3D20
> > > Any insights would be appreciated.=3D20
> > >=3D20
> > > Keith Schleicher
> > > IT Database Administrator
> > > Cell: 224-210-8358
> > > Blackberry:=3D20
> > >=3D20
> > 2242108358@messaging.sprintpcs.com<mailto:=20
> > 2242108358@messaging.sprintpcs.co=3D
> > m>=3D20
> >=20
> > > Page:=3D20
> > > 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
> > >=3D20
> > > For more information, use our DBA Wiki page link below:=3D20
> > >=3D20
> > > http://wiki.intra.sears.com/confluence/display/TechStrag/Database
> > > +Management#DatabaseManagement<http://wiki.intra.sears.com/
> > > confluence/display/TechStrag/Database+Management>
> > >=3D20
> > > This message, including any attachments, is the property of Sears
> > Holdings=3D20
> > > Corporation and/or one of its subsidiaries. It is confidential and=20
> > > may=3D20 contain proprietary or legally privileged information. If you=
> =20
> > > are not=3D20 the
> > intended=3D20
> > > recipient, please delete it without reading the contents. Thank=20
> > >you.=3D20
> > >=3D20
> > >=3D20
> > >=3D20
> >=20
> >=20
> > **********************************************************************
> > *****=3D
> > ****=3D20
> >=20
> > > Forum Note: Use "Reply" to post a response in the discussion=20
> > >forum.=3D20
> > >=3D20
> >=20
> >=20
> > **********************************************************************
> > *****=3D
> > ****
> > Forum Note: Use "Reply" to post a response in the discussion forum.=3D20
> >=20
> > This message, including any attachments, is the property of Sears=20
> > Holdings =3D Corporation and/or one of its subsidiaries. It is=20
> > con
Here is the syntax for future reference:
create external table load_table sameas <tablename>
using ( datafiles ("DISK:1: <filename with full path>"));
insert into <tablename> select * from load_table;
Keith Schleicher
IT Database Administrator
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com=A0
For more information, use our DBA Wiki page link below:
http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Managemen=
t#DatabaseManagement
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art K=
agel
Sent: Monday, October 22, 2012 2:00 PM
To: ids@iiug.org
Subject: RE: Loading tables in XPS [28604]
I don't remember the XPS syntax which is subtly different from the curreht =
IDS syntax, so check the manual for details so i don't steer you wrong. You=
are not far off.=20
Art
On Oct 22, 2012 11:49 AM, "Schleicher, Keith" <Keith.Schleicher@searshc.com>
wrote:=20
> So I would these statements:=20
>=20
> create external table unload_table same as <tablename> using datafiles=20
> exte=3D rnal <tablename>.unl; insert into <tablename> select * from=20
> unload_table;
>=20
> Or is there something wrong in my syntax?=20
>=20
> Keith Schleicher
> IT Database Administrator
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com=3DA0
>=20
> For more information, use our DBA Wiki page link below:=20
>=20
> http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Mana
> gemen=3D
> t#DatabaseManagement
>=20
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of=20
> Art K=3D agel
> Sent: Monday, October 22, 2012 1:37 PM
> To: ids@iiug.org
> Subject: Re: Loading tables in XPS [28602]
>=20
> Defining an external table on the 'load' file and loading from that=20
> table i=3D nto the 'real' table by using INSERT INTO real_table SELECT *=
=20
> FROM external=3D _table; will be MUCH MUCH faster as John stated.=3D20
>=20
> Art=3D20
>=20
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/=3D20
>=20
> Disclaimer: Please keep in mind that my own opinions are my own=20
> opinions an=3D d do not reflect on my employer, Advanced DataTools, the=20
> IIUG, nor any othe=3D r organization with which I am associated either=20
> explicitly, implicitly, or=3D by inference. Neither do those opinions=20
> reflect those of other individuals=3D affiliated with any entity with=20
> which I am affiliated nor those of the ent=3D ities themselves.=3D20
>=20
> On Mon, Oct 22, 2012 at 2:15 PM, Schleicher, Keith <=20
> Keith.Schleicher@sears =3D hc.com> wrote:=3D20
>=20
> > Here's what I did:=3D20
> >=3D20
> > Got the dbschema of the tables.=3D20
> > Updated the extent sizes.=3D20
> > Unloaded the tables to files.=3D20
> > Dropped the tables.=3D20
> > Run the load of the tables from the files.=3D20
> >=3D20
> > I'm guessing that using an external table isn't going to help=20
> >loading=3D20 the f=3D3D ile now. While I will consider this for the=20
> >future, I need to=3D20 know what ca=3D3D n be done at this point in tim=
e=20
> >to ensure the tables are=3D
> =3D20
> > being loaded.=20
> >=3D20
> > Keith Schleicher
> > IT Database Administrator
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com=3D3DA0
> >=3D20
> > For more information, use our DBA Wiki page link below:=3D20
> >=3D20
> >=20
> >http://wiki.intra.sears.com/confluence/display/TechStrag/Database+Man
> >a
> > gemen=3D3D
> > t#DatabaseManagement
> >=3D20
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf=20
> >Of=3D20 John =3D3D Miller iii
> > Sent: Monday, October 22, 2012 12:58 PM
> > To: ids@iiug.org
> > Subject: Re: Loading tables in XPS [28600]
> >=3D20
> > Use an external table. It takes two sql statements, but is much=20
> >much=3D20 much m=3D3D uch more powerful/faster.=3D3D20
> >=3D20
> > You can get a small improvement in load by setting FET_BUF_SIZE=3D20=20=
=20
> >environment=3D3D variable to 32000 before starting dbaccess.=3D3D20
> >=3D20
> > John F. Miller III
> > STSM, Embedability Architect
> > miller3@us.ibm.com
> > 503-578-5645
> > IBM Informix Dynamic Server (IDS)=3D3D20
> >=3D20
> > ids-bounces@iiug.org wrote on 10/22/2012 10:35:42 AM:=3D3D20
> >=3D20
> > > From: "Schleicher, Keith" <Keith.Schleicher@searshc.com>
> > > To: ids@iiug.org,
> > > Date: 10/22/2012 10:37 AM
> > > Subject: Loading tables in XPS [28598] Sent by:=20
> > >ids-bounces@iiug.org
> > >=3D3D20
> > > We have a very old server running XPS 8.21.UD2X2. I am=20
> > >currently=3D3D20=3D
> =3D20=3D20
> > >running
> > a=3D3D20
> > > load of three tables from files using the following format:=3D3D20
> > >=3D3D20
> > > load from <tablename>.unl> > > insert into <tablename>
> > >=3D3D20
> > > These loads are approaching 48 hours of being run, even though=20
> > >two=3D20
> > >of=3D3D20 the
> >=3D20
> > > unload files are around 200 MB, and the other is just over 300=20
> > > MB.=3D20 Iam
> > trying=3D3D20
> > > to see if there is a way to view the number of rows that have=3D20
> > > been=3D3D20 loaded, but the systables won't be updated until=20
> > > update=3D20 statistics is=3D3D20 run, and Ican't
> > run=3D3D20
> > > a Status through dbaccess or a "select (*) from <tablename>."=20
> > > These
> > tables=3D3D20
> > > have taken a while to load, with a table that was 73 MB taking=3D20=20
> > > several
> > hours.=3D3D20
> > > The CPU doesn't look constrained most of the time, and I can=20
> > > see=3D20 there
> > are=3D3D20
> > > exclusive locks on the table, but I'm not sure how to look how=20
> > >the=3D20
> > >I/O=3D3D20 performace is doing.=20
> > >=3D3D20
> > > Is there a way to see if there is a way to view the progress of=3D20=
=20
> > >these
> > table=3D3D20
> > > loads, or even make sure that these tables are loading? Should
> I=3D3D20=3D20
> > > cancel
> > the=3D3D20
> > > run of two of the tables to see if it helps the run of the
> other=3D3D20=3D20
> > > table,
> > even=3D3D20
> > > though the rollback might take a while?=3D3D20
> > >=3D3D20
> > > Any insights would be appreciated.=3D3D20
> > >=3D3D20
> > > Keith Schleicher
> > > IT Database Administrator
> > > Cell: 224-210-8358
> > > Blackberry:=3D3D20
> > >=3D3D20
> > 2242108358@messaging.sprintpcs.com<mailto:=3D20
> > 2242108358@messaging.sprintpcs.co=3D3D
> > m>=3D3D20
> >=3D20
> > > Page:=3D3D20
> > > 2242108358@sprint.skytel.com<mailto:2242108358@sprint.skytel.com>
> > >=3D3D20
> > > For more information, use our DBA Wiki page link below:=3D3D20
> > >=3D3D20
> > > http://wiki.intra.sears.com/confluence/display/TechStrag/Database
> > > +Management#DatabaseManagement<http://wiki.intra