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.
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.
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.
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.
SQL sqlengine SET ODBCxxxx procname
where:
Is the internal server routine name. Possible values are: ODBCTABL, ODBCCOLS, ODBCPKEY, ODBCSTAT, ODBCPROC, ODBCPRCC, ODBCSCOL, ODBCCPRV, ODBCFKEY, and ODBCTPRV.
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:
SQL sqlengine SELECT code;
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.
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
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:
Indicates the data source. You can omit this value if you previously issued the SET SQLENGINE command.
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.
WebFOCUS |