I'm a newcomer to IBM mainframes tasked with creating a Java application that can connect to Db2 z/OS (v11 and v12) via a JDBC connection, then detect certain indexes, drop them, and later recreate them. Since Db2 doesn't store the original "CREATE INDEX" statements, I must query SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXPART for the index attributes so I can construct a "CREATE INDEX" statement that will recreate the dropped index as it originally existed.

The problem is that Db2 seems to either not store certain index attributes in a SYSIBM table, or it stores them contrary to what its documentation claims. Specifically:

DEFINE [YES | NO]: no SYSINDEXES column

[INCLUDE | EXCLUDE] NULL KEYS: no SYSINDEXES column

DEFER [YES | NO]: no SYSINDEXES column

[NOT] PADDED: SYSINDEXES.PADDED exists but never seems to store the possible "Y" and "N" values

UNIQUE WHERE NOT NULL: SYSINDEXES.UNIQUERULE is supposed to be "N" in this case, but it isn't.

PRIQTY, SECQTY, USING VCAT / STOGROUP, FREEPAGE, PCTFREE, GBPCACHE CHANGED|ALL|NONE: can be specified as CREATE INDEX options in v11, but the following corresponding SYSINDEXES columns don't exist until v12: PQTY, SECQTYI, STORTYPE, STORNAME, VCATNAME, FREEPAGE, PCTFREE, GBPCACHE

PARTITION m ENDING AT(n) INCLUSIVE: no SYSINDEXPART columns to indicate ENDING AT value (n) or whether partition element is INCLUSIVE

Is there an alternate way to find these attributes? Thanks!

Hi!

 A couple of things I can answer without thinking:

DEFINE YES/NO is not stored in the catalog anywhere. The column SPACE in SYSINDEXPART is used to register this at creation with a -1 and then when one row is inserted the VSAM LDS is created and this field is changed to be 0. 

DEFER YES/NO is also not stored as this is also a creation style option forcing a REBUILD utility.

So those two you can basically ignore from the DDL!

PADDED works great but you must test this on an index with a VARCHAR field within.

UNIQUE WHERE NOT NULL worked for me... Here you need at least one NULLable column of course (In my test system I have UNIQUERULE values of C, D, G, N, P, R U and X) 

The PRIQTY and other stuff did not exist in Db2 12 when they "moved" all the Part level defs up to the IX level. Basically you must "know" what version of Db2 was used to create the index and/or use the NULL values to trigger use of the INDEXPART values. Messy but the only way to do it properly.

ENDING AT is always true and the limit key is stored in LIMITKEY in SYSINDEXPART (In Ibm Db2 internal format...) More modern systems have the LIMITKEY now only in SYSTABLES and it is in human readable form (You still have to be careful about apostrophe's) 

Hope that helps a bit!

I'm a newcomer to IBM mainframes tasked with creating a Java application that can connect to Db2 z/OS (v11 and v12) via a JDBC connection, then detect certain indexes, drop them, and later recreate them. Since Db2 doesn't store the original "CREATE INDEX" statements, I must query SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXPART for the index attributes so I can construct a "CREATE INDEX" statement that will recreate the dropped index as it originally existed.

The problem is that Db2 seems to either not store certain index attributes in a SYSIBM table, or it stores them contrary to what its documentation claims. Specifically:

DEFINE [YES | NO]: no SYSINDEXES column

[INCLUDE | EXCLUDE] NULL KEYS: no SYSINDEXES column

DEFER [YES | NO]: no SYSINDEXES column

[NOT] PADDED: SYSINDEXES.PADDED exists but never seems to store the possible "Y" and "N" values

UNIQUE WHERE NOT NULL: SYSINDEXES.UNIQUERULE is supposed to be "N" in this case, but it isn't.

PRIQTY, SECQTY, USING VCAT / STOGROUP, FREEPAGE, PCTFREE, GBPCACHE CHANGED|ALL|NONE: can be specified as CREATE INDEX options in v11, but the following corresponding SYSINDEXES columns don't exist until v12: PQTY, SECQTYI, STORTYPE, STORNAME, VCATNAME, FREEPAGE, PCTFREE, GBPCACHE

PARTITION m ENDING AT(n) INCLUSIVE: no SYSINDEXPART columns to indicate ENDING AT value (n) or whether partition element is INCLUSIVE

Is there an alternate way to find these attributes? Thanks!

Thank you, Roy! The "Db2 internal format" in which SYSINDEXPART.LIMITKEY is stored: does it have a name, and are its specifics documented somewhere? I would like to see if there's a way I can convert it into "ENDING AT ..." clause(s) for a CREATE INDEX statement.

Hi!

 A couple of things I can answer without thinking:

DEFINE YES/NO is not stored in the catalog anywhere. The column SPACE in SYSINDEXPART is used to register this at creation with a -1 and then when one row is inserted the VSAM LDS is created and this field is changed to be 0. 

DEFER YES/NO is also not stored as this is also a creation style option forcing a REBUILD utility.

So those two you can basically ignore from the DDL!

PADDED works great but you must test this on an index with a VARCHAR field within.

UNIQUE WHERE NOT NULL worked for me... Here you need at least one NULLable column of course (In my test system I have UNIQUERULE values of C, D, G, N, P, R U and X) 

The PRIQTY and other stuff did not exist in Db2 12 when they "moved" all the Part level defs up to the IX level. Basically you must "know" what version of Db2 was used to create the index and/or use the NULL values to trigger use of the INDEXPART values. Messy but the only way to do it properly.

ENDING AT is always true and the limit key is stored in LIMITKEY in SYSINDEXPART (In Ibm Db2 internal format...) More modern systems have the LIMITKEY now only in SYSTABLES and it is in human readable form (You still have to be careful about apostrophe's) 

Hope that helps a bit!

I'm a newcomer to IBM mainframes tasked with creating a Java application that can connect to Db2 z/OS (v11 and v12) via a JDBC connection, then detect certain indexes, drop them, and later recreate them. Since Db2 doesn't store the original "CREATE INDEX" statements, I must query SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXPART for the index attributes so I can construct a "CREATE INDEX" statement that will recreate the dropped index as it originally existed.

The problem is that Db2 seems to either not store certain index attributes in a SYSIBM table, or it stores them contrary to what its documentation claims. Specifically:

DEFINE [YES | NO]: no SYSINDEXES column

[INCLUDE | EXCLUDE] NULL KEYS: no SYSINDEXES column

DEFER [YES | NO]: no SYSINDEXES column

[NOT] PADDED: SYSINDEXES.PADDED exists but never seems to store the possible "Y" and "N" values

UNIQUE WHERE NOT NULL: SYSINDEXES.UNIQUERULE is supposed to be "N" in this case, but it isn't.

PRIQTY, SECQTY, USING VCAT / STOGROUP, FREEPAGE, PCTFREE, GBPCACHE CHANGED|ALL|NONE: can be specified as CREATE INDEX options in v11, but the following corresponding SYSINDEXES columns don't exist until v12: PQTY, SECQTYI, STORTYPE, STORNAME, VCATNAME, FREEPAGE, PCTFREE, GBPCACHE

PARTITION m ENDING AT(n) INCLUSIVE: no SYSINDEXPART columns to indicate ENDING AT value (n) or whether partition element is INCLUSIVE

Is there an alternate way to find these attributes? Thanks!

Hi Barry!

 Here's an example of how it all looks. First some DDL snippets so you can see my table and index defs:

CREATE TABLE XXXXXXX.XXXXT151      
("PKEY"               CHAR( 1 )      
,"NUMSMALLINT"        SMALLINT       
,"NUMINT"             INTEGER        
,"NUMBIGINT"          BIGINT         
,"X03"                TIME           
,"X04"                DECIMAL(5 , 1) 

Partitioning index:

CREATE UNIQUE INDEX    XXXXXXX.XXXVX151P         
      ON XXXXXXX.XXXXT151                        
     ("PKEY"            ASC                      
     ,"NUMSMALLINT"     ASC                      
     ,"NUMINT"          ASC                      
     ,"NUMBIGINT"       ASC                      
     ,"X03"             ASC                      
     ,"X04"             ASC                      
     )                                           
      CLUSTER                                    
     (PART    1 VALUES ( 'D'   , 32555 )         
     ,PART    2 VALUES ( 'F'   , 12 , 21231232 ) 

The PART defs then go into the LIMIT key and look like this in Hexadecimal:

LIMITKEY              
D....................
CF2FFFFFFFFFFFFFFFFFF4
4FBFFFFFFFFFFFFFFFFFF0
----------------------
FØ.a{6Ø.............. 
C8084F8FFFFFFFFFFFFFF4
60C1360FFFFFFFFFFFFFF0

So you see the D and the F at the start as this is a CHAR(1) so easy. Then the numerics come along... These are inverted first bit versions of the numbers. The first is 32555 which is 7F2B in hex which then gets the high bit set giving FF2B likeweise for 12 which is 000C ends up as 800C. This is all documented in the Diagnosis Guide and Reference in chapter 15 "Data Organization" sub chapters "Numeric Data Columns" and "Date/time Columns" which has now been integrated into the Admin guide Appendix A. Exit Routines "Db2 decoding for numeric data in edit and validation routines"

Hope that helps a bit!

Thank you, Roy! The "Db2 internal format" in which SYSINDEXPART.LIMITKEY is stored: does it have a name, and are its specifics documented somewhere? I would like to see if there's a way I can convert it into "ENDING AT ..." clause(s) for a CREATE INDEX statement.

Hi!

 A couple of things I can answer without thinking:

DEFINE YES/NO is not stored in the catalog anywhere. The column SPACE in SYSINDEXPART is used to register this at creation with a -1 and then when one row is inserted the VSAM LDS is created and this field is changed to be 0. 

DEFER YES/NO is also not stored as this is also a creation style option forcing a REBUILD utility.

So those two you can basically ignore from the DDL!

PADDED works great but you must test this on an index with a VARCHAR field within.

UNIQUE WHERE NOT NULL worked for me... Here you need at least one NULLable column of course (In my test system I have UNIQUERULE values of C, D, G, N, P, R U and X) 

The PRIQTY and other stuff did not exist in Db2 12 when they "moved" all the Part level defs up to the IX level. Basically you must "know" what version of Db2 was used to create the index and/or use the NULL values to trigger use of the INDEXPART values. Messy but the only way to do it properly.

ENDING AT is always true and the limit key is stored in LIMITKEY in SYSINDEXPART (In Ibm Db2 internal format...) More modern systems have the LIMITKEY now only in SYSTABLES and it is in human readable form (You still have to be careful about apostrophe's) 

Hope that helps a bit!

I'm a newcomer to IBM mainframes tasked with creating a Java application that can connect to Db2 z/OS (v11 and v12) via a JDBC connection, then detect certain indexes, drop them, and later recreate them. Since Db2 doesn't store the original "CREATE INDEX" statements, I must query SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXPART for the index attributes so I can construct a "CREATE INDEX" statement that will recreate the dropped index as it originally existed.

The problem is that Db2 seems to either not store certain index attributes in a SYSIBM table, or it stores them contrary to what its documentation claims. Specifically:

DEFINE [YES | NO]: no SYSINDEXES column

[INCLUDE | EXCLUDE] NULL KEYS: no SYSINDEXES column

DEFER [YES | NO]: no SYSINDEXES column

[NOT] PADDED: SYSINDEXES.PADDED exists but never seems to store the possible "Y" and "N" values

UNIQUE WHERE NOT NULL: SYSINDEXES.UNIQUERULE is supposed to be "N" in this case, but it isn't.

PRIQTY, SECQTY, USING VCAT / STOGROUP, FREEPAGE, PCTFREE, GBPCACHE CHANGED|ALL|NONE: can be specified as CREATE INDEX options in v11, but the following corresponding SYSINDEXES columns don't exist until v12: PQTY, SECQTYI, STORTYPE, STORNAME, VCATNAME, FREEPAGE, PCTFREE, GBPCACHE

PARTITION m ENDING AT(n) INCLUSIVE: no SYSINDEXPART columns to indicate ENDING AT value (n) or whether partition element is INCLUSIVE

Is there an alternate way to find these attributes? Thanks!

Thank you again, Roy! Once last question (I hope):

You mentioned that in order to obtain PQTY, SECQTYI, STORTYPE, etc. (all the SYSINDEXES columns that didn't exist until v12 and are documented as being null for v11 indexes and earlier), I need to either "know" which version of Db2 was used to create the index and/or "use the NULL values to trigger use of the INDEXPART values." I don't understand how to "use the NULL values" to trigger anything.

Hi Barry!

 Here's an example of how it all looks. First some DDL snippets so you can see my table and index defs:

CREATE TABLE XXXXXXX.XXXXT151      
("PKEY"               CHAR( 1 )      
,"NUMSMALLINT"        SMALLINT       
,"NUMINT"             INTEGER        
,"NUMBIGINT"          BIGINT         
,"X03"                TIME           
,"X04"                DECIMAL(5 , 1) 

Partitioning index:

CREATE UNIQUE INDEX    XXXXXXX.XXXVX151P         
      ON XXXXXXX.XXXXT151                        
     ("PKEY"            ASC                      
     ,"NUMSMALLINT"     ASC                      
     ,"NUMINT"          ASC                      
     ,"NUMBIGINT"       ASC                      
     ,"X03"             ASC                      
     ,"X04"             ASC                      
     )                                           
      CLUSTER                                    
     (PART    1 VALUES ( 'D'   , 32555 )         
     ,PART    2 VALUES ( 'F'   , 12 , 21231232 ) 

The PART defs then go into the LIMIT key and look like this in Hexadecimal:

LIMITKEY              
D....................
CF2FFFFFFFFFFFFFFFFFF4
4FBFFFFFFFFFFFFFFFFFF0
----------------------
FØ.a{6Ø.............. 
C8084F8FFFFFFFFFFFFFF4
60C1360FFFFFFFFFFFFFF0

So you see the D and the F at the start as this is a CHAR(1) so easy. Then the numerics come along... These are inverted first bit versions of the numbers. The first is 32555 which is 7F2B in hex which then gets the high bit set giving FF2B likeweise for 12 which is 000C ends up as 800C. This is all documented in the Diagnosis Guide and Reference in chapter 15 "Data Organization" sub chapters "Numeric Data Columns" and "Date/time Columns" which has now been integrated into the Admin guide Appendix A. Exit Routines "Db2 decoding for numeric data in edit and validation routines"

Hope that helps a bit!

Thank you, Roy! The "Db2 internal format" in which SYSINDEXPART.LIMITKEY is stored: does it have a name, and are its specifics documented somewhere? I would like to see if there's a way I can convert it into "ENDING AT ..." clause(s) for a CREATE INDEX statement.

Hi!

 A couple of things I can answer without thinking:

DEFINE YES/NO is not stored in the catalog anywhere. The column SPACE in SYSINDEXPART is used to register this at creation with a -1 and then when one row is inserted the VSAM LDS is created and this field is changed to be 0. 

DEFER YES/NO is also not stored as this is also a creation style option forcing a REBUILD utility.

So those two you can basically ignore from the DDL!

PADDED works great but you must test this on an index with a VARCHAR field within.

UNIQUE WHERE NOT NULL worked for me... Here you need at least one NULLable column of course (In my test system I have UNIQUERULE values of C, D, G, N, P, R U and X) 

The PRIQTY and other stuff did not exist in Db2 12 when they "moved" all the Part level defs up to the IX level. Basically you must "know" what version of Db2 was used to create the index and/or use the NULL values to trigger use of the INDEXPART values. Messy but the only way to do it properly.

ENDING AT is always true and the limit key is stored in LIMITKEY in SYSINDEXPART (In Ibm Db2 internal format...) More modern systems have the LIMITKEY now only in SYSTABLES and it is in human readable form (You still have to be careful about apostrophe's) 

Hope that helps a bit!

I'm a newcomer to IBM mainframes tasked with creating a Java application that can connect to Db2 z/OS (v11 and v12) via a JDBC connection, then detect certain indexes, drop them, and later recreate them. Since Db2 doesn't store the original "CREATE INDEX" statements, I must query SYSIBM.SYSINDEXES and SYSIBM.SYSINDEXPART for the index attributes so I can construct a "CREATE INDEX" statement that will recreate the dropped index as it originally existed.

The problem is that Db2 seems to either not store certain index attributes in a SYSIBM table, or it stores them contrary to what its documentation claims. Specifically:

DEFINE [YES | NO]: no SYSINDEXES column

[INCLUDE | EXCLUDE] NULL KEYS: no SYSINDEXES column

DEFER [YES | NO]: no SYSINDEXES column

[NOT] PADDED: SYSINDEXES.PADDED exists but never seems to store the possible "Y" and "N" values

UNIQUE WHERE NOT NULL: SYSINDEXES.UNIQUERULE is supposed to be "N" in this case, but it isn't.

PRIQTY, SECQTY, USING VCAT / STOGROUP, FREEPAGE, PCTFREE, GBPCACHE CHANGED|ALL|NONE: can be specified as CREATE INDEX options in v11, but the following corresponding SYSINDEXES columns don't exist until v12: PQTY, SECQTYI, STORTYPE, STORNAME, VCATNAME, FREEPAGE, PCTFREE, GBPCACHE

PARTITION m ENDING AT(n) INCLUSIVE: no SYSINDEXPART columns to indicate ENDING AT value (n) or whether partition element is INCLUSIVE

Is there an alternate way to find these attributes? Thanks!