Use the audit journal and SQL tools to determine what job is accessing an old program.

Every whodunnit requires a good detective to solve the case. The IBM i has a built-in detective called security auditing that can be used to help solve many mysteries. Say you're planning to remove a year-old deprecated library from the IBM i when you notice the LAST USED date on a program object (or two) in the library was relatively and unexpectedly recent. At this point, it's time to call in the detective to assist.

Of course, you want to verify nothing is using the programs before removing the library, so now you need to investigate what jobs are still using them. Modern systems initiate jobs from sources such as a 5250 app, the job scheduler, client/server applications, web applications, power users using Excel, etc. Therefore, it's increasingly difficult to determine where a stale library list might still be in use or whether there's a hard code somewhere that's causing an old copy of a program to run.

So how do you find out who or what is using the program? One way is to delete the old library and see what jobs blow sky high!

Another option is to use the auditing journal to create journal entries every time a job calls the program(s) in question. Generally, this journal is supposed to be used for security-related items, but in this case it works to aid with finding what is still using an old program. I see no problem with using the auditing journal for this purpose for short periods of time. One thing you don't want to do is use the auditing journal for auditing data transactions!

The audit journal is a special journal called QSYS/QAUDJRN. If this object is not on your system, then you should follow these steps to create it and to start audit journaling. For information about managing the audit journal, see this link.

As part of your setup, make sure that the system value QAUDCTL has been assigned *OBJAUD. If you're worried about performance, the amount of overhead this journaling will impose is only as great as what it's asked to record, which in this case is very little.

Once auditing is set up and enabled, you can use the following steps to trace all executions of program OLDLIB/MYPGM:

CHGOBJAUD OBJ(OLDLIB/MYPGM) OBJTYPE(*PGM) OBJAUD(*ALL)

The Change Object Auditing (CHGOBJAUD) command instructs the IBM i that a particular object (in the case of a program) should be audited when it is called.

If the program has simple parameters, you can test whether auditing is active by calling the program yourself from the command line:

CALL OLDLIB/MYPGM

Once something calls the program, use the Display Journal (DSPJRN) command to review the entries recorded in the audit journal. In this case, the ZR journal entry type (object read access) and journal code T (audit trail entry) can be specified to filter through the potentially many journal entries. Here is the command to show the job(s) that accessed the program:

DSPJRN JRN(QAUDJRN) JRNCDE((T)) ENTTYP(ZR)

The initial screen looks like the text below and shows a summary of the activity (your screen may show more activity depending on what objects you have audited):

                           Display Journal Entries

Journal . . . . . . :   QAUDJRN         Library . . . . . . :   QSYS

Largest sequence number on this screen . . . . . . : 00000000000000000005

Type options, press Enter.

5=Display entire entry

Opt   Sequence Code Type Object    Library     Job         Time    

             4   T     ZR                           QPADEV0001 12:15:01

             5   T     ZR                           QZDASOINIT 12:17:39

In this case, the QPADEV0001 job name indicates the program was called in a 5250 session. The QZDASOINIT job name in the second audit entry means the program was accessed through a database host server job (ODBC, OLE DB, JDBC, .NET managed provider, etc.). If you have many objects being audited at once, you may have to look through each entry to find out which object the entry is auditing.

Put a 5 next to the job audit entry you want to review (for this example, I chose QZDASOINIT) and then press F10 to display the journal entry details. The extended screen looks like this:

                       Display Journal Entry Details                

Journal . . . . . . :   QAUDJRN         Library . . . . . . :   QSYS

Sequence . . . . . . :   5

Code . . . . . . . . :   T - Audit trail entry

Type . . . . . . . . :   ZR - Object read access

Object . . . . . . . :

Type . . . . . . . :

Date . . . . . . . . :   07/31/13

Time . . . . . . . . :   12:17:39

Flag . . . . . . . . :   0

Count/RRN . . . . . :   0

Commit cycle ID . . :   0

Nested commit level :   0

Job . . . . . . . . :   503155/QUSER/QZDASOINIT

User profile . . . . :   MIKE

Ignore APY/RMV . . . :   No

Ref constraint . . . :   No

Trigger . . . . . . :   No

Program . . . . . . :   QZDASOINIT

Library . . . . . :     QSYS

ASP device . . . . :     *SYSBAS

System sequence . . :   11108041005439668225

Thread identifier . :   0000000000000001

Receiver . . . . . . :   AUDRCV0005

Library . . . . . :     DEV

ASP device . . . . :     *SYSBAS

Journal identifier . :   X'00000000000000000000'

Remote address . . . :   172.5.17.85

Address family . . . :   IPv4

Remote port . . . . :   50616

System name . . . . :   V144123

Arm number . . . . . :   1      

Logical unit of work :   *OMITTED

Transaction ID . . . :   *OMITTED

I've highlighted information I feel may be useful for figuring out what accessed the program: the date/time, the job name, the user profile, the calling program (usefulness depends on the interface the job is run from), the remote address and port (what computer initiated the request). This should help hunt down who/what is running the program. If it's a 5250 job name and user, then it's relatively easy to find.

If the job is something like QZDASOINIT and/or the user is generic, then the job's origin may not be so easy to identify by the audit journal alone. In this case, a database monitor can help by acting as the audit journal's sidekick. If you're unfamiliar with the database monitor, it is a tool that records SQL statements submitted to DB2 for i. You can set up a database monitor using the System i Access GUI or the green-screen Start Database Monitor (STRDBMON) command. Further, database analysis tools are available to help you sort out the information collected by the monitor.

If OLDLIB/MYPGM is called using SQL, the database monitor can track it and supply a wealth of information about the job that called the program (similar to what the audit journal does but with the additional benefit of the client registers being populated). Using the database analysis tools in System i Navigator, you can filter all SQL statements recorded in a trace by the program name or the name of an external procedure or function defined for the program. Sometimes information in the ApplicationName client register will return the name of a specific Windows .EXE file submitting the data requests. Otherwise, you just hope that a developer populated these registers with something useful.

With respect to a database job running the old program, one other thing that can be helpful is to look in the SYSROUTINES catalog view for external references to the program name. For example, what if SQL external procedure NEWLIB/MYPGM mistakenly referenced the old OLDLIB/MYPGM program? Example:

CREATE PROCEDURE NEWLIB.MYPGM

EXTERNAL NAME 'OLDLIB/MYPGM'

LANGUAGE RPGLE;

If this library mismatch happens when defining a procedure (or function), the following SQL CALLs will result in calling the OLDLIB version of the RPG program even though the NEWLIB SCHEMA seems to indicate otherwise:

CALL NEWLIB.MYPGM;

or

SET PATH=NEWLIB;

CALL MYPGM;

A query can check to see if there is a mismatch between the library specified in the routine's external name and the routine's schema as follows:

WITH BASE_DATA AS (

SELECT S.*,POSITION('/' IN EXTERNAL_NAME) AS LIB_DELIMITER

FROM QSYS2.SYSROUTINES S

)

SELECT *

FROM BASE_DATA

WHERE LIB_DELIMITER>1

   AND LEFT(EXTERNAL_NAME,LIB_DELIMITER-1)<>ROUTINE_SCHEMA

   AND SPECIFIC_SCHEMA NOT LIKE 'SYS%'

   AND SPECIFIC_SCHEMA NOT LIKE 'SQLJ'

   AND (SPECIFIC_SCHEMA NOT LIKE 'Q%' OR SPECIFIC_SCHEMA='QGPL')

The system catalog views can help hunt down almost anything!

Once you've located the culprit and you're done auditing the object, simply shut off the auditing for the object:

CHGOBJAUD OBJ(OLDLIB/MYPGM) OBJTYPE(*PGM) OBJAUD(*NONE)

The audit journal provides a great deal of information that can be used to trace what is accessing a deprecated program. When necessary, the database monitor and SQL catalogs can also help. Use these tools to hunt down those elusive jobs that have outdated library and program references. Incidentally, the audit journal can track usage of other object types such as data areas, data queues, and user spaces, to name a few.

Also Read

TD/OMS and Gravity “Milestone 1” Enhancements Streamline Efficiency and Advance Application Development Potential for Re...

Now Let’s Consider Vendor Maintenance

Know Where Your Maintenance Stands