DB2

You can configure the server for the database as it is automatically identified by the instance once the name of the database is given.

The first database is considered the default database. You can configure a maximum of ten databases with its own (server and user) credentials.

You can list or change the current settings using the options in Database menu.

You can add or edit the database settings as required using the Set Database option in Database menu.

You can use this option only for the configuration of the default database.

You can configure the user credentials of the first (default) database using the Set DB login user name and Set DB login password options in User menu. If you do not configure the user credentials for other databases, the user credentials of the first database is made available for all the databases.

You can list or change the current settings using the options in User menu.

The install.sql script is available at %DRIVER_HOME%\sql. When the path is given, config-XMLDB2 loads the scripts, invokes sqlcmd at the command prompt and executes the script.

The install.sql script does the following.

1. Creates STUBFILES table for table creation cross reference

2. Creates T24LOCKTABLE to store the locks

3. Creates functions like numsort, numcast

4. Creates a view for the stubfiles

The driver tries to connect to all the configured databases. If the Connection successful… message appears for all the databases, the driver will be able to connect to DB2. If the connection fails, you need to check the database and its credentials.

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.

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

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.

You can use the DELETE-FILE command to delete the file. This command deletes both the DICT and DATA parts of the corresponding table in the database and deletes the reference from the stub.

The Oracle driver detects the table in any of the database and deletes the table, cross reference and even the stub file entry.

The STUBFILE table update is as follows.

File Deletion in a Different Database

There is no change in the deletion of the file. The driver picks up the name of the database in which the database resides from VOC and deletes it.

The CREATE-VIEW command enables you to create a view of the table such that, the column names can be used within SQL statements to refer the underlying XML. The command will process all the extended dictionary entries related to the file and generates the appropriate view in the DB2 RDBMS database. You need to use the verbose option (-v) to output the generated view to the terminal. This command will be executed against the table on a rebuild of the Temenos Transaction Standard Selection.

If the corresponding file is not the default file in another database, VOC is used to resolve the location of the table.

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.

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.

The LIST-EXTINDEX command enables you to display all the indexes created on a table.

The DELETE-EXTINDEX command enables you to delete all the indexes created on a table.

The following format is used for the select command.

Select <filename> [WITH <ATTR> = <VALUE>]

The following sample shows @ID (Right justified) converted to numeric using numcast function.

The following sample shows ACCOUNT.TITLE.1 (left justfied) considered as a string.

The following format is used for the count command.

Count <filename>

The following format is used for the select command.

List <filename>  [ATTR1] [ATTR2]….. [ATTRN] [WITH <ATTR>=  <VALUE>]

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.

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.

Querying a file without Indexes

Creation of indexes on the files affects the query translation from JQL to SQL. When indexes are not created, the query is translated as shown in the following screen capture. The status of query translation is failure. The select statement is modified to the default selection of the table by ignoring the criteria.

The following sample command explains the creation of a BASE64 file in DB2.

jsh -->CREATE-FILE SAMPLEDB2_BASE64 TYPE=XMLDB2 NOXMLSCHEMA=BASE64
[ 417 ] File SAMPLEDB2_BASE64]D created , type = XMLDB2
[ 417 ] File SAMPLEDB2_BASE64 created , type = XMLDB2

The following sample command shows the usage of jstat to view the file stat.

  jsh -->jstat -v SAMPLEDB2_BASE64
 
DIRECT CONNECT DRIVER: XMLDB2 Version: R11.92279
 
Type = XMLDB2
 
DATABASE INFO   : - 09.07.0000
           Server = DB2/LINUXX8664
           User Schema = db2inst1
FILE INFO     : Key = VARCHAR(255) Record = BASE64 XML
           Table = SAMPLEDB2_BASE64, File = ./SAMPLEDB2_BASE64

The following script shows the sample data with its representation in the database table. The data is represented in the xml field as BASE64 data.

db2 => select * from SAMPLEDB2_BASE64
 
RECID  XMLRECORD 
----------------------------
R1    <row id="R1" xml:space="preserve"><c1>TVYx/U1WMv0=</c1></row>
 
 1 record(s) selected.  

The following script shows the representation of Base64 in the STUBFILES table.

db2 => select * from STUBFILES WHERE ID like '%SAMPLEDB2_BASE64%'
 
ID                     PATH         TYPE
-----------------  -----------------------------------   ------
D_SAMPLEDB2_BASE64     /home/stevew/shivtest/SAMPLEDB2_BASE64]D BASE64
SAMPLEDB2_BASE64      /home/stevew/shivtest/SAMPLEDB2_BASE64  BASE64
 
 2 record(s) selected.  

The following script shows some sample queries on the BASE64 file.

jsh -->SELECT SAMPLEDB2_BASE64 WITH @ID like 'm2'
SELECT: SQL:2:Full: SELECT t.RECID FROM SAMPLEDB2_BASE64 t WHERE RECID LIKE 'm2' ESCAPE '\'
 
 No Records selected
 
jsh -->SELECT SAMPLEDB2_BASE64 WITH ATTR2 like 'm2'
accJQL2SQLTranslator::processLeafNode: *** WARNING *** jQLHandler exception: An internal error - "Neither i-types nor BLOB field are supported in JQL2SQL translation" occured.
 
SELECT: SQL:0:Fail (default): SELECT RECID, XMLRECORD FROM SAMPLEDB2_BASE64 FOR READ ONLY
 
 No Records selected  

You need to create a DB2 type file using appropriate TYPE in CREATE-FILE to create the correct driver file. The file creation changes according to the driver used.

jsh-->CREATE-FILE DB2_EXTDICT TYPE=XMLDB2
[ 417 ] File DB2_EXTDICT]D created , type = XMLDB2
[ 417 ] File DB2_EXTDICT created , type = XMLDB2

The following script shows the definition of dictionary for the attributes created along with the file.

jsh -->JED DICT DB2_EXTDICT
  @ID
001 D
002 0
003
004 @ID
005 10L
006 S
  ATTR1
001 D
002 1
003
004 ATTR1
005 10L
006 S
007
...... (Truncated for convenience)
029
030 JBASE_EDICT_START
031 101
032
033
034 ATTR1
035
036
037
038
039 1073741824
040
041
042
043
044
045
046
047 JBASE_EDICT_END
 
ATTR2
001 D
002 2
003
004 ATTR2
005 10R
006 S
007
...... (Truncated for convenience)
029
030 JBASE_EDICT_START
031 108
032
033
034 ATTR2
035
036
037
038
039 1073741824
040
041
042
043
044
045
046
047 JBASE_EDICT_END
  

ATTR1 is a numeric type since the value 101 is used in the extended dictionary type and ATTR2 is a string type as 108 is used in the extended dictionary type.

Before you start working with the created file, you need to set up the following environment variables to utilise the extended dictionary.

You need to use export command to enable these environment variables in AS5, AIX, Solaris, HP-Unix and all other UNIX clones.

export JEDI_XMLDRIVER_ENABLE_ALL_COLUMNS=1
export JEDI_XMLDRIVER_ENABLE_EDICT_TYPE=1  

You need to use set command to enable these environment variables in Windows based systems.

set JEDI_XMLDRIVER_ENABLE_ALL_COLUMNS=1
set JEDI_XMLDRIVER_ENABLE_EDICT_TYPE=1

Without these two environment variables, DCD will not use the correct comparison function in the SQL translation.

This section provides sample queries on the numeric and string attributes.

Numeric Attribute

The following table lists the sample query on the numeric attribute.

jsh	SELECT
jsh -->SELECT DB2_EXTDICT WITH ATTR1 EQ 100	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[ fn:number(c1/text()) =100]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR1 NE 100	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[ fn:number(c1/text()) !=100 or fn:not(c1/text())]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR1 GE 100	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[ fn:number(c1/text()) >=100]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR1 LE 100	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[ fn:number(c1/text()) <=100 or fn:not(c1/text())]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR1 EQ '100'	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[ fn:number(c1/text()) =100]' PASSING XMLRECORD AS "xmlrecvar"

NOTE: The last translation shows that only numeric comparison is used even if the value is in quotes.

String Attribute

The following table lists the sample query on the string attribute.

jsh	SELECT
jsh -->SELECT DB2_EXTDICT WITH ATTR2 EQ 100	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[c2/text()="100"]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR2 NE 100	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[c2/text()!="100" or fn:not(c2/text())]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR2 GE 100	SELECT: SQL:1:Part: SELECT t.RECID,t.XMLRECORD.getClobVal() FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[c2/text()>="100"]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR2 LE 100	SELECT: SQL:1:Part: SELECT t.RECID,t.XMLRECORD.getClobVal() FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[c2/text()<="100" or fn:not(c2/text())]' PASSING XMLRECORD AS "xmlrecvar")
jsh -->SELECT DB2_EXTDICT WITH ATTR2 EQ '100'	SELECT: SQL:2:Full: SELECT t.RECID FROM DB2_EXTDICT t WHERE xmlexists('$xmlrecvar/row[c2/text()="100"]' PASSING XMLRECORD AS "xmlrecvar")

Only string compare is used with ATTR2 since it is defined as 108 in the Extended Dict.

This enhancement is enabled by setting the following environment variable on Unix.

export JEDI_XMLDRIVER_ENABLE_TIMED_QUERIES=N

In the above variable, N indicates the maximum query duration in seconds. Setting the variable to zero will have no effect. Any positive value enables logging of all query duration times in the driver log file.

When the DCD Driver issues a call through the RDBMS client interface to submit a query to the database, a SIGALRM signal is initiated with the timeout value as a parameter, so that it triggers when the period expires. This calls a function, which will set an error value indicating query timeout, and do a SIGINT to interrupt the wait for query response. If the query completes on time, the SIGALRM is reset and processing continues as normal.

The level of logging depends on the tracing options selected by environment variables settings. If the TRACE option is selected, the following message is recorded.

accExecuteSelect : Set query timeout for <SQL query> to <N> seconds

If a timeout occurs for a query, the following message is written, regardless of tracing options.

accExecuteSelect: ***  ERROR *** Query <SQL query>  timed out after <N> seconds  

When the query completes normally within the timeout period and DISPLAY tracing option is selected to write messages to the screen, the following message is displayed.

SELECT: QUERY : <SQL query>,  started  <T>,  duration <D> seconds

The following table lists the keys in the above command and their descriptions.

The following is a sample query with the log file data. The environment variable JEDI_XMLDRIVER_ENABLE_TIMED_QUERIES is set to 1 in .profile.

The following environment variables should be set in .profile to enable the logging facility.

• JEDI_XMLDRIVER_TRACE=1
• JEDI_XMLDRIVER_DEBUG=1
• JEDI_XMLDRIVER_DEBUG_DISPLAY=1

This base key is used for the encryption algorithm along with the user provided encryption key to encrypt the user data. This is the secret key of Temenos. However, the user can configure the base key using the config-XMLDB2 utility. It is stored in jedi_config in encrypted format. This forms from the first level of security for the database. Removal of this base key from jedi_config corrupts the database.

You can also configure the base key using the command line arguments of config-XMLDB2 utility.

The jedi_config file will have the XMLDB2_FRMWRK record with the details of the server, database, user credentials and base key in encrypted form.

The following screen captures show tables with different types of data.

XML Type

BLOB Type

Promoted Column

A file having promoted column is also encrypted if the file is created using the ENCRYPT keyword.

Real Column

The data encryption is not available for the real column. Hence, the real column maintains the performance benefits of the indices.

Insert/Update Function

You can insert or update the data using either JED editor or WRITE/WRITEU functions of JBC. The data entered will be encrypted and stored in the database.

When queried, the record is displayed in the encrypted form.

Data Encryption

The following screen captures show tables with different types of data in encrypted form.

XML Data

BLOB Data

Promoted Column Data

The LASTNAME attribute is converted into promoted columns.

Real Column Data

The CITY (Attribute 5) of the file is converted into real column. The real column is not encrypted. Hence, indexes can be created and used.

Duplicating Encrypted data

You can copy data from one encrypted file to another. Encrypt key of the first file is used to decrypt the data and encrypt key of the second file is used to re-encrypt and store it in the table.

Query

When the data is queried through JQL, the query is translated into SQL. This translation enables you to use all the database performance improvement functionalities including index.

However, when the data is queried using a criteria, this functionality is suppressed because of the encryption. The stored data is encrypted and hence cannot be compared with the user-supplied data. Therefore, a default translation occurs bringing up all the data from the database and then filtering it in jBASE.

Without Criteria

With Criteria

Customisation

You can enable table encryption on all the tables to be created in future using the following environment variable.

set/export JEDI_XMLDRIVER_ENCRYPT_ALL=key  

Restriction

You can rename or copy the encrypted table only through jBASE and never from the backend. Also, you should not change the base key after creating the encrypted tables, else, the tables get corrupted.