IBM i fans only - tips and tricks to start RPG free format programming with simple examples

In this post, we’ll explore how to efficiently retrieve program files used in queries within an IBM i library using the DPR  command, along with its associated CLP ( DPRCL ) and SQL RPGLE program ( DPRRPG ). This combination allows users to dynamically fetch program file references based on various filters, enhancing the management of programs in the IBM i environment. Overview of Components 1. The DPR Command The DPR command serves as the entry point for retrieving program files. It accepts two parameters: the library name and the object type. Below is the source code for the DPR  command:                CMD        PROMPT('Show Pgm Files via DSPPGMREF')  /* COMMAND PROCESSING PROGRAM IS: DPRCL */                PARM       KWD(LIBRARY) TYPE(*CHAR) LEN(10) MIN(1) ...

A colleague of mine left me a real treasure trove of queries, accumulated over many years of work. It’s an endless list: over 1000 queries, all well cataloged and described. Recently, I needed to find a query related to customer orders. Knowing that the order file is called OTORD, the customer file CLCLI, and the sample file CMCMP, I started opening the most likely queries. The process was long and frustrating: Open Query > Specify file selections > View the files used > Close Query. Stressful, right?   So, I thought I’d let our trusty IBM i do some work for me. I created a procedure to retrieve the list of files used by all the queries in a specific library, with the ability to add some filters to answer more specific questions. For example:   Which queries use the files OTORD00F, CLCLI00F, and CMCMP00F?   The result? A clean and well-organized subfile: Don't forget to set the screen to 132 columns! The source code follows: FQRCL    ...

If you need to monitor user access on your IBM i system, you can use SQL to query the history log. Note: At the end of this post, you'll find a request directly addressed to you, the readers. Here’s a simple example of how you can do this: SELECT MESSAGE_ID,        MESSAGE_TIMESTAMP,        FROM_USER,        MESSAGE_TEXT     FROM TABLE (             QSYS2.HISTORY_LOG_INFO()         )     WHERE message_id IN ('CPF1164', 'CPF1124')           AND FROM_USER IN ('QSECOFR', 'QSYS', 'QSYSOPR')     ORDER BY MESSAGE_TIMESTAMP In this query: - QSYS2.HISTORY_LOG_INFO(): This function retrieves information from the history log. -  message_id IN ('CPF1164', 'CPF1124') : Filters the results to include only specific message IDs, which indicate sign-on and sign-off events. - FROM_USER in ('QSECOFR', 'QSYS', 'QSYSOPR'): Filters the resu...