Problem connecting to new set of tables

[DrSmyth]

Hi,

I've having a designer problem that i can't seem to get to the bottom of.

Basically I have a universe set up that is looking at a certain set of tables in a data provider, I open the table browser, find an owner called DDPSCS1U, click on it and I see a bunch of tables.

However, I now want to look at another table owners tables, this time they're called SKPS001, So I go through the same process, click on the table browser, find the owner called SKPS001 but this time i see no tables. (Nearly forgot, we are using DB2 with IBM DB2 Client middleware).

I've tried changing the DB2CLI.ini file to look at the correct tables (see below for settings) but again still no joy.

Can anybody help

Db2cli.ini parameters:
[TestSafeKeeping]
PATCH2=3,6,14
PATCH1=1024
LONGDATACOMPAT=1
LOBMAXCOLUMNSIZE=1048575
TXNISOLATION=1
APPENDAPINAME=1
SCHEMALIST=CURRENT SQLID
CURRENTSQLID=SKPS001
DBNAME=SKPS001
DBALIAS=DST1PSCU

 

[skrandel]

This is almost always a permissions problem. The id you are using in the connection string doesn't have privileges on the owner you referencing.

Steve Krandel
VERITAS Software

 

[blom0344]

The DB2CLI.ini file stores entries for the respective databases that are registrated. Each database can hold one or more schema's (= owners) with different sets of permissions on (sets of) tables/views.
Traditionally DBA's use different schema's to be able to regulate user permissions. The .ini file is not the place to look or modify, this is a DBA's responsibility.

Ties Blom
Information analyst

 

[DrSmyth]

Thanks for the advice gents.... It's a bit of a strange one this though, I've been in touch with the DBA and they monitored the SQL as I was trying to connect.

They are adament that that I have sufficient user privalidges to connect to the new schema, but the sql that is being generated by BO limits me to viewing the previous tables that I could already view. To prove a point I created a new odbc connection in the DB2CLI.ini file as follows:

[TestSafeKeeping]
SCHEMALIST=CURRENT SQLID
CURRENTSQLID=SKPS001
DBALIAS=DST1PSCU

Now if i use this in conjunction with Access it connects fine and i can see the tables, however, I cannot view this connection in BO... Does BO actually use the DB2CLI.ini file for it's connection parameters or is all the admin done in Designer?? If it's all done in designer, is there a way of amending the SQL used for the connection?

Not sure if I'm going down the right path here as it's very much been a case of trial and error. Plus the DBA's are refusing to give any more help as i've managed to connect Access to the database so they seem to think that they've done all they can!!!

Thanks in advance for any further advice you can offer..

Andy

 

[blom0344]

I think that BO connection's are stored by BO itself. You can get overview of available (secure) connections through Supervisor (Tools --> Connections)
You can define any number of connections on a database, each with their own authorization id and password. This should give you manipulation on which set of tables can be accessed.

With more exotic databases (Interbase and such) I have experienced problems with accessing tables. I remember that fiddling with the array fetch size in the connection (lowering it) resolved this. (I do not think that this would be an issue with DB2 though)

In any case ,do not modify the DB2CLI.INI file by hand. Use client configuration assistant if you can ....


Ties Blom
Information analyst

 

[DrSmyth]

Bit of an update to my last post.... All is not rosey with MSAccess, Although I can view all the tables within the new schema using access and my new parameters, I can't view the table data.....

I keep getting an "Unsuccesful execution caused by an unavailable resource reason code 00C200E2" error.... Tried googling the reason code and only got a single response which was well over my head....

Anyway, means I might be able to get some more help from the DBA's....

 

[DrSmyth]

Was just writing my latest thread when you posted your's Ties... Might have a playaround with supervisor...

 

[blom0344]

Dr Smyth,

I have several application that run in Access on DB2 data and from early DB2 version 5.2 on I HAD to use additional actions to enable a stable connection from Access to The back-end. In fact, I have some documentation on this for our foreign offices to configure proper set-up...

Ties Blom
Information analyst

 

[blom0344]

'Unavailable resource' may indicate that something like a reorg/runstats are performed on the database at that particular time. This is a pretty wild guess, but the DBA may have a script that routinely performs maintenance on the database. A reorg will will rebuild the tables and data may not be available for queries......

Ties Blom
Information analyst

 

[DrSmyth]

Is this still the case we're on DB2 version 7.... What sort of additional actions did you have to do???

 

[DrSmyth]

In that case I'll give the DBA a call....

Thanks again Ties

 

[blom0344]

1. Use ODBC administration
2. Choose the DSN involved and choose configure
3. Make connection with the database
4. In the next pop-up screen enter authorization id and password and choose optimize. Optimize for MS Access


Ties Blom
Information analyst

 

[DrSmyth]

Cheers Ties... Will give it a go...