Verifying System Catalog Information for ODBC Use

Resolving The Problem

ODBC applications commonly query table and column catalog information. When incorrect output is received trying to retrieve table or column system catalog information, the following areas should be verified to isolate the source of the problem:

o	The IBM DB2/400 system catalog tables
o	Logical files (views) and SQL packages in library QIWS at pre-V5R1 that are part of the database host server
o	Application errors, including incorrect use of the ODBC pattern search character (the underscore)

Verifying the System Catalog Table Information

You should verify the validity of the system cross-reference tables before further diagnostics are done. System cross-reference files can become corrupted during operations such as a failed release upgrade or a sudden power failure (no UPS).

DB2/400 stores catalog information in system cross-reference files. These files consist of logical files or views and physical files or tables. There are some simple queries that can be run from ISQL (interactive SQL) or Operations Navigator - Database - Run SQL Scripts. The query results can help verify that the system cross-reference tables contain valid information for the tables in question.

SELECT *
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_NAME = 'YOURFILENAME' AND TABLE_SCHEMA = 'YOURLIBNAME'

SELECT *
FROM QSYS.QADBIFLD
WHERE DBILFI = 'YOURFILENAME' AND DBILB2 = 'YOURLIBNAME'

SELECT *
FROM QSYS2.SYSTABLES
WHERE TABLE_NAME = 'YOURFILENAME' AND TABLE_SCHEMA = 'YOURLIBNAME'

SELECT *
FROM QSYS.QADBXREF
WHERE DBXLFI = 'YOURFILENAME' AND DBXLB2 = 'YOURLIBNAME'

Note: The table and library names used above are case sensitive. If the table was created using the SQL CREATE TABLE statement and a delimited identifier (a double-quoted name such as "MyTableName"), type the case-sensitive name ('MyTableName'). All other names must be all uppercase ('QGPL').

Check the result set of each of these queries to verify that data is returned and that the relevant fields listed below contain the correct information. Using a standard naming convention (10 characters or less), long names should equal the real name. If long names are implemented, long names and real names will not match; however, the short name will be derived from the long name. Note that ODBC catalog requests always return the long names.

SYSTABLES Column	Alternative Name	Description	Renamed Field
NAME	TABLE_NAME	Long Table Name	DBXLFI
DBNAME	TABLE_SCHEMA	Library Name	DBXLB2
SYS_TNAME	SYSTEM_TABLE_NAME	File Name	DBXFIL
SYS_DNAME	SYSTEM_TABLE_SCHEMA	Library Name	DBXLIB
TBDBNAME	BASE_TABLE_SCHEMA	Alias Table Name	DBXALB
TBNAME	BASE_TABLE_NAME	Alias File Name	DBXAFL

QADBXREF Column	Alternative Name	Description	Renamed Field
DBXFIL		DBXAFL
DBXLIB		Library Name
DBXREL		Relational File
DBXLFI		Long File Name
DBXLB2		Library Name
DBXADB		Alias Relational DB Name
DBXALB		Alias Library Name
DBXAFL		Alias File Name

SYSCOLUMNS Column	Alternative Name	Description	Renamed Field
NAME	COLUMN_NAME	Long Field Name	DBILFL
TBNAME	TABLE_NAME	Long File Name	DBILFI
COLNO	ORDINAL_POSITION		INTEGER(DBIPOS)
DBNAME	TABLE_SCHEMA	Library Name	DBILB2
SYS_CNAME	SYSTEM_COLUMN_NAME	Field Name	DBIFLD
SYS_TNAME	SYSTEM_TABLE_NAME	File Name	DBIFIL
SYS_DNAME	SYSTEM_TABLE_SCHEMA	Library Name	DBILIB

QADBIFLD Column	Alternative Name	Description	Renamed Field
DBILIB		Library Name
DBIFIL		File Name
DBIFLD		Field Name
DBIREL		Relational File
DBILB2		Long Library Name
DBILFI		Long File Name
DBILFL		Long Field Name

QADBIFLD Column	Alternative Name	Description	Renamed Field
DBILIB		Library Name
DBIFIL		File Name
DBIFLD		Field Name
DBIREL		Relational File
DBILB2		Long Library Name
DBILFI		Long File Name
DBILFL		Long Field Name

For additional information on the DB2/400 system cross-reference files, refer to the DB2 for OS/400 SQL Reference appendices.

If you believe you have found corruption in the system cross-reference files, you should contact a qualified specialist to verify the results. If the tables are corrupted, run RCLSTG *DBXREF from a restricted state to rebuild the system-cross reference files.

If your system is at a V5R4 or later version of operating system, you can take advantage of a new feature that can recover cross-reference information for a specific library without taking the entire system to a restricted state. This new feature is the RCLDBXREF command. This command can be used to check the state of the cross-reference information for all libraries by specifying the command with the *CHECK option: RCLDBXREF *CHECK. To attempt to correct the cross-reference information for a specific library without taking the system to a restricted state, use the RCLDBXREF *FIX MYLIB command where MYLIB represents the specific library to correct. See the help information on this command for further details regarding the use of this command.

Caution: On large systems, the RCLSTG operation can take long periods of time. It is recommended that this operation be done only if absolutely necessary or by recommendation from a developer or qualified specialist. In addition, see the Warning notice later in this document.

Verifying Database Host Server Objects

Prior to V5R1, the DB Host Server uses logical files or views in the QIWS library to perform some catalog requests. The most common error involving these views and indexes is that they do not exist because of a failed LPP installation or restore. To verify that these views exist, use the command WRKOBJ OBJ(QIWS/QAZD*) to ensure that the QAZDTBL* files are present on the system. If these files are present, they are likely all right. If the files are missing or damaged, use CALL PGM(QIWS/QZDAXRLF) PARM(DEL) to delete the existing files, and CALL PGM (QIWS/QZDAXRLF) PARM(CRT) to create the files. The QAZDASRC source file, in library QIWS is required for the QZDAXRLF create command to work successfully. If this source file is not present on the system, the file must be restored from backup or installation media.

Caution: On large systems, the QZDAXRLF create operation can take long periods of time. It is recommended that this operation be done only if absolutely necessary or by recommendation from a developer or qualified specialist.

On rare occasions, incorrect output can be caused by a mismatch between the information stored in package QIWS/QZDAPKG at pre-V5R1 releases or QSYS/QZDAPKG at V5R1 and the current version of the underlying DB2/400 system catalog tables. The package can be deleted using the DLTSQLPKG command (all database server jobs must be ended first). For a step-by-step procedure to delete this SQL package, refer to document N1015556, Deleting SQL Package of the Database Host Server.The package is re-created when the database server jobs are restarted.