Posts

IBMi (AS400) fans only - Journaling database physical files (tables)

Image
Journaling database physical files is a powerful, built-in OS feature. It allows you to audit data file changes. Tracks additions, updates, deletions of a physical file. Let's see how it works. Scenario: I have a phisical file (Tabel) called CLANA00F, I wont tracks additions, updates and deletions about CLANA00F. Step 1: CRTJRNRCV create a journal receiver in your library CRTJRNRCV JRNRCV(VDOTEST1/JRCV) THRESHOLD(1500000) TEXT('My Journal Receiver') Step 2: CRTJRN create a journal into the journal receiver CRTJRN JRN(VDOTEST1/JRNL) JRNRCV(VDOTEST1/JRCV) Here is the just created journal receiver and the journal.  Step 3: STRJRNPF start auditing STRJRNPF FILE(VDOTEST1/CLANA00F) JRN(VDOTEST1/JRNL) IMAGES(*BOTH) OMTJRNE(*OPNCLO) // the option OMTJRNE(*OPNCLO) omit open and close entries. // Open and close operations on the specified file members do not create open and close journal entries. It saves some storage space in the attached receivers.                                

IBMi (AS400) fans only - How to access multimember physical file using SQL alias

Image
#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. SQL has no direct access to IBM i multiple-member physical file but OS/400 support an SQL alias statement . Therefore, I need to create an alias for the member I want to access with SQL. I give an example using the IBM i PUB400 public server. In library VDOTEST1, file QDDSLF I have 8 members. CLANA01L CLANA02L CLANA03L CLANA04L CLANA05L CLANA06L CNCON01L OTORD01L I want to access member CLANA03L via SQL. I type STRSQL (Enter) then I create the ALIAS: create alias A_CLANA03L for VDOTEST1.QDDSLF(CLANA03L) The alias is a persistent object.  The ALIAS object has attribute DDMF. Now I can access the member with SQL: Display data from CLANA03L Don't forget to remove the ALIAS with a "DROP" command: DROP ALIAS myLibrary/myAlias That’s it. Thanks to the suggestion of my friend Don Anderson I add this way: SELECT CAST(LINE AS CHAR(120))                      FROM TABLE (   

IBMi (AS400) fans only - From Query to SQL

Image
#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. In most of IBMi we have a large number of QUERIES. Often these QUERIES are provided to end users, with all limitations of this practice. I think this practice is deplorable. How about turning these queries into SQL and then writing an RPG with embedded SQL? The QUERY to SQL translation process is really simple, it only takes one command: RTVQMQRY QMQRY(MyQueryLib/MyQuery) SRCFILE(MyLib/MySrcFile) ALWQRYDFN(*YES) That's it! Below an example of a cursor declaration in an SQLRPGLE program (Sql embedded) // preapre the data recordset exec sql declare C1 cursor for select OTORD00F.OTANN0, OTORD00F.OTCOR0, CLANA00F.CLNOM0 from OTORD00F inner join CLANA00F on OTCCL0 = CLCCL0 where OTANN0 like :F1ANN0lke and OTCOR0 between :F1COR0str and :F1COR0end and CLNOM0 like :F1NOM0lke order by OTCOR0 for read only; Here is a complete SQLRPGLE sample program with SQL embedded . #IBMiSam

IBMi (AS400) fans only - Another way to manage spooled files

Image
WSF utility, works like WRKSPLF but... New feature: Generate a PDF from a spooled file #IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. If you are interested in this program  Download WSF *SAVF  (New version with Create PDF option) Need some help loading the *savf?  HOW TO GET *SAVF FROM YOUR DESKTOP TO YOUR IBM I SYSTEM Users often have many spool-file rows. How to  quickly   find the last done? how to quickly find the one produced on October 15th? These and other options are just a click away. The WRKSPLF command does not show spool-file rows sorted by Creation-Date/Creation-Time, so the last one is not the first one in the list. I thought a different way of looking at spooled-file rows might be useful. In this program (WSF) the spool-file rows appears in order of Creation-Date/Creation-Time, with the last one at the top of the list. I also added the ability to filter each column, for example to see only the rows in HOLD status

IBMi (AS400) fans only - How to pass parameters to a Query

Image
#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. First of all look at  this post  and compile and populate OTORD00F file to use this example. Let's create a query called Q01A : ccm   represents our parameter to pass to the query. Type F3 Now let's write the CL to pass the ccm parameter to the query: Q01ACL CL ****************** Inizio dati *******************************              PGM        PARM(&CCM)                                          DCL        VAR(&CCM) TYPE(*CHAR) LEN(5)                        STRQMQRY   QMQRY(Q01A) OUTPUT(*) QMFORM(*QMQRY) +                           ALWQRYDFN(*YES) SETVAR((CCM &CCM))               ENDPGM                                             ******************** Fine dati ******************************* We save and compile the CL. Now let's try. Type: CALL PGM(Q01ACL) PARM(('103')) I have two records into OTORD00F with  OTCCM0  = ' 103 '. This is the r

IBMi (AS400) fans only - How to call a C runtime function from RPG program.

Image
#IBMiSample Each IBMi user knows they run the most efficient system. All others just have too much money. This example comes from https://www.ibm.com/support/pages/coding-rpg-iv-beginners-tutorial This example calls the C runtime printf() function to print a message to the standard output instead of to the external message queue.

IBMi (AS400) fans only - how to read a flat file with sql embedded

Image
#IBMiSample It often happens that you have to read all the records of a file. Years ago I would have used the RPG cycle defining the Input/Primary file. Today, with the introduction of embedded SQL, I would do this: First of all I create a file, then I manually write some records. to create a file I write this SQL source: IPFLSQL.SQL -- --  RUNSQLSTM SRCFILE(myLib/MySouceFile) SRCMBR(IPFLSQL) -- --  Generazione tabella CREATE OR REPLACE TABLE myLib /IPFLI00F ( IIFANN CHARACTER(1) NOT NULL WITH DEFAULT, IITEXT CHARACTER(25) NOT NULL WITH DEFAULT ) RCDFMT IPFLI ;  Run IPFLSQL.SQL with RUNSQLSTM SRCFILE(myLib/MySouceFile) SRCMBR(IPFLSQL) to create the file IPFLI00F. Then populate IPFLI00F with some records. No matter what you type, just a few records are enough. Then compile and run  IPFL01.SQLRPGLE       **free       *******************************************************       *  How to read an entire file from the beginning      *       ************************************************