Re: SQL Prepare question

[Don Brown <DBrown@xxxxxxxxxx>]

Thanks Tim,

Hopefully I will get it tested today and will let you know.

Much appreciated.

Cheers

Don




From:   "Tim Fathers" <tim@xxxxxxxxxxxxx>
To:     "Don Brown" <DBrown@xxxxxxxxxx>
Cc:     "Midrange Systems Technical Discussion" 
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date:   02/11/2021 08:08 AM
Subject:        Re: SQL Prepare question



Hi Don,

this would be my take on it. The only thing missing the exception 
handling, which needs to test the SQLSTT/SQLCOD values and throw an 
appropriate exception which would be caught by the monitor block. I cannot 
test it with the same parameters as you as I'm not authorised to the 
journals on my machine, but hopefully it works.

Tim.

**FREE 

ctl-opt copyright('')
  datfmt(*ISO) datedit(*YMD/) timfmt(*ISO)
  debug(*YES) option(*NODEBUGIO: *SRCSTMT)
  main(main);

dcl-ds data_t qualified template inz;
  entry_timestamp timestamp inz(z'1970-01-01-00.00.00');  // Initialise 
with first start time
  journal_entry_type char(2);
  syslog_facility int(10);
  syslog_severity int(10);
  syslog_event varchar(2048);
end-ds;

//**************************************************************************************************************
// Entry point
//**************************************************************************************************************
dcl-proc main;
  dcl-pi *N;
  end-pi;

  dcl-ds data likeds(data_t) inz;
  dcl-s resetCache like(*IN);

  // dow process <> 'STOP';

    monitor;
      OpenLog(data.entry_timestamp);

      dow ReadLog(data: resetCache);
        // do something...
      enddo;


    on-error;
      resetCache = *ON;

    endmon;

    CloseLog();

  // enddo;

end-proc;

//************************************************************************************
// Open the SQL cursor for the Log data.
//************************************************************************************
dcl-proc OpenLog;
  dcl-pi *N extproc(*dclcase);
    startTime timestamp const;
  end-pi;

  exec sql
    declare LOG_CURSOR cursor for
      select entry_timestamp,
             journal_entry_type,
             coalesce(syslog_facility, 0),
             coalesce(syslog_severity, 0),
             coalesce(syslog_event, '')

      from table(QSYS2.DISPLAY_JOURNAL(
                 'QSYS',
                 'QAUDJRN',
                 GENERATE_SYSLOG => 'RFC5424',
                 STARTING_TIMESTAMP => :startTime
           )) as X

      where syslog_event is not null and
            substr(syslog_event, 105, 3) <> 'Low';

  exec sql open LOG_CURSOR;
  CheckState(SQLCOD: SQLSTT: SQLERM);

end-proc;
//**********************************************************************************
// Read log data...
// NOTE: The cache must be reset after any error during a read or when the 
read
//       loop ended before all rows had been read.
//**********************************************************************************
dcl-proc ReadLog;
  dcl-pi *N like(*IN) extproc(*dclcase);
    data likeds(data_t);
    resetCache like(*IN);
  end-pi;

  dcl-c CACHE_SIZE_ROWS  128;
  dcl-ds dataCache likeds(data_t) dim(CACHE_SIZE_ROWS) static;
  dcl-s i uns(5) static;
  dcl-s rows uns(5) static;

  // Force cache to reset.
  if resetCache;
    resetCache = *OFF;
    rows = 0;
  endif;

  i += 1;

  if i > rows;
    exec sql fetch LOG_CURSOR for 128 rows into :dataCache;   -- ensure 
same as cache size!
    CheckState(SQLCOD: SQLSTT: SQLERM);
    exec sql get diagnostics :rows = row_count;
    if rows = 0;
      return *OFF;
    endif;
    i = 1;
  endif;

  data = dataCache(i);

  return *ON;

end-proc;
//**********************************************************************************
// Close the cursor
//**********************************************************************************
dcl-proc CloseLog;
  dcl-pi *N extproc(*dclcase);
  end-pi;

  exec sql close LOG_CURSOR;
  CheckState(SQLCOD: SQLSTT: SQLERM);

end-proc;
//**********************************************************************************
// Check SQL state - should be externalised in a service program 
somewhere....
//**********************************************************************************
dcl-proc CheckState;
  dcl-pi *N like(*IN) extproc(*dclcase);
    SQLCOD int(10) const;
    SQLSTT char(5) const;
    SQLERM char(70) const;
  end-pi;

  // Check for error and throw an exception if necessary..
  // if ..error
  //  QMHSNDPM(....)
  // endif;

  // Return *OFF when no record read.
  return (SQLSTT <> '02000');

end-proc;
//**********************************************************************************
 


From: Don Brown <DBrown@xxxxxxxxxx>
Sent: 01 November 2021 20:55
To: Tim Fathers <tim@xxxxxxxxxxxxx>
Cc: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL Prepare question 
 
Thanks Tim, 

Charles also suggested getting multiple records. 

If you would like to share a working example that would be appreciated. 

This is the SQL statement fyi 

QAUDStmt =   'SELECT journal_entry_type, + 
                     syslog_facility,    + 
                     syslog_severity,    + 
                     syslog_event        + 
              FROM TABLE + 
              (QSYS2.DISPLAY_JOURNAL(''QSYS'',''QAUDJRN'', + 
              GENERATE_SYSLOG =>  +                           
                 ''RFC5424'', +                               
                 STARTING_TIMESTAMP => ?  +                   
                 )) AS X + 
              WHERE syslog_event IS NOT NULL  +               
                 and substr(syslog_event,105, 3) <> ''Low'''; 


Thank you 

Don 





From:        "Tim Fathers" <tim@xxxxxxxxxxxxx> 
To:        "Midrange Systems Technical Discussion" 
<midrange-l@xxxxxxxxxxxxxxxxxx> 
Cc:        "Don Brown" <DBrown@xxxxxxxxxx> 
Date:        02/11/2021 06:42 AM 
Subject:        Re: SQL Prepare question 



Hi Don, 

The SQL isn't included, but I would guess the prepare isn't necessary at 
all and the select statement could just be added as part of the DECLARE 
CURSOR with host variable(s), which would stay inside the loop, that way 
you get syntax checking too. I would also put the messy SQL bits in 
procedures, such as Open(...), Read(...) and Close(...), which I think 
makes it look more readable: 
  dow process <> 'STOP'; 
 
    OpenLog(startTime);
 
    dow ReadLog(desc);
      // do something...
    enddo;
 
    CloseLog();
  enddo; 

A general-purpose SQL error checker procedure is also handy, which can be 
called after each SQL statement. Below illustrates the structure I usually 
use. Also, consider fetching a set of rows on each SQL fetch, this will 
speed up the process considerably. I usually hide this implementation 
inside the Read(...) procedure, such that it reads a set of rows and 
passes one back on each call until that set is exhausted and it reads 
another set. I can post an example of that if it would help. 

Tim. 

**FREE 

ctl-opt copyright('') 
  datfmt(*ISO) datedit(*YMD/) timfmt(*ISO) 
  debug(*YES) option(*NODEBUGIO: *SRCSTMT) 
  main(main); 

dcl-ds data_t qualified template inz; 
  //..log fields... 
end-ds 

//************************************************************************************************************** 

// Entry point 
//************************************************************************************************************** 

dcl-proc main; 
  dcl-pi *N; 
  end-pi; 

  dcl-s startTime packed(8: 0); 
  dcl-ds data likeds(data_t) inz; 
  
  dow process <> 'STOP'; 
  
    OpenLog(startTime); 
  
    dow ReadLog(desc); 
      // do something... 
    enddo; 
  
    CloseLog(); 
  enddo; 

end-proc; 

//************************************************************************************ 

// Open the SQL cursor for the Log data. 
//************************************************************************************ 

dcl-proc OpenLog; 
  dcl-pi *N extproc(*dclcase); 
    startTime packed(8: 0) const; 
  end-pi; 

  exec sql 
    declare LOG_CURSOR cursor for 

    select -- ...log data 

    from LOG_FILE... 

    where LOG_TIME >= startTime; 

  exec sql open LOG_CURSOR; 
  CheckState(SQLCOD: SQLSTT: SQLERM); 

end-proc; 
//********************************************************************************** 

// Read log data... 
//********************************************************************************** 

dcl-proc ReadLog; 
  dcl-pi *N like(*IN) extproc(*dclcase); 
    data likeds(data_t); 
  end-pi; 

  exec sql fetch LOG_CURSOR into :data; 
  
  return CheckState(SQLCOD: SQLSTT: SQLERM); 

end-proc; 
//********************************************************************************** 

// Close the cursor 
//********************************************************************************** 

dcl-proc CloseLog; 
  dcl-pi *N extproc(*dclcase); 
  end-pi; 

  exec sql close LOG_CURSOR; 
  CheckState(SQLCOD: SQLSTT: SQLERM); 

end-proc; 
//********************************************************************************** 

// Check SQL state 
//********************************************************************************** 

dcl-proc CheckState; 
  dcl-pi *N like(*IN) extproc(*dclcase); 
    SQLCOD int(10) const; 
    SQLSTT char(5) const; 
    SQLERM char(70) const; 
  end-pi; 

  // Check for error and throw an exception if necessary.. 
  // if ..error 
  //  QMHSNDPM(....) 
  // endif; 
  
  // Return *OFF when no record read. 
  return (SQLSTT <> '02000'); 

end-proc; 
//********************************************************************************** 

  
  



From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Don 
Brown via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Sent: 01 November 2021 20:16
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <DBrown@xxxxxxxxxx>
Subject: RE: SQL Prepare question 
  
Thanks Rob,

I am reading the audit journal - don't think I can attach a trigger ?

Yes I have a properly prepared SLQ statement in SQLStmt.

So you are saying I can omit the Prepare and Declare in the loop - only 
need them once ?

Thanks

Don





From:   "Rob Berendt" <rob@xxxxxxxxx>
To:     "Midrange Systems Technical Discussion" 
<midrange-l@xxxxxxxxxxxxxxxxxx>
Date:   01/11/2021 09:17 PM
Subject:        RE: SQL Prepare question
Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx>



Since you're using "open using" you probably have a properly prepared sql 
statement.  With that in mind you probably do not need your prepare or 
declare statements within your loop.

But from a higher level I would question why not a trigger or some other 
technique.

Rob Berendt