CODEWITHSUNDEEP
db2ibm-midrangedb2-400iseries-navigator
Swapnil Srivastav
Swapnil Srivastav

Reputation: 192

AS400 index configuration table

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

Upvotes: 2

Views: 2155

Answers (4)

Didier68
Didier68

Reputation: 1323

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

Upvotes: 2

T. Laza
T. Laza

Reputation: 11

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.

Upvotes: 0

jmarkmurphy
jmarkmurphy

Reputation: 11473

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'

Upvotes: 2

Esperento57
Esperento57

Reputation: 17472

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

Upvotes: 1

Related Questions