ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Efficient logging via SQL insert in SQLRPGLE?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Efficient logging via SQL insert in SQLRPGLE?

    So I'm writing a program that needs to log pretty much everything it does. So whenever necessary I do an SQL insert to write some text to a log file. And there are a lot of logs to write per call - hundreds sometimes.

    All those inserts will I'm sure affect performance a bit, and I would like to optimise it.

    Now I could write the logs to an array DS and then insert them in bulk with "exec sql insert into log file :logcount rows values(:logDs)". But that means if the program crashed with a MSGW and then was cancelled, logs in the DS pending write may not get written. I could put everything in a monitor so it does not MSGW, but if a fatal error occurs I would want the job to stay in MSGW on the failed statement.

    Any suggestions?

  • #2
    Check the ON-EXIT op code to see if would meet your needs.

    Comment


    • #3
      Why not let the system log using journaling?

      Comment


      • #4
        I looked at ON_EXIT but it doesn't fire if the job goes into MSGW status and the operator takes option C to cancel the job.

        I'm logging a lot more than simple file IO, so journals are not enough.

        Comment


        • #5
          I would do some performance testing first to see if it really is an issue, you might be looking to optimize something that isn't a problem. If it is you might be able to write the logging to a data queue and then have a program sitting on the queue that will do the actual saving of the log records. I am guessing that the data queue would be faster but of course do performance testing.

          Comment


          • #6
            Stupid question #123,234. If you need to log everything like SQL Insert - why are you doing that manually? The system has a way of doing that using Journals and it does it for you without fail each and every time.

            Why not use journaling?

            Comment


            • #7
              Sorry - I forgot I asked that question already....

              Comment


              • #8
                Is it something where you're working on a particular item - any or all of which can generate a # of logs? You could put those in an array and write them in bulk before processing the next item.

                You could put the log file into a memory pool and insert into that file...

                My guess is that in reality you're not likely to really see a big performance hit as it is. It would be an interesting time the program without writing out the log entries and then time it with the log entries being written.

                Comment


                • #9
                  You could call QMHSNDSM (send scope message) to cause a "scope handling program" to get called when your program ends.

                  Here's a little proof-of-concept example. The first program has an array it partially fills up, and it passes a pointer to the array to the QMHSNDSM API which causes that pointer to be passed to the scope-handling program when it gets called. The scope-handling program displays the array.

                  Sorry, there are no comments ... I just modified some programs I had written a while ago when I found out about QMHSNDSM.

                  QGPL/SCOPMSG1: The program that calls QMHSNDSM. Call this program and reply to the DSPLY with either 0 or 1. If 0, it will crash with an inquiry message. After the inquiry message, the scope program will get called. If 1, it will end normally and the scope program will get called.
                  Code:
                        dcl-pr qmhsndsm extpgm;
                          scope_type char(10) const;
                          scope_pgm likeds(qualname) const;
                          scope_data likeds(scope_data) const;
                          scope_data_len int(10) const;
                          message_key char(4);
                          errcode likeds(errcode);
                        end-pr;
                  
                        dcl-ds scope_data qualified;
                           data_ptr pointer inz(%addr(arr_data));
                        end-ds;
                  
                        dcl-ds arr_data qualified;
                           arr char(5) dim(10);
                           arr_elems int(10) inz(0);
                        end-ds;
                  
                        dcl-ds qualname qualified;
                           obj char(10) inz('SCOPMSG2');
                           lib char(10) inz('QGPL');
                        end-ds;
                  
                        dcl-ds errcode qualified inz;
                           bytes_provided int(10);
                           bytes_available int(10);
                        end-ds;
                  
                        dcl-s msg_key char(4);
                  
                        dcl-s x int(10);
                        dcl-s div int(10);
                  
                        qmhsndsm ('*PGM'     // call scope-handler when this program ends
                                : qualname   // the scope-handling program
                                : scope_data // data to pass to the scope-handler
                                : %size(scope_data)
                                : msg_key
                                : errcode);
                  
                        arr_data.arr(1) = 'one';
                        arr_data.arr(2) = 'two';
                        arr_data.arr(3) = 'three';
                        arr_data.arr_elems = 3;
                  
                        dsply 'value to divide by, 0 to get an error' '' div;
                        x = 1 / div;
                  
                        *inlr = '1';
                  QGPL/SCOPMSG2, the scope handling program
                  Code:
                        dcl-pi *n;
                           parm likeds(scope_data_t);
                        end-pi;
                  
                        dcl-ds scope_data_t qualified template;
                           data_ptr pointer;
                        end-ds;
                        dcl-ds scope_data qualified based(pScope_data);
                           arr char(5) dim(10);
                           arr_elems int(10);
                        end-ds;
                        dcl-s i int(10);
                  
                        pScope_data = parm.data_ptr;
                        dsply ('scope pgm, num elems ' + %char(scope_data.arr_elems));
                        for i = 1 to scope_data.arr_elems;
                           dsply ('   ' + scope_data.arr(i));
                        endfor;
                  
                        return;

                  Comment


                  • #10
                    Lots of good suggestions from people, I will be exploring them.

                    Barbara - I had never heard of scope handling programs. I will look at that in more detail.

                    Thanks all

                    Comment


                    • #11
                      My system doesn't seem to have qmhsndsm - I'm at IBM i 7.2. What am I missing?

                      Comment


                      • #12
                        Rocky, did you try wrkobj *all/qmhsndsm ?

                        Walt

                        Comment


                        • #13
                          Sorry - a typo on my part...

                          Comment


                          • #14
                            Another possibility perhaps???? is https://www.mcpressonline.com/progra...ion-in-servers

                            Comment

                            Working...
                            X