Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: db2 batch process commits

    Hi All,

    I have a piece of code in shell script that reads from a flat file and updates a row in a table. I am updating 1.1 million records at once. My code is committing after every row. Also the process is running for ever. Is there a way I can commit a bunch of records at a time say 500 and then process the next 500. here is my code.



    while read TEST_RISK TEST_RISKREN TEST_RISKEND TEST_MARKER
    do
    echo "Starting Update"
    currval=`db2 -x "select ZONE_IND from TEST_TEST_MARKER_TAB where RISK = '$TEST_RISK' \
    and RISKREN = '$TEST_RISKREN' \
    and RISKEND = '$TEST_RISKEND'"`

    errcnt=`db2 -ec +o "update TEST_TEST_MARKER_TAB set ZONE_IND=('$TEST_MARKER') where RISK = '$TEST_RISK' \
    and RISKREN = '$TEST_RISKREN' \
    and RISKEND = '$TEST_RISKEND'"`

    db2 "commit work"

    if [ "$errcnt" -ne 0 ];
    then
    echo "$TEST_RISK $TEST_RISKREN $TEST_RISKEND" >> risk_count.txt
    else
    echo "insert a entry in journal table for $TEST_RISK $TEST_RISKREN $TEST_RISKEND"
    db2 "insert into DB2INST1.test_journals(journalid,risk,riskren,risk end,subsystem,title,body)
    values ((next value for DB2INST1.JOURNALIDSEQ), '$TEST_RISK', '$TEST_RISKREN', '$TEST_RISKEND',5,
    'THE TEST_MARKER Data Marker value was changed','"THE VALUE OF TEST_MARKER changed from" $currval "to" $TEST_MARKER')"
    fi
    done < upload.csv

    Any suggestions please ..

    Thanks

    dbsam

  2. #2
    Join Date
    Apr 2011
    Posts
    19
    Without knowing exactly what you are doing

    I would say delete the row you are replacing (instead of updating) out of the database and write the new row out to a flat file then, at the end of your run, use the LOAD utility with an appropriate commit (save) count.

    Edit: Auto commit is on or off? (db2 LIST COMMAND OPTIONS ) If it on turn it off then set up a counter in your script and run a manual commit after X rows.
    Last edited by DebianDog; 11-03-11 at 16:35.

Posting Permissions

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