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. 4 1888 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
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 ke**********@sbcglobal.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 Jo*******@email.com wrote: ke**********@sbcglobal.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Acer |
last post by:
hi,
what's the best way to display pictures with an automatic generated shadow ?
|
by: Ralf Obst |
last post by:
Hello,
we get a message SQL0204N when trying to import tables from a DB2/VM
into an MS Access database.
The message text is: ... "DB2/VM SQL0204N 'SQLTables ' not defined
".
When...
|
by: kenfar |
last post by:
I've got a set of redundant marts that I'm trying to catalog on the
client side to allow us to do two things:
1. manually recatalog to point at either of the two fast marts
2. automatically...
|
by: Eric E |
last post by:
Two questions regarding ODBC.
1) I am connecting to a MySQL database using the following code:
Function LoginToMySQL(sUsername As String, sPW As String) As Boolean
Dim dbMySQL As Database
Dim...
|
by: Barry Kelly |
last post by:
I'm designing an application framework which will, amongst other things,
live in an assembly hosted in the ASP.NET worker process, servicing
webservice requests.
Here's the scenario:
APPFX is...
|
by: Damien Anselmi |
last post by:
I am using the CompileAssemblyFromSource method of an ICodeCompiler instance
to dynamically compile an assembly used by a Web Application. The assembly
may be recompiled any number of times during...
|
by: MP |
last post by:
I'm interested to learn how to use mdb files via ado/adox via vb6
Since I'm not using Access are those types of questions o.t. here?
I see very few ado questions here. But there's a lot more...
|
by: eholz1 |
last post by:
Hello CSS group,
I saw a beautiful effect that I would like to use either by CSS or
using photoshop to create the image/effect (maybe even imagemagick)
the site address is:...
|
by: naiduG |
last post by:
Hi all,
How to create Shadow ODBC connectivity for DB2 for ETLs
Can any one help out in this???
Thanks in advance
Naidu.
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |