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