Solved

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

Posted on 2008-10-06
10
3,614 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

738 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