SQL function similar to OBJECT_STATISTICS() to get file information like DSPFD

[garetjax76]

Hi all, I'm new to this forum, I apologize in advance for my poor english.
In order to manage our IBM i system better, I already use some of the new SQL function introduced in the latest technlogy updates, as in https://www.ibm.com/support/pages/node/1119123
In particular, I often use the OBJECT_STATISTICS(), which is very useful, so I built some utilities that make use of it.
Now I'd like to make an utility to check the consistency of files over multiple libraries, in particular I want to make sure all files have the same parameters (like LVLCHK and record level id): now I'm using a combination of DSPFD with OUTPUT(*OUTFILE) and then process the resulting table, but it'd be very nice to have a table function like OBJECT_STATISTICS() to check it at a glance.
So far I haven't found it, does it exists?

Thanks in advance.

[gioulos1983]

Hi,

although it does not provide exactly what you need, as a workaround, you could use the following sql:

 SELECT SYS_DNAME, SYS_TNAME, COLCOUNT, RECLENGTH FROM SYSTABLES WHERE SYS_DNAME in ('LIB2', 'LIB1') 

and then compare the columns counter (COLDCOUNT) and record length (RECLENGTH) to see if a file is modified

This will cover most of your files (especially if you do not mix DDL with DDS)

[JonBoy]

SYSTABLES in QSYS2 will give you the Coumn_Count and Row_Length - I'm sure the level Id must be somewhere around but couldn't find it.

Have you checked all the new tooling IBM have put out ? May be something there. Don't know what release you are on but this is the 7.3 link. they keep adding to these all the time. https://www.ibm.com/support/knowledg...ervicessys.htm

[garetjax76]

SYSTABLES in QSYS2 will give you the Coumn_Count and Row_Length - I'm sure the level Id must be somewhere around but couldn't find it.

Have you checked all the new tooling IBM have put out ? May be something there. Don't know what release you are on but this is the 7.3 link. they keep adding to these all the time. https://www.ibm.com/support/knowledg...ervicessys.htm

Thanks both for the replies.
I already doublechecked the qsys2 functions, along with the technology refreshes' table functions (I'm at 7.3) but there seems to be no function to check record level id.
One could guess that format record level id is, obviously, a format record-related (and not object-related) information, but so are fields count and record length. IBM might have included that information in SYSTABLES, but for some reason didn't. Too bad.

I think I'll write a table function using a temporary file populated with DSPFD *RCDFMT and process it, when it's done I'll post it here.