Create Index DDL

Defining an index in DDL

The CREATE INDEX statement is used to define an index on a database table.

SYSTABLES identifies tables: Long names are in the column table_name, while ten character (short) names are in the column table_name. Table_schema represents the library name. 

SELECT system_table_name FROM qsys2.systables WHERE table_name = 
'OAS_ELEMENT' and table_schema = 'CODA12TST'                     

SELECT system_table_name, TABLE_NAME FROM qsys2.systables WHERE
table_schema = 'CODA12TST'

Syntax

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. Use the Use the CREATE INDEX statement to create an index for one or more columns in a table. 

>>-CREATE--+----------------------+----------------------------->
           | -- index name --     |    
           | -- where not null -- |
           |______________________|
     
>--INDEX--+-----------------+--index--ON-------------------------->
          | --index name -- |
          |_________________|
  
>-----table name-+---------------------+---+ASC----------+-------->
                 | -- column name --   |   |-- DESC --   |
                 | -- key expression --|   |-- Random -- |
                 |_____________________|   |_____________|

>---Other Options----+-------------------+------------------------>
                      |__________________|

Sample Index

-- CMS:*----------------------------------------------  *

  -- * Create Index
  CREATE INDEX
    VRHSTMTSA
  ON
    ARM_STATEMENT_HEADERS
      (
       LoanNumber                                ASC,
       Transfer                                  ASC,
       StatementControlNumber                    ASC
      ) ;
	
RENAME INDEX VRHSTMTSA 
  TO  ARM_Statement_Headers_By_Loan_Transfer
  FOR SYSTEM NAME VRHSTMTSA;

  LABEL ON INDEX
    VRHSTMTSA
  IS
    'Statement header file by loan and transfer' ;

Sample Index

--  ------------------------------------------------------------------------ --
--  Proj: CSG INDEX TO CSGIMBPF BY UUID,TIME STAMP                           --
--  Pgmr: S. CROY       Date: 2018-04-18                                     --
--  ------------------------------------------------------------------------ --
	
  -- * Create Index
  CREATE INDEX
   CSGIMBL1
  ON
    CSGIMBPF
      (
       IMUUID                                          ASC,
       IMDDTM                                          ASC
      ) ;
	
RENAME INDEX  CSGIMBL1 TO  MAILTRACE_BY_GUUID
  FOR SYSTEM NAME CSGIMBL1;

  LABEL ON INDEX
    CSGIMBL1
  IS
    'Mail Trace by UUID, and time stamp' ;