Re: DB2 trigger doesn't work when INSERT comes from Java app; works fine from STRSQL

["Theo Soppe" <theo@xxxxxxxxxxxxx>]

Yes, been there!

I found out that the following code is needed in your trigger program (which in my case is RPG ;-)

Point of the whole problem is that you have to set the Transaction Isolation Level according to the triggering operation. After I made this change everything worked fine. Maybe someone else can help you to implement this in C, if you can't do it yourself.

 

Good luck!

 

Theo.

 

 * ====================================================================
 * Set Transaction Isolation Level to the same as the application     
 * ====================================================================
c                   select                                            
c                   when      cmtlck='0'                              
c/exec sql SET TRANSACTION ISOLATION LEVEL NONE                       
c/end-exec                                                            
c                   when      cmtlck='1'                              
c/exec sql SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, READ     
c+         WRITE                                                      
c/end-exec                                                            
c                   when      cmtlck='2'                              
c/exec sql SET TRANSACTION ISOLATION LEVEL READ COMMITTED             
c/end-exec                                                            
c                   when      cmtlck='3'                              
c/exec sql SET TRANSACTION ISOLATION LEVEL ALL                        

c/end-exec               
c                   endsl

 

----- Original Message -----

From: "Jacob Madsen" <jom@mail.ikasths.dk>

To: <JAVA400-L@midrange.com>

Sent: donderdag 27 april 2000 11:37

Subject: DB2 trigger doesn't work when INSERT comes from Java app; works fine from STRSQL

> Hi all
> 
> I have created a 
trigger in ILE C, which takes care of autogenerating a
> primary key for a 
statistics table. This is made the following way:
> 
> 1. The 
primary key has a DEFAULT of 0
> 2. The trigger first adds 1 to the value 
in the key table - a separate table
> only with the key.
> 3. The 
trigger updates the value each time after insert in the statistics
> table 
WHERE primary key = 0.
> 
> Now the weird part comes. I've tested 
this with INSERT statements in STRSQL,
> and it works exactly like it's 
supposed to. But when I execute a INSERT
> statement from the Java app, 
for which the database is made, the trigger
> never runs, which of course 
results in a key violation.
> 
> Any good ideas?
> 
> 
Jacob
> 
> 
> +---
> | This is the JAVA/400 Mailing 
List!
> | To submit a new message, send your mail to JAVA400-L@midrange.com.
> | To subscribe to 
this list send email to JAVA400-L-SUB@midrange.com.
> | To 
unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
> | Questions 
should be directed to the list owner: joe@zappie.net
> +---