Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2002
    Posts
    34

    Unanswered: db2 commit frequency

    I have 2 questions:

    1. When I try to run an update through command center, I get a "deadlock or timeout error." I am assuming that since I am the only developer in this environment it must be a timeout issue. What are some possible solutions to this problem.

    UPDATE SIEBEL.EIM_ORDER_ITEM
    SET SHIP_ACCNT_NAME = (SELECT B.NAME FROM SIEBEL.S_ORG_EXT B
    WHERE B.LOC = X_JDE_CUSTNO);

    2. Can someone please provide a sample db2 sql stored procedure that commits every N records. Currently when I try to commit after an insert it ends the loop and stored procedure.

    Please advise,

    Brian

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    For ur 1st problem, please take care of the database configuration parameters related to locks and logs.
    Also you can have a event monitor for deadlock to get the exact point at which the deadlock has occured.

    Regarding the procedure,here is one that will commit work after every 50 records (You can have ur own logic):

    CREATE PROCEDURE OWNER.COMMIT_FREQ1 ()
    LANGUAGE SQL
    Specific OWNER.COMMIT_FREQ1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    DECLARE ref_count integer;
    select count(*) into ref_count from CDSWEB.CHOICE ;
    If (MOD(ref_count,50)=0) then
    commit work;
    end if;
    END P1

    Now one thing is where to call the procedure. You can do that on the insert trigger for the particular table
    in which you are inserting records.
    Hope you will do it. Get back if you have any difficulties.

    -Prashant G Dahalkar
    Prashant

  3. #3
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Use this trigger for calling the procedure:

    create trigger ti_commit_freq AFTER insert on CHOICE
    REFERENCING NEW AS NEWROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
    CALL COMMIT_FREQ1();
    end ;

    Note:
    The CALL is used to call a Stored Procedure in both before and after triggers.
    The CALL is used to call a Stored Procedure and can only be used in DB2 for OS/390.

    -Prashant G Dahalkar
    Prashant

  4. #4
    Join Date
    Mar 2002
    Posts
    34
    Prashant,

    Thank you very much for the help and information.

    I have a stored procedure that does a few lookups and then inserts into a table.

    I included the code to commit when "mod(rowCount, 1000) = 0" after the insert.

    Do you recommend to create a trigger on insert into the table? or is it okay to have the commit with the insert stored procedure?

    Thanks again for your help,

    Brian

  5. #5
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    In my case it was necessary to call the procedure through the trigger.
    Now firing a trigger after every insert is not advisable as far as performance is concerned.
    Can you show the procedure , so that i can draw some inference.
    -Prashant G Dahalkar
    Prashant

  6. #6
    Join Date
    Mar 2002
    Posts
    34
    Prashant,

    attached is the stored proc that I wrote.

    I greatly appreciate any and all suggestions.

    Thanks again,

    Brian Kalberer
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2002
    Posts
    6
    Do you shut down the Siebel server and all clients before trying to do your updates? As far as I recall, the SIEBEL.S_ORG_EXT table is used for nearly everything so locks are very common.

    I know it might sound crazy, but it might be worthwhile setting up a table temporarily for the results of (SELECT B.NAME FROM SIEBEL.S_ORG_EXT B WHERE B.LOC = X_JDE_CUSTNO) and do your updates on a join to this. That's the way I ended up getting most of my loads to work anyway!

  8. #8
    Join Date
    Mar 2002
    Posts
    34
    MikeC,

    Thanks for the suggestions. It definetely my be an option. Right now the updates I am running is part of a daily interface being run after 7pm when the user volume is at its lowest.

    Thanks again,

    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •