List of all Libraries with SQL

2205 views
Skip to first unread message

Arne Gellhaus

unread,
Feb 21, 2002, 6:15:23 AM2/21/02
to
Hi,

i would like to get a list of all libraries with a SQL Statement. I
can get all tables with

SELECT * FROM qsys2/systables

and all columns are retrievable from qsys2/syscolumns. Is there
anything like this for libraries?


Regards,
Arne Gellhaus

BPCI

unread,
Feb 21, 2002, 6:38:12 AM2/21/02
to
You can use
dspobjd obj(*all/*all) objtype(*lib) output(outfile) outfile(lib/file)
SELECT odObnm FROM lib/file


"Arne Gellhaus" <gell...@gmx.de> wrote in message
news:8bb46d08.02022...@posting.google.com...

SJ Lennon

unread,
Feb 21, 2002, 8:05:38 PM2/21/02
to
Not sure about this one. Is there a SYSCOLLECTIONS?

Sam

"Arne Gellhaus" <gell...@gmx.de> wrote in message
news:8bb46d08.02022...@posting.google.com...

Arne Gellhaus

unread,
Feb 22, 2002, 4:22:44 AM2/22/02
to
"SJ Lennon" <no...@none.com> wrote in message news:<gTgd8.108$Yn4....@eagle.america.net>...

> Not sure about this one. Is there a SYSCOLLECTIONS?

No. At least not on the AS/400 i am using ;-). This had been too easy.

Arne

Federico Inganni

unread,
Feb 22, 2002, 7:51:28 AM2/22/02
to
"Arne Gellhaus" <gell...@gmx.de> wrote in message
news:8bb46d08.02022...@posting.google.com...

> Hi,


> i would like to get a list of all libraries with a SQL Statement. I
> can get all tables with
> SELECT * FROM qsys2/systables

If what You need is just a list of libraries (containing tables)
you can perform
SELECT distinct table_schema FROM qsys2/systables

You will however be missing libraries containing programs and
any other objs


Bye
Federico

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Arne Gellhaus

unread,
Feb 23, 2002, 6:17:37 AM2/23/02
to
"Federico Inganni" <graym...@tin.it> wrote in message news:<7a63f4bfff02a49d934...@mygate.mailgate.org>...

> If what You need is just a list of libraries (containing tables)
> you can perform
> SELECT distinct table_schema FROM qsys2/systables
>
> You will however be missing libraries containing programs and
> any other objs

This way i did it before, but i want to be able to see empty
libraries, and this is - as you stated before - not possible this way.

After reading many articles on the net, i think it is just not
possible "the right way".

I am using now (as BPCI writes before) a

call qsys/qcmdexc('dspobjd obj(*all/*all) objtype(*lib)
output(*outfile) outfile(qtemp/liblist)', 0000000076.00000)

to create a list of libraries and then

SELECT odObnm FROM qtemp/liblist

to retrieve it.


Thank you all :-)

Arne

Peter Sawatzki

unread,
Feb 23, 2002, 9:00:47 AM2/23/02
to
Arne,

you could use

select distinct dbxlib from QSYS.QADBXATR

for a list of all libraries. QSYS.QADBXATR contains a list of all tables
in all libraries. For example I use the following to retrieve a list of
DB tables that are PF files:

select dbxlib, dbxlfi from QSYS.QADBXATR where dbxrel='Y' and
dbxatr='PF'

returns a library/long filename list.

Peter

In article <8bb46d08.02022...@posting.google.com>,
gell...@gmx.de says...

higson...@gmail.com

unread,
Jul 30, 2020, 9:38:09 AM7/30/20
to
Very simple,

You can use

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALL','*LIB') ) AS X

or simple

SELECT OBJNAME, OBJTYPE, OBJOWNER, OBJTEXT FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALL','*LIB') ) AS X

Mike Colangelo

unread,
Aug 6, 2020, 9:08:34 PM8/6/20
to
Did you notice that the post to which you replied is almost 18.5 years old?

Anyway, try this:

select * from qsys2.sysschemas

I don't think any of the IBM i Services views and table functions were
around in 2002, but they are now.

David Keck

unread,
Aug 10, 2021, 4:56:22 PMAug 10
to
Responses are useful not not only to the original poster at the time of the post but also anyone else, even across time. If he hadn't posted then neither would have you, and I may not have found what I needed. qsys2.sysschemas, very nice. Thank you very much!
Reply all
Reply to author
Forward
0 new messages