Problem solve Get help with specific problems with your technologies, process and projects.

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 last published in May 2005

Dig Deeper on iSeries programming commands

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

  • How do I size a UPS unit?

    Your data center UPS sizing needs are dependent on a variety of factors. Develop configurations and determine the estimated UPS ...

  • How to enhance FTP server security

    If you still use FTP servers in your organization, use IP address whitelists, login restrictions and data encryption -- and just ...

  • 3 ways to approach cloud bursting

    With different cloud bursting techniques and tools from Amazon, Zerto, VMware and Oracle, admins can bolster cloud connections ...

Close