1

I need to update Customer table in chunk of 50K, The number of records in customer table is 2 million.

I am using DB2 command prompt to execute my sql from a file using bat file.

There are two file which I have created.

1. customerupdate.bat

DB2 CONNECT TO DBTEST USER DB2ADMIN USING XXXXXX
set start=%1
set end=%2
db2 -l D:\vinu\CUSTOMERADDRESS.log  -mstf D:\vinu\CUSTOMERADDRESS.sql

2. customer.sql

update customer set firstname='XXXX' where customercid between 1 and 50000

Here I need to pass 1 and 50000 value from command prompt.

update customer set firstname='XXXX' where customercid between 1 and 50000

I am executing above sql using below command successfully, However I need pass the parameter to sql file.

C:\Program Files\IBM\SQLLIB\BIN>customerupdate.bat 1 50000

Please note: I cannot use the query directly like

db2 -l D:\vinu\CUSTOMERADDRESS.log -mst " update customer set firstname='XXXX' where customercid between %1 and %2" The query should be supplied from a sql file only.

Vinayak Dornala
  • 1,609
  • 1
  • 21
  • 27

2 Answers2

2

You cannot do that with the traditional CLP, but it's possible if you use CLPPlus.

In your customer.sql:

update customer set firstname='XXXX' where customercid between &1 and &2;
exit

Invoke CLPPlus (it connects using JDBC):

clpplus -nw db2admin/password@hostname:port/dbtest @customer.sql 1 50000

&1 and &2 will be substituted with the first and second command line arguments respectively.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
1

Rather than putting all of your SQL in a separate SQL file, why not just include the statements in your batch file?

DB2 CONNECT TO DBTEST USER DB2ADMIN USING XXXXXX
set start=%1
set end=%2

set FIRSTNAME=Vinu

db2 -l D:\vinu\CUSTOMERADDRESS.LOG "update customer set firstname = 'XXXXX' where customercid between %start% and %end"

This will require a little more error checking (since you can't rely on the -s flag for the CLP to stop execution), but it will work.

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • I already mention this option in my post. db2 -l D:\vinu\CUSTOMERADDRESS.log -mst " update customer set firstname='XXXX' where customercid between %1 and %2" The query should be supplied from a sql file only. – Vinayak Dornala Nov 10 '14 at 15:02
  • Perhaps I wasn't clear: I gave this alternate solution to @mustaccio's precisely because, you can't do what you're asking via the DB2 CLP. – Ian Bjorhovde Nov 10 '14 at 15:24
  • We can upgrade the client from below link and using CLPPlus command we can execute the script from sql file. http://plutonium.cs.umanitoba.ca/downloads/DB2_client/ – Vinayak Dornala Nov 10 '14 at 18:42