PI73268: ADMIN_INFO_SQL DOES NOT COLLECT UDF DDL

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• ADMIN_INFO_SQL does not collect UDF DDL
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 12 for z/OS customers who use the stored procedure   *
  * SYSPROC.ADMIN_INFO_SQL, either directly with the SQL CALL    *
  * statement, or indirectly using the Query Environment Capture *
  * feature of Data Studio or Optim Query Workload Tuner, or     *
  * from the batch program DSNADMSB.                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * ADMIN_INFO_SQL and DSNADMSB do not support user-defined      *
  * functions (UDF).                                             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * APPLY PTF                                                    *
  ****************************************************************
  The ADMIN_INFO_SQL stored procedure captures statistics about a
  DB2 subsystem, its objects, and applications and returns the
  results in a data set or as a result set. DSNADMSB is a caller
  of ADMIN_INFO_SQL that can be run in a batch environment by
  configuring and executing sample job DSNTEJ6I. Information from
  these tools can help IBM Software Support re-create and
  troubleshoot problems, such as a poorly performing SQL query.
  The output is primarily for the use of IBM Software Support. The
  output might change at any time.
  
  Currently, ADMIN_INFO_SQL and DSNADMSB do not support
  user-defined function (UDF).
  

Problem conclusion

• This APAR enhances ADMIN_INFO_SQL and DSNADMSB to support
  user-defined functions (UDFs).
  
  UDFs referred to in an explained query, and these UDFs'
  underlying objects of type function, global variable, view and
  MQT, will be captured and their CREATE DDLs generated.
  
  Likewise, if a view processed by ADMIN_INFO_SQL or DSNADMSB is
  dependent on a UDF, the CREATE DDLs of this UDF and its
  underlying objects will also be generated.
  
  The EXPLAIN table DSN_FUNCTION_TABLE is used to identify the
  UDFs referred to in a query. If this table does not exist, a
  warning (RETURN CODE=4), together with message DSNA687I, are
  returned.
  
  ADMIN_INFO_SQL or DSNADMSB also uses the SCHEMA_NAME,
  SPEC_FUNC_NAME, and FUNC_VERSION columns from the
  DSN_FUNCTION_TABLE to retrieve information about a function from
  the SYSROUTINES catalog table. If the function is not found in
  SYSROUTINES, a warning (RETURN CODE=4) and a message indicating
  that this function is not processed or not found are returned.
  When this occurs, verify that the FUNC_VERSION column in
  DSN_FUNCTION_TABLE is correct. If not, do one of the following
  after applying this PTF:
  o update FUNC_VERSION with the correct function version
  o EXPLAIN the query again
  and then rerun ADMIN_INFO_SQL or DSNADMSB.
  
  For UDFs that are not dependent upon by a processed view/MQT,
  their CREATE DDLs and their underlying objects' DDLs are printed
  in the DDL file together with the other CREATE DDLs (such as
  CREATE DATABASE, CREATE TABLESPACE, ...). Otherwise, they are
  printed in the 'Start of VIEW/MQT Processing' section, before
  the view/MQT that is dependent on them.
  
  DROP DDLs for UDFs and their underlying objects (when
  applicable) are printed at the top of the DDL file, together
  with the other DROP DDLs.
  
  Currently, view/MQT DROP DDLs are commented and printed in the
  'Start of VIEW/MQT Processing' section. With this enhancement,
  if a view/MQT is dependent on a UDF or global variable, its DROP
  DDL is uncommented and printed at the top of the DDL file,
  before the UDF and global variable DROP DDLs.
  
  If a view/MQT references a UDF or global variable, a new SET
  CURRENT PATH statement is printed after the SET CURRENT SCHEMA
  statement.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI46308

Modules/Macros

• DSN@DMSS DSNADMSS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI46308

     UP17/04/25 P F704

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.