Trigger errors

136290 pts.
Trigger failure
Why is my trigger program failing? It's a CL program and it appears it is not even getting to the early part of the program where I have a halt to check job attributes. I created a physical file on our iSeries and added a trigger using the following command.


If you try adding data to the file using a DFU you get this

Message . . . . :   EXIT DFU and SIGNOFF IMMEDIATELY. To allow data base roll back of data. CPF502B caused the problem.                                  Cause . . . . . :   A Referential Constraint Violation of Trigger program      failure occurred during a PUT by DFU to the data base. Data changes will be rolled back once SIGNOFF from the current job is done.  DO NOT CONTINUE to enter or change data as it will be rolled back as well once you signoff.  


When we try adding data via an SQL or web application,  we are getting error messages saying we need to turn journaling on.. We currently don't use journaling so why is it looking for it now?

The PF looks like this

A                                      UNIQUE                     A          R RF$WCH                                               A            WCHTIK        10S 0       TEXT('WEB TICKET')         A            WCHDIV         2A         TEXT('DIVISION')           A            WCHWH          2A         TEXT('WAREHOUSE')          A            WCHORD#        8S 0       TEXT('ORDER #')            A            WCHSTAT       15A         TEXT('STATUS')             A            WCHTYPE        4A         TEXT('LIVE/TEST')          A            WCHIDTE         L         TEXT('IN DATE')            A            WCHITIME        T         TEXT('IN TIME')            A            WCHITMST        Z         TEXT('IN TIMESTAMP')       A            WCHPDTE         L         TEXT('PROCESS DATE')       A            WCHPTIME        T         TEXT('PROCESS TIME')       A            WCHPTMST        Z         TEXT('PROCESS TIMESTAMP')  A            WCHWRK1        1A         TEXT('WORK FLG1')          A            WCHWRK2        1A         TEXT('WORK FLG2')          A            WCHWRK3        1A         TEXT('WORK FLG3')          A            WCHPGMV        5S 2       TEXT('PROGRAM VER.')                                                                         A          K WCHTIK    

Software/Hardware used:
V5R4M5 on an iSeries

Answer Wiki

Thanks. We'll let you know when a new response is added.

Message CPF502B tells you to review related joblog messages to help determine the cause. With nothing else to go on, it appears as if your trigger program failed. If you need help with why it failed, we’ll need to see the relevant messages and possibly relevant sections of your trigger program code.

Most likely you now need journaling because of the failure. If your trigger doesn’t fail, then journaling can probably be dropped (though it shouldn’t be.) A trigger program is considered to be part of the database, not part of any of your applications. When the database fails, you need proper commitment control to clean things up.

The file is in a kind of indeterminate state until the failure is cleared — that’s essentially what the system is telling you. You need effectively to rollback the transaction. Ending your job gets that done.

BTW, I don’t get the part of your question that says “where I have a halt”. You shouldn’t be interrupting a trigger program. If you need to check attributes, just execute DSPJOB *PRINT and continue running. Actually, it’d probably be better to call QUSRJOBI or similar API to retrieve what’s needed and store the info into a user space. Or use SNDPGMMSG to log info to the joblog. Anything but trying to stop execution while a transaction is being handled.


Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Here's a little more info. We created a PF, and added a trigger using ADDPFTRG FILE(EXTRANET/WCTLH) TRGTIME(*AFTER) TRGEVENT(*INSERT) PGM(EXTRANET/WXTRAOE) TRGLIB(EXTRANET) We could not add data via a DFU to fire the trigger, we got errors. If we tried to do an INSERT via SQL, more errors. Is journaling required on files with a trigger ? Our trigger program is a CL. I don't know if that is our problem. We have triggers only on other RPGLE's with no issues. The reason for it being a CL program is in the trigger file, we are passing a field for a test/live order. Here's the CL code, the *HALT* I put in was force the job to stop so I can check the LIBL of the job running to prevent other corruption and security. I did this to test each way the trigger can be fired. I want to take a look at the active LIBL when the job is running. 0018.00 PGM 0018.01 DCL VAR(&YESNO) TYPE(*CHAR) LEN(1) 0018.02 0018.03 DCLF FILE(WCTLHL) RCDFMT(RF$WCH) 0018.04 0018.05 /* IS THERE A BACKUP RUNNING/ ASYNCS DOWN */ 0018.07 RTVDTAARA DTAARA(BURUNNING) RTNVAR(&YESNO) 0018.08 IF COND(&YESNO *EQ 'Y') + 0018.09 THEN(GOTO CMDLBL(ENDJOB)) 0018.10 0038.00 /* IF LOCKED BY ACTUAL EDI PROCESS TRY AGAIN */ 0038.01 MAINLOOP: RTVDTAARA DTAARA(EDIRUNNING) RTNVAR(&YESNO) 0048.00 IF COND(&YESNO *EQ 'Y') THEN(DO) 0048.01 DLYJOB DLY(5) 0048.02 GOTO CMDLBL(MAINLOOP) 0048.03 ENDDO 0049.01 0049.02 /* SET UP ENVIRONMENT FOR LIVE OR TEST ORDER */ 0049.03 GET: RCVF 0049.05 MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ENDJOB)) 0049.06 0049.07 /*HALT*/ CPYF FROMFILE(WXOP) TOFILE(TRNLIB/WXOP) 0049.08 0049.09 IF COND(&WCHTYPE *EQ 'LIVE') THEN(DO) 0049.10 ADDLIBLE LIB(MESLIV022) POSITION(*BEFORE MESSYS022) 0049.11 MONMSG MSGID(CPF0000) 0049.12 ELSE 0049.13 ADDLIBLE LIB(MESTST022) POSITION(*BEFORE MESSYS022) 0049.14 ENDDO 0049.15 0049.16 /* MOVE WEB TICKET DATA FROM EXTRANET INTO PRODUCTION EDI FILES */ 0049.17 0054.00 CHGDTAARA DTAARA(EDIRUNNING) VALUE('Y') 0054.01 0054.02 CALL WCTLORD 0054.03 ENDJOB: ENDPGM The errors we are getting are below Message ID . . . . . . : SQL7008 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Date sent . . . . . . : 03/31/10 Time sent . . . . . . : 15:32:13 Message . . . . : WCTLH in EXTRANET not valid for operation. Cause . . . . . : The reason code is 3. Reason codes are: 1 -- WCTLH has no members. 2 -- WCTLH has been saved with storage free. 3 -- WCTLH not journaled, no authority to the journal, or the journal state is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL or SET DEFAULT must be journaled to the same journal. 4 and 5 -- WCTLH is in or being created into production library but the user has debug mode UPDPROD(*NO). 6 -- Schema being created, but user in debug mode with UPDPROD(*NO). 7 -- A based-on table used in creation of a view is not valid. Either the table is program described table or it is in a temporary schema. Message ID . . . . . . : CPF4328 Severity . . . . . . . : 50 Message type . . . . . : Escape Date sent . . . . . . : 03/31/10 Time sent . . . . . . : 15:40:03 Message . . . . : Member WCTLH not journaled to journal *N. Cause . . . . . : Member WCTLH file WCTLH in library EXTRANET is not journaled to journal *N in library *N for commitment definition *DFTACTGRP. The journal specified is used by commitment definition *DFTACTGRP. If *N is specified for the journal, the file is not journaled. If the file is a logical file, all based-on physical files may not be journaled to the same journal as required by commitment control. Recovery . . . : Ensure the file being opened is journaled. For logical files, make sure that the based-on physical files are journaled to the same journal. Technical description . . . . . . . . : The commitment definition identifier is X'5CC4C6E3C1C3E3C7D9D7'. Message ID . . . . . . : CPF502B Severity . . . . . . . : 30 Message type . . . . . : Sender copy Date sent . . . . . . : 03/31/10 Time sent . . . . . . : 15:40:05 Message . . . . : Error occurred in trigger program. Cause . . . . . : The trigger program which was called as the result of a database insert, update, delete, or read operation failed. See the previous messages in the job log to determine the error. The failing trigger program is WXTRAOE in library EXTRANET type *SYS. The failing trigger is defined on file WCTLH in library EXTRANET. Recovery . . . : Correct the error in the trigger program. Then try your request again. Possible choices for replying to message . . . . . . . . . . . . . . . : C -- The request is canceled. I -- The request is ignored. Bottom Message ID . . . . . . : SQL0443 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Date sent . . . . . . : 03/31/10 Time sent . . . . . . : 15:40:05 Message . . . . : Trigger program or external routine detected an error. Cause . . . . . : Either a trigger program, external procedure, or external function detected and returned an error to SQL. If the error occurred in a trigger program, the trigger was on table WCTLH in schema EXTRANET. If the error occurred in an external procedure or function, the external name is WCTLH in schema EXTRANET. The associated text is *AFTER *INSERT. If the error occurred in a trigger program, the associated text is the type of trigger program. If the error occurred in an external function, the associated text is the text of the error message returned from the external function. Recovery . . . : Refer to the joblog for more information regarding the detected error. Correct the error and try the request again. Bottom We have since tried another way. using the SQL Create Table. Adding the trigger via the Navigator and are going to try again today.
    136,290 pointsBadges:
  • TomLiotta
    I suspect you have a combination that contributes to the the problem. First, this line of code:
    0018.00 PGM
    There are no parms declared for the trigger buffer areas. While that's generally okay for many languages, I suspect also that you have compiled this program as a CLP (i.e., OPM CL) rather than as CLLE (ILE CL). I'm pretty sure that that's not a valid state for the database to be in. Now, that's separate from doing things like changing the library list and trying to delay database processing by DLYJOB or other means; but correcting it should at least allow your testing to progress. Correction can be either adding appropriate PARM() entries or recompiling as CLLE. Either should work. Both should be done. Tom
    125,585 pointsBadges:
  • Littlepd
    Just like RPG trigger programs, CL trigger programs have to be coded to receive two input parameters: a variable-length parameter containing action info and data from the affected record(s), and a 4-byte bunary value that's the length of the first parm. Your CL has neither of these.
    1,130 pointsBadges:
  • ToddN2000
    Many thanks for the input. We've made some changes and got things working to a point. The issue now is when I do the RCVF and later call my RPG program it's says that the record already locked to this job. That should be an easy problem to fix. I think if I just pares out the fields from the input parm and eliminate the RCVF. I should be fine. Thanks Again.
    136,290 pointsBadges:
  • TomLiotta
    Just like RPG trigger programs, CL trigger programs have to be coded to receive two input parameters:... Although it's pretty pointless to have a trigger that can't access the buffers, it is not required to code the parms unless the language requires it. For example, a trigger compiled with CRTBNDCL will work without the parms. It just won't be able to access the buffer areas. But a program compiled with CRTCLPGM cannot be called successfully if parms are passed and they're not declared. The CALL will fail with CPD0172 -- Parameters passed on CALL do not match those required. ILE can be more forgiving. Tom
    125,585 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: