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

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.