Database update or Upsert activity

Introduction

A Database Update activity updates existing data in a Database endpoint, while a Database Upsert activity both updates existing data and inserts new data in a Database endpoint. Both are intended to be used as a target to consume data in an operation or to be called in a script. (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.

Create a database activity

An instance of an activity is created from a connection using an activity type.

To create an instance of an activity, drag the activity type to the design canvas or copy the activity type and paste it on the design canvas. For details, see Creating an activity instance in Component reuse.

An existing activity can be edited from these locations:

• The design canvas (see Component actions menu in Design canvas).
• The project pane's Components tab (see Component actions menu in Project pane Components tab).

Configure a database update or Upsert activity

The steps for configuring a Database Update or Upsert activity 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 instead to Multiple-object update or upsert.

• Step 1: Enter a name and select an object
• Step 2: Select update keys
• Step 3: Review the data schema

Step 1: Enter a name and select an 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 displayed objects to one of "Tables, Views", "Tables", "Views", or "System Tables". By default, the view "Tables, Views" is shown.

  • Search: Enter any column's value 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 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.

    Note

    For databases that do not support searching on objects, such as Microsoft Access, no results are 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 a single object to add it to the table on the right. If 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 to close the configuration without saving changes made to any step. A message asks 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 are 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 to close the configuration without saving changes made to any step. A message asks you to confirm that you want to discard changes.

Step 3: Review the data schema

• Data Schema: The target data schema is displayed. If the operation uses a transformation, the data schemas are 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 is transferred as it is provided. If there are data length mismatches between source and target fields, you can use String functions to trim the data.

  Note

  To refresh a data schema in an existing activity, you must advance through each activity configuration step again, making at least one change (such as adding and removing a character from the end of the name of the activity) to force a refresh of the schema.

• 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 to close the configuration without saving changes made to any step. A message asks 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 to single object, without joins, refer to Single-object update or upsert.

• Step 1: Enter a name and select objects
• Step 2: Select update keys
• Step 3: Review the data schema

Step 1: Enter a name and select objects

• 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 displayed objects to one of "Tables, Views", "Tables", "Views", or "System Tables". By default, the view "Tables, Views" is shown.

  • Search: Enter any column's value 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 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.

    Note

    For databases that do not support searching on objects, such as Microsoft Access, no results are 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 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 instead 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, a 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 becomes 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, once 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 are indicated by a key icon . If you have a large number of fields, you can 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 large number of fields, you can use the search box to filter fields by name.

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

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

      • Start Over: To clear all assigned link keys, click Start Over located below the parent list on the left.

      • Finish: Click Finish 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 (cardinality) between the selected objects:

        

    • Remove: To remove a selected object from the table, hover over the rightmost column and click the remove 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 to close the configuration without saving changes made to any step. A message asks 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 are 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 to close the configuration without saving changes made to any step. A message asks you to confirm that you want to discard changes.

Step 3: Review the data schema

• Data Schema: The target data schema is displayed. If the operation uses a transformation, the data schemas are 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

  To refresh a data schema in an existing activity, you must advance through each activity configuration step again, making at least one change (such as adding and removing a character from the end of the name of the activity) to force a refresh of the schema.

• 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 to close the configuration without saving changes made to any step. A message asks 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.

Complete the operation

After configuring a Database Update or Upsert activity, complete the configuration of the operation by adding and configuring other activities, transformations, or scripts as operation steps. 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.

Once a Database Update or Upsert activity has been created, menu actions for that activity are accessible from the project pane in either the Workflows or the Components tabs, and from the design canvas. See Activity actions menu for details.

Database Update or Upsert activities can be used as a target with these operation patterns:

• Transformation pattern
• Two-transformation pattern (as the second target only)

Other patterns are not valid using Database Update or Upsert activities. See the validation patterns on the Operation validity page.

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 is automatically created with an additional node named 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).

In addition, if joining database tables, note that the values of a child table's joined fields default to the values of the parent table's joined fields. A child table's joined fields cannot be mapped to.

When ready, deploy and run the operation and validate behavior by checking the operation logs.

Using database activities in scripts

Database Update or Upsert activities can also be referenced in a script for use with script functions that use a database target as a parameter, including these:

• DBLoad
• DBWrite

For more details on referencing activities in scripts, see Endpoints in Jitterbit Script.

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.

Use chunking

When using chunking on an operation where the target is a Database activity, note that the target data is first written to numerous temporary files (one for each chunk). These files are then combined to one target file, which is 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 is instead be committed to the database as it becomes available. This can improve performance significantly as the database insert/updates are performed in parallel.

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