Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2010
    Posts
    207

    Unanswered: DB2 z/OS Get all Index with same column names but different parameters (Cluster, etc)

    Hello friends,

    now i would like to get all indexes with the same column names but different parameters (Cluster, Uniqueness, etc.)

    Therefor I want to create a query that asks sysibm.syskeys table (columns ixname, colname) and sysibm.syskeys table (columns name, uniquerule,Clustering,Clustered,Eraserule,Closerul e)
    in one query.

    Can anyone help me with this? Please keep in mind that i want to use this result for a compare of test and production environment.

    thank you again for your support. Have a nice day.

    Regards,

    DB2N00b

  2. #2
    Join Date
    Dec 2016
    Posts
    4
    I use that kind of query to extract informations from indexes and keys, where colname = xxx
    hope that may help you

    Here it's made for max 5 columns in indexes, to be changed if necessary

    Code:
       SET CURRENT SCHEMA = 'SYSIBM' ;           
       SELECT SUBSTR(IX.TBNAME, 01, 08)  AS TABLE
            , SUBSTR(IX.NAME  , 01, 08)  AS INDEX
            , IX.UNIQUERULE              AS PUD  
            , IX.CLUSTERING              AS CLS  
            , IX.CLUSTERRATIO            AS RATIO
            , SUBSTR(K1.COLNAME, 01, 08) AS COL1 
            , K1.ORDERING                AS O1   
            , C1.COLTYPE                 AS TYPE1
            , C1.LENGTH                  AS LG1  
            , SUBSTR(K2.COLNAME, 01, 08) AS COL2 
            , K2.ORDERING                AS O2   
            , C2.COLTYPE                 AS TYPE2
            , C2.LENGTH                  AS LG2  
            , SUBSTR(K3.COLNAME, 01, 08) AS COL3 
            , K3.ORDERING                AS O3   
            , C3.COLTYPE                 AS TYPE3
            , C3.LENGTH                  AS LG3  
            , SUBSTR(K4.COLNAME, 01, 08) AS COL4 
            , K4.ORDERING                AS O4   
            , C4.COLTYPE                 AS TYPE4
            , C4.LENGTH                  AS LG4  
            , SUBSTR(K5.COLNAME, 01, 08) AS COL5 
            , K5.ORDERING                AS O5   
            , C5.COLTYPE                 AS TYPE5
            , C5.LENGTH                  AS LG5  
       FROM SYSINDEXES AS IX                     
       LEFT JOIN SYSKEYS      AS K1              
         ON K1.IXNAME = IX.NAME                  
        AND K1.COLSEQ = 1                        
       LEFT JOIN SYSCOLUMNS   AS C1              
         ON C1.TBCREATOR= IX.TBCREATOR           
        AND C1.TBNAME   = IX.TBNAME              
        AND C1.NAME     = K1.COLNAME             
       LEFT JOIN SYSKEYS      AS K2              
         ON K2.IXNAME = IX.NAME                  
        AND K2.COLSEQ = 2                        
       LEFT JOIN SYSCOLUMNS   AS C2              
         ON C2.TBCREATOR= IX.TBCREATOR           
        AND C2.TBNAME   = IX.TBNAME              
        AND C2.NAME     = K2.COLNAME             
       LEFT JOIN SYSKEYS      AS K3              
         ON K3.IXNAME = IX.NAME                  
        AND K3.COLSEQ = 3                        
       LEFT JOIN SYSCOLUMNS   AS C3              
         ON C3.TBCREATOR= IX.TBCREATOR         
        AND C3.TBNAME   = IX.TBNAME            
        AND C3.NAME     = K3.COLNAME           
       LEFT JOIN SYSKEYS      AS K4            
         ON K4.IXNAME = IX.NAME                
        AND K4.COLSEQ = 4                      
       LEFT JOIN SYSCOLUMNS   AS C4            
         ON C4.TBCREATOR= IX.TBCREATOR         
        AND C4.TBNAME   = IX.TBNAME            
        AND C4.NAME     = K4.COLNAME           
       LEFT JOIN SYSKEYS      AS K5            
         ON K5.IXNAME = IX.NAME                
        AND K5.COLSEQ = 5                      
       LEFT JOIN SYSCOLUMNS   AS C5            
         ON C5.TBCREATOR= IX.TBCREATOR         
        AND C5.TBNAME   = IX.TBNAME            
        AND C5.NAME     = K5.COLNAME           
       WHERE K1.COLNAME='XXXX'               
          OR K2.COLNAME='XXXX'               
          OR K3.COLNAME='XXXX'               
          OR K4.COLNAME='XXXX'               
          OR K5.COLNAME='XXXX'               
       ORDER BY 1, 2

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •