In the first of this series of TechTips, find information about each object in each library.

Recently, a friend called me for help. His boss asked him two questions:

• How much lost or forgotten old RPG or CL do we have?
• What is the status of the "general review of all the ILE programs" project?

He added, "I don't want a bullet-point listing. If you can give me a report in Excel in a normal table with normal filters, such as used by normal users every day, that would be better."

Uh oh! Something has changed. It's true that the spools are out of favor these days, even the spools copied into an Excel or PDF file.

And of course, the boss didn't want to invest in software for analyzing cross-references. "With all the commands in the OS, there is at least one that gives this info, isn't there?" Unfortunately, my friend's boss had forgotten what command was needed to do the job.

"Umm, yes, boss. No doubt, boss."

Dear friend, it is the Display Object Description (DSPOBJD) command.

The IBM i provides much information about each object in each library. And this command can be used on both the objects manufactured in-house and the objects of purchased software packages. Yep, you can easily find out whether your supplier's management of internal development is perfectly logical or as whimsical as what might have been created in-house.

Here's proof. In order to not offend anyone (yet still get my point across), I will demonstrate this on my own toolbox. I have added one old CLP and one old RPG to it for the purpose of this article; otherwise, I cannot show how to locate heritage programs.

Figure 1: Use the DSPOBJD command. (Click images to enlarge.)

DSPOBJD OBJ(JPLTOOLS/*ALL) OBJTYPE(*ALL) DETAIL(*SERVICE)

  OUTPUT(*OUTFILE) OUTFILE(JPLTOOLS/OBJS) OUTMBR(*FIRST *REPLACE)

If the analysis must include multiple libraries, use the option below to make as many DSPOBJDs as needed by making all DSPOBJDs append to the same file:

 OUTMBR (*FIRST *ADD)

Unfortunately, DSPOBJD typically provides too much information. For building extractions, STRSQL is a bit overwhelmed by the width of the file. I used iNav Sql instead (Run SQL Scripts from IBM i Navigator).

To make access more rapid, add a shortcut to iNav Sql on your desktop:

Figure 2: Add a shortcut to iNav Sql on your desktop.

Right-click Properties:

Target="C:\ProgramFiles\IBM\ClientAccess\Shared\cwbundbs.exe"

Folder="C:\ProgramFiles\IBM\ClientAccess\Shared"

First trial: Get the raw information.

select * from jpltools.objs

Figure 3: Get the raw data.

iNav Sql retrieves data very efficiently, but it's not much for the column headings. With STRSQL in parallel, the gap is filled.

Next step: Identify the columns needed—e.g., source file name and date. We have chosen the year of the source code's last update as the marker of reviewed source code. If we were reviewing program recompilation, we would instead have chosen the object creation date. There is so much information in DSPOBJD!

Here's the problem: DSPOBJD gives the date of the source code, but it gives it with three columns (all characters): the century, the date, and the time. We seek the four-digit year. SQL can take care of that with the following code.

SELECT ODLBNM "Library", ODOBNM "Object" , ODOBTP "Type" , odobat "Attribut",trim(ODSRCL) "Src Lib",  trim( ODSRCF)      "Src PF",  trim(ODSRCM)  "Src Member",case when odsrcc =  '' then ' ' else trim(char( ODSRCC+ 19)) concat substr(ODSRCD , 1 , 2) end as year   FROM jpltools.objs

Figure 4: The year is now shown in standard four-digit format. 

Much prettier.

Note: The ILE *PGM objects have no source. Normally, my ILE programs are created by CRTPGM. The modules will provide the information I'm searching for. If I wanted to analyze an external application, delivered without modules, DSPOBJD would not give me the information I want. In that case, I would have to code a program that uses  the program descriptions API to obtain the  list of the modules and their source code file names. I'll return to that point later.

So my friend's boss was right: the information is available. Now, it's just a matter of extracting this information and exporting it to Excel.

Note that we found a wealth of information, just with the DSPOBJD command. Having discovered that gives us the opportunity to show many different aspects of the application we are analyzing.  So, in the second part of this TechTip series, we'll pursue the application analysis with some other DSPxxx commands. You'll see all the information that can be extracted by using a few DSPxxx commands.

For now, we'll just get the requested information to Excel with the SQL above, and we'll reveal the answers to the questions the boss asked.

In Excel, we used the ODBC connection, because it's one of the easier connection modes to implement. Moreover, it's likely that the ODBC declaration to use already exists on your PC.

Creating an ODBC Connection

Click Start > Run: odbcad32.exe.

Figure 5: Call the ODBC administration application. 

Click the ADD button.

Figure 6: Create an ODBC connection.

Select the Client Access driver.

Figure 7: Select the Client Access ODBC Driver.

Enter a name for your connection and specify the system to connect to. Leave the other settings on their default values.

Figure 8: Name your connection and specify the system log.

The ODBC connection is available.

Import the Information into Excel

I used Excel 2007, but with Excel 2003, the method is similar. Open Excel. In the Data menu, choose Get External Data > From Other Sources > From Microsoft Query. Then follow the steps shown below.

Figure 9: Select a data source and deactivate the Query Wizard.

Close the Add Tables view.

Figure 10: Close Add Tables.

Figure 11: Choose the SQL editor.

Paste in the SQL query shown below. Note that the query syntax here is simple, and ODBC will accept it. This is not always the case. When a query is too complex to manipulate with this very small editor window, you must run the SQL statement batch on the server (e.g., with a STRQMQRY with output OUTFILE) and then read the result with ODBC.

SELECT ODLBNM "Library", ODOBNM "Object" , ODOBTP "Type" , odobat "Attribut",trim(ODSRCL) "Src Lib",  trim( ODSRCF)      "Src PF",  trim(ODSRCM)  "Src Member",case when odsrcc =  '' then ' ' else trim(char( ODSRCC+ 19)) concat substr(ODSRCD , 1 , 2) end as year   FROM jpltools.objs

Figure 12: Paste in the SQL query. 

Click OK.

MS Query is a little confused by what is asked. Don't worry about that. Click OK.

Figure 13: Yes, continue!

MS Query retrieves and displays information. Continue to import to Excel.

Figure 14: Choose Return Data to Microsoft Office Excel.

The information has been received. Choose how you want to view it.

Figure 15: Choose how to view your data.

And voila!

Figure 16: Your data is now in an Excel spreadsheet.

With this table in Excel, we will now easily answer the questions we were asked:

First question : How much lost or forgotten old RPG or CL do we have?

The answer is very simple to provide. Just click on the column labeled Attribute:

Figure 17: Object attributes are revealed.

The answer is clear: there are objects with CL and RPG attributes. Select just these objects' attributes to get the detailed list.

Figure 18: Select RPG and CLP.

Figure 19: Details are revealed.

Second question: What is the status of the "general review of all the ILE programs" project? 

For the answer, we must first filter the objects, keeping only the * MODULE, * PGM, and * SRVPGM objects in which the year is not blank. Remember: we are working on the year of the source update date. Some objects have no source.

Figure 20: First, we filter.

Then, we sort by year.

Figure 21: Now we sort.

We now have an Excel sheet containing the information the boss asked for.If we had worked on ODCCEN plus ODCDAT (i.e., the object creation date) instead of ODSRCC plus ODSRCD (the source update date), we would have gotten a review of a general recompilation project.

The SQL extraction targeted only the year. It could also seek the full date by replacing

concat substr (ODSRCD, 1, 2) 

with

concat ODSRCD

in the SQL statement:

SELECT ODLBNM "Library", ODOBNM "Object" , ODOBTP "Type" , odobat "Attribut",trim(ODSRCL) "Src Lib",  trim( ODSRCF)      "Src PF",  trim(ODSRCM)  "Src Member",case when odsrcc =  '' then ' ' else trim(char( ODSRCC+ 19)) concat substr(ODSRCD , 1 , 2) end as year   FROM jpltools.objs

This first part of this TechTip series ends with the import into Excel of the first statistics on the status of programs' compilation. You have seen that generating the requested information is done simply by using the appropriate system command. You have also seen that, with a few clicks, you can insert these statistics into Excel, which is much more comfortable to use than a spool. Imagine how long it would take to produce these statistics without SQL or ODBC!

You also saw that we came across a wealth of information. In the second part of this series, you'll see all the types of information we derive from the other DSPxxx commands.