Db2 – way to run parameterized update statements using only DB2 commandline and bash

db2performanceprepared-statement

[DB2 LUW 10.5] I need to update a few hundred thousand rows in a DB2 table. Individual update statements will take longer to execute compared to precompiled approach. I cannot use perl or any such scripting tools with ODBC/JDBC/CLI and PreparedStatement.executeUpdate etc. due to certain limitations.

Is there a way to accomplish this using the db2 commandline tool and procedural SQL? My tool set is restricted to bash, SQL and DB2 Command line interface.

Best Answer

If you are on 10.1 or later you can use INGEST. It is almost as fast as load, more feature rich and has less impact on a running system. First, you need to enable it by creating control tables (if you haven't already):

db2 'call sysproc.sysinstallobjects ( 'INGEST' ,'C' , null, null )';

Now you can ingest the data like:

db2 "ingest from file tab1.del 
 format delimited (
     $colb ...,
     $colc ...
 ) update tableA set colA = null 
   where (colb, colc) = ($colb, $colc)"

Here is an developerworks article:

https://www.ibm.com/developerworks/data/library/techarticle/dm-1304ingestcmd/index.html

and here is the documentation:

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0057198.html

Related Question