DB Update¶

Introduction¶

The DB Update plugin batch inserts/updates to a database from a file, or creates a control file to run Oracle SQL Loader. The DB Update plugin supports NVARCHAR2 and supports CLOB column type when using Oracle.

This plugin can be associated only with Private Agent Groups and is not available on the Jitterbit Cloud Agent Groups.

Important

Plugins provided by Jitterbit are deprecated. See Deprecation and Support in Plugins.

Download and Add the Plugin¶

The DB Update plugin must be downloaded at the link provided below and added to the organization (see Add New Plugins in Customizations > Plug-ins).

DB Update (.ZIP)

Associate the Plugin with an Agent Group¶

The DB Update plugin must be associated with a Private Agent Group to make the plugin available on all agents in the group (see Associate Agent Groups in Customizations > Plug-ins).

Set Variables and Use the Plugin in a Project¶

Refer to these topics for information about using plugins in a project:

Plugins (Cloud Studio)
Plugins (Design Studio)

The DB Update plugin can perform a batch insert/update or a replicate using the variables listed below.

Perform Batch Insert/Update¶

To perform a batch insert/update, set the variable jitterbit.plugin.DbUpdate.UseOracleSqlldr to false or null.

The plugin reads data from the target file and inserts/updates a database table. The target file is assumed to be in CSV format with UTF-8 encoding.

The plugin log can be found under <JITTERBIT_HOME>/log/plugin/. Example log file name: DbUpdate_Account_2013_03_11.log.

Perform Replicate¶

To perform a replicate:

Set the variable jitterbit.plugin.DbUpdate.UseOracleSqlldr to true.
Set the variable jitterbit.plugin.DbUpdate.ControlFile to create the filenames:
- The control filename is concatenated from the variable value + .ctrl.
- The data filename is concatenated from the variable value + .data.
- The SQLLDR log file name is concatenated from the variable value + .log.

The process may generate a sequence of responses from query/queryMore.

The plugin will run on the target file at the end of the response transformation.

The first time the plugin is run, it will copy the header (jitterbit.plugin.DbUpdate.ControlHeader) to the control file, and the target file to the data file. On subsequent runs, the target file will be appended to the data file.

Variables¶

Name	Type	Required	Description
`jitterbit.plugin.DbUpdate.ColumnName_list`	String	Optional	Normally all the fields in the Salesforce object are transferred to the database table, and the primary key of the table is `"ID"`. To skip some fields or use some other primary keys, you can use this optional global variable. In this case, the primary key column in the list must append the label `"(PR)"`, so the list would look like the following: `Id(PR),Name, . . . \|`
`jitterbit.plugin.DbUpdate.ControlHeader`	String	Optional	The header to be copied into the control file.
`jitterbit.plugin.DbUpdate.ControlFile`	String	Optional	For a replicate: The control filename is concatenated from the variable value + `.ctrl`. The data filename is concatenated from the variable value + `.data`. The SQLLDR log file name is concatenated from the variable value + `.log`.
`jitterbit.plugin.DbUpdate.DbGuid`	String	Optional	The database GUID.
`jitterbit.plugin.DbUpdate.DebugMode`	String	Optional	Set to any value to enable debug mode. When debug mode is enabled, logging is added.
`jitterbit.plugin.DbUpdate.SqlLdrAuth`	String	Optional	The Oracle SQL Loader authentication.
`jitterbit.plugin.DbUpdate.SqlLdrPath`	String	Optional	The Oracle SQL Loader path.
`jitterbit.plugin.DbUpdate.TableName`	String	Optional	This variable may include the schema name. For example, `"OWNER:ACCOUNT"`. Note The name of the primary key column of the database table must be `"ID"`.
`jitterbit.plugin.DbUpdate.UseOracleSqlldr`	Boolean	Optional	For a replicate, set the value to `true`. For an insert/update, set the value to `false` or `null`. The default value is `false`.