{Blank Subject Dec 22, 1999}

ajay kumar

{Blank Subject Dec 22, 1999}
Hi..

What is the limitation of mass insert..?

We are inserting 236000 records, Record length is 630. When we use DSNTIAUL
mass insert, it is idle no movement for more than 5 hrs. Is any other
systems variables to be set.

We cannot use LOAD utility. Why INSERT is using means, In source table the
data is in float we want to convert to decimal,for that we used INSERT. For
small table, it went thru but for biggrer table it is idle.

I allocated sufficient priqty and SECQTY, interms of CYCLINDER.
Can any one suggest how to avoid this problem...?

Thank
CICS Admin

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Frank D Marchese

(in response to ajay kumar)
Have you looked at the system console or JES log to see if DB2 logging is
causing trouble ?
Is there an index involved and how is it doing ?

-----Original Message-----
From: ajay kumar [mailto:[login to unmask email]
Sent: Wednesday, December 22, 1999 9:42 AM
To: [login to unmask email]
Subject:


Hi..

What is the limitation of mass insert..?

We are inserting 236000 records, Record length is 630. When we use DSNTIAUL
mass insert, it is idle no movement for more than 5 hrs. Is any other
systems variables to be set.

We cannot use LOAD utility. Why INSERT is using means, In source table the
data is in float we want to convert to decimal,for that we used INSERT. For
small table, it went thru but for biggrer table it is idle.

I allocated sufficient priqty and SECQTY, interms of CYCLINDER.
Can any one suggest how to avoid this problem...?

Thank
CICS Admin

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com








Phil Grainger

(in response to Frank D Marchese)
Reading between the lines, I assume you are doing some sort of

INSERT INTO........
..... SELECT ..... FROM

to insert your 236,000 records. This means all 236,000 inserts are being
done under the same unit of work (there can be no intervening COMMITs). So,
there is definately an issue with logging. Don't forget that all 236,000
lots of 630 bytes will have to be written out. Is it likely that DB2 has run
out of active logs and is waiting for an archive to complete? IS any other
work happening in DB2 at the same time??

Also, be very aware of what will happen if your job fails (or is cancelled)
- all the inserts up to that point will have to be undone. This will also
cause a lot of logging activity both reading and writing (the compensating
log records).

All in all, you would be better unloading the data with the select
statement, doing the data conversion and THEN using the LOAD utility!

Phil Grainger
Director DB2 Operations, Europe
Computer Associates International


> -----Original Message-----
> From: Marchese, Frank D, CSCIO [SMTP:[login to unmask email]
> Sent: Wednesday, December 22, 1999 1:02 PM
> To: [login to unmask email]
> Subject:
>
> Have you looked at the system console or JES log to see if DB2 logging is
> causing trouble ?
> Is there an index involved and how is it doing ?
>
> -----Original Message-----
> From: ajay kumar [mailto:[login to unmask email]
> Sent: Wednesday, December 22, 1999 9:42 AM
> To: [login to unmask email]
> Subject:
>
>
> Hi..
>
> What is the limitation of mass insert..?
>
> We are inserting 236000 records, Record length is 630. When we use
> DSNTIAUL
> mass insert, it is idle no movement for more than 5 hrs. Is any other
> systems variables to be set.
>
> We cannot use LOAD utility. Why INSERT is using means, In source table the
> data is in float we want to convert to decimal,for that we used INSERT.
> For
> small table, it went thru but for biggrer table it is idle.
>
> I allocated sufficient priqty and SECQTY, interms of CYCLINDER.
> Can any one suggest how to avoid this problem...?
>
> Thank
> CICS Admin
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
> the
>
>
>
>
>
>
>



Frank D Marchese

(in response to Phil Grainger)
Sounds good plus look for any bufferpool problems or any other data set
contention on JES logs, etc.

-----Original Message-----
From: Grainger, Phil [mailto:[login to unmask email]
Sent: Wednesday, December 22, 1999 8:49 AM
To: [login to unmask email]
Subject:


Reading between the lines, I assume you are doing some sort of

INSERT INTO........
..... SELECT ..... FROM

to insert your 236,000 records. This means all 236,000 inserts are being
done under the same unit of work (there can be no intervening COMMITs). So,
there is definately an issue with logging. Don't forget that all 236,000
lots of 630 bytes will have to be written out. Is it likely that DB2 has run
out of active logs and is waiting for an archive to complete? IS any other
work happening in DB2 at the same time??

Also, be very aware of what will happen if your job fails (or is cancelled)
- all the inserts up to that point will have to be undone. This will also
cause a lot of logging activity both reading and writing (the compensating
log records).

All in all, you would be better unloading the data with the select
statement, doing the data conversion and THEN using the LOAD utility!

Phil Grainger
Director DB2 Operations, Europe
Computer Associates International


> -----Original Message-----
> From: Marchese, Frank D, CSCIO [SMTP:[login to unmask email]
> Sent: Wednesday, December 22, 1999 1:02 PM
> To: [login to unmask email]
> Subject:
>
> Have you looked at the system console or JES log to see if DB2 logging is
> causing trouble ?
> Is there an index involved and how is it doing ?
>
> -----Original Message-----
> From: ajay kumar [mailto:[login to unmask email]
> Sent: Wednesday, December 22, 1999 9:42 AM
> To: [login to unmask email]
> Subject:
>
>
> Hi..
>
> What is the limitation of mass insert..?
>
> We are inserting 236000 records, Record length is 630. When we use
> DSNTIAUL
> mass insert, it is idle no movement for more than 5 hrs. Is any other
> systems variables to be set.
>
> We cannot use LOAD utility. Why INSERT is using means, In source table the
> data is in float we want to convert to decimal,for that we used INSERT.
> For
> small table, it went thru but for biggrer table it is idle.
>
> I allocated sufficient priqty and SECQTY, interms of CYCLINDER.
> Can any one suggest how to avoid this problem...?
>
> Thank
> CICS Admin
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>
>
>
> the
>
>
>
>
>
>
>