PI28425: SQLCODE450 WHEN CALLING SQLSP DSNAEXP BECAUSE OF DB2OVRLAY/K RELATED TO THE 7TH PARAMETER

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE450 when calling the DB2 EXPLAIN stored
  procedure DSNAEXP because of storage overlay related to the 7th
  parameter of DSNAXEP
  

Local fix

• ALTER the DSNAEXP SP definition in DB2 to RUN
  OPTIONS=STORAGE(00,00,00,32K)
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 for z/OS users that use Explain   *
  *                 Stored Procedure DSNAEXP will be affected.   *
  ****************************************************************
  * PROBLEM DESCRIPTION: SQLCODE -450 when calling SQLSP DSNAEXP *
  *                      because of a DB2 overlay that is        *
  *                      related to the 7th parameter.           *
  ****************************************************************
  * RECOMMENDATION: INSTALL CORRECTIVE SERVICE FOR APAR/PTF      *
  ****************************************************************
  DB2 for z/OS EXPLAIN Stored Procedure, DSNAEXP, returned
  SQL code -450 because its 7th parameter overlayed storage beyond
  its declared length due to residual data in the local memory.
  
  The problem can be recreated by setting the runtime language
  environment STORAGE option to STORAGE(40,40,40).
  
  Here is an example on the LE STORAGE setting for a PL/I
  user program.
  
  DECLARE PLIXOPT CHAR(20) VAR INIT('STORAGE(40,40,40)')
          STATIC EXTERNAL;
  
  Here are the outputs:
  
  Before fix:
  BACK FROM DSNAEXP-EXPLAIN STORE PROCEDURE
            SQLCODE =           -450
            SQLSTATE= 39501
            ERROR TXT= DSNAEXP.7
  
  After fix:
  
  BACK FROM DSNAEXP-EXPLAIN STORE PROCEDURE
            SQLCODE =              0
            SQLSTATE= 00000
            ERROR TXT=
  
  Also, DB2 EXPLAIN stored procedure, DSNAEXP, is deprecated.
  Starting in Version 10, use of the EXPLAIN privilege or SQLADM
  authority is recommended instead.
  
  Additional Keywords:
  DSNAEXP SQLCODE450 SQLSTATE39501
  

Problem conclusion

• DB2 code has been corrected to use a fixed size string copy
  when we want to copy the string to the 7th parameter storage
  of DSNAEXP.
  

Temporary fix

Comments

• ×**** PE15/03/12 PTF IN ERROR. SEE APAR PI36863  FOR DESCRIPTION
  

APAR Information

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

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

  UI24674

Modules/Macros

•    DSNAEXPL
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI24674

     UP15/02/11 P F502

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.