Striim Cloud4.1.0documentation

metadata: To retrieve the values for these fields, use the META function.

• OperationName: ROLLBACK, COMMIT, INSERT, DELETE, or UPDATE

• TimeStamp: date and time of the operation (Joda DateTime, milliseconds since 1970-1-1 GMT)

• TxnID: transaction ID

• TableName (returned only for INSERT, DELETE, and UPDATE operations): fully qualified name of the table

• AuditTrailName: logical name of the TMF audit trail from which the record came (either 'MAT' or 'AUX01' through  'AUX15') or, if the audit trails are not being read separately in parallel, 'MERGE'.

• LSNValue: The TMF audit trail position of the event. This may be used to start from a specific position by specifying it as the StartLSN value in the reader (see HP NonStop reader properties).

• PK_UPDATE: Always false.

• Rollback: 1 if the record was generated as a result of the rollback of the associated transaction, otherwise 0

• ROWID (returned only for INSERT, DELETE, and UPDATE operations): the record address, record number, or SYSKEY, or null if the table or file has none of those items. This property is meaningful only for tables or files with a system-generated key, such as entry-sequenced files/tables, relative files/tables, and key-sequenced tables with no user-defined primary key. It is present but null for key-sequenced files and key-sequenced tables that have a user-defined primary key. If the IncludeSYSKEY property is true, the SYSKEY gets put into data[0] (and before[0]) and is not put into ROWID.

• TxnSystemName: the name of the NonStop system on which the current transaction started. (Useful when converting TxnID to HP's normal human-readable format for transaction IDs.)

To retrieve the values for these fields, use the META function. See Parsing the fields of WAEvent for CDC readers. For TxnSystemName, you may use the NSK_TXN_STRING function to convert its value to a human-readable format. For example:

CREATE CQ tst54cq
INSERT INTO tst54cqstream
SELECT meta(s,"OperationName").toString(),
meta(s,"TxnID").toString(),
meta(s,"TxnSystemName").toString(),
NSK_TXN_STRING(meta(s,"TxnID").toString(),meta(s,"TxnSystemName").toString()),
CASE WHEN IS_PRESENT(s,data,0) = true
THEN TO_STRING(data0)
ELSE 'nothing' END,
CASE WHEN IS_PRESENTis_present(s,data,1) = true
THEN TO_STRING(data1)
ELSE 'nothing' END
FROM tst54stream s;

data: an array of fields, numbered from 0, containing:

• for an INSERT operation, the values that were inserted.

• for an UPDATE, the values after the operation was completed; if the HP NonStop reader's compression property is True (see HP NonStop reader properties), only the modified values.

If TMF audit compression is specified for a table and the HP NonStop reader's Compression property is false, then the values of columns that were changed will be included and the values of some of the columns that were not changed might also be included (whichever makes the TMF audit trail record shorter). If TMF audit compression is specified for an Enscribe file, no change records are created for UPDATE operations.

• for a DELETE operation, the values that were deleted; if the HP NonStop reader's compression property is True (see HP NonStop reader properties), contains only the value of the primary key column (unless there are no user-defined key columns, in which case all column values are included)

To retrieve the values for these fields, use SELECT ... (DATA[]). See Parsing the fields of WAEvent for CDC readers.

For Enscribe files, the entries in the data[] array that correspond to DDL fields that are beyond the end of the current record are omitted. The IS_PRESENT() function can be used to determine whether a field's value is included or omitted if it is not possible to determine that from the value of a field that is always present that gives the record type of the current record.

The value of fields of character type that start before the end of the current record, but whose declared length extends beyond the end of the current record, are included in the data[] array, but only the characters up to the end of the current record are used as the value of that field. IS_PRESENT() returns true for such fields. The value of fields of any other data type that start before the end of the current record, but whose declared length makes them extend beyond the end of the current record, are omitted from the data[] array, and IS_PRESENT() returns false for them.

before (for UPDATE operations only): the same format as data, but containing the values as they were prior to the UPDATE operation.

• If the HP NonStop reader's compression property is True (see HP NonStop reader properties), before contains only the value of the primary key columns.

• If the HP NonStop reader's compression property is False, and TMF audit compression is enabled for a file, before contains the values of the primary key columns and the values before the update of any column whose value was changed, but generally the values of columns whose values were not changed generally are omitted (though some of them might be included).

• If a table has no user-defined primary key columns, before contains the values of all columns before the update was done, except if TMF audit compression is enabled for the table, the values of columns that were not changed usually will be omitted.

• For Enscribe files, the entries in the before[] array that correspond to DDL fields that are beyond the end of the current record are handled the same as was described above for the data[] array. Note that for Enscribe UPDATE operations, a record may have a different length after the operation than it had before.

dataPresenceBitMap, beforePresenceBitMap, and typeUUID are reserved and should be ignored.

For SQL/MP and SQL/MX tables that contain a SYSKEY column, the SYSKEY column value is not included in data or before. It is put only into the ROWID part of metadata, unless includeSYSKEY is specified to be true, in whcih case the SYSKEY is put into data[0] (and before[0]) and is not put into ROWID.

For SQL/MP and SQL/MX tables that have the TMF auditcompress attribute set, the change records for updates are guaranteed to contain values only for the columns actually changed and for key columns. Other columns will have null in their spots in data and before. Sometimes the values for other columns will be included if it makes logging the change more efficient, but that cannot be relied upon. If you want update operations to show the values of all the columns, be sure the auditcompress attribute is not set for the tables in question. This affects only updates. Inserts and deletes always show the values of all the columns.