Striim 3.9.8 documentation

CREATE EXCEPTIONSTORE

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

Alternatively:

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

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 CRASH (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:

W (ns1) > select * from ExceptionstoreDemo_exceptionstore;
Processing - select * from ExceptionstoreDemo_exceptionstore
[
   exceptionType = AdapterException
   action = CRASH
   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 WITHIN 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;