IDS Forum

RE: Loading tables in XPS

Posted By: Schleicher, Keith
Date: Monday, 22 October 2012, at 3:33 p.m.

In Response To: RE: Loading tables in XPS (Art Kagel)

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.sears.com/
> > > confluence/display/TechStrag/Database+Management>
> > >=3D3D20
> > > This message, including any attachments, is the property of Sears
> > Holdings=3D3D20
> > > Corporation and/or one of its subsidiaries. It is confidential=20
> > > and=3D20
> > > may=3D3D20 contain proprietary or legally privileged information. If=
=20
> > > you=3D
> =3D20
> > > are not=3D3D20 the
> > intended=3D3D20
> > > recipient, please delete it without reading the contents. Thank=3D20
> > >you.=3D3D20
> > >=3D3D20
> > >=3D3D20
> > >=3D3D20
> >=3D20
> >=3D20
> >=20
> >*********************************************************************
> >*
> > *****=3D3D
> > ****=3D3D20
> >=3D20
> > > Forum Note: Use "Reply" to post a response in the discussion=3D20
> > >forum.=3D3D20
> > >=3D3D20
> >=3D20
> >=3D20
> >=20
> >*********************************************************************
> >*
> > *****=3D3D
> > ****
> > Forum Note: Use "Reply" to post a response in the discussion=20
> >forum.=3D3D20
> >=3D20
> > This message, including any attachments, is the property of Sears=3D20=
=20=20
> >Holdings =3D3D Corporation and/or one of its subsidiaries. It is=3D20=20=
=20
> >confidential and may cont=3D3D ain proprietary or legally privileged=3D2=
0=20=20
> >information. If you are not the inten=3D3D ded recipient, please delete=
=20
> >it=3D
> =3D20
> > without reading the contents. Thank you.=20
> >=3D20
> >=3D20
> >=3D20
> >=3D20
>=20
> **********************************************************************
> *****=3D
> ****=3D20
> > Forum Note: Use "Reply" to post a response in the discussion=20
> >forum.=3D20
> >=3D20
> >=3D20
>=20
> --14dae9340ef1e33c2a04ccaa210e=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

--e89a8f838b35d5e1a204ccaa7602=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.

Messages In This Thread

• Loading tables in XPS
  Schleicher, Keith -- Monday, 22 October 2012, at 1:35 p.m.
  • Re: Loading tables in XPS
    Jack Parker -- Monday, 22 October 2012, at 1:43 p.m.
  • Re: Loading tables in XPS
    John Miller iii -- Monday, 22 October 2012, at 1:58 p.m.
    • RE: Loading tables in XPS
      Schleicher, Keith -- Monday, 22 October 2012, at 2:15 p.m.
      • Re: Loading tables in XPS
        Art Kagel -- Monday, 22 October 2012, at 2:36 p.m.
        • RE: Loading tables in XPS
          Schleicher, Keith -- Monday, 22 October 2012, at 2:48 p.m.
          • RE: Loading tables in XPS
            Art Kagel -- Monday, 22 October 2012, at 3:00 p.m.
            • RE: Loading tables in XPS
              Schleicher, Keith -- Monday, 22 October 2012, at 3:33 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.