AS400 / DB2 cross-library query problem

Question

Hi - I'm querying an Iseries from ODBC in my app and am trying to perform a query that returns results from 2 tables. I need to join the tables but the tables are in different libraries. I don't want to use library identifiers in my query as my libraries change as I move from dev>qa>prod. However, I am certain that these tables will only be in these libraries and there will be no duplicates in other libraries. Is there a way to do this without specifying the Iseries library?

In essense, I want this:

  select DISTINCT  GIDNBR, VSL00100.GRNAME 
FROM **QACOMMON**.CMPGRID2 CMPGRID2 INNER JOIN **QAVISLIVE**.VSL00100  VSL00100  ON VSL00100.GRNO=CMPGRID2.GIDNBR AND  
                      VSL00100.GRSUB=CMPGRID2.GIDSUB AND  
                      VSL00100.GRLOC=CMPGRID2.GIDLOC AND  
                     VSL00100.GRPOOL=CMPGRID2.GIDPOL

to look like this:

select DISTINCT  GIDNBR, VSL00100.GRNAME 
FROM CMPGRID2 CMPGRID2 INNER JOIN VSL00100  VSL00100  
ON 
VSL00100.GRNO=CMPGRID2.GIDNBR AND  
                  VSL00100.GRSUB=CMPGRID2.GIDSUB AND  
                  VSL00100.GRLOC=CMPGRID2.GIDLOC AND  
                 VSL00100.GRPOOL=CMPGRID2.GIDPOL

Any ideas?

Answer 1

You need to go into the configuration for your ODBC connection. On the connection you are using click on "Configure", then select the "Server" tab. The library list option is what you'll need to play with.

Based on your examples it looks like you need to have it say "QACOMMON QAVISLIVE". (with no quotes)

Also you will need to change the naming convention to *SYS instead of *SQL and make the default collection field blank. Make sure your system administrator sets the CURLIB parameter on the user profile you are using to where ever you want new files to go.

Then just save it and try it again. Unfortunately you'll have to either have different data sources or change the ODBC connection when you are switching around between test, production, and whatever else you have.