10BYTE RBA and Logging.

Ken Hynes

10BYTE RBA and Logging.

Hello,

We recently converted the BSDS and the CATALOG on several non-DataSharing DB2 subsystems to the 10BYTE RBA format. There have been concerns raised regarding recent "Spikes" related to the DB2 LOGS. We have not converted any User Tablespaces only the BSDS and the CATALOG itself. I have been reviewing the Update activity of the overall system via Bufferpool information and from what I see the DB2 CATALOG update activity is very small relative to the overall Applications file update activity with respect to potential ARCHIVE LOGGING. I do recall somewhere in the DOC that IBM cautioned about increased activity with the ARCHIVE LOGS, but I cannot locate it at the moment. Based on the relative numbers I am seeing I don't see where the CATALOG would be a major player in this - though anything is possible. Has anyone run into a similar issue with this conversion?

.....Ken Hynes

 

Chad Walmer

10BYTE RBA and Logging.
(in response to Ken Hynes)
When you convert the BSDS, the system then begins to log the full 10 byte RBA for *all* log records (this includes objects that have not been converted yet.) Prior to the BSDS conversion, DB2 was truncating the log records when they were written. I don’t remember the estimates at this point, but there were a number of IBM presentations on the percentage increase of log volume you could expect after converting the BSDS.

Chad Walmer

From: Ken Hynes [mailto:[login to unmask email]
Sent: Monday, May 11, 2020 7:21 PM
To: [login to unmask email]
Subject: [DB2-L] - 10BYTE RBA and Logging.


Hello,

We recently converted the BSDS and the CATALOG on several non-DataSharing DB2 subsystems to the 10BYTE RBA format. There have been concerns raised regarding recent "Spikes" related to the DB2 LOGS. We have not converted any User Tablespaces only the BSDS and the CATALOG itself. I have been reviewing the Update activity of the overall system via Bufferpool information and from what I see the DB2 CATALOG update activity is very small relative to the overall Applications file update activity with respect to potential ARCHIVE LOGGING. I do recall somewhere in the DOC that IBM cautioned about increased activity with the ARCHIVE LOGS, but I cannot locate it at the moment. Based on the relative numbers I am seeing I don't see where the CATALOG would be a major player in this - though anything is possible. Has anyone run into a similar issue with this conversion?

.....Ken Hynes



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

Kai Stroh

RE: 10BYTE RBA and Logging.
(in response to Ken Hynes)

Ken,

the following is not based on any official information from IBM. These are my calculations, and I could over- or underestimate things. Also I hope my math is not off. But I thought I'd share some raw number with you.

Every single log record begins with an LRH structure, no exceptions. The LRH has increased from 38 to 80 Bytes with the introduction of 10 Byte RBAs. That means every log record will use at least 42 extra Bytes.

Now, the majority of records in your average Db2 log are probably data change records that represent changes to tablespace data pages or index data pages. Each of those begins with the LRH, followed by LGDBHEAD and LGBENTRY. The total length of this prefix is:

Db2 V10: 38 + 18 + 8 = 64 Bytes
Db2 V11: 80 + 24 + 8 = 112 Bytes
Db2 V12: 80 + 36 + 8 = 132 Bytes

(V11 means Db2 V11 after the BSDS conversion.)

The log record's actual payload comes after this prefix and does not really change between the different Db2 versions.

That means that, for example, inserting a 100 byte row will result in a log record that is roughly 164 Bytes in V10, 212 Bytes in V11 (~30% increase) and 232 Bytes in V12 (another ~10% increase).

But for a 2 KB row, the numbers are 2100 Bytes, 2112 Bytes (+5%) and 2132 (+1%). So it really depends on the average row length for INSERT and DELETE statements.

For updates, when you change a single byte Db2 can log only that byte, its offset, and the "old" byte. In other words, the payload can be as small as 5 or 6 bytes and the longer prefix can more than double the length of a log record (I guess this is the worst case scenario).

There are also full page replacements that write a whooping 4K, 8K, 16K or 32K to the log, plus the header. The relative change in length is probably negligible.

Then there log records that do not represent data changes and therefore do not have LGDBHEAD or LGBENTRY. For these, I have not really looked into the details. You can find all the structures in SDSNMACS(DSNDQJ00).

Hope that helps you estimate the additional space requirements. I believe the numbers look scarier than they really are, considering that the amount of data and the number of transactions is ever increasing anyway.

Cheers
Kai

 

--
Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

Ken Hynes

RE: 10BYTE RBA and Logging.
(in response to Ken Hynes)

Thanks to both Responders Chad and Kai.

"When you convert the BSDS, the system then begins to log the full 10 byte RBA for *all* log records (this includes objects that have not been converted yet.) Prior to the BSDS conversion, DB2 was truncating the log records when they were written. I don’t remember the estimates at this point, but there were a number of IBM presentations on the percentage increase of log volume you could expect after converting the BSDS."

"Every single log record begins with an LRH structure, no exceptions. The LRH has increased from 38 to 80 Bytes with the introduction of 10 Byte
RBAs. That means every log record will use at least 42 extra Bytes."

I was aware of some of the increase, but I was not expecting "every" record to get hit. I was focused on the Catalog and Directory changes knowing the Applications files had not yet been touched and getting the BSDS restructured as a DB2V12 prerequisite. The additional impact on the LRH Kai pointed out I have yet to find in the DOC - though I'm sure he is correct. I did go back and find some additional information on the BSDS based on Chad's input with mention of increases, but it is pretty high level and a very broad range. I did expect some increase, but with the combination of smaller Active Logs on this system and the increase size of the records it was quite a bit higher than I expected. Looks like I'll have to request some DASD and plan on resizing the Active LOGS in the very near future.

Thanks.....Ken

Kai Stroh

RE: 10BYTE RBA and Logging.
(in response to Ken Hynes)

Ken,

if you're like me, you always want to see things for yourself. It's easy to do. Just run DSN1LOGP to print a handful of log records and you will notice that for the vast majority of records, the program prints the LRH and, if present, LGDBHEAD structures separately. It's not really "formatted" as it does not show what fields these structures consist of, but you get to see the raw contents and the lengths of these headers.

This is what it typically looks like in V12 (it might look misaligned, I don't know how to make it use a monospaced font):

*LRH* 000001DB 00810009 0EA00000 00000000 00000000 038EFE16 A17A0000 00000000  *
      00000000 038EFE19 70F25000 06000001 00000000 0000038E FE1970F2 000000D7  *
      E8FCE78B DE4E8000 00000000 00000000                                      *
*LG** 8C000608 A2000004 A6000000 0000038E FE0B9BDD 4C428000 00018EFE 1970F200  *
      00000000                                                                 *


And here's V10:

*LRH* 008A03BA 06000012 0E80048E 217FAEBE 048E217F B0E10726 048E217F B0E1D7E7  *
      52DAA12A 0000                                                            *
*LG** 00010400 63000000 0000048E 217D2628 7E00                                 *


You can immediately see the different lengths of the headers. In DSNDQJ00, you have separate DSECT mappings called LRH and LRH_V2, the latter being the 10-byte long format.

The LRH contains things like the total length of the log record, flags, the log record type and subtype, the log record LRSN, the URID, the RBA of the previous log record, and the RBA of the next log record that would need to be rolled back if the entire UR is aborted. That's why every log record needs to have that header.

The LRH fields that contain RBAs obviously need to store 10 bytes now. For some of them, IBM increased the length from 6 to 10, but for some they simply added a new 10 byte field and kept the old 6-byte field around - now unused and marked as "reserved".

Cheers
Kai

--

Kai Stroh
UBS Hainer
Fast, efficient Db2 z/OS data migrations and renewals. That’s BCV5.
Learn how the Test Data Management Field Guide can help you to improve your own process.

David Simpson

10BYTE RBA and Logging. [EXTERNAL]
(in response to Ken Hynes)
Another sometimes forgotten issue… if you are doing this conversion because you are near the end of the 6 byte RBA you will accelerate your pace to “the end”. You may even want to convert your application objects first depending on how close you are.

From: Ken Hynes [mailto:[login to unmask email]
Sent: Tuesday, May 12, 2020 10:02 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: 10BYTE RBA and Logging. [EXTERNAL]


Thanks to both Responders Chad and Kai.

"When you convert the BSDS, the system then begins to log the full 10 byte RBA for *all* log records (this includes objects that have not been converted yet.) Prior to the BSDS conversion, DB2 was truncating the log records when they were written. I don’t remember the estimates at this point, but there were a number of IBM presentations on the percentage increase of log volume you could expect after converting the BSDS."

"Every single log record begins with an LRH structure, no exceptions. The LRH has increased from 38 to 80 Bytes with the introduction of 10 Byte RBAs. That means every log record will use at least 42 extra Bytes."

I was aware of some of the increase, but I was not expecting "every" record to get hit. I was focused on the Catalog and Directory changes knowing the Applications files had not yet been touched and getting the BSDS restructured as a DB2V12 prerequisite. The additional impact on the LRH Kai pointed out I have yet to find in the DOC - though I'm sure he is correct. I did go back and find some additional information on the BSDS based on Chad's input with mention of increases, but it is pretty high level and a very broad range. I did expect some increase, but with the combination of smaller Active Logs on this system and the increase size of the records it was quite a bit higher than I expected. Looks like I'll have to request some DASD and plan on resizing the Active LOGS in the very near future.

Thanks.....Ken

-----End Original Message-----
________________________________
Please note: This message originated outside your organization. Please use caution when opening links or attachments.

Ken Hynes

RE: 10BYTE RBA and Logging. [EXTERNAL]
(in response to David Simpson)

David,

 Thanks, I targeted the BSDS for  V12 and the Catalog based on a warning I saw that "If" it ever ran out you might find it hard to recover/correct. I passed along the information regarding the Applications Tablespaces to the DBA Group. I also plan to confirm what the third part tools vendors plans are for their files. I need to get my own house in order first.

For now I am looking at resizing the Active LOGS to alleviate the ARCHIVING Issue. We had one set of DB2 subsystems that were marginally affected and one set that got hit pretty hard. I did find a single bullet point in one of the Migration presentations that listed the possible range of impact going from 3% to 40%. I think we got hit on both the low end and the high end...just lucky I guess.

I do not think we are all that close to the end on this client, but my focus right now is getting all the DB2 subsystems up on V12 in the next several months so one set of problems at a time, but I am aware of the concern. 

 

…..Ken Hynes

   Reply to David Simpson:

Another sometimes forgotten issue… if you are doing this conversion because you are near the end of the 6 byte RBA you will accelerate your pace to “the end”. You may even want to convert your application objects first depending on how close you are.

From: Ken Hynes [mailto:[login to unmask email]
Sent: Tuesday, May 12, 2020 10:02 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: 10BYTE RBA and Logging. [EXTERNAL]


Thanks to both Responders Chad and Kai.

"When you convert the BSDS, the system then begins to log the full 10 byte RBA for *all* log records (this includes objects that have not been converted yet.) Prior to the BSDS conversion, DB2 was truncating the log records when they were written. I don’t remember the estimates at this point, but there were a number of IBM presentations on the percentage increase of log volume you could expect after converting the BSDS."

"Every single log record begins with an LRH structure, no exceptions. The LRH has increased from 38 to 80 Bytes with the introduction of 10 Byte RBAs. That means every log record will use at least 42 extra Bytes."

I was aware of some of the increase, but I was not expecting "every" record to get hit. I was focused on the Catalog and Directory changes knowing the Applications files had not yet been touched and getting the BSDS restructured as a DB2V12 prerequisite. The additional impact on the LRH Kai pointed out I have yet to find in the DOC - though I'm sure he is correct. I did go back and find some additional information on the BSDS based on Chad's input with mention of increases, but it is pretty high level and a very broad range. I did expect some increase, but with the combination of smaller Active Logs on this system and the increase size of the records it was quite a bit higher than I expected. Looks like I'll have to request some DASD and plan on resizing the Active LOGS in the very near future.

Thanks.....Ken

-----End Original Message-----
________________________________
Please note: This message originated outside your organization. Please use caution when opening links or attachments.

Leila Hosseini

How to bind the package NULLID.SQLC2O29
(in response to Ken Hynes)



Hi

I need to Bind Package NULLID.SQLC2O29.

I am using following command in windows

By using DB2 CLP.

Bind ddcsmvs.lst Action Add blocking all grant public sqlerror continue




I thought above command will Add whatever is missing , some packages have been added ,but Not SQLC2O29 which I am looking for.

I appreciate it if some body can shed light on this matter.




Regards 

Leila

Leila Hosseini

How to bind the package NULLID.SQLC2O29
(in response to Ken Hynes)



Hi

I need to Bind Package NULLID.SQLC2O29.

I am using following command in windows

By using DB2 CLP.

Bind ddcsmvs.lst Action Add blocking all grant public sqlerror continue




I thought above command will Add whatever is missing , some packages have been added ,but Not SQLC2O29 which I am looking for.

I appreciate it if some body can shed light on this matter.




Regards 

Leila

Uriah Weber

How to bind the package NULLID.SQLC2O29
(in response to Leila Hosseini)
Leila,
Are you issuing the bind command from a v11.5 product? That’s how we obtained this NULLID package recently.

Regards,
Uriah Weber

From: Leila hosseini <[login to unmask email]>
Sent: Thursday, May 14, 2020 2:00 PM
To: [login to unmask email]; [login to unmask email]
Subject: [DB2-L] - How to bind the package NULLID.SQLC2O29

Hi
I need to Bind Package NULLID.SQLC2O29.
I am using following command in windows
By using DB2 CLP.
Bind ddcsmvs.lst Action Add blocking all grant public sqlerror continue

I thought above command will Add whatever is missing , some packages have been added ,but Not SQLC2O29 which I am looking for.
I appreciate it if some body can shed light on this matter.

Regards
Leila

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

Disclaimer

This message (and any attachments) is confidential and is intended only for the addressee(s). This message may contain information that is protected by one or more legally recognized privileges. If the reader of this message is not the intended recipient, I did not intend to waive, and I do not waive, any legal privilege or the confidentiality of the message. If you receive this message in error, please notify me immediately by return e-mail and delete this message from your computer and network without saving it in any manner. The unauthorized use, dissemination, distribution, or reproduction of this message, including attachments, is prohibited and may be unlawful.

Leila Hosseini

How to bind the package NULLID.SQLC2O29
(in response to Uriah Weber)
Hi Uriah,
Thanks for your prompt answer,It helped a lot. Issue resolved when we changed to db2 V11 from db2 v10. Then BIND ddcsmvs added package SQLC2O29.
Thanks again.
Hi Listers,Our shop is about to upgrade to DB2 12 for z/os ,and the application is heavily rely on NULLID packages ,in order to protect application we decided only Add required packages for NULLID collection , and no change(replace or rebind) for existing packages.I am wondering to know how it goes in other shops?Are people Try to replace or Add for NULLID packages or BIND its packages in NEW collection?I appreciate for any idea or comment.Regards Leila



Sent from Yahoo Mail for iPhone


On Thursday, May 14, 2020, 2:37 PM, Weber, Uriah <[login to unmask email]> wrote:

#yiv1015555024 #yiv1015555024 -- _filtered {} _filtered {}#yiv1015555024 #yiv1015555024 p.yiv1015555024MsoNormal, #yiv1015555024 li.yiv1015555024MsoNormal, #yiv1015555024 div.yiv1015555024MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;}#yiv1015555024 a:link, #yiv1015555024 span.yiv1015555024MsoHyperlink {color:blue;text-decoration:underline;}#yiv1015555024 span.yiv1015555024EmailStyle20 {font-family:sans-serif;color:windowtext;}#yiv1015555024 .yiv1015555024MsoChpDefault {font-size:10.0pt;} _filtered {}#yiv1015555024 div.yiv1015555024WordSection1 {}#yiv1015555024 #yiv1015555024 .yiv1015555024style1 {font-family:New;}
Leila,

Are you issuing the bind command from a v11.5 product?  That’s how we obtained this NULLID package recently.

 

Regards,

Uriah Weber

 

From: Leila hosseini <[login to unmask email]>
Sent: Thursday, May 14, 2020 2:00 PM
To: [login to unmask email]; [login to unmask email]
Subject: [DB2-L] - How to bind the package NULLID.SQLC2O29

 


Hi



I need to Bind Package NULLID.SQLC2O29.



I am using following command in windows



By using DB2 CLP.



Bind ddcsmvs.lst Action Add blocking all grant public sqlerror continue



 



I thought above command will Add whatever is missing , some packages have been added ,but Not SQLC2O29 which I am looking for.



I appreciate it if some body can shed light on this matter.



 



Regards 



Leila


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


Disclaimer

This message (and any attachments) is confidential and is intended only for the addressee(s). This message may contain information that is protected by one or more legally recognized privileges. If the reader of this message is not the intended recipient, I did not intend to waive, and I do not waive, any legal privilege or the confidentiality of the message. If you receive this message in error, please notify me immediately by return e-mail and delete this message from your computer and network without saving it in any manner. The unauthorized use, dissemination, distribution, or reproduction of this message, including attachments, is prohibited and may be unlawful.

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2