Contents:

1.0........................................ Catalog Tables and Views
2.0........................................ DDS vs SQL metadata
3.0........................................ ODBC Remarks
4.0........................................ Returning a Meaningful Column Name to the User
5.0........................................ Troubleshooting


1.0 Catalog Tables and Views

To understand how IBM i Access ODBC and data transfer return database catalog information, we first need to take a look at how the database manager (DB2 on i) tracks information on the tables and files that reside on IBM i. This information is known as metadata.

DB2 on i consists of one system database (*SYSBASE) and each independent ASP consists of an additional set of schemas that are included with the *SYSBASE when they are accessed. DB2 maintains a catalog of this information, which includes detailed information on all of the tables, columns, views, indexes, packages, procedures, parameters, and constraints that exist.

DB2 on i strives to support current SQL standards while maintaining backward compatibility to older applications and databases dating back to the IBM® System/38™ (S/38) and IBM® System/36™ (S/36). The catalog information therefore tracks information on ISO/ANSI standard SQL objects, native relational database objects, and certain non-relational database objects. "Native" relational database objects refer to certain physical and logical files, also referred to as "externally described files," that are created using Data Descriptive Specification (DDS).

The metadata for objects created with SQL is very different from that of DDS created objects. For example, the maximum length of a table name created with SQL is 128 characters, while a file created from DDS can have a maximum length of only 10 characters. Furthermore, applications that use native (non-SQL) file access may only be able to work with the native metadata. To allow both DDS based applications and SQL based applications to work with the same files, the database tracks both SQL information and "system" (DDS based) information for every object. Each table and each field (column) in a table will have both a DDS compatible name (system name) and a SQL compatible name (long name). Optionally, each table and each field in a table can be described with a DDS compatible comment ("text") and/or a SQL compatible comment (long comment).

Note that S/36 files (also referred to as program described files or flat files) and other non-relational files are not returned in catalog information queries. This decision is intentional. These types of files either cannot be accessed through SQL (DDM and IDDU files for example) or might require special handling of the data by the application (program described files).


2.0 DDS vs SQL metadata

The following table shows how the table or column name fields in the catalog are created, shows the maximum length of the name, and gives the catalog table and field where the name is located. Note that Client Access ODBC always returns the long name. Client Access Data Transfer can return the short or long name depending on the "Display Field Alias" property setting.
 
The following table shows how comments and column headings associated with a table or column are created, shows the maximum length of the field, and gives the catalog table and field name where the information is located. Note that Client Access ODBC returns different results depending on the SQL object Comment setting. Client Access Data Transfer's query builder will always display the File Text.
  ** Length of TEXT can be greater than 50 characters, but only the first 50 characters can be used by high-level language compilers.
(1) Value returned depends on the setting configured in the ODBC data source’s Object Description Type Field. See 3.0 ODBC Remarks.


3.0 ODBC Object Comment

The ODBC data source parameter Object Description Type allows you to control whether the native IBM i object description or the SQL object comment will be returned in the REMARKS column for ODBC API catalog calls such as SQLTables, SQLColumns, and so on. Refer to the table above for the specific types of descriptions returned for both. The default is to use the IBM i object description. Use the ODBC administrator function to alter your data source if needed.

4.0 Returning a Meaningful Column Name to the User

If the actual column names in the IBM i table are cryptic and you would like to return a more meaningful name to the application and user, you can add an ALIAS to the data description specification (DDS) for the physical file. This updates the IBM i catalog Long Field Name, which will be returned to the application. You can also modify the query. For example, rather than select cusnum from qiws.qcustcdt use select cusnum as "Customer Number" from qiws.qcustcdt.

5.0 Troubleshooting

If your application is not returning the table or column name that you expect, you can query the catalog tables to directly view the catalog information for your libraries, tables, or columns.

Table information is stored in QSYS/QADBXREF. Column information is stored in QSYS/QADBIFLD. The database manager also provides views over these tables: QSYS2/SYSCOLUMNS and QSYS2/SYSTABLES.

For example, to use Query/400 to query QSYS/QADBXREF for table name information on tables in QGPL, you would:
 
For more information refer to the SQL Reference located in the iSeries Information Center.

For more information on the ODBC naming conventions, refer to Appendix C SQL Grammar in the Microsoft ODBC 2.0 Programmer’s Reference and SDK Guide.