ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Changing a trigger program

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

  • Changing a trigger program

    I know the answer is in this forum... but I can't find it.

    I have a trigger program attached to a transaction file in our system. This file is in use virtually all of the time. I need to add some logic to the trigger program.

    This same thing came up about 2 years ago - at that time someone suggested using two programs... The first pgm would be the PF trigger and would merely pass the "trigger buffer info" to another program - effectively isolating the logic from the PF Trigger.

    At the time, I didn't want to "break" anything.... still feel that way.

    So what is the best way to handle this? Make the changes, then compile the program in the wee hours of the morning, or...

    thx,
    Greg

  • #2
    If you're on a new enough OS, there's a new capability:

    Comment


    • wegrace
      wegrace commented
      Editing a comment
      Thank you for this information! It is most useful.

  • #3
    That sounds exactly like what I need to do... But (like many IBM documents) I don't understand it. I'm running v7r3, so I'm sure it's valid... I just don't know what steps to take to "enable" this little gem.

    Comment


    • #4
      Look for an ibm table called qaqqini; it usually exists in qsys. No modifications should be made to the version in qsys.

      A copy of qaqqini should be made into qusrsys, or a library of your choice.

      Then edit the copied table and look for the ALLOW_DDL_WHILE_OPEN entry; add the entry if it is not found.

      Change qaqqini.qqval to *YES.

      Walt

      Comment


      • #5
        Originally posted by wegrace View Post
        Look for an ibm table called qaqqini; it usually exists in qsys. No modifications should be made to the version in qsys.

        A copy of qaqqini should be made into qusrsys, or a library of your choice.

        Then edit the copied table and look for the ALLOW_DDL_WHILE_OPEN entry; add the entry if it is not found.

        Change qaqqini.qqval to *YES.

        Walt
        Not what I was expecting, but... OK
        So is that change immediate? Or do still need to RMVPFTRG/ADDPFTRG or CHGPFTRG.

        I've opened a PMR with IBM to see what they say. But I also found this article. Looks like you can override QAQQINI and change it just for your specific job?

        Comment


        • #6
          If I'm reading it correctly there's a chance that the trigger will not run during some of this process. This may or may not be acceptable.

          Comment


          • #7
            First question, are you talking about System Trigger (written in an HLL Language such as RPG) or SQL Triggers (written in pure SQL)?

            If you are working with System Triggers, your (registered) Trigger Programm does nothing else then calling an other (regular) program and pass the trigger buffers (i.e. the old/new record data structures) as parameters. The called program is where you implement your program logic. If something changes you easily can modify the called program's source code and recompile your program.

            If you are working with SQL Triggers, the QAQQINI Option ALLOW_DDL_WHILE_OPEN must be set to *YES. This modified QAQQINI must be used in all environmnents, i.e. the QAQQINI must be copied (CRTDUPOBJ) and the ALLOW_DDL_WHILE_OPEN option must be changed in the copied QAQQINI File. If the QAQQINI file is copied into the QUSRSYS, this modified QAQQINI should be used in all jobs, because QUSRSYS is in the System Part of the library list. If the QAQQINI File is copied into any other library, you have to set it by executing the CHGQRYA command (CHGQRYA QRYOPTLIB(YOURLIB)). If all jobs use the modified QAQQINI, you will be able to change and replace the SQL Trigger Program. After you changed the SQL Trigger Program, the old version continues to run in opened cursors until the get closed. When reopening the new version of the trigger program is activated.

            Birgitta

            Comment


            • #8
              Originally posted by Rocky View Post
              If I'm reading it correctly there's a chance that the trigger will not run during some of this process. This may or may not be acceptable.
              I'm not entirely sure what the process is

              Comment


              • #9
                Originally posted by B.Hauser View Post
                First question, are you talking about System Trigger (written in an HLL Language such as RPG) or SQL Triggers (written in pure SQL)?

                If you are working with System Triggers, your (registered) Trigger Programm does nothing else then calling an other (regular) program and pass the trigger buffers (i.e. the old/new record data structures) as parameters. The called program is where you implement your program logic. If something changes you easily can modify the called program's source code and recompile your program.

                If you are working with SQL Triggers, the QAQQINI Option ALLOW_DDL_WHILE_OPEN must be set to *YES. This modified QAQQINI must be used in all environmnents, i.e. the QAQQINI must be copied (CRTDUPOBJ) and the ALLOW_DDL_WHILE_OPEN option must be changed in the copied QAQQINI File. If the QAQQINI file is copied into the QUSRSYS, this modified QAQQINI should be used in all jobs, because QUSRSYS is in the System Part of the library list. If the QAQQINI File is copied into any other library, you have to set it by executing the CHGQRYA command (CHGQRYA QRYOPTLIB(YOURLIB)). If all jobs use the modified QAQQINI, you will be able to change and replace the SQL Trigger Program. After you changed the SQL Trigger Program, the old version continues to run in opened cursors until the get closed. When reopening the new version of the trigger program is activated.

                Birgitta
                What I have is an SQLRPGLE trigger program - a single program HH110 containing the trigger buffers and the program logic for AFTER INSERT.
                I don't know what you mean by "registered". However, I cannot compile my called program without having exclusive access to the file.
                Code:
                CPF2143
                Message . . . . :   Cannot allocate object HH110 in ASTHHOBJ type *PGM.      
                Cause . . . . . :   Existing object HH110 in library ASTHHOBJ type *PGM is in
                  use by another job.  Object HH110 in library ASTHHOBJ type *PGM is the    
                  object being replaced by the create function currently being performed.    
                  Recovery  . . . :   Try the create function again when object HH110 in    
                  library ASTHHOBJ type *PGM becomes available.
                This is from IBM:

                Development confirmed that is correct. With ALLOW_DDL_CHANGES_WHILE_OPEN value *YES, only the following commands will work while the file is in use:
                ? SQL CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER, COMMENT ON TRIGGER, and LABEL ON TRIGGER statements
                ? CL ADDPFTRG, RMVPFTRG, and CHGPFTRG commands

                So you have to either remove the trigger using the parameter, recompile the program and then add the trigger or you can create a copy of the program, recompile it and then remove the old program and add the new one .


                Last edited by gwilburn; August 7, 2018, 06:51 AM.

                Comment


                • #10
                  We have a couple of trigger programs that are CL driven. In this case, MyTrgPgm is an RPG program using the following parameters:

                  PGM PARM(&PARM1 &PARM2)

                  DCL VAR(&PARM1) TYPE(*CHAR) LEN(3000)
                  DCL VAR(&PARM2) TYPE(*CHAR) LEN(10)

                  CALL PGM(MyTrgPgm) PARM(&PARM1 &PARM2)



                  Code:
                  *Entry        Plist                        
                                Parm                    Parm1
                                Parm                    Parm2
                  
                  D Parm1           DS          3000    
                  D  Fname                        10A   
                  D  Lname                        10A   
                  D  Mname                        10A   
                  D  Teven                         1A   
                  D  Ttime                         1A   
                  D  Cmtlck                        1A   
                  D                                3A   
                  D  CCSID                         9B 0
                  D  RRN                           9B 0
                  D                                9B 0
                  D  OldOff                        9B 0
                  D  OldLen                        9B 0
                  D  OldNOff                       9B 0
                  D  OldNLen                       9B 0
                  D  NewOff                        9B 0
                  D  NewOff                        9B 0  
                  D  NewLen                        9B 0  
                  D  NewNOff                       9B 0  
                  D  NewNLen                       9B 0  
                  D                               16A    
                  D  RecdArea                   2900A    
                  
                   //---->  Parm 2                       
                  D Parm2           S              9B 0
                  Whether this is "efficient" or not is debatable and has more to do with your application design, frequency of activity, etc.
                  but it does allow for the recompilation of an RPG trigger program without requiring exclusive control.

                  Comment


                  • #11
                    Terry... so this is what was suggested to me before. I think it was Scott K. who suggested I use an RPG pgm to simply pass the buffers into a second pgm that contained the logic. But the result is the same.

                    Comment


                    • #12
                      Yes...result should be the same...it just uses two different languages.

                      Another similar method we use is to attach an HLL program as the trigger to the database and it does a SBMJOB to a designated queue where the second program does all of the work. This works well for EDI-type stuff coming in from outside sources...

                      Comment


                      • #13
                        Given all this info... here is what I am going to try
                        I've created a new RPG program called HH110TRG that simply takes the trigger buffer and length and calls the original trigger pgm HH110. I've compiled it in the same library.

                        HH110TRG
                        Code:
                               ctl-opt
                               option(*srcstmt: *nodebugio)
                               dftactgrp(*no) actgrp(*caller);
                        
                               dcl-pi *n;
                                 Trgbuffer           likeds(trginfo);
                                 Trgbufferlen        int(10);
                               end-pi;
                        
                              *-------------------- Trigger buffer information ---------------------
                               dcl-ds Trginfo;
                                 File                char(10);
                                 Library             char(10);
                                 Member              char(10);
                                 Event               char(1);
                                 Time                char(1);
                                 Commitlocklev       char(1);
                                 *n                  char(3);
                                 ccsid               int(10);
                                 rrn                 int(10);
                                 *n                  char(4);
                                 Befrecoffset        int(10);
                                 Befreclen           int(10);
                                 Befnulloffset       int(10);
                                 Befnulllen          int(10);
                                 Aftrecoffset        int(10);
                                 Aftreclen           int(10);
                                 Aftnulloffset       int(10);
                                 Aftnulllen          int(10);
                               end-ds;
                        
                              *-------------------------- Prototypes -------------------------------
                        
                               dcl-pr hh110          extpgm('HH110');
                                 Trgbuffer           likeds(trginfo);    // Trgbuffer
                                 Trgbufferlen        int(10);            // Trgbufferlen
                               end-pr;
                        
                              *---------------------------------------------------------------------
                        
                               hh110(Trgbuffer:Trgbufferlen);
                               *Inlr = *On;
                               Return;
                        If I remove the existing trigger pgm HH110, then add the new trigger HH110TRG (above), I should be able to change the logic in HH110 as needed.

                        Obviously I will need to make this change when the file is not being updated... Any other thoughts or recommendations would be appreciated (and thanks for your help thus far)

                        Comment

                        Working...
                        X