Db2 zos Load REPLACE PART high elapse time on build phase.

Manoj Jadwani

Db2 zos Load REPLACE PART high elapse time on build phase.

Hi All ,

At my site there is load utility running loading each partition . Yesterday it was brought to attention that these jobs are running long into the system. On investigation found this situation for one of the partition
Where the loaded records(load replace) were 0 but build phase elapse time was 42:24 minutes

18:57:27.72 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=0 FOR TABLE
E PART=11                                                                       
18:57:27.72 DSNURWT - (RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS LOADED
                                                                                
:27.72 DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=
:27.72 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:00                
18:57:27.73 DSNURBXE - BUILD PHASE STATISTICS - NUMBER OF KEYS=0 FOR INDEX XXXXX
                                                                                
19:39:52.03 DSNURBXE - BUILD PHASE STATISTICS - NUMBER OF KEYS=0 FOR INDEX
:52.03 DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=0                  
:52.03 DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:42:24   

Job performing load already has sorted records(input for load)  for specific partition .               

When I checked further , runstats data was very old December 2018 . Did the runstats to observe further and today also job ran long with similar situation (load records =0  elapse time high on build phase)

Tablespace type is partition by range.
table type is table controlled partition
Table has 2 indexes - which allows duplicates .
One of the index type is P and other type 2

Table has 90 million records. Last REORGed in 2017. Last runstats 6th Feb 2020.

Any suggestions what else I can check .
Regards,

Manoj K Jadwani

Larry Jardine

Db2 zos Load REPLACE PART high elapse time on build phase.
(in response to Manoj Jadwani)
Since it is a load replace of a partition, during the build phase, the NPIs have to be updated to remove any rows that had existed in the partition that was replaced. That may explain the elapsed time.

Larry Jardine
Database Advisor, Aetna, a CVS Health Company

[CVS]
CONFIDENTIALITY NOTICE: This communication and any attachments may contain confidential and/or privileged information for the use of the designated recipients named above. If you are not the intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify the sender immediately by email or telephone and destroy all copies of this communication and any attachments.

From: Manoj Jadwani <[login to unmask email]>
Sent: Friday, February 7, 2020 1:46 AM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - Db2 zos Load REPLACE PART high elapse time on build phase.

**** External Email - Use Caution ****

Hi All ,

At my site there is load utility running loading each partition . Yesterday it was brought to attention that these jobs are running long into the system. On investigation found this situation for one of the partition
Where the loaded records(load replace) were 0 but build phase elapse time was 42:24 minutes

18:57:27.72 DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=0 FOR TABLE
E PART=11
18:57:27.72 DSNURWT - (RE)LOAD PHASE STATISTICS - TOTAL NUMBER OF RECORDS LOADED

:27.72 DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=
:27.72 DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:00
18:57:27.73 DSNURBXE - BUILD PHASE STATISTICS - NUMBER OF KEYS=0 FOR INDEX XXXXX

19:39:52.03 DSNURBXE - BUILD PHASE STATISTICS - NUMBER OF KEYS=0 FOR INDEX
:52.03 DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=0
:52.03 DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:42:24

Job performing load already has sorted records(input for load) for specific partition .

When I checked further , runstats data was very old December 2018 . Did the runstats to observe further and today also job ran long with similar situation (load records =0 elapse time high on build phase)

Tablespace type is partition by range.
table type is table controlled partition
Table has 2 indexes - which allows duplicates .
One of the index type is P and other type 2

Table has 90 million records. Last REORGed in 2017. Last runstats 6th Feb 2020.

Any suggestions what else I can check .
Regards,

Manoj K Jadwani

-----End Original Message-----

NOTICE TO RECIPIENT OF INFORMATION:
This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately.
This e-mail may also contain protected health information (PHI) with information about sensitive medical conditions, including, but not limited to, treatment for substance use disorders, behavioral health, HIV/AIDS, or pregnancy. This type of information may be protected by various federal and/or state laws which prohibit any further disclosure without the express written consent of the person to whom it pertains or as otherwise permitted by law. Any unauthorized further disclosure may be considered a violation of federal and/or state law. A general authorization for the release of medical or other information may NOT be sufficient consent for release of this type of information.
Thank you. Aetna
Attachments

  • image003.png (3.8k)

Michael Hannan

RE: Db2 zos Load REPLACE PART high elapse time on build phase.
(in response to Larry Jardine)

In Reply to Larry Jardine:

Since it is a load replace of a partition, during the build phase, the NPIs have to be updated to remove any rows that had existed in the partition that was replaced. That may explain the elapsed time.

Manoj,

Larry has probably got it, but you might need some more tips.

Understanding LOAD performance is somewhat about having an understanding of how it works.

I searched for diagrams of LOAD Utility workings for recent releases, but found nothing much. Would be great if someone knows where the underlying function diagrams can be found.

While Reorg of individual partitions has been made smarter in recent times, I am not sure that LOAD REPLACE a partition has been also.

If you have Referential constraints where Load Replaced rows are parent, then DB2 removing rows will have to check for children unless you disable this check from the LOAD process and do it separately with Check Data.

O.K. Larry pointed out that Partition replace causes many rows to be removed, and that needs to update the NPI/NPSI index. Why is this slow? 

DB2 normally sorts the secondary index keys subject to the load, and hopefully for keys to be removed by the REPLACE as well. Then the REBUILD has an update index phase in key sequence (assuming it still works how I think) to insert and remove keys. If the secondary index is highly disorganised (bad LEAFDIST), and bufferpool coverage is not fabulous, then the index updates could find the index leaf pages to accessed are somewhat random rather than nicely in physical sequential sequence.  Then the random access sync I/O can slow the process down a lot. In the old days, we used to reorg the indexes first (particularly the secondary non partitioned here), before the LOAD PART, or REORG PART, so that index REBUILD phase would hit the entries in nice sequential sequence, and that made a huge difference way back, since Sync I/Os were very slow back then (e.g. 20 msec each).

I hope my assumption that keys to be deleted are sorted first, is correct. In the data they certainly might be very different sequence when secondary index is not well clustered.

Another interesting experiment would be: Drop the secondary index, time the LOAD REPLACE PART (should be fast if just intializes the part and partitioned index part and no R.I.), then rebuild the secondary index (with parallel processing of parts). This may not be practical in the end, but could give useful insights.

Would love it, if someone can say we now have a new better architecture for LOAD, in recent years that performs better for secondary index updates, i.e. doing index reorg under the covers, but I suspect not.

Hopefully those that market 3rd party utilities will be really expert on the deep workings of DB2 LOAD too, more than me, and can point to the weaknesses. Did I miss any?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 08, 2020 - 05:54 AM (Europe/Berlin)

Manoj Jadwani

RE: Db2 zos Load REPLACE PART high elapse time on build phase.
(in response to Michael Hannan)


Thanks Larry , Michael .

NPI associated with this table was very disorganized . As there were no reorgs in past 14 months the index datasets were running around 11 pieces 4 Gb each piece .

Index was REORG and it is under 4 GB Now.

   DB2X.DSNDBD.XX.XX.I0001.A001    87360   2018/12/07

   DB2X.DSNDBD.XX.XX.I0001.A002    87360   2018/12/19

   DB2X.DSNDBD.XX.XX.I0001.A003    87360   2019/01/25

   DB2X.DSNDBD.XX.XX.I0001.A004    87360   2019/03/19

   DB2X.DSNDBD.XX.XX.I0001.A004    87360   2019/03/19

   DB2X.DSNDBD.XX.XX.I0001.A005    87360   2019/05/01

   DB2X.DSNDBD.XX.XX.I0001.A006    87360   2019/06/21

   DB2X.DSNDBD.XX.XX.I0001.A007    87360   2019/08/15

   DB2X.DSNDBD.XX.XX.I0001.A007    87360   2019/08/15

   DB2X.DSNDBD.XX.XX.I0001.A008    87360   2019/10/03

   DB2X.DSNDBD.XX.XX.I0001.A009    87360   2019/12/04

   DB2X.DSNDBD.XX.XX.I0001.A010    10515   2020/01/29  

 

After reorg;

 

  DB2P.DSNDBD.XX.XX.J0001.A001    76140   2020/02/07


Got help from expert on the account .

But you are right NPI is culprit . Looked at few jobs today they are running under 20 minutes which were around 1 Hour earlier.

So much to learn in Db2 !!

 

Regards,

Manoj K Jadwani

Michael Hannan

RE: Db2 zos Load REPLACE PART high elapse time on build phase.
(in response to Manoj Jadwani)

Manoj,

You will find that Reorg of just the NPI by itself is enough to drastically improve your LOAD REPLACE performance. So you probably do not need to Reorg the table. It is not really about the number of pieces or extents I believe, its the poor Leaf Distribution that makes the index update too random. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 10, 2020 - 03:36 AM (Europe/Berlin)