6.7 Using Macro Expression Query Preprocessing

Macro expression query preprocessing is available in Data Integrator for added flexibility in creating query text. Macro expressions allow embedding control statements and variable substitution in query statements. Operations Center software uses Velocity, which is a third party package provided by the Apache Organization that is bundled into Operations Center for macro expressions. Documentation for Velocity is available at http://jakarta.apache.org/velocity.

One example of a commonly used query is tracking the highest value entered for a computed property. The following example assumes HighestCustomerID is a computed property that tracks the highest customer ID queried. The first query selects all CustomerID values where the CustomerID is greater than 0.

select CustomerID from CustomerInfo where
#if ( ${query.initial} )
    CustomerID > 0
#else
    CustomerID > ${query.property.HighestCustomerID}
#end

The second and subsequent queries select only CustomerID values greater than the HighestCustomerID. If new CustomerID values always increase, this query only selects new CustomerID values during subsequent queries, instead of requerying the entire table for each CustomerID during each scheduled query.

Macro expression query preprocessing is the preferred method for Data Integrator execution. NetIQ recommends not using the older delta query mechanism using '{<COMPUTED PROPERTY NAME>::<INITIAL VALUE>}' and might remove it from a future version of Data Integrator. However, during this transition period, to allow for backwards compatibility, the old Data Integrator delta query mechanism is the default for query preprocessing, unless Macro expression query preprocessing is enabled, as described below.

6.7.1 Enabling Macro Expression

To enable macro expression query preprocessing:

  1. In the Explorer pane, expand Administration > Adapters > Data Integrator.

  2. Right-click an adapter, then select Edit Definition to open the Data Integrator Editor.

  3. In the Definition Navigator pane, click the top node in the adapter hierarchy.

  4. In the right pane, click the Properties icon to display the adapter properties:

  5. Click the Activate check box next to Use Macro Expressions.

    This enables macro expression query preprocessing. It is disabled by default.

  6. In the Default Value field next to Use Macro Expressions, enter true.

  7. Close the Data Integrator Editor.

  8. When prompted to save changes, click Yes.

6.7.2 Understanding Macro Expression Variables

The following variable values are predefined and can be referenced from within a macro expression preprocessed query on the Operations Center server and from the Data Integrator Definition Editor:

  • ${query.initial}: True the first time the query is run; otherwise, False.

  • ${query. macroExpressionn}: The value of the Macro Expression adapter property, where n is between 1 and 15.

    For more information, see Section 6.7.3, Defining Default Macro Expression Adapter Values.

  • ${query.integration.dbHost}: The value of the database connection host.

  • ${query.integration.dbPort}: The value of the database connection port.

  • ${query.integration.dbUser}: The value of the database connection user.

  • ${query.integration.dbType}: The value of the database connection type.

The following predefined values can be referenced from within a macro expression preprocessed query on the Operations Center server only:

  • ${query.property.Computed_Property_Name}: The value of the Computed Property named in Computed_Property_Name. For example:

    ${query.property.HighestCustomerID}

  • ${query.integration.Integration_Property_Name}: The value of the integration property named in Integration_Property_Name. For example:

    ${query.integration.dbType}

  • ${query.control.Control_Property_Name}: The value of the integration property named in Integration_Property_Name. For example:

    ${query.control.statusMessage}

  • ${query.integrationInstance}: The Data Integrator integration object instance.

  • ${query.controlInstance}: The Data Integrator control object instance.

  • ${query.controlInstance.fail(message)}: Causes the Data Integrator integration to fail with the reason provided using the message argument. For example, on a SQL server, to stop your configuration from your SQL code:

    RAISERROR('${query.controlInstance.fail("Invalid Configuration: ${query.macroExpression4}")}',18,1);

6.7.3 Defining Default Macro Expression Adapter Values

After enabling macro expressions, it is possible to define up to 15 adapter properties that macro expression directives in the query text can reference, as illustrated in Figure 6-3:

Figure 6-3 Adapter Properties

The previous figure defines five adapter properties. The first three properties have selected Add to Property Page check boxes, which means these properties display in the adapter property pages and can be modified. The last two properties are enabled using the default values, but do not display in adapter property pages and cannot be modified.

If macro expression processing is enabled, it is possible to reference these adapter properties from within queries, such as ${query. macroExpressionn} where n is the macro expression property index from 1 to 15.

For example, the following statement selects the CustomerID column from the CustomerInfo table where the CustomerID value is between 50 and 100:

Select CustomerID from ${query.macroExpression1} where CustomerID > ${query.macroExpression4} AND CustomerID < ${query.macroExpression5}

If the administrator changes the Customer Information Table adapter property to a different value, then it selects from that table instead.

To define the adapter properties:

  1. In the Data Integrator Editor Definition Navigator pane, select the adapter definition root element.

  2. In the right pane, click the Macro icon to display the property names and values.

  3. Click the (Activate) check box to enable the property.

  4. Click the (Add to Property Page) check box to display the adapter property for the run-time adapter instance.

    This allows administrators to modify the property value.

    Leave the Add to Property Page check box deselected to activate the adapter property, but not display it for the adapter instance.

  5. Specify a name in the Display Name field.

    The field cannot be edited if the Add to Property Page check box is deselected.

  6. Specify a default property value in the Default Value field.

  7. Close the Data Integrator Editor.

  8. When prompted to save your changes, click Yes.

6.7.4 Filtering Data Based on Date

A common application of macro expressions is filtering data most recently added to the database, such as data entered in the last x hours or days.

In this example, upon the initial query, the adapter pulls in all new data entered into the database within the last hour. If no data was entered in the last hour, it uses the Last Time Stamp property to determine the last time stamp value read from the database.  Each subsequent query of the database reads only the new data that was entered in the database with a time stamp greater than the time stamp recorded by the last query.

The following macro expression is in the WHERE clause of the Alarm query:

dbo.measurements.metric_id = dbo.metrics.metric_id and
dbo.measurements.metric_when >
#if ( ${query.initial} )
dateadd(hour, -1, getdate())
#else
'${query.getProperty().lastTimeStamp}'
#end