why does this take longer

Phil Oelkers

why does this take longer
We have a process that populates a db2 table using a

Insert ...
from
select ...

The only use of the resulting table is as input into a cobol program. The
entire table is brought into the program row by row just like it was a flat
file.

To make things run a bit better we tried using DB2BATCH to run only the
select portion of the sql used to create the table and create a flatfile
that
can be read by the cobol program.

The new process appears to work ok but seems to take longer - it seems to
me creating a flat file should be quicker than than inserting into a table -
am
I missing something?

Phil Oelkers
Technical Consultant
Experian Automotive Information Services
[login to unmask email]
> (847) 619-7267
>
>
>
>

Peter J Krawetzky

Re: why does this take longer
(in response to Phil Oelkers)
Check your logic to make sure you are not closing and reopening the file
before each read. This can create a significant amout of overhead.

I assume you are running this on an OS/390 environment?



Peter J. Krawetzky, Database Administrator
Aetna, Inc. Hartford, CT 06156
Email: [login to unmask email]
Phone: (860) 273-0301



-----Original Message-----
From: Oelkers, Phil [mailto:[login to unmask email]
Sent: Friday, October 08, 1999 9:25 AM
To: [login to unmask email]
Subject: why does this take longer


We have a process that populates a db2 table using a

Insert ...
from
select ...

The only use of the resulting table is as input into a cobol program. The
entire table is brought into the program row by row just like it was a flat
file.

To make things run a bit better we tried using DB2BATCH to run only the
select portion of the sql used to create the table and create a flatfile
that
can be read by the cobol program.

The new process appears to work ok but seems to take longer - it seems to
me creating a flat file should be quicker than than inserting into a table -
am
I missing something?

Phil Oelkers
Technical Consultant
Experian Automotive Information Services
[login to unmask email]
> (847) 619-7267
>
>
>
>

[login to unmask email]

Re: why does this take longer
(in response to Peter J Krawetzky)
There are couple of things you could do. One is extract the data in a flat file
then use the load utility to put the data back into the table, just make sure
you use the resume yes parm.
John




"Oelkers, Phil" <[login to unmask email]> on 10/08/99 09:25:19 AM

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

To: [login to unmask email]
cc:
Subject: why does this take longer




We have a process that populates a db2 table using a

Insert ...
from
select ...

The only use of the resulting table is as input into a cobol program. The
entire table is brought into the program row by row just like it was a flat
file.

To make things run a bit better we tried using DB2BATCH to run only the
select portion of the sql used to create the table and create a flatfile
that
can be read by the cobol program.

The new process appears to work ok but seems to take longer - it seems to
me creating a flat file should be quicker than than inserting into a table -
am
I missing something?

Phil Oelkers
Technical Consultant
Experian Automotive Information Services
[login to unmask email]
> (847) 619-7267
>
>
>
>

Mike Holmans

Re: why does this take longer
(in response to John_Lendman@FPL.COM)
When you create the flat file, you incur physical writes to DASD. When you
do the INSERTs, the pages get updated in buffer, but the write to DASD is
asynchronous.

With the first, the writes to DASD have to complete before your process is
finished; in the second, your operation completes once the bufferpool has
been updated, and the write to DASD can happen whenever DB2 gets round to
it, but does not hold up your subsequent processing.

Mike Holmans
BT ISE Technical Design
[login to unmask email]

> -----Original Message-----
> From: Oelkers, Phil [SMTP:[login to unmask email]
> Sent: Friday, October 08, 1999 2:25 PM
> To: [login to unmask email]
> Subject: why does this take longer
>
> We have a process that populates a db2 table using a
>
> Insert ...
> from
> select ...
>
> The only use of the resulting table is as input into a cobol program.
> The
> entire table is brought into the program row by row just like it was a
> flat
> file.
>
> To make things run a bit better we tried using DB2BATCH to run only the
> select portion of the sql used to create the table and create a flatfile
> that
> can be read by the cobol program.
>
> The new process appears to work ok but seems to take longer - it seems to
> me creating a flat file should be quicker than than inserting into a table
> -
> am
> I missing something?
>
> Phil Oelkers
> Technical Consultant
> Experian Automotive Information Services
> [login to unmask email]
> > (847) 619-7267
> >
> >
> >
> >

[login to unmask email]

Re: why does this take longer
(in response to Mike Holmans)
I have been told that doing a LOAD of a flatfile with LOG NO (default is YES) is faster than doing the equivalent individual inserts from a program because logging of individual inserts is disabled during the load. Remember that you have
to do an IMAGE COPY after the LOAD to remove the copy pending status that is set by LOG NO.






[login to unmask email] on 99/10/08 08:52:22 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Rohn Solecki/MTSCommunications/MTS)
Subject: Re: why does this take longer




There are couple of things you could do. One is extract the data in a flat file
then use the load utility to put the data back into the table, just make sure
you use the resume yes parm.
John




"Oelkers, Phil" <[login to unmask email]> on 10/08/99 09:25:19 AM

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

To: [login to unmask email]
cc:
Subject: why does this take longer




We have a process that populates a db2 table using a

Insert ...
from
select ...

The only use of the resulting table is as input into a cobol program. The
entire table is brought into the program row by row just like it was a flat
file.

To make things run a bit better we tried using DB2BATCH to run only the
select portion of the sql used to create the table and create a flatfile
that
can be read by the cobol program.

The new process appears to work ok but seems to take longer - it seems to
me creating a flat file should be quicker than than inserting into a table -
am
I missing something?

Phil Oelkers
Technical Consultant
Experian Automotive Information Services
[login to unmask email]
> (847) 619-7267
>
>
>
>

Missy Case

Re: why does this take longer
(in response to Rohn.Solecki@MTS.MB.CA)
Rohn,
Or you can use the inline copy feature with the load LOG(NO) utility. I
have found this to work really great in V5, saved time and helped my DR as
the users option was 'setnocopypend' right after the load. The load w/ copy
is faster than the load then image copy so it should be an easy sell point.

Missy Case
North Dakota - Life in the VAST Lane.
DB2 DBA
PKS Infrastructure Services
North Dakota 'office'

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Friday, October 08, 1999 9:30 AM
> To: [login to unmask email]
> Subject: Re: why does this take longer
>
> I have been told that doing a LOAD of a flatfile with LOG NO (default is
> YES) is faster than doing the equivalent individual inserts from a program
> because logging of individual inserts is disabled during the load.
> Remember that you have
> to do an IMAGE COPY after the LOAD to remove the copy pending status that
> is set by LOG NO.
>
>
>
>
>
>
> [login to unmask email] on 99/10/08 08:52:22 AM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: Rohn Solecki/MTSCommunications/MTS)
> Subject: Re: why does this take longer
>
>
>
>
> There are couple of things you could do. One is extract the data in a flat
> file
> then use the load utility to put the data back into the table, just make
> sure
> you use the resume yes parm.
> John
>
>
>
>
> "Oelkers, Phil" <[login to unmask email]> on 10/08/99 09:25:19 AM
>
> Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>
>
> To: [login to unmask email]
> cc:
> Subject: why does this take longer
>
>
>
>
> We have a process that populates a db2 table using a
>
> Insert ...
> from
> select ...
>
> The only use of the resulting table is as input into a cobol program.
> The
> entire table is brought into the program row by row just like it was a
> flat
> file.
>
> To make things run a bit better we tried using DB2BATCH to run only the
> select portion of the sql used to create the table and create a flatfile
> that
> can be read by the cobol program.
>
> The new process appears to work ok but seems to take longer - it seems to
> me creating a flat file should be quicker than than inserting into a table
> -
> am
> I missing something?
>
> Phil Oelkers
> Technical Consultant
> Experian Automotive Information Services
> [login to unmask email]
> > (847) 619-7267
> >
> >
> >
> >