Home » Tech Reference » Update Procedures

MetaMatrix Features

MetaMatrix Update Procedures

If you are using MetaMatrix Enterprise, you can perform update operations against virtual tables. The transformation logic for defining how these updates are decomposed into INSERT, UPDATE, and DELETE commands against tables underlying the virtual table are defined in update procedures.

The MetaMatrix procedure language is used in conjunction with SQL when defining update procedures using MetaMatrix Designer.



Overview

Virtual tables are abstractions above physical sources. They typically union or join information from multiple tables, often from multiple data sources or other views. Update commands - INSERT, UPDATE, or DELETE - against a virtual table require logic to define how the tables and views integrated by the virtual table are affected by each type of command. This transformation logic is invoked when an update command is issued against a virtual table. Update procedures define the logic for how a user's update command against a virtual table should be decomposed into the individual commands to be executed against the underlying physical sources.

Similar to virtual procedures, update procedures have the ability to execute queries or other commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.

Update Procedure Definition

Update procedures are defined in MetaMatrix Designer using the MetaMatrix procedure language. Within the transformation of a virtual table, an update procedure has the following basic form:

Basic Structure of an Update Procedure
CREATE PROCEDURE BEGIN <...> END

The CREATE PROCEDURE line demarcates the beginning of the procedure.

The BEGIN and END keywords are used to denote block boundaries. If there is more than one statement in the procedure, then the statements must be enclosed within BEGIN and END tags.

Within the body of the procedure, any valid statement may be used.


Special Variables

You can use a number of special variables when defining your update procedure.

INPUT Variables

Every attribute in the virtual table whose transformation you are defining has an equivalent variable named INPUT.<column_name>. For example, a table that has attributes named isbn, title, and author will have the following "well-known" variables that can be referenced in the update procedure:

  • INPUT.isbn
  • INPUT.title
  • INPUT.author

When the user application executes an INSERT or an UPDATE command against the virtual table, the MetaMatrix System initializes these variables to the values in the INSERT VALUES clause or the UPDATE SET clause in the SQL query.

In an UPDATE or DELETE procedure, the default value of these variables, if they are not set by the command, is null. In an INSERT procedure, the default value of these variables is the default value of the virtual table attributes, based on their defined types.

CHANGING Variables

Similar to the INPUT variables, the MetaMatrix System also creates by default the CHANGING variables. These are Boolean flags of the form CHANGING.<column_name>. These flags are set in response to the user's query against the virtual table.

For example, for a virtual table with columns A, B, C:

If User Executes...Then...
INSERT INTO VT (A, B) VALUES (0, 1) CHANGING.A = true, CHANGING.B = true, CHANGING.C = false
UPDATE VT SET C = 2 CHANGING.A = false, CHANGING.B = false, CHANGING.C = true

This allows you to first know what the command against the virtual layer is doing, and second to differentiate between an INPUT value's null set because user passed null and a null set because user omitted the column from the virtual command entirely.

ROWS_UPDATED Variable

The MetaMatrix System returns the value of the ROWS_UPDATED variable to the user application as a response to an update command executed against the virtual table. Your procedure must set the value that returns when an application executes an update command against the virtual table, which triggers invocation of the update procedure. For example, if an UPDATE command is issued that affects 5 records, the ROWS_UPDATED should be set appropriately so that the user will receive '5' for the count of records affected.


Handling SQL Criteria in a User’s Command

You can use a number of special SQL clauses when defining UPDATE or DELETE procedures. These make it easier to do variable substitutions in WHERE clauses or to check on the change state of variables without using a lot of conditional logic.

HAS CRITERIA

You can use the HAS CRITERIA clause to check whether the user’s command has a particular kind of criteria on a particular set of attributes. This clause evaluates to either true or false. You can use it anywhere you can use a criteria within a procedure.

This is the grammar of the HAS CRITERIA clause:

HAS [criteria operator] CRITERIA [ON (element list)]

The HAS CRITERIA clause has two optional parts:

  • A criteria operator, such as =, <, >, <=, >=, <>, LIKE, IS NULL, or IN.
  • A list of attributes that must be present in the criteria being checked.

Some samples of the HAS CRITERIA clause:

SQLPerforms
HAS CRITERIA Checks simply whether there was any criteria at all.
HAS CRITERIA ON (element1, element2) Checks whether the criteria uses element1 and element2.
HAS = CRITERIA ON (element1) Checks whether the criteria has a comparison criteria with = operator.
HAS LIKE CRITERIA Checks whether the criteria has a match criteria using LIKE.

The elements in a HAS CRITERIA, ON clause always refer to virtual elements.

The HAS CRITERIA predicate is most commonly used in an IF clause, to determine if the user issued a particular form of command and to respond appropriately.

TRANSLATE CRITERIA

You can use the TRANSLATE CRITERIA clause to convert the criteria from the user application’s SQL command into the form required to interact with the target source or view tables. The TRANSLATE CRITERIA statement uses the SELECT transformation to infer the element mapping. This clause evaluates to a translated criteria that is evaluated in the context of a command.

This is the grammar of the TRANSLATE CRITERIA clause:

TRANSLATE [criteria operator] CRITERIA [ON (element list)] [WITH (mapping list)]

The TRANSLATE CRITERIA clause has three optional parts:

  • A criteria operator, such as =, <, >, <=, >=, <>, LIKE, IS NULL, or IN.
  • A list of attributes that must be present in the criteria being checked.
  • A mapping list of the form: = expression

You can use these mappings either to replace the default mappings generated from the SELECT transformation or to specify a reverse expression when a virtual element is defined by an expression.

Some examples of the TRANSLATE CRITERIA clause:

SQLPerforms
TRANSLATE CRITERIA Translates any user criteria using the default mappings.
TRANSLATE CRITERIA WITH (element1 = 'A', element2 = INPUT.element2 + 2) Translates any criteria with some additional mappings: element1 is always mapped to 'A' and element2 is mapped to the incoming element2 value + 2.
TRANSLATE = CRITERIA ON (element1) Translates only criteria that have = comparison operator and involve element1.

The TRANSLATE CRITERIA, ON clause always refers to virtual elements. The WITH clause always has items with form <elem> = <expression> , where the <elem> is a virtual element and the <expression> specifies what that virtual element should be replaced with when TRANSLATE CRITERIA translates the virtual criteria (from UPDATE or DELETE) into a physical criteria in the command. By default, a mapping is created based on the SELECT clause of the SELECT transformation (virtual column gets mapped to expression in SELECT clause at same position).


How Updates Against Virtual Tables are Processed

The following figure illustrates this how update commands against a virtual procedure are processed.

Update Virtual Groups Logic Figure
  1. The user application submits the SQL command to the MetaMatrix System through one of the MetaMatrix Application Programming Interfaces - SOAP, JDBC, or ODBC.
  2. The MetaMatrix System determines the virtual table that this SQL command is against.
  3. The MetaMatrix System determines the procedure it needs to use to handle this sort of SQL command for the virtual table. There is a different procedure for handling INSERT, UPDATE, and DELETE commands.
  4. The MetaMatrix System executes the procedure, which can include calculations, conversions, and SQL commands submitted to the data sources or other views. The MetaMatrix System will only execute this procedure when the user application specifies a SQL command performing the type of command for this procedure. The procedure itself can contain SQL commands of its own which can be of different types than the command submitted by the user application that invoked the procedure.
  5. The MetaMatrix System submits SQL commands as described in the procedure to the individual physical data sources or other views.
  6. The MetaMatrix System returns a value representing the number of rows changed to the user application.