Link to home
Create AccountLog in
Avatar of Matthew Roessner
Matthew Roessner

asked on

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...
ASKER CERTIFIED SOLUTION
Avatar of Gary Patterson, CISSP
Gary Patterson, CISSP
Flag of United States of America image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Matthew Roessner
Matthew Roessner

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
Avatar of Theo Kouwenhoven
Theo Kouwenhoven
Flag of Netherlands image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Theo Kouwenhoven
Theo Kouwenhoven
Flag of Netherlands image
BTW,
You can limit the output  (and runtime) by replacing the library parameter by *ALLUSR

QSYS2.OBJECT_STATISTICS('*ALLUSR','*ALL')
Avatar of Matthew Roessner
Matthew Roessner

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!