PI59192: DSNTIJRT RETURNS RC=12 WITH SQLCODE=-4700 IN V10CM8, ISSUED FROM BIND PACKAGE(DSNASPCC) MEMBER(DSNASPCU) BY 16/07/07 PTF PEREMOVE

APAR status

• Closed as fixed if next.

Error description

• When DSNTIJRT is run in DB2 z/OS V10 CM8 mode, it returns RC=12
  with SQLCODE=-4700 from BIND PACKAGE(DSNASPCC) MEMBER(DSNASPCU)
  statement with RDS code=61, issued by DSNHSM1.
  The same issue does not occur when running DSNTIJRT in V10 NFM.
  Customer found that it is from apar PI38267 (UI28627) where
  the SQL syntax is not acceptable to V10 CM8.
  search keywords: SQLCODE4700 SQLCODE4700N RDSCODE61
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 10 for z/OS in conversion   *
  *                 mode after migration from DB2 8 for z/OS     *
  ****************************************************************
  * PROBLEM DESCRIPTION: One or more of these symptoms occur     *
  *                      when running DB2 10 in conversion       *
  *                      mode(CM) after migration from DB2 8:    *
  *                      1) Installation job DSNTIJRT job step   *
  *                      DSNTRIN ends with return code 12        *
  *                      because SQLCODE -4700 is received from  *
  *                      the BIND PACKAGE(DSNASPCC)              *
  *                      MEMBER(DSNASPCU) request.               *
  *                                                              *
  *                      (2) Calls to DB2-supplied stored        *
  *                      procedure SYSIBM.SQLSPECIALCOLUMNS      *
  *                      using a static path fail with SQLCODE   *
  *                      -443, SQLSTATE 38113 and SQL message    *
  *                      text ROUTINE SQLSPECIALCOLUMNS          *
  *                      (SPECIFIC NAME SQLSPECIALCOLUMNS) HAS   *
  *                      RETURNED AN ERROR SQLSTATE WITH         *
  *                      DIAGNOSTIC TEXT -525 27                 *
  *                      location.DSNASPCC.DSNASPCU.             *
  *                      The SQLCODE -525 means THE SQL          *
  *                      STATEMENT CANNOT BE EXECUTED BECAUSE    *
  *                      IT WAS IN ERROR AT BIND TIME FOR        *
  *                      SECTION = sectno PACKAGE = pkgname      *
  *                      CONSISTENCY TOKEN = contoken            *
  *                                                              *
  *                      (3) Calls to DB2-supplied stored        *
  *                      procedure SYSIBM.SQLSPECIALCOLUMNS      *
  *                      using a dynamic path fail with SQLCODE  *
  *                      -443, SQLSTATE 38113 and SQL message    *
  *                      text ROUTINE SQLSPECIALCOLUMNS          *
  *                      (SPECIFIC NAME SQLSPECIALCOLUMNS) HAS   *
  *                      RETURNED AN ERROR SQLSTATE WITH         *
  *                      DIAGNOSTIC TEXT -4700. SQLCODE=-443,    *
  *                      SQLSTATE=38113 where SQLCODE -4700      *
  *                      means ATTEMPT TO USE NEW FUNCTION       *
  *                      BEFORE NEW FUNCTION MODE                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  After migration from DB2 8 to DB2 10 conversion mode, job
  DSNTIJRT completes with return code 12 because the bind package
  request for DBRM DSNASPCU fails with SQLCODE -4700 (attempt to
  use new function prior to new-function mode). Other DSNTIJRT
  processing completes successfully. The new function SQL syntax
  in the DSNASPCU DBRM is actually new function with respect to
  DB2 9 but unavailable in DB2 10 conversion mode after migration
  from DB2 8.  The new function SQL syntax was introduced
  to DSNASPCU after the end of service for DB2 8 on April
  30, 2009.  The new function SQL pertains only to the
  CLI/ODBC function SQLSpecialColumns() and the JDBC method
  getBestRowIdentifier(), which both make highly specialized
  use of the SYSIBM.SQLSPECIALCOLUMNS stored procedure that
  is unlikely to be used during DB2 10 conversion mode.
  
  Because DB2 8 is out of service, no code changes are provided
  to address the DSNTIJRT symptom.  Instead, use the following
  manual workaround:
  
  * If you do not use the SYSIBM.SQLSPECIALCOLUMNS stored
  procedure, no further action is required.  The DSNASPCU DBRM
  will bind normally when you rerun DSNTIJRT as part of the
  procedure for enabling DB2 10 new-function mode.
  
  * If you do use the SYSIBM.SQLSPECIALCOLUMNS stored procedure,
  customize and run the following job to bind the package and
  bypass the bind error while in DB2 10 conversion mode:
  
  //BNDASPCU JOB ,
  //JOBLIB DD DISP=SHR,DSN=prefix.SDSNLOAD
  //DSNTIRU EXEC PGM=IKJEFT01,DYNAMNBR=20
  //SYSTSPRT DD  SYSOUT=*
  //SYSPRINT DD  SYSOUT=*
  //SYSUDUMP DD  SYSOUT=*
  //SYSTSIN  DD  *
    DSN SYSTEM(ssid)
  
    BIND PACKAGE(DSNASPCC) MEMBER(DSNASPCU) +
         ACTION(REPLACE) ISOLATION(UR) QUALIFIER(SYSIBM) +
         RELEASE(COMMIT) ENCODING(UNICODE) +
         SQLERROR(CONTINUE) +
         LIBRARY('prefix.SDSNDBRM')
  
  Before running the job:
  - add a valid job card
  - change 'prefix' to the prefix of your DB2 10 SMP/E
    target libraries
  - change 'ssid' to the DB2 subsystem name
  
  After you have bound from DSNASPCU with the SQLEROR(CONTINUE)
  option, if calls to the SYSIBM.SQLSPECIALCOLUMNS stored
  procedure fail with one of the SQLCODE -443 symptoms noted in
  the Problem Description section, take one of the following
  actions:
  
  * In DB2 10 new-function mode, rerun the above bind command to
  refresh the package bound from DSNASPCU
  
  * In DB2 10 conversion mode, contact IBM for further guidance.
  

Problem conclusion

Temporary fix

Comments

• The error return code 12 from installation job DSNTIJRT is
  bypassed by performing a manual bind using bind option
  SQLERROR(CONTINUE) for DSNASPCU.
  

APAR Information

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

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

Fix information

Applicable component levels

• RA10 PSN

     UP