Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 12 Accessing catalog information with CSPs

General information

General information

Because the catalog structures for AS/400 and Adaptive Server are different, client applications written to access the Adaptive Server catalog may not work in the AS/400 environment. One possible solution is to re-code the client application queries to send those queries directly to the AS/400 system tables.

Another solution is to use CSPs to access and return information from the system catalog of the target database.

When you invoke a CSP, the access service executes a stored procedure that returns a result set that attempts to match the results an Adaptive Server would return under the same circumstances. Because the AS/400 catalog is significantly different from the Adaptive Server catalog, an exact match is impossible. To make the results match, the access service performs additional computations as the rows return.

CSPs are compatible with the catalog interface for the Open Database Connectivity (ODBC) Application Program Interface (API).

CSP properties

Configuration properties specify the objects about which an access service returns information from CSPs. For information about configuring these properties, see CSP set statements.

"Catalog stored procedure properties" To change operating values for a client connection, use set statements. For information about CSP set statements, see CSP set statements.

How the Access Service assembles stored procedure information

When you invoke a CSP, the access service creates a SQL statement that returns a result set that matches, as closely as possible, the Microsoft ODBC 2.0 specification results. Because the AS/400 system catalog is significantly different from the ODBC specification, an exact match is impossible. To make the results match, the access service performs additional computations as it returns the rows. In some cases, the SQL statement is quite complex and must assemble the requested information from multiple sources.

In a few cases, such as sp_stored_procedures, sp_sproc_columns, and sp_server_info, information is stored in tables that were created when DirectConnect was installed.

Some information is static, depending upon the version of AS/400 you are using. In these cases, the access service uses memory tables to improve the speed of operation. The following CSPs and system procedures use memory tables:

• sp_capabilities

• sp_datatype_info

• sp_helpserver

• sp_sqlgetinfo

Supported CSPs

The following table shows CSPs that an access service supports.