[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2008-10-06
10
Medium Priority
?
3,849 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 400 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
In a recent article here at Experts Exchange (http://www.experts-exchange.com/articles/18880/PaperPort-14-in-Windows-10-A-First-Look.html), I discussed my nine-month sandbox testing of the Windows 10 Technical Preview, specifically with respect to r…
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.

650 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