Metadata Services With SQLENGINE SET

In this section:

When the server is dedicated to accessing one Relational Database Management System (RDBMS) using the SET SQLENGINE command in the global profile, metadata calls to the server are processed against the native catalogs of the RDBMS.


Top of page

x
How Applications Access Metadata

The metadata procedures used by applications to query the native catalog directly are:

The following table shows the relationship between the ODBC call and the API call:

ODBC Call

API Call

SQLTables

ODBCTABL

SQLColumns

ODBCCOLS

SQLPrimaryKeys

ODBCPKEY

SQLStatistics

ODBCSTAT

SQLProcedures

ODBCPROC

SQLProcedureColumns

ODBCPRCC

SQLSpecialColumns

ODBCSCOL

SQLColumnPrivileges

ODBCCPRV

SQLForeignKeys

ODBCFKEY

SQLTablePrivileges

ODBCTPRV

You can use the following two commands to control or override table and column metadata calls:

SQL sqlengine SET ODBCCOLSSORT
SQL sqlengine SET ODBCTABL

You can include these commands in any of the supported server profiles.



x
Obtaining Column Information (DB2 only)

How to:

When you issue either the SQLColumns or ODBCCOLS Metadata call from a client application, by default, the server requests the columns from DB2 in colno order.



x
Syntax: How to Request the Sort Order of Column Data
SET ODBCCOLSSORT {ON|OFF}

where:

ON

The column data is requested from the server with order by colno. This value is the default.

OFF

The column data is returned in unsorted order.


Top of page

x
Obtaining User-Defined Metadata

How to:

When a client application issues either an ODBC or API metadata call, the server runs internal procedures that issue default SQL against the native RDBMS catalogs. You can issue your own SQL to run in place of the default SQL. You can specify this type of override for any of the internal server routines that deal with metadata.



x
Syntax: How to Request User-Defined Metadata
SQL sqlengine SET ODBCxxxx procname

where:

ODBCxxxx

Is the internal server routine name. Possible values are: ODBCTABL, ODBCCOLS, ODBCPKEY, ODBCSTAT, ODBCPROC, ODBCPRCC, ODBCSCOL, ODBCCPRV, ODBCFKEY, and ODBCTPRV.

procname

Is the procedure to run when the server receives the metadata call. This procedure must be available through the FOCEXEC ddname allocation in the server JCL.

Note: If this override procedure is used on a server running under MVS, it will add approximately 600K of storage above the line for each user.

When coding an override procedure, care must be taken to maintain the select list (answer set layout). The select list must conform to the ODBC specification for the return from the SQLTables call. See the ODBC 2.0 Programmer's Reference and SDK Guide for the SQLTables specification layout. Also, when using this override procedure, the parameters that are sent with the Metadata call need to be parsed correctly.

The override procedure should take the following format:

  1. Dialogue Manager code to parse metadata call parameters.
  2. SQL sqlengine  SELECT code;
  3. TABLE
    ON TABLE PCHOLD FORMAT ALPHA
    END

Items 1 and 2 above are user coded; item 3 must always be present at the end of the procedure as coded above.



Example: Returning a List of Tables

The following sample code found in qualif.EDARPC.DATA(DB2ODBC1) returns a list of tables that the connected user is authorized to INSERT, UPDATE, DELETE, and SELECT. It is a sample of how to code a DB2 override procedure for ODBCTABL. It is one of several ways to code SQL to return an answer set for the ODBCTABL call. You can code any relevant query as long as the SELECT list is maintained.

In a server profile, issue SQL DB2 SET ODBCTABL DB2ODBC1, and then execute the following RPC request on the server:

ODBCTABL  ,<NULL>,,,,0,0,*

The following example matches the above ODBCTABL call:

-*
-* Dialogue Manager code to parse the ODBCTABL parameter list
-*
-DEFAULTS 1=' ',2='%',3='%'
-IF &2 NE '<NULL>' THEN GOTO LAB1;
-SET &2 = '%';
-LAB1
-IF &3 NE ' ' THEN GOTO LAB2;
-SET &3 = '%';
-LAB2
-*
-* SQL SELECT code
-*
SQL DB2
SELECT ' ',T2.CREATOR,T2.NAME,'TABLE',' '
FROM SYSIBM.SYSTABAUTH T1,SYSIBM.SYSTABLES T2
WHERE T1.GRANTEE  = USER
AND   T1.TTNAME   LIKE '&2'
AND   T1.TCREATOR LIKE '&3'
AND   T2.TYPE     = 'T'
AND  (T1.DELETEAUTH IN  ('G','Y')
OR    T1.INSERTAUTH IN  ('G','Y')
OR    T1.SELECTAUTH IN  ('G','Y')
OR    T1.UPDATEAUTH IN  ('G','Y'))
AND   T1.TTNAME   = T2.NAME
AND   T1.TCREATOR = T2.CREATOR
UNION
SELECT ' ',T2.CREATOR,T2.NAME,'VIEW',' '
FROM SYSIBM.SYSTABAUTH T1,SYSIBM.SYSTABLES T2
WHERE T1.GRANTEE  = USER
AND   T1.TTNAME   LIKE '&1'
AND   T1.TCREATOR LIKE '&2'
AND   T2.TYPE     = 'V'
AND  (T1.DELETEAUTH IN  ('G','Y')
OR    T1.INSERTAUTH IN  ('G','Y')
OR    T1.SELECTAUTH IN  ('G','Y')
OR    T1.UPDATEAUTH IN  ('G','Y'))
AND   T1.TTNAME   = T2.NAME
AND   T1.TCREATOR = T2.CREATOR
ORDER BY CREATOR,NAME;
-*
-* The following code must always be present
-*
TABLE
ON TABLE PCHOLD FORMAT ALPHA
END

Top of page

x
Maintaining Upward Compatibility

In Version 4.3.x or earlier, an Extended Catalog (SYSOWNER table) was created at installation time. This provided additional control to the list of tables returned with the SQLTables or ODBCTABL call. In Version 5.1.0 and higher, this table is no longer created. If you need to use this table from a previous version of the server to continue to have control over the list of tables, issue the following in any supported server profile:

SQL sqlengine SET OWNERID ownerid

where:

sqlengine

Indicates the data source. You can omit this value if you previously issued the SET SQLENGINE command.

ownerid

Identifies the creator or owner of the Extended Catalog table SYSOWNER. If this command is used, the SQL generated to provide a list of tables will be limited by the owner names in the SYSOWNER table.

This command is only supported for customers who have configured a Relational Gateway in previous releases of the server and want to continue with the same configuration.


iWay Software