A fix is available
APAR status
Closed as program error.
Error description
1/ ADMIN_INFO_SQL / Service SQL only handled PIECESIZE up to 67108864 (64G) It does not handle 128G and 256G, so bad output was generated. 2/ For pre-V11 created partitioning indexes with datetime type ADMIN_INFO_SQL generates LIMITKEY without quotes (which is derived fron v10 definitions). Such DDL need manual re-work
Local fix
manual rework of DDL
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 11 for z/OS and Db2 12 for z/OS * * customers who use the Db2-supplied * * stored procedure SYSPROC.ADMIN_INFO_SQL * * or the batch program DSNADMSB to collect * * Db2 environmental service data either * * directly or via any of the following * * tools: * * o Query Environment Capture feature of * * Data Studio * * o Query Environment Capture feature of * * Optim Query Workload Tuner * * o Capture Query Environment feature of * * Data Server Manager * **************************************************************** * PROBLEM DESCRIPTION: * * The following problems may occur * * when running the batch program * * DSNADMSB or the Db2-supplied * * stored procedure * * SYSPROC.ADMIN_INFO_SQL: * * (1) An invalid PIECESIZE value is * * specified in the generated CREATE * * INDEX PIECESIZE clause, such as: * * PIECESIZE 134217728 * * PIECESIZE 268435456 * * instead of: * * PIECESIZE 128 G * * PIECESIZE 256 G * * causing the CREATE INDEX statement * * to fail with SQLCODE -199. * * (2) A date value in the CREATE INDEX * * ENDING AT clause is not enclosed in * * single quotes, such as: * * ENDING AT (31.12.2000) * * instead of: * * ENDING AT ('31.12.2000') * * causing the CREATE INDEX statement * * to fail with SQLCODE -104. * * (3) An invalid value is specified in * * the CREATE TABLE ENDING AT clause: * * o MAXVALUE is followed by a * * constant, such as: * * ENDING AT (MAXVALUE,X'FFFF', * * MAXVALUE,X'FFFFFFFFFFFC') * * instead of: * * ENDING AT (X'FF',X'FFFF', * * X'FF',X'FFFFFFFFFFFC') * * o MAXVALUE is enclosed in single * * quotes, such as: * * ENDING AT (MAXVALUE,' MAXVALUE', * * ' MAXVALUE',' MAXVALUE') * * instead of: * * ENDING AT (MAXVALUE,MAXVALUE, * * MAXVALUE,MAXVALUE) * * o MINVALUE is enclosed in single * * quotes, such as: * * ENDING AT ('MINVALUE','MINVALUE', * * 'MINVALUE','MINVALUE') * * instead of: * * ENDING AT (MINVALUE,MINVALUE, * * MINVALUE,MINVALUE) * * causing the CREATE TABLE statement * * to fail with SQLCODE -636. * * (4) A SET CURRENT SCHEMA statement * * is assigned a blank value, (i.e., * * SET CURRENT SCHEMA = ''), causing * * the SET CURRENT SCHEMA statement * * to fail with SQLCODE -713. * **************************************************************** * RECOMMENDATION: * * Apply corrective PTF when available. * **************************************************************** The following problems may be found in the DDLs generated by the batch program DSNADMSB or the Db2-supplied stored procedure SYSPROC.ADMIN_INFO_SQL: (1) The SYSIBM.SYSINDEXES.PIECESIZE values of 134217728 and 268435456 are currently not recognized by DSNADMSB or ADMIN_INFO_SQL. Therefore, in the generated CREATE INDEX ENDING AT clause, these particular PIECESIZE values do not have the keyword K, M, or G appended to them, causing the CREATE INDEX statement to fail with SQLCODE -199. (2) A date value with the format dd.mm.yyyy in a CREATE INDEX ENDING AT clause is not recognized by DSNADMSB or ADMIN_INFO_SQL. Therefore, it is not enclosed in single quotes, causing the CREATE INDEX statement to fail with SQLCODE -104. (3) The following invalid values may be specified in the CREATE TABLE ENDING AT clause, causing the CREATE statement to fail with SQLCODE -636. o A key value of x'FF' is arbitrarily changed to MAXVALUE, even if MAXVALUE is followed by a constant. o MAXVALUE is enclosed with single quotes if MAXVALUE is preceded by one or more blanks. o MINVALUE is enclosed with single quotes. (4) The SET CURRENT SCHEMA statement that precedes a CREATE VIEW statement is set to blank when the VIEW is not found in the Db2 catalog SYSDEPENDENCIES, but is found in SYSVIEWDEP. Currently, DSNADMSB and ADMIN_INFO_SQL queries SYSDEPENDENCIES only to determine the value to be assigned to SET CURRENT SCHEMA.
Problem conclusion
To address the issues described in the Problem Description section of this APAR, DSNADMSB and ADMIN_INFO_SQL are modified as follows: (1) Support SYSIBM.SYSINDEXES.PIECESIZE values of 134217728 and 268435456. (2) Check the Db2 catalog table SYSCOLUMNS for key column types DATE, TIMESTMP, TIMESTZ, and TIME, instead of relying on the format of a datetime key value when determining whether to enclose a datetime key value with single quotes in a CREATE INDEX ENDING AT clause. (3) CREATE TABLE ENDING AT clause: - Do not change a key value of x'FF' to MAXVALUE. - Do not enclose MAXVALUE with single quotes if MAXVALUE is preceded by one or more blanks. - Do not enclose MINVALUE with single quotes. (4) When setting the value of the SET CURRENT SCHEMA and SET CURRENT PATH statements that are related to a view, search the Db2 catalog table SYSVIEWDEP if the view is not found in SYSDEPENDENCIES. Likewise, when searching for view dependencies or checking whether a routine has a view that is dependent on it, search SYSVIEWDEP if the view is not found in SYSDEPENDENCIES.
Temporary fix
Comments
APAR Information
APAR number
PI89272
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
2017-10-25
Closed date
2018-02-15
Last modified date
2018-04-03
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI53904 UI53906
Modules/Macros
DSN@DMSS DSNADMSS
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:
03 April 2018