List Libraries / Tables / Fields

[Esoteric]

I interface a website with a DB2 database and an SQL database and this is the first time I need to actually get a list of libraries and tables from the AS/400.

Please illustrate how I can get this list.
I have an ODBC connection to two libraries:

DATALIB
CUSTLIB

would like to know how to get a list of Tables from the selected library. Maybe Even a list of libraries first.

Select the Table (File) and get the (fields) structure of those items. I know how to do this with SQL but am a little lost with DB2. Usually I can get by. Please assist if you can.

I have tried some of these: Maybe I don't have enough clearance to run them, dunno.

Select tabschema, tabname from syscat.tables
SELECT NAME FROM SYSIBM.SYSTABLES
SELECT distinct table_schema FROM CUSTLIB/systables
SELECT * FROM DICTIONARY.TABLES where libname = 'CUSTLIB'
SELECT SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME FROM SYSTABLES ORDERBY SYSTEM_TABLE_SCHEMA

I don't get squat.

 

[Mercury2]

Try this :

SELECT substr(a.table_name, 1, 10) "Library",                      
substr(a.TABLE_SCHEMA, 1, 10) "Table",                             
a.TABLE_TYPE "Alias,Logic,Physic,Table,View",                      
substr(b.COLUMN_NAME, 1, 10) "Field", 
b.ORDINAL_POSITION,          
b.DATA_TYPE, 
b.LENGTH, 
b.NUMERIC_SCALE, 
substr(b.COLUMN_TEXT, 1, 50) as "Text" 
FROM [b]QSYS2/SYSTABLES a join QSYS2/SYSCOLUMNS b[/b] 
on a.table_schema = b.table_schema 
and a.table_name = b.table_name
and a.table_schema = [COLOR=red][b]'MYLIB'[/b][/color] 
oeder by a.SYSTEM_TABLE_SCHEMA, a.TABLE_NAME,          
b.ORDINAL_POSITION

If that stm fits your needs, change 'MYLIB' library name to your own library name.

 

[flapeyre]

Try selecting from QSYS/QADBXREF.


Me transmitte sursum, Caledoni!