[IMAGE]
[IMAGE][IMAGE]
Ron Turull
[IMAGE]
[IMAGE]
DB2 maintains a number of cross-reference files that you can use to easily retrieve information about files on your system. They contain information on files, fields, keys, referential constraints and much more, and they are easy the work with. The cross-reference files are automatically updated by the system any time changes are made to the structure of the iSeries-AS/400 database (which includes such things as creating a new file physical or logical file, changing an existing file, adding and/or removing referential constraints, etc.).
Eight cross-reference files
There are eight cross-reference physical files, each containing a different type of information.
- QADBXREF. This is the main cross-reference file. Although it doesn't "cross-reference" a whole lot, it does contain attributes for every database file on the system, attributes such as the following:
* Physical file, logical file, table, view, or index
* Number of fields
* Number of key fields
* Record length
The QADBXREF file is like a header file for most of the other DB2 cross-reference files.
More information
[IMAGE]
- QADBFDEP. This file holds the information displayed on the DSPDBR command -- information about dependent files (e.g., logical files).
- QADBIFLD. This file contains information about every field in every file on the system. It can be used to find field name conflicts, create customized field description reports and more.
- QADBKFLD. Combined with the QADBIFLD file, these two files are a goldmine. QADBKFLD holds information about all the key fields used in files that have keyed access paths.
- QADBFCST. This is the header file for referential constraint information. It contains file-level information about the constraints that have been added to files on the system. The detail file is QADBCCST (see next).
- QADBCCST. This is the detail file for referential constraint information. It contains the field-level information about referential constraints. You can "link" it to the header file using the field information shown in the table below.
QADBFCST fields
QADBCCST fields
DBCCFL
DBCCLN
...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
DBCCFF
DBCCFN
DBCCNL
DBCCKL
DBCCNM
DBCCKN
- QADBPKG. This file contains information on SQL packages that have been created on the system. The Consistency token field (DBPTKN) contains the package's "signature," similar in concept to export signatures for ILE service programs and level identifiers for files.
- QADBXRDBD. This file maintains information about the relational database definitions present on your system. Relational database definitions are most commonly used in SQL.
To get more information on these files, you can use the Display file field description command (DSPFFD) to display or print the field definitions. Then use Query/400, SQL or some other query tool to examine the contents.
How to access the data
You can access the data in the files directly or you can build your own logical files over these files so you can access the data in whatever way suits your needs. However, each physical file may have one or more logical files already built over it, so before you build any new logical files over these cross-reference files, use the utility presented later to check if a logical file with the appropriate key already exists. SQL users should also note that in addition to predefined logical files, there are a number of predefined SQL views over the cross-reference files (see the redbook DB2/400 Advanced Database Functions (GG24-4249)).
When you start using DB2's cross-reference files you will soon notice that many fields support null and, in fact, many records contain null values in one or more of these fields. Also, some fields are defined as variable length character fields. You should be familiar with "VARCHAR" fields and fields that support null before using these files.
RPG Tip: On all the RPG compiler commands, you can specify CVTOPT(*VARCHAR) to have variable length character fields declared in the program as fixed-length fields and ALWNULL(*YES) to avoid data-mapping errors when records with null values are retrieved.
Note, the cross-references are read-only. The cross-reference files are all defined with the allow write, allow update, and the allow delete attributes all set to *NO. In addition, since the files are always held in the open state by system jobs, you cannot change the attributes.
-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.
