db2upsert: syntax and options

Syntax and options of the db2upsert operator.

The syntax for db2upsert is shown below. Option values you supply are shown in italic typeface. When your value contains a space or a tab character, you must enclose it in single quotes. Exactly one occurrence of the -update option is required; all other options are optional.


db2upsert 
  -update update_or_delete_statement
  [-arraySize n] 
  [-close close_statement] 
  [-client_instance client_instance_name
  -client_dbname database
  -user user_name      
  -password password] 
  [-db_cs character_set] 
  [-dbname database_name]   
  [-insert insert_statement] 
  [-open open_statement] 
  [-omitPart] 
  [-padchar string] 
  [-reject] 
  [-rowCommitInterval n] 
  [-server server_name]
  [-timeCommitInterval n]

Table 1. db2upsert Operator Options
Options	Value
-arraySize	-arraySize `n` Optionally specify the size of the insert array. The default size is 2000 records.
-close	-close close_command Optionally specify an SQL statement to be executed after the insert array is processed. You cannot commit work using this option. The statements are executed only once on the Conductor node.
-client_instance	-client_instance client_instance_name [-client_dbname `database`] -user user_name -password password Specifies the client Db2® instance name. This option is required for a remote connection. The -client_dbname suboption specifies the client database alias name for the remote server database. If you do not specify this option, InfoSphere® DataStage® uses the value of the -dbname option, or the value of the APT_DBNAME environment variable, or Db2.BDFT; in that order. The required -user and -password suboptions specify a user name and password for connecting to Db2.
-db_cs	-db_cs `character_set` Specify the character set to map between Db2 char and Varchar values and DataStage ustring schema types and to map SQL statements for output to Db2. The default character set is UTF-8 which is compatible with your osh jobs that contain 7-bit US-ASCII data. For information on national language support, reference this IBM® ICU site: http://oss.software.ibm.com/icu/charset
-dbname	-dbname `database_name` Specifies the name of the Db2 database to access. By default, the operator uses the setting of the environment variable APT_DBNAME, if defined, and Db2.BDFT otherwise. Specifying -dbname overrides APT_DBNAME and Db2.BDFT.
-insert	-insert `insert_statement` Optionally specify the insert statement to be executed. This option is mutually exclusive with using an -update option that issues a delete statement.
-omitPart	[-omitPart] Specifies that that db2upsert should not automatically insert a partitioner. By default, the db2partitioner is inserted.
-open	-open `open_command` Optionally specify an SQL statement to be executed before the insert array is processed. The statements are executed only once on the Conductor node.
-padchar	-padchar `string` Specify a string to pad string and ustring fields that are less than the length of the Db2 CHAR column. See Using the -padchar option for more information on how to use this option.
-reject	-reject If this option is set, records that fail to be updated or inserted are written to a reject data set. You must designate an output data set for this purpose. This option is mutually exclusive with using an -update option that issues a delete statement.
-rowCommitInterval	-rowCommitInterval `n` Specifies the number of records that should be committed before starting a new transaction. The specified number must be a multiple of the input array size. The default is2000. You can also use the APT_RDBMS_COMMIT_ROWS environment to specify the size of a commit.
-server	-server `server_name` Specify the name of the Db2 instance name for the table name.By default, InfoSphere DataStage uses the setting of the Db2.NSTANCE environment variable. Specifying -server overrides Db2.NSTANCE.
-timeCommitInterval	-timeCommitInterval `n` Specifies the number of seconds InfoSphere DataStage should allow between committing the input array and starting a new transaction. The default time period is 2 seconds
-update	-update `update_or_delete_statement` Use this required option to specify the update or delete statement to be executed. An example delete statement is: -update 'delete from tablename where A = ORCHESTRATE.A' A delete statement cannot be issued when using the -insert or -reject option.