Trigger over table that uses transaction inserts

90 pts.
Tags:
iSeries
JDBC
SQL INSERT
Triggers
I have a table that is being updated via JDBC. The program that is writing records (sql insert) is using transactions. This table has an after insert trigger on it.

When the trigger is disabled the table is updates correctly.

When the trigger is on we get the first record and the tigger locks the record and the insert of the other transaction rows never completes.

My trigger is defined like this.

 


CREATE TRIGGER DECOURTTR

AFTER INSERT ON DECOURT

FOR EACH STATEMENT

MODE DB2SQL

SET OPTION ALWBLK = *ALLREAD ,

ALWCPYDTA = *OPTIMIZE ,

COMMIT = *NONE ,

CLOSQLCSR = *ENDMOD ,

DECRESULT = (31, 31, 00) ,

DFTRDBCOL = *NONE ,

DYNDFTCOL = *NO ,

DYNUSRPRF = *USER ,

SRTSEQ = *HEX

BEGIN ATOMIC

DECLARE P_ACTION CHAR ( 3 ) ;

SET P_ACTION = 'ADD' ;

CALL DECOURTPR ( P_ACTION ) ;

END ;



How do I need to redefine this to make it work? Kerwin



Software/Hardware used:
iSeries

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 18  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    The program that is writing records (sql insert) is using transactions. Can you show a sample of a statement that actually results in the trigger being fired? And what can you tell us about procedure DECOURTPR? Tom
    125,585 pointsBadges:
    report
  • KSC
    Tom, I just contacted the programmer that is writing that code. He is out sick today, I will get the snippet of code for the sql insert to you as soon as I can. I do know he is in a MySQL environment. As for the DECOURTPR, it is calling a program that will read all the records in the table as it reads the record it will validate most of the fields. If all fields of all records for this transaction pass validation it will write the records to another table and delete the records for this transaction from this table. If a field fails the validation it will send a message back to the remote system, complete all validation for this transaction and delete all associated transaction records in this table. Realy we only want the trigger to exicute once per "transaction" A transaction can be 1 or many records. I asked the programmer on the other system if we could add a column that would indicate if this was the last record of the transaction. The the trigger would test this column if the last record flag was on, then it would call the validation program. He really wants to use the transaction method. Push come to shove, I can make him come around to this other method. Kerwin
    90 pointsBadges:
    report
  • BigKat
    not 100% sure I caught what all was going on, but it sounded like this: A process is write multiple records into a file (each of which is causing the trigger to fire) The trigger is reading and deleting the record from that file. The process is waiting for the trigger to finish so it can release its lock and go on, at the same time the trigger is waiting for the delete to occur so it can end. Even though it is an AFTER INSERT trigger, there might be some contention because of commitment control. Not sure I've helped, but maybe give you a direction to look :)
    7,935 pointsBadges:
    report
  • KSC
    BigKat, I think you have it. But, According to the developer on the MySQL side when you use a Transaction it should treat all inserts as one action causing the trigger to run once. Hence the FOR EACH STATEMENT in the trigger create. I am thinking that in his system this might be true but on the iSeries it behaves differantly. I just don't know anything about transaction processing. I am thinking of telling the developer to use plan B. But wanted to make sure I understood, and did not want to miss an opportunity to learn something. This has been a very good learning project. Getting the iSeries to update the MySQL table dynamically in real time and alowing for the reverse. The two boxes/system on two networks also. Kerwin
    90 pointsBadges:
    report
  • TomLiotta
    Hence the FOR EACH STATEMENT in the trigger create. Not exactly so. The trigger will be called for each row regardless of how many rows were inserted (or updated/deleted for other trigger types). The major difference is in when the call actually happens. FOR EACH STATEMENT means that the trigger is called after all rows have been operated on. If a block of 10 rows are inserted, the trigger will be called after all 10 have been inserted; and each time the trigger will be passed the row data for one of those rows. FOR EACH ROW means that the trigger will be called as each row is inserted (or updated/deleted) but the calls will happen while the statement is executing. In the first case, one implication is that the database must maintain images of the rows until the invoking statement completes. And it then needs to cycle through those images as it calls the trigger multiple times. Once all images have been processed, they are deleted. There are only a few cases where FOR EACH STATEMENT should be used. The additional overhead of processing each row twice usually isn't worth incurring. If all fields of all records for this transaction pass validation it will write the records to another table and delete the records for this transaction from this table. Is that meaning that DECOURTPR is deleting the rows that caused the trigger to fire? Tom
    125,585 pointsBadges:
    report
  • KSC
    Tom, I think I understand now how the FOR EACH STATEMENT and FOR EACH ROW now. The way you said it makes it clear. You should write the IBM books. "Is that meaning that DECOURTPR is deleting the rows that caused the trigger to fire?" The answer is No and Yes. DECOURTPR is calling a program that is deleting the records. Now that you have clarified how the Trigger is going to behave base on the FOR EACH xxx. Unless someone can tell me how to make it fire one time for the transaction. I think plan B must be done. "I asked the programmer on the other system if we could add a column that would indicate if this was the last record of the transaction. The the trigger would test this column if the last record flag was on, then it would call the validation program." Kerwin
    90 pointsBadges:
    report
  • TomLiotta
    If all fields of all records for this transaction pass validation it will write the records to another table and delete the records for this transaction from this table. BTW, if "validation" is the purpose, that's usually better suited for a BEFORE INSERT trigger. Note that a BEFORE INSERT trigger forces FOR EACH ROW and MODE DB2ROW. Also, what happens to the "transaction" if validation fails? How is notification sent? (Or is it?) Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    The way you said it makes it clear. Then I must've said something poorly. If it's all clear to you now, YOU need to be writing the books. There are tricky little details in the SQL documentation that need to be corrected apparently. I made a request for a clarification/correction a day or so ago that actually touches on something I see in your trigger. Your SET OPTION CLOSQLCSR = *ENDMOD is documented as invalid for triggers. See the SET OPTION-statement section of the triggered-action sub-topic under CREATE TRIGGER. Note the paragraph:
    • The options CLOSQLCSR, CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE TRIGGER statement.
    Yet, in the closqlcsr-option section of SET OPTION, we find:
    • *ENDACTGRP and *ENDMOD are for use by ILE programs and modules, SQL functions, SQL procedures, or SQL triggers.
    Sigh. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    As I think about it, what you might want to review is the use of the 'transition table' that is available for your type of trigger. See the REFERENCING NEW TABLE AS table-identifier subtopic under CREATE TRIGGER. You apparently want the action to take place when the row that is being processed is the last row in the 'transition table'. I'm not sure when/if I'll have time to find a good way to use it, but you can study what it means and also perhaps study how a 'transition table' relates to REFERENCING NEW ROW AS correlation-name. For the trigger type that you originally defined, the temporary 'transition table' is essentially the result set of all affected rows. It might normally be used when a trigger needs to handle a COUNT(*) of rows or a SUM(*) over a column rather than just the individual rows passed through the trigger buffer. ("Trigger buffer" is more meaningful for native triggers rather than SQL triggers such as yours.) Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    No, something isn't right yet. You have MODE DB2SQL plus FOR EACH STATEMENT. I'm going to see if I can create a working example this evening. The combination that you have should cause a very specific series of actions; but combined with the deletion of rows from the triggering table while the trigger is running might be interfering. Something keeps raising a flag in my mind, but it's not getting all the way out. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Not enough time this evening. I have a basic setup with table, trigger and deletion proc; but I need to run it with external transactions. It'll probably be tomorrow evening before I have a test environment running to see what is happening. Something is indeed going on within the transaction. I'll see if I can post a full working example or find out what's really happening. Tom
    125,585 pointsBadges:
    report
  • Darryn
    Just a quick question, you might have considered it already, but why aren't you doing the verification in the system before writing to the database? Seems easier to verify if everything is ok, then write to the correct tables, rather than writing to a table, verifying the data, and then moving the data.
    765 pointsBadges:
    report
  • KSC
    Tom, "Also, what happens to the “transaction” if validation fails? How is notification sent? (Or is it?)" If the validation fails, we send an error code back to the sending system (different code for different reasons). then delete the record. If it passes validation we also send a code back to the sending system saying all good. I had read that manual page and never noticed the discrepancy. Tom I do appreciated all the time you are putting in on this. I see your posting times, get some sleep. I do not know yet if the other developer is still sick. If he is in I will talk to him some. Kerwin
    90 pointsBadges:
    report
  • KSC
    Darryn, The remote data base dose not have all needed tables to do the validation. We did not want to go through the time and issues that would come up to keep about 15 tables in sync (real time) on two systems. I would like to do this but... They have a system (old home grown) in place that work good for what they are doing. We have another system (old home grown) in place that works good also. We added a new function to our system. We want them to use the new function. But they can not sign on to our system. So they are modifying their system to basically send the data to us, we validate and...
    90 pointsBadges:
    report
  • TomLiotta
    I need to change some previous comments, but first... ...the insert of the other transaction rows... Please define what is meant by "transaction" and "rows". That is, how does the client set transaction boundaries? And how does it insert multiple rows -- lists of values? Multiple INSERT statements? Then, why is this chosen in the trigger, and how does it relate to attributes of DECOURTPR: SET OPTION ... COMMIT = *NONE Does DECOURTPR run in the *CALLER activation group? Tom
    125,585 pointsBadges:
    report
  • KSC
    Tom Please define what is meant by “transaction” and “rows”. That is, how does the client set transaction boundaries? And how does it insert multiple rows — lists of values? Multiple INSERT statements? I am not sure how all of this is suppose to work, I need to talk to the developer (he is still out sick). I think based on what you are saying and what I have found. My best bet is to have the developer change his process. Not using the "transactions". My plan B. I hope he is back in the office Wednesday and I will talk to him about this.
    90 pointsBadges:
    report
  • TomLiotta
    I have essentially a copy of your trigger, a stored proc (external) for a program to delete inserted rows from the table and a table that has the trigger assigned. It's working well enough for me as far as my trivial "transactions" go, but I need to mimic the remote process. I need to send transactions the same way that your client is doing, at least the same structure of transactions. For that, I need to know an example of how many rows might be inserted within one transaction, how many INSERT statements are run to insert those rows (i.e., blocked INSERTs? multiple single INSERTs?), and ideally which client is used (JDBC, but which release?). IBM made at least one change for V5R4 in the java Toolkit that might be related; slim chance. So, what JDBC driver release and what iSeries OS release? Tom
    125,585 pointsBadges:
    report
  • Darryn
    I would still put the validation in prior to trying to insert into your tables, and there are plenty of options for doing this. As it is an integration between the systems, you can capture the information at the "gateway" to your application, use your application tables to validate, and then respond to the source system. You can do this quite easily using many open source ETL/integration tools that have been built for this type of functionality.
    765 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following