2022 new-function APARs for Db2 Admin Tool 13.1
The following APARs were delivered in 2022. They introduced enhancements to Db2 Admin Tool 13.1 since the General Availability (GA) date.
Support for statement-level dependencies for packages
PH50890, PH51493 - December, 2022
Db2 13 FL 502 Db2 13 function level 502 introduces an option for package dependencies and validation to be recorded at the statement level. This behavior is controlled by the new DEPLEVEL bind option and PACKAGE_DEPENDENCY_LEVEL subsystem parameter.
This new functionality is supported by Db2 Admin Tool 13.1 and can be useful for determining which statements are affected by object changes. Specifically, this Db2 enhancement is supported as follows:
- You can specify a value for the new DEPLEVEL bind option on the Bind package (ADB21KB) panel, the Rebind package (ADB21KR) panel, and the Bind Rest Service (ADB21RSB) panel.
- You can view statement-level dependencies for a package by using the new DPS line command on the Packages (ADB21K) panel and the Package Dependencies (ADB21KD) panel.
- The Packages (ADB21K) panel has a new Dep Lev column that displays the DEPLEVEL value. Also, the VA (Valid) column on this panel has a new value S for statement.
- When you interpret a package (by using the I line command) or view the details for a package (by using the DET or BET line command), the dependency level is displayed.
- You can change the PACKAGE_DEPENDENCY_LEVEL subsystem parameter on the System Parameters — System Parameters (ADB2ZZMN) panel.
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 specify REBIND options when altering objects
PH50333 - November, 2022
When altering objects in Db2 Admin Tool by using the ALT command, you can choose to rebind any dependent packages. Prior to this APAR, these packages were rebound with their existing BIND options (the options that were used during the previous bind or rebind operation). With this APAR, you can now specify different BIND options. For example, you can specify APREUSE(ERROR) to help retain existing access paths.
To specify REBIND options, use the new REBIND options field when the ALTER - Build Analyze and Apply Job (ADBPALT) panel is displayed as part of the ALTER process:
ADBPALT ------------------- ALTER - Build Analyze and Apply Job --------------
Command ===>
....
Optional processes:
Run CHECK DATA . . . . . NO (Yes/No)
Run COPY . . . . . . . . N (after: Reload/Alter/Both/None)
Run REORG/REBUILD . . . . N (Mandatory, All relevant, None)
Run RUNSTATS . . . . . . N (after: Reload/Alter/Both/Min/None)
Run REBIND . . . . . . . A (Mandatory, All relevant, None)
REBIND options . . . . Yes (Yes/No)
Utility control options:
Use templates . . . . . . NO (Yes/No)
Use utility options . . . NO (Yes/No)
BP - Change batch job parameters
TU - Specify TEMPLATE usage
UO - Customize utility options
CO - Change options common to change functions When you specify Yes in this new field (and either M or A in the Run REBIND field), the REBIND options (ADBPREBO) panel is displayed where you can specify the following options:
ADBPREBO ------------------------ REBIND options ------------------------
Command ===>
Specify additional REBIND parameters to generate rebinds
for dependent packages.
APREUSE . . . . (None, Warn, Error)
EXPLAIN . . . . (Yes, No, Only)
OWNER . . . . . > (Owner of package)
OWNERTYPE . . (Role, User)
Additional options: _________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________
_____________________________________________________________________________Any BIND options that you specify in the Additional options field are added as is; they are not validated.
If you use Change Management (CM), you will see this same REBIND options field on the Generate Analyze Job (ADB2C11A) panel:
ADB2C11A ----------------- Generate Analyze Job -----------------------
Command ===>
Specify the following for Analyze:
...
Retain GENERATED ALWAYS:
For ROWID . . . . . . . . (Yes/No)
For ROW CHANGE TIMESTAMP. (Yes/No)
IDENTITY START value . . . (Original, Computed)
SEQUENCE RESTART value . . (Original, Computed)
Disable REORG optimization (Yes/No)
Optional jobs after Reload or Alter:
Run CHECK DATA . . . . . NO (Yes/No)
Take an image copy . . . N (after: Reload/Alter/Both/None)
Run REORG/REBUILD . . . . N (Mandatory, All relevant, None)
Run RUNSTATS . . . . . . N (after: Reload/Alter/Both/Min/None)
Run REBIND . . . . . . . N (Mandatory, All relevant, None)
REBIND options . . . . YES (Yes/No)
BP - Change batch job parameters
TU - Specify TEMPLATE usage
UO - Change utility options
CO - Change options common to change functions Again, if you specify Yes, you can enter any BIND options on the subsequent REBIND options (ADBPREBO) panel .
Similarly, you can specify these BIND options when using CM batch by using the following new parameters:
You can also specify REBIND options when altering objects in Db2 Object Comparison Tool. See New-function APARs in Db2 Object Comparison Tool 13.1.
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.
- If you are using CM batch, set
USE_UTILITY_OPTIONS='Y'and specify a value forUTIL_REORG_SHRLEVEL. - If you are using panels, specify the SHRLEVEL value on the Specify Utility Options - REORG
(ADB2USO) panel and set one of the following
values to YES:
- Use Utility Options on the Generate Analyze Job (ADB2C11A) panel (when using CM)
- Use customized util opts on the Generate Compare Jobs (GOC5) panel (when using Object Comparison Tool)
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:
- ALTER TABLE statement with the ADD PARTITION (MAXVALUE) clause.
- REORG utility statement.
- 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.
AL support for online PBG to PBR conversion
PH47480 - August, 2022
Beginning in Db2 Admin Tool 13.1, you can use the ALT line command to convert a partition-by-growth (PBG) table space to a partition-by-range (PBR) table space with the new Db2 13 ALTER syntax. For more information about this enhancement, see Online conversion of partition-by-growth (PBG) table spaces to partition-by-range (PBR) table spaces.
APAR PH47480 expands this support for an online conversion of a table space from PBG to PBR. With this APAR, you now have the option to use the AL line command (instead of the ALT line command) for this conversion. Note that you must be running Db2 13 to do this conversion online (with an ALTER statement instead of DROP and CREATE statements).
Eliminate drop and create when altering table spaces
PH48508 - August, 2022
Previously, when you used the ALT line command to alter a table space, the Db2 Admin Tool ALT function generated ADMIN DROP (pseudo DROP) and CREATE statements with a full object definition to process the change, which could lead to updating an object with an outdated definition. With this APAR, Db2 Admin Tool now generates Db2 ALTER statements and ADMIN ALTER statements so only the attributes that were modified by the user are referenced. This change is reflected on the Redefine Table Space (ADB21SAR) panel:
ADB21SAR ------------------ DB2X Redefine Table Space ------- Row 1 to 1 of 1
Command ===> Scroll ===> CSR
Commands: NEXT ORIGINAL MAKEPBG MAKEPBR MAKEPBR2
Line commands: S - Split part R - Remove part O - Original data
C - Clear data ? - Show all line commands
ALTER TABLESPACE: TSFGR IN DBFGR
Owner . . . . . . . RIVERAG > Owner type . . _ (U/R)
...
Additionally, values that are set on this Redefine Table Space (ADB21SAR) panel are saved during the ALT process and correctly displayed when you later specify the A (Alter) line command on the Alter Objects (ADB27CA) panel.
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:
- To implement this change for the 30 schemas on your local subsystem, 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'). - 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. - 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').