Striim Platform 4.1.2 documentation

CREATE EXCEPTIONSTORE FOR APPLICATION <name> [ TTL: '<interval>' ];

Alternatively, to create an exception store at the same time you create an application::

CREATE APPLICATION <name> USE EXCEPTIONSTORE  [ TTL: '<interval>' ];

You may also create and browse an exception store in the Flow Designer:

When you create an application using templates, this is enabled automatically. Turn it off if you do not want an exception store for the application.

An exception store collects exceptions for a single application along with events related to the exception and persists them to Elasticsearch.

The type for exception store events is Global.ExceptionEvent. Its fields are:

For a simple example, say you have the following Oracle table in SOURCEDB and TARGETDB schemas:

CREATE TABLE MYTABLE(
ID int PRIMARY KEY,
NAME varchar2(100),
CITY varchar2(100));

Replicate it from one Oracle instance to another using the following application:

CREATE APPLICATION ExceptionstoreDemo USE EXCEPTIONSTORE;
CREATE SOURCE OracleCDC USING OracleReader (
  Username:'striim',
  Password:'******',
  ConnectionURL:'10.211.55.3:1521:orcl1',
  Tables:'SOURCEDB.MYTABLE'
)
OUTPUT TO OracleCDCStream;
CREATE TARGET WriteToOracle USING DatabaseWriter (
  ConnectionURL:'jdbc:oracle:thin:@10.211.55.3:1521:orcl1',
  Username:'striim',
  Password:'******',
  Tables:'SOURCEDB.MYTABLE,TARGETDB.MYTABLE',
  IgnorableExceptionCode: 'NO_OP_UPDATE'
)
INPUT FROM OracleCDCStream;
END APPLICATION ExceptionstoreDemo;

Insert the identical row twice:

INSERT INTO MYTABLE VALUES (1,'name1','city1');
INSERT INTO MYTABLE VALUES (1,'name1','city1');

Since the primary key already exists, the second insert will throw an exception. Since NO_OP_UPDATE is specified as an ignorable exception (see discussion of Ignorable Exception Code property in Database Writer), the exception will be written to the application's exception store. You can query the exception store using the same syntax you would to query a WActionStore:Database Writer

W (ns1) > select * from ExceptionstoreDemo_exceptionstore;
Processing - select * from ExceptionstoreDemo_exceptionstore
[
   exceptionType = AdapterException
   action = STOP
   appName = ns1.NSDemo
   appid = 01ea1e42-8e77-7651-b9fa-52b54b45818e
   entityType = TARGET
   entityName = t
   className = java.sql.SQLIntegrityConstraintViolationException
   message = ORA-00001: unique constraint (MYUSERID.SYS_C007357) violated
   exceptionTime = 2019-12-14T12:57:32.067+05:30
   epochNumber = 1576308181383
   relatedActivity = target notify exception
   relatedObjects = {"_id":null,"timeStamp":1576308452050,"originTimeStamp":0,"key":null,
"sourceUUID":{"uuidstring":"01ea1e42-8ea5-3d11-b9fa-52b54b45818e"},"data":["1","name1","city1"],
"metadata":{"RbaSqn":"3","AuditSessionId":"30106","TableSpace":"USERS","CURRENTSCN":"1579373",
"SQLRedoLength":"78","BytesProcessed":null,"ParentTxnID":"1.27.567","SessionInfo":"UNKNOWN",
"RecordSetID":" 0x000003.000454d0.0010 ","DBCommitTimestamp":"1576308452000","COMMITSCN":1579374,
"SEQUENCE":"1","Rollback":"0","STARTSCN":"1579373","SegmentName":"MYTABLE","OperationName":"INSERT",
"TimeStamp":1576337252000,"TxnUserID":"MYUSERID","RbaBlk":"283856","SegmentType":"TABLE",
"TableName":"SOURCEDB.MYTABLE","TxnID":"1.27.567","Serial":"28518","ThreadID":"1",
"COMMIT_TIMESTAMP":1576337252000,"OperationType":"DML","ROWID":"AAAR+CAAHAAAADcAAB",
"DBTimeStamp":"1576308452000","TransactionName":"","SCN":"157937300000008444435329188000001",
"Session":"344"},"userdata":null,"before":null,"dataPresenceBitMap":"Bw==",
"beforePresenceBitMap":"AA==","typeUUID":{"uuidstring":"01ea1e42-9082-3a71-8672-52b54b45818e"}}
]

The following application demonstrates how you might query data from the exception store and write it to a file you could use as a starting point for inserting some or all of the logged events into the target. Contact Striim support if you need assistance in developing such an application.

CREATE APPLICATION ProcessExceptionstoreDemo;
CREATE TYPE ExceptionList_Type (
  evtlist java.util.List
);
CREATE STREAM ExceptionListStream OF ExceptionList_Type;
CREATE CQ ReadFromExceppStore 
INSERT INTO ExceptionListStream
SELECT to_waevent(s.relatedObjects) AS evtlist 
FROM ExceptionstoreDemo_exceptionstore [JUMPING 5 SECOND] s;

CREATE STREAM RelatedEventStream OF Global.WAEvent;
CREATE CQ GetRelatedEvents 
INSERT INTO RelatedEventStream
SELECT com.webaction.proc.events.WAEvent.makecopy(cdcevent) 
FROM ExceptionListStream a, iterator(a.evtlist) cdcevent;

CREATE TARGET WriteToFileAsJSON USING FileWriter ( 
  filename: 'expEvent',
  directory: 'ExpStore_logs'
) 
FORMAT USING JSONFormatter()
INPUT FROM RelatedEventStream;
END APPLICATION ProcessExceptionstoreDemo;