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.
Hi Simon, the earlier article was good work around too. I do understand that the OBJECT_STATISTICS is the best way, but the work around can also hang along. Hope the old link is still available in some archived form.
ReplyDeleteThe link to the original post is the first link in this post.
ReplyDelete