PI67611: BINDCOPY END WITH SQLCODE4700 ON A NATIVE STORED PROCEDURE PACKAGE WITH AN ILLEGAL SELECT INTO SYNTAX

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A stored procedure with illegal syntax for SELECT INTO, with two
  
  INTO and an UNION, is created successfully first, but runs into
  
  SQLCODE4700 when do the BIND COPY.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All DB2 users of UNION and SELECT INTO SQL.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * DB2 unintentionally allows UNION or UNION ALL set operators  *
  * in a SELECT INTO SQL statement.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 unintentionally allows UNION or UNION ALL in a SELECT INTO
  
  SQL statement.  UNION or UNION ALL are not allowed directly in
  
  the FROM clause of a SELECT INTO statement.
  
  
  
  UNION or UNION ALL may be specified in a fullselect, but
  
  the FROM clause of a SELECT INTO statement is a single table
  
  reference and not a fullselect.
  
  
  
  Simplified examples of the illegal SQL that DB2 mistakenly
  
  allowed include:
  
  
  
   SELECT C1 INTO :hv1 FROM T1
  
   UNION ALL
  
   SELECT C2 INTO :hv2 FROM T2;
  
  
  
  or
  
  
  
   SELECT C1 INTO :hv1 FROM T1
  
   UNION
  
   SELECT C2 FROM T2;
  
  
  
  DB2 allowed either of these statements though they are
  
  both illegal.  Though they may have completed with SQLCODE=0
  
  indicating a successful completion, their behavior is
  
  undefined.
  

Problem conclusion

• DB2 code is modified to disallow a UNION or UNION ALL in
  the FROM clause of a SELECT INTO statement.
  
  A new DB2 subsystem parameter is added to DSN6SPRM called
  DISALLOW_SEL_INTO_UNION that can be used to specify whether DB2
  should prevent use of a UNION or UNION ALL in the outermost
  FROM clause of a SELECT INTO statement.  Such usage is illegal
  SQL syntax and an application that uses it will fail during
  BIND or REBIND with SQLCODE -109.  Valid settings for
  
  DISALLOW_SEL_INTO_UNION are:
  
  - NO : Allows usage of this illegal SQL syntax
  
         When such usage is encountered during
  
         execution of a BIND or REBIND command,
  
         DB2 will write an incompatibility trace
  
         record, IFCID 376.  Use these
         trace records to identify and correct
  
         applications that are using the illegal
  
         SQL syntax.
  
  - YES: Disallow usage of this illegal SQL
  
         syntax.  Statements that include it
  
         will fail with SQLCODE -109.
  
         ==> This is the default setting.
  
  The default value of DISALLOW_SEL_INTO_UNION is Yes.
  
  The following procedure may help determine which packages are
  
  affected while DISALLOW_SEL_INTO_UNION is set to allow the
  
  illegal SQL.  BIND possible affected packages into a dummy
  
  collection ID with EXPLAIN(ONLY) and monitor IFCID 366 or
  
  IFCID 376 trace records.
  
  
  
  1. Issue this SQL statement to generate a list of BIND commands.
  
  
  
  SELECT 'BIND PACKAGE(DUMMYCOL) COPY(' ||
  
         COLLID || '.' || NAME || ') ' ||
  
  &#160;&#160;&#160;&#160;&#160;&#160; CASE WHEN(VERSION <> '')
  
  &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; THEN 'COPYVER(' || VERSION || ') '
  
  &#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ELSE '' END ||
  
  &#160;&#160;&#160;&#160;&#160;&#160; 'EXPLAIN(ONLY)'
  
  FROM SYSIBM.SYSPACKSTMT
  
  WHERE STATEMENT LIKE '%SELECT%INTO%UNION%';
  
  
  
  This statement will generate results that look like this:
  
  
  BIND PACKAGE(DUMMYCOL) COPY(DSN_DEFAULT_COLLID_PLAY01.PLAY01)
  
  EXPLAIN(ONLY)
  
  
  
  2. Copy the output of the SELECT statement into a BIND job.
  
  Note that the output may be longer than 72 characters and may
  
  require formatting to fit into the BIND job.
  
  
  
  3. Activate and collect IFCID 376 incompatibility trace.
  
  
  
  4. Run the BIND job produced in step 2.
  
  
  
  5. Stop IFCID 376 trace and analyze the output to determine
  
  the affected packages.
  
  
  
  Recompiling the affected program that contains this illegal SQL
  
  would result in DSNH4792I warning messages, indicating that an
  
  SQL statement could cause an error in BIND if
  
  DISALLOW_SEL_INTO_UNION is set to YES.
  
  
  
  The following illegal SQL:
  
  
  
   SELECT C1 INTO :hv1 FROM T1
  
   UNION ALL
  
   SELECT C2 FROM T2;
  
  
  
  may be written this way, which is valid SQL:
  
  
  
   SELECT C INTO :hv1 FROM
  
   (SELECT C1 FROM T1
  
    UNION ALL
  
    SELECT C2 FROM T2);
  
  
  
  The fullselect containing UNION ALL is enclosed in a
  
  nested table expression using parentheses.  The nested
  
  table expression is considered part of a single FROM
  
  clause for the SELECT INTO statement.
  
  ADDITIONAL KEYWORDS: SQLUNION SQLUNIONALL SQLSELECTINTO
                                            SQLCODE109
  

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:

  UI42747

Modules/Macros

• DSNTXAZP DSNTIVIE DSNWZIFC DSNDQWPZ DSNTIVIA DSNTIWMD DSNDSPRM
  DSNTIVMD DSNHXLTR DSNTIWIA DSNHSM1  DSNTIWIE DSNHERRM DSNTINST
  DSNTIDXA DSNTIJUZ DSNTIWIN DSNTIDXB DSNTIPM3 DSNTIPM4 DSNTINMF
  DSNDQW05 DSN6SPRM DSN@XAZP DSNTIVIN DSNTIWMS DSNTIVMS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI42747

     UP16/12/07 P F612

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.