TAFJ-R e a d O nly D a t a b a s e

R22
 TAFJ-R e a d O nly D a t a b a s e

Amendment History:

Revisio
Date Amended Name Description
n
1 18/12/2013 TAFJ team Initial version
2 12/2/2914 R. Vincent R14GA Review

6 10th March 2015 H. Aubert R15 AMR review

7 1 April 2015 R. Vincent Add $DIM documentation

8 15th March 2015 R. Vincent R16 AMR review

9 24th May 2018 Riswana Add a note for $RR File

Add new property that allow session when RO

10 25th July 2018 Riswana
database is offline

11 22nd March R. Vincent R19 AMR review

2019
Add warning about XA connection with RO
12 23rd May 2019 R. Vincent
database.

13 25th March Hariprasath R20 AMR review

2020
14 22nd March Riswana Add DBlink creation steps for PostgreSQL
2021
15 12th April 2021 Vikas R21 AMR

16 Preethi
25th March 2022 R22 AMR review
Evangelin

Page 2
TAFJ-R e a d O nly D a t a b a s e

Copyri g h t
Copyright © Temenos Headquarters SA 2009-2022. All rights reserved.
This document contains proprietary information that is protected by copyright. No part of this document may
be reproduced, transmitted, or made available directly or indirectly to a third party without the express
written agreement of TEMENOS UK Limited. Receipt of this material directly TEMENOS UK Limited
constitutes its express permission to copy. Permission to use or copy this document expressly excludes
modifying it for any purpose, or using it to create a derivative therefrom.

Erra t a and Com m e n t s

If you have any comments regarding this manual or wish to report any errors in the
documentation, please document them and send them to the address below:
Technology Department

Temenos Headquarters SA
2 Rue de l’Ecole-de-Chimie,
CH - 1205 Geneva,
Switzerland

Tel SB: +41 (0) 22 708 1150

Fax: +41 (0) 22 708 1160

Please include your name, company, address, and telephone and fax numbers, and email
address if applicable. TAFJdev@temenos.com

Page 3
TAFJ-R e a d O nly D a t a b a s e

Table of Contents
Copyright................................................................................................................................................ 3
Errata and Comments............................................................................................................................ 3
Introduction............................................................................................................................................. 5
Prerequisites........................................................................................................................................... 5
TAFJ Properties...................................................................................................................................... 6
Standalone connection........................................................................................................6
Application server connection..............................................................................................6
Allow Session when RO Database is Offline.......................................................................7
Underneath the Covers.......................................................................................................................... 7
$RO, $DIM, and $RR File Workflow.................................................................................9
Creating new tables with the CREATE-FILE command..........................................................................9
Creating Views in the $RO Database................................................................................................... 10
DBLink Creation................................................................................................................................... 13
PostgreSQL....................................................................................................................... 13

Page 4
TAFJ-R e a d O nly D a t a b a s e

Intro d u c t i o n

TAFJ supports a read-only database feature where certain tables will be archived and stored
in a separate database instance. This will enable the logical and physical separation of
historical data from the ‘live’ data hence minimizing the amount of data retained in the live
data area. Minimizing the live data will improve the general performance and reduce the
non-functional operational overheads required to support the banks daily transactional
activities. As well as “lessening the load”, a read-only dimensional database can act as a
staging area for data warehousing purposes without impacting the transactional database.

TAFJ Readonly files are suffixed with $RO. Dimensional files are suffixed with $DIM

NOTE: Read-only/DIM/RR databases should use non XA

connections due to an Oracle bug with result sets held over
commit boundaries. XA is not needed for read-only/DIM/RR
database.

Prer e q u i s i t e s

A read only database must be created and loaded before TAFJ read-only functionality can
work. Likewise, a dimensional database needs to be prepared beforehand. Generally, a set
of scripts is run for the typical tables that will be added to the read-only database. This
process is not covered in this document, nor is the preparation of a dimensional database.

From a TAFJ perspective, it means that the TAFJ_VOC must be defined correctly. See the
section “Underneath the covers.”

A database link from the T24 transactional database to each read-only database must exist
(created on the transactional database).

Page 5
TAFJ-R e a d O nly D a t a b a s e

TAFJ Prop e r t i e s

Sta n d a l o n e co n n e c t i o n

The following TAFJ properties will invoke another two standalone connections, one for the
read-only DB TESTRODB and one for the dimensional database TESTDIM. Multiple values
are separated by a comma. (Oracle is shown here as an example). The database link name
must match the one defined in the database and they are required otherwise Standard
Selection will fail. Database links must be present regardless if running in an application
server context.

temn.tafj.jdbc.ro.urls=jdbc:oracle:thin:@localhost:1521/TESTRODB,
jdbc:oracle:thin:@localhost:1521/TESTDIM

temn.tafj.jdbc.ro.drivers=oracle.jdbc.driver.OracleDriver, oracle.jdbc.driver.OracleDriver

temn.tafj.jdbc.ro.usernames=myuser,myuser2

temn.tafj.jdbc.ro.passwords=mypassord,mypassword2

temn.tafj.jdbc.ro.dataSourceNames=

temn.tafj.jdbc.ro.dataBaseLinks=TESTRODB,TESTDIMLINK

Note that $DIM files DO NOT require a physical database link, but one must be filled out
because TAFJ cannot determine what kind of database it is connecting to. It only knows this
through the file type ($RO or $DIM).

In Postgres, specify dblink name along with server name separated by semi-colon.

temn.tafj.jdbc.ro.dataBaseLinks=TESTRODB;<server_name>

Refer DBlink Creation topic for more details regarding DBLink creation in PostgreSQL.

Applica tio n serve r conn e c tio n

The following TAFJ properties will invoke another connection from a defined datasource.
The datasource below (t24RODataSource) is defined as a reference in the TAFJ EAR file. If
more than one is needed, it might require modifications to the TAFJEAR file. See the TAFJ-
AS TAFJ document on how to do that. Note that database link again is required.

temn.tafj.jdbc.ro.dataSourceNames= java:comp/env/jdbc/t24RODataSource

Page 6
TAFJ-R e a d O nly D a t a b a s e

temn.tafj.jdbc.ro.dataBaseLinks=TESTRODB

When TAFJ is invoked within an application server context, a JNDI lookup will occur on the
datasource defined. Therefore, the datasource must also be defined within the particular
application server. If database link is used, there is no need for the temn.tafj.jdbc.ro.urls,
temn.tafj.jdbc.ro.drivers, temn.tafj.jdbc.ro.usernames, and temn.tafj.jdbc.ro.passwords
properties as they are all defined in the datasource at the application server.

Allow Se s s i o n wh e n RO Dat a b a s e is Offli n e

When one of the database configured in temn.tafj.jdbc.ro.urls or

temn.tafj.jdbc.ro.dataSourceNames is unavailable for any reason, session cannot be
launched. This functionality may be altered by changing the below property to false in
tafj.properties.

temn.tafj.jdbc.force.ro.db.check=false

When setting this property to false we will be able to a launch a session even when the
configured RO databases are down but the LIVE database should be active in any case.

Und e r n e a t h the Cover s

The design of the solution appears below from a T24 perspective.

Page 7
TAFJ-R e a d O nly D a t a b a s e

Particular to TAFJ, is the TAFJ_VOC that is a mapping table of T24 internal names to
physical tables (not all fields shown). The highlighted fields are particular to a read-only table
pair (one exists in the transactional database, one exists in the read-only database)

SQL> desc tafj_voc

Name Null? Type

----------------------------------------- -------- ----------------------------

RECID NOT NULL VARCHAR2(255)

XMLRECORD CLOB

ISREADONLY VARCHAR2(1)

ASSOCIATED VARCHAR2(70)

PDATEALIAS VARCHAR2(70)

OTHERATTRIBUTES VARCHAR2(70)

The OTHERATTRIBUTES field stores the schema and database of an associated $RO,
$DIM, or $RR table as :

SCHEMA @FM DATABASE

Page 8
TAFJ-R e a d O nly D a t a b a s e

An example follows:

T24 table F_TABLE is a table that exists in both the transactional and read-only database. A
row would exist in the TAFJ_VOC for it, AND the associated read-only table named
DB.SCHEMA.F_TABLE#RO (which again exists in the separate read-only table). DB
specifies the database in which it is located (local or remote). SCHEMA specifies the
schema in which it is located (local or remote)

RECID TABLENAME ASSOCIATED ISREADONLY PDATEALIAS

OTHERATTRIBUTES

F.TABLE F_TABLE F.TABLE$RO N MYDATE

F.TABLE$RO F_TABLE#RO Y SCHEMA

@FM DB

A synonym for F_TABLE#RO would have been created in the transactional database for the
remote table F_TABLE#RO. This way UNION clauses can be constructed to form an “all
rows” queries.

On the read-only database side, F_TABLE#RO would exist as well as its associated view
with one extra field called PDATE. This field exists as the partition key for very large tables.
Since this field doesn’t exist on the transactional database, queries that want rows from the
read-only side must be rewritten to swap in the PDATE field on the other side. For example:

SELECT RECID FROM V_F_TABLE WHERE MYDATE = ’13-DEC-12’ UNION SELECT

RECID FROM V_F_TABLE#RO WHERE PDATE = ’13-DEC-12’

Above PDATE would replace MYDATE for performance reasons on the second half of the
UNION clause which hits the remote database.

For dimensional files ($DIM) files, they would appear like this:

RECID TABLENAME ASSOCIATED ISREADONLY PDATEALIAS

OTHERATTRIBUTES

Page 9
TAFJ-R e a d O nly D a t a b a s e

F.TABLE$DIM F_TABLE#DIM Y
SCHEMA @FM DB

$DIM files have no association. They exist on their own. Queries on a $DIM file will use the
connection setup to the dimensional database directly.

Page 10
TAFJ-R e a d O nly D a t a b a s e

$RO, $DI M, and $RR File Workfl o w

The workflow for $RO is:

T24 App  READ $RO  TAFJ  Check Live DB  Not Found  Check RO DB  Found
 Read

It is the below for $DIM

T24 App  READ $DIM  TAFJ Check DIM DB  Found  Read

It is the below for $RR

T24 App  READ $RR  TAFJ Check RR DB  Found  Read

There are no need for UNION queries with $DIM file either, since they are assumed to exist
in a different database anyway.

Creat i n g new tabl e s wit h th e CREATE- FILE

co m m a n d
Two commands are needed to create new T24 archive tables.

CREATE-FILE F.TESTROFILE TYPE=XML ASSOCIATE="YES"

The command above creates the table in the transactional database and sets it up for a
“read-only” pair.

CREATE-FILE F.TESTROFILE$RO TYPE=XML DATABASE="TESTRODB"

SCHEMA="TAFJRO" READONLY="YES"

The command above finishes the sequence such the two tables are now linked. Assuming
the database link has been set up correctly, a synonym would be created on the
transactional database to the read-only database. The database targeted is TESTRODB
with schema TAFJRO. Ie, a URL like:

temn.tafj.jdbc.ro.urls=jdbc:oracle:thin:@localhost:1521/TESTRODB

and user like:

Page 11
TAFJ-R e a d O nly D a t a b a s e

temn.tafj.jdbc.ro.usernames= TAFJRO

must exist in the tafj.properties file for this to work (or a datasource correctly defined if in an
application server).

For $DIM files the CREATE-FILE would be as follows:

CREATE-FILE F.TESTROFILE$DIM TYPE=XML DATABASE="TESTDIMDB"

SCHEMA="TAFJRO" READONLY="YES"

This will create the F.TESTROFILE$DIM table on the database TESTDIMDB in the schema
TAFJRO. The view to the table will exist in TESTDIMDB. TAFJRO as well.

Note: CREATE-FILE and DELETE-FILE is not supported for $RR Files.

Creat i n g View s in the $RO Data b a s e

To create views in the read-only database, one must follow the prerequisites for creating
views for TAFJ. See the TAFJ-DB Setup document for details, but briefly you need to:

1) Set up appropriate DB privileges for the database user in the RO database.

2) Created the java stored functions in the target schema of the RO database.

3) Run the javafunctions sql script which maps JAVA to SQL functions in the same
schema in the RO database.

4) DBImport optionally must be run to create F.STANDARD.SELECTION and

F.PGM.FILE in the RO database if you don’t want these two views to fail as both are
added if not “All” files specified (these should be dropped later along with the
TAFJ_VOC).

After doing this, you run DBImport for views only specifying both the OLTP connection for the
source and RO connection as the target. This will create a TAFJ_VOC inside the RO
database that needs to be dropped. This task can also be done with DBUpdate, but in this
case a TAFC VOC file must exist. An example with DBImport follows. DatabaseLinkRO is
absolutely required or DBImport will not understand your target is a read-only database (in
which case certain tables might not be present).

Page 12
TAFJ-R e a d O nly D a t a b a s e

With following properties in the TAFJDBImport.default file where TESTRODB is the RO

database and TESTRODBLINK is the database link from TESTDB to TESTRODB:

URLSourceDB = jdbc:oracle:thin:@localhost:1522/TESTDB

DriverSourceDB = oracle.jdbc.driver.OracleDriver

UserSourceDB = user

PasswordSourceDB = password

DatabaseLinkRO = TESTRODBLINK

Url = jdbc:oracle:thin:@localhost:1522/TESTRODB

Driver = oracle.jdbc.driver.OracleDriver

User = user

Password = password

Page 13
TAFJ-R e a d O nly D a t a b a s e

Click “Next”

Click “Next”

Page 14
TAFJ-R e a d O nly D a t a b a s e

Click “Next”

Page 15
TAFJ-R e a d O nly D a t a b a s e

Click “Next”

specify any tables, a wild card sequence (the below would give you all $RO views only) or All
(in which case views would be regenerated on the OLTP as well as being created in RO
database). As well, synonyms for RO tables would be created in OLTP database (for use in
UNION queries).

DBLin k Creat i o n
Pos t g r e S Q L
In Postgres, to connect between two databases Foreign data wrapper has to be created in
the LIVE database and User Mapping should be done. Edit the values between <> as
required in the below script and execute it in LIVE database with admin user. In PostgreSQL
, generally ‘postgres’ is the admin user.

CREATE USER <ro_user> PASSWORD '<password>';

GRANT <ro_user> TO CURRENT_USER;

Page 16
TAFJ-R e a d O nly D a t a b a s e

CREATE DATABASE “<ro_database>” OWNER <ro_user>;

CREATE EXTENSION dblink;

CREATE SERVER <server_name>

FOREIGN DATA WRAPPER dblink_fdw

OPTIONS (host 'localhost',

dbname '<ro_database> ', port '5432');

Here default port is 5432, change it as per the database configuration.

CREATE USER MAPPING FOR <live_user>

SERVER <server_name>

OPTIONS (user '<ro_user>', password '<password>');

GRANT USAGE ON FOREIGN SERVER db2remote TO <live_user>;

Below statement should return OK .

SELECT dblink_connect('<dblink_name>', '<server_name>');

Above '<dblink_name>' and '<server_name>' has to be configured in tafj.properties as

below,

temn.tafj.jdbc.ro.dataBaseLinks=<dblink_name>;<server_name>

In RO database, create the schema as below,

CREATE SCHEMA “<ro_schema>”;

ALTER ROLE <ro_user> SET search_path TO ““<ro_schema>”;

Page 17