Solved

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

Posted on 2008-10-06
10
3,431 Views
1 Endorsement
Last Modified: 2013-12-06
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.
1
Comment
Question by:richard_crist
10 Comments
 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 100 total points
ID: 22653621
Hi
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.

Dave
0
 
LVL 3

Author Comment

by:richard_crist
ID: 22654270
daveslater,

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.
0
 
LVL 13

Expert Comment

by:_b_h
ID: 22655144
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?
Barry
0
 
LVL 3

Author Comment

by:richard_crist
ID: 22655712
_b_h,

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!
0
 
LVL 13

Accepted Solution

by:
_b_h earned 400 total points
ID: 22656196
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 */                                  
DCL &FILE  *CHAR   10  /* FILE NAME, CAN BE GENERIC LIKE BLAH* */          
DCLF QADSPOBJ                                                              
             DSPOBJD    OBJ(&LIB/&FILE) OBJTYPE(*FILE) +                  
                          OUTPUT(*OUTFILE) OUTFILE(QTEMP/QADSPOBJ)        
             OVRDBF     FILE(QADSPOBJ) TOFILE(QTEMP/QADSPOBJ)              
NEXTFILE:                                                                  
             RCVF                                                          
             MONMSG (CPF0864) EXEC(DO)                                    
                GOTO ENDOFFILE                                            
                ENDDO                                                      
             /* 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                                                
ENDOFFILE:                                                                
             ENDPGM                                                        

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.
Barry
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:richard_crist
ID: 22660139
_b_h,

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!   :)
0
 
LVL 3

Author Closing Comment

by:richard_crist
ID: 31503562
_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.   :)
0
 
LVL 3

Author Comment

by:richard_crist
ID: 22671927
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.   :)
0
 
LVL 27

Expert Comment

by:tliotta
ID: 22672227
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.

Tom
0
 
LVL 3

Author Comment

by:richard_crist
ID: 22680421
tliotta,

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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally Windows/Microsoft Updates will fail to update. We have found a code that will delete all temporary files and re-register all dll's related to Windows/Microsoft Updates! This works 99% of the time to get the updates working again! The…
Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now