Search journals effectively

I often have to look at journals to determine what happened in the data. The following commands make this easier to deal with. I'll also include the source for the user-defined function that I use.

From OS/400: 

       DSPJRN JRN(prodLib/QSQJRN) 
       FILE((prodLib/MyFile MyFile)) 
       RCVRNG(*CURCHAIN) 
       FROMTIME(050405 0700) 
       TOTIME(050405 0830) 
       JRNCDE((R)) 
       ENTTYP(PT UP UR PX DL) 
       OUTPUT(*OUTFILE) 
       OUTFILFMT(*TYPE3) 
       OUTFILE(myLib/DSPJRNOUT)

From SQL: 

 
   select 
  date(mgj1.jotstp) as entrydate, 
  time(mgj1.jotstp) as entrytime, 
  mgj1.jouspf as userprofile, 
  mgj1.jojob as jobname, 
  mgj1.jouser as jobuser, 
  mgj1.jonbr as jobnumber, 
  case mgj1.joentt 
    when 'UB' then 'Update' 
    when 'PT' then 'Insert' 
    else mgj1.joentt 
  end as transactiontype, 
  fn_binarytointeger(char(substr(mgj1.joesd, 1, 4))) as araccountid 
from 
  DSPJRNOUT mgj1 
order by entrydate, 
  entrytime 
/*---------------------------------------------------------*/ 
/* Procedure  : Fn_BinaryToInteger                         */ 
/* Type       : SQLC                                       */ 
/* Date       : 06/17/02                                   */ 
/* ----------------- Compile Definitions ----------------- */ 
/* $$DEFS 
$$SQLDROP 
  DROP FUNCTION &CURRENTLIB/Fn_BinaryToInteger; 
$$SQLCREATE 
  CREATE FUNCTION &CURRENTLIB/Fn_BinaryToInteger 
  ( 
    v_binaryString  CHAR(4) 
  ) 
  RETURNS INT 
  LANGUAGE C 
  EXTERNAL NAME '&CURRENTLIB/F_BIN2INT(Fn_BinaryToInteger)' 
  NOT DETERMINISTIC 
  NO EXTERNAL ACTION 
  PARAMETER STYLE SQL 
  ALLOW PARALLEL; 
$$ENDDEFS 
/* --------------------END DEFINITIONS ------------------- */ 
#include  
#include  
#include  
#include  
#include  
#include  
EXEC SQL 
INCLUDE SQLCA; 
/*---------------------------------------------------------*/ 
/* Main                                                    */ 
/*---------------------------------------------------------*/ 
void Fn_BinaryToInteger( 
       long *pv_binary, 
       long *pv_integer, 
       short *pnv_binary, 
       short *pnv_integer) 
{ 
  *pv_integer = *pv_binary; 
  *pnv_integer = *pnv_binary; 
}

Instructions on how to create the function: Fn_BinaryToInteger 

 
CRTSQLCI OBJ(myLib/F_BIN2INT) SRCFILE(myLib/QCSRC) OBJTYPE(*MODULE) 
CRTSRVPGM SRVPGM(myLib/F_BIN2INT) EXPORT(*ALL) ACTGRP(*CALLER) 
The SQL statement to declare the function is in the source: 
  CREATE FUNCTION myLib/Fn_BinaryToInteger 
  ( 
    v_binaryString  CHAR(4) 
  ) 
  RETURNS INT 
  LANGUAGE C 
  EXTERNAL NAME ' myLib/F_BIN2INT(Fn_BinaryToInteger)' 
  NOT DETERMINISTIC 
  NO EXTERNAL ACTION 
  PARAMETER STYLE SQL 
  ALLOW PARALLEL;

This was first published in May 2005

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top