IDS Forum

RE: Loading tables in XPS

Posted By: Art Kagel
Date: Monday, 22 October 2012, at 3:00 p.m.

In Response To: RE: Loading tables in XPS (Schleicher, Keith)

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

--e89a8f838b35d5e1a204ccaa7602

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.