DB2 bulk update

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

just re sending..

-Suresh

On Mon, Jun 10, 2013 at 7:48 PM, Suresh Kumar <[hidden email]> wrote:

Hi,

 

I am looking for a bulk update mechanism in DB2.

 

My sql is  be something like below and  have a bind object which has the aspects and the values in format of array by columns.

'update ELEMENT set instance=:instance,name=:name,idx_ind=:id,type=:type,origin=:origin,date=:datex,alias=:aliasName,ulabel=:userLabel,host=:elementId,user=:username,invariant=:invariant,state=:state where ind=:id'

 But here the binding is failing since its not binding using the array of column values . Please suggest some clues to perform bulk update.

 

Thanks,

Suresh

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Hi Suresh,

I can tell you how I do a mulit-row update using the VisualWorks DB2EXDI class.  It's very important to use "case", "else" and "where" clauses to restrict the number of rows to only those you want to update, otherwise some very bad things can happen to the rows you don't want to update.  Note this assumes "conn' is the connection to your db2 session.

Warnings:
1. The else [column] clause is required for each case statement used, otherwise you will end up nulling-out data you do not want to!
2. The where clause at the end must be used to restrict the update to rows that require at least one column updating, otherwise every column in the table will be updated, increasing both execution time and pressure on the transaction log.  Every column that meets the where clause WILL BE UPDATED and logged, even if it is the same, present value

Here's an example that will hopefully get you going the right direction at least:

sess := conn getSession.
"Drop the test table if existed."
sess prepare: 'DROP TABLE TESTTABLE';
               execute;
               answer;
               answer.

"Create a test table."
sess prepare:  'CREATE TABLE TESTTABLE(
               cid int ,
               name varchar(30),
               cd date,
               ct clob(2m)
)';
               execute;
               answer;
               answer.

"Set the number of records being inserted."
loopCount := 2.
"The SQL used to do insert."
sql := 'INSERT INTO TESTTABLE VALUES (?, ?, ?, ?)'.

"Insert some test data."
sess prepare: sql.
1 to: loopCount do: [ :i|
         sess bindInput: (Array with: i with: ('test', i printString) with: Date today with:(String new: 4096 withAll: $a));
                              execute;
                              answer;
                              answer.].

"Select the test data to verify."
sess := conn getSession.
sess prepare: 'Select * from TESTTABLE'.
sess execute.
ans := sess answer.
res := ans upToEnd.

 "Create an array of binding values."
bindList := Array new: 6.
bindList at: 1 put: 'firstRow'.
bindList at: 2 put: 'secondRow'.
bindList at: 3 put: (Date fromDays: 123).
bindList at: 4 put: (Date fromDays: 234).
bindList at: 5 put: (String new: 4096 withAll: $b).
bindList at: 6 put: (String new: 4096 withAll: $c).

"Do the update."
sess := conn getSession.
sess prepare: 'update TESTTABLE
            set name =
            case
            when cid=1 then cast( ? as varchar(30))
            when cid=2 then cast( ? as varchar(30))
            else name
            end,
            cd =
            case
            when cid=1 then cast( ? as date )
            when cid=2 then cast( ? as date )
            else cd
            end,
            ct =
            case
            when cid=1 then cast( ? as clob(2m))
            when cid=2 then cast( ? as clob(2m))
            else ct
            end'.

sess bindInput: bindList.
sess execute.
ans := sess answer.
 
"Select the test data to verify."
sess := conn getSession.
sess prepare: 'Select * from TESTTABLE'.
sess execute.
ans := sess answer.
res := ans upToEnd.
sess disconnect.

Hope this helps and I don't have any typo's.
Mark Petersen
IBM Corp.

oops, already noticed I forgot the where clause in reviewing this.  I'm cobbling from a couple places so missed it.  Amend the prepare statement to be:

sess := conn getSession.
sess prepare: 'update TESTTABLE
            set name =
            case
            when cid=1 then cast( ? as varchar(30))
            when cid=2 then cast( ? as varchar(30))
            else name
            end,
            cd =
            case
            when cid=1 then cast( ? as date )
            when cid=2 then cast( ? as date )
            else cd
            end,
            ct =
            case
            when cid=1 then cast( ? as clob(2m))
            when cid=2 then cast( ? as clob(2m))
            else ct
            end
            where (cid=1 or cid=2)'.

Dear Suresh,
    our DB2 expert suggests the following script:

"Connect to DB2 server."
conn := DB2Connection new.
conn username: 'username';
password: 'password';
environment: 'DB2'.
conn connect.

"Drop the test table if it existed."
sess := conn getSession.
sess prepare:  'drop table test_bulk_update'.
sess execute.
sess answer.
sess answer.

"Create a test table."
sess := conn getSession.
sess prepare:  'create table test_bulk_update (cid smallint, cd date,
cvc varchar(100))'.
sess execute.
sess answer.
sess answer.

"Insert a row of test data."
listOfInsert := Array with: 1 with: Date today with: 'test12345678'.
sess := conn getSession.
sess prepare:  'INSERT INTO test_bulk_update VALUES (?, ?, ?)'.
sess bindInput: listOfInsert.
sess execute.
sess answer.
sess answer.

"Verify the inserted data."
sess := conn getSession.
sess prepare: 'select * from test_bulk_update'.
sess execute.
ansStrm := sess answer upToEnd inspect.
sess answer.

"Update the test data."
listOfUpdate := Array with: 1 with: (Date today addDays: 10) with:
'test12345678_new'.
sess := conn getSession.
sess prepare:  'update test_bulk_update set cd = :2, cvc = :3 where
cid=:1 '.
sess bindInput: listOfUpdate.
sess execute.
sess answer.
sess answer.

"Verify the updates."
sess := conn getSession.
sess prepare: 'select * from test_bulk_update'.
sess execute.
ansStrm := sess answer upToEnd inspect.
sess answer.

          HTH
                Niall Ross


_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc