db2 administration tool user's guide · 2020-03-06 · version 12 release 1 ibm db2 administration...

Version 12 Release 1

IBM Db2 Administration Tool for z/OSUser's Guide

IBM

SC27-8898-04

2020-03-05 edition

This edition applies to Version 12 Release 1 of IBM® Db2® Administration Tool for z/OS® (product number 5655-DT2) andto all subsequent releases and modifications until otherwise indicated in new editions.© Copyright International Business Machines Corporation 1995, 2020.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract withIBM Corp.

Contents

About this information.......................................................................................... xi

Chapter 1. Db2 Admin Tool overview......................................................................1What's new in Db2 Admin Tool V12.1......................................................................................................... 1What does Db2 Admin Tool do?.................................................................................................................. 7Support for function levels in Db2 12 for z/OS ...........................................................................................8Database administration and change management solutions.................................................................10Db2 Admin Tool features and benefits..................................................................................................... 11

Db2 Admin Tool features..................................................................................................................... 11Db2 Admin Tool benefits......................................................................................................................16

Service updates and support information.................................................................................................17Product documentation and updates........................................................................................................17Accessibility features.................................................................................................................................17

Chapter 2. Customization.....................................................................................19Setting global variables for Db2 Admin Tool.............................................................................................19Preparing to customize Db2 Admin Tool...................................................................................................21

Set up your environment prior to customization.................................................................................25Worksheets: Gathering required data set names................................................................................26Worksheets: Gathering parameter values for Tools Customizer........................................................ 26

Customizing Db2 Admin Tool.................................................................................................................... 76Roadmap: Customizing Db2 Admin Tool for the first time..................................................................76Roadmap: Migrating to Db2 Admin Tool V12.1 from a previous release........................................... 80Roadmap: Recustomizing Db2 Admin Tool......................................................................................... 82Starting and preparing Tools Customizer for use................................................................................83Specifying the metadata library for the product to customize........................................................... 87Discovering Db2 Admin information automatically.............................................................................88Creating and associating Db2 entries.................................................................................................. 90Defining parameters.............................................................................................................................91Generating customization jobs............................................................................................................ 97Submitting customization jobs............................................................................................................ 98Browsing parameters.........................................................................................................................100Copying Db2 entries...........................................................................................................................100Removing Db2 entries........................................................................................................................101Deleting Db2 entries.......................................................................................................................... 102Displaying customization jobs........................................................................................................... 102Maintaining customization jobs......................................................................................................... 102Using Tools Customizer in a multiple-LPAR environment................................................................ 103Optional customization tasks............................................................................................................ 104

The Db2 Tools Launchpad.......................................................................................................................120Creating the Launchpad table............................................................................................................120Methods for modifying the Launchpad table.....................................................................................122Launching tools.................................................................................................................................. 125

Chapter 3. Getting started................................................................................. 127Db2 Admin Tool panels........................................................................................................................... 127

Types of panels.................................................................................................................................. 127Finding the panel ID...........................................................................................................................130Db2 Admin Tool commands.............................................................................................................. 130Determining valid values....................................................................................................................133

iii

Filtering data on panels..................................................................................................................... 135Sorting display data........................................................................................................................... 136Refreshing data on panels................................................................................................................. 138Displaying complete field values....................................................................................................... 139Checking the status of Db2 Admin Tool............................................................................................ 139DB2 Administration Menu panel (ADB2)........................................................................................ 140Setting panel display options.............................................................................................................142

Tutorial.....................................................................................................................................................142Running queries................................................................................................................................. 142Running utilities................................................................................................................................. 144Granting authorizations..................................................................................................................... 147Binding plans and packages.............................................................................................................. 148Displaying detailed information about an object.............................................................................. 151Reverse engineering objects..............................................................................................................152

Chapter 4. Db2 management..............................................................................157Settings and appearance.........................................................................................................................157

Changing colors and highlights..........................................................................................................157Changing defaults.............................................................................................................................. 158Changing alter options....................................................................................................................... 163Changing batch parameters...............................................................................................................163Changing options for change functions............................................................................................. 163Changing display options................................................................................................................... 164Changing installation default parameters......................................................................................... 164Generating parameters......................................................................................................................164Changing migrate settings................................................................................................................. 164Changing the SQL ID.......................................................................................................................... 164Changing and allocating print data sets............................................................................................ 165Changing Db2 Admin Tool prompt options....................................................................................... 169Managing session scope for global variables.................................................................................... 171Consolidating messages into a single file..........................................................................................172

System catalog queries........................................................................................................................... 174Switching copies of the Db2 catalog................................................................................................. 174Reports from the Db2 catalog............................................................................................................175Display of Db2 catalog columns........................................................................................................ 178Restrictions on Db2 object names.....................................................................................................178

Masks....................................................................................................................................................... 179Mask definitions................................................................................................................................. 179Creating masks in the Change Management repository................................................................... 210Creating masks in a data set..............................................................................................................213Specifying masks................................................................................................................................218Displaying masks in the Change Management database..................................................................220Editing and deleting masks in the Change Management database..................................................221

SQL statements....................................................................................................................................... 221Running SQL statements from screen input..................................................................................... 222Running SQL statements from a data set..........................................................................................223Running or explaining SQL statements from a program file............................................................. 224Building SQL SELECT, INSERT, UPDATE and DELETE prototypes....................................................227Issuing CREATE, DROP, LABEL ON, and COMMENT ON statements................................................233Revoking privileges............................................................................................................................ 254Copying privileges from existing objects to other objects................................................................ 255Revoking system authority from an SQLID....................................................................................... 258

DDL reconstruction (Db2 Admin Tool Reverse Engineering)..................................................................260Generating SQL to re-create a Db2 object........................................................................................ 261Wildcard characters........................................................................................................................... 269ADB2RE stored procedure.................................................................................................................271Sample output from generating SQL................................................................................................. 287

iv

Sample output with the rebind option...............................................................................................287Performance queries............................................................................................................................... 288

Running queries on table spaces without RUNSTATS...................................................................... 289Running queries on indexes without RUNSTATS.............................................................................. 290Running queries on table spaces with more than n percent relocated rows...................................292Running queries on indexes with clustering-level problems............................................................293Running queries on table spaces with more than n percent dropped space...................................294Running queries on Db2 table spaces with locking size = 'S'........................................................... 295Running queries on indexes with n or more levels............................................................................297Running queries on indexes with n or more leaf page distance....................................................... 298Running queries on indexes on tables with fewer than n pages...................................................... 299Running queries on indexes not used by any plan or package.........................................................300Running queries on table spaces containing more than one table.................................................. 302Running queries on table spaces without SPACE information......................................................... 302Running queries on indexes without SPACE information................................................................. 304Running queries on table spaces exceeding allocated primary quantity.........................................305Running queries on indexes exceeding allocated primary quantity.................................................307Running queries on allocated and used space for table spaces...................................................... 308Running queries on table space maintenance recommendations................................................... 310Running queries on index space maintenance recommendations...................................................312Running queries on indexes not used within n number of days....................................................... 314

LISTDEFs and TEMPLATEs...................................................................................................................... 316LISTDEF management....................................................................................................................... 316TEMPLATE management....................................................................................................................327Associating TEMPLATEs with data sets.............................................................................................338Unloading data from LOBs by using the utility template.................................................................. 340Unloading data from an XML column by using the utility template..................................................340

Db2 object changes................................................................................................................................. 341Database changes..............................................................................................................................343Table space changes..........................................................................................................................347Table changes.................................................................................................................................... 358Index changes.................................................................................................................................... 377Altering triggers..................................................................................................................................388Altering views.....................................................................................................................................388Altering foreign keys.......................................................................................................................... 389Altering sequence aliases.................................................................................................................. 390Transferring ownership of objects from one user or role to another............................................... 392Authorization switching..................................................................................................................... 392Implicit LOB and XML table support..................................................................................................395

Details about objects in batch mode.......................................................................................................396Retrieving details about tables in batch mode..................................................................................404Retrieving details about packages in batch mode............................................................................ 405Retrieving details about accelerated tables in batch mode............................................................. 405

Migrating Db2 objects, data, views, and catalog statistics.................................................................... 406Step 1. Specify the objects to migrate or clone................................................................................ 407Step 2. Generate batch jobs.............................................................................................................. 409Step 3. Run the batch jobs.................................................................................................................412Step 4. Optional: Transfer the jobs/work statement list and data to the target system................. 413Step 5. Run the batch define, reload, and optional jobs...................................................................413Work data sets used by the Migrate function....................................................................................414

Work statement lists................................................................................................................................417Work statement list creation............................................................................................................. 418WSL storage........................................................................................................................................420How running a WSL works................................................................................................................. 420WSL management.............................................................................................................................. 421Sample scenario for creating and using a work statement list.........................................................436Running WSLs with the utility template for LOBs............................................................................. 442Running WSLs with the utility template for unloading XML data......................................................443

v

Db2 High Performance Unload within a work statement list............................................................444Manually created WSLs......................................................................................................................446

The Batch Restart programs: ADBTEP2 and ADBTEPA..........................................................................450Introduction to ADBTEP2.................................................................................................................. 450Parameters passed to the ADBTEP2 program.................................................................................. 451Use of a REXX routine with the ADBTEP2 program.......................................................................... 458Data sets that ADBTEP2 uses............................................................................................................461Using ADBTEP2.................................................................................................................................. 462Dialog support for the batch job checkpoint table............................................................................462The ADBTEP2 summary report..........................................................................................................463Restarting an ADBTEP2 job............................................................................................................... 463Using ADBTEP2 with LOBs.................................................................................................................465Overview of ADBTEPA........................................................................................................................467Using ADBTEPA.................................................................................................................................. 467Restarting ADBTEPA after a failure................................................................................................... 468Automated REORG with ADBTEP2.................................................................................................... 469ADBOPT parameters.......................................................................................................................... 469Pausing ADBTEP2 and ADBTEPA.......................................................................................................470

Db2 Admin Tool utilities.......................................................................................................................... 470Running Db2 utilities on table spaces .............................................................................................. 470Using utility options for XML and LOBs............................................................................................. 478Running table utilities........................................................................................................................ 478Using index utilities............................................................................................................................482Running utilities on LISTDEF lists......................................................................................................484Using offline utilities.......................................................................................................................... 486Using DB2 High Performance Unload................................................................................................487

Db2 EXPLAIN........................................................................................................................................... 488Explaining SQL Statements................................................................................................................488Listing rows from a plan table............................................................................................................490Copying PLAN_TABLE contents......................................................................................................... 493Upgrading plan tables........................................................................................................................ 494Creating plan tables........................................................................................................................... 495Creating an index on a plan table...................................................................................................... 496Creating statement tables................................................................................................................. 498Creating function tables.....................................................................................................................499Stabilizing dynamic SQL statements................................................................................................. 500

Db2 systems administration................................................................................................................... 507Displaying threads..............................................................................................................................509Displaying or terminating utilities......................................................................................................510Managing traces................................................................................................................................. 512Displaying or updating the owner of resource limit (RLIMIT) ables.................................................513Stopping Db2......................................................................................................................................517Displaying group information.............................................................................................................518Displaying or managing batch checkpoint tables............................................................................. 518Package management........................................................................................................................521IBM Db2 Analytics Accelerator for z/OS............................................................................................532System parameter management....................................................................................................... 561Displaying global variables and their authorizations........................................................................ 565Granting global variable authorizations.............................................................................................571Revoking global variable authorizations............................................................................................572Revoking all authorizations from a user............................................................................................ 576Granting a set of authorizations to a user......................................................................................... 578Displaying buffer pool status............................................................................................................. 579Altering buffer pools.......................................................................................................................... 579Displaying buffer pool hit ratios.........................................................................................................581Viewing group buffer pools................................................................................................................ 582Altering group buffer pools................................................................................................................ 584Displaying archive log information.................................................................................................... 586

vi

Setting archive log parameters..........................................................................................................587Archiving the current Db2 log............................................................................................................ 587Displaying log information................................................................................................................. 588Changing Db2 system checkpoint frequency....................................................................................588Communications settings.................................................................................................................. 589Displaying DDF................................................................................................................................... 596Displaying or canceling distributed threads......................................................................................598Displaying location details and threads............................................................................................ 599Starting DDF....................................................................................................................................... 600Stopping DDF......................................................................................................................................600Stored procedure management.........................................................................................................601Function management....................................................................................................................... 614Db2 subsystem backup and recovery............................................................................................... 623

Space management.................................................................................................................................626Displaying page set statistics............................................................................................................ 626Resizing page sets..............................................................................................................................628Moving between STOGROUP- and VCAT-related space................................................................... 628Estimating space requirements for tables........................................................................................ 630Estimating space requirements for index spaces............................................................................. 631

Change Management...............................................................................................................................634Change Management terminology.....................................................................................................635The Change Management process.................................................................................................... 636The Change Management main menu panel.....................................................................................640Prerequisites for Change Management............................................................................................. 641Recommendations for designing a Change Management strategy.................................................. 642Change Management scenarios.........................................................................................................642Making changes through Change Management................................................................................ 645Making changes by using the Change Management batch interface............................................... 669Recovering changes that are made through Change Management..................................................814Modifying changes............................................................................................................................. 816Deleting changes................................................................................................................................817Promoting changes............................................................................................................................ 818Importing changes............................................................................................................................. 819Ignores............................................................................................................................................... 822Creating or managing exclude specifications ...................................................................................831Versions.............................................................................................................................................. 831Version scopes................................................................................................................................... 837Tracking changes and changed objects............................................................................................ 842

Determining whether applications need to be rebound.........................................................................844

Chapter 5. Advanced topics............................................................................... 847Db2 Admin Tool application development............................................................................................. 847

The application development process.............................................................................................. 847Defining your own line commands.................................................................................................... 847Sample application............................................................................................................................ 848Types of panels.................................................................................................................................. 849Controlling Db2 Admin Tool processing............................................................................................849Db2 Admin Tool processing flow.......................................................................................................850Panel naming conventions.................................................................................................................851Invoking new applications................................................................................................................. 851Updating rows by using SQL.............................................................................................................. 852Variables in your applications............................................................................................................852

Copies of the Db2 catalog....................................................................................................................... 853Making copies of the Db2 catalog for Db2 Admin Tool.....................................................................854Display Catalog Copy Versions panel (ADB2CCD).........................................................................857Using previously defined copies of the Db2 catalog......................................................................... 858

Db2 Admin Tool distributed support.......................................................................................................858

vii

Accessing a remote subsystem......................................................................................................... 860

Chapter 6. Troubleshooting................................................................................863Gathering diagnostic information............................................................................................................863

TSO ISRDDN.......................................................................................................................................864Db2 Admin Tool messages and codes.................................................................................................... 864

Db2 Admin Tool condition codes.......................................................................................................864Db2 Admin Tool messages................................................................................................................ 865

Tools Customizer troubleshooting........................................................................................................1001Gathering diagnostic information....................................................................................................1001Determining the trace data set name..............................................................................................1002

Tools Customizer messages................................................................................................................. 1002Frequently asked questions..................................................................................................................1073

Chapter 7. Reference....................................................................................... 1075Tools Customizer reference.................................................................................................................. 1075

Tools Customizer terminology and data sets..................................................................................1075System catalog panels.......................................................................................................................... 1079

The System Catalog panel (ADB21).............................................................................................. 1079Option A. Aliases..............................................................................................................................1081Option C. Columns........................................................................................................................... 1082Option D. Databases........................................................................................................................ 1084Option DS. Database Structures......................................................................................................1086Option DSP. Database Structures with Plans and Packages.......................................................... 1088Option E. User-Defined Data Types.................................................................................................1089Option F. Functions.......................................................................................................................... 1092Option G. Storage Groups................................................................................................................1095Option GV. Global Variables.............................................................................................................1096Option H. Schemas.......................................................................................................................... 1097Option J. Triggers.............................................................................................................................1098Option K. Packages.......................................................................................................................... 1099Option L. Collections........................................................................................................................1105Option N. Constraints.......................................................................................................................1105Option O. Stored Procedures...........................................................................................................1106Option P. Plans................................................................................................................................. 1108Option PDC. DB2 Pending Definition Changes................................................................................ 1113Option Q. Sequences....................................................................................................................... 1115Option S. Table Spaces.................................................................................................................... 1116Option T. Tables, Views, and Aliases...............................................................................................1119Option TR. Trusted Contexts........................................................................................................... 1125Option V. Views................................................................................................................................ 1127Option X. Indexes............................................................................................................................ 1128Option XCU. Index Cleanup............................................................................................................. 1131Option Y. Synonyms.........................................................................................................................1132Option AO. Authorization options....................................................................................................1134

Db2 Admin Tool commands..................................................................................................................1135Db2 Admin Tool primary commands...............................................................................................1135Db2 Admin Tool line commands..................................................................................................... 1142

Db2 Admin Tool data type conversions................................................................................................ 1151Db2 Admin Tool with a large number of objects.................................................................................. 1153

ISPF work data sets......................................................................................................................... 1153Output data sets for GEN DDL ........................................................................................................ 1154Other recommendations for a large number of objects................................................................. 1154

Notices............................................................................................................1155

viii

Index.............................................................................................................. 1159

ix

About this information

This information provides instructions for customizing and using IBM Db2 Administration Tool for z/OS, aDb2 catalog administration tool.

These topics are designed to help database administrators, system programmers, and applicationprogrammers perform these tasks:

• Plan for the installation of Db2 Admin Tool.• Install and operate Db2 Admin Tool.• Customize your Db2 Admin Tool environment.• Administrate IBM Db2 by using Db2 Admin Tool• Diagnose and recover from Db2 Admin Tool problems.

Users of this information should understand basic Db2 concepts and facilities.

Always check the Db2 Tools Product publications page for the most current version of this publication:

http://www.ibm.com/software/data/db2imstools/db2tools-library.html

© Copyright IBM Corp. 1995, 2020 xi

xii Db2 Administration Tool User's Guide

Chapter 1. Db2 Admin Tool overviewDb2 Admin Tool is an administration product that can greatly increase the productivity of the entire Db2for z/OS staff (database administrators, system administrators, and application developers).

Db2 Admin Tool uses dynamic SQL to access the Db2 catalog tables and to present the information in aneasy-to-use ISPF interface.

Db2 Admin Tool is one of several IBM tools that can help you manage database administration and thechange management processes.

What's new in Db2 Admin Tool V12.1New and changed technical information is indicated by a vertical bar (|) to the left of a change.

The following technical changes have been made to IBM Db2 Administration Tool for z/OS (Db2 AdminTool) Version 12 Release 1.

March 2020

Enhancement Link to more information Related APAR

The Packages panel (ADB21K) isenhanced to display the applicableplan name when displaying apackage list for a plan.

“Option K. Packages” on page 1099 PH16874

February 2020


ADBTEP2 can now automaticallyretry statements that fail due totimeouts when waiting onresources. To control retry behaviorfor ADBTEP2, use the new CMBatch parametersadbtep2_timeout_retries andadbtep2_timeout_wait_time orspecify values on the Batch JobUtility Parameters panel(ADB2UPA).

• “adbtep2_timeout_retries” onpage 687

• “adbtep2_timeout_wait_time” onpage 687

• “Batch job utility parameters” onpage 474

• “Parameters passed to theADBTEP2 program” on page 451

PH20650

December 2019


When using IBM Db2 ObjectComparison Tool for z/OS and thetarget of an object comparison isautomatically selected, you canspecify that a warning message beissued if the scope is insufficient.This message warns you thatobjects that exist only in the targetmight be dropped.

• “scope_warning” on page 723• “ADB7353W” on page 934

PH19997

© Copyright IBM Corp. 1995, 2020 1

http://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH16874http://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH20650http://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH19997


Support is added for the new Db212 bind optionsCONCENTRATESTMT andAPREUSESOURCE. These optionscan help improve queryperformance. CONCENTRATESTMTcan be specified for the BINDPACKAGE and REBIND PACKAGEcommands. APREUSESOURCE canbe specified with the REBINDPACKAGE and REBIND TRIGGERPACKAGE commands.

• “Binding packages” on page 521• “Rebinding packages” on page

522• Improvements for the dynamic

statement cache (Db2 12 for z/OSdocumentation)

• Static plan stabilityenhancements (Db2 12 for z/OSdocumentation)

PH19997

Support is added for the followingnew Db2 12 options for the FREEPACKAGE command:

• The INVALIDONLY option• The ORIGINAL and PREVIOUS

values for the PLANMGMTSCOPEoption

“Freeing packages” on page 524 PH19997

You can view information aboutprevious and original copies ofpackages.

“Viewing information aboutpackage copies” on page 528

PH19997

November 2019


The APPLCOMPAT bind optionV12R1M506 is supported.

None PH18697

2 Db2 Administration Tool User's Guide

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_dynamicstmtcache.htmlhttps://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_dynamicstmtcache.htmlhttps://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_dynamicstmtcache.htmlhttps://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_staticplanstability.htmlhttps://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_staticplanstability.htmlhttps://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_12_staticplanstability.htmlhttp://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH19997http://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH19997http://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH19997http://www.ibm.com/support/docview.wss?crawler=1&uid=swg1PH18697

October 2019


Support is added for the new Db212 INSERT ALGORITHM attribute ofa table space. You can specify thisattribute when creating or altering atable space in Db2 Admin Tool. Youcan mask or ignore this field. Youcan also compare this field whendoing object comparisons.

• “Creating table spaces” on page236

• “Altering a table space by usingthe AL line command” on page347

• “Redefining partitions in apartitioned table space that usesindex-controlled partitioning” onpage 355

• “Altering a table space by usingthe ALT command” on page 350

• “Adding LOB columns to anexisting table” on page 372

• “Mask definitions” on page 179• “Db2 catalog columns and the

corresponding masks” on page197

• “Verification masks” on page 194• “Ignores” on page 822

PH16862

Support is added for the newBACKOUT option of the LOAD utilitywith RESUME YES.

“Running table utilities” on page478

PH16862

The new Db2 12 dynamic planstability feature is supported. Youcan evaluate the cached dynamicstatements that are likely to becaptured and stabilized, managethe captured and stabilizedstatements, run EXPLAIN against astabilized statement, and show Db2catalog objects that are associatedwith the stabilized statements.

“Stabilizing dynamic SQLstatements” on page 500

PH16862

Chapter 1. Db2 Admin Tool overview 3

http://www.ibm.com/support/docview.wss?rs=434&context=SSZJXP&q=&uid=swg1PH16862http://www.ibm.com/support/docview.wss?rs=434&context=SSZJXP&q=&uid=swg1PH16862http://www.ibm.com/support/docview.wss?rs=434&context=SSZJXP&q=&uid=swg1PH16862

September 2019


The new UNLOAD privilege issupported. You can grant andrevoke these privileges. You canalso see the impact of revoking theUNLOAD privilege for a table,including the impact on theprivileges of other users (alsoknown as cascade revokes).Additionally, if you call the GENcommand for a table on which theUNLOAD privilege has beengranted, the generated SQLincludes the GRANT UNLOADstatement.

“Granting authorizations” on page147“Revoking privileges” on page 254

PH10086

You can specify the DROP_PARToption of the REORG TABLESPACEutility either as a CM batchparameter (util_reorg_drop_part)or on a panel (panel ADB2USO,Specify Utility Options - REORG) .

“util_reorg_drop_part” on page753

PH10086

If you are using Db2 12 for z/OS,the Change Management (CM)batch interface now uses the newDSNUTILV stored procedure to runutilities. Therefore, you can specifyutility statements that are largerthan 32 KB. (If you are using a priorversion of Db2 for z/OS, CM batchuses the DSNUTILU storedprocedure to run utilities.)

“Making changes by using theChange Management batchinterface” on page 669

PH10086

You can specify whether you wantCM batch to run Db2 utilities bycalling a Db2 stored procedure(DSNUTILV or DSNUTILU) or byinvoking them directly in the CMbatch job. Use the new CM batchparameter use_dsnutil_sp tocontrol this behavior.

use_dsnutil_sp PH10086

The Build SQL DELETE Prototypepanel (ADB21TDE) has a newFETCH command that adds theFETCH FIRST n ROWS ONLY clauseto a DELETE statement. This clause,which is new in Db2 12 for z/OS,limits the number of rows that aredeleted in a single SQL statement.

“Build SQL Prototype panel” onpage 230

PH10086

The parser and formatter supportthe following new Db2 12 for z/OSclauses in SELECT statements:OFFSET, FETCH NEXT (or FETCHFIRST) and LIMIT

None PH10086


http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086


Db2 Admin Tool has beenenhanced to reduce resourcecontention and the likelihood of atimeout when running a change. (ACOMMIT was added to releaselocks after updating a change. Theisolation level of the change table ischanged to ROW to reducecontention between two changes.)

None PH10086

The new Db2 catalog columnCOMPRESSRATIO is included in therelevant table space panels: theTable Spaces panel (ADB21S) andthe Interpretation of an Object inSYSTABLESPACE panel(ADB21SI1).

None PH10086

The APPLCOMPAT bind optionV12R1M505 is supported.

None PH10086

July 2019


If you are using IBM Db2 AnalyticsAccelerator for z/OS V7.1.0 (IDAA7.1) or later, you can view theupdated trace details in Db2 AdminTool.

“Retrieving trace data foraccelerators” on page 543“Displaying accelerator status” onpage 535

PH09487

You can specify the following utilityoptions in Db2 Admin Tool whengathering inline statistics:

• INVALIDATECACHE (for theREORG, LOAD, and REBUILDINDEX utilities)

• USE PROFILE (for the LOAD andREORG TABLSPACE utilities)

“Running table utilities” on page478

PH02457

You can specify CM batchparameters for the utility statisticsoptions INVALIDATECACHE andUSE PROFILE.

util_reorg_statistics_invalidatecacheutil_reorg_statistics_use_profileutil_runstats_invalidatecache

PH02457

Db2 Admin Tool now dynamicallyfinds DECP settings by using Db2session variables instead of gettingthem from DSNHDECP module.Therefore, Db2 Admin Tool can getDECP settings regardless of thename of the DSNHDECP module.

None PH02457


http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?uid=swg1PH10086http://www.ibm.com/support/docview.wss?&uid=swg1PH09487http://www.ibm.com/support/docview.wss?rs=434&context=SSZJXP&q=&uid=swg1PH02457http://www.ibm.com/support/docview.wss?rs=434&context=SSZJXP&q=&uid=swg1PH02457http://www.ibm.com/support/docview.wss?rs=434&context=SSZJXP&q=&uid=swg1PH02457

June 2019


Messages about Db2 12 functionlevels are improved.

“Support for function levels in Db212 for z/OS ” on page 8

“ADB1970W” on page 891“ADB1971S” on page 891“ADB1972W” on page 892

PH12055

Object comparison reports areincluded in the ADBMSGS data set.

“Consolidating messages into asingle file” on page 172

“ADB8998I” on page 951“ADB8999I” on page 951

PH12055

April 2019


Support is added for Db2 12 forz/OS continuous delivery.

“Support for function levels in Db212 for z/OS ” on page 8

PH06164

You can set global variables byusing an invocation exit.

“Setting global variables for Db2Admin Tool” on page 19

PH09606

You can specify whether to unloadaltered tables as part of the analyzeprocess by using theunload_altered_tables parameter.

“unload_altered_tables” on page732“Enabling and disabling automaticrecreate, reload, or removal ofaccelerated tables” on page 551

PH00552

The format of messages in theADBMSGS data set is improved forreadability.

“Consolidating messages into asingle file” on page 172

“ADB1609W” on page 884“ADB7953I” on page 941“ADB7954W” on page 942“ADB7955E” on page 942“ADB7956I” on page 942

PH08484

March 2019


For the APPLCOMPAT bind option,you can specify values for Db2 12function levels.

“Binding packages” on page 521“Rebinding packages” on page 522

PH06164

Feb 2019


You can use CM batch to generateCOPY utility statements forpartition-level image copies.

util_listdef_partlevel PH03675


https://www.ibm.com/support/docview.wss?uid=swg1PH12055https://www.ibm.com/support/docview.wss?uid=swg1PH12055https://www.ibm.com/support/docview.wss?uid=swg1PH06164https://www.ibm.com/support/docview.wss?uid=swg1PH09606https://www.ibm.com/support/docview.wss?uid=swg1PH00552https://www.ibm.com/support/docview.wss?uid=swg1PH08484https://www.ibm.com/support/docview.wss?uid=swg1PH06164https://www.ibm.com/support/docview.wss?uid=swg1PH03675

What does Db2 Admin Tool do?IBM Db2 Administration Tool for z/OS, also referred to as Db2 Admin Tool, helps you with the day-to-daytasks that are associated with managing Db2 environments efficiently and effectively.

Db2 Admin Tool simplifies the complex tasks that are associated with safely managing Db2 objects andschema throughout the application lifecycle with the least possible impact to availability. The keyattributes of Db2 Admin Tool include the following:

• Enables quick and easy navigation through the Db2 catalog• Builds and executes dynamic SQL statements without requiring you to know the exact SQL syntax• Manages and tracks changes that are made to Db2 object definitions, resolving any potential conflicts

before execution• Helps build Db2 commands to execute against databases and tables• Builds and executes utility jobs, enabling use of LISTDEFs and TEMPLATEs for increased productivity• Enables you to create, alter, migrate, drop and reverse engineer Db2 objects

The easy-to-use comprehensive features of Db2 Admin Tool can increase your productivity and increasethe reliability of your Db2 objects:

Object management

• Provides in-depth Db2 catalog navigation, which can minimize the time that is required to review thecatalog. Objects in the catalog are shown and interpreted, and relevant catalog information is presentedlogically. You can issue any Db2 command, including BIND, REBIND, and FREE, against selected plansand packages.

Db2 Admin Tool presents the Db2 catalog quickly and logically:

– Displays any object in the catalog– Displays related Db2 objects by using special line commands– Interprets catalog information– Displays the authorization for objects– Displays the static SQL statements from application plans and packages– Displays the DDL for existing views– Runs on one of multiple copies of the Db2 system catalog

• Integrated with Db2 utilities to simplify the creation of Db2 utility jobs. JCL can be generated for Db2utilities and can be executed. The use of LISTDEFs and TEMPLATEs is also supported.

• Enables tasks such as alter, create, drop and migrate of Db2 objects• Allows reverse engineering of Db2 objects• Supports Db2 predictive governing• Enables you to alter the definition of a Db2 table• Enables you to request the Prompt function, which prompts you before a statement is executed

Security management

• Displays authorizations that have been granted on any type of Db2 object, and enables you to REVOKEthese authorizations or GRANT new authorizations

• Provides REVOKE impact analysis to prevent inadvertent data loss when you revoke authorities• Displays the list of secondary authorization IDs and manages SQL IDs

Performance management

• Allows complex performance and space queries


• Contains a built-in EXPLAIN function that allows you to EXPLAIN a query, and provides an interpretationof the PLAN_TABLE output into an easy-to-understand format

• Comes with a set of performance health check catalog queries• Enables you to perform space-related functions such as resizing page sets, lets you move page sets to

and from STOGROUP- and VCAT-defined space, and helps you estimate space allocations for new tablespaces and indexes

• Enables you to create and manage work statement lists (WSLs) and run the WSL as a batch job• Enables you to dynamically manage system parameters

Change management

• Allows you to manage and track changes to Db2 objects• Allows you to register changes to multiple target environments• Allows groups of users to collaborate to build changes by managing information through a series of Db2

tables• Provides a convenient audit trail that can be used to determine the status of objects that are being

changed and where those changes were deployed• Allows you to recover changes and restore database objects to their previous state

System management

• Allows you to display and cancel threads; display and alter buffer pools; display, start, and stop Db2traces; and set and display the logs

• Performs various system administration functions, such as updating RLIMITs and managing DDF tables• Provides a convenient way to administer RLF and DDF tables• Manages stored procedure operations, such as creating, displaying or altering stored procedures,

issuing the Db2 START and STOP STORED PROCEDURE command, and showing statistics for storedprocedures that are accessed by Db2 applications

• Displays current dynamic DSNZPARMs change parameters, generates new DSNZPARM modules withchanges, and activates those changes in Db2

Application management

• Builds and executes dynamic SQL statements without requiring you to know the exact SQL syntax• Runs most Db2 utilities• Enables you to extend existing Db2 Admin Tool applications or to rapidly develop new applications• Allows you to work with a copy of the Db2 catalog to avoid contention and other performance problems

on the actual catalog• Accesses a remote Db2 catalog where a DDF connection exists between systems. This feature enables

you to centrally manage all of your Db2 subsystems with a single Db2 Admin session.• Allows you to execute any dynamic SQL statement through Db2 Admin Tool, or to invoke SPUFI

Support for function levels in Db2 12 for z/OSIBM Db2 Administration Tool for z/OS (Db2 Admin Tool) offers varying levels of support for the functionlevels that are available in Db2 12 for z/OS.

Each Db2 12 function level is categorized into one of the following levels of support:

Supported function levelsEnhancements in the function level are supported by Db2 Admin Tool.


Tolerated function levelsDb2 Admin Tool can run on a member or subsystem at the function level, but it does not support allnew enhancements in the function level.

Function levels that are not toleratedIf you try to run Db2 Admin Tool on a member or subsystem at the function level, results may beunpredictable. However, most Db2 Admin features work.

Implications of running with function levels that are not supported or tolerated

If you run Db2 Admin Tool on a subsystem with a function level that is tolerated but not supported anduse enhancements in that function level, affected objects or attributes might not be handled or displayedcorrectly. For example, if an object is dropped and recreated to implement a change, new attributes forthat object might no longer be defined. If you do not use new enhancements in the tolerated functionlevel, Db2 Admin Tool runs normally.

If try to run Db2 Admin Tool on a subsystem with a function level that is not tolerated, the results may beunpredictable.

Terminology:

Db2 Admin Tool uses the following terms when referring to function levels:

Db2 function levelThe function level at which the Db2 subsystem is currently running. When Db2 Admin starts, it getsthe value from Db2.

For batch jobs that do not connect to Db2, Db2 Admin Tool determines the current function level ofthe subsystem based on the value in the dsnhdecp module. You can override this value by specifying adifferent function level on the Options for Change Functions panel (ADB2PCO) in the Db2 functionlevel field. Jobs that are generated by Db2 Admin Tool use the value from this Db2 function levelfield.

maximum Db2 function level accepted (accept_fl)The maximum function level on which you want to allow Db2 Admin Tool to run, even if that functionlevel is not tolerated or supported. By default, no value is defined.

You can specify this value in one of the following places:

• Option 3 on the Db2 Function Level Confirmation panelwhen you start Db2 Admin Tool (Thisoption sets the maximum Db2 function level accepted to the current Db2 function level.)

• The Max Db2 function level accepted field on the Admin Defaults panel (ADB2P2)• The accept_fl parameter of CM Batch• The accept_fl parameter of GEN

If you choose to specify a maximum accepted function level value that is not tolerated or supported,carefully consider the implications.

target function level (tgtfl and tgt_db2fl)The function level for generated DDL statements. Db2 Admin Tool generates any requested DDLstatements based on the syntax requirements for the target function level. The default value is thecurrent Db2 function level. However, you can specify any function level lower than the current Db2function level.

You can specify this value in one of the following places:

• In the Target Function Level field on the Generate SQL from DB2 catalog panel (ADB2GEN)• The tgtfl parameter for GEN• The tgt_db2fl parameter for CM Batch


Db2 function level confirmation when starting Db2 Admin Tool

When Db2 Admin Tool starts, if the current function level is not supported or tolerated, a confirmationpanel is displayed, as shown in the following example:

ADB2CONF DC1A Db2 Function Level Confirmation 09:35 The Db2 Administration Tool does not tolerate function level 504. Unpredictable errors may occur in some functions. Most functions continue to work as on previous function levels. Please select one of the following choices: Select a choice 1. Exit 2. Continue - I understand and accept the risk 3. Continue as above and do not prompt again for this function level.

Consider the implications before continuing.

If you want to continue, select 2 or 3. If you select either of these options, Db2 Admin Tool sets themaximum Db2 function level accepted (accept_fl) to the current Db2 function level, which is displayed onthe confirmation panel. (In the preceding example, the current Db2 function level is 504.)

If you select 2, the maximum Db2 function level accepted is set for only the current session.

If you select 3, the maximum Db2 function level accepted is stored in the ISPF profile. This confirmationpanel is not displayed again when you start Db2 Admin.

Recommendation: Select 3.

Related referenceAdopting new capabilities in Db2 12 continuous delivery (Db2 12 documentation)“Parameter definitions: Change Management batch interface” on page 674The following Change Management batch interface parameters can be used to control ChangeManagement (CM) actions and settings.

Database administration and change management solutionsIBM solutions help IT organizations maximize their investment in Db2 and IMS databases and addresssome of today's toughest IT challenges.

Database administration and change management are the core responsibilities of the DBA. If notmanaged correctly, database administration and change management can monopolize data centerresources, waste valuable time, and result in the generation of unwanted errors.

In managing critical database assets and the change management process, DBAs are faced with manychallenges. Some examples are as follows:

• Being able to quickly and easily navigate the Db2 catalog• Ensuring that all of the necessary steps are completed when making a change• Managing and tracking the changes to the definitions of database objects• Propagating changes to other database environments• Keeping Db2 software versions current• Managing a corrupt database

Many Db2 Tools products provide database management features that are not available in Db2 itself orthat provide enhancements to capabilities that are built into Db2.

For example, Db2 Admin Tool allows you to navigate the Db2 catalog quickly and easily.

Db2 Admin Tool provides integration with other Db2 Tools products to create extra function with product-specific line commands for editing tables, analyzing the cost of SQL statements, and analyzing potential


https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/wnew/src/tpc/db2z_managenewcapability.html

access path changes. Db2 Admin Tool offers a central, ISPF-based access point for other Db2 Toolsproducts, such as Db2 Table Editor, Db2 SQL Performance Analyzer, and Db2 High Performance Unload .

Db2 Admin Tool is only one of several Db2 Tools products that provide enhancements to the process ofdatabase administration and change management for your databases.

The following Db2 Tools products that can assist with database administration and change management:

• IBM Db2 Object Comparison Tool for z/OS• Db2 Storage Management Utility• Optim™ Test Data Management• Db2 Table Editor• Db2 SQL Performance Analyzer• Db2 High Performance Unload

Db2 Admin Tool features and benefitsThe features of Db2 Admin Tool help you to efficiently and effectively manage Db2 environments.

Db2 Admin Tool features

Display the Db2 catalog tables

Db2 Admin Tool provides extensive support for displaying the Db2 catalog. The scope of information thatcan be displayed is described in this information.

Display any object in the Db2 catalogYou can retrieve catalog data for any Db2 data object. You can specify the data that is retrieved (forexample, you might request that data be retrieved for all databases that are owned by THOMAS andthat have the prefix D402).

Db2 Admin Tool retrieves catalog data by using predefined SELECT statements for the morecommonly used queries. The rows that are retrieved from the catalog are displayed using the ISPFtable-display service. The display panel can be the Db2 Admin Tool default panel, from which you canissue various Db2 Admin Tool line commands, or a panel that you tailor for the result of a particularSQL SELECT. In the latter case, you can use line commands to issue new SQL calls that useinformation from the columns of rows that have been returned.

Display related Db2 objects using line commandsYou can use Db2 Admin Tool line commands to navigate the catalog. For example, from a displaypanel that shows databases, you can use a line command to show all table spaces in one of thedatabases. Then, from the table spaces panel, you could issue a line command to show authorizationsfor a table space or show the status of image copies for the table space.

Display catalog informationYou can request detailed information about any object in the Db2 catalog. A request for details aboutan application plan, for example, returns information such as the plan's owner, latest bind time, andnumber of bytes in the base section.

Show the authorization for Db2 objectsYou can retrieve information about the authorizations for all Db2 objects. From an authorizationdisplay panel, you can then grant and revoke privileges.

Display the static SQL from application plans and packagesYou can display the static SQL statements in a plan or a package, which is useful if you do not haveaccess to a program's source code.

Display the DDL for existing viewsYou can display the SQL source that created a view, which is useful if you do not have access to theCREATE VIEW SQL (DDL) statement.


Run with multiple copies of the Db2 catalogThis function allows you to use the Db2 system catalog, one of the many copies of it, or the catalog ofa remote site. You might choose to use a different copy of the catalog for each weekday, thusassociating a backup with each weekday. Or this feature can allow the system administrator to workon the actual system catalog, while developers use a copy of the catalog, thereby decreasingcontention for the catalog.

Execute dynamic SQL statements

You can issue any dynamic SQL statement from your screen or from a data set. You can build and executean SQL SELECT statement interactively by using line commands.

In addition, by entering required parameters from a panel, you can execute the following SQL statements:GRANT, REVOKE, CREATE, DROP, LABEL ON, and COMMENT ON. This feature allows you to execute thestatements without knowing the exact SQL syntax; Db2 Admin Tool provides guidance for the requiredSQL parameters.

Manage changes to Db2 objects

Use the Change Management function to manage and track the changes that you make to the definitionsof your Db2 objects. You can use the Change Management function to complete all of the steps that aretypically involved with changing database objects:

1. Defining your changes.2. Resolving any conflicts by applying any pending changes for the objects as virtual changes.3. Registering the changes.4. Analyzing the changes to generate a work statement list that applies the changes.5. Running the changes in the correct order.

Change Management also makes it easy to back out completed changes. Making and managing changeswith Change Management provides a convenient audit trail.

Multi-target change enhances change management and provides the following capabilities:

• Changes can be deployed from one central system to multiple target locations.• Status and other information about the target change can be communicated back to the central system.• From one centralized view, DBAs can view of all the changes that have been imported across various

target systems.

Issue Db2 commands against databases and table spaces

You can issue any Db2 command against any database or table space that you have selected using Db2Admin Tool. For example, you can issue the DISPLAY, START, and STOP commands against a database.

Db2 commands are passed to the instrumentation facility interface (IFI), and the result is displayed inISPF browse.

Run Db2 utilities

You can generate the JCL for Db2 utilities and then run them in batch, or you can include the utilitystatements in a work statement list to be run at another time or on another subsystem. This functionapplies to the utilities for storage groups, table spaces, tables, and indexes. For example, you can,generate JCL to run the COPY, REORG, and RUNSTATS utilities for a table space.

The generated JCL consists of a JOB statement, EXEC statement, and all required DD statements. Whenthe JCL is generated, Db2 Admin Tool invokes ISPF edit, which lets you change the JCL, submit it, or copyit to another data set.

You can generate utilities using LISTDEFs and TEMPLATEs.


Issue complex queries

You can run performance and space utilization queries against a database. The data that is returned canhelp you to determine whether you need to:

• Run the RUNSTATS or STOSPACE utilities• Reorganize or redesign parts of your database or indexes• Change the locking rule for tables• Drop an index• Move tables to separate table spaces• Extend the primary allocation for a table space or index• Reduce the size of a table space

Use the EXPLAIN function

The Db2 Admin Tool EXPLAIN function supports the EXPLAIN statement and provides related support.(The EXPLAIN statement gathers information about the access path Db2 chose to process a query.) Byusing the EXPLAIN function you can:

• Create a plan table (PLAN_TABLE) in the wanted database and table space.• Issue an SQL EXPLAIN statement and see the resulting rows in the plan table.• List a plan table to look at rows from previously executed EXPLAIN statements, or rows from BIND and

REBIND operations that were executed with EXPLAIN(YES) specified.

With this function, predefined search criteria help you find rows in the plan table. Predefined searchcriteria exist for application plans, DBRMs, collections, and packages. You can see the access path thatis chosen by Db2 to process queries, and the tables and indexes that are accessed by Db2.

• Use EXPLAIN (ONLY) to populate EXPLAIN tables but not create a package. This option allows EXPLAINto be run when the authorization ID of the bind or rebind process does not have the privilege to executestatements in the package.

• Upgrade a plan table to the current version of Db2.• Look at the Db2 calculated cost.• Create and display the Db2 explain tables.• Insert and work with Db2 optimizer hints in the plan table.

Manage SQL IDs

You can change the current Db2 SQL ID by entering a new one or by selecting one from a list of secondarySQL IDs. Db2 Admin Tool displays a list of SQL IDs that you are allowed to use. The list is created eitherby simulating or invoking the authorization exit in your system.

Perform system administration functions

The system administration functions that you can perform using Db2 Admin Tool include:

• Displaying threads• Displaying and terminating utilities• Displaying and managing traces• Displaying and updating RLIMITs, including the predictive governing limits in Db2• Displaying and altering buffer pools• Displaying and setting archive log parameters and archiving the log• Displaying Db2 system parameters and updating dynamic parameters• For DDF (distributed data facility):

– Starting and stopping DDF


– Displaying and updating the communications database (CDB)– Displaying and canceling distributed threads– Displaying active locations

• Dynamically managing system parameters

Reverse engineer Db2 objects

Reverse engineering generates the SQL statements necessary to re-create a Db2 object. You can reverseengineer the Db2 objects in your database catalog.

Typical uses for the Db2 Admin Tool reverse engineering function include the following tasks:

• Extracting the DDL for an object before changes are made, so that the changes are applied to thecurrent definition and are available for fallback purposes.

• Moving Db2 objects to another Db2 subsystem. By using the reverse engineering function (together withthe table unload and load functions), objects can be moved after a few manual modifications to thegenerated SQL and batch jobs.

The SQL statements can be generated online or with a batch job.

Use the Db2 predictive governing

You can use Db2 Admin Tool to display, insert, update, or delete predictive governing rows in the resourcelimit tables. Furthermore, if Db2 Admin Tool receives a predictive governing warning (SQLCODE +495)when running a dynamic SQL statement, Db2 Admin Tool ask whether the SQL statement should beexecuted or cancelled. If the predictive governing estimates that executing a dynamic SQL statement thatwas issued from Db2 Admin Tool will exceed the error limit (SQLCODE -495), Db2 Admin Tool displays anerror message, and the SQL statement is not executed.

You can use predictive governing limits to prevent users from running wild queries on catalog tables orany other tables that aredisplayed using Db2 Admin Tool. By using predictive governing limits for the Db2Admin Tool package, this type of query can be inhibited either by setting up a predictive governingwarning or an error limit in the resource limit table.

Related information:

Limiting resources for SQL statements predictively (Db2 12 for z/OS documentation)

Alter the Db2 table definition

You can alter the definition of a Db2 table. Permissible changes include the following tasks:

• Changing the database, table space, owner, and the name of the table• Modifying the definitions of table columns• Changing the sequence of the columns in the table• Inserting and dropping columns

Migrate Db2 data to other Db2 systems

You can copy Db2 data to another Db2 system. This is a useful function if you want to create a separateDb2 test system or if you want to move a test system into production. You can also use this function toconsolidate two separate database systems into one.

Extend existing Db2 Admin Tool applications or develop new applications

You can extend Db2 Admin Tool to invoke other ISPF applications that you use for Db2 Admin Tool andapplication development. Some applications that you might want to invoke from Db2 Admin Tool are asfollows:

• Security tools


https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/perf/src/tpc/db2z_predictgovern.html

• Vendor Db2 utilities• Storage management tools

Db2 Admin Tool also enables you to quickly build new ISPF applications for displaying and maintainingDb2 data. Some of the types of data for which you might build such applications are as follows:

• Application definition data• Db2 performance data• Extra security data

A sample application is included with the product to illustrate how you might use Db2 Admin Tool tocreate new applications.

Perform space management functions

Db2 Admin Tool enables you to perform space-related functions such as resizing page sets, moving pagesets to and from STOGROUP- and VCAT-defined space, and estimating space allocations for new tablespaces and indexes.

Create and run work statement lists

Db2 Admin Tool enables you to create and run work statement lists that include sets of operations. Youcan execute the entire set, rerun sets, or capture a set of operations that you create on one system for useon another system.

Launch installed IBM Db2 Tools that have an ISPF interface

You can invoke installed IBM Db2 tools that have an ISPF interface—directly from Db2 Admin Tool. TheDb2 Admin Launchpad provides a convenient way of creating a centralized ISPF table with the names ofyour tools. Then, by selecting an entry in this table, you can easily start one of the tools.

Performance

Db2 Admin Tool is equipped with the following performance features:

• Db2 Admin Tool uses dynamic SQL to access the Db2 catalog, which ensures that Db2 always uses themost efficient access path to the catalog (provided RUNSTATS statistics are available for the Db2optimizer).

• Before Db2 Admin Tool displays information, it does an SQL commit. By doing so, Db2 Admin Toolensures that a user cannot lock the catalog for long periods of time. If an SQL error occurs, Db2 AdminTool rolls back the unit of work before it displays any information.

• Db2 Admin Tool has a default limit of 1000 for fetching rows. This limit helps to prevent time-consuming queries. You can change the default of 1000 for an execution of Db2 Admin Tool if morerows are needed. You can set this value permanently or you can set a parameter in the DB2 AdminDefaults panel (ADB2P2) to reset the default value at the next startup.

• You can use Db2 resource limit facilities (RLF) to limit the amount of CPU time that a dynamic SQLstatement in Db2 Admin Tool can use - either by using the reactive governor facilities of RLF or by usingthe predictive governing facilities.

• Db2 Admin Tool can run on a copy of the Db2 catalog. Besides improving performance, running on acopy of the catalog can reduce contention for the catalog. Db2 Admin Tool provides commands togenerate jobs to create and populate copies of the Db2 catalog.

Security

Db2 Admin Tool does not expose the security of the Db2 system. All Db2 access is controlled by theexisting security provisions of the Db2 system. You can optionally configure Db2 Admin Tool to allowusers to execute DDL generated to re-create views that are dropped as a result of dropping other objects.The user can execute this DDL even if they do not have the direct authority. This is done by using auth-


switching and has its own safeguards to ensure the DDL is not changed from that generated by Db2 AdminTool. A user must have access to a RACF® (or equivalent) profile to use auth-switching.

Db2 Admin Tool benefitsThis section describes a few of the many ways in which Db2 Admin Tool is typically used, and givesexamples of specific applications.

Explore databases

Db2 Admin Tool lets you quickly navigate the Db2 catalog and display tables, table columns, and indexes.If you are authorized by Db2, you can also display the content of tables either by doing a simple list of thetable or by building SQL statements and executing them against a table.

You can use the Db2 Admin Tool functions to explore unknown databases rapidly or get a quick overviewof a database. None of these uses require that you remember the exact syntax of Db2 commands or SQLstatements.

Determine and correct problems

You can use Db2 Admin Tool to identify and fix problems with your databases. With its ability to navigatethe catalog and use Db2 commands on objects, Db2 Admin Tool can help you discover, analyze, and fixdatabase problems in a more user-friendly fashion than is available with native Db2.

Develop small applications

You can use Db2 Admin Tool to rapidly develop small applications. As you become familiar with the tool,you might find the time that it takes to develop small Db2 Admin Tool dialogs is greatly reduced.

Examples

• If you have a tool a

db2 administration tool user's guide · 2020-03-06 · version 12 release 1 ibm db2 administration...

Documents