| View previous topic :: View next topic |
| Author |
Message |
vani
Beginner
Joined: 28 May 2003
Posts: 51
Topics: 30
|
 Posted: Thu Nov 20, 2003 1:27 am Post subject: Db2 VER 6 Triggers |
 |
|
We have a change request to send updated/inserted information to Webmethods integration server. Since lot of programs/adhoc sqls like spufi are updating the concerned table, we planned to define DB2 Triggers which inturn invokes the DB2 stored procedures. To fulfill this, we have the following queries.
1. According Bookmanage DB2 version 6 UDB "Triggers are not activated as the result of updates made to tables by DB2 utilities".
I tested with FileAid-DB2 and Spufi to update the table. Triggers are activated. Could you tell us what are the DB2 utilities which won't activate the Triggers?
2.Trigger invokes Stored Procedure and there is a application error in Stored procedure. Hence we need to rollback the updations done at CICS programs which activated the Trigger.
How can we do that?
Note:- We will define stored procedures with COMMIT ON RETURN NO option. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Nov 20, 2003 10:01 am Post subject: |
|
|
Vani,
Can you please post feedback for the solutions posted for your prior questions? Just a simple note if the proposed solution worked or not. This not only helps future searches for similar problems as well as it lets the solution provider know that his/her efforts are appreciated and did indeed help the poster.
| Quote: |
1. According Bookmanage DB2 version 6 UDB "Triggers are not activated as the result of updates made to tables by DB2 utilities".
I tested with FileAid-DB2 and Spufi to update the table. Triggers are activated. Could you tell us what are the DB2 utilities which won't activate the Triggers?
|
Answer:The DB2 utilities which would not activate a trigger are the following:
CATMAINT
CHECK DATA
CHECK INDEX
CHECK LOB
COPY
DIAGNOSE
LOAD
MERGECOPY
MODIFY
QUIESCE
REBUILD INDEX
RECOVER
REORG INDEX
REORG TABLESPACE
REPAIR
REPORT
RUNSTATS
STOSPACE
when you update a table using file-aid, spufi, qmf you are actually running SQL's to update the tables. File-aid , Spufi ,QMF & pro-edit are just interactive interfaces to execuete your sql queries.
| Quote: |
2.Trigger invokes Stored Procedure and there is a application error in Stored procedure. Hence we need to rollback the updations done at CICS programs which activated the Trigger.
How can we do that?
Note:- We will define stored procedures with COMMIT ON RETURN NO option.
|
You really cannot rollback because they are different actions which are not related. One way to ensure that your updates in CICS program are in sync with your stored procedure updates, just get rid of the trigger and invoke the stored procedure from the cics program itself.If the everything goes well issue a syncpoint or else issue a rollback in the cics program.
Take a look at this topic which discusses this issue.
http://www.mvsforums.com/helpboards/viewtopic.php?t=1342
Hope this helps...
cheers
kolusu |
|
| Back to top |
|
|
vani
Beginner
Joined: 28 May 2003
Posts: 51
Topics: 30
|
| Posted: Thu Nov 20, 2003 11:27 pm Post subject: |
|
|
| Yes the above suggestions worked. And regarding my prior questions , the suggestions given have worked out. I thank you for this. Sorry for not giving the feedback.Will update from now on. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
