Reads AS400 Journals and creates tables for reading by Java client of Logstash app.

LOGSTASHER.RPGLE

 * Logstasher - program to run as RCVJRNE exit point.
 * It will create tables of journal records in which the blob 
 * field JOESD is parsed into the fields of the record that 
 * is the subject of the journal record.
 * - - - - - - - - - - - - - - - - - - - - - - - -
 *    dependencies                                
 * 1. LOGSTASH Data Area provides properties      
 * 2. FLATFILE Physical File required for compile 
 * 3. QCMDEXC 
 * 4. SQL 
 * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -       
 *     Testing Setup
 * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -       
 * 0.) Step zero on IBM i is always set your CURLIB 
 *  
 * 1.) CREATE REQUIRED OUTPUT TABLE  
 *     CREATE TABLE *CURLIB/FLATFILE (FLATFIELD CHAR (32766) NOT NULL WITH
 *     DEFAULT) RCDFMT RFLAT 
 * 
 * 2.) CREATE TEST DATA IN JOURNAL 
 *     create table funkyfile ( 
 *     id int NOT NULL AS IDENTITY PRIMARY KEY 
 *     , first_name  CHAR(20) 
 *     , last_name CHAR(30) 
 *     , age int) 
 *  
 * 2a.) STRJRNPF FILE(FUNKYFILE) JRN(JRN01) IMAGES(*BOTH) 
 * 2b.) INSERT INTO FUNKYFILE 
 *      (id, first_name, last_name, age) values(1, 'Steve', 'Jobs', 55)
 * 2c.) INSERT INTO FUNKYFILE 
 *      (id, first_name, last_name, age) values(2, 'Bill', 'Gates', 65)
 *  
 * 3.)  Call This Program 
 *      RCVJRNE JRN(JRN01) EXITPGM(LOGSTASHER) FILE(*ALLFILE) JRNCDE((R)) 
 * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  

  ctl-opt Option(*Srcstmt:*Nodebugio:*NoUnRef);
  ctl-opt Main(Main); 

    dcl-pr qcmdexc ExtPgm; 
        cmd          char(500)     Const Options(*VarSize); 
        cmdLength    packed(15: 5) Const;  
    end-pr;

    dcl-ds logstash DTAARA; 
           JrnLib  CHAR(10); 
    end-ds; 

    dcl-proc Main; 
    dcl-pi *N extpgm;
        pTYPE2 like(maxdata); 
        pJOCOM CHAR(3);  
    end-pi; 

       dcl-f flatfile usage(*output) usropn extfile(myfile);                     
       dcl-s myfile char(20);   // this is the name of the file that is written  
       dcl-s cmd char(100); 
       dcl-s maxdata char(32922) template;
       dcl-ds flatFileDS likerec(RFLAT);  

       dcl-c sqlCreateTable 
          'create table ';

       dcl-c sqlCreateTemp
          '/tempjrn (JOENTL  NUMERIC(5,0) + 
           , JOSEQN  NUMERIC(10,0) +  
           , JOCODE  CHAR(1) + 
           , JOENTT  CHAR(2) + 
           , JODATE  CHAR(6) + 
           , JOTIME  NUMERIC(6,0) + 
           , JOJOB   CHAR(10) + 
           , JOUSER  CHAR(10) + 
           , JONBR   NUMERIC(6,0) + 
           , JOPGM   CHAR(10) + 
           , JOOBJ   CHAR(10) + 
           , JOLIB   CHAR(10) + 
           , JOMBR   CHAR(10) + 
           , JOCTRR  NUMERIC(10,0) +  
           , JOFLAG  CHAR(1) + 
           , JOCCID  NUMERIC(10,0) + 
           , JOINCDAT CHAR(1) + 
           , JOMINESD CHAR(1) + 
           , JORES   CHAR(6))'; 

       dcl-c sqlCreateJoin  
           ' AS (SELECT * FROM QTEMP.TEMPJRN JOIN ';

        dcl-c sqlCreateNoData  
           ' T2 ON 1=1) WITH NO DATA RCDFMT RFLAT';

        dcl-s tempJrnCreated             IND;  
        dcl-s stmtTxt                    CHAR(3000); 
        dcl-s file_system_name           CHAR(10);
        dcl-s createdTableArray          CHAR(20) DIM(999); 
        dcl-s lastEntryCreatedTableArray PACKED(3:0) INZ(0);

        dcl-ds unformattedInput; 
           unformattedData               CHAR(32922);       
        end-ds;                                             
        dcl-ds formattedInput;                              
           JOENTL                        zoned( 5:0) ;      
           JOSEQN                        zoned(10:0) ;      
           JOCODE                        char(  1  ) ;      
           JOENTT                        char(  2  ) ;      
           JODATE                        char(  6  ) ;      
           JOTIME                        zoned( 6:0) ;      
           JOJOB                         char( 10  ) ;      
           JOUSER                        char( 10  ) ;      
           JONBR                         zoned( 6:0) ;      
           JOPGM                         char( 10  ) ;      
           JOOBJ                         char( 10  ) ;      
           JOLIB                         char( 10  ) ;      
           JOMBR                         char( 10  ) ;      
           JOCTRR                        zoned(10:0) ;      
           JOFLAG                        char(  1  ) ;      
           JOCCID                        zoned(10:0) ;      
           JOINCDAT                      char(  1  ) ;      
           JOMINESD                      char(  1  ) ;      
           JORES                         char(  6  ) ;      
           JOESD                         char(32641) ;      
        end-ds; 

        // *************************************************
        // Main Proc  
        // *************************************************

        in logstash;  // retrieve JrnLib config property
        formattedInput = pType2; 
        unformattedInput = pType2;

        if not tempJrnCreated; 
            stmtTxt = sqlCreateTable + %trim(JrnLib) + sqlCreateTemp; 
            exec sql execute immediate :stmtTxt; 
            tempJrnCreated = *ON; 
        endif;

        if %lookup((%trim(jolib) + %trim(joobj)) : createdTableArray ) = 0;
            stmtTxt = sqlCreateTable + %trim(JRNLIB) + '.' + %trim(joobj)  
                    + sqlCreateJoin  + %trim(jrnlib) + '.' + %trim(joobj)   
                    + sqlCreateNoData; 
            exec sql execute immediate :stmtTxt;
            lastEntryCreatedTableArray += 1; 
            createdTableArray(lastEntryCreatedTableArray) =  
                %trim(jolib) + %trim(joobj); 
        endif;

        myfile = JOOBJ; 
        cmd = 'OVRDBF FLATFILE ' + %trim(myfile) + ' LVLCHK(*NO) +
               OVRSCOPE(*JOB)'; 
        qcmdexc(cmd:%len(cmd));
        cmd = 'OVRDBF '+ %trim(myfile) + ' LVLCHK(*NO) +          
               OVRSCOPE(*JOB)';
        qcmdexc(cmd:%len(cmd));
        open flatfile;
        flatFileDS = pType2;
        write rflat flatFileDS;
        close flatfile;

        return;

    end-proc;

Hi Greg,

I stumbled upon you code which is very close to what I needed. I'm wondering if it is possible to ask you help us write a program to extract AS400 Journals, and how much would it cost? We need a way to:
. capture data changes for all the DB2 tables ;
. send the changes "continuously" (let's say every 5 minutes) to the other systems (say Kafka)
We tried 3rd party packages but not satisfied.

If you can get this message, I'd appreciate your response to me at johnlee@guess.com

Thanks

John

@xingkuan 4 years later we stumble on the same issue. Have you got your problem solved with any commercial product? If yes, please DM me :)

Apologies to John, I never saw the message from 2018. @calloc I don't recognize an issue in the code above but I can't say for certain that this code is the final, working version of the code. I only used logstash the one time and I was definitely stumbling around in the dark. I still have no clue what logstash is about. Happy ending though, I was moved to a higher priority project.