Specify and use multiple libraries in ODBC connection string

Question

My odbc connection string for connecting to DB2i looks like this:

Driver={Client Access ODBC Driver (32-bit)};system=xx.xx.xx.xx;dbq=LIB1 LIB2 LIB3 LIB4 LIB5 LIB6 LIB7 LIB8;languageid=ENU;qrystglmt=-1;cmt=0;signon=1

The above connection string specifies multiple libraries/schemas for use. But when I try to access a file/table from a library other than the first one (like from LIB2...LIB8) I get a exception saying "FILE xx not found in LIB1"

Why does not it automatically search for the file/table in other specified libraries, it searches for the file in the first library only?

I need a workaround for this situation.

Linet

Answer 1

Use "system naming" mode, by adding naming=1 to your connection string.

In your library list, place a comma before the first library.

Driver={Client Access ODBC Driver (32-bit)};system=systemname;naming=1;
dbq=,LIB1,LIB2,LIB3,LIB4,LIB5,LIB6,LIB7,LIB8;languageid=ENU;cmt=0;signon=1

Pish

That's a nice article you linked to on "system naming" versus "SQL naming". I think it's worth mentioning that the other article in the series goes on to explain that you need system naming if you want to implicitly search multiple libraries (a.k.a. schemas), because SQL naming mode gives you only one schema for unqualified names. But the article points out that the default schema can contain aliases or views which access other schemas, in case that is an option for some people. – Shoop

Perfect. The problem for me was that I was using "Default Collection" or "LibraryList" in order to set the default schema/scope. "dbq"=myschema solved my issues.... This is a bit undocumented. – Forth

Answer 2

This works as documented in the manual:

The library list is used for resolving unqualified stored procedure calls and finding libraries in catalog API calls. ... Note: The first library listed in this property will also be the default library, which is used to resolve unqualified names in SQL statements.

Blucher

Answer 3

As stated above, Schema/library list is used to resolve functions/procedure names, not tables.

Let assume you need to read data from lib1.tab1 and lib2.tab2;

Here my personal workarounds (from easy to complex):

a) ask the db admin to have - for each table you need to use - the corresponding schema name, then do "select * from lib1.tab1 join lib2.tab2 on [...]" ;-) b) ask the db admin to create on schema "MyAlias" several alias (create alias) for each table you want to use. Then do "set current schema=MyAlias" followed by all the SQL statement you need e.g. "select * from tab1 join tab2". Since you’re querying myalias.tab1 which is an alias pointing to table lib1.tab1 it should work.

c) Complex: create your own SQL function that returns the corresponding schema_name for a table (e.g. myfunct('TAB1'). This could be done reading system view “qsys2.systables” where table_name=’TAB1’ and returning TABLE_SCHEMA column, which is a varchar(128). Once you got it, build up a dynamically prepared using the variable you just obtained. E.g. "set mylib = myfunct('TAB1'). "set mystmt = 'select * from '||table_schema || ‘.tab1’ …”

Prepare mystmt and then execute mystmt.

I did something similar in VBA using ado ibmdrda and it worked.

hope this helps.

f.

Broody