Support for UNLOAD NOPAD when making catalog copies
PH58226 - December, 2023
When you use Db2 Admin Tool to make a
catalog copy and select the UNLOAD/LOAD method, the UNLOAD utility option NOPAD is now used
by default. NOPAD ensures that space is used more efficiently for VARCHAR data.
Also, when making catalog copies, the usability of selecting utility options is improved.
When you specify UO (utility options) on the Create Catalog Copy and Bind Batch Jobs (ADB2CCJ) panel, only UNLOAD options are now listed.
Those options are the only relevant utility options for making catalog copies:
REST service enhancements: creating a REST service in batch and deploying a REST
service
PH58019 - November, 2023
Db2 Admin Tool can now generate a batch
job to create a REST service. Use the new BB (bind in batch) line command on the REST Services (ADB21RS) panel to
generate such a job. This job binds the service with the specified SQL statement.
You can also now use Db2 Admin Tool to
deploy your REST service to a new location or collection. Use the new BC (Bind Copy) line
command on the REST Services (ADB21RS) panel to bind a copy of the service to a new location or collection.
The migrate (MIG) function in Db2 Admin Tool is enhanced to improve productivity. When you use MIG, you can now save your selected
objects and related options (migrate parameters, templates, batch parameters, GEN options,
and utility options). You can later resume your specifications and continue to add more
objects and edit the options without losing the previously specified list of objects and
options.
To save and restore your MIG specifications, use the new SAVE and RESTORE commands on the
following panels:
Migrate Table Spaces (ADB28S) panel
Migrate Tables (ADB28T) panel
Migrate Views (ADBP8V) panel
Additionally, the MIG function can now be accessed from any panel in Db2 Admin Tool by using the new MIGRATE primary
command. This command opens a panel where you can select an object type to use as the base
for migration:
Ability to specify MAXPARTITIONS when moving tables from multi-table table spaces to
UTS
PH58158- November, 2023
In Db2 Admin Tool, you can move tables
from multi-table table spaces to partition-by-growth universal table spaces (UTS) by using
the MOVETB command. This command is available as both a line command and primary command on
the Table Spaces (ADB21S) panel and the Databases (ADB21D) panel. When using this comment, you can now
specify a maximum number or partitions on the DB2 Admin Move Table Options (ADB2MVT1) panel. Previously, the MAXPARTITIONS value
was always 1 when using MOVETB.
For expression-based indexes, you can now view and alter the application compatibility
(APPLCOMPAT) value in Db2 Admin Tool.
You can view the APPLCOMPAT value on any of the following panels:
Indexes (ADB21X) panel
From the main menu, select option 1 (DB2 system
catalog) and then option X
(Indexes). Then, scroll right on the Indexes (ADB21X) panel to see the new
APPLCOMPAT
column:
ADB21X in DD1A Indexes Row 796 from 1000
Command ===> Scroll ===> PAGE
More: < >
Commands: DIS STA STO ALL XSPACE
Line commands:
T - Tables D - Database G - Storage group P - Plans C - Columns
DIS - Display index space STA - Start index space STO - Stop index space
? - Show all line commands
Index
Select Index Name Schema Regenerated Timestamp APPLCOMPAT
* * *
------ ------------------ -------- -------------------------------- ----------
IXBOC5P1 SHBOC5P2 2023-07-20-11.35.24.697491028320
IXBOC5P3 SHBOC5P2 2023-07-20-11.35.24.895470727539
IXBOC5P2 SHBOC5P2 2023-07-20-11.35.24.801770510742
IXBOC061 SHBOC061 2022-12-09-11.19.50.846979880859
IXBOC7I4 SHBOC7I1 2023-06-22-19.13.55.955152574707 V13R1M503
IXBOC7I3 SHBOC7I1 2023-06-22-19.13.55.943132144531
IXBOC7I1 SHBOC7I1 2023-06-22-19.13.55.919794389160
IXBOC062 SHBOC061 2022-12-09-11.19.51.141838157226
IXBOC7I2 SHBOC7I1 2023-06-22-19.13.55.932016939453
IXBOC5P1 SHBOC5P1 2023-07-20-11.35.23.637435011718
IXBOC5P2 SHBOC5P1 2023-07-20-11.35.23.863702606933
IXBOC5P3 SHBOC5P1 2023-07-20-11.35.23.978512189941
Notice
that for indexes that are not expression-based, this column is blank.
Environment Variables (ADB21ENV) panel
On the Indexes (ADB21X) panel, specify the
ENV line command on an expression-based index. Then, on the Environment Variables (ADB21ENV) panel, scroll right to view the new
APPLCOMPAT column:
ADB21ENV -------------- DD1A Environment Variables ---------- Row 1 to 1 of 1
Command ===> Scroll ===> PAGE
More: <
Line commands:
I - Interpretation
Application
Sel Schema CCSID Created Timestamp APPLCOMPAT
* * * *
---- -------- ----------- -------------------------------- ----------
SHBOC7I1 37 2023-06-21-13.16.04.841985141113 V13R1M503
******************************* END OF DB2 DATA *******************************
Interpretation of an Object in SYSINDEXES (ADB21XI1) panel
On the Indexes (ADB21X) panel, specify the
I (Interpret) line command on an expression-based index. Then,
on the Interpretation of an Object in SYSINDEXES (ADB21XI1) panel, scroll down to see the
new APPLCOMPAT level
field:
ADB21XI1 -------- DD1A Interpretation of an Object in SYSINDEXES ------- 16:26
Command ===>
More: - +
Key target count . . . . : 7
Extended index type . . : Index on scalar expression
Index compression active : Yes
Environment ID . . . . . : 2412 APPLCOMPAT level . . . : V13R1M503
COPY YES RBA/LRSN . . . : 000000000545632610E1
Hash overflow index . . : No Sparse index . . . . . : No
ROWID for LOB columns . : 95238C2A0BEBB02E8C9C0113B1400100000000007237
Max data set size (KB) . : 0 - Non-partitioned index
Page format . . . . . . : A - Absolute page numbering
Limit key columns . . . : 0 - Index is not a key-partitioned secondary index
Index status . . . . . . : Blank - Definition is complete
Index status reason . . : Blank - Definition is complete
Number of partitions . . : 0 - Non-partitioned index
Primary allocation (4KB) : PRIQTY not specified
Storage allocation type : I - Implicit, storage group used
Storage group name . . . : SYSDEFLT
ICF catalog name . . . . : DD1A
Free page frequency . . : 0
Percentage free space . : 0
Group BP cache option . : Blank - Only changed pages are cached
You can alter the APPLCOMPAT value by using the AL or ALT line commands:
AL
When you issue the AL line command, you can specify a new APPLCOMPAT value on the
Alter Index (ADB21XA) panel:
ADB21XA n ------------------ DD1A Alter Index ---------------- Row 1 to 1 of 1
Command ===> Scroll ===> PAGE
ALTER INDEX SHBOC7I1.IXBOC7I4 (Nonpartitioned )
Buffer Pool . . . BP16K1 Close Rule . . . . NO Copy Allowed . . . YES
Piece Size . . . . 4194304 Cluster . . . . . NO Padded . . . . . . NO
Compress . . . . . YES Regenerate . . . . ApplCompat . V13R1M503
Sel Part Pqty Sqty FreePg %Free Erase ST VCAT Stogroup GBPCache
--- ------ ----------- ------- ------ ----- ----- -- -------- -------> --------
0 -1 -1 0 0 NO I DD1A SYSDEFLT CHANGED
******************************* END OF DB2 DATA *******************************
Notice
that you can also regenerate the index by specify Y in the new
Regenerate field. You can regenerate an expression-based
index regardless of whether you change the APPLCOMPAT value.
ALT
When you issue the ALT line command, you can specify a new APPLCOMPAT value on the
Redefine Index (ADB21XAR) panel:
ADB21XAR --------------- DD1A Redefine Index --------------- Row 1 to 3 of 28
Command ===> Scroll ===> PAGE
Commands: NEXT ORIGINAL
Line commands: nnn A|D - Sequence & order R - Remove the column I - Include
A - Ascending D - Descending RA - Random U - Update expression/XML pattern
? - Show all line commands
ALTER INDEX SHBOC7I1 . IXBOC7I4 >
ON SHBOC7I1.TBBOC7I1
Owner . . . . . . > Owner type . . . . . (U/R)
Unique . . . . . . YES Where Not Null . .
Buffer Pool . . . BP16K1 Close Rule . . . . NO Copy Allowed . . YES
Piece Size . . . . 4194304
Partitioned . . . Padded . . . . . . NO Compress . . . . YES
Exclude Null Keys NO ApplCompat . V13R1M503
Select Column Name Col Type Length Scale N ColSeq Ord OldSeq Ord
* * * * * * * * *
------ ------------------ -------- ----------- ------ - ------ --- ------ ---
<EXPRESSION> 1
<EXPRESSION> 2
<EXPRESSION> 3
In either case (AL or ALT), regenerating the index will leave it in REBUILD-pending status.
Therefore, ensure that you select the option to rebuild the index.
Also, if you use Db2 Object Comparison Tool to compare
expression-based indexes, the APPLCOMPAT values are compared. If you do not want to compare
these values, you can ignore them by using the new ignore SYSENVIRONMENT APPLCOMPAT.
Db2 13 FL504: Utility object-level history
support
PH57761, PH57762 - November,
2023
Db2 13 FL 504Db2 13 function level 504
introduces utility object-level history. With this enhancement, you can collect object-level
information along with utility execution information for IBM®Db2 utilities.
In Db2 Admin Tool, you can view utility history by
selecting option Z and then option 2H. This
functionality is now extended so that you can also view utility object-level history. You
can either select an object for which you want to view the utility history or select a
utility execution for which you want to view the objects processed:
To view the utility history for an object, you can either use the new UH (Utility
History) line command on a catalog panel (for databases, table spaces, tables, or indexes)
or specify new object-level criteria on the existing Display Utility History (ADB2Z2H) panel:
ADB2Z2H n DD1A Display Utility History 15:15
Option ===> 1
More: +
Enter option and optional criteria: UTILITY_HISTORY zparm:OBJECT
1 - Display utility history rows
Utility . . . . . . (RUNSTATS, REORG, ? to lookup))
Event Id . . . . .
Inserted by . . . . (DB2 or other product name)
Userid . . . . . .
Jobname . . . . . .
Utility ID . . . .
Return code >= . .
Status . . . . . . (A-Active, E-Ended, T-Term., F-Forced)
Start time within . (e.g. n DAYS or n MONTHS)
Age >= . . . . . . (e.g. n DAYS or n MONTHS)
CPU time >= . . . . (in msecs)
Elapsed time >= . . (in msecs)
Includes obj type . (T-Tablespace I-Index)
Database LIKE .
Space name LIKE .
2 - Delete Utility history rows
Status . . . . . . (E-Ended, T-Terminated, F-Forced)
Age >= . . . . . . (e.g. n DAYS or n MONTHS)
Predefined queries:
3 - Show top 25 completed utilities with longest CPU time
4 - Show top 25 completed utilities with longest elapsed time
4 - Show top 25 completed utilities with longest elapsed time
5 - Show all utilities group by utility
6 - Show top 25 active utilites with longest elapsed time
7 - Show top 25 utilities with return code >= 8
Start time within . (e.g. n DAYS or n MONTHS)
Create own query:
8 - Use the SELECT prototype on SYSUTILITIES
Display exceptions:
9 - Show utilities that differs from the average
Start time within . (e.g. n DAYS or n MONTHS)
CPU time >= . . . . (in msecs, default 1000)
Std deviations . . (1-9, default 3)
In either case,
the Utility History (ADB2Z2H1) panel displays the utility history
for that object or objects.
To view the objects processed by a utility execution (the utility object-level history),
specify the new S line command next to a particular utility event on the Utility History (ADB2Z2H1) panel. The new Utility History - Objects (ADB2Z2HO) panel displays the object-level
information for that utility event:
ADB2Z2HO XC1B Utility History - Objects Row 1 to 2 of 2
Command ===> Scroll ===> PAGE
More: >
Commands: REFRESH
Line commands: ICS - Show SYSCOPY DEL - Delete row I - Interpret
S - Show object SP - Show partitions D - Database
? - Show all line commands
Sel Event Id Utility T DB name SP name Part I Count Elapsed Time
* * * * * * * * *
--- <--------- ------> - -------- -------- ---- - <--------- ------------
499061 REBUILD T DSN05020 L1PUPTWX 0 1 0 ?
499061 REBUILD I DSN05020 IL0030B1 0 1 0 ?
******************************* END OF DB2 DATA *******************************
You can also use Db2 Admin Tool to
manage the size of the utility object-level history. When you use the existing delete option
[option 2 on the Display Utility History (ADB2Z2H) panel] to delete
old rows in the utility history (in SYSIBM.SYSUTILITIES), the corresponding rows for utility
object-level history (in SYSIBM.SYSOBJEVENTS) are now automatically deleted for you. You
will be prompted before the deletion
occurs:
ADB2CONF XC1B Delete confirmation 12:50
Confirm the deletion of rows from SYSUTILITIES
Rows to be deleted: 48998
Corresponding rows from SYSOBJEVENTS will be also deleted
Select a choice
1. Continue with deletion
2. Cancel
Utility
history behavior is controlled by the Db2 subsystem parameter UTILITY_HISTORY.
You can modify this parameter in Db2 Admin Tool on the System Parameters — System Parameters (ADB2ZZMN) panel. The new value for utility
object-level history, OBJECT, is supported. When you specify OBJECT, both utility history
and utility object-level history are collected.
Db2 13 FL504: Support for
statement-level invalidation
PH57761, PH57762 - November, 2023
Db2 13 FL 504Db2 13 function level 504 provides the
ability to enable packages for statement-level invalidation. As part of this new function,
the Db2 catalog table SYSPACKAGES
has a new possible value in the OPERATIVE column, R, to indicate that the package is in
rebind-advisory status. Db2 Admin Tool
supports this new column value and will display it on the Packages (ADB21K) panel in the OP (OPERATIVE) column when
applicable. If you issue the I, DET, or BET line command on a package with OPERATIVE=R, an
explanation of the R value is included in the detailed information.
Db2 13 FL 504Db2 13 function level 504 provides
increased capacity for Db2 traces.
Starting in function level 504, the maximum number of OP buffers for trace monitoring with
READA calls is increased from 8 to 16. Db2 Admin Tool supports this enhancement. On the
Trace Functions (ADB2Z2TS) panel, you can specify an OP buffer
value of OP1 up to OP16 in the DEST field:
A revoke impact report helps you determine how authorizations and database objects will be
affected by revoking an authorization. Previously, you could request these reports only on
the Revoke object Privileges panels. With this enhancement, you
can now request these reports by using a batch job. A sample job is provided in member
ADBRIPB in the sample library. Edit this job as needed to add your revoke requests. Then,
run it to view the requested revoke impact reports.
With this APAR, you can now better control who can update grant management objects. Use the
new GRANT privileges on GM programs field on the Product Parameters (CCQPPRD) panel in IBM Tools Customizer for z/OS (TCz) to specify the authorization IDs and
roles that you want to allow to update the grant management tables:
To allow for this authorization control, the packages for the grant management programs are
now bound in its own collection. The users that you specify (in the GRANT
privileges on GM programs field) are given access to that collection. By
default, this collection name is ADBLGM. If you want to specify a different collection name,
specify it in the BIND: Collection for GM packages field on the same
panel.
When you use Db2 Object Comparison Tool, you specify the
objects to be compared by selecting either DDL, objects from the Db2 catalog, or a compare version file.
With this enhancement, you now have another option: you can identify the source and target
objects in a comparison by using an SQL SELECT statement against the Db2 catalog. Db2 Object Comparison Tool uses all of the objects that are
returned by the query for the source or target definition.
Using a SELECT statement to identify the source and target objects can be more efficient
than individually specifying objects from the Db2 catalog. Additionally, when using a
SELECT statement, you can use clauses to filter the object list.
New CM batch options to include foreign key changes when comparing objects
PH55583 - October, 2023
When running the CM batch interface JCL procedure (GOCCM) to compare objects, you can now
specify whether foreign key changes should be included in the generated DDL. To do so, use
the following new CM batch parameters:
SOURCE_GEN_FOREIGN_KEYS
TARGET_GEN_FOREIGN_KEYS
This functionality is similar to the GENRELS parameter in the JCL that is generated by
Db2 Object Comparison Tool.
Object Compare report can include original names of added objects
PH56749 - September, 2023
If you use Db2 Object Comparison Tool to compare
objects, the resulting changes might include adding objects to the target. In this case,
those new object names might be masked and therefore different than the original object
names in the source. To help you determine which source object was added, you can now
request that Object Comparison Tool report the
original object name in addition to the new name when running change management (CM) batch.
To do so, set the new CM batch parameter REPORT_ORIGINAL_NAMES_ADDED_OBJECTS to YES. When
this parameter is set to YES, the summary report includes the original name (under
Source Object) and the new masked name (under Target
Object), as shown in the following example:
COMPARISON SUMMARY REPORT
=========================
Obtyp Source Object Target Object Result Object type
----- ------------------------------- ------------------------------- -------------------- ---------------
X MPX8130.XDEPTNEW MPT8130.XDEPTNEW Added Index
Db2 Admin Tool now supports the following
LOAD utility options:
DEFINEAUX
DRAIN_WAIT
FLOAT
FORCE
INDEXDEFER
IGNORE
NOCHECKPEND
NOSUBS
OVERRIDE
PREFORMAT
RETRY
RETRY_DELAY
SWITCHTIME
UPDMAXASSIGNEDVAL
You can specify these options on the Specify Utility Options - LOAD (ADB2UTC) panel and, when applicable, on the Table Utilities - LOAD with Cross Loader (ADBPUTLC) panel
The values for RBALRSN_CONVERSION and SHRLEVEL are also updated to match those values that
are currently supported by Db2.
Additionally, the following Change Management (CM) batch options are added for LOAD utility
options:
In
Db2 Admin Tool, you can now generate and
execute GRANT statements for groups of objects. You no longer have to manually execute
individual GRANT statements per object for each specified user or users. This new feature is
called grant management.
When you specify the objects in grant
management, you can use wildcard characters in the names, so that many objects can be
included in the group of objects for which you want to manage privileges. Additionally, you
can optionally set a specific time frame for which you want a privilege to apply by
specifying start and end timestamps. Db2 Admin Tool generates REVOKE statements when
needed, for example, when a privilege is set to end. You can optionally preview all
generated GRANT and REVOKE statements before running them.
To use this new feature,
select option GM on the System Administration (ADB2Z) panel. This option is available only if
grant management is enabled during customization with TCz. Also, the Db2 subsystem parameter
REVOKE_DEP_PRIVILEGES must be set to SQLSTMT or NO.
The resulting Grant Management Menu (ADB2ZGM) panel displays the available grant management
options:
Figure 1. Grant Management Menu (ADB2ZGM) panel
ADB2ZGM n ----------------- DD1A Grant Management Menu ------------------ 13:53
Option ===>
Db2 System: DD1A
Db2 SQL ID: ADM001
G - Groups GO - Group objects
P - Privileges GP - Group privileges
S - SSIDs PP - Pending privileges
Enter standard selection criteria:
Name . . . . > Creator . . . >
Type . . . . AUTHID . . . >
Qualifier . . > SSID . . . .
Ends in <=. . > (e.g. n DAYS or n MONTHS)
From this panel, you can define your groups of objects and the privileges that you
want to grant, including any time frames for those privileges. Based on your specifications,
Db2 Admin Tool generates the GRANT and
REVOKE statements as pending privileges. You must run these statements (the pending
privileges) to implement the authorizations.
This grant management feature is also
available as a REST service. The name of the provided REST API and associated stored
procedure is ADBGMSP.
COPY, REORG, and RUNSTATS utility jobs that are generated by Db2 Admin Tool can now include LISTDEF lists.
Using LISTDEF simplifies the generated JCL job and can improve performance in some cases by
promoting parallel processing.
When you use the UTIL primary command to request that COPY, REORG, or RUNSTATS be run on
multiple objects, the generated utility job can now include a LISTDEF statement, which
defines the list of objects. The job then includes a single COPY, REORG, or RUNSTATS
statement that references that list. Previously, these generated jobs included a new utility
statement for each object.
To enable this use of LISTDEF, you must set the new option Generate utilities
with LISTDEF to YES on the Utility Settings (ADBPPIU2) panel. The default value is
NO, which preserves the existing behavior of generating a new
utility statement for each object. For COPY and REORG, you must also set up the required
data set templates. RUNSTATS does not require templates.
This enhancement applies only to running utilities by using panels. Running utilities by
using change management (CM) batch is not affected.
You can now view and change the APPLCOMPAT for views by using Db2 Admin Tool.
The APPLCOMPAT and current schema values are added to the Views (ADB21VV) panel and the Tables, Views, and Aliases (ADB21T) panel. Scroll right (PF11) to
see these new columns, as shown in the following examples:
ADB21VV n ------------------------- DD1A Views -------------- Row 1 to 5 of 5
Command ===> Scroll ===> PAGE
More: <
Commands: GRANT UTIL MIG ALL
Line commands:
C - Columns A - Auth L - List S - Table space D - Database
T - Tables Y - Synonyms SEL - Select prototyping DDL - Show DDL
? - Show all line commands
Current
Sel Name Schema APPLCOMPATSchema
* * * *
----- ------------------ -------- ---------- --------
ADBCHGV1 TS6462 V13R1M502 TS6462
ADBPROFILE TS6462 V13R1M502 TS6462
ADBPROFILE_PARMS TS6462 V13R1M502 TS6462
EEE_OBJ_DATA TS6462 V13R1M502 TS6462
EEE_OBJ_DIRECTORY TS6462 V13R1M502 TS6462
******************************* END OF DB2 DATA *******************************
ADB21T in DD1A Tables, Views, and Aliases Row 1 from 69
Command ===> Scroll ===> PAGE
More: < >
Commands: GRANT MIG ALL CT
Line commands:
C - Columns A - Auth L - List X - Indexes S - Table space D - Database
V - Views T - Tables K - Packages Y - Synonyms SEL - Select prototyping
? - Show all line commands
Current
Sel Name Schema APPLCOMPATSchema
* * * *
----- ------------------ -------- ---------- --------
ADBCATVT TS6462
ADBCHG TS6462
ADBCHGAT TS6462
ADBCHGM TS6462
ADBCHGOCM_LOCAL TS6462
ADBCHGS TS6462
ADBCHGSR TS6462
ADBCHGT TS6462
ADBCHGTG TS6462
ADBCHGV1 TS6462 V13R1M502 TS6462
ADBCHK TS6462
ADBCHKPT TS6462
The ENV line command, which displays environment variables, is extended so that you can now
use it for views and indexes. You can issue this line command on any of the following
panels:
Views (ADB21VV) panel
Tables, Views, and Aliases (ADB21T) panel
Indexes (ADB21X) panel
When you issue the ENV line command, the following information is displayed:
ADB21ENV -------------- DD1A Environment Variables ---------- Row 1 to 1 of 1
Command ===> Scroll ===> PAGE
Line commands:
I - Interpretation
Application Original Host D M S S M D D T F C
Sel Schema CCSID CCSID Language P S D S D A F F F S F R
* * * * * * * * * * * * * * * *
---- -------- ----------- ----------- -------- - - - - - - - - - - - -
TS6462 37 37 NONE P N A A N 1 S A N E
******************************* END OF DB2 DATA *******************************
Additionally, you can regenerate a view to change the APPLCOMPAT value. On the Views (ADB21VV) panel or the Tables, Views, and Aliases (ADB21T) panel, use the AL line command
and then specify the new APPLCOMPAT value in the generated ALTER VIEW statement.
Usability improvements for horizontally scrolling on table display panels
PH37650, PH55431 - July, 2023
To help you more efficiently navigate table display panels that are horizontally
scrollable, the new primary commands LEFT and RIGHT are added. The syntax is as follows:
After you specify LEFT or RIGHT, specify one of the
following options to indicate how far you want to scroll:
MAX or M
The maximum amount
number
The number of logical pages that you want to scroll
For example, the following command specifications scroll the panel as indicated:
New command to update the high-level qualifier for Db2 catalog data sets
PH52946 - July, 2023
You can use the new CATHLQ primary command to update the high-level qualifier that Db2 Admin Tool is to use during the current
session for data sets for the Db2
catalog. When you issue this command, the following panel is displayed so that you can
update or correct the high-level qualifier as needed:
ADB2MSPC Db2 Space Manager
Command ===>
The Db2 Space Manager is collecting VSAM information for one or
more DB2 catalog page sets. Specify the high-level qualifier
for the Db2 catalog that you are copying. Generally, this value
is the Db2 subsystem ID. You can also use CATHLQ command to
update the high-level qualifier.
Db2 catalog high-level qualifier . .
Usability improvements for altering and renaming columns
PH54154, PH54232 - June, 2023
You can now alter and rename a column by using the AL line command. Previously, you had to
use the ALT line command for these changes.
When you use the AL line command to make these changes, select the new
ALTER/RENAME COLUMN option on the Alter Table (ADB21TA) panel. This enhancement builds upon the
recent alter enhancement: Usability improvements for altering objects.
When you run the REORG utility or UNLOAD utility from Db2 Admin Tool, the data sets that are needed by
those utilities can now be allocated with the DSNTYPE=LARGE parameter. This parameter is
useful for utility jobs that require large data sets.
To enable the use of DSNTYPE=LARGE, you must recustomize Db2 Admin Tool and set the new TCz parameter DSNTYPE=LARGE
enabled to YES. (YES is the default value.) When you
set this value to YES, larger values can be specified for the space parameters on the Batch Job Utility Parameters (ADB2UPA) panel.
Improved usability for changing table space and index attributes at the object
level
PH54716 - June, 2023
When you create or alter table spaces or indexes, you can specify object attributes on the
related panels. For partitioned objects, the process of specifying or changing attributes at
the object level is now simplified with the following enhancements:
When you use the AL line command to change certain attributes at the table space or
index level, that change can now be done in one ALTER statement. Previously, the attribute
value was changed for each partition. For example, suppose you want to change the PQTY
attribute of a partitioned table space and therefore, enter the following value on the
first row of this panel:Figure 2. Alter Table Space (ADB21SA) panel
ADB21SA n -------------------- DD1A Alter Table Space ------- Row 1 to 5 of 16
Command ===> Scroll ===> PAGE
Line commands:
D - Display Database I - Interpret
ALTER TABLESPACE : ADM001.TSQA2502 (No. of Partitions: 16)
Buffer Pool . . . . BP0 Close Rule . . . NO Max Rows . . 255
Lock Size . . . . . PAGE Lock Part . . . . NO Lock Max . . 0
Max Partitions . . . LOG . . . . . . . YES Insert Algo . 0
SEGSIZE . . . . . . 0
E T S
S Part Pqty Sqty FP PF PFU Cmp R M T VCAT Stogroup GBPCach DSSIZE
* * * * * * * * * * * * * *
- ------ <------- <------ --- -- --- --- - - - -------- -------> ------- ------
All Part 5000
1 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
2 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
3 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
4 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
5 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
...
Previously, this specification resulted in an ALTER TABLESPACE PART statement for
each partition of the table space. With this APAR applied, the same panel specification
results in only one ALTER TABLESPACE statement that changes this attribute for the
entire table space.
This AL enhancement applies to the following attributes:
Table space attributes [on the Alter Table Space (ADB21SA) panel]
PQTY
SQTY
FREEPAGE
PCTFREE
PCTFREE FOR UPDATE
COMPRESS
ERASE
TRACKMOD
STORTYPE
VCATNAME
STORNAME
GBPCACHE
Index attributes [on the Alter Index (ADB21XA) panel]
PQTY
SQTY
FREEPAGE
PCTFREE
ERASE
VCATNAME
STORNAME
GBPCACHE
When you create a partitioned table space or index or use the ALT line command to change
one of these objects, the DEFAULT row on the resulting panel now displays the object-level
attribute values. If no object-level attribute was specified, the value is blank, as shown
in the following example:Figure 3. Revised Create Table Space (ADB21SAR) panel
ADB21SAR ------------------ DD1A Create Table Space -------- Row 1 to 5 of 20
Command ===> Scroll ===> PAGE
Commands: NEXT ORIGINAL BALANCE MAKEPBG MAKEPBR MAKEPBR2
Line commands: I - Insert part D - Delete part U - Update part
C - Clear data R - Repeat part ? - Show all line commands
CREATE TABLESPACE: TESTKW IN DSNDB04 (create PBR)
Numparts . . . . . 20 LOB . . . . . NO
Define . . . . . . YES LOG . . . . . YES
Member Cluster . . NO SEGSIZE . . . . 64 CCSID . . . . EBCDIC
Buffer Pool . . . . BP2 Close Rule . . YES Max Rows . . 255
Lock Size . . . . . ANY Lock Part . . . NO Lock Max . . SYSTEM
Max Partitions . . 0 PAGENUM . . . . A Insert Algo .
C E T S
S Part Pqty Sqty FP PF PFU O R M T VCAT Stogroup GBPCach DSSIZE
- ------ ---------- ------- --- -- --- - - - - -------- -------> ------- ------
Default: F N 4 G
1 -1 -1 0 10 0 N Y I DD1A SYSDEFLT CHANGED
2 -1 -1 0 10 0 N Y I DD1A SYSDEFLT CHANGED
3 -1 -1 0 10 0 N Y I DD1A SYSDEFLT CHANGED
4 -1 -1 0 10 0 N Y I DD1A SYSDEFLT CHANGED
5 -1 -1 0 10 0 N Y I DD1A SYSDEFLT CHANGED
Previously, this DEFAULT row displayed the values for the first partition. This
enhancement affects the following panels:
Create Table Space (ADB21SAR) panel
Redefine Table Space (ADB21SAR) panel
Create Index - Space (ADB21XAS) panel
Redefine Index - Space (ADB21XAS) panel
When you use ALT to change table spaces or indexes, any null attribute values are now
displayed as blanks. These null values were previously displayed as question mark
characters (?). For example, if an object was created prior to Db2 12, some attributes at the object
level might be null. Such values are now displayed as blanks.
The Compress field is removed from the following table space
panels, because it is redundant with the Cmp or
CO (Compress) column on these panels:
Alter Table Space (ADB21SA) panel
Redefine Table Space (ADB21SAR) panel
Create Table Space (ADB21SAR) panel
For example, in the following panel, notice that the COMPRESS
field that was below the SEGSIZE field has been removed:Figure 4. Revised Alter Table Space (ADB21SA) panel
ADB21SA n -------------------- DD1A Alter Table Space ------- Row 1 to 5 of 16
Command ===> Scroll ===> PAGE
Line commands:
D - Display Database I - Interpret
ALTER TABLESPACE : ADM001.TSQA2502 (No. of Partitions: 16)
Buffer Pool . . . . BP0 Close Rule . . . NO Max Rows . . 255
Lock Size . . . . . PAGE Lock Part . . . . NO Lock Max . . 0
Max Partitions . . . LOG . . . . . . . YES Insert Algo . 0
SEGSIZE . . . . . . 0
E T S
S Part Pqty Sqty FP PF PFU Cmp R M T VCAT Stogroup GBPCach DSSIZE
* * * * * * * * * * * * * *
- ------ <------- <------ --- -- --- --- - - - -------- -------> ------- ------
All Part 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
1 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
2 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
3 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
4 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
5 128 128 0 5 0 NO N Y I DD1A SYSDEFLT CHANGED
...
Db2 13 APAR PH48764 introduced the
following new KEYWORD value for profile attributes: MONITOR product_type
CONNECTIONS FOR SECURITY where product_type can be REST, JDBC, CLI,
DB2CONNECT, DSN, or * . Db2 Admin Tool
added support for this new KEYWORD value. You can specify this value on the Insert/Update Profile Attribute Values (ADB2Z2PA) panel, and if used, see this value
listed on the Display Profile Attributes (ADB2Z2P2) panel.
You can now compare DDL to DDL by using Change Management (CM) batch. Previously, this type
of comparison was allowed only by using the Db2 Object Comparison Tool panels.
To do this comparison, specify the CM batch parameters SOURCE_TYPE='DDL'
and TARGET_TYPE="DDL'. (DDL is a new value for TARGET_TYPE.) To specify the
data sets that contain the DDL, use either the compare input files (SRCIN DD and TGTIN DD)
or the relevant CM batch parameters for the data set names (SOURCE_DSN and TARGET_DSN) .
Db2 Admin Tool provides a new REST API,
ADB2FMM, that masks and formats a given SQL statement.
For example, suppose the following statement and masks are provided as input to
ADB2FMM:
Statement
CREATE DATABASE XXX STOGROUP XXX
Mask
DBNAME:XX*,YY*;SGNAME:XX*,ZZ*
The following SQL statement is returned:
CREATE DATABASE XYZ STOGROUP ZZX
Verification (VER) masks are ignored.
To use this new REST API, you must first create the provided ADB2FMM stored procedure and
then bind it as a REST service. You can then invoke it by using REST calls.
You can use the existing masks SGKEYLABL and TBKEYLABL to overwrite key labels. SGKEYLABL
can overwrite the key label for a storage group, and TBKEYLABL can overwrite the key label
for a table. This APAR adds support to these masks for removing a key label. You can now
specify the value NOKEYLABEL (or NO) for these masks to remove a key label. For example:
Support for displaying index traverse counts and log reading tasks
PH52342 - April, 2023
In Db2, you can display index
traverse counts and log reading tasks by using the DISPLAY STATS command with the
appropriate option: INDEXTRAVERSECOUNT or LOGREADERTASKS Db2 Admin Tool now supports both of these options
as follows:
To view the index traverse count for one or more indexes, navigate to the existing Manage Fast Index Traversal (ADB2Z2I) panel (option
Z.2I from the main panel) and select the new option 3
- Display index traverse count. On the resulting Display Index Traverse Count (ADB2Z2I3) panel, you can enter any additional
parameters before Db2 Admin Tool issues
the command -DIS STATS(INDEXTRAVERSECOUNT).
Alternatively, you can
also view the index traverse count for a database, index, or index partition by using
the new DISITIC line command on any of the following panels:
Databases (ADB21D) panel
Indexes (ADB21X) panel
Index Parts (ADB21XP) panel
To view the log reading tasks, navigate to the System Administration (ADB2Z) panel (option Z from the main menu) and
select the new option LT - Display log reader tasks. On the
resulting Display Log Reading Tasks (ADB2ZLT) panel, you can enter any
additional parameters, such as SCOPE(GROUP), before Db2 Admin Tool issues the command -DIS
STATS(LOGREADERTASKS).
Db2 Admin Tool also added support to limit
the number of messages that are displayed when you display statistics for fast index
traversal. To limit these messages, use the new LIMIT option on the
Manage Fast Index Traversal (ADB2Z2I) panel.
You can alter objects in Db2 Admin Tool by
using either the AL or ALT line command. In certain cases, you can use only AL or ALT
depending on the object and the type of change.
This APAR expands the alter functionality in Db2 Admin Tool, so that you can use ALT for the
following changes:
Adding and dropping check constraints
Dropping foreign keys
Previously, you had to use AL for these changes. Because you can now use ALT for these
changes, they can be combined with other changes at the same time. For example, you can add
a column and add a check constraint in the same change.
To support this enhancement, the Alter - Unique Constraints
(ADBP7CN) panel has the following changes:
It is renamed as Alter - Constraints.
It now lists foreign keys and check constraints, in addition to primary keys and unique
keys.
The ADD primary command is replaced with ADDU (add unique or primary) to distinguish
which constraints are added.
The new primary command ADDC (add check constraints) is added.
The new line command D (drop) is added for dropping foreign key and check
constraints.
Additionally, the Alter Foreign Key Constraint (ADB21TAF) panel now includes
an ENFORCED field, so that you can specify whether you want the new
foreign key constraint enforced or not enforced during normal operations. ENFORCED = YES is
the default.
If you store a change in a work statement list (WSL) and that change requires an unload
operation, Db2 Admin Tool generates an IFF
file. If you then transport the WSL to another system, the IFF file must also be transported
independently. To simplify this process of transporting WSLs, a new option is now available
to embed the IFF file directly in the WSL. When you specify this option, all information in
the IFF file is embedded in the WSL in an encoded format. You can then transport the WSL to
another system without needing a separate IFF file.
This new Embed IFF into WSL option is available on the following
panels:
Generate Compare Jobs (GOC5) panel (when comparing objects)
When you free a package in Db2 Admin Tool,
you can now specify the PHASEOUT value for the PLANMGMTSCOPE option on the Free Package (ADB21KF) panel if you are running Db2 12 function level 505 or higher.
Support for additional values for the QUERY_ACCEL_OPTIONS subsystem parameter
PH52919 - March, 2023
In Db2 Admin Tool, you can now specify the
following values for the QUERY_ACCEL_OPTIONS subsystem parameter (zparm):
8, 9, 10, 11, and 13
You can specify these values on the System Parameters — System Parameters (ADB2ZZMN) panel and the System Parameters - Application Programming (ADB2ZZAP ) panel.
Db2 Admin Tool13.1 supports Db2 13 function level 503. In IBM Tools Customizer for z/OS (TCz) you can specify
V13R1M503 as a valid value in the APPLCOMPAT
field.
Db2 Admin Tool now supports the new
Db2 system profile attribute
SHARE_LOCKS. This attribute allows external stored procedures that meet the profile criteria
to share locks. You can specify this new attribute on the Insert/Update Profile Attribute Values (ADB2Z2PA) panel.
Additionally, you can now issue the Db2 command DISPLAY PROFILE directly from
the Display/Manage Profile Tables (ADB2Z2P) panel to show the current
Db2 system profiling status. To
do so, specify option 7:
ADB2Z2P n DD1A Display/Manage Profile Tables
Command ===>
1 - Display profiles
2 - Display profile attributes
3 - Display profile history
4 - Display profile attributes history
5 - START PROFILE
6 - STOP PROFILE
7 - DISPLAY PROFILE
History display filter:
Created within .
This new option (7) is different than option 1. Option 7 issues the command, while option 1
displays all profile information in DSN_PROFILE_TABLE.
Db2 Admin Tool added support for the COPY
options NOCHECKPAGE and DSNUM. NOCHECKPAGE indicates that COPY is to perform only basic
checks on each page. DSNUM identifies the partition or data set to be copied.
You can specify these options on the Specify Utility Options - COPY (ADB2USC) panel (for table spaces) and the Specify Utility Options - COPY INDEX (ADB2UXC) panel (for indexes). The DSNUM field
can accept a range of partitions.
If you are using Change Management (CM) batch, you can specify the NOCHECKPAGE option by
using the new the UTIL_COPY_NOCHECKPAGE parameter.
If you have Db2 SQL Performance Analyzer (SQL PA) installed, you can now invoke it
directly from Db2 Admin Tool so that you no
longer have to copy information between the two tools.
When viewing a list of packages
on the Packages (ADB21K) panel in Db2 Admin Tool, you can launch SQL PA by issuing the PA line command on a
package. SQL PA panel
Process packages (SQLPA510) will be displayed with the
relevant package information: the collection ID, package name, and version. You can then
perform tasks on this package, such as running EXPLAIN.
Before you can invoke SQL PA from Db2 Admin Tool, SQL PA must be enabled during the customization
of Db2 Admin Tool. You also must have the
fix applied to SQL PA for APAR
PH50522.
You can now call the SQL formatter function in Db2 Admin Tool as a REST API. This function
formats SQL statements for readability. To use this REST API, you must first create the
provided ADB2FMP stored procedure and then bind it as a REST service. You can then invoke it
by using REST calls.