2

How can I view index of particular table in AS400? In which table index description of table is stored?

Swapnil Srivastav
  • 182
  • 2
  • 12
  • dspfd mylib/myindex dspdbr mylib/mytable – danny117 Oct 21 '16 at 15:44
  • What do you mean by `view index`? Do you want to learn what indexes are defined over a table? Do you want to see what columns are defined for the index? Do you want to see what values are in the index? – user2338816 Oct 26 '16 at 17:09

4 Answers4

2

If your "index" is really a logical file, you can see a list of these using:

select * from qsys2.systables
where table_schema = 'YOURLIBNAME' and table_type = 'L'
jmarkmurphy
  • 11,030
  • 31
  • 59
2

To complete the previous answers: if your AS400/IBMi's files are "IBM's old style" Physical and Logical files, the qsys2.syskeys and qsys2.sysindexes are empty.

==> you retrieve index infos in QADBKFLD (for "indexes" info) and QADBXREF(for fields list) tables

select * from QSYS.QADBXREF where DBXFIL = 'YOUR_LOGICAL_FILE_NAME' and DBXLIB = 'YOUR_LIBRARY' 

select * from QSYS.QADBKFLD  where DBKFIL = 'YOUR_LOGICAL_FILE_NAME' and DBKLB2 = 'YOUR_LIBRARY'

WARNING: YOUR_LOGICAL_FILE_NAME is not your "table name", but the name of the file ! You have to join another table QSYS.QADBFDEP to match LOGICAL_FILE_NAME / TABLE_NAME :

To found indexes from your table's name:

Select r.*
from QSYS.QADBXREF r, QSYS.QADBFDEP d 
where  d.DBFFDP = r.DBXFIL and d.DBFLIB=r.DBXLIB 
  and d.DBFFIL =  'YOUR_TABLE_NAME' and d.DBFLIB = 'YOUR_LIBRARY'

To found all indexes' fields from your table:

Select DBXFIL , f.DBKFLD, DBKPOS , t.DBXUNQ 
from QSYS.QADBXREF t 
  INNER JOIN QSYS.QADBKFLD  f on DBXFIL = DBKFIL and DBXLIB = DBKLIB 
  INNER JOIN QSYS.QADBFDEP d on d.DBFFDP = t.DBXFIL and d.DBFLIB=t.DBXLIB 
  where d.DBFFIL =  'YOUR_TABLE_NAME' and d.DBFLIB = 'YOUR_LIBRARY' 
order by DBXFIL, DBKPOS
Didier68
  • 1,027
  • 12
  • 26
1

if your indexes is create with SQL you can see liste of index in sysindexes system view

  SELECT * FROM qsys2.sysindexes WHERE TABLE_SCHEMA='YOURLIBNAME' and 
  TABLE_NAME = 'YOURTABLENAME'

if you want detail columns for index you can join syskeys tables

  SELECT KEYS.INDEX_NAME, KEYS.COLUMN_NAME     
  FROM   qsys2.syskeys KEYS                    
  JOIN qsys2.sysindexes IX ON KEYS.ixname = IX.name            
  WHERE  TABLE_SCHEMA='YOURLIBNAME' and TABLE_NAME = 'YOURTABLENAME'             
  order by INDEX_NAME
Esperento57
  • 16,521
  • 3
  • 39
  • 45
0

You could also use commands to get the information. Command DSPDBR FILE(LIBNAME/FILENAME) will show a list of the objects dependent on a physical file. The objects that show a data dependency can then be further explored by running DSPFD FILE(LIBNAME/FILENAME). This will show the access paths of the logical file.

T. Laza
  • 11
  • 4