IBM i (AS400) fans only - tips and tricks to start RPGfree programming - simple examples of RPG Free

Step 1. On your IBMi create a Save File in any library (A Save File is a special type of file that is used to store a backup of data or objects). I use the MYSELF1 library in this example. CRTSAVF FILE(MYSELF1/YourSavF) Step 2. Save Your library to Your Save File. SAVLIB LIB(LibraryToSave) DEV(*SAVF) SAVF(MYSELF1/YourSavF) Now go to your Windows Personal Computer 1. Open a command prompt window ( CMD ). 2. Type the following: CD C:\TEMP Press the Enter key. 3. FTP SYSTEMNAME or FTP xxx.xxx.xxx.xxx where SYSTEMNAME is the IBM i system name and xxx.xxx.xxx.xxx is the IP address. Press the Enter key. 4. Type the user ID and password. 5. To change to binary mode, type the following: BIN Press the Enter key. 6. To change the host and client to naming convention type 1, type the following: QUOTE SITE NAMEFMT 1 Press the Enter key. 7. Get the SAVF and put it into C:\TEMP GET /QSYS.LIB/MYSELF1.LIB/YOURSAVF.SAVF YourSavF.SAVF Press the Enter key. Now YourSavF.SAVF is in PC folder C:\TEMP

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.                                

#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 (   

#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

New feature:  F18=Search string into listed files WSF utility, works like WRKSPLF but... New feature: Searching a string into  spooled files 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

#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

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

#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      *       ************************************************

#IBMiSample Compile and Run this SQLRPGLE to create table CLANA00F, index CLANA01L and table OTORD00F, and populate:       **free       *****************************       * create CLANA00F & OTORD00F       *****************************        ctl-opt option(*nodebugio) dftactgrp(*no) actgrp(*new);         // The EXEC SQL is never executed. It is used at compile time.         exec sql Set Option Commit = *None;         // Create File         exec sql           CREATE or REPLACE TABLE myLib/CLANA00F (           CLANN0 CHARACTER(01) NOT NULL WITH DEFAULT,           CLCCL0 NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,           CLCIB0 CHARACTER(03) NOT NULL WITH DEFAULT,           CLNOM0 CHARACTER(50) NOT NULL WITH DEFAULT           )           RCDFMT CLANA           ;         // Create index         exec sql           CREATE INDEX myLib/CLANA01L ON myLib/CLANA00F (CLCCL0 ASC);         // Fill values         exec sql           INSERT INTO CLANA00F (CLCIB0, CLNOM0)          

#IBMiSample This CLP  sends a SpoolFile per Email as TXT attachment and need 4 parameters: &SPOOLF Spool file name &SUBJCT Subject &BODY00 body &MAIL01 Email (Pay attention at the "Send Email" note at the bottom) Compile this CLP source with: CRTCLPGM PGM(myLib/SNDSPLF) SRCFILE(myLib/mySourceFile) SRCMBR(SNDSPLF) SNDSPLF.CLP                    PGM        PARM(&SPOOLF &SUBJCT &BODY00 &MAIL01) /*****************************************************************************/ /* Send a SpoolFile per Email as TXT attachment                              */ /* &SPOOLF Spool file name                                                   */ /* &SUBJCT Subject                                                           */ /* &BODY00 body                                                              */ /* &MAIL01 Email                                                             */ /* Try with: