Platform Framework
R24 AMR | Min(s) read

TAFJ Promoted Columns

This section explains how to promote columns for TAFJ for performance improvements.

Promoted columns are relational columns in which the data is generated through a function when it is inserted or updated for these columns to perform better when indexed over an xml index.

This is applicable for single value columns. To index multi-value columns, see Oracle’s XMLINDEX clause. The advanced use case shows how to index a multi-value without XMLINDEX, but it requires a code change that probably will not work with a normal Temenos Transact use case. Below is an example of an XMLINDEX for Oracle:

create index IX_FBNK_CUSTOMER_NAME_1_3 ON FBNK_CUSTOMER(XMLRECORD) INDEXTYPE IS XDB.XMLINDEX PARAMETERS ('PATHS (INCLUDE (/row/c3))');

If you want to index the multi-values in attribute c3 of the xml document, then the following SQL mode command outputs a script to create a promoted column for indexing.  Use it with the SPOOL command.

tafj@localhost:TB201507>SPOOL

tafj@localhost:TB201507>CREATE-PROMOTED-COL IX_RANK ATTRIBUTE=2 DATATYPE=VARCHAR LENGTH=30 COLNAME=RANK TABLENAME=FBNK_CURRENCY

tafj@localhost:TB201507>SPOOL

The resulting script can be found under $TAFJ_HOME/log/DBTools.

The below example codes show how to promote the RANK column (xml attribute 1) in the FBNK_CURRENCY table in different servers or databases.

set QUOTED_IDENTIFIER on;
go

CREATE FUNCTION udf_RANK_CURRENCY_C1 (@xmlrecord XML) 
RETURNS integer 
WITH SCHEMABINDING 
BEGIN
RETURN @xmlrecord.value('(/row/c1/text())[1]', 'integer')
END

ALTER TABLE FBNK_CURRENCY
ADD RANK AS dbo.udf_RANK_CURRENCY_C1(XMLRECORD) PERSISTED

CREATE INDEX ix_FBNK_CURRENCY_RANK ON FBNK_CURRENCY(RANK)

drop function extractC1_INT@

create function extractC1_INT(xmlrecord XML)
returns INTEGER
language sql contains sql
no external action deterministic
return xmlcast(xmlquery('$t/row/c1' passing xmlrecord as "t") as varchar(10))@

set integrity for FBNK_CURRENCY off@

alter table FBNK_CURRENCY add RANK INTEGER generated always as (extractC1_INT(XMLRECORD))@

set integrity for FBNK_CURRENCY immediate checked force generated @

create index IX_FBNK_CURRENCY_RANK on FBNK_CURRENCY(RANK)@

alter table fbnk_currency
add (
RANK  number(10)   as (NVL(CAST(extractValue(xmlrecord,'/row/c1') as NUMBER),0))
);

create index IX_FBNK_CURRENCY_RANK on FBNK_CURRENCY(RANK)

Drop and Recreate Views

In all databases, you need to drop and recreate view so that the new column goes directly to the rdbms column and not the XML column when you query the view.  You can retrieve a view from a particular database or generate it using DBImport with DBImport logging set to DEBUG in $TAFJ_HOME/conf/TAFJTrace.properties. This section provides you the examples of getting and recreating views on Oracle and DB2 databases, respectively.

You can get the view definition from Oracle database using the following SQL commands.

SQL> set long 100000
SQL> select text from all_views where view_name = 'TAFJV_FBNK_CURRENCY';

You can recreate the view to use the new column RANK in DB2 database using the following commands.

CREATE VIEW TAFJV_FBNK_CURRENCY as
SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "CURRENCY_CODE"
,RANK "RANK"
,XMLCAST(XMLQUERY('$d/row/c2[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "NUMERIC_CCY_CODE"
,XMLCAST(XMLQUERY('$d/row/c3[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CCY_NAME"
,XMLQUERY('$d/row/c3' passing a.XMLRECORD as "d") "CCY_NAME_3"
,XMLCAST(XMLQUERY('$d/row/c4[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "NO_OF_DECIMALS"
,XMLCAST(XMLQUERY('$d/row/c5[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "QUOTATION_CODE"
,XMLCAST(XMLQUERY('$d/row/c6[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "QUOTATION_PIPS"
,XMLCAST(XMLQUERY('$d/row/c7[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DAYS_DELIVERY"
,XMLCAST(XMLQUERY('$d/row/c8[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DAYS_FORWARD"
,XMLCAST(XMLQUERY('$d/row/c9[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "INTEREST_DAY_BASIS"
,XMLCAST(XMLQUERY('$d/row/c10[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RATE_ALLOWANCE"
,XMLCAST(XMLQUERY('$d/row/c11[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "FIXING_DATE"
,XMLCAST(XMLQUERY('$d/row/c12[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CURRENCY_MARKET"
,XMLQUERY('$d/row/c12' passing a.XMLRECORD as "d") "CURRENCY_MARKET_12"
,XMLCAST(XMLQUERY('$d/row/c13[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "QUOTATION_SUSP"
,XMLQUERY('$d/row/c13' passing a.XMLRECORD as "d") "QUOTATION_SUSP_13"
,XMLCAST(XMLQUERY('$d/row/c14[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "MID_REVAL_RATE"
,XMLQUERY('$d/row/c14' passing a.XMLRECORD as "d") "MID_REVAL_RATE_14"
,XMLCAST(XMLQUERY('$d/row/c15[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEFAULT_SPREAD"
,XMLQUERY('$d/row/c15' passing a.XMLRECORD as "d") "DEFAULT_SPREAD_15"
,XMLCAST(XMLQUERY('$d/row/c16[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "BUY_RATE"
,XMLQUERY('$d/row/c16' passing a.XMLRECORD as "d") "BUY_RATE_16"
,XMLCAST(XMLQUERY('$d/row/c17[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "SELL_RATE"
,XMLQUERY('$d/row/c17' passing a.XMLRECORD as "d") "SELL_RATE_17"
,XMLCAST(XMLQUERY('$d/row/c18[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "UPTO_SMALL_AMT"
,XMLQUERY('$d/row/c18' passing a.XMLRECORD as "d") "UPTO_SMALL_AMT_18"
,XMLCAST(XMLQUERY('$d/row/c19[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "TRSY_SMALL_SPRD"
,XMLQUERY('$d/row/c19' passing a.XMLRECORD as "d") "TRSY_SMALL_SPRD_19"
,XMLCAST(XMLQUERY('$d/row/c20[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CUST_SMALL_SPRD"
,XMLQUERY('$d/row/c20' passing a.XMLRECORD as "d") "CUST_SMALL_SPRD_20"
,XMLCAST(XMLQUERY('$d/row/c21[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "NEGOTIABLE_AMT"
,XMLQUERY('$d/row/c21' passing a.XMLRECORD as "d") "NEGOTIABLE_AMT_21"
,XMLCAST(XMLQUERY('$d/row/c22[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "TRSY_MED_SPREAD"
,XMLQUERY('$d/row/c22' passing a.XMLRECORD as "d") "TRSY_MED_SPREAD_22"
,XMLCAST(XMLQUERY('$d/row/c23[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CUST_MED_SPREAD"
,XMLQUERY('$d/row/c23' passing a.XMLRECORD as "d") "CUST_MED_SPREAD_23"
,XMLCAST(XMLQUERY('$d/row/c24[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "REVAL_RATE"
,XMLQUERY('$d/row/c24' passing a.XMLRECORD as "d") "REVAL_RATE_24"
,XMLCAST(XMLQUERY('$d/row/c25[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "TRSY_LIMIT_AMT"
,XMLQUERY('$d/row/c25' passing a.XMLRECORD as "d") "TRSY_LIMIT_AMT_25"
,XMLCAST(XMLQUERY('$d/row/c26[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "MIN_ROUND_AMOUNT"
,XMLCAST(XMLQUERY('$d/row/c27[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CASH_ONLY_ROUNDING"
,XMLCAST(XMLQUERY('$d/row/c28[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "MIN_ROUND_TYPE"
,XMLCAST(XMLQUERY('$d/row/c29[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CASH_ROUND_TYPE"
,XMLCAST(XMLQUERY('$d/row/c31[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "PRECIOUS_METAL"
,XMLCAST(XMLQUERY('$d/row/c32[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "EQUIVALENT_CCYS"
,XMLQUERY('$d/row/c32' passing a.XMLRECORD as "d") "EQUIVALENT_CCYS_32"
,XMLCAST(XMLQUERY('$d/row/c33[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "COUNTRY_CODE"
,XMLCAST(XMLQUERY('$d/row/c34[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "FIXED_RATE"
,XMLCAST(XMLQUERY('$d/row/c35[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "FIXED_CCY"
,XMLCAST(XMLQUERY('$d/row/c36[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "FIXED_START_DATE"
,XMLCAST(XMLQUERY('$d/row/c37[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "BASE_CCY_RANK"
,XMLCAST(XMLQUERY('$d/row/c38[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "AVAILABLE_DATE"
,XMLCAST(XMLQUERY('$d/row/c39[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "BLOCKED_DATE"
,XMLCAST(XMLQUERY('$d/row/c40[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CUT_OFF_TIME"
,XMLCAST(XMLQUERY('$d/row/c41[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CLS_CCY"
,XMLCAST(XMLQUERY('$d/row/c42[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED14"
,XMLCAST(XMLQUERY('$d/row/c43[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED13"
,XMLCAST(XMLQUERY('$d/row/c44[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED12"
,XMLCAST(XMLQUERY('$d/row/c45[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED11"
,XMLCAST(XMLQUERY('$d/row/c46[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED10"
,XMLCAST(XMLQUERY('$d/row/c47[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED9"
,XMLCAST(XMLQUERY('$d/row/c48[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED8"
,XMLCAST(XMLQUERY('$d/row/c49[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED7"
,XMLCAST(XMLQUERY('$d/row/c50[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED6"
,XMLCAST(XMLQUERY('$d/row/c51[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED5"
,XMLCAST(XMLQUERY('$d/row/c52[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED4"
,XMLCAST(XMLQUERY('$d/row/c53[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED3"
,XMLCAST(XMLQUERY('$d/row/c54[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED2"
,XMLCAST(XMLQUERY('$d/row/c55[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RESERVED1"
,XMLCAST(XMLQUERY('$d/row/c56[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "OVERRIDE"
,XMLQUERY('$d/row/c56' passing a.XMLRECORD as "d") "OVERRIDE_56"
,XMLCAST(XMLQUERY('$d/row/c57[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "RECORD_STATUS"
,XMLCAST(XMLQUERY('$d/row/c58[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CURR_NO"
,XMLCAST(XMLQUERY('$d/row/c59[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "INPUTTER"
,XMLQUERY('$d/row/c59' passing a.XMLRECORD as "d") "INPUTTER_59"
,XMLCAST(XMLQUERY('$d/row/c60[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DATE_TIME"
,XMLQUERY('$d/row/c60' passing a.XMLRECORD as "d") "DATE_TIME_60"
,XMLCAST(XMLQUERY('$d/row/c61[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "AUTHORISER"
,XMLCAST(XMLQUERY('$d/row/c62[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "CO_CODE"
,XMLCAST(XMLQUERY('$d/row/c63[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "DEPT_CODE"
,XMLCAST(XMLQUERY('$d/row/c64[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "AUDITOR_CODE"
,XMLCAST(XMLQUERY('$d/row/c65[position()=1]' passing a.XMLRECORD as "d") as VARCHAR(4000)) "AUDIT_DATE_TIME"
FROM 
"FBNK_CURRENCY" a

USE CASE

Below is an advanced example with column SCRIPT.GROUP (a multi-value column).

SELECT F.SEAT.SCRIPTS WITH SCRIPT.STATUS EQ 'ACTIVE' AND WITH SCRIPT.GROUP EQ 'TB01-START'

SELECT RECID FROM "TAFJV_F_SEAT_SCRIPTS" WHERE "SCRIPT_STATUS" = 'ACTIVE' and ( XMLEXISTS('$t/c10[text()="TB01-START"]' PASSING "SCRIPT_GROUP_10" as "t") )

The above creates a full table scan. You can index the column by using the following methods:

Alter Table for each multi-value

ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_1  VARCHAR2(4000) as ( substr(extractValue(xmlrecord,'/row/c10[1]'),1,100) ));
ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_2  VARCHAR2(4000) as ( substr(extractValue(xmlrecord,'/row/c10[2]'),1,100) ));
ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_3  VARCHAR2(4000) as ( substr(extractValue(xmlrecord,'/row/c10[3]'),1,100) ));
ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_4  VARCHAR2(4000) as ( substr(extractValue(xmlrecord,'/row/c10[4]'),1,100) ));
ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_5  VARCHAR2(4000) as ( substr(extractValue(xmlrecord,'/row/c10[5]'),1,100) ));

Create Indexes for each new column

CREATE INDEX ix_f_seat_scripts_script_grp_1 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_1);
CREATE INDEX ix_f_seat_scripts_script_grp_2 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_2);
CREATE INDEX ix_f_seat_scripts_script_grp_3 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_3);
CREATE INDEX ix_f_seat_scripts_script_grp_4 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_4);
CREATE INDEX ix_f_seat_scripts_script_grp_5 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_5);
CREATE INDEX ix_f_seat_scripts_script_grp_n ON F_SEAT_SCRIPTS (SCRIPT_GROUP_N);

Recreate the view with new columns

CREATE OR REPLACE VIEW TAFJV_F_SEAT_SCRIPTS as SELECT a.RECID, a.XMLRECORD "THE_RECORD"
,a.RECID "SCRIPT_ID"
,extractValue(a.XMLRECORD,'/row/c1[position()=1]') "DESCRIPT"
,extract(a.XMLRECORD,'/row/c1') "DESCRIPT_1"
,extractValue(a.XMLRECORD,'/row/c2[position()=1]') "COMPANY_CODE"
,extractValue(a.XMLRECORD,'/row/c3[position()=1]') "SCRIPT_STATUS"
,extractValue(a.XMLRECORD,'/row/c4[position()=1]') "SCRIPT_SOURCE"
,extractValue(a.XMLRECORD,'/row/c5[position()=1]') "ALTERNATE_REF"
,extractValue(a.XMLRECORD,'/row/c6[position()=1]') "SELECT_ROUTINE"
,extractValue(a.XMLRECORD,'/row/c7[position()=1]') "BASE_RELEASE"
,extractValue(a.XMLRECORD,'/row/c8[position()=1]') "PRODUCT_GROUP"
,extractValue(a.XMLRECORD,'/row/c9[position()=1]') "PRODUCT_CODE"
,extractValue(a.XMLRECORD,'/row/c10[position()=1]') "SCRIPT_GROUP"
,SCRIPT_GROUP_N "SCRIPT_GROUP_10"
,SCRIPT_GROUP_1
,SCRIPT_GROUP_2
,SCRIPT_GROUP_3
,SCRIPT_GROUP_4
,SCRIPT_GROUP_5
,extractValue(a.XMLRECORD,'/row/c11[position()=1]') "APPLICATION"
,extractValue(a.XMLRECORD,'/row/c12[position()=1]') "STATIC_SETUP"
,extractValue(a.XMLRECORD,'/row/c13[position()=1]') "VERSION"
,extractValue(a.XMLRECORD,'/row/c14[position()=1]') "FUNCTION"
,extractValue(a.XMLRECORD,'/row/c15[position()=1]') "TXN_ID"
,extractValue(a.XMLRECORD,'/row/c16[position()=1]') "FIELD_NAME"
,extract(a.XMLRECORD,'/row/c16') "FIELD_NAME_16"
,extractValue(a.XMLRECORD,'/row/c17[position()=1]') "FIELD_VALUE"
,extract(a.XMLRECORD,'/row/c17') "FIELD_VALUE_17"
,extractValue(a.XMLRECORD,'/row/c18[position()=1]') "FIELD_INPUT"
,extract(a.XMLRECORD,'/row/c18') "FIELD_INPUT_18"
,extractValue(a.XMLRECORD,'/row/c19[position()=1]') "SEAT_ID"
,extractValue(a.XMLRECORD,'/row/c20[position()=1]') "UPDATE_APPL"
,extract(a.XMLRECORD,'/row/c20') "UPDATE_APPL_20"
,extractValue(a.XMLRECORD,'/row/c21[position()=1]') "UPDATE_SCRPT_ID"
,extract(a.XMLRECORD,'/row/c21') "UPDATE_SCRPT_ID_21"
,extractValue(a.XMLRECORD,'/row/c22[position()=1]') "UPD_APPL_FLD_IDEN"
,extract(a.XMLRECORD,'/row/c22') "UPD_APPL_FLD_IDEN_22"
,extractValue(a.XMLRECORD,'/row/c23[position()=1]') "GENERATE_ERROR"
,extractValue(a.XMLRECORD,'/row/c24[position()=1]') "DEFINE_ERROR"
,extractValue(a.XMLRECORD,'/row/c25[position()=1]') "CREATED_BY"
,extractValue(a.XMLRECORD,'/row/c26[position()=1]') "APPROVED_BY"
,extractValue(a.XMLRECORD,'/row/c27[position()=1]') "USER"
,extractValue(a.XMLRECORD,'/row/c28[position()=1]') "MSG_FROM_FILE"
,extractValue(a.XMLRECORD,'/row/c29[position()=1]') "TPR_FILE_NAME"
,extractValue(a.XMLRECORD,'/row/c30[position()=1]') "CREATED_DATE"
,extractValue(a.XMLRECORD,'/row/c31[position()=1]') "LAST_MODIFIED_DATE"
,extractValue(a.XMLRECORD,'/row/c32[position()=1]') "IO_ANALYSIS"
,extract(a.XMLRECORD,'/row/c32') "IO_ANALYSIS_32"
,extractValue(a.XMLRECORD,'/row/c33[position()=1]') "RESERVED_2"
,extractValue(a.XMLRECORD,'/row/c34[position()=1]') "RESERVED_1"
,extractValue(a.XMLRECORD,'/row/c36[position()=1]') "OVERRIDE"
,extract(a.XMLRECORD,'/row/c36') "OVERRIDE_36"
,extractValue(a.XMLRECORD,'/row/c37[position()=1]') "RECORD_STATUS"
,extractValue(a.XMLRECORD,'/row/c38[position()=1]') "CURR_NO"
,extractValue(a.XMLRECORD,'/row/c39[position()=1]') "INPUTTER"
,extract(a.XMLRECORD,'/row/c39') "INPUTTER_39"
,extractValue(a.XMLRECORD,'/row/c40[position()=1]') "DATE_TIME"
,extract(a.XMLRECORD,'/row/c40') "DATE_TIME_40"
,extractValue(a.XMLRECORD,'/row/c41[position()=1]') "AUTHORISER"
,extractValue(a.XMLRECORD,'/row/c42[position()=1]') "CO_CODE"
,extractValue(a.XMLRECORD,'/row/c43[position()=1]') "DEPT_CODE"
,extractValue(a.XMLRECORD,'/row/c44[position()=1]') "AUDITOR_CODE"
,extractValue(a.XMLRECORD,'/row/c45[position()=1]') "AUDIT_DATE_TIME"
FROM
"F_SEAT_SCRIPTS" a

Change query

SELECT RECID FROM TAFJV_F_SEAT_SCRIPTS WHERE "SCRIPT_STATUS" = 'ACTIVE' and (SCRIPT_GROUP_1 = 'TB01-START' OR SCRIPT_GROUP_2 = 'TB01-START' OR SCRIPT_GROUP_3 = 'TB01-START' OR SCRIPT_GROUP_4 = 'TB01-START' OR SCRIPT_GROUP_5 = 'TB01-START');

EXT-INDEX

Database index has been created manually on an application or field combination. The same is now achieved with DBTools in SQL mode. The following operations are allowed with EXT-INDEX:

  • CREATE-EXTINDEX
  • DELETE-EXTINDEX
  • LIST-EXTINDEX
These operations apply only to XML tables for Oracle, SQL Server and DB2.

This command creates an index on an application or field combination by specifying the application and field name. The syntax is as shown below,

CREATE-EXTINDEX {-f|-x} {-n} {-s} <FileName> <FieldName>

Where,

  • -f, -x and -n are specific to Oracle
  • -f : Functional index
  • -x : XML index
  • -n : Add numcast, used with functional index
  • -s : Script mode

With Oracle, you can create functional index or XML index, whereas these options are not available for SQL Server and DB2. For Oracle and numeric fields, -n has to be used to add numcast to the index query generated in runtime.

When –s is included in the query, index is not created but the SQL queries to create index are written in DBTools log. Make sure DBTools log is set to the minimum of INFO level, when script mode is used.

Both single and multi-value fields can be indexed, however multi-value field indexing is not supported for DB2.

This command deletes the index created as part of TAFJ. The syntax for the same is shown below.

DELETE-EXTINDEX {-x/-f} <filename> <fieldname>

Where,

  • -f and -x are specific to Oracle
  • -f : Functional index
  • -x : XML index

This command lists the existing index on a table. Unlike the previous two options, this command takes only the file name and lists all the indexes created on the table, and does not take any optional arguments. The syntax for this command is shown below.

LIST-EXTINDEX <filename>

This command creates promoted column on a table. The command syntax is shown below.

CREATE-PCOLUMN {-s} {-i} <filename> <fieldname>

Where,

  • -s : Used for script creation and can be executed later for table alter.
  • -i : Used to create index on the created promoted column.

When using script mode, the SQL queries to create promoted column is written in the DBTools log under INFO level, which can be used later to generate the promoted column.

When –i is used, in addition to the creation of promoted column, index is also created.

Copyright © 2020- Temenos Headquarters SA

Contact Us

knowledge@temenos.com

Published on :
Monday, May 27, 2024 5:14:18 PM IST