PI74740: PROBLEM USING EXTERNAL STORED PROCEDURE AND JDBC

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Hello,
  we have an external Cobol stored procedure. Using Cobol to call
  it all
  works fine. With JDBC we get the following error:
  "No stored procedure by the name of "CB.ZFP73SPR" having
  compatible
  arguments was found"
  Using SYSIBM.SQLPROCEDURECOLS to check columns/parameters of
  the stored
  procedure shows an additional parameter with COLUMN_TYPE 0 and
  DATA_TYPE 0 (see attached JOBLOG).
  The external stored procedure was created with DB2 V8. A
  DROP/CREATE of
  the procedure in V10&V11 solved the problem.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 10 for z/OS and DB2 11 for  *
  *                 z/OS of ODBC/CLI , IBM Data Server Driver    *
  *                 for JDBC and SQLJ, or IBM Data Server        *
  *                 Driver for ODBC and CLI that connect to      *
  *                 DB2 10 for z/OS or DB2 11 for z/OS.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: SYSIBM.SQLPROCEDURECOLS returned extra  *
  *                      row if the external procedure was       *
  *                      created prior to DB2 9 NFM and then     *
  *                      DB2 system was migrated to DB2 10 z/OS  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  When an external procedure with n parameters was created prior
  to version 9 NFM and then the DB2 system was migrated to DB2
  10 z/OS, DB2 metadata store procedure SYSIBM.SQLPROCEDURECOLS
  should return n rows in the resultset. Instead, it may return
  n + 1 rows. The extra row returned has a COLUMN_NAME of "",
  DATA_TYPE of 0, and COLUMN_TYPE of 0 .
  
  For example a JDBC client tried to find an existing procedure
  "SCH1.PROC1" in the current DB2 version 10 system, where the
  procedure was created on DB2 8 z/OS with 2 VARCHAR
  parameters and a PARAMETER VARCHAR clause specified.
  Because of the extra row, SYSIBM.SQLPROCEDURECOLS cannot
  find a matching procedure with name "SCH1.PROC" and 2
  parameters. SYSIBM.SQLPROCEDURECOLS would return the following
  error message:
  
    No stored procedure by the name of "SH1.PROC1"
    having compatible arguments was found
  

Problem conclusion

• The extra row is removed and correct resultset is returned
  

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:

  UI45040 UI45041

Modules/Macros

• DSN@PCOU DSNAPCOU
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI45040

     UP17/03/14 P F703

• RB10 PSY UI45041

     UP17/03/14 P F703

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.