Do you, Steve, have one of the log scrape tools - BMC LogMaster, Broadcom CA Log
Analyser, IBM Log Analysis Tool?
If so, could you set a QUIESCE point (i.e point of consistency), copy data at your leisure, run
a log scrape extract of updates, apply updates to the PBR, rename tables, rebind, and off you
go?
James Campbell
On 1 Apr 2022 at 22:28, Bruce Williamson via International DB2 Us wrote:
> Indeed it has been a long time mate ... too long
>
>
>
>
> The main differences I see in the REORG parms are MAXRO 2, RETRY 2 & RETRY_DELAY 30, we use 1, 60 & 15 at CBA, rational being that if we've gone to this amount of effort to REORG we don't want to waste the opportunity if possible ... YMMV as they say
>
> Do you have DIAGNOSE TYPE(100,101,102) coded on your REORGs, I don't believe it's a big CPU cost? Depending how your REORGs are generated best to try in non-prod first, as DB2 Auto Tool has a conniption with the additional diagnostics in the job output. This diagnostic info will allow you to see where in the LOG PHASE the delay is, doesn't mean you'll be able to do anything about it but it can be extremely useful, example below. In our case it was the LOG FORCE WRITE on non-IBM DASD and UPDATE VVDS which was killing us but the UPDATE VVDS has subsequently been moved to just prior to the DRAIN ALL of the final LOG ITERATION
>
>
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = LOG DRAIN ALL CPU (SEC) = 0.000694 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.016
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = LOG PROMOTE UT CPU (SEC) = 0.001798 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.010
>
> BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> BP0 2
> BP32K 3 1
>
> TOTAL 5 1
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = READ SYSLGRNX CPU (SEC) = 0.017630 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.018
>
> BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> BP0 944
>
> TOTAL 944
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = READ LOG RECORDS CPU (SEC) = 0.002556 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.005
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = DATALOG subtask 01 CPU (SEC) = 0.014363 ZIIP = 0.000000
> LEVEL = SUBPHASE ELAPSED TIME (SEC) = 75.133
>
> BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> BP0 9
> BP3 29 75 1
> BP0 1669 417 577
> BP8K2 243 140 61
>
> TOTAL 1950 632 638 1
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = FINAL INCREMENTAL CPU (SEC) = 0.000137 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.000
>
> BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> BP8K2 7 3 1
>
> TOTAL 7 3 1
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = LOG FORCE WRITE DW CPU (SEC) = 0.000082 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.001
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = DICT VERSION UPDATE CPU (SEC) = 0.001001 ZIIP = 0.000000
> LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.011
>
> BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> BP8K2 66 66 10
>
> TOTAL 66 66 10
>
> DDNAME DS OPEN DS CLOSE READ I/O WRITE I/O I/O CHECKS I/O WAIT END OF VOL
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> SYS00003 22 22
>
> TOTAL 22 22
>
> -----------------------------------------------------------------------------------------------------------------------
> INTERVAL = LOG CPU (SEC) = 1.210357 ZIIP = 0.000000
> LEVEL = PHASE ELAPSED TIME (SEC) = 75.222
>
> BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> BP0 10408 206 33
> BP3 56 99 8 1
> BP0 13 13
> BP32K 57 26
> BP8K2 99745 46556 6595 10 215
>
> TOTAL 110279 46681 6822 10 215 34
>
> DDNAME DS OPEN DS CLOSE READ I/O WRITE I/O I/O CHECKS I/O WAIT END OF VOL
> -------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
> SYS00003 2245 2245 0.349
> TOTAL 2245 2245 0.349
>
> How many physical datasets is your PBG? Would increasing the DSSIZE to 256 GB be an option? On the other hand, if these aren't an option then it's X-Loader or UNLOAD/LOAD. My gut feel is the U/L will be quicker if you can split the UNLOAD datasets I'm not sure if this will work but can you use multiple FROM TABLE clauses on the same table based on your partitioning key so your loads can be run in parallel?
>
> Best of luck
>
> Cheers
> Bruce
>
> ------------------------------
> Bruce Williamson
> Commonwealth Bank of Australia
> ------------------------------
> -------------------------------------------
> Original Message:
> Sent: Apr 01, 2022 11:12 AM
> From: Steven Lamb
> Subject: DB2 v12 fastest way to copy data from one table to another?
>
>
> Hi Bruce - long time :)
>
> OK, the PBG is "linked" to the BCA_PRENOTE table, so any transactions that insert into that also insert into the PBG. We're seeing delays in the final log iteration of up to 4 sec last time I checked, although I've seen 6 seconds in the past. This last weekend the final iteration took just over 3.2 seconds
> 04:20:22.73 DSNURLOG - DRAIN ALL WITH START TIME 2022-03-27-04.20.22.699787 HAS COMPLETED SUCCESSFULLY
> 04:20:25.97 FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORDS = 18
> 04:20:25.97 LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 3, NUMBER OF LOG RECORDS = 27033
> 04:20:25.97 LOG PHASE COMPLETE, ELAPSED TIME = 00:00:31
>
> Even though we do these Reorgs around 4am on a Sunday, those pesky customers still get in the way!
> The ATM transactions go via Base24 and that gives the mainframe 2 seconds to respond; if it doesn't it performs its own checks with a chance of "old" data being used to make the decision on whether the customer gets their money or not.
>
> The disks are IBM DS8888F, with DB2 v12 on z15s. Four member DSG.
> The main Reorg parms are:
> REORG TABLESPACE LIST REO11001
> SCOPE ALL
> SHRLEVEL CHANGE
> LOG NO
> SORTDATA YES
> NOSYSREC
> DRAIN_WAIT 1
> RETRY 2
> RETRY_DELAY 30
> MAXRO 2
> DRAIN ALL
> LOGRANGES YES
> DRAIN_ALLPARTS NO
> FASTSWITCH YES
> SORTNPSI NO
> SORTDEVT 3390
> PREFORMAT (we'll be removing this soon)
>
> The implicit TS is using 1 partition
> DSSIZE 64G
> SEGSIZE 32
> PGSIZE 4K
> Member Cluster, Append, space = 0,0
>
> Agghhh!
> My first attempt at an Unload was fine - until I tried to read the SYSPUNCH dataset. RACF and separation of duties says I have to use "id 1" to access the data for the Unload and Id 1 can create the SYSPUNCH datasets etc. My real TSO super-user id that I use to submit the Unload with USER=Id 1 on the jobcard can't access those datasets. I now need to find a "common" hlq that I can use :(.
>
> Steve
>
>
>
>
>
> ------------------------------
> Steven Lamb
> ------------------------------
>
> Original Message:
> Sent: Apr 01, 2022 09:50 AM
> From: Bruce Williamson
> Subject: DB2 v12 fastest way to copy data from one table to another?
>
>
> Howzit Steve?
>
> I'm curious about a few things and not necessarily the data movement ...
>
> What REORG parameters are you using and what are the critical tablespace attributes e.g. DSSIZE, SEGSIZE, PGSIZE, etc.. Furthermore what is your DASD type (vendor)? Do you have any REORG diagnostics active (DIAGNOSE TYPE 100,101,102 if memory serves) or results from said diagnostics?
>
> The reason I ask is we have some pretty substantial PBGs, I'll check when I logon in the morning for comparison but we don't have those sorts of issues anymore, I guess it all depends how active the tables is ...
>
> Cheers
> Bruce
>
>
>
>
> ------------------------------
> Bruce Williamson
> Commonwealth Bank of Australia
>
> Original Message:
> Sent: Apr 01, 2022 09:05 AM
> From: Steven Lamb
> Subject: DB2 v12 fastest way to copy data from one table to another?
>
> Friday afternoon question time...
>
> We have a table with approx. 500m rows in it, in a PBG tablespace. We want to convert this to a 120-part PBR, one reason being multi-second drain waits during SHRLEVEL CHANGE Reorgs. The table has one index, with the new PBR version being partitioned. The PBG tablespace is approx. 700,000 tracks with compression around 50%.
>
> What's the fastest way to copy the data from the original PBG to a new PBR version? Speed is required as this transformation would require an "outage" in prod.
> A straight-forward cross-load utility took about 45 mins and has no parallelism. Looking at the manuals, it appears that if we wanted to do things at the partition level, we can only operate on one part of the PBR at a time, meaning we'd need 120 cursors / Load statements, which is a bit sub-optimal really.
>
> Any thoughts?
>
> Regards,
> Steve
>
> ------------------------------
> Steven Lamb
> ------------------------------
>
Original Message:
Sent: 4/1/2022 6:29:00 PM
From: Bruce Williamson
Subject: RE: DB2 v12 fastest way to copy data from one table to another?
Indeed it has been a long time mate … too long
The main differences I see in the REORG parms are MAXRO 2, RETRY 2 & RETRY_DELAY 30, we use 1, 60 & 15 at CBA, rational being that if we've gone to this amount of effort to REORG we don't want to waste the opportunity if possible … YMMV as they say
Do you have DIAGNOSE TYPE(100,101,102) coded on your REORGs, I don't believe it's a big CPU cost? Depending how your REORGs are generated best to try in non-prod first, as DB2 Auto Tool has a conniption with the additional diagnostics in the job output. This diagnostic info will allow you to see where in the LOG PHASE the delay is, doesn't mean you'll be able to do anything about it but it can be extremely useful, example below. In our case it was the LOG FORCE WRITE on non-IBM DASD and UPDATE VVDS which was killing us but the UPDATE VVDS has subsequently been moved to just prior to the DRAIN ALL of the final LOG ITERATION
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = LOG DRAIN ALL CPU (SEC) = 0.000694 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.016
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = LOG PROMOTE UT CPU (SEC) = 0.001798 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.010
BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
BP0 2
BP32K 3 1
TOTAL 5 1
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = READ SYSLGRNX CPU (SEC) = 0.017630 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.018
BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
BP0 944
TOTAL 944
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = READ LOG RECORDS CPU (SEC) = 0.002556 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.005
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = DATALOG subtask 01 CPU (SEC) = 0.014363 ZIIP = 0.000000
LEVEL = SUBPHASE ELAPSED TIME (SEC) = 75.133
BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
BP0 9
BP3 29 75 1
BP0 1669 417 577
BP8K2 243 140 61
TOTAL 1950 632 638 1
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = FINAL INCREMENTAL CPU (SEC) = 0.000137 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.000
BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
BP8K2 7 3 1
TOTAL 7 3 1
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = LOG FORCE WRITE DW CPU (SEC) = 0.000082 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.001
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = DICT VERSION UPDATE CPU (SEC) = 0.001001 ZIIP = 0.000000
LEVEL = LOG SUBPROCESS ELAPSED TIME (SEC) = 0.011
BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
BP8K2 66 66 10
TOTAL 66 66 10
DDNAME DS OPEN DS CLOSE READ I/O WRITE I/O I/O CHECKS I/O WAIT END OF VOL
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
SYS00003 22 22
TOTAL 22 22
-----------------------------------------------------------------------------------------------------------------------
INTERVAL = LOG CPU (SEC) = 1.210357 ZIIP = 0.000000
LEVEL = PHASE ELAPSED TIME (SEC) = 75.222
BUF POOL GETPAGES SYS SETW SYNC READS SYNC WRITE SEQ PREFCH LIST PREFCH DYN PREFCH
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
BP0 10408 206 33
BP3 56 99 8 1
BP0 13 13
BP32K 57 26
BP8K2 99745 46556 6595 10 215
TOTAL 110279 46681 6822 10 215 34
DDNAME DS OPEN DS CLOSE READ I/O WRITE I/O I/O CHECKS I/O WAIT END OF VOL
-------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
SYS00003 2245 2245 0.349
TOTAL 2245 2245 0.349
How many physical datasets is your PBG? Would increasing the DSSIZE to 256 GB be an option? On the other hand, if these aren't an option then it's X-Loader or UNLOAD/LOAD. My gut feel is the U/L will be quicker if you can split the UNLOAD datasets I'm not sure if this will work but can you use multiple FROM TABLE clauses on the same table based on your partitioning key so your loads can be run in parallel?
Best of luck
Cheers
Bruce
------------------------------
Bruce Williamson
Commonwealth Bank of Australia
------------------------------
Original Message:
Sent: Apr 01, 2022 11:12 AM
From: Steven Lamb
Subject: DB2 v12 fastest way to copy data from one table to another?
Hi Bruce - long time :)
OK, the PBG is "linked" to the BCA_PRENOTE table, so any transactions that insert into that also insert into the PBG. We're seeing delays in the final log iteration of up to 4 sec last time I checked, although I've seen 6 seconds in the past. This last weekend the final iteration took just over 3.2 seconds
04:20:22.73 DSNURLOG - DRAIN ALL WITH START TIME 2022-03-27-04.20.22.699787 HAS COMPLETED SUCCESSFULLY
04:20:25.97 FINAL LOG ITERATION STATISTICS. NUMBER OF LOG RECORDS = 18
04:20:25.97 LOG PHASE STATISTICS. NUMBER OF ITERATIONS = 3, NUMBER OF LOG RECORDS = 27033
04:20:25.97 LOG PHASE COMPLETE, ELAPSED TIME = 00:00:31
Even though we do these Reorgs around 4am on a Sunday, those pesky customers still get in the way!
The ATM transactions go via Base24 and that gives the mainframe 2 seconds to respond; if it doesn't it performs its own checks with a chance of "old" data being used to make the decision on whether the customer gets their money or not.
The disks are IBM DS8888F, with DB2 v12 on z15s. Four member DSG.
The main Reorg parms are:
REORG TABLESPACE LIST REO11001
SCOPE ALL
SHRLEVEL CHANGE
LOG NO
SORTDATA YES
NOSYSREC
DRAIN_WAIT 1
RETRY 2
RETRY_DELAY 30
MAXRO 2
DRAIN ALL
LOGRANGES YES
DRAIN_ALLPARTS NO
FASTSWITCH YES
SORTNPSI NO
SORTDEVT 3390
PREFORMAT (we'll be removing this soon)
The implicit TS is using 1 partition
DSSIZE 64G
SEGSIZE 32
PGSIZE 4K
Member Cluster, Append, space = 0,0
Agghhh!
My first attempt at an Unload was fine - until I tried to read the SYSPUNCH dataset. RACF and separation of duties says I have to use "id 1" to access the data for the Unload and Id 1 can create the SYSPUNCH datasets etc. My real TSO super-user id that I use to submit the Unload with USER=Id 1 on the jobcard can't access those datasets. I now need to find a "common" hlq that I can use :(.
Steve
------------------------------
Steven Lamb
Original Message:
Sent: Apr 01, 2022 09:50 AM
From: Bruce Williamson
Subject: DB2 v12 fastest way to copy data from one table to another?
Howzit Steve?
I'm curious about a few things and not necessarily the data movement …
What REORG parameters are you using and what are the critical tablespace attributes e.g. DSSIZE, SEGSIZE, PGSIZE, etc.. Furthermore what is your DASD type (vendor)? Do you have any REORG diagnostics active (DIAGNOSE TYPE 100,101,102 if memory serves) or results from said diagnostics?
The reason I ask is we have some pretty substantial PBGs, I'll check when I logon in the morning for comparison but we don't have those sorts of issues anymore, I guess it all depends how active the tables is …
Cheers
Bruce
------------------------------
Bruce Williamson
Commonwealth Bank of Australia
Original Message:
Sent: Apr 01, 2022 09:05 AM
From: Steven Lamb
Subject: DB2 v12 fastest way to copy data from one table to another?
Friday afternoon question time...
We have a table with approx. 500m rows in it, in a PBG tablespace. We want to convert this to a 120-part PBR, one reason being multi-second drain waits during SHRLEVEL CHANGE Reorgs. The table has one index, with the new PBR version being partitioned. The PBG tablespace is approx. 700,000 tracks with compression around 50%.
What's the fastest way to copy the data from the original PBG to a new PBR version? Speed is required as this transformation would require an "outage" in prod.
A straight-forward cross-load utility took about 45 mins and has no parallelism. Looking at the manuals, it appears that if we wanted to do things at the partition level, we can only operate on one part of the PBR at a time, meaning we'd need 120 cursors / Load statements, which is a bit sub-optimal really.
Any thoughts?
Regards,
Steve
------------------------------
Steven Lamb
------------------------------