Announcement

**TedHolt** · March 7, 2017, 11:06 AM

Hi, Scott.

Welcome to these fora.

I thought there might be a service that gives journaling information, but I didn't find one on IBM's web site.

I also thought that journal information might be in one of the system catalog files. Maybe it is, but I didn't find it there either.

One way that does work is to use the Display File Description command to load a temporary file, which you can query with SQL or anything else.

Code:

DSPFD FILE(MYLIB/*ALL)  TYPE(*ATR) OUTPUT(*OUTFILE)  FILEATR(*PF) OUTFILE(QTEMP/FD)

**tomliotta** · March 10, 2017, 11:20 PM

It took some time to dig out, but I have some SQL that comes close. With a little spare time, I could make it more specific. I'll post the bits that I have, and you can see how it might be tweaked to fit exactly.

Essentially what I had was a REXX proc that took a qualified file name as input and displayed any journal entries that it found for that file. The first thing that it had to do was call the Retrieve Database File Description (QDBRTVFD) API in order to determine if the file was journaled and, if it was, to extract the journal name from the returned structure. Once the journal was determined, the file name could be used to retrieve journal entries. SQL was then used to display the entries (with a little trickery).

That proc worked well enough. And a few years later I was looking to update it when moving it to an i 6.1 system. About the same time, I was also looking to experiment with some system APIs in SQL. I remembered that REXX proc and started looking at how to replace the API call with a SQL stored proc call.

Bear in mind that this code was only taken far enough to get it working for my own example. It's not intended to be the best way to code it up.

First thing I did was create this external SQL stored proc to wrap the API:

Code:

CREATE PROCEDURE SQLEXAMPLE.DBRTVFD ( 
    INOUT FD CHAR(1024) , 
    IN SZFD INTEGER , 
    INOUT RTNFD CHAR(20) , 
    IN FORMAT CHAR(8) , 
    IN QF CHAR(20) , 
    IN "RCDFMT" CHAR(10) , 
    IN OVRPRC CHAR(1) , 
    IN SYSTEM CHAR(10) , 
    IN FMTTYP CHAR(10) , 
    IN ERRCOD CHAR(8) ) 
    LANGUAGE CL 
    SPECIFIC SQLEXAMPLE.DBRTVFD 
    NOT DETERMINISTIC 
    NO SQL 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'QSYS/QDBRTVFD' 
    PARAMETER STYLE GENERAL ;

Once that exists, a SQL CALL SQLEXAMPLE.DBRTVFD () results in a call of the API, assuming parameters are properly supplied. Of course, the parm values are a bit of a problem, so I worked on another wrapper stored proc to see how those might be handled. This is where I left it:

Code:

SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","MyLib" ; 

CREATE PROCEDURE SQLEXAMPLE.GENDBRTVFD ( 
    INOUT FD VARCHAR(1024) , 
    IN SZFD INTEGER , 
    INOUT RTNFD VARCHAR(20) , 
    IN FORMAT VARCHAR(8) , 
    IN QF VARCHAR(20) , 
    IN "RCDFMT" VARCHAR(10) , 
    IN OVRPRC VARCHAR(1) , 
    IN SYSTEM VARCHAR(10) , 
    IN FMTTYP VARCHAR(10) , 
    IN ERRCOD VARCHAR(8) ) 
    LANGUAGE SQL 
    SPECIFIC SQLEXAMPLE.GENDBRTVFD 
    NOT DETERMINISTIC 
    MODIFIES SQL DATA 
    CALLED ON NULL INPUT 
    SET OPTION  ALWBLK = *ALLREAD , 
    ALWCPYDTA = *OPTIMIZE , 
    COMMIT = *NONE , 
    DBGVIEW = *LIST , 
    CLOSQLCSR = *ENDMOD , 
    DECRESULT = (31, 31, 00) , 
    DFTRDBCOL = *NONE , 
    DLYPRP = *NO , 
    DYNDFTCOL = *NO , 
    DYNUSRPRF = *USER , 
    RDBCNNMTH = *RUW , 
    SRTSEQ = *HEX   
    P1 : BEGIN 
DECLARE FDI CHAR ( 1024 ) ; 
DECLARE RTNFDI CHAR ( 20 ) ; 
DECLARE FORMATI CHAR ( 8 ) ; 
DECLARE QFI CHAR ( 20 ) ; 
DECLARE RCDFMTI CHAR ( 10 ) ; 
DECLARE OVRPRCI CHAR ( 1 ) ; 
DECLARE SYSTEMI CHAR ( 10 ) ; 
DECLARE FMTTYPI CHAR ( 10 ) ; 
DECLARE ERRCODI CHAR ( 8 ) ; 
DECLARE STKCMD CHAR ( 10 ) ; 

SET FDI = X'00000000' ; 
SET RTNFDI = ' ' ; 
SET FORMATI = FORMAT ; 
SET QFI = QF ; 
SET RCDFMTI = "RCDFMT" ; 
SET OVRPRCI = OVRPRC ; 
SET SYSTEMI = SYSTEM ; 
SET FMTTYPI = FMTTYP ; 
SET ERRCODI = X'0000000000000000' ; 
SET STKCMD = '*LOG' ; 

CALL SQLEXAMPLE . DBRTVFD ( FDI , SZFD , RTNFDI , FORMATI , QFI , RCDFMTI , OVRPRCI , SYSTEMI , FMTTYPI , ERRCODI ) ; 

SET FD = FDI ; 
SET RTNFD = RTNFDI ; 
SET ERRCOD = ERRCODI ; 

END P1  ;

This proc accepts its parm values and does a little minor manipulation in order to call SQLEXAMPLE.DBRTVFD reliably and to return the resulting structure in the FD INOUT parameter.

That's all that it does because that's all that I wanted. For you, you'd possibly want to extract the qualified journal name and return only that. I didn't know what I might want to do, so I returned the entire structure back out to my REXX proc.

This might not be useful to you, but I'll include the REXX code that calls the stored proc and extracts the journal name from the returned structure:

Code:

     rtnFLib = Copies('00'x, 20)
     errCod  = Copies('00'x,  8)
     format  = 'FILD0100'
     rcdFmt  = '*FIRST    '
     ovrPrc  = '0'
     system  = '*LCL      '
     fmtTyp  = '*EXT      '

     storProc    = 'call SQLEXAMPLE/GenDBRTVFD( ',
                         '?, 1024, ? , ? , ? , ? , ? , ? , ? , ? )'

     errLocn = 'Prepare'
     address '*EXECSQL' execsql ,
            'PREPARE S1 FROM :storProc'
     if SQLCODE < 0 then
       signal ERROR

     errLocn = 'Execute'
     address '*EXECSQL' execsql ,
            'EXECUTE S1 USING :fd,',
                             ':rtnFLib, ',
                             ':format, ',
                             ':qfile, ',
                             ':rcdFmt, ',
                             ':ovrPrc, ',
                             ':system, ',
                             ':fmtTyp, ',
                             ':errCod '

     if SQLCODE < 0 then
       signal ERROR

/* 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.                  */
     say c2x( fd )
     say fd
     say rtnFLib
     say c2x( errCod )
     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 )
     say 'Jrn' jrn

It took a little extra work because REXX doesn't handle binary integer values extracted out of a character structure particularly well. The part of the structure that holds the offset is extracted as a 4-char substring, and that's converted to a hex value, and the hex converted to decimal with this line:

Code:

ofsjrnInf = x2d( c2x( substr( fd , 379, 4 ) ) )

Using the offset, the journal and journal library names are extracted by substrings. Ideally, you'd do that directly in your version of the SQL stored proc. Those would be the values that you returned rather than returning the entire structure. Actually, you might create a SQL function rather than a stored proc. I guess it depends on what you eventually want to do.

I'm not sure how much time I can add to this. Post back here if this is helpful at all and if you need more detail or real help with making progress.

**sstarkey** · March 16, 2017, 11:32 PM

This was all very helpful.

Thank you!

Scott

Announcement

Using SQL to determine which files are being journaled

Using SQL to determine which files are being journaled

Comment

Comment

Comment