Scalability
Business resiliency is a key component of the value proposition of DB2 for z/OS and the System z platform, supporting your efforts to keep your business running even when the workload keeps growing or you need to make changes. DB2 11 innovations drive new value in resiliency through scalability improvements and fewer outages—planned or unplanned.
The most important scalability enhancement delivers the ability to switch to a longer log RBA and LRSN value. This allows for logging capability that should cover you for the next decades even with growth at more than historic rates.
This chapter describes the following:
3.1 Extended RBA and LRSN
Since the initial version of DB2, the relative byte address (RBA) of log records has been 6 bytes. This size provides 256 TB of log record addressing capacity over the life of a DB2 non-data sharing subsystem. In DB2 environments with high logging volumes, there have been several cases where users have exhausted DB2’s logging capacity. When this happens, DB2 initially issues DSNJ032E and DSNJ033E warning messages and eventually terminates if you do not address the situation.
The following sections describe the current, basic format, RBA, and the enabling of the DB2 11, extended format RBA/LRSN:
3.1.1 Reaching the end of the basic RBA
When the end of the log RBA range is reached, you must take manual recovery actions to reset the RBA back to zero.
You also need to reset the PGLOGRBA values in every page for all objects. This change necessitates an extended outage.
For data sharing, the process is less intrusive, and involves shutting down the affected member and starting a new member to take its place. Data sharing is less intrusive because PGLOGRBA contains an log record sequence number (LRSN), which is a 6-bite value derived from the TOD clock value to be used for all members of the data sharing group. This value will not run out until the year 2042.
Data sharing customers who have disabled and re-enabled data sharing can have LRSN values “in the future” with respect to the TOD clock. This is, because during the re-enable process, DB2 records an LRSN “delta” value in the BSDS which gets added to the TOD clock value to derive the LRSN.
Another way that you end up with an LRSN value that is “in the future” is when you enable data sharing for a subsystem that has a RBA value that is numerically greater than the current TOD clock value. This might be done when a non data sharing system is approaching the end-of-range of its RBA, so enabling data sharing can yield a few extra years before the problem must be dealt with.
Therefore, some customers will run out of LRSN range well before 2042.
 
Note: The LRSN delta only occurs if the RBA of the originating member is higher than the STCK value at the moment data sharing is turned on. The determined delta is common to all members of a data sharing group. Its purpose is to ensure that an LRSN value is always larger than any of the RBAs used on any member. This delta is kept in each member’s BSDS and in the SCA.1
1 The shared communications area (SCA) is a list structure in the coupling facility.
Figure 3-1 shows an example of how an LRSN delta is calculated and applied to find the real LRSN value that is associated with the various log records. The example also shows how the LRSN of this system would progress from August 5, 2013 to April 04, 2018 due to the necessary delta.
Figure 3-1 LRSN delta explanation
 
Important: If you use a tool, such as the IBM z/OS Store Clock converter, keep in mind that you have to add two bytes x’0000’ to the LRSN value that you see.
The 6-byte LRSN provides a granularity of 16 microseconds, On faster machines, many consecutive log records can have duplicate LRSN values. DB2 9 and DB2 10 provide enhancements so that DB2 no longer has to ‘spin’ in the Log Manager to avoid duplicate LRSNs for most cases, but there are still some cases where CPU spinning is necessary. This adds considerable overhead. Extending the LRSN to use more of the TOD clock precision eliminates the need to “spin” to obtain a unique value, which improves data sharing performance.
To address these problems, the RBA and LRSN are being expanded to 10 bytes.
The LRSN is expanded allowing for over five orders of magnitude greater granularity and over 30,000 years before the end of range is encountered. The 10 byte RBA value should be large enough that it will take many decades to exhaust even if hardware speeds continue to increase at their historic rates.
3.1.2 The new 10 byte RBA and LRSN
RBA and LRSN values have both been extended to 10 bytes from the previous six byte size. For an RBA, a six byte value is converted to a ten byte value by adding zeroes to the four most significant bytes, to the left side of the value.
The new RBA limits
For an LRSN, the existing value has one zero byte added to the left side and three bytes added to the right side. The three bytes on the right side can be zero or x'FF', depending on the usage. In particular, zero padding applies to new log records that are generated, and x'FF' padding is used for existing LRSN records that are generated. Figure 3-2 visualizes this example.
Figure 3-2 10-byte RBA/LRSN formats
Even if you do nothing with regards to larger RBAs and LRSNs, when starting DB2 11 in conversion mode (CM), these longer records are displayed everywhere in DB2.
As shown in Example 3-1, which lists the output of a -DIS GROUP command, subsystem DB0B is currently running in DB2 11 CM.
Example 3-1 Output of DISPLAY GROUP command
DSN7100I -DB0B DSN7GCMD
*** BEGIN DISPLAY OF GROUP(........) CATALOG LEVEL(111) MODE(CM )
PROTOCOL LEVEL(2) GROUP ATTACH NAME(....)
--------------------------------------------------------------------
DB2 DB2 SYSTEM IRLM
MEMBER ID SUBSYS CMDPREF STATUS LVL NAME SUBSYS IRLMPROC
-------- --- ---- -------- -------- --- -------- ---- --------
........ 0 DB0B -DB0B ACTIVE 111 SC63 ID0B DB0BIRLM
--------------------------------------------------------------------
SPT01 INLINE LENGTH: 32138
*** END DISPLAY OF GROUP(........)
DSN9022I -DB0B DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
***
All messages, log records, and so on are already handling the RBA in the 10 byte, the extended format.
Example 3-2 shows the longer RBA values in the messages of the starting MSTR address space.
Example 3-2 Ten byte RBA in MSTR in CM
. . . . . . . . . . . . . . . . . . . . . . . . . . .
Display Filter View Print Options Search Help
-------------------------------------------------------------------------------
SDSF OUTPUT DISPLAY DB0BMSTR STC03760 DSID 2 LINE 26 COLUMNS 21- 100
COMMAND INPUT ===> SCROLL ===> CSR
DSNY001I -DB0B SUBSYSTEM STARTING
DSNJ127I -DB0B SYSTEM TIMESTAMP FOR BSDS= 13.190 06:32:16.83
DSNJ001I -DB0B DSNJW007 CURRENT COPY 1 ACTIVE LOG 425
DATA SET IS DSNAME=DB0BL.LOGCOPY1.DS01,
STARTRBA=0000000000002C4C0000,ENDRBA=0000000000002E67FFFF
DSNJ001I -DB0B DSNJW007 CURRENT COPY 2 ACTIVE LOG 426
DATA SET IS DSNAME=DB0BL.LOGCOPY2.DS01,
STARTRBA=0000000000002C4C0000,ENDRBA=0000000000002E67FFFF
DSNJ099I -DB0B LOG RECORDING TO COMMENCE WITH 427
STARTRBA=0000000000002C539000
S DB0BDBM1
S DB0BDIST
DSNR001I -DB0B RESTART INITIATED
DSNR003I -DB0B RESTART...PRIOR CHECKPOINT RBA=0000000000002C5339EE
DSNR004I -DB0B RESTART...UR STATUS COUNTS 442
IN COMMIT=0, INDOUBT=0, INFLIGHT=0, IN ABORT=0, POSTPONED ABORT=0
DSNR005I -DB0B RESTART...COUNTS AFTER FORWARD 443
F1=HELP F2=SPLIT F3=END F4=RETURN F5=IFIND F6=BOOK
F7=UP F8=DOWN F9=SWAP F10=LEFT F11=RIGHT F12=RETRIEVE
.....
Example 3-3 shows an extract of the archive log. The RBA and LRSN hexadecimal values appear in the expanded format.
Example 3-3 Log record in CM
DSN1LPRT UR CONNID=DB0B CORRID=021.OPNLGR00 AUTHID=SYSOPR PLAN=SYSTEM
START DATE=00.162 TIME=21:40:02 DISP=COMMITTED INFO=COMPLETE
STARTRBA=0000000000003DB1827A ENDRBA=0000000000003DB18658
STARTLRSN=00CBC49885F1B4000000 ENDLRSN=00CBC49885F1D6000000
NID=* LUWID=USIBMSC.SCPDB0B.CBC49885F1A9.0001
COORDINATOR=* PARTICIPANTS=*
DATA MODIFIED:
DATABASE=0001=DSNDB01 PAGE SET=00CF=SYSLGRNX
DATABASE=0001=DSNDB01 PAGE SET=0087=DSNLLX01
DATABASE=0001=DSNDB01 PAGE SET=0086=DSNLLX02
You see all these extended format RBAs and LRSNs but you are not yet actually using its functionality.
The new page format for larger RBA and LRSN
Because the RBA for non-data sharing or the LRSN for data sharing that correspond to the last logged update to a page (table or index page) are stored in a fixed six byte location in each page (PGLOGRBA), the format of the pages needs to change to accommodate the new, larger value. This format is supported in New Function Mode (NFM), ENFM*, and CM*, that is, any mode that does not support coexistence with or fall back to an earlier release.
The terms basic format and extended format refer to objects that are in the 6 byte and 10 byte PGLOGRBA formats, respectively. An object can be converted to and from EXTENDED format by using the REORG, REBUILD, or LOAD REPLACE utilities.
Refer to Example 3-4 for the changed layout of the header page.
Example 3-4 DSN1PRNT of a header page in extended format
PAGE: # 00000000 ------------------------------------------------------------------------------------------------------
HEADER PAGE: PGCOMB='10'X PGBIGRBA='0000000000014AC0F7C7'X PGNUM='00000000'X PGFLAGS='38'X
HPGOBID='01340005'X HPGHPREF='00000002'X HPGCATRL='00'X HPGREL='D7'X HPGZLD='00'X
HPGCATV='00'X HPGTORBA='000000000000'X HPGTSTMP='20130725163247762845'X
HPGSSNM='DB1A' HPGFOID='0004'X HPGPGSZ='1000'X HPGSGSZ='0004'X HPGPARTN='0000'X
HPGZ3PNO='000000'X HPGZNUMP='00'X HPGTBLC='0001'X HPGROID='0006'X
HPGZ4PNO='00000000'X HPGMAXL='0059'X HPGNUMCO='000E'X HPGFLAGS='0008'X
HPGFLAGS2='00'X HPGFLAGS3='80'X HPGCONTM='20130807162852039670'X
HPGSGNAM='SYSDEFLT' HPGVCATN='DB1AD ' HPGRBRBA='000000000000'X
HPGLEVEL='000000000000'X HPGPLEVL='000000000000'X HPGCLRSN='000000000000'X
HPGSCCSI='0025'X HPGDCCSI='0000'X HPGMCCSI='0000'X HPGDSSZ='00200000'X
HPGFLAG2='00'X HPGEPOCH='0000'X HPGRBLP='000000000000'X HPGDNUMB='1F'X
HPGDNUMC='0007'X HPGDFSG='00000000'X HPGDLSG='00000000'X HPGSISP='00000000'X
HPGBIGTORBA='00000000000000000000'X HPGBIGRBRBA='0000000000014ABD9979'X
HPGBIGLEVEL='00000000000000000000'X HPGBIGPLEVL='00000000000000000000'X
HPGBIGCLRSN='0000000000014AC0F7C7'X HPGBIGRBLP='00000000000000000000'X FOEND='52'X
For your convenience, Example 3-5 also shows the header page of a table space that is still in basic format.
Notice the difference highlighted in blue in both outputs. The extended format header page has seven new fields at the bottom of the page, which all contain the three letters BIG in the field names. In addition, PGLOGRBA is changed to PGBIGRBA, which can now hold the 10 byte RBA.
Also notice that the FOEND field at the end of the page contains a character if the page set is still stored and worked within basic format. If it is converted to extended format, it contains a hexadecimal value.
Example 3-5 DSN1PRNT of a header page in basic format
PAGE: # 00000000 ----------------------------------------------------------------------------------
HEADER PAGE: PGCOMB='00'X PGLOGRBA='0000044F9766'X PGNUM='00000000'X PGFLAGS='38'X
HPGOBID='01120002'X HPGHPREF='0000001A'X HPGCATRL='00'X HPGREL='D6'X HPGZLD='00'X
HPGCATV='00'X HPGTORBA='000000000000'X HPGTSTMP='20110428113926841184'X
HPGSSNM='DB0B' HPGFOID='0001'X HPGPGSZ='1000'X HPGSGSZ='0004'X HPGPARTN='0000'X
HPGZ3PNO='000000'X HPGZNUMP='00'X HPGTBLC='0002'X HPGROID='0000'X
HPGZ4PNO='00000000'X HPGMAXL='0000'X HPGNUMCO='0000'X HPGFLAGS='0008'X
HPGFLAGS2='00'X HPGFLAGS3='00'X HPGCONTM='20110428114327048498'X
HPGSGNAM='DSN8G100' HPGVCATN='DB0BD ' HPGRBRBA='0000044FBDE4'X
HPGLEVEL='0000044FBDE4'X HPGPLEVL='000000000000'X HPGCLRSN='0000044F9766'X
HPGSCCSI='0025'X HPGDCCSI='0000'X HPGMCCSI='0000'X HPGDSSZ='00200000'X
HPGFLAG2='00'X HPGEPOCH='0000'X HPGRBLP='000000000000'X HPGDNUMB='1F'X
HPGDNUMC='0007'X HPGDFSG='00000000'X HPGDLSG='00000000'X HPGSISP='00000000'X
FOEND='E'
Other types of pages, such as data or dictionary pages, are different but have the same BIG fields. You can search for PGBIG or simply check out the FOEND information. If FOEND appears in hexadecimal, you are currently looking at a page set that has been converted to EXTENDED format.
3.1.3 Considerations before converting to extended format
Partitioned tables and indexes can be converted one partition at a time. You do not need to convert all partitions at the same time, except for PBG hashed table spaces that require the entire table to be REORGed when being converted. XML tables with 8 byte version IDs must also have all partitions converted at the same time. Also, CLONE tables cannot be converted until the clone is dropped. The SYSINDEXPART and SYSTABLEPART catalog tables include an indicator of what format the partition is expected to be in.
This value is not guaranteed to be accurate because DSN1COPY can cause the format to change without updating the catalog. Therefore, this value is for informational purposes only, such for determining what objects might or might not have been converted to the new format. You can use the REPAIR utility to correct the format column, if it is incorrect. Utilities that change the format of the object also update the catalog columns.
The soft and hard limit
There are two logging limits that impact SQL and utility processing. These log limits are expressed as RBAs in non-data sharing and as LRSNs time-derived values in data sharing. For data sharing, the RBA value triggers warning messages from the log manager, but it does not affect utilities behavior because the LRSN value is used instead of the RBA value in database objects.
Warning messages already existed in DB2 10 for a subsystem that is approaching the end of its RBA range. When the subsystem reaches the critical threshold, the subsystem is terminated and can be restarted only in LIGHT mode or with ACCESS(MAINT). The same applies when approaching the end of the LRSN range.
A warning message is issued starting approximately one year before the LRSN range will be exhausted and is reissued for every log switch. At about two months before the end of the LRSN range, the soft limit processing begins (as described in the next section). At about two weeks before the end of the LRSN range, the subsystem terminates and can be started only in LIGHT mode or with ACCESS(MAINT) until the BSDS is converted. If the BSDS is not converted to the new format and if the LRSN has exceeded the 6 byte maximum, the subsystem is not allowed to restart until the BSDS is converted.
The soft limit
This limit occurs at RBA 'FFF800000000'x or at an LRSN approximately two months before the 6 byte capacity is exhausted. This advisory limit, also known as the soft limit, marks the beginning of new utility behaviors for any utility that updates a BASIC format object. The new behaviors at this limit are as follows:
•Objects in basic 6 byte format are available for read-only access. Attempts to update these objects are rejected. If you need to update table spaces and indexes that have reached the soft limit, you need to convert them to extended 10 byte format.
•As a general rule, with exceptions noted in the final item in this list, utilities that attempt to log an update of a BASIC page set fail with a DSNT500-style resource unavailable error and the '00C2026D'x reason code.
•The following catalog table spaces, and their indexes, are heavily used by utilities. If these table spaces are in BASIC format at the soft limit, utilities are significantly impacted and in many cases unusable.
 – DSNDB01.SYSUTILX
 – DSNDB06.SYSTSCPY
 – DSNDB01.SYSLGRNX
 
Important: When your DB2 subsystem hits the soft limit and the DSNDB06.SYSTSCPY table space is still in BASIC format, you cannot run the RECOVER utility for any table space any longer. The RECOVER command always records its execution in SYSIBM.SYSCOPY, which is stored in this table space.
•If you cannot use a utility while in this phase but if this utility did not change the format of the page set from BASIC to EXTENDED (that is, left it in BASIC format), the page set is available only for read-only access.
•The following utilities are not affected by the restrictions that are put in place after the soft limit is reached:
 – Utilities that do not have a target object, such as STOSPACE
 – Utilities that do not update the target object, such as REPORT
 – Utilities that are in the process of converting page sets to the EXTENDED format
 – Utilities that are invoked against EXTENDED format page sets
 – Utilities that open the output object as non-recoverable
The hard limit
The actual logging limit occurs when the RBA or LRSN no longer fits in 6 bytes. At this time, the soft limit restrictions remain in place. In addition, you must convert the BSDS to start DB2. If BSDS is not converted, the attempt to start DB2 fails. As a consequence, you cannot use any online utilities.
New subsystem parameters for extended RBA
The following subsystem parameters are related to the extended RBAs and LRSNs when dealing with DB2 objects:
OBJECT_CREATE_FORMAT
The OBJECT_CREATE_FORMAT subsystem parameter specifies whether DB2 creates new table spaces and indexes to use a basic or extended log record format.
The acceptable values are BASIC and EXTENDED. The default value depends on whether a subsystem is newly installed in DB2 11 or migrated from DB2 10. The default value is EXTENDED for the newly installed DB2 and BASIC for migration.
•BASIC
New table spaces and indexes are created with a maximum of 256 TB of log record addressing capacity over the life of a DB2 subsystem, or a maximum log record sequence number (LRSN) of 2**48 over the life of a DB2 data-sharing group. Use BASIC if you intend to use this instance of DB2 to copy or recover data from an instance of DB2 that does not support the EXTENDED format. However, after the 6 byte logging limit is exceeded, all new objects are created in EXTENDED format.
•EXTENDED
New table spaces and indexes are created with a maximum of 1 YB (yottabyte1) of log record addressing capacity over the life of a DB2 subsystem or a maximum LRSN of 2**80 over the life of the DB2 data-sharing group. This setting is the default. Use this setting in either of the following situations:
 – When DB2 is likely to exhaust the basic log format. This setting is required to update database objects if the DB2 log RBA exceeds 2**48 for non-data-sharing environments or if the LRSN exceeds x'FFFFFFFFFFFF' for data-sharing environments.
 – In data-sharing environments only, when duplicate LRSN values occur because the processing speed exceeds the precision of the traditional log addressing format.
 
Important: In conversion mode, basic 6 byte format is used regardless of the setting of this parameter.
There is no way of overriding this setting with any keyword in the DDL syntax.
 
Important: The setting for this system parameter is ignored after you hit the hard limit. Objects are always created in EXTENDED format in this situation.
UTILITY_OBJECT_CONVERSION
The value of the UTILITY_OBJECT_CONVERSION parameter specifies whether DB2 utilities that accept the RBALRSN_CONVERSION option convert existing table spaces and indexes to 6 byte page format, to a 10 byte page format, or perform no conversion.
 
Note: The RBALRSN_CONVERSION keyword is available for the following utility control statements:
•REORG TABLESPACE
•REORG INDEX
•REBUILD INDEX
•LOAD
When you specify this keyword in the utility control statement, it generally overrides the current setting of the subsystem parameter unless set to NOBASIC (see Table 3-1 on page 37).
The following values are acceptable for this parameter:
•BASIC
Existing table spaces and indexes that use extended 10-byte page format are converted to basic 6-byte page format. The BASIC option is allowed only if the OBJECT CREATE FORMAT field is also set to BASIC.
•EXTENDED
Existing table spaces and indexes that use 6-byte page format are converted to extended 10-byte page format. The EXTENDED option is allowed only if the OBJECT CREATE FORMAT field is also set to EXTENDED.
•NOBASIC
Existing table spaces and indexes that use 6-byte page format are converted to extended 10-byte page format. Table spaces and indexes that already use extended 10-byte page format cannot be returned to the 6-byte page format. When this setting is in effect, utilities that specify the RBALRSN_CONVERSION keyword with BASIC fail. In addition, utilities that specify RBALRSN_CONVERSION keyword with NONE when the object is in 6-byte page format fail. The NOBASIC value is allowed in this field only if the OBJECT CREATE FORMAT field is set to EXTENDED.
 
Important: If you use the NOBASIC value, you prevent the user from overriding the system parameter setting with the utility control statement.
•NONE (default)
No conversion is performed. This option is the default setting of this parameter. The NONE option is allowed regardless of the OBJECT CREATE FORMAT setting.
Conversion from six to 10 byte RBA and LRSN and vice versa is only possible in NFM.
3.1.4 Steps for enabling the extended RBA/LRSN format
 
Important: After migrating to NFM, check the RBA situation and decide if migration to extended RBS/LRSN is needed.
To fully enable the extended RBA/LRSN format, you need complete the following important tasks in NFM for DB2 11. Although you should run the BSDS conversion first, to improve performance, you can complete the other tasks in any order.
•Convert the BSDS records to support EXTENDED format (install job DSNTIJCB), as described 3.1.5, “Converting the BSDS” on page 32.
This conversion causes outage for non-data-sharing subsystems. You need to plan for stopping DB2 and running the DSNTIJCB. It is a fast execution.
In data sharing, you can convert a member at the time and avoid outage.
•Convert the DB2 catalog and directory table spaces and indexes to extended format, as described in 3.1.6, “Converting DB2 catalog and directory” on page 35, and then install the DSNTIJCV job.
This task is mainly a REORG SHRLEVEL CHANGE, because it causes no outage. Furthermore, you can break the DSNTIJCV job logically by object and execute it a bit at the time if necessary.
You also need to decide which DSNZPARM option to use. See “New subsystem parameters for extended RBA” on page 30. In addition, keep an eye on the extended RBA disk space increase.
3.1.5 Converting the BSDS
The first step towards being able to use the extended RBA/LRSN format is to run the DSNJCNVT stand-alone utility for BSDS conversion. Running DSNJCNVT is optional and can be done any time after migrating to DB2 11 New Function Mode (NFM) if you are not approaching the end of the 6 byte range. The conversion is required during the DB2 installation if the RBA or LRSN is approaching the end of the 6 byte range.
For a data-sharing installation, if the LRSN is approaching the end of the 6 byte range, the BSDS of each member can be converted one at a time. If the RBA or LRSN are approaching the end of the 6 byte range, you need to convert the database objects also. They become read-only when the end of the range is reached.
Considerations for running DSNJCNVT
Keep in mind the following considerations when running the DSNJCNVT stand-alone utility:
•You must stop the DB2 subsystem that owns the BSDSs that are to be converted. DSNJCNVT is a stand-alone utility.
•In a data-sharing environment, allow DB2 utilities that read the logs of peer members to finish before converting the BSDSs.
•In a data-sharing environment, stop data replication products before the conversion to ensure that the old BSDSs can be successfully renamed and replaced by the converted BSDSs. The preferred procedure is to stop the replication product first and then stop the DB2 system that is to have its BSDSs converted. This procedure allows sharing systems to deallocate the BSDSs when the state of the member changes to inactive.
•The RACF user ID that is running DSNJCNVT must have read/write access to the new BSDSs and read access to the old BSDSs.
•The DB2 subsystem that owns the BSDS that is to be converted must start after the data sharing group was migrated to DB2 11 NFM.
•Conversion to the new BSDS format is required to write new format log records and remove the 6 byte RBA and LRSN limits.
Sample DSNJCNVT JCL
The statements in Example 3-6 specify that DSNJCNVT stand-alone utility is to convert the BSDS to support 10 byte RBA and LRSN fields.
Example 3-6 DSNJCNVT control statement
//CONVERT EXEC PGM=DSNJCNVT,REGION=64M
//SYSUT1 DD DSN=DB2A.OLD.BSDS01,DISP=SHR
//SYSUT2 DD DSN=DB2A.OLD.BSDS02,DISP=SHR
//SYSUT3 DD DSN=DB2A.BSDS01,DISP=OLD
//SYSUT4 DD DSN=DB2A.BSDS02,DISP=OLD
//SYSPRINT DD SYSOUT=*
DSNJU004 to check if conversion has run
You can use the Print Log Map (DSNJU004) stand-alone utility to check if the conversion for a specific subsystem or member was performed. See the JCL shown in Example 3-7.
Example 3-7 DSNJU004 JCL
//DSNTLOG EXEC PGM=DSNJU004
//STEPLIB DD DISP=SHR,DSN=your.SDSNEXIT
// DD DISP=SHR,DSN=your.SDSNLOAD
//SYSUT1 DD DISP=SHR,DSN=your.BSDS01
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN DD *
//*
The resulting messages are shown in Example 3-8. The line marked in bold in this example indicates that for the DB2 subsystem the conversion has not been run yet.
Example 3-8 DSNJU004 output showing if DSNJCNVT has run
DSNJCNVB CONVERSION PROGRAM HAS RUN DDNAME=SYSUT1
DSNJCNVT CONVERSION PROGRAM HAS NOT RUN DDNAME=SYSUT1
LOG MAP OF BSDS DATA SET COPY 1, DSN=DB0BB.BSDS01
LTIME INDICATES LOCAL TIME, ALL OTHER TIMES ARE GMT.
DATA SHARING MODE IS OFF
SYSTEM TIMESTAMP - DATE=2013.217 LTIME=12:27:14.79
UTILITY TIMESTAMP - DATE=2013.178 LTIME= 8:13:30.76
VSAM CATALOG NAME=DB0BD
HIGHEST RBA WRITTEN 0000000000003DB21472 2013.217
HIGHEST RBA OFFLOADED 0000000000003DB21FFF
RBA WHEN CONVERTED TO V4 00000000000000000000
 
 
Important: if you want the new BSDS format, you need to convert to it in NFM regardless of whether you installed or migrated to DB2 11.
 
Note: Looking at the DSNJU004 output, RBAs and LRSNs already are shown in a 10 byte format. The DSNJU004 stand-alone utility, as most DB2 externalization functions, uses the extended format, but DB2 still stores the RBAs and LRSNs as 6 byte values until the conversion is completed. The conversion changes the CI size for existing BSDSs from 4 KB to 8 KB so that it can accommodate the larger record sizes.
Install job DSNTIJCB
The DSNTIJCB new installation job applies the needed changes to the BSDS. It includes the following job steps:
DSNTDEF Activates IDCAMS to define new BSDS data sets with CLUSTER, DATA, and INDEX components under the temporary names prefix.BSDS01.NEW and prefix.BSDS02.NEW.
DSNTCNVT Activates DSNJCNVT to read records from the existing BSDS, converts them to support the extended RBA and LRSN format, and writes them into the prefix.BSDS01.NEW and prefix.BSDS02.NEW data sets.
DSNTRENO Renames the existing BSDS data sets to backup names, prefix.BSDS01.OLDFMT and prefix.BSDS02.OLDFMT.
DSNTRENN Renames prefix.BSDS01.NEW and prefix.BSDS02.NEW to prefix.BSDS01 and prefix.BSDS02.
The job completes with return code zero. The following message displays in the job log:
IDC0002I IDCAMS PROCESSING COMPLETE. MAXIMUM CONDITION CODE WAS 0
DSNJ200I DSNJCNVT CONVERT UTILITY PROCESSING COMPLETED SUCCESSFULLY FOR MEMBER DB1A
After the BSDS is converted, the physical log records are written in the new format. A larger log record header (LRH) is required for the larger RBA and LRSN values, and there are changes to the format of the log data sets as well (collectively called new format logs). Members of a data sharing group can be converted one at a time and can run with a mix of converted and not converted members is supported.
 
Tips: The conversion only takes a few seconds. In addition, if the conversion fails, you can restart DB2 with the old BSDS while you resolve the problem of the failure. You can test the conversion with a copy of the BSDS before you really convert the actual BSDS.
3.1.6 Converting DB2 catalog and directory
Because catalog and directory are composed of table and index spaces, you have to change the RBA/LRSN formats for those also. You need to distinguish between subsystems, which are newly installed in DB2 11, and those that are migrated from DB2 10.
New installations
For new installations all catalog and directory table spaces and indexes are always created in the extended object format. The setting of OBJECT_CREATE_FORMAT system parameter is not honored for catalog and directory objects. As a result, if you query the catalog of a newly installed subsystem, the RBA_FORMAT column of SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART contains value ‘E’ for all catalog and directory objects.
Migrated subsystems
The tables that are newly created in CM are as follows:
NAME TSNAME
---------+---------+---------+
SYSOBD_AUX SYSTSOBX
SYSQUERYPREDICATE SYSTSQRE
SYSQUERYSEL SYSTSQRS
SYSVARIABLES_DESC SYSTSVAD
SYSVARIABLEAUTH SYSTSVAU
SYSINDEXCLEANUP SYSTSIXC
SYSSTATFEEDBACK SYSTSSFB
SYSVARIABLES SYSTSVAR
SYSVARIABLES_TEXT SYSTSVAT
All the table spaces holding these tables are created using the BASIC format. You can verify this RBA_FORMAT browsing column in SYSIBM.SYSTABLEPART. The associated indexes are in basic format as well, because the EXTENDED format for page sets and for the BSDS is available only in NFM.
DSNTIJCV
DB2 11 introduces the DSNTIJCV new installation job for catalog conversion, which is customized during the ENFM installation/migration job generation.
You can use the DSNTIJCV installation job to convert the DB2 catalog and directory table spaces and their indexes to extended RBA/LRSN format. You need to be in NFM when you run this job. Do not rearrange the processing sequence and the table space grouping in this job. The COPY and REORG steps use LISTDEF filtering to exclude table spaces that are already in extended format, because they have been converted previously.
The main objective of the DSNTIJCV job is to migrate the catalog and directory to EXTENDED format. However, you can also it to change the catalog and directory back from EXTENDED format to BASIC format.
The following list shows a detailed description of the job steps as described in the job prolog:
JCVTRM00 STEP TERMINATE PENDING UTILITIES FOR THIS JOB
JCVCVT01 STEP CONVERT SYSUTILX DIRECTORY TABLE SPACE
JCVCPY01 STEP IMAGE COPY THE CONVERTED SYSUTILX TABLE SPACE
JCVCPY02 STEP IMAGE COPY THE SYSLGRNX TABLE SPACE
JCVCVT02 STEP CONVERT THE FORMAT OF THE SYSLGRNX TABLE SPACE
JCVCPY03 STEP COPY OTHER DIRECTORY TABLE SPACES TO BE CONVERTED
JCVCVT03 STEP CONVERT THE OTHER DIRECTORY TABLE SPACES
JCVCPY04 STEP COPY THE DIRECTORY LOB TABLE SPACES
JCVCVT04 STEP CONVERT THE DIRECTORY LOB TABLE SPACES
JCVCPY05 STEP COPY THE SYSTSCPY CATALOG TABLE SPACE
JCVCVT05 STEP CONVERT THE SYSTSCPY CATALOG TABLE SPACE
JCVCPY06 STEP COPY OTHER CATALOG TABLE SPACES TO BE CONVERTED (PART 1)
JCVCVT06 STEP CONVERT OTHER CATALOG TABLE SPACES (PART 1)
JCVCPY07 STEP COPY RELATED CATALOG LOB TABLE SPACES (PART 1)
JCVCVT07 STEP CONVERT RELATED CATALOG LOB TABLE SPACES (PART 1)
JCVCPY08 STEP COPY OTHER CATALOG TABLE SPACES TO BE CONVERTED (PART 2)
JCVCVT08 STEP CONVERT OTHER CATALOG TABLE SPACES (PART 2)
JCVCPY09 STEP COPY RELATED CATALOG LOB TABLE SPACES (PART 2)
JCVCVT09 STEP CONVERT RELATED CATALOG LOB TABLE SPACES (PART 2)
JCVCPY10 STEP COPY OTHER CATALOG TABLE SPACES TO BE CONVERTED (PART 3)
JCVCVT10 STEP CONVERT OTHER CATALOG TABLE SPACES (PART 3)
JCVCPY11 STEP COPY RELATED CATALOG LOB TABLE SPACES (PART 3)
JCVCVT11 STEP CONVERT RELATED CATALOG LOB TABLE SPACES (PART 3)
JCVCPY12 STEP COPY OTHER CATALOG TABLE SPACES TO BE CONVERTED (PART 4)
JCVCVT12 STEP CONVERT OTHER CATALOG TABLE SPACES (PART 4)
JCVCPY13 STEP COPY RELATED CATALOG LOB TABLE SPACES (PART 4)
JCVCVT13 STEP CONVERT RELATED CATALOG LOB TABLE SPACES (PART 4)
 
Important: The catalog and directory are special DB2 objects. Carefully watch your subsystems RBAs and LRSNs. Definitely make sure that you do not hit the hard limit with the catalog and directory not being migrated to the EXTENDED format. The ability of utilities to convert, especially the SYSUTILX, SYSTSCPY, and SYSLGRNX utilities to the EXTENDED format after you hit the hard limit, is more restricted than for any other page set. It might occur that you cannot convert these objects using regular utilities, and you might be forced to follow a manual procedure to reset RBA and LRSN for your subsystem.
 
Tip: Make sure you closely watch for messages about potential soft limit hits for your DB2 subsystem. Consider a timely conversion of your subsystem’s BSDSs before you run into major problems and convert the catalog directory early enough to avoid major problems with hitting the hard limit.
3.1.7 Converting data from 6 byte to 10 byte RBA/LRSN or vice versa
The most obvious way to convert the RBA/LRSN format of an existing page set from 6 bytes to 10 bytes are the REORG TABLESPACE and REORG INDEX utilities.
You can convert a partitioned table space index one part at a time with the following exceptions:
•PBG table spaces that are organized by hash must be converted at table space level, that is all parts in one REORG job.
•Tables with 8 byte XML version IDs must also be converted at the table level.
•Tables that are in an active clone relationship cannot be converted. You must drop the clone first.
The REORG INDEX and REORG TABLESPACE utilities
The REORG TABLESPACE and REORG INDEX utilities in SHRLEVEL REFERENCE or NONE have three new utility control statements that influence if and how an RBA/LRSN conversion is performed during the utility execution.
The UTILITY_OBJECT_CONVERSION DSNZPARM setting is used when the utility control statement does not specify the RBALRSN_CONVERSION keyword with the following options:
BASIC Existing table spaces and indexes that use extended 10 byte page format are converted to basic 6 byte page format. The BASIC option is allowed only if the OBJECT CREATE FORMAT field is also set to BASIC.
EXTENDED Existing table spaces and indexes that use 6 byte page format are converted to extended 10 byte page format. The EXTENDED option is allowed only if the OBJECT CREATE FORMAT field is also set to EXTENDED.
NOBASIC Existing table spaces and indexes that use 6 byte page format are converted to extended 10 byte page format. Table spaces and indexes that already use extended 10 byte page format cannot be returned to the 6 byte page format. When this setting is in effect, utilities that specify the RBALRSN_CONVERSION keyword with the BASIC option fail. In addition, utilities that specify the RBALRSN_CONVERSION keyword with NONE when the object is in 6 byte page format fail. The NOBASIC option is allowed in this field only if the OBJECT CREATE FORMAT field is set to EXTENDED. If a value is not specified for RBALRSN_CONVERSION, the RBALRSN_CONVERSION value defaults to EXTENDED.
NONE No conversion is performed. This option is the default setting of this parameter. The NONE option is allowed regardless of the OBJECT CREATE FORMAT setting.
Table 3-1 shows what type of conversion is performed and the error messages, depending on the system parameter setting and the usage of the RBALRSN_CONVERSION keyword in the utility control statement. For REORG TABLESPACE, all the results listed in the four NFM columns also apply to the conversion of all indexes that are defined on the tables in the table space that is being reorganized.
Table 3-1 UTILITY_OBJECT_CONVERSION
Utility option
UTILITY_OBJECT_CONVERSION
 
NONE
BASIC
EXTENDED
NOBASIC
CM
NFM
CM
NFM
CM
NFM
CM
NFM
NONE
DSNU169I1 DSNU123I2
NONE
DSNU169I DSNU123I
NONE
DSNU169I DSNU123I
NONE
DSNU169I DSNU123I
fails if object is BASIC
BASIC
DSNU169I DSNU123I
BASIC
DSNU169I DSNU123I
BASIC
DSNU169I DSNU123I
BASIC
DSNU169I DSNU123
fails
EXTENDED
DSNU169I DSNU123I
EXTENDED
DSNU169I DSNU123I
EXTENDED
DSNU169I DSNU123I
EXTENDED
DSNU169I DSNU123I
EXTENDED
omitted
ignored
NONE
ignored
BASIC
ignored
EXTENDED
ignored
EXTENDED
1 DSNU169I The OBJECT CONVERSION REQUESTED BY requestor-type requestor-name requestor-operand IS IGNORED
2 DSNU123I csect-name ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE
Even if the table space is not actually converted by the REORG, because it already exists in the requested format, the index is converted to the same format as the table space. See Figure 3-3.
Figure 3-3 REORG TABLESPACE with Index conversion
If a conversion of the RBA/LRSN format occurs during the REORG, the job output provides the following message:
DSNU1169I -DB1A 220 18:59:09.53 DSNURFIJ - TABLESPACE SABIDB3.SEGMENT CONVERTED BY KEYWORD TO EXTENDED RBA/LRSN FORMAT
 
Active CLONE relationship restriction: If there is an active CLONE relationship, page set conversion is not performed. If this request is based on a system parameter setting, the fact that a conversion is requested is ignored. If, however, you request this conversion through a RBALRSN_CONVERSON utility control statement, a DSNU1459 error message is issued.
A new RBA_FORMAT column is added to the SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART catalog tables. The values in this column are:
B Basic, with 6 byte RBA/LRSN format
E Extended, with 10 byte RBA/LRSN format
U Undefined, where DEFINE NO was specified when creating the table space, and the table space is not an XML table space with XML versions.
blank For migrated objects
In addition to this new column, which indicates the RBA/LRSN format for a given page set, the TTYPE column on the SYSIBM.SYSCOPY catalog table adds additional information when a utility execution leads to a format conversion. If the resulting format is EXTENDED, you see an E in this column and if it is BASIC the value is B. As a consequence, you might see the combinations as listed in Table 3-2 in SYSIBM.SYSCOPY.
Table 3-2 SYSCOPY values for ICTYPE and TTYPE
Utility
ICTYPE
TTYPE
REORG LOG YES
X
B or E
REORG LOG NO
W
B or E
RECOVER PIT
P
B or E
REBUILD INDEX
B
B or E
LOAD REPLACE LOG YES
R
B or E
LOAD REPLACE LOG NO
S
B or E
 
Note: For index spaces, the entries occur only if they are copy-enabled. In addition, they are also generated only through the REORG INDEX, REBUILD INDEX, and RECOVER to PIT utilities. Thus, if the RBA/LRSN format is changed, through a REORG TABLESPACE, no information about this change is kept in the SYSIBM.SYSCOPY.
LOAD REPLACE
In addition to the REORG TABLESPACE and REORG INDEX utilities, LOAD with option REPLACE is another way to convert a page set’s RBA and LRSN length. The setting of UTILITY_OBJECT_CONVERSION system parameter applies, analogous to REORG, only past conversion mode. The RBALRSN_CONVERSION utility control statement can be set to NONE, BASIC, EXTENDED, and the utility behavior is basically the same as described in “The REORG INDEX and REORG TABLESPACE utilities” on page 37.
Keep in mind that specifying the RBALRSN_CONVERSION can only make sense on LOAD REPLACE. If you use LOAD RESUME, the nature of the utility is to load only a subset of the total number of rows into a table (space). Having a few rows use a 6 byte RBA and some using a 10 byte RBA really does not make sense. You receive the following error message when you try to request a conversion during a LOAD RESUME:
DSNU071 KEYWORD ‘RBALRSN_CONVERSION REQUIRES KEYWORD ‘REPLACE’.
REBUILD INDEX
In DB2 11 NFM, the same functionality and the same rules apply for the conversion of RBAs and LRSNs as described for REORG INDEX and TABLESPACE and for LOAD REPLACE.
DSN1COPY
DSN1COPY is a stand-alone utility. When you use this utility to overlay data of an existing table space with image copy data from an image copy that you created earlier, DB2 does not check or modify anything in the catalog.
Using the DSN1COPY utility can cause a mismatch between the information stored in the catalog and the actual table space format, as illustrated in Figure 3-4. This scenario assumes a TS is created in EXTENDED format (with the OBJECT_CREATE_FORMAT=EXTENDED system parameter). Take an image copy of the page set and subsequently reorganize it. Using the REORG control statement, specify the RBALRSN_CONVERSION option with BASIC value. This option changes the format of the table space from EXTENDED to BASIC. This change is also reflected in the SYSIBM.SYSTABLESPACE table RBA_FORMAT column, which now shows the B value. Next, run the DSN1COPY utility, and overlay the table space data with what is on image copy FC1, that is data in EXTENDED format. Now there is a mismatch between the information that is stored in the catalog and what is in the table space.
Figure 3-4 DSN1COPY: Catalog information mismatch
When you start working with the table stored in this table space, these is no message or, even worse, error message, but the mismatch is nevertheless not nice. You can use the REPAIR utility with the CATALOG option to fix this mismatch.
The REPAIR CATALOG option is a new option on the REPAIR utility. It indicates that the REPAIR utility is to validate information in the catalog for the specified table space. When you specify REPAIR CATALOG, the utility performs the following actions:
1. Compares the following information in the catalog with the data:
 – Row format (can be either reordered row format or basic row format)
 – RBA format (can be either 6 byte format or 10 byte format)
 – Data version information (same functionality that is performed with REPAIR VERSIONS)
 – Hash space value
For these items, if the information in the catalog is different from the data, the REPAIR utility changes the values in the catalog to match the data.
2. Validates the following information:
 – DBID, PSID, and OBID
 – Table space type
 – SEGSIZE
 – PAGESIZE
 – Table definition
For these items, if the information in the catalog is different from the data, the REPAIR utility does not correct the information in the catalog. Instead, the REPAIR utility fails and reports the mismatched information in a message. To correct the mismatched information, take the action that is documented for the message that you receive.
REPAIR CATALOG does not make any corrections for indexes. If you or the REPAIR utility made corrections to the data or catalog as a result of running REPAIR CATALOG, rebuild any indexes on the target tables.
The syntax for the REPAIR utility includes a TEST option that you can specify together with REPAIR CATALOG. This option indicates that the REPAIR utility is not to correct any mismatched information. Wih this option set, the utility checks all of the same information that it checks when you specify REPAIR CATALOG. However, any information differences between the data and catalog are reported only in messages. The utility does not take any corrective actions.
Example 3-9 shows the results before running REPAIR CATALOG for the situation described in Figure 3-4 on page 40, using the TEST option.
Example 3-9 Output of TEST option
DSNU674I -DB1A 221 15:58:59.95 DSNUCBVR - RBA FORMAT FOR DBID=X'014F' PSID=X'0005' IN THE DB2 CATALOG IS BASIC, BUT IN THE PAGE SET IS EXPANDED.
Then, subsequently running the REPAIR utility, the utility reports the messages shown in Example 3-10.
Example 3-10 Repair output
DSNU674I -DB1A 221 16:41:23.70 DSNUCBVR - RBA FORMAT FOR DBID=X'014F' PSID=X'0005' IN THE DB2 CATALOG IS BASIC, BUT IN THE PAGE SET IS EXPANDED.
DSNU695I -DB1A 221 16:41:23.71 DSNUCBVR - INFORMATION IN THE CATALOG WAS UPDATED
TO MATCH THE PAGE SET
3.1.8 Additional considerations regarding utilities
This section discusses several aspects about how utilities are affected by extended RBA/LRSNs and how utilities support RBS/LRSNs.
BACKUP/RESTORE SYSTEM
When you run the BACKUP SYSTEM utility, a token is associated with the backup to identify it. In a system for which the BSDS is not converted so that it can hold the extended RBAs and LRSNs, the token is 18 bytes long. In this situation, the token is composed as explained in Table 3-3.
Table 3-3 Composition of BACKUP token
Field
Field description
Length
DB2 SSID
DB2 subsystem ID
4 bytes
TOD
Time of day of SYSPITR = LRSN
8 bytes
RBA
Checkpoint RBA of last checkpoint before BACKUP
6 bytes
Example 3-11 shows a token resulting from a BACKUP SYSTEM execution on a DB2 subsystem for which the BSDS is not converted to the extended RBA/LRSN.
Example 3-11 BACKUP token prior to BSDS conversion
TOKEN = X'C4C2F0C2CBC85E68889EE60800003E44A7B1'
After you have converted the BSDS, the information given in the job output is different. First, the length of the token is increased by 4 bytes, which are added to the RBA at the end of the token. No change occurs for the time of day, which you can expect, because the BSDS conversion is for both, 10 bytes by RBAs as well as 10 bytes by LRSNs. Second, the job output is slightly changed. It shows both the tokenand the real value that is stored in the BSDS for the time of day.
Example 3-12 shows how the token changed and the additional information for the DATA COMPLETE LRSN, which in fact is 10 bytes long.
Example 3-12 BACKUP SYSTEM job output after BSDS conversion
DSNU1600I 220 17:06:57.72 DSNUVBBD - BACKUP SYSTEM UTILITY FOR DATA STARTING,
COPYPOOL = DSN$DB1A$DB
TOKEN = X'C4C2F1C1CBC8654135D846040000000000014B43AB62'.
DSNU1614I 220 17:06:59.95 DSNUVBBD - BACKUP SYSTEM UTILITY FOR DATA COMPLETED
COPYPOOL = DSN$DB1A$DB
TOKEN = X'C4C2F1C1CBC8654135D846040000000000014B43AB62'
DATA COMPLETE LRSN = X'0000000000014B43F6DA'
ELAPSED TIME = 00:00:02.
Also, if you print the BSDS afterwards and scroll to the section that lists all available SYSTEM BACKUPS, the long LRSN is also listed. Example 3-13 contains the information about one existing system level backup. Notice the entries showing up in the EXTENDED format.
Example 3-13 DSNJU004 after BACKUP SYSTEM for non-data sharing system
START STCK DATA COMPLETE
DATA LOG RBLP LRSN
---------------- ---------------- -------------------- --------------------
CBC8654135D84604 0000000000000000 0000000000014B43AB62 0000000000014B43F6DA
TOKEN = C4C2F1C1CBC8654135D846040000000000014B43AB62
Z/OS 1.13 CAT=YES
LOCATION NAME = DB1A
Things are a little different for data sharing systems. Example 3-14 shows almost the same data as Example 3-12, with the exception that the last 10 bytes of the token do not represent an RBA but an LRSN.
Example 3-14 BACK SYSTEM job output from data sharing system
DSNU1600I 220 16:05:48.87 DSNUVBBD - BACKUP SYSTEM UTILITY FOR DATA STARTING,
COPYPOOL = DSN$DB1B$DB
TOKEN = X'C4F1C2F1CBC8579652E78E0A00CBC85695DC17000000'.
DSNU1614I 220 16:05:49.80 DSNUVBBD - BACKUP SYSTEM UTILITY FOR DATA COMPLETED
COPYPOOL = DSN$DB1B$DB
TOKEN = X'C4F1C2F1CBC8579652E78E0A00CBC85695DC17000000'
DATA COMPLETE LRSN = X'00CBC8579651972A2200'
ELAPSED TIME = 00:00:00.
For completion, Example 3-15 shows the information listed in the BSDS for the data sharing system.
Example 3-15 DSNJU004 after BACKUP SYSTEM for data sharing
START STCK DATA COMPLETE
DATA LOG RBLP LRSN
---------------- ---------------- -------------------- --------------------
CBC8579652E78E0A 0000000000000000 00CBC85695DC17000000 00CBC8579651972A2200
TOKEN = C4F1C2F1CBC8579652E78E0A00CBC85695DC17000000
Z/OS 1.13 CAT=YES
LOCATION NAME = DB1B
RECOVER
The RECOVER utility TORBA, TOLOGPOINT, and RESTORBEFORE keywords now accept 6 byte or 10 byte RBAs or LRSNs. Operands of 6 bytes or less are interpreted as being in BASIC format. Operands greater than 6 bytes are interpreted as being in EXTENDED format.
The RECOVER utility now can handle the different RBA/LRSN formats. Figure 3-5 illustrates what happens when you recover a page set to a point prior to changing the RBA/LRSN format.
Figure 3-5 PIT RECOVERY
This example creates an object in BASIC format and take an image copy, which in this situation includes the data in BASIC format. Subsequently, the RBA/LRSN format changes using a REORG TABLESPACE. Because changing the RBA/LRSN format is possible only with a SHRLEVEL REFERENCE or CHANGE REORG, an image copy is mandatory. This image copy contains the data in EXTENDED format. If you had to perform a subsequent RECOVER to current, you use this image copy, and the object remains in EXTENDED format.
If you decide not to recover to CURRENT, but to image copy FC1, RECOVER also accepts this image copy as a recover basis. It uses the data as is on the image copy and recovers the data up to the point that you specify on your RECOVER TABLESPACE statement. If you specify any given RBA between FC1 and FC2, the result is a recovered object in BASIC format. The fact that the object is reverted to BASIC format is mentioned in the job output through the following message:
DSNU1169I -DB1A 220 18:59:09.53 DSNURFIJ - TABLESPACE SABIDB3.SEGMENT CONVERTED BY KEYWORD TO BASIC RBA/LRSN FORMAT.
In addition, several catalog changes are also applied to reflect this conversion. RBALRSN_FORMAT in SYSIBM.SYSTABLEPART is set to B and also the entry for the PIT recovery in SYSIBM.SYSCOPY indicates this through the TTYPE column for the PIT recovery record.
REPORT RECOVERY
The REPORT RECOVERY utility output now accommodates the longer RBAs and LRSNs. The utility output is restricted to 120 bytes in length, which means that the existing layout is compressed. So if you rely on processing the output automatically, make sure that you review current processes and adjust accordingly.
3.2 NOT LOGGED for declared global temporary tables
A declared global temporary table (DGTT) is used by an application to store intermediate SQL results data while an application is still running. DB2 11 improves this implementation by allowing the option to avoid logging during insert, update, and delete activity to DGTTs. This option can improve the performance and usability of DGTTs by applications. It is also in line with DB2 family compatibility because for DB2 for Linux, UNIX, and Windows already supports not-logged DGTTs.
With the ability to choose to not log DGTT activity, it might be beneficial for you to use DGTTs instead of a created global temporary table (CGTT). Although CGTTs support not logging and can provide better performance because the schema is known prior to execution of the application program, CGTTs do not support certain key features, such as indexes.
3.2.1 Syntax extension
Figure 3-6 illustrates the DECLARE GLOBAL TEMPORARY TABLE syntax.
Figure 3-6 CREATE DGTT syntax with NOT LOGGED options
The syntax in Figure 3-6 shows that a DGTT now has the following different logging options:
•LOGGED
This option is the default and the current behavior. In this case, DB2 logs all changes, and during ROLLBACK or ROLLBACK TO SAVEPOINT, the changes to the DGTT are undone.
•NOT LOGGED ON ROLLBACK DELETE ROWS
This option specifies that you do not want logging to occur for this table, and during ROLLBACK or ROLLBACK TO SAVEPOINT, all rows in the DGTT are deleted if any change was made in the duration.
•NOT LOGGED ON ROLLBACK PRESERVE ROWS
This option specifies that you do not want logging to occur for this table, and during ROLLBACK or ROLLBACK TO SAVEPOINT, the rows in the DGTT are preserved as they are.
You usually think about rollbacks in an error situation when there is a negative SQLCODE or message returned to the application. This kind of rollback is not affected by the new behavior. In the case of an error situation during an SQL statement, where an SQLCODE or message is issued, if an update was made to a DGTT and LOGGED is specified, the changes to the DGTT are undone.
Also, in the case of an error situation during an SQL statement, where an SQLCODE or message is issued, if an update was made to a DGTT and NOT LOGGED is specified, all rows in that DGTT are deleted, regardless of the DELETE/PRESERVE ROWS qualification.
 
Important: Your decision on the ROLLBACK behavior for DGTTs affects only situations in which your application explicitly issues a ROLLBACK statement.
3.2.2 Undo processing for NOT LOGGED DGTTs
Undo processing operates by reading the information on the log in a backward direction and backs out the changes made by the current unit of recovery, as indicated by the log records encountered. Undo processing is entered for of a number of reasons:
•The application issues the ROLLBACK SQL statement.
The undo processing depends on ON ROLLBACK DELETE/PRESERVE specification described previously. If you specified NOT LOGGED ON ROLLBACK DELETE ROWS on the DECLARE GLOBAL TEMPORARY TABLE statement and if the DGTT was updated since the last COMMIT, all rows in the DGTT are deleted. The DGTT itself remains available; however, all cursors open against the DGTT are closed.
If you specified NOT LOGGED ON ROLLBACK PRESERVE ROWS, all rows in the DGTT are preserved as is. The DGTT and the rows are available.
•The application issues the ROLLBACK TO SAVEPOINT statement.
In this case the undo processing depends on the ON ROLLBACK DELETE/PRESERVE specification described previously.
If you specified NOT LOGGED ON ROLLBACK DELETE ROWS and if the DGTT was updated since the last COMMIT, all rows in the DGTT are deleted. The DGTT itself is available; however, all cursors open against the DGTT remain open but not positioned because the rows are deleted.
If you used NOT LOGGED ON ROLLBACK PRESERVE ROWS on the DECLARE GLOBAL TEMPORARY TABLE statement, all rows in the DGTT are preserved as is. The DGTT and the rows are available.
•If there is an error while the application executes an INSERT, UPDATE, or DELETE statement, all rows are deleted from the DGTT. This type of error includes, for example, a duplicate key violation. The DGTT itself is available; however, any cursors open against the DGTT remain open but no longer have position.
•If an application gets cancelled using a -CANCEL THREAD command, all rows are deleted from the DGTT, and the DGTT is dropped.
•For statements that insert multiple rows, the ATOMIC and NOT ATOMIC CONTINUE ON SQLEXCEPTION options of the INSERT statement determine the result of an error inserting any of the rows.
 – The ATOMIC option specifies that if the insert for any row fails, all changes made to the database by any of the inserts, including changes made by successful inserts, are undone. This option is the default.
 – The NOT ATOMIC CONTINUE ON SQLEXCEPTION option specifies that, regardless of the failure of any particular insert of a row, the INSERT statement will not undo any changes made to the database by the successful inserts of other rows, and inserting will be attempted for subsequent rows. However, the minimum level of atomicity is at least that of a single insert (that is, it is not possible for a partial insert to complete), including any triggers that might have been executed as a result of the INSERT statement.
The ATOMIC or NOT ATOMIC CONTINUE ON SQLEXCEPTION clauses can be specified for a static multiple-row-insert. However, do not specify this clause for a dynamic INSERT statement. For a dynamic statement, use the ATOMIC or NOT ATOMIC CONTINUE ON SQLEXCEPTION clause as an attribute on the PREPARE statement.
3.2.3 Thread reuse
A thread is qualified for reuse if the table was defined with both the ON COMMIT DELETE ROWS attribute, which is the default, and the NOT LOGGED ON ROLLBACK DELETE ROWS attribute, which is not the default.
A thread is not qualified for reuse if the table is defined with PRESERVE ROWS specified either ON COMMIT or NOT LOGGED ON ROLLBACK.
3.2.4 Sample scenarios
The following examples summarize these types of scenarios.
Example 1
Assume that the application has the following sequence of SQL statements:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. CREATE UNIQUE INDEX on DT1
3. INSERT INTO DT1 (successful)
4. INSERT INTO DT1 (duplicate key error)
In this error situation, because no UNDO log records are available due to NOT LOGGED specification, DB2 deletes all the rows in DT1. However, the DGTT, DT1, are available for INSERT, UPDATE, DELETE, and FETCH.
Example 2
Assume that the application has the following sequence of SQL statements:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. CREATE UNIQUE INDEX on DT1
3. INSERT INTO DT1 (successful)
4. INSERT INTO DT1 (successful)
5. DECLARE GLOBAL TEMPORARY TABLE DT2 NOT LOGGED ON ROLLBACK DELETE ROWS (declaring a second DGTT) produces an internal error such as no space.
In this error situation, DB2 only undoes anything done for DECLARE DT2. DT1 remains as is, with 2 inserted rows, and available for further updates.
Example 3
Assume the following sequence of steps:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. CREATE UNIQUE INDEX on DT1
3. INSERT INTO DT1 (successful)
4. INSERT INTO DT1 (successful)
5. DECLARE GLOBAL TEMPORARY TABLE DT2 NOT LOGGED ON ROLLBACK DELETE ROWS
6. INSERT INTO DT2 (successful)
7. INSERT INTO DT2 (duplicate key error)
In this situation, DB2 delete rows from DT2, because the error was on INSERT into DT2.
Example 4
Assumes the following sequence of SQL statements:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. INSERT INTO DT1 with a subselect
3. COMMIT
4. DECLARE CURSOR C1 on DT1
5. OPEN C1
6. FETCH C1
7. UPDATE where current of C1
8. INSERT into DT1
9. ROLLBACK
This roll back causes DB2 to delete all the rows in DT1. Even the inserted rows that were committed. However, the DGTT, DT1, is available for INSERT, UPDATE, DELETE, and FETCH.
Example 5
Assume the application has the following sequence of SQL statements:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. INSERT INTO DT1 with a subselect
3. COMMIT
4. DECLARE CURSOR C1 on DT1
5. OPEN C1
6. FETCH C1
7. ROLLBACK
This rollback does not delete the rows in DT1 because no updates were made to DT1 since the last commit.
 
Difference with this example in the roll back: Note the difference here. When NOT LOGGED ON ROLLBACK DELETE ROWS is specified, insert, update, and delete activity is not logged. During a ROLLBACK or ROLLBACK TO SAVE POINT, if there was any updates to the DGTT since the last COMMIT statement, all rows from the DGTT are deleted, and any open cursors against the DGTT have no position. If the declaration of the DGTT itself was not committed, the DGTT itself is rolled back.
Example 6
Assume the following sequence of statements:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. INSERT INTO DT1 with a subselect
3. DECLARE CURSOR C1 on DT1
4. OPEN C1
5. FETCH C1
6. ROLLBACK
This rollback deallocates the DGTT because there was no COMMIT after it was declared. The NOT LOGGED attribute does not apply to log records written during the DECLARE and DROP of the DGTT.
Example 7
The following example is a sequence of steps for a DGTT, which is defined as NOT LOGGED ON ROLLBACK PRESERVE ROWS:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK PRESERVE ROWS
2. INSERT INTO DT1 with a subselect
3. COMMIT
4. DECLARE CURSOR C1 on DT1
5. OPEN C1
6. FETCH C1
7. UPDATE where current of C1
8. INSERT into DT1
9. DELETE FROM T2 (T2 is a different table unrelated to DGTT and is logged)
10. ROLLBACK
This roll back causes DB2 to rollback the DELETE from T2; however the DGTT, DT1, remains untouched because of the PRESERVE ROWS specification.
Example 8
Assume the following SQL statements are executed in the application:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK PRESERVE ROWS
2. INSERT INTO DT1 with a subselect
3. DECLARE CURSOR C1 on DT1
4. OPEN C1
5. FETCH C1
6. ROLLBACK
This rollback deallocates the DGTT because there was no COMMIT after it was declared. The NOT LOGGED attribute does not apply to log records written during the DECLARE and DROP of the DGTT.
Example 9
Assume that the application has the following sequence of SQL statements:
1. DECLARE GLOBAL TEMPORARY TABLE DT1 NOT LOGGED ON ROLLBACK DELETE ROWS
2. CREATE UNIQUE INDEX on DT1
3. INSERT INTO DT1 (successful)
4. SET SAVEPOINT SP1
5. INSERT INTO DT1 (successful)
6. DECLARE GLOBAL TEMPORARY TABLE DT2 NOT LOGGED ON ROLLBACK DELETE ROWS
7. INSERT INTO DT2 (successful)
8. ROLLBACK TO SP1
In this situation, DB2 deletes rows from DT1 and DT2. DB2 also undoes the declare of DT2. DT1 will still be available.
3.3 More open data sets (DSMAX)
The DSMAX subsystem parameter determines the maximum number of data sets that is to be allowed open at one time. DB2 11 increases the maximum number from 100,000 to 200,000. Although the maximum number of concurrently open data sets is 200,000, the practical limit might be significantly less on any given DB2 subsystem, depending on availability of virtual storage below the 2 GB bar. In most cases, a value of 50,000 to 75,000 open data sets is sufficient. The maximum number of 200, 000 open data sets is available in DB2 11 CM and has is retrofitted to DB2 10 through APAR PM88166.
3.4 PBG mapping tables to lift the 64 GB limit
When you reorganize a table space using SHRLEVEL CHANGE, you have to use a mapping table. For each row, the mapping table records the position of each record (RID) in the original table space and the one that it is to be found on the reorganized shadow copy.
DB2 11 includes the following enhancements related to the mapping tables:
•The mapping table can be created automatically by DB2 during REORG execution.
•The mapping table can grow up to 16 TB.
3.4.1 Autonomic creation of the mapping table
When processing a REORG TABLESPACE SHRLEVEL CHANGE request, the REORG utility has the option to create its own mapping table and mapping index, instead of relying on a user’s input. Use the REORG_MAPPING_DATABASE system parameter to specify a valid database name, and do not specify anything for the mapping table on the utility control statement. This action directs REORG to allocate the mapping table in the database that is specified.
If the name is valid only in terms of following the naming rules for database names but if the name that you specify is not an existing database in the DB2 subsystem, the REORG fails while trying to allocate a table space for the mapping table in this database.
Example 3-16 shows the error message in the utility output in this case.
Example 3-16 Error message for not found map table space
DSNUGUTC - REORG TABLESPACE SABIDB3.SEGMENT SHRLEVEL CHANGE
.35 DSNURMAP - MAPPING DATABASE yourdb IS INVALID
DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST RETURN CODE=8
The autonomic creation of mapping tables is described in more detail at 11.1.4, “Automated REORG mapping table management” on page 278.
3.4.2 Mapping tables up to 16 TB
Mapping tables must store information about each row’s source and target RID. Thus, a REORG job can hit the limit of 64 GB when very large table spaces are reorganized. Prior to DB2 11, this limit meant a mapping table had to be a segmented table space. With growing data needs, REORG jobs can hit the limit more often. DB2 11 allows you to use PBG table spaces for storing mapping tables, which practically eliminates the limit.
To determine how much space you need for a REORG, make the following calculation for the index:
1.1 * Number-of-rows-in-table-space * 31
1 The byte units are: Kilobyte ·Megabyte ·Gigabyte ·Terabyte ·Petabyte ·Exabyte ·Zettabyte ·Yottabyte
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset