IBM DB2 for i: Code example

SQL Stored Procedure - Index Listing Utility

CREATE PROCEDURE qgpl.indexlist(TableName VARCHAR(128), SchemaName VARCHAR(128) ) 
LANGUAGE SQL
RESULT SET 1
BEGIN
DECLARE ixTable VARCHAR(128);
DECLARE ixTableSchema VARCHAR(128);
DECLARE c1 CURSOR WITH RETURN FOR 
WITH SQL_Indices 
(index_name,  index_schema, is_unique, system_index_name, system_index_schema)
  AS ( SELECT index_name,  index_schema, is_unique, system_index_name, 
  system_index_schema       
    FROM qsys2.sysindexes
      WHERE table_name = ixTable   AND table_schema = ixTableSchema ),
Const_Indices (index_name, index_schema, is_unique) 
  AS ( SELECT constraint_name, constraint_schema, 
    CASE WHEN constraint_type = 'FOREIGN KEY' THEN 'D'  ELSE 'U' END  
      FROM qsys2.syscst 
        WHERE table_name = ixTable AND table_schema = ixTableSchema 
                and constraint_type<>'CHECK'),
KeyedLF_Indices (index_name, index_schema, is_unique) 
  AS ( SELECT dbxfil, dbxlib, dbxunq 
    FROM qsys.qadbxref, qsys.qadbfdep 
      WHERE dbxatr='LF' AND dbxnkf>0 AND dbffdp=dbxfil AND dbfldp=dbxlib 
          AND dbffil=ixTable 
      AND dbflib=ixTableSchema),
KeyedPF_Index (index_name, index_schema, is_unique,dbxfil) 
AS (SELECT dbxlfi,dbxlib,dbxunq,dbxfil 
  FROM qsys.qadbxref 
    WHERE dbxatr='PF'  AND dbxnkf>0  AND dbxlib=ixTableSchema AND dbxlfi=ixTable
    AND NOT EXISTS ( SELECT 1 FROM qsys.qadbfcst 
    WHERE dbxlib=dbccfl AND dbxfil=dbccff AND dbccty='PRIMARY KEY')  )
/* -----------------------------------------------------------------------------*/
/* SQL Indices */
/*------------------------------------------------------------------------------*/
SELECT                                            
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(SQL_Indices.index_name, 1, 30)  
    ELSE ' '
      END as INDEX_NAME,                                       
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(SQL_Indices.index_schema, 1, 10)
    ELSE ' '
      END as INDEX_SCHEMA,                                       
      is_unique,                     
      SUBSTRING(column_name, 1, 30) as KEY_COLUMN,             
      ordinal_position,                          
      ordering   ,               
      SQL_Indices.system_index_name,                       
      SQL_Indices.system_index_schema                    
    FROM  SQL_Indices,   qsys2.syskeys B                             
    WHERE SQL_Indices.system_index_name = B.system_index_name  
      AND SQL_Indices.system_index_schema = B.system_index_schema
UNION
/* -----------------------------------------------------------------------------*/
/* Constraint Indices */
/*------------------------------------------------------------------------------*/
SELECT                                            
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(Const_Indices.index_name, 1, 30)  
    WHEN ordinal_position > 1 THEN ' '
    END,                                       
  CASE                                       
    WHEN ordinal_position = 1              
      THEN SUBSTRING(Const_Indices.index_schema, 1, 10)
    WHEN ordinal_position > 1 THEN ' '
    END,                                       
    is_unique,                     
    SUBSTRING(column_name, 1, 30),             
    ordinal_position,                          
    'A' , 
    index_name,
    index_schema
  FROM  Const_Indices,   qsys2.syskeycst B                             
  WHERE Const_Indices.index_name = B.constraint_name
    AND Const_Indices.index_schema = B.constraint_schema
UNION
/* -----------------------------------------------------------------------------*/
/* Keyed LF Indices */
/*------------------------------------------------------------------------------*/
  SELECT                                            
    CASE                                       
      WHEN dbkpos = 1              
        THEN SUBSTRING(KeyedLF_Indices.index_name, 1, 10)  
      ELSE ' '
      END,                                       
    CASE                                       
      WHEN dbkpos = 1              
        THEN SUBSTRING(KeyedLF_Indices.index_schema, 1, 10)
      ELSE  ' '
      END,                                       
      is_unique,                      
      dbkfld,
      dbkpos,
      dbkord,                         
      index_name,
      index_schema
    FROM  KeyedLF_Indices,   qsys.qadbkfld B        
    WHERE KeyedLF_Indices.index_name = dbkfil   
      AND KeyedLF_Indices.index_schema = dbklib 
    UNION
/* -----------------------------------------------------------------------------*/
/* Keyed PF Indices */
/*------------------------------------------------------------------------------*/
SELECT                                            
  CASE                                       
    WHEN dbkpos = 1              
      THEN SUBSTRING(KeyedPF_Index.index_name, 1, 10)  
    ELSE ' '
    END,                                       
  CASE                                       
    WHEN dbkpos = 1              
      THEN SUBSTRING(KeyedPF_Index.index_schema, 1, 10)
    ELSE  ' '
    END,                                       
    is_unique,                     
    dbkfld, 
    dbkpos,
    dbkord,                         
    index_name,
    index_schema
  FROM  KeyedPF_Index,   qsys.qadbkfld B        
  WHERE KeyedPF_Index.index_name = dbkfil   
    AND KeyedPF_Index.index_schema = dbklib 
  ORDER BY 7,8,5 FOR READ ONLY;
SELECT table_name, table_schema INTO ixTable, ixTableSchema 
  FROM qsys2.systables 
  WHERE   (table_name=UPPER(TableName) and table_schema=UPPER(SchemaName)) OR
     ( system_table_name=UPPER(TableName) 
         and system_table_schema=UPPER(SchemaName));
/* Open the cursor so the index information can be returned as a result set */
OPEN c1;
END

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

IBM and IBM Business Partner solutions exploit key benefits in IBM Power Systems’ unique design combined with systems software to provide support for next generation applications for big data and analytics and cognitive computing to providing unprecedented insights into opportunities, threats and efficiencies to your organization.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information