Topic
  • 7 replies
  • Latest Post - ‏2019-05-13T08:40:12Z by stiruvee
robberendt
robberendt
84 Posts

Pinned topic How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

‏2019-05-01T19:32:59Z | journal

I am looking at the following two services:

DISPLAY_JOURNAL returns a lot of nice information However I would like to automatically parse the column ENTRY_DATA.  IBM has an undocumented stored procedure to do so called DISPLAY_JOURNAL_ENTRY_INFO.

I am trying to figure out a way to join these two together.

Let's start simple.

I can call the latter with

call qsys2.display_journal_entry_info(2, --not sure what 2 means
'journal_library',
'journal name',
Sequence_number,
'receiver library',
'receiver name',
'journal code (aka PT, DL, etc)'
);

Sample:

call qsys2.display_journal_entry_info(2, --not sure what 2 means
'#MXJRN',
'BPCS_ALL',
20200218, -- SEQUENCE_NUMBER
'#MXJRN', -- RECEIVER_LIBRARY
'ALLRCV7590', -- RECEIVER_NAME
'HI' -- 'journal code (aka PT, DL, etc)'  JOURNAL_CODE or JOURNAL_ENTRY_TYPE
);

How do I get the results from this stored procedure returned as a view or udtf so that I may join it with DISPLAY_JOURNAL?

 

Bibliography

DISPLAY_JOURNAL

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfdisplayjournal.htm

DISPLAY_JOURNAL_ENTRY_INFO

Undocumented feature.  Sparse documentation only found in APAR and cover letter information

call qsys2.display_journal_entry_info(2, --not sure what 2 means
'journal_library',
'journal name',
Sequence_number,
'receiver library',
'receiver name',
'journal code (aka PT, DL, etc)'
);

 

  • Jim_IT
    Jim_IT
    20 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-09T03:22:03Z  

    Rob,

    I've done this, but after checking the performance of the stored procedure I quickly abandoned it but I will post my code so you can evaluate it yourself.

     

    CREATE FUNCTION CVTJRNLIB/HOUSEJE3(
    JOURNAL_LIBRARY VARCHAR(10),
    JOURNAL_NAME VARCHAR(10),
    STARTING_RECEIVER_LIBRARY VARCHAR(10),
    STARTING_RECEIVER_NAME VARCHAR(10),
    STARTING_TIMESTAMP TIMESTAMP,
    STARTING_SEQUENCE DECIMAL(21,0),
    JOURNAL_CODES VARCHAR(48),
    JOURNAL_ENTRY_TYPES VARCHAR(1200),
    FILE_LIBRARY VARCHAR(10),
    USER_PROFILE VARCHAR(10),
    JOB VARCHAR(26),
    "PROGRAM" VARCHAR(10))
    RETURNS TABLE (
    SYSTEM_ID CHAR(8),
    ENTRY_TIMESTAMP TIMESTAMP,
    RECEIVER_NAME VARCHAR(10),
    RECEIVER_LIBRARY VARCHAR(10),
    SEQUENCE_NUMBER DECIMAL(21,0),
    JOURNAL_CODE CHAR(1),
    JOURNAL_ENTRY_TYPE CHAR(2),
    PROGRAM_NAME VARCHAR(10),
    COUNT_OR_RRN BIGINT,
    FILE_NAME CHAR(10),
    FILE_LIB CHAR(10),
    FILE_MBR CHAR(10),
    USER_ID VARCHAR(10),
    JOB_NAME VARCHAR(10),
    JOB_USER VARCHAR(10),
    JOB_NUMBER VARCHAR(6),
    HONROV DECIMAL(3,0),
    HORCD CHAR(1),
    HONUM DECIMAL(7,0),
    HOCUS DECIMAL(2,0),
    HOCSL CHAR(6),
    HOLOF CHAR(3),
    HODIR CHAR(2),
    HOSTN CHAR(25),
    HOCIT CHAR(25),
    HOSTC CHAR(2))
    LANGUAGE SQL
    SPECIFIC CVTJRNLIB/HOUSEJE3
    DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    BEGIN
    DECLARE RS RESULT_SET_LOCATOR VARYING ;
    DECLARE HONROV DECIMAL(3,0);
    DECLARE HORCD CHAR(1);
    DECLARE HONUM DECIMAL(7,0);
    DECLARE HOCUS DECIMAL(2,0);
    DECLARE HOCSL CHAR(6);
    DECLARE HOLOF CHAR(3);
    DECLARE HODIR CHAR(2);
    DECLARE HOSTN CHAR(25);
    DECLARE HOCIT CHAR(25);
    DECLARE HOSTC CHAR(2 );
    FOR JE CURSOR FOR
    SELECT
    CAST(CURRENT SERVER AS CHAR(8)) AS SYSTEM_ID,
    ENTRY_TIMESTAMP,
    RECEIVER_NAME,
    RECEIVER_LIBRARY,
    SEQUENCE_NUMBER,
    JOURNAL_CODE,
    JOURNAL_ENTRY_TYPE,
    PROGRAM_NAME,
    COUNT_OR_RRN,
    SUBSTR(OBJECT,1,10) AS FILE_NAME,
    SUBSTR(OBJECT,11,10) AS FILE_LIB,
    SUBSTR(OBJECT,21,10) AS FILE_MBR,
    "CURRENT_USER" AS USER_ID,
    JOB_NAME,
    JOB_USER,
    JOB_NUMBER
    FROM TABLE(
    DISPLAY_JOURNAL(
    JOURNAL_LIBRARY,
    JOURNAL_NAME,
    STARTING_RECEIVER_LIBRARY,
    STARTING_RECEIVER_NAME,
    STARTING_TIMESTAMP,
    STARTING_SEQUENCE,
    JOURNAL_CODES,
    JOURNAL_ENTRY_TYPES,
    FILE_LIBRARY,
    'HOSTPF',
    '*FILE',
    '*ALL',
    USER_PROFILE,
    JOB,
    PROGRAM)) AS A
    FOR READ ONLY

    DO

    CALL QSYS2/DISPLAY_JOURNAL_ENTRY_INFO(2,
    JOURNAL_LIBRARY,
    JOURNAL_NAME,
    SEQUENCE_NUMBER,
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    JOURNAL_CODE);

    ASSOCIATE RESULT SET LOCATORS(RS)
    WITH PROCEDURE QSYS2 . DISPLAY_JOURNAL_ENTRY_INFO;

    ALLOCATE JEDETAILS CURSOR FOR RESULT SET RS;

    -- each column needs to be specified here,and declared
    -- above,in the 'return table' clause,and also
    -- each one needs to be declared the proper type and size,too.
    FETCH JEDETAILS INTO
    HONROV,HORCD,HONUM,HOCUS,HOCSL,
    HOLOF,HODIR,HOSTN,HOCIT,HOSTC;

    PIPE(SYSTEM_ID,ENTRY_TIMESTAMP,RECEIVER_NAME,
    RECEIVER_LIBRARY,SEQUENCE_NUMBER,JOURNAL_CODE,
    JOURNAL_ENTRY_TYPE,PROGRAM_NAME,COUNT_OR_RRN,
    FILE_NAME,FILE_LIB,FILE_MBR,USER_ID,
    JOB_NAME,JOB_USER,JOB_NUMBER,
    HONROV,HORCD,HONUM,HOCUS,HOCSL,
    HOLOF,HODIR,HOSTN,HOCIT,HOSTC);

    CLOSE JEDETAILS;
    END FOR;
    RETURN;
    END ;

    Jim

     

    Updated on 2019-05-09T03:23:19Z at 2019-05-09T03:23:19Z by Jim_IT
  • robberendt
    robberendt
    84 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-09T11:51:52Z  
    • Jim_IT
    • ‏2019-05-09T03:22:03Z

    Rob,

    I've done this, but after checking the performance of the stored procedure I quickly abandoned it but I will post my code so you can evaluate it yourself.

     

    CREATE FUNCTION CVTJRNLIB/HOUSEJE3(
    JOURNAL_LIBRARY VARCHAR(10),
    JOURNAL_NAME VARCHAR(10),
    STARTING_RECEIVER_LIBRARY VARCHAR(10),
    STARTING_RECEIVER_NAME VARCHAR(10),
    STARTING_TIMESTAMP TIMESTAMP,
    STARTING_SEQUENCE DECIMAL(21,0),
    JOURNAL_CODES VARCHAR(48),
    JOURNAL_ENTRY_TYPES VARCHAR(1200),
    FILE_LIBRARY VARCHAR(10),
    USER_PROFILE VARCHAR(10),
    JOB VARCHAR(26),
    "PROGRAM" VARCHAR(10))
    RETURNS TABLE (
    SYSTEM_ID CHAR(8),
    ENTRY_TIMESTAMP TIMESTAMP,
    RECEIVER_NAME VARCHAR(10),
    RECEIVER_LIBRARY VARCHAR(10),
    SEQUENCE_NUMBER DECIMAL(21,0),
    JOURNAL_CODE CHAR(1),
    JOURNAL_ENTRY_TYPE CHAR(2),
    PROGRAM_NAME VARCHAR(10),
    COUNT_OR_RRN BIGINT,
    FILE_NAME CHAR(10),
    FILE_LIB CHAR(10),
    FILE_MBR CHAR(10),
    USER_ID VARCHAR(10),
    JOB_NAME VARCHAR(10),
    JOB_USER VARCHAR(10),
    JOB_NUMBER VARCHAR(6),
    HONROV DECIMAL(3,0),
    HORCD CHAR(1),
    HONUM DECIMAL(7,0),
    HOCUS DECIMAL(2,0),
    HOCSL CHAR(6),
    HOLOF CHAR(3),
    HODIR CHAR(2),
    HOSTN CHAR(25),
    HOCIT CHAR(25),
    HOSTC CHAR(2))
    LANGUAGE SQL
    SPECIFIC CVTJRNLIB/HOUSEJE3
    DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    BEGIN
    DECLARE RS RESULT_SET_LOCATOR VARYING ;
    DECLARE HONROV DECIMAL(3,0);
    DECLARE HORCD CHAR(1);
    DECLARE HONUM DECIMAL(7,0);
    DECLARE HOCUS DECIMAL(2,0);
    DECLARE HOCSL CHAR(6);
    DECLARE HOLOF CHAR(3);
    DECLARE HODIR CHAR(2);
    DECLARE HOSTN CHAR(25);
    DECLARE HOCIT CHAR(25);
    DECLARE HOSTC CHAR(2 );
    FOR JE CURSOR FOR
    SELECT
    CAST(CURRENT SERVER AS CHAR(8)) AS SYSTEM_ID,
    ENTRY_TIMESTAMP,
    RECEIVER_NAME,
    RECEIVER_LIBRARY,
    SEQUENCE_NUMBER,
    JOURNAL_CODE,
    JOURNAL_ENTRY_TYPE,
    PROGRAM_NAME,
    COUNT_OR_RRN,
    SUBSTR(OBJECT,1,10) AS FILE_NAME,
    SUBSTR(OBJECT,11,10) AS FILE_LIB,
    SUBSTR(OBJECT,21,10) AS FILE_MBR,
    "CURRENT_USER" AS USER_ID,
    JOB_NAME,
    JOB_USER,
    JOB_NUMBER
    FROM TABLE(
    DISPLAY_JOURNAL(
    JOURNAL_LIBRARY,
    JOURNAL_NAME,
    STARTING_RECEIVER_LIBRARY,
    STARTING_RECEIVER_NAME,
    STARTING_TIMESTAMP,
    STARTING_SEQUENCE,
    JOURNAL_CODES,
    JOURNAL_ENTRY_TYPES,
    FILE_LIBRARY,
    'HOSTPF',
    '*FILE',
    '*ALL',
    USER_PROFILE,
    JOB,
    PROGRAM)) AS A
    FOR READ ONLY

    DO

    CALL QSYS2/DISPLAY_JOURNAL_ENTRY_INFO(2,
    JOURNAL_LIBRARY,
    JOURNAL_NAME,
    SEQUENCE_NUMBER,
    RECEIVER_LIBRARY,
    RECEIVER_NAME,
    JOURNAL_CODE);

    ASSOCIATE RESULT SET LOCATORS(RS)
    WITH PROCEDURE QSYS2 . DISPLAY_JOURNAL_ENTRY_INFO;

    ALLOCATE JEDETAILS CURSOR FOR RESULT SET RS;

    -- each column needs to be specified here,and declared
    -- above,in the 'return table' clause,and also
    -- each one needs to be declared the proper type and size,too.
    FETCH JEDETAILS INTO
    HONROV,HORCD,HONUM,HOCUS,HOCSL,
    HOLOF,HODIR,HOSTN,HOCIT,HOSTC;

    PIPE(SYSTEM_ID,ENTRY_TIMESTAMP,RECEIVER_NAME,
    RECEIVER_LIBRARY,SEQUENCE_NUMBER,JOURNAL_CODE,
    JOURNAL_ENTRY_TYPE,PROGRAM_NAME,COUNT_OR_RRN,
    FILE_NAME,FILE_LIB,FILE_MBR,USER_ID,
    JOB_NAME,JOB_USER,JOB_NUMBER,
    HONROV,HORCD,HONUM,HOCUS,HOCSL,
    HOLOF,HODIR,HOSTN,HOCIT,HOSTC);

    CLOSE JEDETAILS;
    END FOR;
    RETURN;
    END ;

    Jim

     

    Thank you.  I'll mull this over.

  • stiruvee
    stiruvee
    48 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-10T02:24:43Z  

    Thank you.  I'll mull this over.

    Hi,

    Is there a reason for not using old method of using cpyf with *nochk option? I have been running following SQL script to extract journal information. SQL script is executed using   i Navigator - Run SQL Scripts  after changing journaled file name and time period . If needed the script can be converted into stored procedure. The approach of using new stored procedures seems complicated and time consuming.

    Note: DBASCRIPTS.JRNRAW2 uses  output format TYPE2 excluding column joesd.

     

    getjrninfo.sql:

    -- Extract table specific records from journal receiver
    call qsys2.qcmdexc ( 'DSPJRN JRN(TESTLIB/QSQJRN) RCVRNG(*CURRENT) FILE(TESTLIB/SITE) FROMTIME(01022019 090000) TOTIME(28022019 170000) ENTTYP(*RCD)  OUTPUT(*OUTFILE)   OUTFILFMT(*TYPE2) OUTFILE(QTEMP/JRNRAW) ENTDTALEN(*CALC) ' ) ;
    
    -- Create table with final layout
    create table QTEMP.JRNFINAL as (  select * from DBASCRIPTS.JRNRAW2  , TESTLIB.SITE) with no data ;
    
    -- Copy data to final table 
    call qsys2.qcmdexc ( 'CPYF FROMFILE(QTEMP/JRNRAW) TOFILE(QTEMP/JRNFINAL) MBROPT(*replace) FMTOPT(*NOCHK)' ) ;
    
    select * FROM QTEMP.JRNFINAL ;
    

     

  • robberendt
    robberendt
    84 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-10T20:16:26Z  
    • stiruvee
    • ‏2019-05-10T02:24:43Z

    Hi,

    Is there a reason for not using old method of using cpyf with *nochk option? I have been running following SQL script to extract journal information. SQL script is executed using   i Navigator - Run SQL Scripts  after changing journaled file name and time period . If needed the script can be converted into stored procedure. The approach of using new stored procedures seems complicated and time consuming.

    Note: DBASCRIPTS.JRNRAW2 uses  output format TYPE2 excluding column joesd.

     

    getjrninfo.sql:

    <pre class="html dw" dir="ltr">-- Extract table specific records from journal receiver call qsys2.qcmdexc ( 'DSPJRN JRN(TESTLIB/QSQJRN) RCVRNG(*CURRENT) FILE(TESTLIB/SITE) FROMTIME(01022019 090000) TOTIME(28022019 170000) ENTTYP(*RCD) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE2) OUTFILE(QTEMP/JRNRAW) ENTDTALEN(*CALC) ' ) ; -- Create table with final layout create table QTEMP.JRNFINAL as ( select * from DBASCRIPTS.JRNRAW2 , TESTLIB.SITE) with no data ; -- Copy data to final table call qsys2.qcmdexc ( 'CPYF FROMFILE(QTEMP/JRNRAW) TOFILE(QTEMP/JRNFINAL) MBROPT(*replace) FMTOPT(*NOCHK)' ) ; select * FROM QTEMP.JRNFINAL ; </pre>

     

    How, pray tell, does your suggestion parse the transaction data into all of the columns of the table that the transaction was against?  Because I just do not see it.  

    If it wasn't for that issue our initial query would simply be   

     

    
    
    SELECT * FROM TABLE (
                     QSYS2.DISPLAY_JOURNAL( 'TESTLIB', 'QSQJRN')) AS JT;
    
  • stiruvee
    stiruvee
    48 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-11T02:42:29Z  

    How, pray tell, does your suggestion parse the transaction data into all of the columns of the table that the transaction was against?  Because I just do not see it.  

    If it wasn't for that issue our initial query would simply be   

     

    <pre class="pre codeblock" dir="ltr" style="font-family:'courier new' , 'courier' , monospace;color:rgb( 34 , 34 , 34 );font-size:12px;font-style:normal;font-weight:400;letter-spacing:normal;text-align:left;text-indent:0px;text-transform:none;word-spacing:0px;background-color:rgb( 255 , 255 , 255 )"> SELECT * FROM TABLE ( QSYS2.DISPLAY_JOURNAL( 'TESTLIB', 'QSQJRN')) AS JT; </pre>

     

    Table QTEMP.JRNFINAL in my  example contains individual columns from site table after parsing JOESD column in QTEMP.JRNRAW. Parsing JOESD is done by CPYF. There are several articles on the net with instructions for using cpyf method to extract journal information. My script simplifies the process and eliminates manual steps.

     

    My script is only four lines. How did you miss the CPYF step? You can use following steps to create table dbascripts.jrnraw2 with  *TYPE2 output format excluding JOESD column.

     

    CREATE SCHEMA DBASCRIPTS;
    create table DBASCRIPTS.TEMPTBL (i1 int) ;
    insert into DBASCRIPTS.TEMPTBL values (1) ;
    call qsys2.qcmdexc ( 'DSPJRN JRN(DBASCRIPTS/QSQJRN) RCVRNG(*CURRENT) FILE(DBASCRIPTS/TEMPTBL)  ENTTYP(*RCD)  OUTPUT(*OUTFILE)   OUTFILFMT(*TYPE2) OUTFILE(DBASCRIPTS/JRNRAW2) ENTDTALEN(*CALC) ' ) ;
    alter  table DBASCRIPTS.JRNRAW2 drop column joesd ; 
    

     

  • robberendt
    robberendt
    84 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-12T13:56:06Z  
    • stiruvee
    • ‏2019-05-11T02:42:29Z

     

    Table QTEMP.JRNFINAL in my  example contains individual columns from site table after parsing JOESD column in QTEMP.JRNRAW. Parsing JOESD is done by CPYF. There are several articles on the net with instructions for using cpyf method to extract journal information. My script simplifies the process and eliminates manual steps.

     

    My script is only four lines. How did you miss the CPYF step? You can use following steps to create table dbascripts.jrnraw2 with  *TYPE2 output format excluding JOESD column.

     

    <pre class="html dw" dir="ltr">CREATE SCHEMA DBASCRIPTS; create table DBASCRIPTS.TEMPTBL (i1 int) ; insert into DBASCRIPTS.TEMPTBL values (1) ; call qsys2.qcmdexc ( 'DSPJRN JRN(DBASCRIPTS/QSQJRN) RCVRNG(*CURRENT) FILE(DBASCRIPTS/TEMPTBL) ENTTYP(*RCD) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE2) OUTFILE(DBASCRIPTS/JRNRAW2) ENTDTALEN(*CALC) ' ) ; alter table DBASCRIPTS.JRNRAW2 drop column joesd ; </pre>

     

    Ok, now I see it.  I'll have to take a look at this.  

    I was hoping to avoid generating a temporary table.  Our journal receivers we are farming through measure into the TB's.

  • stiruvee
    stiruvee
    48 Posts

    Re: How do I convert DISPLAY_JOURNAL_ENTRY_INFO from a stored procedure into a view, or UDTF (User Defined Table Function)?

    ‏2019-05-13T08:40:12Z  

    Ok, now I see it.  I'll have to take a look at this.  

    I was hoping to avoid generating a temporary table.  Our journal receivers we are farming through measure into the TB's.

     

    * What about temporary table created by DISPLAY_JOURNAL_ENTRY_INFO? Calling DISPLAY_JOURNAL_ENTRY_INFO  for every journal record will create & drop  temporary table  for every journal record.  How are you avoiding  temporary table by using DISPLAY_JOURNAL_ENTRY_INFO?

     

    * Disk space required by temporary tables depends on number of records (&size) extracted for a specific table & specific time period. Not on total size of journal receivers.  If disk space is really a constraint to use old method, you can consider processing journal information for smaller time period (one hour) at a time instead of processing data for entire time period (day/week/month) in one iteration.