2022 new-function APARs for Db2 Admin Tool 12.1

Support for REORG LASTLOG

PH50651 (Db2 Admin Tool), PH50652 (Object Comparison Tool) - November, 2022

Db2 recently introduced the LASTLOG option for the REORG TABLESPACE and REORG INDEX utilities to help reduce the outage window during REORG SHRLEVEL CHANGE executions. LASTLOG controls whether the utility applies log records during the last log iteration in the LOG phase. This option is valid in both Db2 12 and Db2 13.

APARs PH50651 and PH50652 provide support in Db2 Admin Tool and Object Comparison Tool for this new utility option. You can specify a LASTLOG value on the following REORG option panels:

• Specify Utility Options - REORG TABLESPACE (ADB2USO1) panel
• Specify Utility Options - REORG INDEX (ADB2UXO1) panel

These panels are displayed only after you specify C (CHANGE) for SHRLEVEL on the Specify Utility Options - REORG (ADB2USO) panel or the Specify Utility Options - REORG INDEX (ADB2UXO) panel

Additionally, you can specify the LASTLOG option by using the following new Change Management (CM) batch parameters:

• UTIL_REORG_LASTLOG
• UTIL_REORG_INDEX_LASTLOG

Note that you must also specify the SHRLEVEL CHANGE option (either UTIL_REORG_INDEX_ SHRLEVEL = 'C' or UTIL_REORG_SHRLEVEL = 'C') for LASTLOG to be valid.

The new CM batch parameter UTIL_REORG_INDEX_DRAIN is also added so that you can specify a drain value for REORG INDEX.

Eliminate unnecessary changes when comparisons involve objects created prior to Db2 12

PH49601 - November, 2022

If a table space was created prior to Db2 12, certain table space attributes that are new in version 12 might still be set to NULL in the Db2 catalog for that table space. If you use Db2 Object Comparison Tool to compare one of these table spaces in the catalog (the target) with DDL that was created in Db2 12 or later (the source), and the first partition values on the target are the same as the source table-space-level attributes, no ALTER statements should be generated for these attributes.

This APAR ensures that Object Comparison Tool does not generate unnecessary changes for the following attributes when comparing table spaces and the target is a table space in the catalog that was created prior to Db2 12:

• PQTY
• SECQTYI
• STORTYPE
• STORNAME
• VCATNAME
• PCTFREE
• PCTFREE_UPD
• TRACKMOD
• COMPRESS
• FREEPAGE
• GBPCACHE

A similar situation exists for indexes that were created prior to Db2 12. This APAR also ensures that Object Comparison Tool does not generate unnecessary changes for the following index attributes when comparing indexes and the target is an index in the catalog that was created prior to Db2 12:

• PQTY
• SECQTYI
• STORTYPE
• STORNAME
• VCATNAME
• FREEPAGE
• PCTFREE
• GBPCCAHE

Ability to estimate data set extents

PH42549 - September, 2022

The space estimator feature of Db2 Admin Tool is enhanced so that it can now estimate the number of data set extents required based on the primary and secondary space allocations. This enhancement applies to space estimates for table spaces, table space partitions, index spaces, and index partitions.

To view these extent estimates, use the new EXTENTS command on the DB2 Table Space Estimator (ADB2MES) panel and the DB2 Index Space Estimator (ADB2MEX) panel. When you issue this command, a pop-up window displays the estimated number of extents:

ADB2ME2 n ----------- DB2 Extents Estimator ------------- 14:08  
                                                                 
 PRIQTY . . . . . 11520               (16 cyls)                  
 SECQTY . . . . . 1440                (2 cyls)                   
                                                                 
 Estimated extents: 1                                            

You can override the PRIQTY and SECQTY field values (in KB) on this window to see how the estimated number of extents changes.

Improvements to page set resizing

PH42549 - September, 2022

An existing feature in Db2 Admin Tool is the ability to resize page sets by using the RESZ command in space manager [option SM.1 from the main DB2 Administration Menu (ADB2) panel]:

 ADB2M1S n ------------------- DD1A Page Set Statistics ------- Row 1 to 4 of 4 
 Command ===>                                                  Scroll ===> PAGE 
                                                                      More: >   
 Commands: VDEF VSTAT DDEF DSTAT RESZ LASTPG                                    
 Line commands:                                                                 
  I - Info  S - Space  SP - Space Part  G - Storage Group  DIS - Display        
  STA - Start  STO - Stop  LISTC - Listcat  LISTD - Listcat Data                
  ? - Show all line commands                                                    
                                                                                
       Data     Page             Sub         VSAM        VSAM Pct VSAM          
 Sel   Base     Set       Num T  Type    KB Alloc     KB Used Usd Exts Volser #V
       *        *           * *  *              *           *   *    * *       *
 ----- -------- -------- ---- -- ---- ----------- ----------- --- ---- ------ --
       DSNDB04  TSKQ23C     1 S  LOB         7200        7200 100    1 DBP16C  1
       DSNDB04  TSSJ2021    1 SP PBG          720         720 100    1 DBP16C  1
       DSNDB04  TSSMPL1     1 SP PBG          720         720 100    1 DBP185  1
       DSNDB04  TS251421    1 SP PBG          720         720 100    1 DBP175  1
 ******************************* END OF DB2 DATA *******************************

Previously, when you specified RESZ, the generated job included STO and STA commands to stop and restart your database. With this APAR, those commands are no longer included in the generated job unless they are required. This change helps minimize unnecessary disruptions to your environment when resizing page sets.

Usability improvements to space manager

PH42549 - September, 2022

You can now access the space manager feature of Db2 Admin Tool directly from the system catalog panels by using the SM and SE line commands. You can use the SM line command to view page set space statistics for databases, table spaces, indexes, and partitions. You can use the SE line command to get space estimates for table spaces, indexes, and partitions.

Additionally, to improve the usability of the Page Set Statistics (ADB2M1S) panel, you can now scroll right and left on this panel to view all page set statistics. You can also continue to use the VDEF, VSTAT, DDEF, and DSTAT commands to focus on particular statistics.

Space estimator REST API

PH42549 - September, 2022

You can now call the space estimator feature of Db2 Admin Tool as a REST API. Three REXX stored procedures are provided for this purpose, one for table space estimates, one for index space estimates, and one for extent estimates. To call space estimator as a REST API, you must first create these stored procedures and then bind them as REST services. You can then invoke them by using REST calls.

REORG SHRLEVEL default change to avoid pending changes

PH49639 – September, 2022

When using Change Management (CM), Object Comparison Tool, or the ALT command, a change might require a REORG utility operation. In this case, if no value is specified for the REORG SHRLEVEL option, Db2 Admin Tool generates a REORG statement with a default value for SHRLEVEL. With this APAR applied, SHRLEVEL NONE will no longer be generated for these situations, because it prevents pending definition changes from being materialized and can leave objects in a pending state. Instead, to ensure that any pending changes are materialized successfully, either SHRLEVEL CHANGE or SHRLEVEL REFERENCE will be used; Db2 Admin Tool determines the best value (CHANGE or REFERENCE) depending on the circumstance.

These changes to REORG SHRLEVEL do not apply to other contexts (outside of CM, Object Comparison Tool, and ALT). For example, if you run the REORG utility from the Table Spaces (ADB21S) panel, the resulting REORG statements are unchanged.

Improvements to inserting and adding partitions

PH48016 – August, 2022

Db2 Object Comparison Tool is enhanced to improve how partitions are added and inserted. In some cases, these changes reduce unnecessary and potentially costly REORG utility operations.

Prior to this APAR, when comparing partition-by-range (PBR) table spaces where the source has more partitions than the target, Object Comparison Tool generated the following statements for each partition to be added:

1. ALTER TABLE statement with the ADD PARTITION (MAXVALUE) clause.
2. REORG utility statement.
3. ALTER TABLE statement with the ALTER PARTITION clause to alter the added partition with the new limit key.

With this APAR applied, a single ADD PARTITION is generated, which also reduces the REORG statements that are generated in some cases.

Ability to limit UNLOAD_ALTERED_TABLES to destructive alters

PH48010 – July, 2022

When using Change Management (CM), you can specify whether to unload tables as part of the analyze process by using the CM batch parameter UNLOAD_ALTERED_TABLES or the Unload altered tables field on the Options for Change Functions (ADB2PCO) panel. Specifying YES ensures that a copy of the data is preserved. However, the unload process can take a significant amount of time and thus increase the overall time it takes to run a change. To help reduce this time, APAR PH48010 introduces a new value for this unload option, DES, which specifies that only those tables with destructive alters are to be unloaded. Destructive alters are changes that might result in the loss of data, such as ALTER TABLE DROP COLUMN. DES (or the short value D) is valid for both the CM batch parameter UNLOAD_ALTERED_TABLES and the corresponding Unload altered tables field.

Simplified process for propagating multi-target changes to remote subsystems

PH47981 - July, 2022

The process of analyzing and running multi-target changes on remote subsystems is simplified. You can now analyze and run remote target changes in fewer jobs by specifying the location of the central subsystem and the change name. Use the new CM batch parameter MTC_CENTRAL_LOCATION to specify the location to look for the change.

Previously, when propagating changes to remote subsystems, you had to run a separate analyze job and run job with a unique change name for each target on each remote subsystem. Specifying a central location for the change reduces the number of jobs that you need to run.

For example, assume you create a change with the name MYMTC that affects 30 schemas on your local subsystem DC1A, and you want to propagate this change to 10 remote subsystems. Prior to this enhancement, you had to take the following steps:

1. To implement this change for the 30 schemas on your local susbystem, run two CM batch jobs: one job to analyze the change (ACTION_ANALYZE_CHANGE='Y') and one job to run the change (ACTION_RUN_CHANGE='Y' ). Both of these jobs specify the change name MYMTC (CHANGE_NAME='MYMTC').
2. For the first remote subsystem, run two jobs (one analyze job and one run job) for each of the 30 target schemas on the subsystem. Each pair of jobs specifies a different change name (for example, CHANGE_NAME ='MYMTC:TGT1:remote-location-name', CHANGE_NAME='MYMTC:TGT2:remote-location-name' and so on.) This step has 60 jobs total.
3. Repeat step 2 for the remaining 9 subsystems.

This enhancement simplifies steps 2 and 3. Assuming a DRDA connection exists between the central and remote subsystems, you now need to run only 2 jobs (one analyze job and one run job) on each remote subsystem:

• The analyze job specifies ACTION_ANALYZE_CHANGE='Y' , the change name (CHANGE_NAME='MYMTC'), and the central location (MTC_CENTRAL_LOCATION = 'dc1a-location').
• The run jobs specifies ACTION_RUN_CHANGE='Y' , the change name (CHANGE_NAME='MYMTC'), and the central location (MTC_CENTRAL_LOCATION = 'dc1a-location').

Ability to prevent binds when a CONTOKEN changes but the package version is the same

PH42301 - March, 2022

The bind avoidance program ADBBMA3 is enhanced so that you can specify whether you want a package to be bound if the consistency token (CONTOKEN) has changed but the package version is still the same.

BND2919I SYSPACKAGE ENTRY=dbrm_name WITH VERSION version HAD CONTOKEN contoken_value 
AND DOES NOT MATCH NEW DBRM

BND2920E VERSION version IS NOT UNIQUE FOR PACKAGE package-name IN COLLECTION collid. 
SETTING RC=8 BECAUSE OPTION UNIQUE-VERSION WAS SPECIFIED