CREATE EXCEPTIONSTORE

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 exception store's name is the application name with _ExceptionStore appended.
By default, the time to live (TTL) is 7d, which means events are discarded after seven days. Optionally, you may specify a different time to live as m (milliseconds), s (seconds), h (hours), d (days), or w (weeks). To change the time to live, use ALTER EXCEPTIONSTORE <name> TTL: '<interval>'; (recompile is not necessary).
Dropping the application does not drop the exception store. This allows you to drop the application, modify the TQL, and reload it without losing the existing data in its exception store.

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

field	type	notes
exceptionType	java.lang.String	One of the following:CRA AdapterException ArithmeticException ClassCastException ConnectionException InvalidDataException NullPointerException NumberFormatException SystemException UnexpectedDDLException UnknownException
action	java.lang.String	IGNORE or STOP (see Handling exceptions)Handling exceptions
appName	java.lang.String	the name of the associated application
entityType	java.lang.String	the type of component that threw the exception (source, CQ, target, etc.)
entityName	java.lang.String	the component's name
className	java.lang.String	the Java class name related to the exception
message	java.lang.String	the error message from the target DBMS
exceptionTime	org.joda.time.DateTime	the time of the exception
epochNumber	java.lang.Long	the epoch time of the exception
relatedActivity	java.lang.String	the Striim activity that caused the exception
relatedObjects	java.lang.String	the Striim event(s) affected by the exception

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;

CREATE EXCEPTIONSTORE

Search results