Trigger errors
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.
ADDPFTRG FILE(EXTRANET/WCTLH) TRGTIME(*AFTER) TRGEVENT(*INSERT) PGM(EXTRANET/WXTRAOE) TRGLIB(EXTRANET).
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
Software/Hardware used:
V5R4M5 on an iSeries
ADDPFTRG FILE(EXTRANET/WCTLH) TRGTIME(*AFTER) TRGEVENT(*INSERT) PGM(EXTRANET/WXTRAOE) TRGLIB(EXTRANET).
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
ASKED:
March 31, 2010 6:20 PM
UPDATED:
April 1, 2010 10:30 PM
Answer Wiki:
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.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
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.
I suspect you have a combination that contributes to the the problem. First, this line of code:
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
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.
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
Thanks again.
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