Query Last Used Date on Multiple Servers

Does anyone know a quick and easy way to find the last used date for an object - on multiple iSeries systems? We have 25 servers and need to find the last used date for an object...

I didn't know if there was some kind of SQL query I could run to find the information or if there was another easy way...

Thanks in advance
LVL 1
Matthew RoessnerSenior Systems ProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
You could put DspFd in a CL program that writes to an outfile:

DSPFD FILE(MyLib/MyFile)
      TYPE(*BASATR)
      OUTPUT(*OUTFILE)
      OUTFILE(SomeLib/DSPFD_OUT)

Open in new window


Then, you could query the output with SQL to get the last used date:

SELECT MLLIB,
       MLFILE,
       MLUDAT
  FROM SomeLib.dspfd_out

Library     File        Last Used
                        Date     
MYLIB       MYFILE      150820  

Open in new window


Since they live on multiple systems, you'd have to connect to each system in your SQL, but that shouldn't  be too hard.

HTH,
DaveSlash
Gary PattersonVP Technology / Senior Consultant Commented:
What OS release are you running on these machines (oldest and newest if they are at multiple versions)?
Gary PattersonVP Technology / Senior Consultant Commented:
This is just one technique, but assuming you have a config that allows RUNRMTCMD to these systems and supports DDM connections, then in any OS version, you can create a CL that:

loops through a file containing a list of system names and credentials (if needed)
RUNRMTCMD to execute DSPOBJD OUTPUT(*OUTFILE) to a temp file on the remote system (not in QTEMP, though)
CRTDDMF to create a QTEMP DDMF pointing to the remote file containing DSPOBJD output
CPYF qtemp/ddmf to local file specifying MBROPT(*ADD) to append
RUNRMTCMD to DLTF the temp file you created on each remote system

Once you're done, you have a local file containing the concatenated output of the DSPOBJD command from each system.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
I was able to create output from the DSPOBJD command on all of my systems (all are at V7R1). Now that I have that output on all of the servers, how can I "query" it and get results back from all the remote systems?
Gary PattersonVP Technology / Senior Consultant Commented:
If your systems are at V7R1 TR4 or later, you can do all this from an SQL stored proc:

Loop through a list of systems
CONNECT to remote system
CALL QCMDEXC to run DSPOBJD command on remote system
DISCONNECT
INSERT mylib/DSPOBJDOUT SELECT * REMOTSYS/REMOTELIB/DSPOBJDOUT OB

http://www.itjungle.com/fhg/fhg072512-story02.html
Matthew RoessnerSenior Systems ProgrammerAuthor Commented:
When I run the following, the code works:

INSERT INTO QGPL/SQLQUERY
SELECT * FROM REMOTESYSTEM/QGPL/QUERYFILE

But if I attempt to only get back certain information from the file, it does not work:

INSERT INTO QGPL/SQLQUERY
SELECT ODLBNM,ODOBNM,ODUDAT FROM REMOTESYSTEM/QGPL/QUERYFILE

I get the following error:
Statement contains wrong number of values.

Thanks!
Dave FordSoftware Developer / Database AdministratorCommented:
Matthew, on an INSERT / SELECT statement, the quantity and data-types of your columns have to match between the "input" table and the "output" table. Therefore, if you want to only insert into SOME of the columns, you need to manually specify which ones.

e.g.
INSERT INTO QGPL/SQLQUERY
 (yourColumn1Name, yourColumn2Name, yourColumn3Name)
SELECT ODLBNM,

       ODOBNM,
       ODUDAT
  FROM REMOTESYSTEM/QGPL/QUERYFILE

Open in new window


HTH,
DaveSlash
tliottaCommented:
The big problem for finding a standard SQL feature or query would be that there will be tens of thousands (at least) of objects in potentially thousands of libraries to track in larger systems, and any number of objects would be added/deleted each day and last-used would be updated each day. Almost nobody will want DB2 to take time for that, especially since most sites won't want this info more than once or twice a year, maybe. So, it's not likely there'll ever be anything that you don't create yourself.

But creating it isn't necessarily a bad thing. A stored proc could be replicated to your network. You might keep it fairly well grounded in SQL with a User-Defined Table Function on each system that returns info about selected lists of objects. See The power of user-defined table functions for a good article on the concept.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.