2e and Plex Emporium by Lee Dare (A developer blog): IBMi

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.

https://www.ibm.com/docs/en/i/7.4.0?topic=services-object-statistics-table-function

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')

SQL to extract object usage data on IBM I and incorporating some synon model queries for extra info.

UPDATE: 06/12/2025 - Added a summary link post to aggregate these types of posts.

https://leedare-plex2e.blogspot.com/2025/12/synon-2e-sql-and-model-files-summary.html

Continuing the series on using IBM i SQL for some basic work management tasks. Today I had a large list of objects that I wanted to understand the date that they were 'last used' and 'days used' count in order to determine if :-

1. The objects could be removed or flagged as obsolete.

2. Prioritise the development/testing based on high volume activity.

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 the

Synon 2E and Plex Emporium by Lee Dare (A developer blog).

Lee.

IBMi A few more little SQL's

UPDATE: 06/12/2025 - Added a summary link post to aggregate these types of posts.

https://leedare-plex2e.blogspot.com/2025/12/synon-2e-sql-and-model-files-summary.html

QSYS2 is a library that has lots of system data readily available. More and more data that has traditionally been the reserve of a particular command or API is now available for query. These views have been around for years and IBM are adding more and every release.

The SQL view will have a long name equivalent for easier SQL readability, as well as, the standard IBM object notation for use in tools like Query/400 rather than SQL statements. (See Table as the end of this blog)

For example the file for querying data area's on the system is 'DATA_AREA_INFO' in SQL parlance but 'DTAARA_INF' when referred to directly as an object.

Let's take a look at some sample for the kind of data you can mine on your IBMi

Who is taking up spool file space?

SELECT USER_NAME, SUM(SIZE) AS TOTAL_SPOOL_SPACE

FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC

GROUP BY USER_NAME

ORDER BY TOTAL_SPOOL_SPACE DESC LIMIT 25

Do we have very large spool files that can be purged?

SELECT * FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC ORDER BY SIZE DESC

FETCH FIRST 100 ROWS ONLY

Do we have some really old ones?

SELECT * FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC

ORDER BY CREATE_TIMESTAMP

FETCH FIRST 100 ROWS ONLY

What Data area's (*DTAARA) are there in a given library and their values?

SELECT DATA_AREA_NAME, DATA_AREA_VALUE

FROM QSYS2.DATA_AREA_INFO

WHERE DATA_AREA_LIBRARY = 'YOUR_LIB'

What about the current library list?

SELECT DATA_AREA_LIBRARY, DATA_AREA_NAME, DATA_AREA_VALUE

FROM QSYS2.DATA_AREA_INFO

WHERE DATA_AREA_LIBRARY

IN (SELECT SCHEMA_NAME FROM QSYS2.LIBRARY_LIST_INFO)

I'm only interested in the user portion!

SELECT DATA_AREA_LIBRARY, DATA_AREA_NAME, DATA_AREA_VALUE

FROM QSYS2.DATA_AREA_INFO

WHERE DATA_AREA_LIBRARY

IN (SELECT SCHEMA_NAME FROM QSYS2.LIBRARY_LIST_INFO

WHERE TYPE = 'USER')

Want to know the *JOBD's that use a particular library?

SELECT JOB_DESCRIPTION_LIBRARY, JOB_DESCRIPTION, LIBRARY_LIST

FROM QSYS2.JOB_DESCRIPTION_INFO

WHERE LIBRARY_LIST LIKE '%YOUR_LIB%'

Profiles with *SECADM that are *ENABLED

SELECT * FROM QSYS2.USER_INFO

WHERE SPECIAL_AUTHORITIES LIKE '%*ALLOBJ%'

AND STATUS = '*ENABLED' ORDER BY PREVIOUS_SIGNON

Stagnant user profiles? Assist with your security compliance.

SELECT * FROM QSYS2.USER_INFO

WHERE STATUS = '*ENABLED' ORDER BY PREVIOUS_SIGNON

To explore for yourselves take a look at this page, the above is really the tip of the iceberg when it comes to managing your system.

https://www.ibm.com/docs/en/i/7.4?topic=services-user-info-view

Or look for the files in the QSYS2 library and google the documentation. You may well find this useful link.

https://www.ibm.com/support/pages/db2-i-services-system-names-new-files-qsys2

Worst case you have the tried and tested WRKOBJ discovery method.

WRKOBJ QSYS2/*ALL *FILE

Thanks for reading the

Synon 2E and Plex Emporium by Lee Dare (A developer blog).

Lee.

This is a summary of the files available for SQL. We've only just scratched the surface with these. We are only limited by our imagination with what tasks we can automate, improve etc.

SQL Object

ASP_INFO ASP_INFO

ASP_JOB_INFO ASPJ_INFO

ASP_VARY_INFO VARY_INFO

AUTHORITY_COLLECTION AUTH_COL

AUTHORITY_COLLECTION_DLO AUTH_COLDL

AUTHORITY_COLLECTION_FSOBJ AUTH_COLFS

AUTHORITY_COLLECTION_LIBRARIES AUTH_COLLI

AUTHORITY_COLLECTION_OBJECT AUTH_COLOB

AUTHORIZATION_LIST_INFO AUTHL_INFO

AUTHORIZATION_LIST_USER_INFO AUTL_USERS

AUTOSTART_JOB_INFO AUTOJ_INFO

BINDING_DIRECTORY_INFO BNDDIR_INF

BOUND_MODULE_INFO MODULE_INF

BOUND_SRVPGM_INFO SRVPGM_INF

COLLECTION_SERVICES_INFO CS_INFO

COMMAND_INFO CMD_INFO

COMMUNICATIONS_ENTRY_INFO COMM_INFO

DATA_AREA_INFO DTAARA_INF

DATA_QUEUE_INFO DTAQ_INFO

DATABASE_MONITOR_INFO DBMON_INFO

DB_TRANSACTION_INFO TRANS_INFO

DRDA_AUTHENTICATION_ENTRY_INFO DRDA_AUTHE

ELECTRONIC_SERVICE_AGENT_INFO ESA_INFO

ENVIRONMENT_VARIABLE_INFO ENV_VARS

EXIT_POINT_INFO EXIT_POINT

EXIT_PROGRAM_INFO EXIT_PGM

FUNCTION_INFO FCN_INFO

FUNCTION_USAGE FCN_USAGE

HARDWARE_RESOURCE_INFO HW_INFO

HTTP_SERVER_INFO HTTP_SRVR

JOB_DESCRIPTION_INFO JOBD_INFO

JOB_QUEUE_INFO JOBQ_INFO

JOURNAL_INFO JRNINFO

JOURNAL_INHERIT_RULES LIB_JRN

JOURNAL_RECEIVER_INFO JRNRCV_INF

JOURNALED_OBJECTS JRN_OBJS

LIBRARY_LIST_INFO LIBLIST

LICENSE_INFO LIC_INFO

LOCKING_POLICY_INFO LOCK_POL

MEDIA_LIBRARY_INFO MEDIA_INFO

MESSAGE_FILE_DATA MSGF_DATA

MESSAGE_QUEUE_INFO MSGQ_INFO

NETSTAT_INFO NS_INFO

NETSTAT_INTERFACE_INFO NS_INTER

NETSTAT_JOB_INFO NS_JOB

NETSTAT_ROUTE_INFO NS_ROUTE

NVME_INFO NVME_INFO

OBJECTCONNECT_INFO OBJC_INFO

OBJECT_LOCK_INFO OBJ_LOCK

OBJECT_OWNERSHIP OBJ_OWN

OBJECT_PRIVILEGES OBJ_PRIV

OUTPUT_QUEUE_ENTRIES OUTQ_INFO

OUTPUT_QUEUE_ENTRIES_BASIC OUTQ_INFOB

OUTPUT_QUEUE_INFO OUTQ_DTL

PRESTART_JOB_INFO PREJ_INFO

PROGRAM_EXPORT_IMPORT_INFO EXPIMP_INF

PROGRAM_INFO PGM_INFO

QUERY_SUPERVISOR QRY_SUPER

QRY_SUPERI

QRY_SUPERT

QRY_SUPERX

RECORD_LOCK_INFO RCD_LOCK

REMOTE_JOURNAL_INFO RMT_JRNS

REPLY_LIST_INFO REPLYLIST

ROUTING_ENTRY_INFO RTG_INFO

SCHEDULED_JOB_INFO SCHED_JOB

SECURITY_INFO SEC_INFO

SERVER_SBS_CONFIGURATION SERVER_CFG

SERVER_SBS_ROUTING SRVR_RTG

SERVER_SHARE_INFO SHARE_INFO

SERVICES_INFO SERV_INFO

SOFTWARE_PRODUCT_INFO SFW_PROD

SUBSYSTEM_INFO SBS_INFO

SUBSYSTEM_POOL_INFO SBS_POOL

SYSLIMITS SYSLIMITS

SYSLIMITS_BASIC SYSLIMIT_B

SYSPACKAGESTMTSTAT PKGSTMSTAT

SYSPROGRAMSTMTSTAT PGMSTMSTAT

SYSTEM_STATUS_INFO SYS_STATUS

SYSTEM_STATUS_INFO_BASIC SYS_STAT_B

SYSTEM_VALUE_INFO SYSVALINFO

TELNET_SERVER_ATTRIBUTES TN_ATTR

TIME_PROTOCOL_INFO TIME_PROTO

TRACKED_JOB_QUEUES TRACKED_JQ

USER_INDEX_INFO USRIDX_INF

USER_INFO USER_INFO

USER_INFO_BASIC USER_INFOB

USER_SPACE_INFO USRSPC_INF

USER_STORAGE USER_STG

WATCH_INFO WATCH_INFO

WORKLOAD_GROUP_INFO WLG_INFO

Additional SYS files. That might come in handy. Again lots and lots of information readily available.

SYSCAT1

SYSCAT2

SYSCHARSET

SYSCHKCST

SYSCHRSET1

SYSCHRSET2

SYSCOLAUTH

SYSCOLUMNS

SYSCOLUMN2

SYSCOL2SSN

SYSCONTROL

SYSCST

SYSCSTAT

SYSCSTCOL

SYSCSTDEP

SYSCTRLDEP

SYSDISKS

SYSFEATURE

SYSFIELDS

SYSFILES

SYSFUNCS

SYSHIST

SYSINDEXES

SYSIXADV

SYSIXADVIX

SYSIXSTAT

SYSJARCONT

SYSJAROBJ

SYSKEYCST

SYSKEYS

SYSLANGS

SYSLIMPO_A

SYSLIMPO_B

SYSLIMTBL

SYSMQTSTAT

SYSPACKAGE

SYSPARMS

SYSPDISK

SYSPERIODS

SYSPGSTAT

SYSPIDISK

SYSPINDEX

SYSPISTAT

SYSPKAUTH

SYSPKSTAT

SYSPMQT

SYSPROCS

SYSPSTAT

SYSREFCST

SYSROUTDEP

SYSROUTINE

SYSROUTINP

SYSRTNAUTH

SYSRTNDEP

SYSSCHAUTH

SYSSCHEMAS

SYSSEQ

SYSSEQAUTH

SYSSEQOBJ

SYSTABAUTH

SYSTABDEP

SYSTABLES

SYSTISTAT

SYSTMPSTG

SYSTRIGCOL

SYSTRIGDEP

SYSTRIGGER

SYSTRIGUPD

SYSTSTAT

SYSTXTCLMN

SYSTXTCNFG

SYSTXTCOLI

SYSTXTDFLT

SYSTXTINDI

SYSTXTINDX

SYSTXTLBLS

SYSTXTSRHS

SYSTXTSRVR

SYSTYPES

SYSUDTAUTH

SYSVARAUTH

SYSVARDEP

SYSVARS

SYSVIEWDEP

SYSVIEWS

SYSXSRAUTH

IBMi Reading my Journal

All,

Ever had a situation where data was being erroneously written, or simply disappeared and you had no idea why. For anyone with a journaled database on the IBMi this will be an all to familiar issue. A commitment control boundary may undo your updates, or the data simply doesn't look right as it is being updated elsewhere in your call stack, far away from the code area you are presently maintaining.

When this happens to me I usually like to review the entries in the journal and analyse the database I/O data to get a feel for what is happening.

So how do I go about doing this.....

Firstly I would identify the journal that is being used. This may change from time to time so it is best to check each and every time you perform these steps. Do a WRKOBJ for the library and main file you are trying to track entries for.

WRKOBJ OBJ(DATALIB/TARGETFILE)

Take option 8=Display Description and page down three times or do a DSPFD for the given file and search of the Journal name from the output.

Now you know the journal. Perform the action(s) you wish to monitor for and note the times. (start and Finish)

You can then extract this data from the journal using the DSPJRN command. Below is an example of isolating the entries in a particular environment. I have gone for all files in library MSLTSTDTA in this example but there are plenty of filtering options. I then chose to output the resultant data into a file so that I could query it more easily.

DSPJRN JRN(TB_JRN/JRN0103000) FILE((DATALIB/*ALL)) FROMTIME(280222 074200) TOTIME(280222 074300) OUTPUT(*OUTFILE) OUTFILE(LDARE/LD_JOURNAL). The OUTFILE keyword is optional but will help if you want to perform some queries are a saved dataset.

Please note that the date and time parameters are particularly important as this can take a while to extract otherwise. Just this small sample took a few seconds interactively. If querying for more, you might want to consider submitting the task.

The resultant data file is formatted as follows:-

The main item to look for is the 'Entry Type'. These codes identify when data is inserted, updated or deleted from the database table as well as if it is rolled back.

The table below are the main ones that I refer to. All of these are for Journal Code 'R' - Operation on Specific Record. For a full list of the items covered by journaling see this link

https://www.ibm.com/docs/en/i/7.2?topic=information-journal-code-descriptions

Entry Code	Description
BR	Before-image of record updated for rollback operation
DR	Record deleted for rollback operation
PT	Record added to a physical file member. If the file is set up to reuse deleted records, then you may receive either a PT or PX journal entry for the change
PX	Record added directly by RRN (relative record number) to a physical file member. If the file is set up to reuse deleted records, then you may receive either a PT or PX journal entry for the change
UB	Before-image of a record that is updated in the physical file member (this entry is present only if IMAGES(*BOTH) is specified on the STRJRNPF command)
UP	After-image of a record that is updated in the physical file member
UR	After-image of a record that is updated for rollback information

For a full list of journaling codes you can use this link within the IBM i documentation.

https://www.ibm.com/docs/en/i/7.2?topic=information-all-journal-entries-by-code-type

Thanks for reading the

Synon 2E and Plex Emporium by Lee Dare (A developer blog).

Lee.

IBMi - Slow running batch tasks - A simple method to get you started (Synon developers guide)

Detailed breakdown for how to spot excessive IO and dependencies in your long running tasks

UPDATE: 06/12/2025 - Linked to a summary post dedicated to deep dive long form posts.

https://leedare-plex2e.blogspot.com/2025/12/synon-2e-technical-deep-dives.html

Edit - 27/04/2023 - Tidied up SQL formatting and added a note around multi-member files.

"The EOD job is taking too long!", says every system administrator ever!!!!

Tip: First of all if you are reading this please benchmark the program(s) and do one adjustment each time to determine what is making the difference for your situation. Don't be tempted into making too many changes at once as you'll never learn the value of each approach. Overtime you will learn which ones make the most difference for your system workload. Above all, you must remember to re-baseline after each set of changes, as not all changes are equal, some may actually slow down your programs.

There are numerous strategies for improving overall batch performance. Typically these would included.

Reducing unnecessary random IO - Use Arrays (or memory) instead of disk IO.
Ensure files are opened and closed once per job execution (if possible).
Try to eliminate multiple passes of the same data set.
Breaking the jobs up to perform threaded processing. Comes with a warning!
Remove journaling overheads (if possible).
Reduce record lock contention.
Place independent jobs in parallel in one subsystem.
Hardware upgrade (CPU, Disk, Memory) etc.
Distributing processing to after a high intensity window (deferred processing)

This is all well and good but how can you get at the information to assist you with identifying where your programmatic problems are. Many batch processes can nest into dozens of layers deep (both functions and physical program objects).

Obviously there are tools on the IBMi to assist greatly, some are licensed and others are provided by third parties. I am going to assume you are reading this and you are not yet ready for performance monitoring or job tracing but just have some general batch performance issues and require some quick wins.

Some developers have the skills to just look at a programs architecture and make some compelling changes but most require some hard evidence. Even if you are one of these programmers with a great deal of insight for your system I'd suggest you do the baseline below to measure your improvements.

To get started I tend to query the database member statistics pre and post execution for the program(s) to determine what occurred. Note: It is best to capture this data onsite (production) and slot wrap the following commands around the batch program(s). If you want to reduce noise (data interference) then end as many jobs and subsystems as relevant so that only the IO of the job at hand is being
captured.

Please note setup commands and queries are highlighted in pink and queries to analyse the results are highlighted in blue.

Capture before details of file(s) IO

SBMJOB CMD(DSPFD FILE(LIBRARY/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(LEED/BF_DTA)) JOB(BF_DTA)

Execute the batch tasks/programs in question and then capture the after details of the file(s) IO.

SBMJOB CMD(DSPFD FILE(LIBRARY/*ALL) TYPE(*MBR) OUTPUT(*OUTFILE) FILEATR(*PF) OUTFILE(LEED/AF_DTA)) JOB(AF_DTA)

After this you will have two files that you can compare the before and after scenario. This can give you insight into what database activity occurred whilst your program/job (set of jobs) were running. In the example below the SQL refers to the files as BF_DTA and AF_DTA (both in library LEED). You will need to change these accordingly.

SELECT T01.MBLIB, T01.MBFILE, T02.MBNRCD, T02.MBOPOP, T02.MBCLOP, T02.MBWROP, T02.MBUPOP, T02.MBDLOP,
T02.MBLRDS, T02.MBOPOP-T01.MBOPOP AS DIFF_OPEN, T02.MBCLOP-T01.MBCLOP AS DIFF_CLOSE, T02.MBWROP-T01.MBWROP AS DIFF_CRT, T02.MBUPOP-T01.MBUPOP AS DIFF_UPD, T02.MBDLOP-T01.MBDLOP AS DIFF_DLT, T02.MBLRDS-T01.MBLRDS AS DIFF_READ, (T02.MBWROP-T01.MBWROP)+(T02.MBUPOP-T01.MBUPOP)+(T02.MBDLOP-T01.MBDLOP) AS DIFF_CUD, (T02.MBUPOP-T01.MBUPOP)/NULLIF(T01.MBNRCD,0) AS IOINTENSE
FROM LEED/BF_DTA T01 INNER JOIN LEED/AF_DTA T02 ON T01.MBFILE = T02.MBFILE AND T01.MBLIB = T02.MBLIB

Note if you have multi members for a given file then the query above should have the WHERE clause extended by focusing on the main physical file member only. This helps avoid a many to many join scenario. In my latest environment I append the following. You can also tune your query to simply omit certain files also.

AND T01.MBFILE = T01.MBNAME

The output is a comparison by file showing the IO differences i.e. Reads, Updates, Opens, Closes etc.

To output this to a file wrap the SQL statement above with the following...

CREATE TABLE LEED/DIFF AS (

INSERT SQL STATEMENT ABOVE HERE!!!

) WITH DATA

Again, replace LEED with a library of your choice.

Please note that the target file shouldn't exist already and that a library and file name is your choice and will impact the queries below.

This raw data should be enough for you to highlight any performance bottlenecks.

Additional Queries

As each environment is different here are a few SQL's to execute over the differences file to provide some pointers.

The queries below will help to identify certain database performance scenarios. I have highlighted the recommended editable values.

High IO Count

SELECT MBLIB, MBFILE, DIFF_CUD FROM LEED/DIFF WHERE diff_cud > 100 ORDER BY diff_cud desc

Review and see if the IO is commensurate with the number of accounts or clients (records) being processed etc. if not, you may have duplication and refactoring could help.

High IO and Triggers

SELECT t01.MBLIB, t01.mBFILE, DIFF_upd FROM LEED/DIFF t01 inner join ytrgctlp t02 on T01.MBFILE = T02.TRGFIL WHERE diff_upd > 100 AND T02.TRGEVT = 'U' and T02.CMTLVL = 1 ORDER BY diff_upd desc

This will highlight any files with high IO that also have Synon triggers. Excessive volume may lead to increases in runtime. Perhaps you have changed objects that are updating records which haven't changed.... Null Update suppression may work here.

Excessive Reads (Arrays, *QUIT required, Join Logicals)

SELECT MBLIB, MBFILE, DIFF_read, MBNRCD FROM LEED/DIFF WHERE diff_read > 1000 ORDER BY diff_read desc

The 1000 (example) figure is very low.   Typically I would be looking for numbers in the millions for a good sized client.

Excessive Reads for low record count files (Possibility to move to arrays)

SELECT MBLIB, MBFILE, DIFF_read, MBNRCD FROM LEED/DIFF WHERE diff_read > 1000 and mbnrcd < 100 ORDER BY diff_read desc

Again, review these numbers based on the client database. If you are constantly reading from the same file then these could be committed to memory (array), moved to SSD, loaded in memory etc.

High UPDATES for low volume files (indicates potential contention i.e. a surrogate etc)

SELECT MBLIB, MBFILE, DIFF_upd, MBNRCD FROM LEED/DIFF WHERE diff_upd > 1000 and mbnrcd < 100 ORDER BY diff_upd desc

Note: Often people have a surrogate file to get next value for a key. Especially if you are running parallel processing (either multiple jobs or parallel jobs over one dataset) the parallel jobs can cause record lock contention.

High File Open/Close

SELECT MBLIB, MBFILE, DIFF_open FROM LEED/DIFF WHERE diff_open > 10 ORDER BY diff_open desc

This is used to identify if the task/program has excessive close downs. Perhaps a routine is set to Closedown = 'Y'. It is inefficient to keep opening and closing files. Check the Synon function options within your call stack.

I hope that this information is useful and motivates you to finally have the confidence to look at that long running job. Using some of these techniques above I have had significant performance improvements. It is the true IO data that is a reflection of your code and for that you need to use tools or mine the data for yourself.

This, I promise, is a good starting point and as always, I'm happy to help.

Thanks for reading the

Synon 2E and Plex Emporium by Lee Dare (A developer blog).

Lee.

Really Useful stuff!

Synon 2E - SQL and Model files summary

Synon 2E - SQL and Model files summary

Object Usage via SQL

SQL to extract object usage data on IBM I and incorporating some synon model queries for extra info.

IBMi A few more little SQL's

IBMi Reading my Journal

IBMi - Slow running batch tasks - A simple method to get you started (Synon developers guide)

Detailed breakdown for how to spot excessive IO and dependencies in your long running tasks