PK58016: WHEN ISSUING ALTER INDEX VIA CC390 AGAINST DB2 V8 ON Z/OS, GOT SQLCODE -644 BECAUSE INVALID PRIQTY AND SECQTY.

Obtain the fix for this APAR

APAR status

• Closed as program error.

Error description

• DSNACCOB generates invalid sizes on ALTER INDEX statement:
  
  ALTER INDEX "SAPAR3"."D010INC~1"
  
  USING STOGROUP AR3ESI
  
  PRIQTY 0 SECQTY 4194304
  SQL ERROR DURING EXECUTE IMMEDIATE
  DSNT408I SQLCODE = -644, ERROR: INVALID VALUE SPECIFIED FOR
  KEYWORD OR
  CLAUSE PRIQTY IN STATEMENT CREATE OR ALTER
  DSNT418I SQLSTATE = 42615 SQLSTATE RETURN CODE
  DSNT415I SQLERRP = DSNHSM8 SQL PROCEDURE DETECTING ERROR
  DSNT416I SQLERRD = 2902 0 0 -1 291 0 SQL DIAGNOSTIC INFORMATION
  DSNT416I SQLERRD = X'00000B56' X'00000000' X'00000000'
  X'FFFFFFFF'
  X'0000
  INFORMATION
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 UDB for z/OS Version 8 *
  * and DB2 Version 9.1 for z/OS *
  * who are using the DB2-supplied batch *
  * object maintenance program DSNACCOB. *
  ****************************************************************
  * PROBLEM DESCRIPTION: DSNACCOB generated an ALTER INDEX *
  * or ALTER TABLESPACE statement that *
  * contains an invalid PRIQTY or *
  * SECQTY value. For example: *
  * *
  * ALTER INDEX ... *
  * USING STOGROUP ... *
  * PRIQTY -1288486912 SECQTY 4194304 *
  ****************************************************************
  * RECOMMENDATION: Apply PTF *
  ****************************************************************
  The variables used in calculating the PRIQTY and SECQTY values
  in the generated ALTER TABLESPACE and ALTER INDEX statements
  were declared as int, instead of long int, which caused an
  arithmetic overflow to occur in cases where very large PQTY and
  SQTY values are encountered.
  
  When calculating PRIQTY and SECQTY for the generated ALTER
  TABLESPACE and ALTER INDEX statements, DSNACCOB was multiplying
  PRIQTY and SECQTY by the page size, instead of 4. Since PQTY
  and SQTY in SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART are in
  units of 4KB, PRIQTY and SECQTY must be a multiple of 4 instead
  of page size.
  
  Where applicable, DSNACCOB did not consider the DSSIZE when
  calculating the PRIQTY and SECQTY in the ALTER TABLESPACE
  statement it generated. Neither did it consider the PIECESIZE
  when calculating the PRIQTY and SECQTY in the ALTER INDEX
  statement it generated. This allowed the generated PRIQTY and
  SECQTY values to exceed the value DSSIZE or PIECESIZE.
  
  DSNACCOB also did not consider the case where the value of the
  current PQTY or SQTY in SYSIBM.SYSTABLEPART or
  SYSIBM.SYSINDEXPART is -1. This allowed the generated PRIQTY or
  SECQTY to have a negative value (other than -1).
  

Problem conclusion

• DSNACCOB is changed as follows:
  - To prevent arithmetic overflow, all variables used in
  the calculatin of PRIQTY and SECQTY are changed to long int.
  - To prevent specifying negative values (other than -1) for
  PRIQTY and SECQTY in the generated ALTER TABLESPACE and
  ALTER INDEX statements, the logic which calculates the new
  PRIQTY or the new SECQTY is bypassed if the corresponding
  PQTY or SQTY value in the DB2 catalog is -1.
  
  If PQTY and SQTY are both -1, an ALTER TABLESPACE or
  ALTER INDEX statement will not be generated at all.
  If only PQTY is -1, PRIQTY in the generated ALTER
  statement will be set to -1. On the other hand, if only
  SQTY is -1, SECQTY in the generated ALTER statement will
  be set to -1.
  - Since PQTY and SQTY in SYSIBM.SYSTABLEPART and
  SYSIBM.SYSINDEXPART are in units of 4KB, the new PRIQTY
  and new SECQTY are multiplied by 4, instead of pagesize.
  - PRIQTY and SECQTY in the generated ALTER TABLESPACE
  statement are compared with DSSIZE, if DSSIZE is > 0.
  If DSSIZE is > 0:
  PRIQTY cannot exceed 64GB and DSSIZE. It is set
  to min(64GB, DSSIZE) if it exceeds both thresholds.
  SECQTY cannot exceed 4GB and DSSIZE. It is set
  to min(4GB, DSSIZE) if it exceeds both thresholds.
  - PRIQTY and SECQTY in the generated ALTER INDEX statement
  are compared with PIECESIZE, if PIECESIZE is > 0.
  If PIECESIZE is > 0:
  PRIQTY cannot exceed 64GB and PIECESIZE. It is set
  to min(64GB, PIECESIZE) if it exceeds both thresholds.
  SECQTY cannot exceed 4GB and PIECESIZE. It is set
  to min(4GB, PIECESIZE) if it exceeds both thresholds.
  

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:

  UK35957 UK35964

Fix information

• Fixed component name

  ZOS ENA DB2 CTL

• Fixed component ID

  5740XYR05

Applicable component levels

• R81D PSY UK35964

     UP08/05/14 P F805

• R91D PSY UK35957

     UP08/05/14 P F805

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.