Error: Invalid spaceKey on retrieving a related space config.

AR Upsert step

The AR Upsert step uses the Checksum mechanism to compare the source record and the target record to determine whether or not to import the source record. The AR Upsert step also performs the tasks mentioned below: 

  • Connects to the AR System server using the connection input parameters. A connection is created once and reused.
  • Substitutes the batch commit size and qualifications with real values, if variables are used.
  • Checks whether to start a new batch based on the flag, if the commit size is provided. This flag is true by default. After a transaction is started, the flag changes to false. Once the transaction is committed, the flag is reset to true.
  • Retrieves the input row from the previous step using the Atrium Integrator get row API function.
  • Converts the Atrium Integrator input row to an AR System form entry using the Mapping Field list. The Atrium Integrator row data type is converted to the AR System data type. After the value is converted to the corresponding AR System data type, a second-level conversion is required if the field's data type does not match the converted value's data type. For example, when the field has selection or enum data type, the Atrium Integrator performs second level of conversion if the mapped field has a string value. The data type conversions are listed here.
  • Parses the matching qualifications and substitutes the values of stream fields from the input row in the qualification object.
  • Uses the qualification object to retrieve IDs for the matching entries.
  • Checks whether it is time to commit the transaction based on the record number, if a bulk transaction is started. If it is, then calls the end bulk entry transaction API and sets the start bulk entry transaction flag to true.

The following screenshot illustrates the AR Upsert step in the Spoon client:


Overview of Checksum mechanism

You can configure a Checksum value for a job transformation in AR Upsert step. This is used in a job transformation with other Input step, which obtains data from the external source.

The AR Upsert step uses the Secure Hash Algorithm (SHA) logic to calculate a Checksum value for all the fields you have mapped in the Filed Mapping tab of AR Upsert step. The AR Upsert step does not perform field-by-field comparison while updating records in a BMC Remedy AR System form. It refers to the Checksum value and decides whether or not to update the record. This steps improves the performance by eliminating unnecessary calls to the AR System server to insert or update records in a form.

You must add a checksum field with the field ID 304412001 to your form to use the checksum feature.

The Checksum computation is based on the following conditions:

ResultCondition
Entry in a form in updated
  • When the Checksum value of the input row does not match with the Checksum value  present on the staging or target form.
  • When the Checksum value is not present on the staging or target form.
A new entry is added in a formWhen the Checksum flag is checked and an entry does not exist in the staging or target form.
No operation is performedWhen the Checksum flag is checked and when an entry exists in the staging or target form. The Checksum value of the input row matches with the Checksum value present on the staging or target form.

General tab

The General tab in the AR Upsert step allows you set connection and values for available fields. The following screenshot illustrates the General tab.

The General tab has the following fields:

FieldDescription
Connection Click the New button to create a new AR System server connection. Click the Edit button to edit an existing connection. Select an existing connection from the drop down menu.Click Wizard for a step-by-step guided procedure to create a new connection.
Form NameName of the AR System form where you output the data. This also works as a staging form when you provide the Alternate Target Form Name.
Use Checksum CheckboxPerforms checksum comparison in update mode.
Alternate Target Form Name

Specify when the target form is different from the staging form. 

When you provide the Alternate Target Form Name in an AR Upsert step, the operations are performed on the staging form. When you update any record, an entry is inserted into the staging form.  

The Alternate Target form is used for Checksum comparison.

Example:
When you push data into the staging form and that data is validated and promoted to the target from using a mechanism chosen by the Data Management administration. For example, when you push the data into the CTM: LoadPeople form, where the data is validated before moving to target form CTM: People form.
The data in the CTM: LoadPeople form is purged over the period of time so that the last Checksum value is lost and not available for the next Data Load activity.

In such scenarios you can specify CTM: People as an Alternate Target Form. When AR Upsert gets the same record for processing, it does comparison based on last Checksum value from CTM: People against the input record Checksum. If checksum is matched, then the record is not imported to CTM: LoadPeople, if the record does not match, then it creates a new record in CTM: LoadPeople form.

Batch Commit Size

(Optional) When you specify a Batch Commit Size, the bulk API import the data. You can specify the Batch Commit Size as variable. When you specify the Batch Commit Size as a variable, the AR Upsert step uses the actual value provided.

Best Practice:

When the source data has large number of duplicate records, BMC recommends either of the following:  

  • Use step that has the filtering capability, such as Filtering unique records, which filters the duplicate records before calling AR Upsert step or AROutput step.
  • Keep the batch size smaller that can be compared with the unique records.
Fallback to single row commit if batch commit fails Use this option to switch to a single-row commit if batch-commit fails.
Insert OnlyCreates a new record if an entry does not exist in AR System form. Skips the record, if an entry already exists in an AR System form. The Insert Only mode ignores the Use Checksum flag and adds a new entry.
Update RecordPerforms an update as well as insert operation, based on the Checksum conditions.
Match By Request ID

Select to match output rows by Request ID. When you select the Match by Request ID checkbox, a new entry is created when the Request ID is not provided for the input row.

Multi Match OptionSelect when more than one record can matche the given qualification. 
Configure Matching QualificationClick to configure a matching qualification in the qualification helper dialog.

Field Mapping tab

You can map  the output fields from previous steps, or stream fields, to their respective AR System form fields, using the Field Mapping tab.  You can also exclude a particular field from computing checksum. By default all fields are included in the checksum calculation.

Best Practice

BMC recommends that you exclude the fields that participates in the qualification and the fields that are known to have a unique value for the Checksum computation.
The following screenshot illustrates the Field Mapping tab in the AR Upsert step: 



The Edit Mapping button provides a helper dialog to map the fields.

The following screenshot illustrates the Enter Mapping dialog from the Field Mapping tab: 

The following table shows AR Upsert data type conversions:

Atrium Integrator adapter data type

AR System data type

None

Null

Number

Real

String

Char

Date

Date

Boolean

Enum

Integer

Ulong

Bignumber

Decimal

Binary

Attach

Related topics

ARInput step

AROutput step

ARX file input step

This mechanism is supported from BMC Atrium Core version 9.1.03 and later. The AR Upsert step uses the Checksum mechanism to compare the source record and the target record to determine whether or not to import the source record. The AR Upsert step also performs the tasks mentioned below: 

  • Connects to the AR System server using the connection input parameters. A connection is created once and reused.
  • Substitutes the batch commit size and qualifications with real values, if variables are used.
  • Checks whether to start a new batch based on the flag, if the commit size is provided. This flag is true by default. After a transaction is started, the flag changes to false. Once the transaction is committed, the flag is reset to true.
  • Retrieves the input row from the previous step using the Atrium Integrator get row API function.
  • Converts the Atrium Integrator input row to an AR System form entry using the Mapping Field list. The Atrium Integrator row data type is converted to the AR System data type. After the value is converted to the corresponding AR System data type, a second-level conversion is required if the field's data type does not match the converted value's data type. For example, when the field has selection or enum data type, the Atrium Integrator performs second level of conversion if the mapped field has a string value. The data type conversions are listed here.
  • Parses the matching qualifications and substitutes the values of stream fields from the input row in the qualification object.
  • Uses the qualification object to retrieve IDs for the matching entries.
  • Checks whether it is time to commit the transaction based on the record number, if a bulk transaction is started. If it is, then calls the end bulk entry transaction API and sets the start bulk entry transaction flag to true.

The following screenshot illustrates the AR Upsert step in the Spoon client:


Overview of Checksum mechanism

You can configure a Checksum value for a job transformation in AR Upsert step. This is used in a job transformation with other Input step, which obtains data from the external source.

The AR Upsert step uses the Secure Hash Algorithm (SHA) logic to calculate a Checksum value for all the fields you have mapped in the Filed Mapping tab of AR Upsert step. The AR Upsert step does not perform field-by-field comparison while updating records in a BMC Remedy AR System form. It refers to the Checksum value and decides whether or not to update the record. This steps improves the performance by eliminating unnecessary calls to the AR System server to insert or update records in a form.

You must add a checksum field with the field ID 304412001 to your form to use the checksum feature.

The Checksum computation is based on the following conditions:

ResultCondition
Entry in a form in updated
  • When the Checksum value of the input row does not match with the Checksum value  present on the staging or target form.
  • When the Checksum value is not present on the staging or target form.
A new entry is added in a formWhen the Checksum flag is checked and an entry does not exist in the staging or target form.
No operation is performedWhen the Checksum flag is checked and when an entry exists in the staging or target form. The Checksum value of the input row matches with the Checksum value present on the staging or target form.

General tab

The General tab in the AR Upsert step allows you set connection and values for available fields. The following screenshot illustrates the General tab.

The General tab has the following fields:

FieldDescription
Connection Click the New button to create a new AR System server connection. Click the Edit button to edit an existing connection. Select an existing connection from the drop down menu.Click Wizard for a step-by-step guided procedure to create a new connection.
Form NameName of the AR System form where you output the data. This also works as a staging form when you provide the Alternate Target Form Name.
Use Checksum CheckboxPerforms checksum comparison in update mode.
Alternate Target Form Name

Specify when the target form is different from the staging form. When you provide the Alternate Target Form Name in an AR Upsert step, the operations are performed on the staging form. When you update any record, an entry is inserted into the staging form.  

The Alternate Target form is used for Checksum comparison. Consider the following scenario:
When you push data into the staging form and that data is validated and promoted to the target from using a mechanism chosen by the Data Management administration. For example, when you push the data into the CTM: LoadPeople form, where the data is validated before moving to target form CTM: People form.
The data in the CTM: People form is purged over the period of time so that the last Checksum value is lost and not available for the next Data Load activity.

In such scenarios you can specify CTM: People as an Alternate Target Form. When AR Upsert gets the same record for processing, it does comparison based on last Checksum value from CTM: People against the input record Checksum. If checksum is matched, then the record is not imported to CTM: LoadPeople, if the record does not match, then it creates a new record in CTM: LoadPeople form.

Batch Commit Size

(Optional) When you specify a Batch Commit Size, the bulk API import the data. You can specify the Batch Commit Size as variable. When you specify the Batch Commit Size as a variable, the AR Upsert step uses the actual value provided.

Best Practice:

When the source data has large number of duplicate records, BMC recommends either of the following:  

  • Use step that has the filtering capability, such as Filtering unique records, which filters the duplicate records before calling AR Upsert step or AROutput step.
  • Keep the batch size smaller that can be compared with the unique records.
Fallback to single row commit if batch commit fails Use this option to switch to a single-row commit if batch-commit fails.
Insert OnlyCreates a new record if an entry does not exist in AR System form. Skips the record, if an entry already exists in an AR System form. The Insert Only mode ignores the Use Checksum flag and adds a new entry.
Update RecordPerforms an update as well as insert operation, based on the Checksum conditions.
Match By Request ID

Select to match output rows by Request ID. When you select the Match by Request ID checkbox, a new entry is created when the Request ID is not provided for the input row.

Multi Match OptionSelect when more than one record can matche the given qualification. 
Configure Matching QualificationClick to configure a matching qualification in the qualification helper dialog.

Field Mapping tab

You can map  the output fields from previous steps, or stream fields, to their respective AR System form fields, using the Field Mapping tab.  You can also exclude a particular field from computing checksum. By default all fields are included in the checksum calculation.

Best Practice

BMC recommends that you exclude the fields that participates in the qualification and the fields that are known to have a unique value for the Checksum computation.
The following screenshot illustrates the Field Mapping tab in the AR Upsert step: 



The Edit Mapping button provides a helper dialog to map the fields.

The following screenshot illustrates the Enter Mapping dialog from the Field Mapping tab: 

The following table shows AR Upsert data type conversions:

Atrium Integrator adapter data type

AR System data type

None

Null

Number

Real

String

Char

Date

Date

Boolean

Enum

Integer

Ulong

Bignumber

Decimal

Binary

Attach

Related topics

ARInput step

AROutput step

ARX file input step

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Jean Christophe

    Can we give an example on how the checksum decides if records are to be imported or not? At the moment its not quite clear HOW this should work

    "It refers to the Checksum value and decides whether or not to update the record" do we know how it decides this?

     

     

    May 15, 2017 06:26
  2. Mohamed Atta

    the same here, could you please give some examples on how the checksum decides if records are to be imported or not?

    Jan 29, 2018 01:12
    1. Anagha Deshpande

      Hello Mohamed,

      I will check with the SME and will write back to you.

      Regards,

      Anagha

      Jan 29, 2018 09:07
      1. Anagha Deshpande

        Hello Mohamed,

        Apologies for the delayed response.

        We have added an example to the topic.

        Regards,

        Anagha

        Jun 24, 2019 11:03