Tools_RPG

*************** Beginning of data ***************************************************************************************
      * SQL Performace Recorder
*************************************************************************************************************************
 ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
--CRTLIB LIB(XTESTL) TEXT('test lib for SQL perf data')
--CRTSRCPF FILE(XTESTL/QDDSSRC) RCDLEN(112) TEXT('PFSRC for RPG sources')
--Save XTESTF as PFSQL member type with the following SRCDTA
CREATE TABLE XTESTL/XTESTF (
  TimeInX TIMESTAMP,
  TimeOutX TIMESTAMP,
  SrcDtaX CHAR(2000));
--Use following command to create the file object in *CURLIB
--CHGCURLIB CURLIB(XTESTL)
--RUNSQLSTM SRCFILE(XTESTL/QDDSSRC) SRCMBR(XTESTF)
.....A*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
or use native PF DDS as follows...
.....A*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
     A* DDS to be copied and compiled as a PF with name as XTESTF
     A* CRTLIB LIB(XTESTL) TEXT('test lib for SQL perf data')
     A          R XTESTFA
     A            TimeInX         Z
     A            TimeOutX        Z
     A            SrcDtaX     2000 
 ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
     D TimeInZ         S               Z
dcl-s TimeInZ         timestamp;

TimeInZ = %TIMESTAMP;
exec sql INSERT INTO XTESTL/XTESTF (TimeInX, SrcDtaX)
         VALUES(:TimeInZ,'Description or Comment');

//prc();

exec sql UPDATE XTESTL/XTESTF SET TimeOutX = CURRENT_TIMESTAMP
         WHERE TimeInX = :TimeInZ;
 ...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
SELECT ROUND(SUM(TimeOutX-TimeInX),2),
  SUM (TimeOutX-TimeInX) AS Diff_in_Microsecs, 
  SUBSTR(SrcDtaX,1,50) AS Data
FROM XTESTL/XTESTF
GROUP BY TimeInX, SrcDtaX ORDER BY Diff_in_Microsecs DESC
****************** End of data ******************************************************************************************