5 replies Latest Post - ‏2014-02-21T03:09:36Z by tomliotta
2 Posts

Pinned topic DisplaY_Journal, how to break out entry data?

‏2014-02-17T13:22:19Z |

So the UDF is all fine and such, but how do we break out the entry_data? It comes out purely as hex which is a useless form. Does anyone have a suggestion on how to use that field? Perhaps embedded SQL in RPG and put it into a DS?

  • tomliotta
    38 Posts

    Re: DisplaY_Journal, how to break out entry data?

    ‏2014-02-19T14:03:18Z  in response to BPJN_Chris_Holko

    What does your "entry data" contain? Is this a database journal? Audit journal? Accounting journal? Other journal?

    Overlaying the JOESD field on a DS is one good way to do it.


    • BPJN_Chris_Holko
      2 Posts

      Re: DisplaY_Journal, how to break out entry data?

      ‏2014-02-19T14:31:14Z  in response to tomliotta

      table data, I am inquiring on a normal production table. I was hoping for an SQL only solution.

      • tomliotta
        38 Posts

        Re: DisplaY_Journal, how to break out entry data?

        ‏2014-02-20T11:23:42Z  in response to BPJN_Chris_Holko

        I'd never really thought about SQL itself providing a solution, though there is some merit in the idea considering how important journals can be to execution. Journals don't really seem to be SQL objects as far as a SQL standard might go (but I don't have access to any official SQL Standards document to see how the feature might be referenced). And journals on IBM i aren't quite like whatever might be used by any other SQL DBMS.

        I have had similar desire to see formatted journal entries. Long ago, I created a REXX procedure that would do exactly that. I'll post the source for the procedure I use, then I'll add a couple usage comments and some thoughts on where a major problem could arise if something similar might be attempted in actual SQL.

        The source:

        ./* Show basic journal entries for LIB/FILE                             */

        /* Get our file name from input parm...                */
             parse arg inparm
             qfile = translate( inparm )

             parse var qfile part1 '/' part2
             if part2 = '' then
                   lib = '*LIBL     '
                   file = substr( part1, 1, 10 )
                   sqlname = file
                   lib = substr( part1, 1, 10 )
                   file = substr( part2, 1, 10 )
                   sqlname = strip( lib )'/'strip( file )

             if file = ' ' then do ; say 'No file' ; exit ; end

        /* --------------------------------------------------- */
        /* This section determines the appropriate journal...  */
        /* --------------------------------------------------- */

             qfile = (file)(lib)

        /* Locate journal from the file description...         */
        /* DSPFD *OUTFILE could be used, then the row could be */
        /* FETCHED. But this is simpler.                       */

        /* We'll use a 1K receiver variable...                 */
             fd = Copies('00'x, 1024)

             "call QSYS/QDBRTVFD ( ",
                                   " &fd              ",
                                   " x'00000400'      ",
                                   " '                    ' ",
                                   " 'FILD0100'       ",
                                   " &qfile           ",
                                   " '*FIRST    '     ",
                                   " '0'              ",
                                   " '*LCL      '     ",
                                   " '*EXT      '     ",
                                   " x'0000000000000000' ",
                                ")                   "

        /* Extract journal name info...                        */
        /* The offset is a 4-byte binary INT, so we convert to */
        /* hex before extracting, and convert the 8-byte hex   */
        /* to decimal for an offset. The c2x() converts four   */
        /* character bytes to hex; then x2d() can convert      */
        /* eight hex digits to decimal. With the offset, we    */
        /* retrieve journal and library name.                  */

             ofsjrnInf = x2d( c2x( substr( fd , 379, 4 ) ) )
             if ofsjrnInf = 0 then do ; say 'No journal (offset)' ; exit ; end

        /* Convert "offset" to "position"...                   */
             posjrnInf = ofsjrnInf + 1

             jrn   = substr(  fd ,  posjrnInf       , 10 )
             jlib  = substr(  fd , (posjrnInf + 10) , 10 )

        /* --------------------------------------------------- */
        /* This section displays entries to a temp table...    */
        /* --------------------------------------------------- */

        /* Display journal entries into temp file...           */
        /* DSPJRN is prompted to allow examination...          */

             '? dspjrn    'strip( jlib )'/'strip( jrn ) ,
                          ' file(( 'strip( lib )'/'strip( file )' )) ' ,
                          ' rcvrng( *CURRENT ) ' ,
                          ' enttyp( ' ,
                                  ' BR DL DR PT PX UB ' ,
                                  ' UP UR ' ,
                                ' ) ' ,
                          ' output( *OUTFILE ) outfilfmt( *TYPE4 ) ' ,
                          ' outfile( QTEMP/MYJRNOUT4 ) ' ,
                          ' entdtalen( *CALC ) '

        /* --------------------------------------------------- */
        /* This section supplies column formatting...          */
        /* --------------------------------------------------- */

        /* We now have a temporary file (MyDSPJRN4) with       */
        /* data. It has all journal columns described plus     */
        /* the 'entry-specific data' that is not described. We */
        /* can now create a record format that combines the    */
        /* two sets of column descriptions into a new table    */
        /* that will have the column descriptions of both.     */

             address '*EXECSQL' execsql ,
                     'CREATE TABLE QTEMP/MyDATA4  AS (' ,
                     ' SELECT a.JOENTL, a.JOSEQN, a.JOCODE, ' ,
                     ' a.JOENTT, a.JOTSTP, a.JOJOB, a.JOUSER, ' ,
                     ' a.JONBR, a.JOPGM, a.JOOBJ, a.JOLIB, a.JOMBR, ' ,
                     ' a.JOCTRR, a.JOFLAG, a.JOCCID, a.JOUSPF, ' ,
                     ' a.JOSYNM, a.JOJID, a.JORCST, a.JOTGR, ' ,
                     ' a.JOINCDAT, a.JOIGNAPY, a.JOMINESD, ' ,
                     ' a.JORES, a.JONVI, ' ,
                        'b.* from QTEMP/MYJRNOUT4 a,' ,
                         sqlname ' b ) WITH NO DATA'
             address '*EXECSQL' execsql 'COMMIT'

        /* Do an image-copy of the displayed entries into our  */
        /* fully described format...                           */

             'cpyf        fromfile( QTEMP/MYJRNOUT4 ) ' ,
                          ' tofile( QTEMP/MyDATA4 ) ' ,
                          ' mbropt( *REPLACE ) ' ,
                          ' fmtopt( *NOCHK ) '

        /* Now no longer needed for copying...                 */
             'dltf        QTEMP/MYJRNOUT4 '

        /* Make a hex field blank for 5250 viewing...          */
        /* (Remove it to learn why)...                         */

             address '*EXECSQL' execsql ,
                     'UPDATE  QTEMP/MyDATA4 ' ,
                        'set JOJID = ''        '' ' ,
                        'with NC'

        /* --------------------------------------------------- */
        /* This section displays formatted data...             */
        /* --------------------------------------------------- */

        /* Now view the fully-described entries...             */

             'runqry      *n  QTEMP/MyDATA4 '

        /* --------------------------------------------------- */
        /* This section cleans up and exits...                 */
        /* --------------------------------------------------- */

             address '*EXECSQL' execsql ,
                     'DROP TABLE QTEMP/MyDATA4 '
             address '*EXECSQL' execsql 'COMMIT'


        Run it this way:

        STRREXPRC SRCMBR( myRexxProc ) SRCFILE( mylib/myRexSrcF ) PARM( myPF )
        STRREXPRC SRCMBR( myRexxProc ) SRCFILE( mylib/myRexSrcF ) PARM( 'mylib/myPF' )

        First, if all comments are removed, it's much shorter. It's not as complicated as it might seem at a glance. It accepts the optionally qualified name of a file as parm input. The file description is retrieved to determine the journal name. Entries from the journal for that file are placed into a QTEMP file using *TYPE4 output formatting.

        Note that this has not been used for files with BLOBs/CLOBs/etc., nor with other less common table or column attributes. It can probably be modified to fit unusual tables.

        As you probably know, the 'entry-specific data' in that output (or in related journal API or RTVJRNE/RCVJRNE commands) isn't formatted according to the database file field descriptions. It's just a single-field record image. The REXX proc then issues a CREATE TABLE statement that uses all of the fields from the new QTEMP file except JOESD plus all of the fields from the given file name. This has the effect of creating a record format that has everything formatted including the JOESD area.

        Once that second table is created, the REXX proc runs a CPYF with FMTOPT( *NOCHK ) to do a record-image copy from the first QTEMP file into the second. That has the effect of applying field definitions over the JOESD field area. The first QTEMP file is no longer needed, so it's deleted.

        At that point, it's reasonable to run a basic RUNQRY to see a clean view of all retrieved journal entries. All that's left to do is basic cleanup and EXIT.

        Although it's done in REXX, any ILE language could be used. I used REXX because I didn't have the SQL Dev Kit at the time I created it, and REXX does SQL easily. The final table in QTEMP is deleted, but it could be left alone if desired.

        Now, consider making a change near the end. Replace the RUNQRY and subsequent statements with CREATE CURSOR ... WITH RETURN and OPEN CURSOR. Leave the cursor open and return the open cursor as a result set.

        Then use CREATE FUNCTION to create a user-defined table function so you can run SELECT over it at any desired time. First thing you'll run into is the need to declare every column in the returned UDTF.

        Because of that, it's no longer reasonable to pass in a file name as a parm. Instead, each file will need its own UDTF definition. That can certainly be done, but it'd be easier just to use a DS declared like an external file.

        SQL would have a similar problem. Every kind of JOESD in every journal would potentially have a different format. The DSPJRN parm values for ENTDTALEN() could have drastic effects. And for tables that had been DROPped or ALTERed, there would be no valid external description available. (My proc has the same problem, but that's my choice. Technically, I could create a repository of descriptions to keep things as long as I wanted.)

        Regardless, SQL needs the field descriptions for the journal output file and those descriptions need to be in the database catalog. The definition of JOESD stays the same regardless of what kind of journal is accessed and what kind of Entry Type is requested.

        The varying Entry Types alone would make it difficult to (efficiently) ensure any consistent output. We might only be interested in UB, UP, PT, DL and one or two others; but there is a big variety of them. And when combined with the variety of Entry Types multiplied by unknown number of possible files (and other objects), any coding could be extremely complex.

        Of course, we have it easy. We only need to code for the few uses we need personally. Trying to code for what everybody in the world might need is difficult. I don't expect to see much from SQL to handle this.



        • ScottForstie
          11 Posts

          Re: DisplaY_Journal, how to break out entry data?

          ‏2014-02-20T21:41:39Z  in response to tomliotta


          Did you realize that with SQL on i you can consume the entry_data?  It doesn't have to be limited to the default hexadecimal form.

          For example, lets say that I have a data journal and I'm interested in having SQL pull out the before images for modification operations.

          If I know the layout of the entry specific data, I can consume the detail with SQL.

          -- What data was deleted?
          select cast( cast(substring(entry_data,3, 10) as varchar(10) for bit data) as varchar(32000) ccsid 37) as entry_data  from table (
          'PRODDATA', 'QSQJRN', -- Journal library and name
          '', '', -- Receiver library and name
          CAST(null as TIMESTAMP), -- Starting timestamp
          CAST(null as DECIMAL(21,0)), -- Starting sequence number
          'R', -- Journal codes
          'DL,PT,PX,UP', -- Journal entry types
          'PRODDATA','SALES','*FILE','SALES', -- Object library, Object name, Object type, Object member
          '', -- User
          '', -- Job
          '' -- Program
          ) ) as x
          order by entry_timestamp desc  ;

          image 1 - shows the data being deleted

          image 2 - shows the deleted data displayed from the data journal using SQL


          I've underlined the portion of the SQL that transforms the HEX data into readable data.

          Updated on 2014-02-20T21:43:00Z at 2014-02-20T21:43:00Z by ScottForstie
          • tomliotta
            38 Posts

            Re: DisplaY_Journal, how to break out entry data?

            ‏2014-02-21T03:09:36Z  in response to ScottForstie

            I think that those examples illustrate the perceived "problem". The desired solution would replace this:

            select cast( cast(substring(entry_data,3, 10) as varchar(10) for bit data) as varchar(32000) ccsid 37) as entry_data  from table (...

            ...with this:

            select mycolumn1_from_PRODDATA_SALES  from table (...

            The idea is that SQL should simply know how to format the
            mycolumn1_from_PRODDATA_SALES column without needing to code the rest of the presentation description. I can imagine a complex function that might do a passable job for most (or all) columns of any table, but I don't think it should be expected of SQL to do it automatically.


            Updated on 2014-02-21T03:10:37Z at 2014-02-21T03:10:37Z by tomliotta