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. Novell 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.
To enable macro expression query preprocessing:
In the
pane, expand > > .Right-click an adapter, then select
to open the Data Integrator Editor.In the
pane, click the top node in the adapter hierarchy.In the right pane, click the
icon to display the adapter properties:Click the
check box next to .This enables macro expression query preprocessing. It is disabled by default.
In the true.
field next to , enterClose the Data Integrator Editor.
When prompted to save changes, click
.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);
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
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
column from the table where the 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:
In the Data Integrator Editor
pane, select the adapter definition root element.In the right pane, click the
icon to display the property names and values.Click the (
) check box to enable the property.Click the (
) check box to display the adapter property for the run-time adapter instance.This allows administrators to modify the property value.
Leave the
check box deselected to activate the adapter property, but not display it for the adapter instance.Specify a name in the
field.The field cannot be edited if the
check box is deselected.Specify a default property value in the
field.Close the Data Integrator Editor.
When prompted to save your changes, click
.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