PL31104: SQLCODE904 WHEN ATTEMPTING TO JOIN ON A VIEW THAT HAS TWO SUMS AND NO TEMPORARY TABLESPACES GREATER THAN 4K ARE AVAILABLE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• A VIEW WAS CREATED WITH TWO SUM PARAMETERS. A SUBSEQUENT JOIN
  ON THIS VIEW FAILED WITH 'SQLCODE904 SQLCODE -904 UNSUCCESSFUL
  EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE, REASON 00C90084
  RC00C90084 TYPE OF RESOURCE 00000230, AND RESOURCE NAME 32K'.
  THIS OCCURRED BECAUSE THE JOIN REQUIRED A 32K TEMPORARY
  DATABASE TABLESPACE AND THIS CUSTOMER ONLY HAD 4K TABLESPACES
  AVAILABLE.
  ADDITIONAL SYMPTOMS:  SQLCODE670 DSNXOMW SQLERRD 100
  

Local fix

• DEFINE A 32K WORKFILE IN DSNDB07
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: ALL                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION: RECEIVE SQLCODE-904 UNAVAILABLE         *
  *                      RESOURCE ON DB2 32K WORKFILE            *
  *                      DOING A SELECT ON A VIEW THAT           *
  *                      HAS DECIMAL DATA TYPE COLUMNS.          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  RECEIVE MSGDSNT501I UNAVAILABLE RESOURCE RC00C90084 TYPE230
  NAME 32K WITH SQLCODE-904 INVOLVING VIEW MATERIALIZATION AND
  DECIMAL TYPE COLUMNS. FOR EXAMPLE:
  .
    SELECT VC1, VC2 FROM V1
  .
  COLUMNS VC1 AND VC2 ARE BASED ON COLUMNS IN TABLE T1 AND ARE
  DEFINED AS DECIMAL(7,2).
  .
  IN MODULE DSNXOMW, SCAN ON ALL THE LITERAL LIST IS DONE TO
  DETERMINE THE NUMBER OF COLUMNS AND THE PAGE SIZE. TO DO
  THIS, IT LOOPS THROUGH ALL THE LITERAL NODES AND TOTALS UP
  THEIR LENGTHS. IT THEN DETERMINES WHETHER A 4K OR A 32K
  WORKFILE IS NEEDED BASED UPON THE TOTAL LENGTH (TOTLEN).
  HOWEVER, FOR DECIMAL DATA TYPE COLUMNS, INSTEAD OF JUST ADDING
  THE PRECISION, IT TOOK BOTH THE PRECISION AND THE SCALE
  (HALFWORD) AS THE LENGTH OF THE DECIMAL COLUMN MAKING IT A HUGE
  NUMBER. THIS FORCED DB2 TO USE THE 32K WORKFILE. HOWEVER, IN
  THIS CASE, THE CUSTOMER DID NOT HAVE A 32K WORKFILE CAUSING
  THE ABOVE MESSAGE TO BE ISSUED.
  .
  MAY ALSO GET SQLCODE-670 IF THE TOTLEN EXCEEDS THE 32K WORKFILE
  PAGE SIZE.
  

Problem conclusion

• CODE IN DSNXOMW WILL BE CHANGED TO CORRECTLY USE ONLY THE
  PRECISION WHEN DETERMINING THE LENGTH OF A DECIMAL DATA TYPE
  COLUMN.
  .
  SQLCODE-670 SQLCODE670
  .
  TO MAKE THIS FIX EFFECTIVE FOR STATIC APPLICATIONS, THEY
  MUST BE REBOUND AFTER THE APPLICATION OF THIS APAR.
  

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:

  UL37766 UL37767

Modules/Macros

•    DSNXOMW
  

Fix information

• Fixed component name

  5740 IBM DATABA

• Fixed component ID

  5740XYR00

Applicable component levels

• R210 PSY UL37767

     UP88/10/17 P F808

• R302 PSY UL37766

     UP88/10/14 P F808

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.