Ah, the ever present debate, when is a logical an index? I like to think that this could be a good Zen koan, but it is actually quite simple. You see, IBM has a system catalogue view in the library QSYS2 called SYSINDEXES that lists all of the indexes on your AS/400 system. The problem is the view only lists SQL INDEXES. See, a logical file only makes it into the list of indexes if the SQL CREATE INDEX statement created it. Since most AS/400 shops are dealing with legacy systems their logical files were created by DDS. Therefore, none of their logical files are in the SYSINDEXES view. However, most keyed logical files are indexes and can be consider for use by SQL in the resolution of a query. The problem is a semantic one; IBM chose to call a logical file an index only if the CREATE INDEX SQL statement created it.

A better way to look for indexes, (er logical files), which can be used in the resolution of a query is to look at the system catalogue tables in QSYS. QSYS has several physical files that have useful information on all of the database objects on your AS/400 system.

The first catalogue file to ponder is the QADBFDEP file. This physical file contains cross-reference information about what files are dependent on each other on your AS/400 system. The following query could be used to list the files that are dependent on the FOO table in the BAR library:

SELECT * FROM QSYS.QADBFDEP
WHERE DBFFIL = 'FOO' AND DBFLIB = 'BAR'

The DBFFIL field is the name of the target table, and the DBFLIB field is the name of the target library. The above query would list all of the files that are dependent on the FOO table and would include the name of the dependent table, its library, the type of dependency and its dependent files record format. Note that this is sort of like running the DSPDBR command against a table.

Another interesting table in QSYS is QADBXREF. This table contains information about all tables, physical files, indexes, logical files, source physical files and SQL views on your AS/400 system. It is like a subset of the information available from the DSPFD command. The following query joins the QADBFDEP table to the QADBXREF table in order to produce a listing of the files dependent on the OEPTOLY table in the HD1100PD library. The query also returns the type of dependent file, the short description of the file and whether the file is considered relational by the AS/400 system.

SELECT DBFLDP AS LIBRARY, DBFFDP AS FILENAME,
DBXOWN AS OWNER, DBXTXT AS SHORT_DESC,
DBXATR AS FILE_TYPE, DBXREL AS RELATIONAL,
DBXATS AS LAST_CHANGE
FROM QSYS.QADBFDEP LEFT JOIN QSYS.QADBXREF
ON (DBFFDP=DBXFIL AND DBXLDP=DBXLIB)
WHERE DBFFIL='OEPTOLY' AND DBFLIB='HD1100PD'

Now, the above query is much more useful than the output of DSPDBR because it includes the description of the file and they type of dependent file. Other information available in QADBXREF that you might want to include in the query is the number of fields in the file, the maximum record length and the number of key fields in the file.

Now, this begs the question about that relational field, what exactly does that mean. Well, as near as I can determine if the file type is a logical file and it is not a multi-format file it is considered relational by the AS/400 system. What this means to you is that you can name the file in the FROM clause of a query. I know this is not a good explanation, but we only have a limited amount of space this week so I need to move on. Suffice it to say, I will address the ‘relational’ nature of these files in a future article. However, I do want to point out that the relational attribute in QADBXREF is important to JDBC and ODBC programmers. In JDBC and ODBC, you can execute a query against your database connection to determine the indexes available against your physical file. This operation does not go against the SYSINDEXES view in QSYS2, but rather is a query against the relational=’Y’ logical files in QADBXREF that relate to your physical file. This is not an actual list of ALL of the logical files that might be considered in the resolution of your query. You see, DB2 might elect to use any of the logical files returned by my query above in resolution of a query against the physical file. In fact, if you check the query optimizer, you will see the DB2 looks at each of the logical files that are related to each of the physical files that are referenced in your query.

So, the short answer is to use the query above to get a list of indexes that might be used in resolution of an SQL query against a physical file. With a few changes, the query can even be used to return the names of the fields like the following:

SELECT DBFLDP AS LIBRARY,
DBFFDP AS FILENAME,
DBXTXT AS SHORT_DESC,
DBKPOS AS ORDINAL_POS,
DBKFLD AS KEY_FIELD,
DBXREL AS RELATIONAL
FROM QSYS.QADBFDEP
LEFT JOIN QSYS.QADBXREF
ON (dbffdp=dbxfil AND dbfldp=dbxlib)
LEFT JOIN QSYS.QADBKFLD
ON (DBFFDP=DBKFIL AND
DBFLDP = DBKLIB AND
DBFRDP = DBKFMT)
WHERE dbffil='OEPTOLY' AND dbflib='HD1100PD'
AND DBXATR='LF'
ORDER BY FILENAME, ORDINAL_POS

Note that the above query restricts itself to only files that have the LF, logical file, DBXATR so that we are only listing logical files that are related to the physical file. In addition, the query specifies that the information by returned order by the FILENAME and then by the ORDINAL_POS. ORDINAL_POS is the offset of the key in the logical file, where the 1st key would be ordinal position 1 and the second key would be ordinal position 2. Remember, with SQL you always want to specify the order that you want your result set returned in, otherwise you have no guanentees as to how the AS/400 will return the results of your query. The above query might be sutable for driving a report on the indexes available against a physical file, but by joining the query to the QADBIFLD file we can further enhance information about the keys in the logical file by returning the data type of the field and the fields short description. The next query returns most of the information you might want to know about a logical file by joining the QADBFDEP, QADBXREF, QADBKFLD and QADBIFLD files together.

SELECT DBFLDP AS LIBRARY,
DBFFDP AS FILENAME,
DBXTXT AS SHORT_DESC,
DBKPOS AS ORDINAL_POS,
DBKFLD AS KEY_FIELD,
DBITYP AS DATA_TYPE,
DBITXT AS FIELD_DESC,
DBICLN AS CHARACTER_LEN,
DBINLN AS NUMERIC_LEN,
DBINSC AS NUMERIC_SCALE,
DBXREL AS RELATIONAL
FROM QSYS.QADBFDEP
LEFT JOIN QSYS.QADBXREF
ON (dbffdp=dbxfil AND dbfldp=dbxlib)
LEFT JOIN QSYS.QADBKFLD
ON (DBFFDP=DBKFIL AND
DBFLDP = DBKLIB AND
DBFRDP = DBKFMT)
LEFT JOIN QSYS.QADBIFLD
ON (DBFFDP=DBIFIL AND
DBFLDP=DBILIB AND
DBFRDP=DBIFMT AND
DBKFLD=DBIFLD)
WHERE dbffil='OEPTOLY' AND dbflib='HD1100PD'
AND DBXATR='LF'
ORDER BY FILENAME, ORDINAL_POS

The system catalogue tables in QSYS are a wealth of information, spend some time looking at what is available to you and attempting to construct queries to explore your AS/400 and look for some updated pages on my web site, www.sqlthing.com , addressing these and other issues with indexes.