asked on 1/2/2018

List all Libraries and number of objects for each library

Does anyone have a SQL statement that will list all libraries on my IBM i server and the number of objects in each of the libraries?

So my output would have just 2 columns. Table Name and Number of Objects.

I appreciate any help...

IBM System i SQL

Last Comment

Matthew Roessner

1/3/2018

ASKER CERTIFIED SOLUTION

Gary Patterson, CISSP

1/2/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Matthew Roessner

1/3/2018

ASKER

Yeah, I have been playing with the OBJECT_STATISTICS UDTF but I haven't been able to come up with what I wanted.

Your statement returns an error - like all the ones I have tried! I am able to return the number of items in a library if I use the following:

SELECT COUNT(OBJNAME) NUMBER_OF_OBJECTS FROM TABLE(QSYS2.OBJECT_STATISTICS('<%LIBRARYNAME%>','*ALL')) A

But I want to ultimately have a statement I can run that will give me the library name and how many objects are in it.

Seems like this is an easy thing that shouldn't be this hard...

SOLUTION

Theo Kouwenhoven

1/3/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Theo Kouwenhoven

1/3/2018

BTW,
You can limit the output (and runtime) by replacing the library parameter by *ALLUSR

QSYS2.OBJECT_STATISTICS('*ALLUSR','*ALL')

Matthew Roessner

1/3/2018

ASKER

The exact statement I actually ran was:

SELECT OBJLO00002 "LIBRARY", count(1) "NUMBER OF OBJECTS"
FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALLUSR','*ALL')) AS X
GROUP BY OBJLO00002
ORDER BY OBJLO00002

Thanks for all your help guys!