Displaying detailed package information
Db2 Admin Tool can report detailed information for one or more packages, including SQL and EXPLAIN information.
About this task
- Package details, such as the package type and version.
- SQL information and EXPLAIN information, if available. (EXPLAIN information is displayed for only those SQL statements that have EXPLAIN data in the package owner's plan table. EXPLAIN information is also included for queries that are eligible to be offloaded to an accelerator.)
Procedure
To display detailed package information:
- On the DB2 Administration Menu (ADB2) panel, specify option 1, and press Enter.
- On the System Catalog (ADB21) panel, specify option K (for packages), and press Enter.
-
On the Packages (ADB21K)
panel, specify the
DET line command next to the package for which you want to see the
details.
The package information is displayed on the Details for object(s) (ADBPD) panel.
SQL statements are displayed in lines that are 72 bytes long. If a statement contains host variables, the variable name and data type are displayed on a separate line.
In the following example of this package information, the SQL information is collapsed.
Figure 1. Details for object(s) (ADBPD) panel ADBPD DD1A Details for object(s) Command ===> Scroll ===> PAGE Commands: SAVE ZOOM _ Details for package : SPADJB009012345678901(*1) in collection : SCADJB009(*2) _ Package information Package type . . . . . . . . . . : Native SQL routine package Version . . . . . . . . . . . . : MYVERSION Authorization ID of owner . . . : J148286 Owner type . . . . . . . . . . . : Auth ID Authorization ID of creator . . : VNDR001 Created timestamp . . . . . . . : 2012-08-23-05.38.20.906062 Latest BIND timestamp . . . . . : 2012-11-06-16.42.39.648458 Version under which package bound: V11 Qualifier for unqualified SQL . : J148286 Operative status of package . . : Package is valid and operative Resource and authorization check : At BIND time Size of the base section (bytes) : 4272 (in EDM pool during execution) Average DML section size (bytes) : 5220 (loaded when needed during exec) Package bound with EXPLAIN . . . : Yes SQLERROR specified at BIND time : No - SQLERROR(NOPACKAGE) specified BIND or REBIND from remote loc. : No - (RE)BIND was from a local system Remote packages creation method : Source of the package . . . . . : Number of enabled/disabled conn. : 0 Data concurrency . . . . . . . . : B - not required Effect on blocking . . . . . . : Allow blocking for ambiguous cursors DEGREE of I/O parallelism . . . : 1 - parallel I/O inhibited Group member that performed BIND : Dynamic SQL rules . . . . . . . : Use definers authid and authorizations Re-optimize SQL at execution time: 1 - use exec. time variable values once Defer prepare . . . . . . . . . : Yes - prepare is defered to OPEN time Keep prepared dynamic SQL stmts : No - are destroyed at COMMIT Protocol for 3 part names . . . : D - uses DRDA Function resolved at . . . . . . : 2012-11-06-16.42.39.648445 Optimizer hint identifier . . . : THIS IS THE OPTHINT FOR JB Encode CCSID . . . . . . . . . . : 37 Write group buffer pool pages . : Immediate write ROUNDING option used on last bind: Round Down Concurrent Access . . . . . . . : W - Wait for release of write lock Last date objects used . . . . . : 01/01/0001 SQL path for resolving UDT,UDF,SP: "J148286","SYSADM","USRT001" Precompiler related information: Timestamp of precompilation . : 0001-01-01-00.00.00.000000 Consistency token in hex . . . : 1941FCD60BBACC4D SQL escape character . . . . . : ' (apostrophe) Decimal point character . . . : . (period) Host program language . . . . : Remotely bound, trigger, or SQL package Mixed character set . . . . . : N Decimal 31 used . . . . . . . : Yes Katakana . . . . . . . . . . . : No Resource allocation information: Resources are released . . . . : At plan deallocation time Isolation level . . . . . . . : Read stability Temporal special register information: Sensitive to SYSTEM_TIME . . . : Yes Sensitive to BUSINESS_TIME . . : Yes Sensitive to GET_ARCHIVE . . . . : Yes Bind options: Access path reuse behavior . . : No - does not reuse paths Package compat level behavior : V12R1M502 Static SQL DESCRIBE requests . : Yes - creates DESCRIBE SQLDA _ SQL statements in package: SCADJB00901234567890.SPADJB009012345678901234(*3) _ SQL in statement: 39 _ Explain information for SQL statement: 39 _ SQL in statement: 39 _ SQL in statement: 40 _ SQL in statement: 42 INSERT INTO SCADJB00.TBADJB00 (ORDER_WAREHOUSE_ID) VALUES ('EEE') _ Explain information for SQL statement: 42 The operation is INSERT, UPDATE or DELETE. Inner join or no join. -------------------------------------------------------------------------- Table Schema . . . : SCADJB00 Table Name . . . . : TBADJB00 Query number . . . : 42 Access type . . . : Plan number . . . : 0 Query block no . . : 1 Match columns . . : 0 _ SQL in statement: 39 CLOSE C1 _______________________________________________________________________________ _ Long names legend (*1) - SPADJB00901234567890123456E (*2) - SCADJB00901234567890 (*3) - SCADJB00901234567890.SPADJB00901234567890123456E.MYVERSIONThe following example shows the displayed package information for a package that contains a query that is marked to be offloaded to an accelerator. Accelerated queries have an access type of A (accesstype = 'A').
Figure 2. Details for object(s) (ADBPD) panel ADBPD min ----------------- DD1A Details for object(s) ---------------- 15:55 Command ===> Scroll ===> PAGE Commands: SAVE ZOOM _ Details for package : ADM1PK01 in collection : RRLCOL _ Package information _ SQL statements in package: RRLCOL.ADM1PK01 _ SQL in statement: 1686 SELECT * INTO :policyid Var Char(10) , :coverage Integer , :start Var Char(49) , :COUNT Integer , :timeid Var Char(49) FROM SCADM101.TBADM101 _ Explain information for SQL statement: 1686 Query is marked to be offloaded to an accelerator. Query qualifies for routing to an accelerator. -------------------------------------------------------------------------- Table schema . . . : SCADM101 Table name . . . . : TBADM101 Query blk no . . . : 1 Access type . . . : A Accelerator name . : ZGRYPHON Location name . . : DB2EC1 Reason code . . . : 0 _______________________________________________________________________________ ******************************* Bottom of data ********************************