1. The objects could be removed or flagged as obsolete.
2. Prioritise the development/testing based on high volume activity.
Usually, I would use the Work with Object (WRKOBJ) command and work through them one by one capturing the data in excel or notepad manually. Another option is to build a list of objects on the system using the OUTFILE keyword from the DSPOBJD command and then query these via SQL or Query/400 or simply by using YWRKF our trusted friend in the 2E world.
Today however, I had a list of around 50 or so objects and as we have separate development and production machines (the latter, I have no access to via segregation of duties policies that I support BTW), I felt it was unfair to ask a colleague to send me 50 screen prints, as well as, this being prone to user error with so many commands to execute.
I could have documented the steps for the traditional method above but this isn't really repeatable for my colleagues nor is it enjoyable. Therefore, I decide to write an SQL (or four) as it turns out to get me the data and leave us with a template for the future. There is a bonus 5th one for us 2Er's
There is a bump in the road though. Isn't there always aye!
Even though IBM have made huge strides with the availability of data via SQL in recent releases. A simple view for the OBJECT_STATISTICS does not exist. There is a table function which will get you the data but is precluded on obtaining data for an entire library or a subset based on object type.
Here is an example straight from the IBM documentation.
When I applied this to my test library, I didn't want the timestamp but a date only and I wanted a subset of the fields available.
This is quite a simple modification to the IBM example, we just do a substring at take the first 10 characters and give the field as nice name. Note also the replacement of the asterisk (*) for the select to exact fields.
Unfortunately, I hit a hurdle when trying to view the resulting data via YWRKF. The 2E programs raised an error condition and threw a hissy fit as I had some null dates represented as '-' in the returned data and not the ISO format (0001-01-01) it was expecting.
A quick google later and an a minor adjustment to the SQL to present null as an empty ISO date and I was now able to view the data. FYI, Query/400 was fine with the data, so this is an extra steps for loyal 2E users/shops like me/us. There are also other date conversion routines readily available I just chose this method for now....
This is all well and good, but I also wanted to restrict this SQL result to the objects that were of interest. I achieved this by using a SQL and filter out those of interest.
Today however, I had a list of around 50 or so objects and as we have separate development and production machines (the latter, I have no access to via segregation of duties policies that I support BTW), I felt it was unfair to ask a colleague to send me 50 screen prints, as well as, this being prone to user error with so many commands to execute.
I could have documented the steps for the traditional method above but this isn't really repeatable for my colleagues nor is it enjoyable. Therefore, I decide to write an SQL (or four) as it turns out to get me the data and leave us with a template for the future. There is a bonus 5th one for us 2Er's
There is a bump in the road though. Isn't there always aye!
Even though IBM have made huge strides with the availability of data via SQL in recent releases. A simple view for the OBJECT_STATISTICS does not exist. There is a table function which will get you the data but is precluded on obtaining data for an entire library or a subset based on object type.
Here is an example straight from the IBM documentation.
When I applied this to my test library, I didn't want the timestamp but a date only and I wanted a subset of the fields available.
This is quite a simple modification to the IBM example, we just do a substring at take the first 10 characters and give the field as nice name. Note also the replacement of the asterisk (*) for the select to exact fields.
SELECT OBJNAME, DAYS_00001,
SUBSTR(VARCHAR(LAST_00001), 1, 10) AS LAST_USED
FROM table
(object_statistics('MYLIB', '*ALL'))
Unfortunately, I hit a hurdle when trying to view the resulting data via YWRKF. The 2E programs raised an error condition and threw a hissy fit as I had some null dates represented as '-' in the returned data and not the ISO format (0001-01-01) it was expecting.
A quick google later and an a minor adjustment to the SQL to present null as an empty ISO date and I was now able to view the data. FYI, Query/400 was fine with the data, so this is an extra steps for loyal 2E users/shops like me/us. There are also other date conversion routines readily available I just chose this method for now....
SELECT OBJNAME, DAYS_00001,
IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED
FROM table
(object_statistics('MYLIB', '*ALL'))
This is all well and good, but I also wanted to restrict this SQL result to the objects that were of interest. I achieved this by using a SQL and filter out those of interest.
SELECT OBJNAME, DAYS_00001,
IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED
FROM table
(object_statistics('MYLIB', '*ALL')) a WHERE a.OBJNAME in
('MYOBJ01', 'MYOBJ02', 'MYOBJ03', 'MYOBJ04', 'MYOBJ05',
'MYOBJ06', 'MYOBJ07', 'MYOBJ08', 'MYOBJ09', 'MYOBJ10')
Whilst this is great it is only a view on the screen. I didn't want my colleague to have to take screen prints or scrape the screen with tedious cut and paste to build an excel file for me.
CREATE TABLE QTEMP/OBJ_USAGE AS(
SELECT OBJNAME, DAYS_00001,
IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED
FROM table
(object_statistics('MYLIB', '*ALL')) a WHERE a.OBJNAME in
('MYOBJ01', 'MYOBJ02', 'MYOBJ03', 'MYOBJ04', 'MYOBJ05', 'MYOBJ06', 'MYOBJ07', 'MYOBJ08', 'MYOBJ09', 'MYOBJ10')
) WITH DATA
This was great for my purposes, I sent the SQL to a colleague, and they were able to implement it and send me a file back. Job done.
Hold Tight!!
This is a 2E blog (mainly) and I have only mentioned YWRKF. You can do better than that I hear you cry.
The above list of objects can easily be expanded or substituted with a list from a file which you could build somehow. What if I linked this to a Model List? That would be cool right!
Whilst this isn't an option for my site due to having separate machines, it might work for you. If you are like us, you may have send over some data periodically from production and work on the queries from a different angle. Anyhow, assuming you have model lists and objects you wish to query on the same machine you can embed your SQL in RPG, CL or as a SQL source member and run with RUNSQLSTM etc.
You just need to link the Model list with your library of objects. See below for one method.
We create an alias of the member we wish to use as our list.
CREATE OR REPLACE ALIAS QTEMP/OBJ_USAGE FOR MYMODEL/YMDLLSTRFP(MYLIST)
Execute the query. Optionally as above, you can output to a file if you wish.
SELECT OBJNAME, DAYS_00001, IFNULL(SUBSTR(VARCHAR(LAST_00001), 1, 10),'0001-01-01') AS LAST_USED
FROM TABLE (OBJECT_STATISTICS('MYLIB', '*ALL')) a LEFT JOIN QTEMP/OBJ_USAGE a ON a.OBJNAME = b.IMPNME WHERE b.OBJTYP = 'FUN'
Lastly, tidy up after ourselves and drop the temporary ALIAS.
DROP ALIAS QTEMP/OBJ_USAGE
As always, I am sure that there are other ways of solving this problem and I would love to hear about them in the comments. This is my current 'new method' and will likely change as I notice more and more flaws or need to expand the scope.
Thanks for reading.
Lee.
