In my previous post about making a list of files in a library that are journaled I stated that I could not find this information in any Db2 for i view or table function.

Krister Karlsson brought to my attention that the information is available in the OBJECT_STATISTICS table function. Looking at the documentation from IBM it would appear to have been added in the previous round of Technology Refreshes.

Rather than using the Display Object Description command, DSPOBJD , I can get the information I desire from a simple Select statement.

SELECT OBJLIB,OBJNAME,JOURNALED,JOURNAL_LIBRARY, JOURNAL_NAME,JOURNAL_IMAGES,OMIT_JOURNAL_ENTRY FROM TABLE (QSYS2.OBJECT_STATISTICS('MYLIB','*FILE')) AS A WHERE JOURNALED = 'YES'

This gives me:

OBJLIB OBJNAME OBJTYPE JOURNALED JOURN00002 JOURN00001 MYLIB AVAILSPACE *FILE YES QRECOVERY QDBJRNCOLM JOURN00003 OMIT_00001 *AFTER *NONE

This means I do not have to have a CL program, as I did before, I can just run the following statement in my favorite SQL interface to generate a table/file.

CREATE TABLE MYLIB.JRNFILE AS (SELECT OBJLIB,OBJNAME,OBJTYPE, JOURNAL_LIBRARY AS JRNLIB, JOURNAL_NAME AS JRNNAME, JOURNAL_IMAGES AS JRNIMG, OMIT_JOURNAL_ENTRY AS OMITJRNENT FROM TABLE(QSYS2.OBJECT_STATISTICS('MYLIB','*FILE')) AS A WHERE JOURNALED = 'YES') WITH DATA

And the created table/file looks like:

OBJLIB OBJNAME OBJTYPE JRNLIB JRNNAME JRNIMG OMITJRNENT MYLIB AVAILSPACE *FILE QRECOVERY QDBJRNCOLM *AFTER *NONE

Which is a lot faster that using the DSPOBJD command, and then creating my table of journaled files from it.

You can learn more about the Db2 for i OBJECT_STATISTICS table function from the IBM website here.

This article was written for IBM i 7.3 TR3 and 7.2 TR7.