Search journals effectively

Often we look at journals to determine what happened in the data. The following commands make this easier to deal with.

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

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close