Triggers in TimesTen (XLA Application)

TimesTen supports PL / SQL (procedures, functions, packages, etc.), but there is no support for triggers, since triggers adversely affect performance. But what if you need to implement trigger logic?

The answer is to write an XLA app. You can write to C or Java, to whom that is closer.
Below I will describe an example using java :)


')
The documentation (Java Developer's Guide) says:

"You can use the TimesTen JMS / XLA API (JMS / XLA) to monitor TimesTen for
notifications for these changes. JMS / XLA is a high-performance, asynchronous alternative to triggers. ”

Those. You can write a java application that can use the JMS / XLA API to receive messages (in asynchronous mode) about changes to TimesTen. JMS / XLA uses the JMS publish-subscribe interface to access XLA changes. You can read more about JMS here (http://download.oracle.com/javaee/1.3/jms/tutorial).

Next, try to create such an application.

Initially, create objects in TimesTen.
[oracle @ tt1 xla] $ ttisql dbxla

Copyright 1996-2010, Oracle. All rights reserved.
Type? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=dbxla"; Connection successful: DSN=dbxla;UID=oracle;DataStore=/u01/app/oracle/datastore/dbxla;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.1/TimesTen/tt1/lib/libtten.so;PermSize=32;TempSize=50;TypeMode=0;PLSQL=0;CacheGridEnable=0; (Default setting AutoCommit=1) Command> CREATE USER oratt IDENTIFIED BY oracle; User created. Command> grant create session, create table, XLA to oratt; Command> connect "DSN=dbxla;UID=oratt;PWD=oracle;"; Connection successful: DSN=dbxla;UID=oratt;DataStore=/u01/app/oracle/datastore/dbxla;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.1/TimesTen/tt1/lib/libtten.so;PermSize=32;TempSize=50;TypeMode=0;PLSQL=0;CacheGridEnable=0; (Default setting AutoCommit=1) con1: Command> create table xlatest ( id NUMBER NOT NULL PRIMARY KEY, > name VARCHAR2(100) ); con1: Command> 

Now, create a bookmark. XLA bookmarks are used to mark read positions in transaction logs. This bookmark is used to track changes in the xlatest table.
con1: Command> call ttXlaBookmarkCreate ('bookmark');
con1: Command>

Next, we determine which table changes we will observe. To do this, call the procedure ttXlaSubscribe. In this case, we will observe changes with the xlatest table using the bookmark bookmark.
con1: Command> call ttXlaSubscribe ('xlatest', 'bookmark');
con1: Command>

Next, we proceed to configure the application.
To connect to the XLA, you must establish a connection to the JMS Topic, which is associated with a specific TimesTen database. The JMS / XLA configuration file provides a binding between the name of the topic and the database. By default, the application searches for this file, named jmsxla.xml, in the current directory, but if you wish, you can define a different name and location for this file (see the documentation).

In this case, I use the following jmsxla.xml file:

 <xlaconfig> <topics> <!-- topic for Xla demo --> <topic name="xlademo" connectionString="DSN=dbxla" xlaPrefetch="100" /> </topics> </xlaconfig> 

As you can see, I linked the name of the xlademo topic to the dbxla database.

Now let's start, directly, writing a java Application.
Initially, we initialize the context.
Hashtable env = new Hashtable ();
env.put (Context.INITIAL_CONTEXT_FACTORY, "com.timesten.dataserver.jmsxla.SimpleInitialContextFactory");
InitialContext ic = new InitialContext (env);

Next, use the JMS connection factory to connect to the XLA. After that, we call the start () method of the connection to activate sending messages. After that, using this connection we create a session.
private javax.jms.TopicConnection connection; / ** JMS connection * /
private TopicSession session; / ** JMS session * /
...
TopicConnectionFactory connectionFactory = (TopicConnectionFactory) ic.lookup ("TopicConnectionFactory");
connection = connectionFactory.createTopicConnection ();
...
// get session
session = connection.createTopicSession (false, Session.AUTO_ACKNOWLEDGE);
...

Also, when creating a session, it is necessary to specify the transactionalness of the session and the type of the acknowledgment modes.
JMS / XLA supports three models (AUTO_ACKNOWLEDGE, DUPS_OK_ACKNOWLEDGE, CLIENT_ACKNOWLEDGE), more information about the models can be found in the documentation. In the example I use the first model and set the session transactionalness to false.

Next, you need to decide on the mode of receiving messages. Two options are possible: synchronous and asynchronous.

In the synchronous version, messages are processed sequentially (one after the other). This means that while the message is not processed, the other is waiting.
For synchronous variation, we call the start () method on the connection, activate message sending, create a Topic, then create a subscriber and receive messages using the receive () and receiveNoWait () methods.

 connection.start(); Topic topic = session.createTopic(topicName); TopicSubscriber subscriber = session.createDurableSubscriber(topic, bookmark); .. MapMessage message = (MapMessage)subscriber.receive(); ... 

In asynchronous mode, you need to create a listener and process messages in it.
MyListener myListener = new MyListener (outStream);

Topic xlaTopic = session.createTopic (topic);
TopicSubscriber subscriber = session.createDurableSubscriber (xlaTopic, bookmark);
...
subscriber.setMessageListener (myListener);
connection.start ();
...

Below is an example of a class that implements synchronous mode (file DemoXLA.java).
import java.util.Enumeration;
import java.util.Hashtable;
import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Session;
import javax.jms.Topic;
import javax.jms.TopicConnectionFactory;
import javax.jms.TopicConnection;
import javax.jms.TopicSession;
import javax.jms.TopicSubscriber;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

 public class DemoXLA { private TopicConnectionFactory connectionFactory; private TopicConnection connection; private TopicSession session; private Topic topic; private TopicSubscriber subscriber; public DemoXLA( String cf, String topicName, String selector) throws JMSException, NamingException {String key; Context messaging = getInitialContext(); // getting the context connectionFactory = (TopicConnectionFactory)messaging.lookup(cf); connection = connectionFactory.createTopicConnection(); connection.start(); session = connection.createTopicSession(false, Session.AUTO_ACKNOWLEDGE); topic = session.createTopic(topicName); subscriber = session.createDurableSubscriber(topic, selector); int i=0; while (i<10) { MapMessage message = (MapMessage)subscriber.receive(); Enumeration e = message.getMapNames(); while (e.hasMoreElements()) { key = (String)e.nextElement(); System.out.println("[ " + key + " = " + message.getObject(key) + " ]"); } System.out.println("----------------------------------------"); } session.unsubscribe(selector); subscriber.close(); session.close(); connection.stop(); } private Context getInitialContext() throws NamingException { Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.timesten.dataserver.jmsxla.SimpleInitialContextFactory"); InitialContext initialContext = new InitialContext(env); return initialContext; } public static void main(String[] args) throws JMSException, NamingException { DemoXLA demo = new DemoXLA("TopicConnectionFactory", "Level2Demo", "bookmark"); } } 

Below is an example of classes that implement asynchronous mode (MyListener.java, DemoXLA2.java).

MyListener.java
import java.util.Enumeration;
import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Message;
import javax.jms.MessageListener;

 public class MyListener implements MessageListener { public MyListener() {} public void onMessage(Message message) { MapMessage mp = (MapMessage)message; Enumeration e; try { e = mp.getMapNames(); } catch (JMSException s) { e = null; System.out.println("error 1"); } while (e.hasMoreElements()) { String key = (String)e.nextElement(); try { System.out.println("[ " + key + " = " + mp.getObject(key) + " ]"); } catch (JMSException f) { System.out.println("error 2"); } } System.out.println("----------------------------------------"); } } 

DemoXLA2.java
import java.util.Hashtable;
import javax.jms.JMSException;
import javax.jms.Session;
import javax.jms.Topic;
import javax.jms.TopicConnectionFactory;
import javax.jms.TopicSession;
import javax.jms.TopicSubscriber;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

 public class DemoXLA2 { private javax.jms.TopicConnectionFactory connectionFactory; private javax.jms.TopicConnection connection; private TopicSession session; private Topic topic; private TopicSubscriber subscriber; public DemoXLA2( String cf, String topicName, String selector) throws JMSException, NamingException, InterruptedException { Context messaging = getInitialContext(); Object connectionFactoryObject = messaging.lookup(cf); connectionFactory = (TopicConnectionFactory)connectionFactoryObject; connection = connectionFactory.createTopicConnection(); MyListener myListener = new MyListener(); session = connection.createTopicSession(false, Session.AUTO_ACKNOWLEDGE); topic = session.createTopic(topicName); subscriber = session.createDurableSubscriber(topic, selector); subscriber.setMessageListener(myListener); connection.start(); Thread.sleep(60000); session.unsubscribe(selector); subscriber.close(); session.close(); connection.stop(); } private Context getInitialContext() throws NamingException { Hashtable env = new Hashtable(); env.put(Context.INITIAL_CONTEXT_FACTORY, "com.timesten.dataserver.jmsxla.SimpleInitialContextFactory"); InitialContext initialContext = new InitialContext(env); return initialContext; } public static void main(String[] args) throws JMSException, NamingException, InterruptedException { DemoXLA2 demo = new DemoXLA2("TopicConnectionFactory", "xlademo", "bookmark"); } } 

Next, run any of the examples and try to enter, modify and delete data from the xlatest table.

 Command> insert into xlatest values (2, 'w'); 1 row inserted. Command> update xlatest set name = 'test' where id=2; 1 row updated. Command> delete from xlatest; 1 row deleted. Command> 

Accordingly, in the application we get:

 [ __TYPE = 10 ] [ __COMMIT = true ] [ __FIRST = true ] [ __NULLS = ] [ __TBLNAME = XLATEST ] [ __TBLOWNER = ORATT ] [ __mver = 5621355056449191939 ] [ __mtyp = null ] [ ID = 2 ] [ NAME = w ] ---------------------------------------- [ __TYPE = 11 ] [ __COMMIT = true ] [ __FIRST = true ] [ __UPDCOLS = NAME ] [ __NULLS = ] [ __TBLNAME = XLATEST ] [ __TBLOWNER = ORATT ] [ __mver = 5621355056449191942 ] [ __mtyp = null ] [ _ID = 2 ] [ ID = 2 ] [ _NAME = w ] [ NAME = test ] ---------------------------------------- [ __TYPE = 12 ] [ __COMMIT = true ] [ __FIRST = true ] [ __NULLS = ] [ __TBLNAME = XLATEST ] [ __TBLOWNER = ORATT ] [ __mver = 5621355056449191945 ] [ __mtyp = D ] [ ID = 2 ] [ NAME = test ] ---------------------------------------- 

As you can see, we received messages about the past operations with the xlatest table.
Messages have the following format:
System attributes start with a double underscore, for example:
__TYPE - type of operation (Insert (10), Update (11), Delete (12)) other types are also possible (see documentation). To determine the type of operation there are constants.
__COMMIT - signals the completion of the transaction (if true).
__FIRST - signals the first operation in the transaction (if true).
__TBLNAME is the name of the table
__TBLOWNER - table owner
__NULLS - signals the attributes that contain the value null
__mver and __mtyp are system attributes.
etc. (see documentation)
Attributes without an underscore are columns of tables that have specific values, for example:
[Id = 2]
[NAME = test]
Attributes that start with one underscore are old field values ​​(appear during Update operation), for example:
[_ID = 2]
[Id = 2]
[_NAME = w]
[NAME = test]

Total
Therefore, with a fairly superficial knowledge of java, you can write an XLA application that can handle various messages received from TimesTen. In addition, the XLA application works asynchronously, which has virtually no effect on the performance of Oracle TimesTen.

Article author: Gennady Sigalayev