Questions on a 'shadow catalog' and ODBC

[datapro01]

Running DB28.2 on AIX 5.2.

I have two questions if its possible for anyone to assist.

1. Is it possible to make use of a 'Shadow Catalog' on DB2 UDB v8.2 on
AIX? If so, how? Can anyone give me an example of the SQL to create
the appropriate view and catalog table?

2. Is there anyway via the ODBC datasource parameters to influence the
SQLTables and SQLColumns stored procedures to use a schema_name when
reading catalog information during the initial connection? These
procedures do have a parameter that can be supplied to limit the search
to a specific schema but I have no control over this application
functionality.

[Knut Stolze]

da*******@yahoo .com wrote:

Running DB28.2 on AIX 5.2.

I have two questions if its possible for anyone to assist.

1. Is it possible to make use of a 'Shadow Catalog' on DB2 UDB v8.2 on
AIX? If so, how? Can anyone give me an example of the SQL to create
the appropriate view and catalog table?
What kind of information shall the 'shadow catalog' contain?
2. Is there anyway via the ODBC datasource parameters to influence the
SQLTables and SQLColumns stored procedures to use a schema_name when
reading catalog information during the initial connection? These
procedures do have a parameter that can be supplied to limit the search
to a specific schema but I have no control over this application
functionality.

What exactly do you want to control? If you just want to get all the tables
in a particular schema, provide the schema name. Then rely on the function
to do it the best way possible.

p.s: SQLTables and SQLColumns are functions, not (stored) procedures.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[kevin_burton]

Knut,

This questions was posted by a DBA that I am working with at a client
site. My name is Kevin Burton, and I am the one working on this issues.
Below is my response to your question:

This is a third party application that is connecting to the DB2
database base via an ODBC connection. Upon connection, the call to the
SQLColumns function is generating 1,000,000+ reads for each connection.
It appears that the connection is executing the SQLColumns function
once for each table returned from the SQLTable call. The third party
application is not supplying the SQLColumns call with the schema or
table name. This is resulting in the all of the columns being read
from the system table for each table. Hence the 1,000,000+ reads for
each connection.

What I want to do is force the SQLTable and SQLColumns function call to
use a schema name. I can't change the application code. After reading
some documentation on ODBC parameters, I believe I should be able to
set the 'clishema' parameter to a schema value. As a result, these
functions should then use that schema supplied instead of the system
schema (SYSIBM).

If this did not work, I have gotten the idea that we could implement a
'Shadow Schema'. We can create views of the system tables that contain
only the tables, views, columns that we want in the target schema. If
the 'clischema' parameter is set, the SQLTable and SQLColumn functions
would then use the views in the target schema. I tried doing this but
it did not seem to work. Since the the SQLTables and SQLColumns calls
are the problem, I tried creating views of the system tables that were
named SQLTABLES, TABLES, SQLCOLUMNS and COLUMNS in the target schema.
This did not appear to work. I am not sure if I have to create actual
tables in the target schema as well as the views for them to get this
to work or not. Any thoughts?

I was hoping to get some verification that this would work. Is there
anyway to see what the SQLTables and SQLColumns functions are doing?
Can we confirm that the functions should look at a specific schema if
supplied?

Thank You

- Kevin Burton

[JoKlassen]

ke**********@sb cglobal.net schrieb:

Knut,

This questions was posted by a DBA that I am working with at a client
site. My name is Kevin Burton, and I am the one working on this issues.
Below is my response to your question:

This is a third party application that is connecting to the DB2
database base via an ODBC connection. Upon connection, the call to the
SQLColumns function is generating 1,000,000+ reads for each connection.
It appears that the connection is executing the SQLColumns function
once for each table returned from the SQLTable call. The third party
application is not supplying the SQLColumns call with the schema or
table name. This is resulting in the all of the columns being read
from the system table for each table. Hence the 1,000,000+ reads for
each connection.

What I want to do is force the SQLTable and SQLColumns function call to
use a schema name. I can't change the application code. After reading
some documentation on ODBC parameters, I believe I should be able to
set the 'clishema' parameter to a schema value. As a result, these
functions should then use that schema supplied instead of the system
schema (SYSIBM).

If this did not work, I have gotten the idea that we could implement a
'Shadow Schema'. We can create views of the system tables that contain
only the tables, views, columns that we want in the target schema. If
the 'clischema' parameter is set, the SQLTable and SQLColumn functions
would then use the views in the target schema. I tried doing this but
it did not seem to work. Since the the SQLTables and SQLColumns calls
are the problem, I tried creating views of the system tables that were
named SQLTABLES, TABLES, SQLCOLUMNS and COLUMNS in the target schema.
This did not appear to work. I am not sure if I have to create actual
tables in the target schema as well as the views for them to get this
to work or not. Any thoughts?

I was hoping to get some verification that this would work. Is there
anyway to see what the SQLTables and SQLColumns functions are doing?
Can we confirm that the functions should look at a specific schema if
supplied?

Thank You

- Kevin Burton

Kevin,

take a look at the db2ocat tool at
ftp://ftp.software.ibm.com/ps/products/db2/tools/

it creates a "shadow system catalog" for ODBC calls

HTH
Joachim

[kevin_burton]

Jo*******@email .com wrote:

ke**********@sb cglobal.net schrieb:

Knut,

This questions was posted by a DBA that I am working with at a client
site. My name is Kevin Burton, and I am the one working on this issues.
Below is my response to your question:

This is a third party application that is connecting to the DB2
database base via an ODBC connection. Upon connection, the call to the
SQLColumns function is generating 1,000,000+ reads for each connection.
It appears that the connection is executing the SQLColumns function
once for each table returned from the SQLTable call. The third party
application is not supplying the SQLColumns call with the schema or
table name. This is resulting in the all of the columns being read
from the system table for each table. Hence the 1,000,000+ reads for
each connection.

What I want to do is force the SQLTable and SQLColumns function call to
use a schema name. I can't change the application code. After reading
some documentation on ODBC parameters, I believe I should be able to
set the 'clishema' parameter to a schema value. As a result, these
functions should then use that schema supplied instead of the system
schema (SYSIBM).

If this did not work, I have gotten the idea that we could implement a
'Shadow Schema'. We can create views of the system tables that contain
only the tables, views, columns that we want in the target schema. If
the 'clischema' parameter is set, the SQLTable and SQLColumn functions
would then use the views in the target schema. I tried doing this but
it did not seem to work. Since the the SQLTables and SQLColumns calls
are the problem, I tried creating views of the system tables that were
named SQLTABLES, TABLES, SQLCOLUMNS and COLUMNS in the target schema.
This did not appear to work. I am not sure if I have to create actual
tables in the target schema as well as the views for them to get this
to work or not. Any thoughts?

I was hoping to get some verification that this would work. Is there
anyway to see what the SQLTables and SQLColumns functions are doing?
Can we confirm that the functions should look at a specific schema if
supplied?

Thank You

- Kevin Burton

Kevin,

take a look at the db2ocat tool at
ftp://ftp.software.ibm.com/ps/products/db2/tools/

it creates a "shadow system catalog" for ODBC calls

HTH
Joachim

I was able to accomplish what I needed by setting the SYSCHEMA and
SCHEMALIST parameters for the ODBC connection.

We are testing and validating the changes but things are looking very
good.

Thank You!

- Kevin