DB2

The Temenos Transact DB2 Direct Connect driver is a middleware component between Temenos Transact and DB2 database. It enables Temenos Transact to send to and retrieve data from DB2 database storage. The data is stored in DB2 server as either XML columns or BLOBs (Binary Large Objects) for internal or work files. This section provides details about the database configuration, commands, transactions and driver environment variables involved in multiple database access and table details.

Having huge Temenos Transact data in single server or database hinders the performance of the database in both transactional and reporting services. Therefore, this data need to be separated categorically as per the business needs.

The Temenos Transact data is classified into volatile (transactional) and non-volatile (read-only) data. The data is separated and stored in different databases, which:

Boosts the performance of the transactional processing
Enables timely retrieval of the historical (non-volatile) data for the reports

The DB2 Direct Connect Driver (DCD) enables you to configure and access maximum of ten databases. Each database can be configured with its own credentials. A table can be created in a specific database for an easier and accurate access. Each table has two columns as listed in the following table.

Column	Description
RECID	Holds the primary key of the table
XMLRECORD	Holds the table data.

If the XMLRECORD is of XML type, the data will be converted from the internal dynamic array format into an XML sequence for insertion into the DB2 database. If the record is of BLOB type, the data will be stored directly in the XMLRECORD column in binary format.

On retrieval of data, the row information from the XMLRECORD column is converted back from an XML sequence into the internal dynamic array format for use by the application.

The Temenos Transact DB2 RDBMS Direct Connect driver is implemented using standard DB2 Command Line functions to communicate with the DB2 database and supports internationalized characters by the standard DB2 RDBMS code page conversion configuration.

Database Configuration

This section provides configuration for the Linux version of DB2. However, the same configuration is applicable for the Windows version as well. You need to make changes corresponding to the operating system while setting the following environment variables.

You can find the location of the DB2 server and edit the following in .profile.

export DB2_HOME=/home/db2inst1/sqllib
export DB2_SHLIB=$DB2_HOME/lib64
export DB2_SHLIB_PATH=$DB2_HOME/lib64
export DB2_LD_LIBRARY_PATH=$DB2_HOME/lib64
export PATH=$DB2_HOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DB2_LD_LIBRARY_PATH
export LIBPATH=$LIBPATH:$DB2_SHLIB

NOTE: For Linux, you need to use EXPORT instead of SET.

To access the database, you need to use the DB2 command line tool db2.

The version of the DB2 server is visible when db2 command is executed.

The XMLDB2 Driver is located in %TAFC_HOME%\XMLDB2 folder. The following table lists the libraries and executables available in the driver.

Libraries	Executable
config.XMLDB2.so config.XMLDB2.so.el	Dynamic linked library DB2 Driver
config.XMLDB2	Executable used for the DB2 driver configuration
libTAFCdb2.so libTAFCdb2.so.el	Shared object for DB2
libTAFCdb2utils.so libTAFCdb2utils.so.el	Shared object for TAFC-DB2 utils
libTAFCtransformer.so libTAFCtransformer.so.el	Shared object for TAFC transformers

The following commands enable you to edit .profile or remote.cmd.

export DRIVER_HOME=$TAFC_HOME/XMLDB2
export JBCOBJECTLIST=$JBCOBJECTLIST:$DRIVER_HOME/lib
export PATH=$PATH:$TAFC_HOME/bin:$DRIVER_HOME/bin

You can configure the DB2 Direct connect driver using the config-XMLDB2 executable. This creates the jedi_config driver configuration file at %TAFC_HOME%\config, which stores all the data entered through this executable.

Commands for Multiple Database Access

This section provides examples of commands that can be used with the DB2 driver and expected output. These commands are mostly built in the Temenos Transact environment, which you can execute with the necessary options when required.

Create File

You can use the CREATE-FILE command with a type qualifier (TYPE) to define the file to be created as a table in the DB2 RBDMS database. For example, TYPE=XMLDB2.

The above command generates the following tables in the DB2 RBDMS database.

Table	Description
D_EXAMPLE_TABLE	This table equates to the dictionary section
EXAMPLE_TABLE	This table equates to the data section. The naming convention shows that the dots in the file name are converted to underscores in table name.

Both the tables have two columns as listed in the following table.

Column	Description
RECID	Holds the primary key of the table
XMLRECORD	Holds the table data.

Generally, the dictionary files are held as NOXMLSCHEMA types (BLOB) as these files are not usually queried.

For each table a cross reference entry is created in the STUBFILES table with the DB2 RDBMS database. In addition, a native file stub is also created for each table in the specified file path (in the above example, it is the current directory). The stub files are used to locate and invoke the correct driver for the corresponding table.

Based on the conversion, the stub file information may alternatively be contained within VOC, (which in turn can also be an RDBMS table), ensuring that all the related information is completely contained within the database.

The file is created in the default database.

File Creation in a Different Database

You can create a database in any other database using the DATABASE qualifier. The following table lists the options that can be used in CREATE-FILE.

Qualifier	Value	Description
TYPE	XMLDB2	Indicates the type of the file to be created.
DATABASE	User specified	Indicates the name of the pre-existing database in which the table needs to be created
TABLE	User specified	Indicates the name of the table
READONLY	YES	Indicates if the table is to be considered as read-only (applicable only for the data file).
NOXMLSCHEMA	YES	Uses BLOB data type instead of XML data type to write the XML data
XSDSCHEMAREG	YES	Registers XSD SCHEMA in the database.
XSDSCHEMA	User Specified	Uses XSD schema to describe the data layout required for long tag XML format data.
KEY	VARCHAR[User specified length in integer] or INTEGER	Indicates the key to alter the data type and length of RECID
ASSOCIATE	YES	Indicates if the table has an associated read only table.

The following example shows a table created in the TSTDB2 database.

The VOC of the table gives the information about the location of the table as follows.

VOC of EXAMPLE.TABLE

VOC of EXAMPLE.TABLE.TSTDB2

EXAMPLE_TABLE Describe

The two cross reference table entries are created in the RDBMS STUBFILE table.

After the table is created, you can add the sample data to the table using standard tools like the command line editor ED or screen editor JED.

You can then view the sample XML data using the DB2 SQL select statement with the XMLRECORD column.

Create Extindex

The CREATE-EXTINDEX command enables you to create indexes in the DB2 RDBMS tables. You need to execute this command manually from the command line, as it is not invoked by any Temenos Transact action.

The following table lists the options to be used with the CREATE-EXTINDEX command.

Options	Description
x	Creates an XML index

The following screen capture displays the extindex for the table in the default database.

You can use the CREATE-EXTINDEX command to create index on tables of the other databases as well in the similar way. The following example screen capture shows a functional index created on a table.

You can invoke a verbose display of the index creation process using the verbose (-v) option on the CREATE-EXTINDEX command line.

Table Creation Using Long Tag XML

Generally, the XML Schema Definition document (.xsd) is not required for DB2 and XML Schema Definition is not registered, by default. However, you can use the long tag elements as per the Temenos Transact XML Schema Definition (.xsd) document and store the definition within the table. The short tag XML is the default format.

NOTE: There is an overhead to system while using the long tag format in the amount of data and the performance of the system.

You can invoke the long tag table XML format by specifying the XSDSCHEMA qualifier when creating the table.

The XML Schema Definition (ACCOUNT in this case) must be:

Generated by the Temenos Transact Standard Selection Rebuild (See XSD Schema Generation User Guide)
Placed in the MSSQL Server Driver schema directory

By default, the XML Schema Definition is not registered in the DB2 RDBMS Database. To register the XML Schema Definition manually, you can add the additional qualifier XSDSCHEMAREG with the CREATE-FILE command line set to YES. For example, XSDSCHEMAREG=YES.

The following screen capture displays an Oracle describe, which shows the table type to be different from the short tag XML CLOB table description

The following screen capture shows an example of a data record in the long tag structured storage format.

The following screen capture shows an example of index creation on a long tag xml table. It is similar to creating indexes for normal files.

Table Querying

You can use the general jBase Query Language (JQL) queries used to query a J4/JR file, to query the tables as well. The driver converts these queries to the corresponding underlying database query and fetches the data. The translated query is logged in the log file. If the translated query is to be displayed on the standard output, you need to set JEDI_XMLDRIVER_DEBUG_DISPLAY. The following are the different commands involved in querying tables.

Read-Only Associate Tables

The Temenos Transact data is classified into volatile (transactional) and non-volatile (read-only) data. The volatile data is retained in LIVE or default database. The non-volatile data is moved to the second database referred to as non-volatile DB.

You can create read-only tables in any of the configured database. However, you cannot do the following.

Create a DICT file for the read-only table
Write, clear or delete a record from the READ-ONLY table. It results in a coredump and generates a log.

The Temenos Transact table will have an associated RO table if the LIVE file contains an ASSOCIATE flag in VOC. This RO table can reside in any of the configured databases. The VOC entry of the LIVE table evaluates the name and location of the ASSOCIATED RO table of the LIVE file.

The LIST-EXTINDEX command on the LIVE table gives the details about the indexes created on the associate RO table as well.

If the LIVE database has an associated RO table in non-volatile DB, the driver queries both the tables and displays the data.

The RO table can be of any name. However, the RO file created should be <“live” file$RO>. A nickname has to be created in the LIVE database as <RO table name> for the RO table.

The user of the first database should have the permission to access and run the scripts on the second database.

When a query is executed on the LIVE file, the records are displayed from both the LIVE file and associated file avoiding the duplicates, by default. If you want to change the default functionality, you need to set the JEDI_XMLDRIVER_ASSOCIATE_FILE variable to any of the following values, as required.

Value	Functionality
1	Disables the redirection and query to the ASSOCIATE table
2	Disables query for ASSOCIATE table, but redirection is active
4	Disables PDATE in the query, but redirection and ASSOCIATE table query with UNION will be active

NOTE: PDATE is a real column in the RO table used for Data Separation. These RO tables are created using the DL.INITIAL.SEPARATION Utility tool and not manually.

If the query returns an error message as shown in the following screen capture, you need to check whether the nickname is pointing to the valid object.

Transaction in Multiple Databases

When a WRITE or UPDATE action is performed within the transaction boundary (between TRANSTART and TRANSEND), it is termed a transaction. The transaction starts only with WRITE, so only the database where the data is written is in the transaction. The transaction can also read a file from one database and write to another file from a different database.

The transaction aborts on updating or writing data to the files of multiple databases resulting in a coredump.

Driver Environment Variables

You need to configure the following environment variables to be used with the DB2 Direct Connect Driver.

Internationalisation

JBASE_I18N=1 (Mandatory)
JBASE_CODEPAGE=utf8
JBASE_LOCALE=en_US
JBASE_TIMEZONE=Europe/London
JBASE_CORE_DUMP=1 (Force core dump on segmentation violation)

TIP: You can use the jtimezones keyword to list all the possible values for JBASE_TIMEZONE configuration.

Optional

The following table lists the optional variables and their functionality.

Command	Functionality
JEDI_XMLDRIVER_TRACE=1	Traces all driver functions
JEDI_XMLDRIVER_DEBUG=1	Traces only query translations
JEDI_XMLDRIVER_NO_SPACE_PRESERVE=1	Indicates that the white space is not preserved in xml Trace
JEDI_XMLDRIVER_PREFETCH_ROWS = n	Indicates the number of rows to be pre-fetched in each fetch. The default value is 500.
JEDI_XMLDRIVER_ENABLE_DB_SORT=1	Enables the DB sort instead of JQL Sort
JEDI_XMLDRIVER_DISABLE_RECID_NUMSORT=1	Ignores the data type of the RECID while sorting on RECID
JEDI_XMLDRIVER_ENABLE_EDICT_TYPE=1	Enables the EDICT data type detection
JEDI_XMLDRIVER_DISABLE_DATABASE_LOCKS=1	Disables the DB row locks

NOTE: For more details, refer http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/index.jsp

Bookmark Name	Actions

BACK

In this topic

Add Bookmark View Bookmark

Previous Docs

Feedback