?
Solved

iSeries/Client Access - How do I get a list of libraries/environments via ADO?

Posted on 2007-07-25
7
Medium Priority
?
2,131 Views
Last Modified: 2013-12-25
Hi Everyone,

I use ADO/ODBC (via VB6) to connect to one of our AS400 boxes to query some files in different libraries.  I'm trying to figure out how I can determine if a library exists so I can have a dynamic SQL statement (in case libraries get added/removed).  I'm guessing I have to use the OpenSchema method of the ADODB connection object, but I'm open to other ideas if you have any. This is what I have so far

Here is (a sanitized version of) the connection string I use:
"ODBC;DSN=MYDSN;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=SERVER.DOMAIN.COM;"

Currently there are 4 libraries that I pull data from, the sql statements are the same, and I already generate this dynamically from an array, but here is the overall sql statement:

SELECT digits(MyTbl1.EMRNPA)||digits(MyTbl1.EMREXC) As NPANXX, MyTbl1.EMRIXC As CIC, MyTbl1.EMRSTC as SourceType, COUNT(*) as QTY FROM Lib1.TblName MyTbl1 WHERE MyTbl1.EMRIXC NOT IN ('0000','0001','0002') GROUP BY MyTbl1.EMRNPA, MyTbl1.EMREXC, MyTbl1.EMRIXC, MyTbl1.EMRSTC
UNION ALL
SELECT digits(MyTbl2.EMRNPA)||digits(MyTbl2.EMREXC) As NPANXX, MyTbl2.EMRIXC As CIC, MyTbl2.EMRSTC as SourceType, COUNT(*) as QTY FROM Lib2.TblName MyTbl2 WHERE MyTbl2.EMRIXC NOT IN ('0000','0001','0002') GROUP BY MyTbl2.EMRNPA, MyTbl2.EMREXC, MyTbl2.EMRIXC, MyTbl2.EMRSTC
UNION ALL
SELECT digits(MyTbl3.EMRNPA)||digits(MyTbl3.EMREXC) As NPANXX, MyTbl3.EMRIXC As CIC, MyTbl3.EMRSTC as SourceType, COUNT(*) as QTY FROM Lib3.TblName MyTbl3 WHERE MyTbl3.EMRIXC NOT IN ('0000','0001','0002') GROUP BY MyTbl3.EMRNPA, MyTbl3.EMREXC, MyTbl3.EMRIXC, MyTbl3.EMRSTC
UNION ALL
SELECT digits(MyTbl4.EMRNPA)||digits(MyTbl4.EMREXC) As NPANXX, MyTbl4.EMRIXC As CIC, MyTbl4.EMRSTC as SourceType, COUNT(*) as QTY FROM Lib4.TblName MyTbl4 WHERE MyTbl4.EMRIXC NOT IN ('0000','0001','0002') GROUP BY MyTbl4.EMRNPA, MyTbl4.EMREXC, MyTbl4.EMRIXC, MyTbl4.EMRSTC


What I'm trying to do is get a list of Libraries (Lib1, Lib2, Lib3, Lib4) from my AS400. There are some testing environments I don't want to pull from, so I'll need to check each library name (ie. starting with "Lib") but I can do that part; I'm just mentioning this in case there is some magical sql statement to pull from all libraries or something, which I wouldn't want.  I'm doing this in case I'm not in this position when new ones might get added so the actual code won't have to be modified.

I did try using .OpenSchema(adSchemaTables) but that did not work as I indended, and it looks like adSchemaCatalogs requires a library name so that doesn't appear to work either. I know enough about working with databases to get by, but I'm surely no expert with them (though I'm trying to eventually be good with them, they are my latest toy to learn from)

Any ideas? How can I get a list of libraries?

Thanks! Please don't hesitate to ask for any more information
Matt
0
Comment
Question by:mvidas
  • 4
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 19565865

This is one way:

select distinct    
           table_schema
from    systables
;

HTH,
DaveSlash
0
 
LVL 35

Author Comment

by:mvidas
ID: 19566130
Hi Dave,

My guess is that will do the same as looping through OpenSchema(adSchemaTables) but I'd still like to try it. I used the following sql statement:

"SELECT distinct table_schema from systables"


and got an error:

"SQL0204 - SYSTABLES in MYUSERNAME type *FILE not found."


Should I have changed 'systables' to something else, if so, what would I look for to know what to change it to?

Thanks
Matt
0
 
LVL 35

Author Comment

by:mvidas
ID: 19566157
I should probably add that the reason adSchemaTables didn't work as I wanted, is that it didn't list any of the libraries I know exist. My guess is that is retrieving system tables, and each Library name I'm trying to retrieve has tables of their own (like I use "Lib1.TblName" in the SQL statement), but as I said that is just my guess
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
Dave Ford earned 2000 total points
ID: 19566263

You're operating in *SQL naming convention (as opposed to *SYS), so just qualify the table with its schema.

e.g.
select distinct    
           table_schema
from    qsys2.systables
;

If you want to narrow your results down, you can simply add a WHERE clause:

e.g.
select distinct    
           table_schema
from    qsys2.systables
where  table_schema like 'ABC%'
;

HTH,
DaveSlash
0
 
LVL 35

Author Comment

by:mvidas
ID: 19566594
I understand.. I think (if I understand you correctly), I'm trying to get the "qsys2" from that. Maybe a little background information will help.

I work for a phone company which has millions of access lines. To prevent system overload (presumably), my company has split them into different environments/libraries based on region. When I first login to this as400 server via the emulator (iSeries Access for Windows), I'm presented with a list of Environments, asking which I'd like to use (ie Lib1, Lib2, etc, though they are named based on the region).

In trying to get you more information which may help, I just looked through iSeries Navigator, and I can see a list of the libraries via:

My Connections
 -> SERVER.DOMAIN.COM
    -> File Systems
       -> Integrated File System
          -> QSYS.LIB
             -> Library list is here

I tried using "SELECT distinct table_schema from qsys.systables" but got the same error as earlier (replacing QSYS with MYUSERNAME). I then tried (figuring it wouldnt work but wanting to try anyways) getting the schema from "qsys.lib.systables" and it didn't work either.

Matt
0
 
LVL 18

Expert Comment

by:Dave Ford
ID: 19566656

It's qsys2, not qsys.

Cut and paste the query in my previous post into your SQL environment.
0
 
LVL 35

Author Comment

by:mvidas
ID: 19567021
Sorry about that :) I used qsys since my navigator said QSYS.LIB. I used qsys2 and it worked great, thanks!!!
Matt
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question