Oracle

You can configure the server for the database by entering the machine name or IP address of the Oracle server.

The first database is considered the default database. For default database, you can set the server name using the Set Server option in Database menu. You can set the user name and the password for this database using User Settings. You can also configure the username and password for all additional databases. You need to set the server name for the database as the name of the database.

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 also set data and index tablespaces using the corresponding options in Database menu. The following screen captures show the data and index tablespaces set to T24DATA and T24INDEX, respectively.

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 store the password in either in jedi_config or external vault. To store the password in an external vault, the following environment variable must be set.

set/export JEDI_XMLDRIVER_ENABLE_EXT_PWD=1

The library should be named as libUserAPI.so (for *nix) and libUserAPI.dll (for Win) and placed in the $TAFC_HOME/lib folder. The library should contain the API, which returns the password.

The signature of the user defined API should be in the following format.

char * getExtPassword (char* database, char* schema ,char* username)

If the external vault option is enabled, DCD consumes the password provided by the user API. Otherwise, it will consume the API stored in jedi_config.

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-XMLMSSQL loads the scripts, invokes sqlcmd at the command prompt and executes the script.

The install.sql script creates the following.

1. STUBFILES table for table creation cross reference

2. T24LOCKTABLE to store the locks

3. Functions like numsort, numcast

4. 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 Oracle. If the connection fails, you need to check the database, its credentials and reconfirm whether the service and listener are running.

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

The above command generates the following tables in the Oracle 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 Oracle 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	XMLORACLE	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).
DATATABLESPACE	User specified	Indicates the name of the tablespace where the data needs to be stored
INDEXTABLESPACE	User specified	Indicates the name of the tablespace where the indexes needs to be stored
KEY	VARCHAR[User specified length in integer] or INTEGER	Indicates the key to alter the data type and length of RECID
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.
ASSOCIATE	YES	Indicates if the table has an associated read only table.

The following example shows a table created in the RODB 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.RODB

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 Oracle 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 Oracle 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 Oracle 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. You can use this command on any table irrespective of the location of their database.

The following format is used for the select command.

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

The following sample shows @ID (Right justified) comparing with a number.

The following sample shows ACCOUNT.TITLE.1 (left justfied) comparing with 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 ignoring the criteria.

Querying a file with Indexes

You can create an index on an attribute in LIVE or RO table. The driver looks up for the index on an attribute in both LIVE and RO tables. The index will be used for the query translation to enhance the data retrieval process.

The following example shows the query translation of the query with search criteria on CATEGORY. A function-based index is created on CATEGORY in non-volatile DB and not in LIVE.

If an attribute has different types of indexes in LIVE and RO, the justification of the attribute is used as the criteria to translate the query.

For example, consider a right justified attribute CATEGORY. It has xpath index in LIVE and function-based index in RO. The driver ignores the xpath index and uses the function-based index for query translation, since the attribute is right justified.

For left justified attributes XPATH index will be applied.

Data Sorting

To enable the database sort, you need to set JEDI_XMLDRIVER_ENABLE_DB_SORT=1. The status of query translation depends on the index of the attribute used in the search criteria and its justification.

SSELECT

SORT on RECID with search criteria

SORT on attribute with search criteria

The query translation status is Partial. Only the search criteria are translated and sorting is done by JQL.

Similarly, when SORT is on RECID with search criteria, but RECID is right justified the query translation is partial.