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...
LVL 1
Matthew RoessnerSenior Systems ProgrammerAsked:
Who is Participating?
 
Gary PattersonVP Technology / Senior Consultant Commented:
Depends on your OS release and TR level.

See the object_statistics UDTF:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM+i+Technology+Updates/page/QSYS2.Object_Statistics+table+function+ease+of+use

I didn't try this, but something like this should do it if you have the latest OBJECT_STATISTICS UDTF.

SELECT objlib, count(*)
FROM TABLE (QSYS2.OBJECT_S​TATISTICS('*ALL','*ALL','*ALLSIMPLE')) AS X
GROUP BY objlib
0
 
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
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...
0
 
MurpheyApplication ConsultantCommented:
I did try Gary's SQL and it will work, but take some time.... (.... just a little more then "some")

When it runs into error, maybe your on an other release, try this one:

SELECT OBJLO00002, count(1)                            
FROM TABLE (QSYS2.OBJECT_STATISTICS('*ALL','*ALL')) AS X
GROUP BY OBJLO00002
0
 
MurpheyApplication ConsultantCommented:
BTW,
You can limit the output  (and runtime) by replacing the library parameter by *ALLUSR

QSYS2.OBJECT_STATISTICS('*ALLUSR','*ALL')
0
 
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.