Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• This APAR is opened to add stored procedures and views that
  can be used by JDBC and ODBC drivers to rerieve schema-based
  (catalog) metadata.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 users that connect to DB2 using      *
  *                 the DB2 Universal Driver JDBC or ODBC        *
  *                 drivers shipped with DB2 UDB Version 8.      *
  ****************************************************************
  * PROBLEM DESCRIPTION: New functionality is needed to provide  *
  *                      a uniform view of metadata information  *
  *                      from the DB2 catalog, for use by        *
  *                      the DB2 Universal Driver.               *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 Universal Database for Linux, Unix, and Windows has added
  new implementations of the JDBC and CLI drivers, referred to
  collectively as the DB2 Universal Driver.  This implementation
  requires a uniform method for retrieving metadata information
  from the catalogs of any of the potential target systems that
  it may connect to, including DB2 for OS/390 and z/OS.  The
  uniform implementation will take the form of a set of stored
  procedures with identical signatures that create result sets
  that similar to the output defined for the JDBC and ODBC
  schema metadata methods.
  
  Additionally, a uniform method is needed for formatting
  SQLCODE messages on each platform.  DB2 typically returns
  an SQLCA structure containing an SQLCODE, and associated message
  tokens.  To format the message text for the SQLCODE, with the
  the message tokens requires the aplication to invoke a local API
  (such as DSNTIAR).  To provide a more uniform method for remote
  clients to accomplish this, another stored procedure is needed.
  

Problem conclusion

Temporary fix

Comments

• This APAR introduces 13 stored procedures.  Twelve of these
  procedures provide the ability to generate a result set that
  corresponds to the Schema Metadata APIs documented in the
  JDBC and ODBC specifications.  These procedures will be used
  by the JDBC and ODBC drivers provided in the DB2 Universal
  Driver.
  
  An additional procedure introduced by this APAR formats SQLCODE
  message text, given input fields from a DB2-generated SQLCA.
  The procedure provides a method to invoke the DSNTIAR message
  formatter via an SQL CALL statement.  No result set is returned.
  The output message is returned via a VARCHAR output parameter.
  This procedure is not used by ODBC.
  
  Following are the stored procedures provided:
  
  
  SYSIBM.SQLCOLPRIVILEGES
  SYSIBM.SQLCOLUMNS
  SYSIBM.SQLFOREIGNKEYS
  SYSIBM.SQLPRIMARYKEYS
  SYSIBM.SQLPROCEDURECOLS
  SYSIBM.SQLPROCEDURES
  SYSIBM.SQLSPECIALCOLUMNS
  SYSIBM.SQLSTATISTICS
  SYSIBM.SQLTABLEPRIVILEGES
  SYSIBM.SQLTABLES
  SYSIBM.SQLGETTYPEINFO
  SYSIBM.SQLUDTS
  SYSIBM.SQLCAMESSAGE
  
  After applying this APAR, the JCL job named DSNTIJMS must be
  customized and run to create these procedures.  This member
  will be found in the db2prefix.SDSNSAMP data set.  The
  instructions for customization are in the header of that job.
  The job should be  run by a user with SYSADM authority,
  preferably the Install SYSADM userid.  For sites that do not
  need these stored procedures, it is not necessary to customize
  and run DSNTIJMS.
  
  If DSNTIJMS is run, it will create a number of other objects
  in addition to the stored procedured themselves.  Each procedure
  will have a package, as well as a created temporary table for
  storing the result set rows.  Additonally, several indexes are
  created on the DB2 catalog, to improve performance of the
  queries used in the procedures.
  
  Some of these stored procedures are dependent on the DB2 system
  initialization parameter(ZPARM) DESCRIBE FOR STATIC, on install
  panel DSNTIPF being set to YES.  This ZPARM is named DESCSTAT
  in the ZPARM assembly job DSNTIJUZ.  If not already set to YES,
  you should change this ZPARM value to YES, re-assemble and
  re-initialize the ZPARM module before running the DSNTIJMS JCL.
  
  By default, the stored procedures will gather data from the real
  DB2 catalog (schema SYSIBM).  However, some users may have
  created a "shadow" catalog under a different schema, to
  alleviate contention on the real catalog.  To direct the stored
  procedures to use the shadow catalog, the BIND PACKAGE commands
  in job DSNTIJMS should all be changed so that the BIND option
  QUALIFIER(SYSIBM) is changed to specify the schema name of the
  shadow catalog.
  
  This PTF also modifies the CREATE PROCEDURE statement for an
  existing DB2-supplied stored procedure, DSNTPSMP (the SQL
  procedure processor).  Names are now included to the parameter
  definition clauses to improve readability and facilitate
  use of DSNTPSMP.
   **** PE03/06/06 FIX IN ERROR. SEE APAR PQ72453  FOR DESCRIPTION
  

APAR Information

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

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

  UQ72081 UQ72082 UQ72083

Modules/Macros

•    DSN@COLN DSN@CPRV DSN@FNKY DSN@PCOL DSN@PRCS
  DSN@PRKY DSN@SPCL DSN@STAT DSN@TBLS DSN@TBPR DSN@TYPE DSN@UDTS
  DSNACOLN DSNACPRV DSNAFNKY DSNAMSTX DSNAPCOL DSNAPRCS DSNAPRKY
  DSNASPCL DSNASPFN DSNASTAT DSNATBLS DSNATBPR DSNATMMC DSNATYPE
  DSNAUDTS DSNTIJMS DSNTIJSG DSNTINS1 HDB7710J JDB6613J
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R610 PSY UQ72081

     UP02/12/27 P F212

• R613 PSY UQ72082

     UP02/12/27 P F212

• R710 PSY UQ72083

     UP02/12/26 P F212

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.

IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.

Product categories:

Software

Data Management

Data Servers (Database Management Systems)

DB2 for z/OS

Software version:

610

Reference #:

PQ62695

IBM Group:

Software Group

Modified date:

2003-08-07