Selecting property value with a hook and provide it to a rule

Overview

This guide describes, how information that is stored in the database can be used in a rule. Furthermore, it shows, how information stored in report can be used in an hook.

In our example, we want to set the start value an IDENTITY column automatically for newly created tables, based on the maximum value of the identity column in the source table.

This is necessary when creating the structure (DDL: tables, indices, references, etc.) of an SQL Server database containing approximately 30 tables into a new database without copying the corresponding data. The main objective is to ensure that synthetic data generated in the new database does not conflict with selectively loaded data from the source system. We use a structure compare task to archive this.

To achieve this, the IDENTITY column of each table in the target database must start at the maximum value found in the source table plus one. This process should be repeatable, accommodating changes in table count and record numbers without requiring manual adjustments.

Prerequisites

To implement this approach, the following components are required:

An SQL Server database containing source tables with IDENTITY columns.

Process Steps

We will create a task stage hook to identify the IDENTITY columns, to determine the maximum value of this column and to store the corresponding start value in the runtime properties.

Determine selected tables from a report

First, we have to identify the tables selected in Stage 1 of the task. For this we use the objectSelectorTableReport . For every table listed in the report the identity column will be identified. The complete hook can be found here.

var report = Packages.org.apache.commons.io.FileUtils.readFileToString(new File (properties.get('root_dir') + "/reports/Stage1_0_objectSelectorTableReport_Source.rpd"));
var reportData = Packages.de.ubs.xdm.utils.reportingutils.ReportUtils.deserializeReportData(report);

for (i = 0; i < reportData.getRowCount(); i++) {
    tableSchema = reportData.getRow(i)[2];
    tableName = reportData.getRow(i)[3];
    query1 = "SELECT COLS.name AS COLNAME FROM sys.identity_columns COLS INNER JOIN sys.tables TABS ON COLS.object_id=TABS.object_id INNER JOIN sys.schemas SCH ON SCH.schema_id=TABS.schema_id WHERE (SCH.name = '" + tableSchema + "') AND (TABS.name = '" + tableName + "') AND COLS.is_computed=0 ORDER BY COLS.column_id;"
    pStmt1 = taskStageHook.connection.jdbcConnection.prepareStatement(query1);
    rs1 = pStmt1.executeQuery();
    ...
}

Select maximum value and store start value

Afterward, we have to select the maximum value of the identity column, calculate the start value and store the value as a runtime property. If no data is in the table, the value in the runtime property is set to 1.

    if (rs1.next()) {
          column = rs1.getString("COLNAME");
          query2 = 'SELECT MAX(' + column + ') AS VALUE FROM ' + tableSchema +'.' + tableName;
          pStmt2 = taskStageHook.connection.jdbcConnection.prepareStatement(query2);
          rs2 = pStmt2.executeQuery();
          if (rs2.next()) {
                 result = rs2.getString("VALUE");
                 print("Max value for identity column in table " + tableSchema + "." + tableName + ": " + result);
                 if (result != null) {
                 startValue = parseInt(result) + 1;
                 } else {
                     startValue =1;
                 }
                 print("Start value for identity column in table " + tableSchema + "." + tableName + ": " + startValue);
                 properties.put(tableName, startValue);
                 }
          }

Define a Database Specific Mapping Rule

The stored property is used in a mapping rule.

This rule applies to Microsoft SQL Server to set the IDENTITY column start value in the target database.

The rule utilizes JavaScript to fetch the stored runtime property and apply it during the mapping process. It is defined as follows:

Field Selection Mode

Database specific

Object Type

IDENTITY_COLUMNS

Field Type

SEED_VALUE

Source selection pattern

%

Target value mode

Java script

Target Value

Java script code

The following JavaScript snippet retrieves the runtime property and applies it to the corresponding table:

importPackage(Packages.de.ubs.xdm.utils.databasemodel.model.db);
tableName = baseObject.columnProvider.getName(PropertyObject.ApplyHooks.NO);
outputValue = Packages.de.ubs.xdm.utils.core.RuntimePropertiesUtil.get(tableName, "");

This implementation assumes that each table has a unique name and only one IDENTITY column. If multiple IDENTITY columns exist or table names are not unique, both the hook and mapping rule must be adjusted accordingly.

Benefits

Automation: The process eliminates the need for manual adjustments when the database structure or record count changes.

Consistency: Ensures that new synthetic data does not overlap with selectively loaded data from the source system.

Repeatability: The process can be executed multiple times without additional effort.

Minimal Configuration: No need to pre-extract and manually adjust maximum values.

Conclusion

This approach provides an efficient and automated method to copy SQL Server database structures while ensuring IDENTITY column consistency. By leveraging XDM Task Stage Hooks and Database Specific Mapping Rules, manual intervention is minimized, making the process scalable and adaptable to future changes.