IBM Support

Verifying System Catalog Information for ODBC Use

Troubleshooting


Problem

The iSeries Access for Windows ODBC driver derives metadata from the IBM DB2 UDB for iSeries catalog tables. If the ODBC driver returns incorrect metadata information, the catalog tables should be checked. This document describes how to verify the catalog table information is correct.

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:

oThe IBM DB2/400 system catalog tables
oLogical files (views) and SQL packages in library QIWS at pre-V5R1 that are part of the database host server
oApplication 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 ColumnAlternative NameDescriptionRenamed Field
NAMETABLE_NAMELong Table NameDBXLFI
DBNAMETABLE_SCHEMALibrary NameDBXLB2
SYS_TNAMESYSTEM_TABLE_NAMEFile NameDBXFIL
SYS_DNAMESYSTEM_TABLE_SCHEMALibrary NameDBXLIB
TBDBNAMEBASE_TABLE_SCHEMAAlias Table NameDBXALB
TBNAMEBASE_TABLE_NAMEAlias File NameDBXAFL

QADBXREF ColumnAlternative NameDescriptionRenamed 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 ColumnAlternative NameDescriptionRenamed Field
NAMECOLUMN_NAMELong Field NameDBILFL
TBNAMETABLE_NAMELong File NameDBILFI
COLNOORDINAL_POSITION
INTEGER(DBIPOS)
DBNAMETABLE_SCHEMALibrary NameDBILB2
SYS_CNAMESYSTEM_COLUMN_NAMEField NameDBIFLD
SYS_TNAMESYSTEM_TABLE_NAMEFile NameDBIFIL
SYS_DNAMESYSTEM_TABLE_SCHEMALibrary NameDBILIB

QADBIFLD ColumnAlternative NameDescriptionRenamed 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 ColumnAlternative NameDescriptionRenamed 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.

Internal Use Only

CA/400 WIN 95 EXPRESS CLIENT (5769XE100)

[{"Product":{"code":"SGYQGH","label":"IBM i"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1;7.1;4.5.0;5.1.0;5.2.0;5.3.0;5.4.0;6.1.0","Edition":""},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""}]

Historical Number

21455731

Document Information

Modified date:
17 June 2018

UID

nas8N1019539