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

#IBMiSample We can use %DAYS to add or subtract to a date. Hier is an example:

#IBMiSample The ACTIVE_JOB_INFO table function returns one row for every active job.  You can use it to get the same information as the WRKACTJOB command in an alternate mode. Look at the following examples: a. List all jobs of  user VDOTEST SELECT JOB_NAME, SUBSYSTEM, SUBSY00001, AUTHO00001, JOB_TYPE, FUNCT00001, FUNCTION, JOB_STATUS,  MEMOR00001                 FROM TABLE(ACTIVE_JOB_INFO())                                 WHERE JOB_NAME LIKE '%VDOTEST%'                               b. List all jobs of  subsystem QBATCH SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_TOTAL_DISK_IO_COUNT, ELAPSED_CPU_PERCENTAGE                                            FROM TABLE(QSYS2.ACTIVE_JOB_INFO(                                 SUBSYSTEM_LIST_FILTER => 'QBATCH')) A                             ORDER BY ELAPSED_TOTAL_DISK_IO_COUNT DESC                         c. List all jobs temporary storage SELECT JOB_NAME, AUTHORIZATION_NAME, TEMPORARY_STORAGE,      SQL_STATEMENT_TEXT         

#IBMiSample INDARA (Indicator Area) keyword for ICF files workstn indds indicator data structure INDARA is used to put the field option indicators and function(response) key indicators in a separate area of memory called indicator area. By using this keyword, we can make indicator data structure in our program to give the customized name to indicators defined in the display file. Hence, it makes the program more readable and easy to understand. Step 1. indicate INDARA  keyword in your Display File Step 2. indicate INDDS in your RPG workstation file declaration dcl-f PKLTSFV workstn indds(Dspf) sfile(SFL1 : SF1NUM); Step 3. declare the  indicator area        dcl-ds Dspf qualified ;          Exit            ind pos(03) inz(*off);          SflClr          ind pos(50) inz(*off);          DspAtrRI        ind pos(61) inz(*off);          DspClrRed       ind pos(62) inz(*off);          SflEnd          ind pos(91) inz(*off);        end-ds ;     Step 4. In the RPG source program replace all the

#IBMiSample I'm tired of one-line messages. Too short. The average user needs clear and comprehensive messages. Then I need a large enough window to display a clear and complete message . Why not use a SUBFILE? A SUBFILE program can handle a list of many lines, a clear and complete message is just a list of many text lines! Here's how I did it I need a table that contains the messages.                           --                                                  --  RUNSQLSTM SRCFILE(VDOTEST1/QSOURCE) SRCMBR(W03AM00F) --                                                  CREATE TABLE VDOTEST1/W03AM00F (                    W3FANN CHARACTER(1) NOT NULL WITH DEFAULT,          W3TPMS CHARACTER(6) NOT NULL WITH DEFAULT,          W3COD0 NUMERIC(4, 0) NOT NULL WITH DEFAULT,         W3RIG0 NUMERIC(2, 0) NOT NULL WITH DEFAULT,         W3TXT0 CHARACTER(078) NOT NULL WITH DEFAULT         )                                                   RCDFMT W03AM                                       

#IBMiSample The Run SQL Statements (RUNSQLSTM) command processes a source file of Structure Query Language (SQL) statements. let's have an example: Create a source member AS52A00F into library VDOTEST1/QSQLSRC. VDOTEST1 is MyLibrary. Use your library name. AS52A00F.SQL

#IBMiSample How to check the correctness of a numeric date field 6 with 0 decimals. This program receives as input 1. a date in the DDMMYY format ( p_data ), for example p_data= 100612, that is June 10, 2012, an Error field ( p_error ). 2. a Flag field accepts date = 0 ( p_zero ). 3. returns a BLANK or 'E' value in the p_error field as a result of the check. CHKDATA.RPGLE       **free        ctl-opt option(*nodebugio:*srcstmt:*nounref) dftactgrp(*no)         ALWNULL(*USRCTL) ;         // Try with  STRDBG and         // CALL CHKDATA PARM(X'0120718F' 'x' 'N') valid date         // CALL CHKDATA PARM(X'0999999F' 'x' 'N') wrong date         // CALL CHKDATA PARM(X'0000000F' 'x' 'Y') no date, but it is allowed         // CALL CHKDATA PARM(X'0000000F' 'x' 'N') no date, it is not allowed         dcl-pi CHKDATA;           p_data packed(6);           p_error char(1);           p_zero  char(1);

#IBMiSample Typically we have a table and we need to perform read, write, update operations. This simple program is an example of how. First of all look at  this post  and compile and populate these files to use this example.

#IBMiSample First of all look at this post and compile and populate these files to use this example. Then compile the two following soures, OT08SF displayfile, OT08SF sqlrpg file. OT08SFV.DSPF                                             CHGINPDFT(CS UL HI)                                             INDARA                                             CA03(03 'F3=EXIT')                                             REF(OTORD00F)       *--------------------------------------------------------------                 R SFL1                      SFL       *--------------------------------------------------------------                   S1OPT          1A  B  6  2                   S1ANN0    R             +3REFFLD(OTANN0)                   S1COR0    R            + 1REFFLD(OTCOR0) EDTCDE(Z)                   S1NOM0    R            + 1REFFLD(CLANA/CLNOM0 CLANA00F)       *--------------------------------------------------------------                 R FMT01                     SFLCTL(SFL1)

#IBMiSample A simple way is to leave the file open by ending programs with RETURN instead of SETON LR. Example: Program A calls Program B. Program B opens a printerfile and write some pages in the spool file. Program B closes with RETURN. The printer file is already open. Program A calls Program B again. Program B opens a printerfile (it is already open) and write some other pages on the same spool file. Program B closes with RETURN. The printer file is already open. Program A calls Program B again. Program B opens a printerfile (it is already open) and write some other pages on the same spool file. Program B closes with RETURN. The printer file is already open. ... and so on. At the end, run RCLACTGRP ACTGRP(QILE). This command will end the QILE activation group, it means the spool file will also be closed. I appreciate all the comments made on this blog.

#IBMiSample Let's try with a very simple csv like this, called file1.csv: Code;Name 3741;Joe 3742;Barth 3810; 3945; First of all we need to save file1.csv into ISF . In my PUB400 (free AS400 at pub400.com) I have this directory: /home/PALDO I copy file1.csv to  /home/PALDO Use standard FTP transfer software (I use WinScp, I think Filezilla will work too) Let's take a look inside my directory I type WRKLNK and I verify the existence of File1.csv It looks as expected: To import this file I need a similar table on DB2. In my example it means that I will create a table with a decimal field and a charachter field . I create the table TEMPCSV (PALDO1 is my Library): CREATE TABLE PALDO1.TEMPCSV ( CLCCL0 DECIMAL(7, 0) DEFAULT 0 , CLNOM0 CHAR(50) DEFAULT ' ' ) RCDFMT FMTXX Here's my new table TEMPCSV : The table  TEMPCSV  is now created and it is empty: We can optionally set up a source file to note lines not imported by the CPYF

#IBMiSample How to get a primary key field that increments itself and a time field that updates itself? Look at  LGID00 and  LGDTTM fields. Create a table: CREATE TABLE LGCUS00F ( LGID00 INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, LGDTTM TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, LGNAME CHAR(50), LGSURN CHAR(50)) RCDFMT LGCUS Now try to add a record: INSERT INTO LGCUS00F (LGNAME, LGSURN) VALUES ('Aldo', 'Succi') Let's see the result: SELECT * FROM LGCUS00F As you can see, LGID00=1 and LGDTTM=2018-04-01-10.01.49.781922 That's it! I appreciate all the comments made on this blog.

#IBMiSample This looks like magic! The operating system, in a totally automatic way, keeps track of every change in the data. This simple technique allows you to find the value of a field on a certain date and time. use STRSQL Step 1) CREATE TABLE CUSTOMER (CUSTOMER_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1 ) ,CUSTOMER_NAME VARCHAR(10) ,ADDRESS VARCHAR(20)) Step 2) alter table customer add system_start timestamp(12) generated always as row begin not null add system_end timestamp(12) generated always as row end not null add tx_start timestamp(12) generated always as transaction start id implicitly hidden add period system_time (system_start, system_end) Step 3) create table customer_h like customer Step 4) alter table customer add versioning use history table customer_h Step 5) Now use command line: CRTJRNRCV JRNRCV(VDOTEST1/JRNRCVNAME) THRESHOLD(150) TEXT('Journal receiver') Step 6) CRTJRN JRN(VDOTEST1/jrnname) JRNRCV(VDOTEST1/jrnrcv