1

In my OS/400 v6.x system I created an AFTER INSERT trigger for a physical file (DB2 table) using the ADDPFTRG command which is fired correctly everytime I execute a WRITE command on an RPG IV program or make an INSERT statement from the STRSQL but when I execute an INSERT statement from a Java program using JT400(JTOpen) JDBC driver the trigger is not fired.

In both scenarios (From OS400 and from Java) the record is inserted successfully but the trigger is only fired when the record is inserted from the OS400 way.

I've searched on IBM redbooks, JTOpen forums and Google anything that could be related to my case and found nothing at all.

Sadly, since the JT400 jar doesn't have a version number, I can't tell if my version is obsolete and I haven't had the opportunity to try the leatest from JTOpen page.

Is there any technical restriction or library bug that prevents me from firing a DB2 trigger from Java/JT400?

Roberto Linares
  • 2,215
  • 3
  • 23
  • 35
  • 1
    Did you mean `ADDPFTRG`? My system doesn't have `CRTPFTRG`. How do you know you're inserting the record (through Java) - are you checking the table directly, or are you querying through Java again? If you're using an ORM framework of some sort, have you flushed the buffer? I _think_ triggers fire before the commit cycle completes. We probably need to see the definition of your trigger, and possibly your Java code as well. – Clockwork-Muse Aug 03 '12 at 16:18
  • @X Zero yesterday Sorry, you are right, The command is ADDPFTRG. Just fixed the post. After I inserted the record through Java I Checked the record inserted and it was there. I checked from STRSQL and from my Java based DB client. For the insert I'm using basic JDBC INSERT commands, no ORMs. – Roberto Linares Aug 04 '12 at 18:02
  • Well, could we get the relevant portions of code? And you may still have to deal with commits, if the trigger happens to be firing after the insert is completed. – Clockwork-Muse Aug 06 '12 at 15:17
  • Sorry I couldn't respond earlier. My java code is as simple as a JNDI based connection on a websphere Application Server creating a simple java Statement object which fires and executeUpdate on a DB2 table. The statement executed is just an INSERT Sql statement. No transaction management neither manual commits and I have assured the row have been inserted successfully. I'll try to post the ADDPFTRG command soon. – Roberto Linares Aug 30 '12 at 05:43

2 Answers2

0

I solved my problem and will post the solution so anyone else (including my future self) with similar problems can at least get a tip on how to troubleshoot it.

The problem had to do with the libraries added to the JDBC connection user. The trigger was actually being fired but I couldn't notice it because I was expecting it to call an RPG program (say, Program1) after this program was suposed to call another RPG program (say, Program2). I couldn't see the second program being called so that's why I thought my trigger was not being fired from the JDBC INSERT SQL statement.

When I checked the QZDAZOINIT job logs in the WRKACTJOB, I could nottice that my Program2 was not being called because it couldn't have been found in the libraries I configured for the JDBC connection. I only added libraries to be able to see the Phisical data Files (PF) but not the RPG programs (PGM) and inside my trigger program (Program1) I was trying to call another one, so I needed to have the programs library on that JDBC connection user. After I configured all the necesary libraries in the JDBC connection properties, it worked as expected.

Roberto Linares
  • 2,215
  • 3
  • 23
  • 35
0

Library list is not the only problem. You may need to care about "Threadsafe" and "Multithreaded job action" parameters of ADDPFTRG command.

ADDPFTRG .... THDSAFE(*YES) MLTTHDACN(*RUN)

Samih Kip
  • 1