PM86962: SQLCODE670 IN DSNXOPCO WHEN DB2 CHOOSES TO MERGE A TABLE EXPRESSION

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQLCODE-670 from DSNXOPCO can occur when DB2 chooses to merge a
    table expression and there are many references to a long Varchar
    column.
    

Local fix

  • The workaround for this problem is to turn off the hidden zparm
    SPRMTBLVMG, or other way that could force materialization
    instead of merge.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of DB2 10 for z/OS who have        *
    *                 queries using OUTER JOIN                     *
    ****************************************************************
    * PROBLEM DESCRIPTION: SQL code -670 with SQLERRD1 = -97 in    *
    *                      accompanying message DSNT416I           *
    *                      may be issued from DSNXOPCO for a       *
    *                      query using OUTER JOIN even if          *
    *                      the row length of the join result       *
    *                      is under the maximum limit.             *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    SQL code -670 and accompanying message DSNT416I with
    SQLERRD1 = -97 from DSNXOPCO may be issued for a query using
    OUTER JOIN even if the row length of the join result is
    under the maximum limit.  This can occur when the same
    column is referred to multiple times in a query.  A
    calculation of the row length of the join result
    incorrectly added the column length of the same column
    multiple times when determining whether to issue the
    -670 SQL code.  This miscalculation could cause -670
    to be issued erroneously.
    
    Additional Keywords:
    SQLOUTERJOIN SQLFULLJOIN SQLOUTER SQLFULL SQLJOIN
    SQLLEFTJOIN SQLLEFT SQLRIGHTJOIN SQLRIGHT
    SQLCODE670 MSGDSNT416I
    

Problem conclusion

  • Code was added to correct the calculation of the join result
    row length when the query contains OUTER JOIN so that SQL
    code -670 can be issued accurately.
    

Temporary fix

Comments

APAR Information

  • APAR number

    PM86962

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-04-14

  • Closed date

    2013-06-13

  • Last modified date

    2013-07-15

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

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

    UK95094

Modules/Macros

  • DSNXOPCO
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK95094

       UP13/06/28 P F306

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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

A10

Reference #:

PM86962

Modified date:

2013-07-15

Translate my page

Machine Translation

Content navigation