• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

iSeries SQL to Query QSYS2 for Table Size?

DBohner-(db)

Legendary Poster
Hi All,

Many years ago I heard the tip, "Fourth and long - PUNT"... . I'm hoping for a better tip than that, today.

You can obtain a lot of table information pulling from the iSeries QSYS2 structures. What I can't figure out - how the heck, via SQL can I pull the Table Size (or last hit-on date) from within SQL.

Yes, I know the table size can be pulled via the dsplib - giving a very concise list of objects and sizes. I'm just hoping for a trick to using the QSYS2 and straight SQL to pull the data.

Any hints (or tips) - greatly appreciated.

(db)
 

David Robertson

Reputable Poster
What structures are you looking at in QSYS2?

What still appears to me to be the most accurate is to run a DSPOBJD for all first. I run a full list as an overnight job:
SBMJOB CMD(DSPOBJD OBJ(*ALL/*ALL) OBJTYPE(*ALL) OUTPUT(*OUTFILE) OUTFILE(ROBERD/DSPOBJD)) JOB(DSPOBJDALL) JOBQ(QS36EVOKE)
then using SQL to get the stats I require, basically:
SELECT * FROM ROBERD.DSPOBJD ORDER BY (ODSIZU*ODBPUN) DESC, ODOBNM;
That would be the same as your DSPLIB I guess.

I've tried using SYSIBM.TABLES and SYSIBM.SYSTABLESTAT, but DSPOBJD still seems to give the best figures (sizes of (ODSIZU*ODBPUN) seem about 10% more than SYSTABLESTAT.DATA_SIZE).
 
Top