What's new in Db2 Admin Tool 13.1
IBM® Db2 Administration Tool for z/OS® (Db2 Admin Tool) 13.1 introduces new features to support Db2 13 for z/OS and other usability features. The following enhancements are available as of the General Availability (GA) date of Db2 Admin Tool 13.1.
To see videos for version 13 enhancements in Db2 Admin Tool, visit Db2 Administration Tool 13.1 video playlist.
For a list of enhancements that have been added since GA, see Recent enhancements to Db2 Admin Tool 13.1.
Online conversion of partition-by-growth (PBG) table spaces to partition-by-range (PBR) table spaces
Db2 13 introduces the capability to convert a table with growth-based partitions (in a PBG table space) to use range-based partitions (in a PBR table space) with an online change that has minimal impact to your applications. This online conversion is accomplished by using an ALTER TABLE statement with the new ALTER PARTITIONING TO PARTITION BY RANGE clause. You can use Db2 Admin Tool 13.1 to do this online conversion.
Specifically, in Db2 Admin Tool, you can use the ALT command and subsequently the MAKEPBR2 command to alter a table space from PBG to PBR. Beginning in version 13.1 with APPLCOMPAT set to V13R1M500 or higher, when you specify MAKEPBR2 and the high limit key for the last partition is MAXVALUE (for ascending columns) or MINVALUE (for descending columns), an ALTER statement is generated with the new ALTER PARTITIONING TO PARTITION BY RANGE clause. Previously, this conversion was done by DROP and CREATE statements. The new ALTER statement avoids any outages. Additionally, a REORG utility statement is generated to materialize the pending change if needed and requested. Note that the high limit key requirement is a Db2 requirement to avoid the data being discarded during any subsequent REORG operations.
Support for using the AL line command to do this conversion is added by APAR PH47480. For more information, see AL support for online PBG to PBR conversion (PH47480 - August, 2022).
You can also use Db2 Object Comparison Tool 13.1 to perform this online conversion. When APPLCOMPAT is set to V13R1M500 or higher and a target PBG table space needs to be changed to a PBR table space, Object Comparison Tool generates an ALTER statement when valid (according to any Db2 restrictions) and any necessary REORG statements to perform this conversion and thus minimize outages.
This new ALTER syntax is also supported by all other functions in Db2 Admin Tool 13.1, such as GEN and DDL.
Support for managing utility history
Db2 13 provides the ability to collect information about IBM Db2 utility executions and store that information in the new Db2 catalog table SYSUTILITIES. Db2 Admin Tool 13.1 allows you to easily view and search this utility history and delete obsolete data from SYSUTILITIES.
You can search utility history by using one of the following methods:
- Entering criteria, such as the utility, user ID, age, and elapsed time
- Running queries that are predefined by Db2 Admin Tool, such as a query that returns the top 25 active utilities with the longest elapsed time
- Creating your own queries by using the SELECT prototype function
- Viewing utilities that use more CPU time than similar utilities
Additionally, Db2 Admin Tool helps you maintain SYSUTILITIES. You can delete rows that were added before a specified date. You can also choose to delete rows by utility status (ended, terminated, or terminated by force).
You can perform all of these actions from the new Display Utility History (ADB2Z2H) panel [option 2H on the System Administration (ADB2Z) panel].
Ability to generate commands on a list of objects
Beginning in Db2 Admin Tool 13.1, you can generate commands on a list of objects from any catalog panel by using the new GCMDS command. You can generate SQL statements, DSN commands, and Db2 commands.
In previous versions of Db2 Admin Tool, you had to execute these commands individually on each object, which could be time consuming depending on the number of objects. The new GCMDS command eliminates these repetitive steps. For example, you can easily transfer ownership of many tables to another user in a single process without having to repeat steps.
When you specify the GCMDS command, a command skeleton is displayed where you can specify one or more commands that you want to run on all of the objects listed. You can generate the commands as a batch job or store them in a data set. If you store the generated commands in a data set, you can import them into Change Management (CM) and analyze the changes to determine the impact on your objects.
Support for Db2 profile tables
- View profiles and attributes
- Create and update profiles and attributes
- Start and stop profiles
- View profile and attribute history
Ability to delete active log data sets
Beginning in Db2 13 function level 500, you can remove an active log data set from the BSDS while Db2 is running. You can use Db2 Admin Tool 13.1 to perform this action by using the new LR option on the System Administration (ADB2Z) panel.
When you specify LR, you can choose to display a list of all active log data sets. From this list you can select the one that you want to delete by using the new SRL line command. Db2 Admin Tool then issues the Db2 command SET LOG REMOVELOG to remove the log or mark it as REMOVAL PENDING if it is in use.
Db2 13 also enhanced the DISPLAY LOG command to add a new DETAIL option, which displays detailed information about active log data sets with a REMOVAL PENDING status. To support this enhancement, Db2 Admin Tool updated the LI option (Display Log Information) on the System Administration (ADB2Z) panel, which issues the DISPLAY LOG command. Beginning in Db2 Admin Tool 13.1, this command is issued with the new DETAIL option for Db2 13 function level 500 or higher.
Additionally, to improve the usability of the System Administration (ADB2Z) panel, the options for Db2 log functions are rearranged in Db2 Admin Tool 13.1 to be more logical. The layout of these options for version 12.1 were as follows:
DB2 log functions:
LD - Display archive log parameters LS - Set archive log parameters
LA - Archive current log LI - Display log information
LZ - Set log checkpoint frequency
The layout of these options for version 13.1 are now as follows:
DB2 log functions:
LI - Display log information LD - Display archive log parameters
LA - Archive current log LS - Set archive log parameters
LZ - Set log checkpoint frequency LR - Remove active log data set
Support for package owner type
- For the Db2 commands BIND and REBIND, Db2 13 introduces the new keyword OWNERTYPE for the OWNER bind option.
- For the SQL CREATE and ALTER statements for compiled SQL scalar functions and native SQL procedures, Db2 13 introduces the new keywords AS ROLE and AS OWNER in the PACKAGE OWNER clause.
Db2 Admin Tool 13.1 supports this new syntax.
When binding and rebinding packages and plans, you can specify OWNERTYPE on the following panels:
- Bind package (ADB21KB) panel
- Rebind package (ADB21KR) panel
- Bind Application Plan (ADB21PB) panel
- Rebind Application Plan (ADB21PR) panel
- Create Rest Service (ADB21RSB) panel
Additionally, the OWNER field on these panels is enhanced so that you can use the lookup function to select a role from a list. For example, if you specify ROLE for OWNERTYPE, you can specify ? for OWNER to look up the available roles:
OWNER . . . . . . . ? > (Owner of package or ? for lookup) OWNERTYPE . . . . ROLE (Role, User)
On the subsequent panel, you can specify + next to the role that you want to select:
ADB2ARL n DD1A Roles Row 1 to 12 of 16
Command ===> Scroll ===> PAGE
Select by typing '+'
Commands: GEN
Line commands:
TC - Trusted Contexts OBJ - Dependent objects DROP - Drop COM - Comment
DR - Definer role I - Interpretation DDL - Generate DDL GEN - Generate SQL
? - Show all line commands
Sel Name Definer T Created Timestamp
* * * *
---- -----------------> -------- - --------------------------
CONNECT_ROLE NEWTON 2021-09-29-11.28.37.571536
+ R1 NEWTON 2021-09-29-11.53.55.882643
R2 NEWTON 2021-09-29-11.53.55.883281
R3 NEWTON 2021-09-29-11.53.55.883767
R4 NEWTON 2021-09-29-11.53.55.884318
R5 NEWTON 2021-09-29-11.53.55.884809
R6 NEWTON 2021-09-29-11.53.55.885270
RL174061 TS5465 2021-11-11-08.19.23.115417
RL174063 TS5465 2021-12-22-06.47.23.296060
ROLE1 NEWTON 2021-09-29-11.53.55.885712
ROLEE NEWTON 2021-09-29-11.53.55.886209
TEST TS3629 2021-11-10-04.00.43.392693
When running Db2 Admin Tool 13.1 with Db2 13 function level 500 or higher, any BIND or REBIND commands that are generated by Db2 Admin Tool include the OWNERTYPE option.
When creating and altering compiled SQL scalar functions and native SQL procedures, you can specify AS ROLE or AS USER under the PACKAGE OWNER field on the following bind panels:
- Create Stored Procedure BIND Options (ADB26COW) panel
- Alter Stored Procedure BIND Options (ADB26COW) panel
- Create SQL Scalar BIND Options (ADBP6CFZ) panel
- Alter SQL Scalar BIND Options (ADBP6CFZ) panel
The PACKAGE OWNER field on these panels is also enhanced so that you can use the lookup function to select a role from a list:
PACKAGE OWNER . . . . . . ? > (Owner of package or ? for lookup) AS . . . . . . . . . . ROLE (Role, User)
When running Db2 Admin Tool 13.1 with Db2 13 function level 500 or higher, any DDL statements that are generated by Db2 Admin Tool for compiled SQL scalar functions and native SQL procedures include the new AS USER or AS ROLE clause. (DDL can be generated by using the DDL or GEN commands.)
Additionally, Object Comparison Tool 13.1 can compare the owner and owner type for these procedures and functions and generate changes as needed. For example, if the owner and owner type differ between the source object and the target object, the compare report contains a message similar to the following message:
Options
(A)Field PACKAGE OWNER changed from 'RL174061 AS ROLE' to 'TS5465 AS USER'
Native SQL Procedure options will be alteredAbility to view index split information
Db2 13 function level 500 introduced instrumentation to provide detailed information about index splits. Starting in function level 501, the following new real-time statistics (RTS) columns in the Db2 catalog table SYSIBM.SYSINDEXSPACESTATS record and aggregate general index split information since the last table reorganization, index rebuild, or load replace:
- REORGTOTALSPLITS
- REORGSPLITTIME
- REORGEXCSPLITS
In Db2 Admin Tool 13.1, you can view the data in these columns when getting maintenance recommendations for index spaces. For example, the following example Index Space Maintenance (ADB2314X) panel, which is scrolled all the way to the right, displays split information under Last Reorg or Rebuild:
- The Tot Splits column displays data from the REORGTOTALSPLITS column in SYSINDEXSPACESTATS.
- The Split Time column displays data from the REORGSPLITTIME column in SYSINDEXSPACESTATS.
- The Abn Splits column displays data from the REORGEXCSPLITS column in SYSINDEXSPACESTATS.
ADB2314X DD1A Index Space Maintenance Row 764 from 1000
Command ===> Scroll ===> PAGE
More: <
Commands: COPY REORG RUNSTATS REFRTS
Line commands: C - Copy O - Reorg R - Runstats AL - Resize S - Select
I - Interpret REF - REFRTS ? - Show all line commands
Index -------Last Reorg or Rebuild-------
Sel Space DB Name Tot Splits Split Time Abn Splits
* * * * *
--- -------- -------- ----------- <---------- -----------
ARYSVIX0 ARYDB320 2 6 0
ARYV1OPM ARYDB320 2 4 0
ARYDPIX1 ARYDB320 2 2 0
IX88816 DB88816 2 1 0
******************************* END OF DB2 DATA *******************************
Additionally, when you request index space maintenance recommendations, you can specify the following criteria for index splits on the Input Parameters for Real-Time Statistics (ADB2314I) panel:
- The limit for the number of abnormal index splits since the last REORG or REBUILD INDEX
- The ratio, as a percent, of the total number of index splits to pages since the last REORG or REBUILD INDEX
Support for page sampling for inline statistics
Db2 13 introduced page sampling for inline statistics that are gathered during LOAD and REORG utility operations. Page sampling has the potential to reduce the CPU time and elapsed time required to gather statistics. This new option is supported in Db2 Admin Tool 13.1. You can specify the new keyword TABLESAMPLE SYSTEM on the following utility option panels:
- Specify Utility Options - LOAD (ADB2UTC) panel
- Specify Utility Options - REORG (ADB2USO) panel
Alternatively, if you use Change Management (CM) batch, you can specify the following parameters:
Support for changes to real-time statistics (RTS) tables
In Db2 13, several columns in the RTS tables SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS and their associated history tables SYSIBM.SYSTABSPACESTATS_H and SYSIBM.SYSIXSPACESTATS_H are being expanded from type SMALLINT to INTEGER or from type INTEGER to BIGINT. These expanded types help accommodate larger values for ever increasing volumes of data.
In Db2 Admin Tool, you can view these RTS column values on the Table Space Maintenance (ADB2314) panel and Index Space Maintenance (ADB2314X) panel. In version 13.1, the fields on these panels for those expanded RTS columns are now scrollable to accommodate the larger values.
Additionally, the following panels are deprecated:
- Real-time Statistics for Table (ADBP1RTS) panel
- Real-Time Statistics for Index Space (ADB21XS) panel
Because these panels are similar to the Table Space Maintenance (ADB2314) panel and Index Space Maintenance (ADB2314X) panel, the functionality on these deprecated panels is added to panels ADB2314 and ADB2314X. Panels ADBP1RTS and ADB21XS will be removed in the future.
Support for REORG INDEX NOSYSUT1 default change
Beginning in Db2 13 function level 500, the REORG INDEX utility no longer requires NOSYSUT1 to be specified. NOSYSUT1 behavior (not using a work data set to hold the unloaded index keys) is always the default behavior if SHRLEVEL REFERENCE or SHRLEVEL CHANGE is specified.
Because NOSYSUT1 no longer needs to be explicitly specified, Db2 Admin Tool 13.1 has made the following changes to improve usability if the target Db2 subsystem is at V13R1M500 or later:
- NOSYSUT1 is removed from the Specify Utility Options - REORG INDEX (ADB2UXO) panel
- NOSYSUT1 is no longer included in generated REORG INDEX jobs.
- The Change Management batch parameter UTIL_REORG_INDEX_NOSYSUT1 is still allowed but ignored.
Support for long column names
Db2 13 introduced support for long column names (up to 128 bytes) when the TABLE_COL_NAME_EXPANSION subsystem parameter setting is ON. Previously, the limit was 30 bytes. Db2 Admin Tool 13.1 and Object Comparison Tool 13.1 can manage these longer column names. For example, you can generate DDL for tables with long column names, alter a table to use long column names, compare objects with long column names, and more.
Additionally, you can set the TABLE_COL_NAME_EXPANSION subsystem parameter value from Db2 Admin Tool. To avoid unpredictable results, ensure that TABLE_COL_NAME_EXPANSION has the same setting on all members of a data sharing group.
Unpredictable results can also occur if long name support is enabled and tables are created with long names and long name support subsequently is disabled. To resolve this problem, re-enable long name support and correct any long column names so that all column names are 30 bytes or less. You can make this change by renaming the affected columns (ALTER TABLE RENAME COLUMN or rename by using Db2 Admin Tool) or dropping the affected columns (ALTER TABLE DROP COLUMN or drop by using Db2 Admin Tool). Then disable long name support again.
Support for Db2 13 changes to subsystem parameters
In Db2 13, a number of subsystem parameters were added, changed, or removed. To support these changes in Db2 Admin Tool 13.1, the System Parameters — System Parameters (ADB2ZZMN) panel is modified as follows:
- The following parameters were removed:
- AUTHCACH
- CACHEPAC
- CACHERAС
- CHGDC
- COMPRESS_SPT01
- DDF_COMPATIBILITY
- DSVCI
- EDPROP
- EXTRAREQ
- EXTRASRV
- HONOR_KEEPDICTIONARY
- IMMEDWRI
- IRLMAUT
- IRLMSWT
- IX_TB_PART_CONV_EXCLUDE
- MAXARCH
- MAXTYPE1
- OBJECT_CREATE_FORMAT
- OPT1ROWBLOCKSORT
- PARA_EFF
- PCLOSEN
- PLANMGMTSCOPE
- REALSTORAGE_MANAGEMENT
- RESYNC
- SPT01_INLINE_LENGTH
- SUBQ_MIDX
- TRACSTR
- UTILITY_OBJECT_CONVERSION
- The following parameters were added:
- PACKAGE_DEPENDENCY_LEVEL
- SPREG_LOCK_TIMEOUT_MAX
- TABLE_COL_NAME_EXPANSION
- UTILITY_HISTORY
- Parameter values are adjusted to match any new ranges allowed in Db2 13
Db2 catalog level displayed
In Db2 Admin Tool 13.1, you can view the Db2 catalog level from the main menu:
ADB2 dmin -------------- DB2 Administration Menu 13.1.0 ----------------- 13:07
Option ===>
1 - DB2 system catalog DB2 System: DC1A
2 - Execute SQL statements DB2 SQL ID: TS6462
3 - DB2 performance queries Userid : TS6462
4 - Change current SQL ID DB2 Schema: TS6462
5 - Utility generation using LISTDEFs and TEMPLATEs DB2 Rel : 1315
P - Change DB2 Admin parameters DB2 F.Lvl : V13R1M501
DD - Distributed DB2 systems Max ApplC : V13R1M501
E - Explain ApplCompat: V13R1M501
Z - DB2 system administration Cat Level : V13R1M501
SM - Space management functions
W - Manage work statement lists
X - Exit DB2 Admin
CC - DB2 catalog copy version maintenance
CM - Change management
More: +
Interface to other DB2 products and offerings:
I DB2I
C DB2 OBJECT COMPARISON TOOL
Location name displayed
In Db2 Admin Tool 13.1, if you are connected to a remote Db2 subsystem, the remote subsystem location name is displayed on the main menu:
DB2 Admin -------------- DB2 Administration Menu 12.1.0 ----------------- 10:36 Option ===> 1 - DB2 system catalog DB2 System: DB2A 2 - Execute SQL statements DB2 SQL ID: MYUSERID 3 - DB2 performance queries Userid : MYUSERID 4 - Change current SQL ID DB2 Schema: MYSCHEMA 5 - Utility generation using LISTDEFs and TEMPLATEs DB2 Rel : 1215 P - Change DB2 Admin parameters DB2 F.Lvl : 501 DD - Distributed DB2 systems ApplCompat: E - Explain Loc. : DB2B11 Z - DB2 system administration SM - Space management functions W - Manage work statement lists X - Exit DB2 Admin CC - DB2 catalog copy version maintenance
If you are not connected to a remote subsystem, the Loc. (Location) field is not displayed.
Ability to suppress the copyright statement when invoking Db2 Admin Tool with CMD
An external tool can invoke Db2 Admin Tool to view catalog objects by using the CMD parameter with a catalog navigation command. In version 13.1, you can use the new SILENT option so that the copyright statement is not displayed each time that Db2 Admin Tool is invoked with the CMD parameter.
Example: CMD(''CAT D DSNDB06'') SILENT
Specifying SILENT implies acknowledgment of the Db2 Admin Tool copyright.