Skip to end of metadata
Go to start of metadata

Introduction

A database update activity interacts with a database connection to update existing data as a target in an operation or script, while a database upsert activity interacts with a database connection to both update existing data and insert new data in an operation or script. (Jitterbit Harmony supports upsert activities for databases by using a combination of query, insert, and update.) After configuring a database connection, you can configure as many database activities as you like for each database connection.

These activities are covered together, as both activities require at least one update key to be selected to use as the field or fields to identify which records need to be updated, and the steps to configure a database update or upsert activity are the same.

Creating a Database Activity

From the design canvas, open the Connectivity tab of the design component palette:

Within the Endpoints filter, click the database connection block to display activities that are available to be used with a database connection:

To create an activity that can be configured, the activity must first be added to an operation on the design canvas. To add an activity to an operation, drag the activity block from the palette to the operation.

For more information about the parts of an operation and adding activities to operations, see Operation Creation and Configuration.

Accessing Menu Actions

After a database activity has been added to an operation, menu actions for that activity are accessible from the project pane in both the Workflows and Components tabs, and from the design canvas:

  • Project Pane: In the Workflows or Components tab of the project pane, hover over an activity name and click the actions menu icon  to open the actions menu.

  • Design Canvas: Within the operation, click an existing activity block to open the actions menu.

Each of these menu actions is available:

  • View/Edit: This opens the activity configuration screen for you to configure the activity. For details, see Configuring a Database Update or Upsert Activity later on this page.
  • Delete: This is used to permanently delete the activity (see Component Dependencies, Deletion, and Removal).
  • Rename: This positions the cursor on the activity name in the project pane for you to make edits.
  • View Dependencies: This changes the view in the project pane to display any other parts of the project that the activity is dependent on (see Component Dependencies, Deletion, and Removal).
  • Remove: Available only from the actions menu on the design canvas, this removes the activity as a step in the operation without deleting it from the project. When you remove an activity that is adjacent to a transformation, if schemas are provided within that activity, they will no longer be referenced by the transformation. Removed components can be accessed or permanently deleted from the project pane (see Component Dependencies, Deletion, and Removal).
  • Deploy: This deploys the activity and any components it is dependent on (see Component Deployment).
  • Configurable Deploy: This opens the deployment screen, where you can select project components to deploy (see Component Deployment).
  • Add to group: This opens a prompt to create a new custom group or to add the component to an existing group. Custom groups are an organizational tool to help organize a project (see Component Groups).
  • Duplicate: This creates a copy of the activity as a new, unreferenced component. Upon creating the component copy, the cursor is positioned on the component name within the project pane for you to rename the component.

Configuring a Database Update or Upsert Activity

The steps for configuring a database update or upsert are the same; however, these steps differ depending on if you need to join multiple objects. To update or upsert only a single object, without joins, refer to Single-Object Update or Upsert. To update or upsert multiple objects, with joins, refer to Multiple-Object Update or Upsert.

Single-Object Update or Upsert

Follow these steps if the database update or upsert is to a single object, and you do not need to create any joins with other objects. If you need to join multiple objects, refer to Multiple-Object Update or Upsert.

Step 1 – Enter Basic Information and Select Object

  • Name: Enter a name to use to identify the database activity. The name must be unique for each database update or upsert activity and must not contain forward slashes (/) or colons (:).
  • Provide the Table Names Reference: This section displays the tables available in the database endpoint using any applied filters, after using the refresh icon  for the initial loading of objects. If there are a large number of objects in the database, it is recommended to filter the objects using View or Search prior to refreshing.
    • View: Use the dropdown to filter the list of objects to tables, views, or system tables. By default, tables and views are shown.

    • Search: Enter any part of the object name into the search box to filter the list of objects. The search is not case-sensitive. The list of objects may be filtered automatically or you may need to refresh the list, depending on whether the objects have already been retrieved:

      • If searching on objects that have already been retrieved from the database and already appear within the list, any search terms typed within the search box will automatically filter the list.

      • If searching on objects that have not yet been retrieved, for example when attempting to limit the number of objects retrieved for databases with a large number of objects, use the refresh icon next to the search box to fetch the objects from the database using the filter.

      CAUTION: For databases that do not support searching on objects, such as Microsoft Access, no results will be returned.
    • Refresh: Click the refresh icon  to load or reload tables from the database endpoint. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects to the database, or if you have additional search criteria that you wish to apply prior to retrieving database objects.

    • Include the schema/owner in the table names: Select this checkbox to include the schema in the table names. This may be useful if you use the same table name in multiple schemas (such as for development and production) to allow you to see and select the table in the correct schema.
    • Provide the Table Names Reference: The list of objects is displayed in a column on the left. Click on a single object only to add it to the table on the right. If instead you are updating or upserting to multiple objects that need to be joined, refer to Multiple-Object Update or Upsert.

      TIP: If the list does not populate with available objects even after applying a filter, the database connection may not be successful. Ensure you are connected by reopening the connection and retesting the credentials.
    • Table of Selected Objects: The selected object is displayed in a table on the right. The columns in this table are not relevant for single-object updates or upserts and cannot be edited.

  • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Step 2 – Select Update Keys

  • Select Keys: Select the checkboxes of any fields you want to use as update keys. You can also select an entire object to select all of its fields at once. Update key(s) are the field(s) used to identify which existing records need to be updated. Any fields that are selected to be used as update keys will be listed on the right.
  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Step 3 – Review Data Schema

  • Data Schema: The target data schema is displayed. If the operation uses a transformation, the data schemas will be displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

    NOTE: Data will be transferred as provided. If there are data length mismatches between source and target fields, you can use String Functions to trim the data.
  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Finished: Click to save the configuration for all steps and close the activity configuration.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Multiple-Object Update or Upsert

Follow these steps if the database update or upsert is to multiple objects, and you need to create joins between them. To update or upsert only a single object, without joins, refer to Single-Object Update or Upsert.

Step 1 – Enter Basic Information and Select Object

  • Name: Enter a name to use to identify the database activity. The name must be unique for each database update or upsert activity and must not contain forward slashes (/) or colons (:).
  • Provide the Table Names Reference: This section displays the tables available in the database endpoint using any applied filters, after using the refresh icon  for the initial loading of objects. If there are a large number of objects in the database, it is recommended to filter the objects using View or Search prior to refreshing.
    • View: Use the dropdown to filter the list of objects to tables, views, or system tables. By default, tables and views are shown.

    • Search: Enter any part of the object name into the search box to filter the list of objects. The search is not case-sensitive. The list of objects may be filtered automatically or you may need to refresh the list, depending on whether the objects have already been retrieved:

      • If searching on objects that have already been retrieved from the database and already appear within the list, any search terms typed within the search box will automatically filter the list.

      • If searching on objects that have not yet been retrieved, for example when attempting to limit the number of objects retrieved for databases with a large number of objects, use the refresh icon next to the search box to fetch the objects from the database using the filter.

      CAUTION: For databases that do not support searching on objects, such as Microsoft Access, no results will be returned.
    • Refresh: Click the refresh icon  to load or reload tables from the database endpoint. This must be used for the initial loading of any objects. In addition, it may also be useful if you have recently added objects to the database, or if you have additional search criteria that you wish to apply prior to retrieving database objects.

    • Include the schema/owner in the table names: Select this checkbox to include the schema in the table names. This may be useful if you use the same table name in multiple schemas (such as for development and production) to allow you to see and select the table in the correct schema.
    • Provide the Table Names Reference: The list of objects is displayed in a column on the left. Click on objects one at a time to add them to the table on the right. Any combination of tables, views, and/or system tables may be selected. If instead you need to update or upsert a single object without joins, refer to Single-Object Update or Upsert.

      TIP: If the list does not populate with available objects even after applying a filter, the database connection may not be successful. Ensure you are connected by reopening the connection and retesting the credentials.
    • Table of Selected Objects: The selected objects are displayed in a table on the right. Each column is detailed below.

      • Table: The name of the selected object, including table, view, or system table.

      • Parent: On each child object being joined, use the dropdown to select the name of the object that should be the parent. This is needed to enforce transactional integrity of an object as well as to derive the link key(s) for a child object. 

      • Join Type: On each child object being joined, a dropdown will become available after you have assigned Link Keys (covered next). Use the dropdown to set the type of relationship between the selected objects:
        • One or More: This join type requires that every parent record has at least 1 child record, and possibly more child records.
        • One Only: This join type requires that every parent record has 1 and only 1 child record.
        • Zero or More: This join type allows every parent record to have either 0 or more child records.
        • Zero or One: This join type allows every parent record to have either 0 child records or only 1 child record.
      • Link Keys: On each child object being joined, when an object is selected as the Parent, a red Assign link appears within this field. Click Assign to open a separate window where you assign the link keys:

        • Parent Object: The parent object and list of fields is displayed in the left column. Any fields used as primary keys within the database will be indicated by a key icon . If you have a lot of fields, you can also use the search box to filter fields by name.

        • Child Object: The child object and list of fields is displayed in the right column. If you have a lot of fields, you may also use the search box to filter fields by name.

        • Assign Link Key(s): To assign a field as a link keys, drag a field from the parent object on the left to a field within the child object on the right, or vice versa. If you would like to assign multiple link keys, repeat this process.

        • Unassign Link Key(s): To unassign link keys, click on any field that has already been linked. The linked fields will become deselected.

        • Start Over: To clear all assigned link keys, click the Start Over link located below the parent list on the left.
        • Finish: Click the Finish button to save the assigned link keys and close the window. Under Link Keys, you should now see the number of assigned link keys, which you can click to reopen the link key assignment window. In addition, the Join Type dropdown becomes available for you to set the type of relationship between the selected objects:

      • Remove: To remove a selected object from the table, hover over the far right column and click the trash icon .
  • Save & Exit: If enabled, click to save the configuration for this step and close the activity configuration.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Step 2 – Select Update Keys

  • Select Keys: Select the checkboxes of any fields you want to use as update keys. You can also select an entire object to select all of its fields at once. Update key(s) are the field(s) used to identify which existing records need to be updated. Any fields that are selected to be used as update keys will be listed on the right.
  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Next: Click to temporarily store the configuration for this step and continue to the next step. The configuration will not be saved until you click the Finished button on the last step.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Step 3 – Review Data Schema

  • Data Schema: The target data schema is displayed. If the operation uses a transformation, the data schemas will be displayed again later during the transformation mapping process, where you can map to target fields using source objects, scripts, variables, custom values, and more.

  • Back: Click to temporarily store the configuration for this step and return to the previous step.
  • Finished: Click to save the configuration for all steps and close the activity configuration.
  • Discard Changes: After making changes, click Discard Changes to close the configuration without saving changes made to any step. A message will ask you to confirm that you want to discard changes.

Next Steps

After configuring a database update or upsert activity, you can use it within an operation or script as described below. You may also want to configure chunking to split the data into smaller chunks for processing.

Completing the Operation

After configuring a database update or upsert activity, complete the configuration of the operation by adding and configuring other activitiestransformations, or scripts as operation steps (see Operation Creation and Configuration). You can also configure an operation's operation settings, which include the ability to chain operations together that are in the same or different workflows (see Operation Settings).

Database update or upsert activities can be used as a target with these operation patterns:

Other patterns are not valid using database update or upsert activities. See the validation patterns on the Operation Validity page.

This example setup depicts one common operation setup using a database update or upsert activity:

Within a transformation, if using the option to mirror a schema provided by a database activity using a single table (without joins), the resulting mirrored schema will automatically be created with an additional node called row. When fields within this additional node are mapped to, the node becomes a loop node to allow all records to be looped through (see Loop Nodes under Nodes and Fields).

When ready, deploy and run the operation (see Operation Deployment and Execution) and validate behavior by checking the operation logs (see Operation Logs).

Using Database Activities in Scripts

Database update or upsert activities that have already been configured as part of an operation can also be referenced in a script for use with certain script functions that use a database target as a parameter. For example:

To add a configured database activity to a script to be referenced by one of these functions, drag the configured activity from the Endpoints tab of the script component palette to the script. Or, if you already know the function you want to use, add it from the Functions tab first; then position the cursor after the opening parenthesis of the function and press Control+Space to display a list of autocomplete suggestions. Select an activity to insert the activity reference into the script. For more details on referencing activities in scripts, see Endpoints on the Jitterbit Script page.

In addition, you may be able to use additional database functions in the transformation. For example, in mapping to a database target, if you have a multi-table target with parent-child relationships and you are updating or upserting data, you can use the transformation functions <SQLIDENTITY> and <SEQUENCE>.

  • <SQLIDENTITY> is used for all non-Oracle target databases that support identity or automatic number generating keys. After the parent is inserted, the child can inherit the generated number from the parent by mapping this function to the foreign key in the child table.
  • <SEQUENCE> performs a similar action with Oracle databases.

Using Chunking

When using chunking on an operation where the target is a database, note that the target data will first be written to numerous temporary files (one for each chunk). These files will then be combined to one target file, which will be sent to the database for insert/update.

If you set the Jitterbit variable jitterbit.target.db.commit_chunks to 1 or true when chunking is enabled, each chunk will instead be committed to the database as it becomes available. This can improve performance significantly as the database insert/updates will be performed in parallel.

For instructions and best practices on using chunking, see Operation Options.

On This Page

Last updated:  Jan 23, 2020

  • No labels