A fix is available
APAR status
Closed as program error.
Error description
Scenario: tablespace with MAXROWS 100 DEFINE NO and created a table in this tablespace. After that, table is still empty - so no physical define of ts - and runnnit an ALTER TABLESPACE dbname.tsname MAXROWS 255 and receives DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT DB2.TS2 HAS PLACED OBJECT IN ADVISORY REORG PENDING and requires a REORG on empty tablespace, which also materialises dataset. Keywords: SQLCODE610 SQLALTER
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: All Db2 11 and 12 for z/OS users of ALTER * * TABLESPACE MAXROWS statement. * **************************************************************** * PROBLEM DESCRIPTION: Db2 incorrectly issues SQLCODE610 * * and places the table space in * * advisory reorg pending when the user * * attempts to alter the MAXROWS of a * * DEFINE NO table space. * **************************************************************** * RECOMMENDATION: Apply corrective PTF when available. * **************************************************************** When Db2 processes an ALTER TABLESPACE MAXROWS statement, the table space DEFINE YES or DEFINE NO attribute is not being checked. Hence, the table space is always being placed in advisory reorg pending state with SQLCODE610 even when the table sapce has a DEFINE NO attribute. Example of the incorrect dbet states: -DISPLAY DB(DB2) SPACENAM(*) LIMIT(*) DSNT360I -DB2A *********************************** DSNT361I -DB2A * DISPLAY DATABASE SUMMARY * GLOBAL DSNT360I -DB2A *********************************** DSNT362I -DB2A DATABASE = DB2 STATUS = RW DBD LENGTH = 4028 DSNT397I -DB2A NAME TYPE PART STATUS -------- ---- ----- ----------------- -------- -------- TS2 TS 0001 RW,AREO* TS2 TS Example of the incorrect SQLCODE610 issued: RESULT OF SQL STATEMENT: DSNT404I SQLCODE = 610, WARNING: A CREATE/ALTER ON OBJECT DB405621.TS405621 HAS PLACED OBJECT IN ADVISORY REORG PENDING DSNT418I SQLSTATE = 01566 SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXIATS SQL PROCEDURE DETECTING ERROR DSNT416I SQLERRD = 156 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'0000009C' X'00000000' X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Problem conclusion
Db2 code has been fixed. On an ALTER TABLESPACE MAXROWS statement, Db2 will not issue SQLCODE610 and place the table space in advisory reorg pending state if it is a DEFINE NO table space. However, applying APAR PH13310 does not change the existing table spaces' dbet state which might be incorrectly in advisory reorg pending state. AREO* should not impact any further DDL/DML but is primarily for performance, which is not an issue for DEFINE NO objects. User can run REPAIR utility with NOAREORPENDSTAR option to clear the table space advisory reorg pending state. ADDITIONAL KEYWORDS: SQLALTER SQLCODE610
Temporary fix
Comments
APAR Information
APAR number
PH13310
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2019-06-14
Closed date
2019-09-25
Last modified date
2019-11-01
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI65508 UI65510
Modules/Macros
DSNGDADP
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
01 November 2019