A few more little SQL's for IBM i

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.
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

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

 

 

*MOVE and *MOVE ARRAY with 2E date and time fields

Every 2E model has functions that are no longer required, yet they persist and continually get reused.  A good example of this common issue is the continued usage of legacy date conversion routines that exist in most 2E data models.

How many of you have the following functions (or very similar) in your model?

The reason why is quite simple!

 

They were probably written long before 2E supported the date fields indicated.  After all, the DT8 (Date 8) and DT# (Date ISO) were added relatively late in the tools evolution.

I recall working at a company in London (pre 2000), who had solved the Y2K problem by transitioning their date fields from DTE to an user defined field type CDT (Century Date), this was basically a DT8 i.e. YYYYMMDD but implemented way before 2E had supported it.  I believe 2E were quite late to the party and implemented DT8 support around 1999.  Synon Inc started supporting the DT8 field specifically for people transitioning their models from DTE to DT8 and to help overcome any potential Y2K date rollover issues within their code.  There was even a model analysis tool that helped people identify these issues.

The problem is..... Many sites had already solved their issues (as above), whilst others stuck with the DTE format and its limitations and perhaps only targeted certain field like DOB.  I reckon there are dozens of sites around the world that continue to define dates as DTE out of habit!  I would be guilty of doing so for sure.

Anyhow, for those that have moved on and are defining DT8 or DT# or TS# (ISO Timestamp), I still believe that many programmers will be using the self coded legacy functions to perform date conversion.  I know I have too been guilty of this crime from time to time in the heat of coding.

 

The thinking goes something like this?

1. Navigate to your system functions or date functions (scoping file DFN).
2. Filter on Date or Convert (cnv) and pick the function that suites your needs.
3. Test function and everything works fine.
4. Great, Job done!

However, there is one small flaw with this.  You didn't need to use the legacy date conversion functions anymore.  Remember, these were likely written before 2E supported the source or target date format or have been written more recently by a developer who didn't realise that 2E already handles automatic date conversion between its (shipped) data types.

 

As long as you are moving data from a field type that is supported and that it has 'date like' data in it, 2E will automatically handle the conversion for you.

The table below (directly from the 2E online documentation) helps showcase all the automatic conversions that are handled by 2E and below that a table highlighting the limitations or rules.

 

 

* Conversions for the shipped D8# and the user-defined DT8 (8-digit internal representation) data types are identical.

 

What does this look like in the code?  Well let's take a look at some generated code to find out.

The following 'mock up' function is trying to covert the DTE (*JOB DATE) to a DT# (ISO) and DT8 format.

The source (RP4) for this is generated as follows:-

The ISO conversion is a little more complicated and is also generated into a subroutine so that it can be called for any date conversions from DTE to DT#.  The DTE to DT8 conversion is something I am sure you have done many times in code using the *ADD etc these are generated inline and not passed to a subroutine due to the small number of lines of code.

The subroutine code for ISO is below.

A final point to note is that if you have a number field of 7.0 length masquerading as a date, you can move it into a date field DTE date.  Same with 8.0 to DT8, which will be more common if interfacing to data derived from more modern databases that never had a history of supporting dates like DTE or Julian.

You can then apply 2E date functions as usual.

Finally, we are not limited to dates, times and their myriad of formats are also interchangeable.

Thanks for reading. 

Lee.

SQL is your friend

Today I had a minor task to implement.  The task was to initialise some new data in a file.

Typically, I would write a program as an EXCEXTFUN and do my processing via standard 2E code.  The problem with little data update programs is that unless you manage them well (i.e. remove them, label them so you can ignore them etc), they start to clutter the model.  On the other hand they are handy to have around in case someone needs to do something similar in the future.

However, unless you are a software house, you probably don't care too much that this 'product field' was initialised 8 years ago by xyz user.

That all said and done, my task was to insert a data record and there were some fields that needed to be initialised that contained the business data, and, there was also an AUDIT STAMP for the INSERT.  If you don't mind me saying, it was quite a convoluted one at that too with numerous fields.  This AUDIT STAMP required a User, Date, Time, Job Number and Job Name among other fields.  With 2E all of these are simple to MAP and are available to us via the JOB context.

My task was to implement the data without adding to the setup/fix program maintenance burden and on this occasion we have a few choices.

1. Write a document explaining to the implementer how I want them to add a record. Either by a YWRKF hack or perhaps a user screen.
2. Give them a small data file and ask them to CPYF the records into the target file as *ADD.
3. Write that 2E program I spoke of above and get them to call it.

Or, as I did in this instance.  

    4. Simply give them a line of SQL I wanted them to execute.

Sweet, job done, but how do I initialise the 2E formatted date, or get the current system date and time from the machine and, where do I get the JOB details from that are so easily available within 2E code via the JOB context.

The answer is as follows: SQL has what it calls global variables (Blue) and functions (Red).  It just turns out that some of these are useful for me to update my fields in the AUDIT STAMP.

In order to initialise my fields I just substitute my insert data with the following:-

User Profile = USER
Date = DEC(CURDATE())-19000000
Time = DEC(CURTIME())
Job Number = LEFT(JOB_NAME,6)
Job Name = SUBSTRING(JOB_NAME, LOCATE('/',JOB_NAME,8)+1,10)

You will note that to get the 2E DTE format we simply minus the 19000000.  For the standard TIME and USER fields we just use the variables without any manipulation.

For Job Number we need to extract the data the JOB_NAME variable.  Usually this is formatted as 123456/LDARE/QPADEV0001 for example.  However, for people with longer names it could be 654321/PLONGNAME/EOD, also the job running the task may not be an interactive screen if it was submitted for example.

This means that to get the job name data from the JOB_NAME SQL global variable we need to call a little function that locates and substrings all the data after the 2nd '/'.  Given that the JOB_NUMBER is always 6 long I've just arbitrarily started from the 8th character.  I could have embedded more LOCATE calls to replace this hardcoded value, but as always, time was against me.

Hopefully, in the future if you need to update a table via SQL, but were put off by now knowing how to get at some of these global variables, the above will stick in your mind.

Thanks for reading. 

Lee.

DSPRCD refresh trick and a few extra tips.

Today I was asked to fix a DSPRCD screen that wasn’t refreshing after an up-screen EDIT function (Command Key) had changed some values. Upon returning to the DSPRCD it still showed the original values.

DISCLAIMER: This is correct behaviour for a 2E function but a little bewildering for the user as they are hesitant as to whether they actually made the change in the update screen.

For a DSPFIL we would use the *reload subfile to force a reload of the screen.

For a PMTRCD we can set the function option ‘Repeat Prompt’ to get the same behaviour.

For DSPRCD etc it is a little trickier…….

Typically, I have seen people utilise a driver program and execute the screen in a loop and simply recall the screen or exit.  This is doable……    There is however, (as always)…. An alternative, a trick and as it is much simpler I will show you.

The answer is to force the transaction to continue.  You maybe be aware of the PGM.*Continue Transaction field in 2E.   It is typically used (in action diagrams) for the DSPTRN and EDTTRN function types.  The same field is used in many AD’s including the DSPRCD.

In this instance it is simply there to initiate a loop.  Typically this field only has one condition (*NO) for using the EDTTRN and DSPTRN.  See these notes in the 2E manual….

https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/it-operations-management/ca-2e/8-7/building/building-applications/modifying-action-diagrams/understanding-action-diagram-user-points.html

The trick is that the DSPRCD generates code that says if W0TRN (*Continue Transaction) = ‘R’ then keep re-showing screen. 

Our issue (default model)  is that we have no way (default way) of setting it…… We do now. 

I have simply added a new condition to the field *Continue Transaction (I’ve done it at many sites).  The condition is called ‘*Reload Trick’ and we just need to set in after we’ve called our screen.

Please note you may need to follow this with a *QUIT depending on your circumstances.

Voila…. It works 😊

Additionally, did you know.....?

1. USER:Process Command Keys has got nothing to do with command keys. Command key processing should normally be added to USER:Validate Detail Screen. 

or for DSPRCDx

1. The correct screen context DTL/2ND/3RD must be used for error message parameters to ensure that field is highlighted. Normally in any action diagram coding the screen context is not significant.
2. The validation cycle processes data from all pages together, therefore, the relation settings apply to the function as a whole, and not to individual screens.
3. It is not possible to control, which page the user sees. If there is an error the function always displays the first page, which has an error outstanding, but this may not correspond to the actual error message displayed on the message subfile line. The messages are displayed according to the sequence they were sent. Therefore, you may have to review the validation sequence.

Tip: Validate a page at a time and only start validating a subsequent page if *PGMERR is not set.

Thanks for reading. 
Lee.

Enhancement for 2E (Come on Broadcom.....)

I've been having a think about a few enhancements for 2E in the hope that the new owners of the products will invest in them. Leaving aside the roadmap ideas and up voted ideas on the idea wall (which need acting upon, especially REST API support), here are a few other ideas for consideration.

Enhancement One

Add an option in Display All Functions to get at the details screen for a given function to show the model object display screen.  Currently you have to do a Usage (wait…) and the do an 8 next to the 000 level function.

I use this screen to see what list an object is associated with etc.

Enhancement Two

The Open Functions screen is really handy but it would be more useful if we can search for a function via its CPF Name.

Enhancement Three

Action Diagram templates to display (fully) what code they are generating.  A good example of this is the RTVOBJ where the return code is set to *Normal at initialisation and automatically set to *Record does not exist in ‘Record Not Found’ user point.  I would be good if this was shown.

There are other examples where the templates could be expanded to show the exact processing to be generated.


I've seen so much bad coding where people are initialising these values not realising what the template generates.  As 2E is supposed to insulate you from the generated code it would make sense to show in AD what actually occurs.... wouldn't it....

Perhaps an expert mode like F21 (WRKACTJOB) to determine how much detail is shown.  I do honestly believe there is a lot of bad code out there due to template knowledge.

Enhancement Four

SQL Statement Support.  Whilst this is already possible for embedded EXCUSRSRC etc, I was thinking more of an interface to do generic SQL processing but use to the 2E method of declaring files and fields etc, have the added bonus of Impact Analysis.

Ability to define an SQL interface using 2E files/Fields and to execute the SQL statement.  This will show up in usages for the files and fields as *SQLSTM

Enhancement Five

License information.  Sure there is a command to show licensing of the product on the box (YDSPLICPRD), but I use it so infrequently I have to look it up almost every time I need it.  Perhaps the model details screens can be extended to have a command key to show the model licensing details.

So come on Broadcom.  Shape your product, prove that you still care.

Thanks for reading.
Lee.

Slow running batch tasks - A simple method to get you started

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.

1. Reducing unnecessary random IO - Use Arrays (or memory) instead of disk IO.
2. Ensure files are opened and closed once per job execution (if possible).
3. Try to eliminate multiple passes of the same data set.
4. Breaking the jobs up to perform threaded processing. Comes with a warning!
5. Remove journaling overheads (if possible).
6. Reduce record lock contention.
7. Place independent jobs in parallel in one subsystem. 
8. Hardware upgrade (CPU, Disk, Memory) etc.
9. 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.
Lee.