I need an AS400 DB2 Query for physical file Last Used Date

I use the AS400 command "DSPOBJD" to list one or more physical files.  Once the files are on the screen I put the number 5 beside a file for "Display full attributes".  I hit enter and a screen is displayed showing the full object description.  I page down to the next screen and one of the lines of information is "Last used date".

My question:  Is it possible to create an SQL query against the SYSIBM, QSYS2, and/or other system tables to list physical files and their "Last used date" information?  I have created queries for all kinds of other information like column names, etc.  I cannot find any table or query for "Last used date" information.  I have found "last_altered_timestamp" and so forth, but I just want the "Last used date" data displayed on the DSPOBJD" results screen.  Any help is greatly appreciated.
Who is Participating?
_b_hConnect With a Mentor Commented:
Here is some code that will get you started. It displays the object descriptions to a file, and then processes them by last used date ODUDAT.
You call the program:  CALL  THEPROGRAM (MYLIB 'BLAH*')
so that only the files you want have their descriptions displayed.
Insert some logic that checks the date in file name as well; sounds like another parm to pass.

PGM (&LIB &FILE)                                                          
DCL &LIB   *CHAR   10  /* LIBRARY NAME */                                  
DCLF QADSPOBJ                                                              
             DSPOBJD    OBJ(&LIB/&FILE) OBJTYPE(*FILE) +                  
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/QADSPOBJ)        
             OVRDBF     FILE(QADSPOBJ) TOFILE(QTEMP/QADSPOBJ)              
             MONMSG (CPF0864) EXEC(DO)                                    
                GOTO ENDOFFILE                                            
             /* INSERT LOGIC BASED ON FILE NAME HERE             */        
             /* CHECK FILE LAST USED DATE                        */        
             SNDPGMMSG  MSG('File ' *CAT &ODOBNM *CAT ' in library ' +    
                          *CAT &LIB *CAT ' last used ' *CAT &ODUDAT)      
             GOTO NEXTFILE                                                

Sample program output:
call bh/rtvobjd (bh q*)                                
File QAJBACG4   in library BH         last used 082307
File QCLLESRC   in library BH         last used 091908
File QCLSRC     in library BH         last used 100608
File QDDSSRC    in library BH         last used 082808
File QFTP       in library BH         last used 012108
File QHTMLSRC   in library BH         last used 010908
File QRPGLESRC  in library BH         last used 091908
File QRPGSRC    in library BH         last used 051708
File QTXTSRC    in library BH         last used 112007
File QUERIES    in library BH         last used 060608
File QUSLJOBP   in library BH         last used 040308

Don't sweat the point split; we're here to help, not rack up points.

Yes, this is a great forum! The experts are all volunteers, and happy to help.
daveslaterConnect With a Mentor Commented:
Have you tried usted the dspobjd to an output file then linking the system tables to output. becuase the attribute is for all objects and not just files I don't think it isheld at in any of the system tables relating to files.
If dspobjd is too slow then you can use the relevent API.

richard_cristAuthor Commented:

Thank you for the quick reply.  I have used that method before for querying other information (when all else fails).  I was hoping that there was some hidden way that I had not stumbled upon.  I think that I could create a CL that accepts a combination of library and object names and then ties that together with the system tables, as you mentioned above.  If I could do that and make it ad hoc (with throttles so I don't eat up the production system), then that would be ever so useful for my purposes.  Could you please tell me a little more about the API?  Actually, I imagine that, based on your comment, there may be API's for most or all commands?  Again, the starting point help is much appreciated.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

If disk space information is collected regularly, say weekly, the QAEZDISK file in QUSRSYS would contain what you need.
How are you using the information, and how current does it need to be?
richard_cristAuthor Commented:

Thanks for the information.  I will check out the QAEZDISK file and see what it has compared to the dspobjd command.  I am not sure about how often statistics are collected, but it will probably suit my needs for now if it is only collected weekly.

The situation I am addressing at this time is that there are files with names like blahmmddyy where mmddyy is the date of the backup (not my design!).  We need to purge most of these files up until this year.  I would like to be able to easily see the Last Date Used to see if anyone has accessed a particular file for research purposes, or if it has sat there since creation without anyone accessing it.

If I can use the information from daveslater and you to make a semi-ad hoc query that I can use, then that would be useful to me and probably others.

As a side question (I know that is discouraged), if I find information from two answers useful in my final solution, is it possible to give credit to both experts?  I am sure that most of the time the answer is clear cut, but this question may be one where my solution might be a mixture of both methods you two have discussed so far.

This is the best forum ever!
richard_cristAuthor Commented:

Thank you for the code example.  I have developed on an AS400 before, but it has been a few years.

I will implement your code and reply with results.  I imagine that this will most likely be what I am looking for.  I think I might add a multiple library ability, either with multiple library parameters, or with one parameter that can accept a string of library names separated by commas.  This is because sometimes we have the same blah* format file names in different libraries.  I will try to test and reply today.

Thank you!   :)
richard_cristAuthor Commented:
_b_h gave me the example I needed to understand the concept.  I am giving daveslater some points because I now realize that he was talking about the same method.  I plan on taking the "override a system output file to my own copy" method and making good use of it for other command output (as applicable).  Thank you both for the quick response.  This answer was exactly what I was looking for.  Now I know what is going on when I issue an official command and the green screen shows "running query" messages at the bottom of the screen.   :)
richard_cristAuthor Commented:
This answer was exactly what I was looking for.  _b_h's example helped me grasp the general concept.  I realize now that daveslater was talking about the same thing.  Thank you both for your time and help!  I now plan on taking this general concept and using it (as applicable) to gather queryable information from other AS400 commands.   :)
Minor note... Barry's program has [ DCLF QADSPOBJ ]. If you prompt DSPOBJD and review the help for the OUTFILE() parameter, you can find where the name of the file that IBM uses as the model is given. For commands that support outfiles, you will generally compile your program over the declared name given from IBM; and then issue the OVRDBF to whatever name you create in your program. In the example, Barry used the same name, but generated it into library QTEMP.

If you're going to use the same name, make sure you put your outfile in a different library. You do not want to put data into the model file that IBM provides.

The general technique of [IBM model file plus your temporary file] is used for many similar commands. Each command has a different model; and some commands have multiple models depending on the format that you choose.

richard_cristAuthor Commented:

Thank you for your comment.  I was just talking yesterday with my supervisor and asking her the exact question for which you provided information above.  I prompted the DSPOBJD command and pressed F1 on the Outfile parameter and found the information just as you described.  I tried your technique on the DSPUSRPRF and indeed found different models based on the value of Type of Information field.  Thank you, also, for your help with this question.  This additional information will be useful to me.  Again, this is the best forum ever.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.