MATCHING clause

Syntax

MATCHING [ ONCE [ ( event [, ...] ) ] [ interval : pattern ] [ on_clause ]

pattern

[!] { event | ( pattern ) | [ interval : pattern ] } [ {&& | || | ,} [!] { event | ( pattern ) | [ interval : pattern ] } ] [...]

Interval Expression: A valid interval literal, or an expression that evaluates to an interval.

Event Name: The name or alias of a data stream that is also listed as one of the query's data sources. If a stream is referenced more than once in a pattern definition, each reference must use its own alias.

Usage

The optional MATCHING clause follows the FROM clause in a Query statement, Database statement, or Remote Procedure statement. When used, the MATCHING clause must immediately follow the FROM data source list, which must include all data sources specified in the MATCHING clause, and cannot include any other data sources.

The MATCHING clause allows a query to monitor several data sources for a specified pattern of rows that arrive, or don't arrive, in the query's data sources within a specified time interval. If the specified pattern is detected, and other query selection conditions are met, the query publishes output to its destination.

Sybase CEP pattern matching is inclusive; a pattern is considered matched, as long as the specified events occur (or do not occur) as indicated, even if additional unspecified events also occur during the designated interval.

When used without the ONCE subclause, the MATCHING clause produces one output row for each unique combination of input events that matches the pattern (subject to other query selection conditions). Data streams can also contain overlapping occurrences of patterns, in which case all occurrences are published. This rule can be modified with the ONCE subclause.

If the MATCHING clause is used with a ONCE subclause, the query outputs no more than one pattern match for every event specified by the ONCE subclause. In this case, the published pattern match will be the first pattern match involving the specified event to be detected and to fall within the WHERE clause selection condition, if such a condition is indicated. If the ONCE subclause does not specify a list of events, the once-only rule applies to all events in the pattern.

The MATCHING clause of a query that includes multiple data sources can contain an optional ON subclause, which defines one or more multi-equalities that limit the join condition. Queries containing a MATCHING clause can also include other selection criteria, such as a WHERE clause, that further refine the matching conditions for the pattern. However, only the WHERE clause can place selection conditions on non-events. See the WHERE clause description for more information.

The outer level of a pattern definition consists of a bounded pattern definition. Bounded pattern definitions are enclosed in square brackets ([ ]) and contain:

• An interval expression specifying the interval within which the pattern must be detected. The interval expression can include any CCL elements that are evaluated at compile time, such as functions, operators and literals, but cannot contain references to stream or window columns or to other elements whose value is determined at run time.

• A pattern specification, indicating the events or groups of events that must occur, or not occur, within the specified interval to meet the pattern matching criteria.

  The pattern specification includes one or more of the following:

  • Event specifications: An event is specified by indicating the name or alias of a data stream to which the query subscribes. The event occurs every time a row arrives in the specified data stream.

  • Nested bounded patterns: Syntactically identical to the outer bounded pattern. Nested bounded patterns define a smaller subinterval within the larger interval, and a series of events or groups of events, that must occur or fail to occur, within the smaller interval to produce a pattern match.

  Where a pattern specification consists of more than one event, the events or groups of events must be connected with the operators listed in the following table. A special ! operator signifies that the event or group of events must not occur to produce a pattern match. Non-occurring events are sometimes called non-events.

All the components of a bounded pattern, including nested bounded patterns, must occur within the interval specified for the bounded pattern to produce a pattern match.

The default order of precedence in which pattern components are analyzed for a possible pattern match follows the order of operators, as they are listed in the table. The tightest binding between an operator and a pattern component is that of the Not operator. The bindings then get progressively looser, for events linked with a conjunction, disjunction, and sequence operators, respectively. This default order of precedence can be overridden by enclosing a pattern component in parentheses.

Pattern Definition Validity Rules

A valid pattern definition and all of its components must be properly anchored. Anchoring refers to whether or not the beginning and end of the time interval corresponding to the pattern component can be determined. Anchoring can be fixed by the row timestamp of an event within the pattern component, or can be deduced from the context of the component. Pattern components are referred to as being left-anchored (a pattern component with a fixed interval start time), right-anchored (a pattern component with a fixed interval end time), fully anchored (a pattern component that is both left-anchored and right-anchored), or unanchored (a pattern component that is neither left-anchored nor right-anchored). The following rules must be satisfied for a pattern definition to be considered valid:

1. Every pattern component within any bounded pattern must be either left-anchored, right-anchored, or both.

2. For any two consecutive pattern components within a sequence, the left component must be right-anchored, and/or the right component must be left-anchored.

The following table specifies the anchoring status of all pattern components.

Here are some examples illustrating pattern definition validity rules:

[10 SECONDS: A, B, !C]

This is a valid pattern definition. The sequence A, B, !C is left-anchored (because A is left-anchored), so the first anchoring rule is satisfied. A is right-anchored and B is left-anchored, so the second anchoring rule is satisfied for A and B. B is right-anchored, so the second anchoring rule is satisfied for B and !C.

[1 SECOND: !A, B, !C]

This pattern definition is invalid. The sequence !A, B, !C fails the first anchoring rule, because it is unanchored.

[3 SECONDS: A, !B, !C]

This pattern definition is invalid. !B is not right-anchored and !C is not left-anchored, so the pattern definition fails the second anchoring rule.

[5 MINUTES: A && (!B, C)]

This pattern definition is valid. The conjunction is right-anchored, since both A and (!B, C) are right-anchored.

[20 SECONDS: [10 SECONDS: !A, B], !C]

This pattern definition is valid. The nested bounded pattern is fully anchored, so the sequence [10 SECONDS: !A, B], !C is valid and left-anchored.

Pattern Interval Rules

A pattern component definition, together with the actual incoming events scanned for pattern matches, determine a time interval, within which the A pattern component can be said to occur. Specifically, the pattern component's interval is determined by subtracting the pattern component's starting time from its ending time. Where the starting time and ending time are the same, the interval is considered to be NULL.

The following rules specify how the starting time and ending time of each component are determined:

A single event A

• The starting time and ending time are determined by the row timestamp of A.

Non-event !A, where A is a single event or other pattern component

• Although a non-event does not contain a start or end time, the context in which the non-event appears (conjunction, disjunction, sequence) exactly specifies the interval to which it applies, as determine by the other rules in this list.

A conjunction A && B, where A and B are single events or other pattern-components

• The starting time is the earlier of the starting times of A and B.

• The ending time is the later of the ending times of A and B.

• A left-unanchored operand of a conjunction takes its starting time from the starting time of the conjunction.

• A right-unanchored operand of a conjunction takes its ending time from the ending time of the conjunction.

A disjunction A || B, where A and B are single events or other pattern components

• The starting and ending times are those associated with A or B: whichever of the two occurs.

• A left-unanchored operand of a disjunction takes its starting time from the starting time of the disjunction.

• A right-unanchored operand of a disjunction takes its ending time from the ending time of the disjunction.

A sequence A, ..., B where A and B are single events or other pattern components

• The starting time is the starting time of A.

• The ending time is the ending time of B.

• If the first pattern component of a sequence is left-unanchored, it takes its starting time from the starting time of the sequence.

• If a pattern component is left-unanchored, and is not the first operand of a sequence, its starting time is calculated by adding one microsecond to the ending time of the previous operand in the sequence.

• If a pattern component is right-unanchored, and is not the last operand of a sequence, its ending time is calculated by subtracting one microsecond from the starting time of the next operand in the sequence.

• If the last pattern component of a sequence is right-unanchored, it takes its ending time from the ending time of the sequence.

A bounded pattern [interval-expression: A] where A is a single event, or other pattern-component

• If A is fully anchored, the starting and ending times are those of A.

• If A is left-unanchored, the ending time is the ending time of A, and the starting time is determined by the ending time of A minus the result of the specified interval-expression.

• If A is right-unanchored, the starting time is the starting time of A, and the ending time is determined by the starting time of A plus the result of the specified interval-expression.

• A left-unanchored operand of a bounded pattern takes its starting time from the starting time of the bounded pattern.

• A right-unanchored operand of a bounded pattern takes its ending time from the ending time of the bounded pattern.

Here are some illustrations of pattern interval rules.

[10 SECONDS: A, B, !C]

The interval for the whole pattern starts when a row arrives in stream A and ends ten seconds later. The interval associated with B starts one microsecond after the arrival of the row in A (that is, a row in stream B must arrive after the arrival of the row in A, and must not overlap A to produce a pattern match). Likewise, the interval associated with C starts one microsecond after the arrival of the row in B and extends until ten seconds have elapsed since A's arrival.

[5 MINUTES: A && (!B, C)]

The interval for the whole pattern ends when a row arrives in either stream A or C - whichever arrives later - and starts five minutes before this time. The interval associated with B starts at the same time as the interval for the whole pattern, and ends when the row arrives in C.

[20 SECONDS: [10 SECONDS: !A, B], !C]

The interval for [10 SECONDS: !A, B] ends when a row arrives in B, and starts ten seconds earlier. The interval for the whole pattern starts ten seconds before the row arrives in B and ends 20 seconds later (that is, ten seconds after the row arrives in B.) The interval associated with A starts ten seconds before the arrival of a row in B minus one microsecond. The interval associated with C starts one microsecond after a row arrives in B and ends ten seconds after the row arrives in B.

Capturing Matching Row Data

The XMLPATTERNMATCH() function can be used in the SELECT clause of the query that contains the MATCHING clause to generate an XML tree that contains data from all the rows (except non-events) that resulted in successful pattern matches. The XML tree can then be published to the query's destination.

Restrictions

The following restrictions apply to the use of pattern matching:

• The MATCHING clause supports pattern matches only within data streams and subqueries, not database subqueries or windows.

• A stream name or alias name can appear only once within a pattern definition. In the case of multiple references to the same stream, each reference must use a different alias.

• Events that are directly or indirectly specified as non-events cannot be referred to in the query's select list, since a non-existent row cannot be published.

• Events that are directly or indirectly specified as non-events can be referenced with certain restrictions in the query's WHERE clause. See WHERE for details.

• The interval expression specifying the interval within which the pattern must be detected can include any CCL elements that are evaluated at compile time, such as functions, operators, and literals, but cannot contain references to stream or window columns or to other elements whose value is determined at run time.

See Also

• Database Statement

• Query Statement

• Remote Procedure Statement

• ON

• XMLPATTERNMATCH()

Examples

The following example monitors for a row arriving in the HighTemperature stream, followed by a row arriving in the HighSmoke stream, and no row in the SprinklersOn stream, all within a 120-second interval, where the RoomNumber column in the HighTemperature and HighSmoke streams contain the same value:

INSERT INTO OutStream
SELECT HighTemperature.RoomNumber
FROM HighTemperature, HighSmoke, SprinklersOn
   MATCHING [120 SECONDS: HighTemperature, HighSmoke, !SprinklersOn]
ON HighTemperature.RoomNumber = HighSmoke.RoomNumber 
   = SprinklersOn.RoomNumber;

The pattern defined in the following example is similar to the previous one, but allows the rows in HighTemperature and HighSmoke to occur in either order. The pattern interval in this example is shorter (20 seconds) than in the previous one, and each stream has an associated alias. A condition selection requires the Building column in all three streams to contain the same value:

INSERT INTO BackupAlarmSystem
SELECT 'SprinklerFailure'
FROM HighSmoke AS HS, HighTemperature AS HT, SprinklersOn AS S
   MATCHING [20 SECONDS: HS && HT, !S]
ON HS.Building = HT.Building = S.Building;

Here is another example of a pattern definition:

INSERT INTO OutStream
SELECT A.x, B.x, C.x
FROM A, B, C, D, E, F, G, H, I
   MATCHING [20 SECONDS: A && B, C && D, E, !F, G || H, !I];

The following example contains a parentheses-enclosed subpattern that overrides the default event operator order of precedence:

INSERT INTO OutStream
SELECT A.x, C.x, D.x
FROM A, C, D
   MATCHING [1 SECOND: (A || B) && C, D];

The following example contains a nested bounded pattern definition that monitors for events with a five-second interval, nested within the larger ten-second interval of the outer pattern:

INSERT INTO OutStream
SELECT A.x, D.x
FROM A, D
   MATCHING [10 SECONDS: A, !B, [5 SECONDS: !C, D]];

The following example shows a pattern-matching specification that contains a ONCE subclause, which limits the output of matches involving stream A, causing the query to output only the first pattern match involving A, where the value of B.x is greater than 100.

INSERT INTO OutStream
SELECT A.x, B.x
FROM A, B
   MATCHING ONCE (A) [10 SECONDS: A, B]
WHERE B.x > 100;