PI36439: SQLCODE811 -811 WAS ISSUED WHEN RUNNING DSNTIJRT. MSGDSNT002I AT "SYSIBM.SYSROUTINES (2)" CAN BE SEEN WHEN SYSTOOLS.REGSP

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE811 -811 was issued when running DSNTIJRT DSNTRIN.
  Error message DSNT002I "SELECT WLM_ENVIRONMENT FROM
  SYSIBM.SYSROUTINES (2)" can be seen.  This error
  occurred when the input is SYSTOOLS.REGSP and there are
  more than one version of REGSP exists in SYSIBM.SYSROUTINES.
  

Local fix

• Use ALTER PROCEDURE SYSTOOLS.REGSP DROP VERSION xxxxxx
  to drop the REGSP with version not equal to ='V1'.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DSNTRIN, the installation and   *
  *                 configuration tool for DB2-supplied stored   *
  *                 procedures and user-defined functions (UDFs) *
  *                 in DB2 10 for z/OS and DB2 11 for z/OS.      *
  ****************************************************************
  * PROBLEM DESCRIPTION: DSNTIJRT, the DB2 installation job,     *
  *                      ends with return code 12 and one of     *
  *                      the following error messages:           *
  *                                                              *
  *                          DSNT002I DSNTRIN RECEIVED AN        *
  *                            UNEXPECTED SQLCODE OF -811        *
  *                            FROM THE 'SELECT                  *
  *                            RTRIM(WLM_ENVIRONMENT) FROM       *
  *                            SYSIBM.SYSROUTINES (2)'           *
  *                            REQUEST                           *
  *                          DSNT408I SQLCODE = -811, ERROR:     *
  *                            THE RESULT OF AN EMBEDDED         *
  *                            SELECT STATEMENT OR A             *
  *                            SUBSELECT IN THE SET CLAUSE       *
  *                            OF AN UPDATE STATEMENT IS A       *
  *                            TABLE OF MORE THAN ONE ROW,       *
  *                            OR THE RESULT OF A SUBQUERY       *
  *                            OF A BASIC PREDICATE IS           *
  *                            MORE THAN ONE VALUE               *
  *                          when multiple versions exist for    *
  *                          the native SQL procedure            *
  *                          SYSTOOLS.REGSP                      *
  *                                                              *
  *                          DSNT002I DSNTRIN RECEIVED AN        *
  *                            UNEXPECTED SQLCODE OF -811        *
  *                            FROM THE 'SELECT                  *
  *                            RTRIM(WLM_ENVIRONMENT) FROM       *
  *                            SYSIBM.SYSROUTINES (1)'           *
  *                            REQUEST                           *
  *                          DSNT408I SQLCODE = -811, ERROR:     *
  *                            THE RESULT OF AN EMBEDDED         *
  *                            SELECT STATEMENT OR A             *
  *                            SUBSELECT IN THE SET CLAUSE       *
  *                            OF AN UPDATE STATEMENT IS A       *
  *                            TABLE OF MORE THAN ONE ROW,       *
  *                            OR THE RESULT OF A SUBQUERY       *
  *                            OF A BASIC PREDICATE IS           *
  *                            MORE THAN ONE VALUE               *
  *                          when multiple versions exist for    *
  *                          the UDF SYSTOOLS.REG_MATCHES or     *
  *                          SYSTOOLS.REG_MATCHES_NOFLAGS        *
  ****************************************************************
  * RECOMMENDATION: Apply the fixing PTF for this APAR.          *
  ****************************************************************
  Program DSNTRIN is called by job DSNTIJRT to install and
  configure DB2-supplied routines (stored procedures and UDFs),
  including the DB2 objects, such as databases, used by the
  routines.
  
  When DSNTRIN checks if a stored procedure or UDF exists, it
  uses a singleton SELECT statement. The error occurs when
  multiple versions of the native SQL procedure SYSTOOLS.REGSP
  or multiple versions of the compiled SQL scalar function
  SYSTOOLS.REG_MATCHES or SYSTOOLS.REG_MATCHES_NOFLAGS exist.
  Currently, DB2 supplies and supports only a single version,
  its own version, of each of these routines. Other versions
  are considered to be user-defined versions and are not
  permitted because they interfere with serviceability and
  can cause unpredictable results for callers.
  

Problem conclusion

• In response, this APAR modifies DSNTRIN to detect user-defined
  versions of DB2-supplied native SQL procedures and compiled
  SQL scalar functions and report them in a new diagnostic
  message DSNT058I. The check occurs early in DSNTRIN processing,
  and discovery of an user-defined version causes DSNTRIN to
  terminate with return code 12 before other processing occurs.
  
  DSNT058I reports a user-defined version of a DB2-supplied
  native SQL procedure or compiled SQL scalar function and
  whether this version is active or not. DSNTRIN issues this
  message once for each user-defined version. If one routine
  has 2 user-defined versions and another routine has 3,
  DSNTRIN issues this message 5 times.
  
  NOTE: Any version of DB2-supplied native SQL procedures and
  compiled SQL scalar functions other than the version V1 is
  considered a user-defined version.
  

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:

  UI27865 UI27866

Modules/Macros

•    DSN@RIN  DSNTIJRT DSNTRIN
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI27865

     UP15/07/14 P F507

• RB10 PSY UI27866

     UP15/07/14 P F507

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.