TAFJ-DB P e rf o r m a n c e

R20
 TAFJ-DB P e rf o r m a n c e

Amendment History:

Revisio
Date Amended Name Description
n

Initial 29th sept.-16 JN.Charpin Promoted column - Fetch size setup

2 22nd March 2019 Riswana F R19 AMR review

3 25th March 2020 Riswana F R20 AMR review

Page 2
TAFJ-DB P e rf o r m a n c e

Copyri g h t
Copyright © Temenos Headquarters SA 2009-2020. 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.

Errat 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. [email protected]

Page 3
TAFJ-DB P e rf o r m a n c e

Table of Contents
Copyright................................................................................................................................................ 3
Errata and Comments............................................................................................................................ 3
TAFJ Promoted Columns....................................................................................................................... 5
Introduction............................................................................................................................ 5
Promoting Columns for SQLServer example........................................................................ 5
Promoting Columns for DB2 example................................................................................... 5
Promoting Columns for Oracle example............................................................................... 6
Drop and Recreate the Views (All Databases)...................................................................... 6
USE CASE.......................................................................................................................... 10
Jql.................................................................................................................................... 10
Sql translation.................................................................................................................. 10
Alter Table for each multi-value....................................................................................... 10
Create Indexes for each new column.............................................................................. 10
Recreate the view with new columns............................................................................... 11
Change query.................................................................................................................. 13
JDBC Fetch size setup......................................................................................................................... 14
Goal..................................................................................................................................... 14
Oracle.................................................................................................................................. 14
Weblogic.......................................................................................................................... 14
JBoss............................................................................................................................... 15
Webpshere...................................................................................................................... 16
MSSQL................................................................................................................................ 17
DB2..................................................................................................................................... 18
Tuning DB2 Connect by Increasing the I/O Block Size................................................... 18

Page 4
TAFJ-DB P e rf o r m a n c e

TAFJ Pro m o t e d Colu m n s

Intr o d u c t i o n
The goal of this section is to show how to promote columns for TAFJ for performance
improvements. Promoted columns are relational columns where data is generated via a
function when it is inserted/updated. Therefore, these columns will perform better when
indexed over an xml index.

Pro m o t i n g Colu m n s for SQLS e r v e r exa m p l e

Below is an example of how to promote the RANK column (xml attribute 1) on the
FBNK_CURRENCY table

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)

Pro m o t i n g Colu m n s for DB 2 exa m p l e

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))@

Page 5
TAFJ-DB P e rf o r m a n c e

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)@

Pro m o t i n g Colu m n s for Orac l e exa m p l e

alter table fbnk_currency

add (

RANK number(10) as (CAST(extractValue(xmlrecord,'/row/c1') as

NUMBER))

);

create index IX_FBNK_CURRENCY_RANK on FBNK_CURRENCY(RANK)

Drop and Re cr e a t e th e View s (All Dat a b a s e s )

Drop and recreate view so that the new column will go directly to the rdbms column and not
the XML column when the view is queried. (View can be retrieved from the particular
database or generated by using DBImport with DBImport logging set to DEBUG in
$TAFJ_HOME/conf/TAFJTrace.properties. Here you just want to regenerate the VIEW
ONLY for the PARTICULAR TABLE ONLY)

Oracle example of getting the view definition from the database:

SQL> set long 100000

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

Page 6
TAFJ-DB P e rf o r m a n c e

This is a DB2 example only for recreating the view to use the new column RANK.

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"

Page 7
TAFJ-DB P e rf o r m a n c e

,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"

Page 8
TAFJ-DB P e rf o r m a n c e

,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"

Page 9
TAFJ-DB P e rf o r m a n c e

,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

Page 10
TAFJ-DB P e rf o r m a n c e

USE CASE
Here is an advanced example with column SCRIPT.GROUP a multi-value column

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

Sql tran s l a t i o n
SELECT RECID FROM "TAFJV_F_SEAT_SCRIPTS" WHERE "SCRIPT_STATUS" = 'ACTIVE' and
( XMLEXISTS('$t/c 1 0[ t ex t() = " TB0 1- START"]' PASSING "SCRIPT_GROUP_10" as "t") )

The above cre a t e s a full table scan. Her e is one way to index it.

Alter Tabl e for ea c h mu l ti - valu e

ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_1 VARCHAR2(40 0 0) as

( subst r( ex t r a c tVal u e(x mlr e c o r d , '/ ro w/ c 1 0 [ 1]'),1,1 0 0) ));

ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_2 VARCHAR2(40 0 0) as

( subst r( ex t r a c tVal u e(x mlr e c o r d , '/ ro w/ c 1 0 [ 2]'),1,1 0 0) ));

ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_3 VARCHAR2(40 0 0) as

( subst r( ex t r a c tVal u e(x mlr e c o r d , '/ ro w/ c 1 0 [ 3]'),1,1 0 0) ));

ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_4 VARCHAR2(40 0 0) as

( subst r( ex t r a c tVal u e(x mlr e c o r d , '/ ro w/ c 1 0 [ 4]'),1,1 0 0) ));

ALTER TABLE F_SEAT_SCRIPTS ADD( SCRIPT_GROUP_5 VARCHAR2(40 0 0) as

( subst r( ex t r a c tVal u e(x mlr e c o r d , '/ ro w/ c 1 0 [ 5]'),1,1 0 0) ));

Creat e Ind ex e s for eac h ne w col u m n

CREATE INDEX ix_f_seat_scri pt s_sc rip t_g r p_1 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_1);

CREATE INDEX ix_f_seat_scri pt s_sc rip t_g r p_2 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_2);

CREATE INDEX ix_f_seat_scri pt s_sc rip t_g r p_3 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_3);

CREATE INDEX ix_f_seat_scri pt s_sc rip t_g r p_4 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_4);

CREATE INDEX ix_f_seat_scri pt s_sc rip t_g r p_5 ON F_SEAT_SCRIPTS(SCRIPT_GROUP_5);

CREATE INDEX ix_f_seat_scri pt s_sc rip t_g r p_n ON F_SEAT_SCRIPTS (SCRIPT_GROUP_N);

Page 11
TAFJ-DB P e rf o r m a n c e

Recr e a t e th e view with new col u m n s

CREATE OR REPLACE VIEW TAFJV_F_SEAT_SCRIPTS as SELECT a.RECID, a.XMLRECORD
"THE_RECORD"

,a.RECID "SCRIPT_ID"

,extr a c tValu e( a.XMLRECORD,'/row/c 1[ po sitio n() = 1 ] ') "DESCRIPT"

,extr a c t( a.XMLRECORD,'/row/c1 ') "DESCRIPT_1"

,extr a c tValu e( a.XMLRECORD,'/row/c 2[ po sitio n() = 1 ] ') "COMPANY_CODE"

,extr a c tValu e( a.XMLRECORD,'/row/c 3[ po sitio n() = 1 ] ') "SCRIPT_STATUS"

,extr a c tValu e( a.XMLRECORD,'/row/c 4[ po sitio n() = 1 ] ') "SCRIPT_SOURCE"

,extr a c tValu e( a.XMLRECORD,'/row/c 5[ po sitio n() = 1 ] ') "ALTERNATE_REF"

,extr a c tValu e( a.XMLRECORD,'/row/c 6[ po sitio n() = 1 ] ') "SELECT_ROUTINE"

,extr a c tValu e( a.XMLRECORD,'/row/c 7[ po sitio n() = 1 ] ') "BASE_RELEASE"

,extr a c tValu e( a.XMLRECORD,'/row/c 8[ po sitio n() = 1 ] ') "PRODUCT_GROUP"

,extr a c tValu e( a.XMLRECORD,'/row/c 9[ po sitio n() = 1 ] ') "PRODUCT_CODE"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 0[ p o sitio n() = 1 ] ') "SCRIPT_GROUP"

,SCRIPT_GROUP_N "SCRIPT_GROUP_10"

,SCRIPT_GROUP_1

,SCRIPT_GROUP_2

,SCRIPT_GROUP_3

,SCRIPT_GROUP_4

,SCRIPT_GROUP_5

,extr a c tValu e( a.XMLRECORD,'/row/c 1 1[ p o sitio n() = 1 ] ') "APPLICATION"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 2[ p o sitio n() = 1 ] ') "STATIC_SETUP"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 3[ p o sitio n() = 1 ] ') "VERSION"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 4[ p o sitio n() = 1 ] ') "FUNCTION"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 5[ p o sitio n() = 1 ] ') "TXN_ID"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 6[ p o sitio n() = 1 ] ') "FIELD_NAME"

,extr a c t( a.XMLRECORD,'/row/c1 6 ') "FIELD_NAME_16"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 7[ p o sitio n() = 1 ] ') "FIELD_VALUE"

,extr a c t( a.XMLRECORD,'/row/c1 7 ') "FIELD_VALUE_17"

Page 12
TAFJ-DB P e rf o r m a n c e

,extr a c tValu e( a.XMLRECORD,'/row/c 1 8[ p o sitio n() = 1 ] ') "FIELD_INPUT"

,extr a c t( a.XMLRECORD,'/row/c1 8 ') "FIELD_INPUT_18"

,extr a c tValu e( a.XMLRECORD,'/row/c 1 9[ p o sitio n() = 1 ] ') "SEAT_ID"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 0[ p o sitio n() = 1 ] ') "UPDATE_APPL"

,extr a c t( a.XMLRECORD,'/row/c2 0 ') "UPDATE_APPL_20"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 1[ p o sitio n() = 1 ] ') "UPDATE_SCRPT_ID"

,extr a c t( a.XMLRECORD,'/row/c2 1 ') "UPDATE_SCRPT_ID_21"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 2[ p o sitio n() = 1 ] ') "UPD_APPL_FLD_IDEN"

,extr a c t( a.XMLRECORD,'/row/c2 2 ') "UPD_APPL_FLD_IDEN_22"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 3[ p o sitio n() = 1 ] ') "GENERATE_ERROR"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 4[ p o sitio n() = 1 ] ') "DEFINE_ERROR"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 5[ p o sitio n() = 1 ] ') "CREATED_BY"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 6[ p o sitio n() = 1 ] ') "APPROVED_BY"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 7[ p o sitio n() = 1 ] ') "USER"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 8[ p o sitio n() = 1 ] ') "MSG_FROM_FILE"

,extr a c tValu e( a.XMLRECORD,'/row/c 2 9[ p o sitio n() = 1 ] ') "TPR_FILE_NAME"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 0[ p o sitio n() = 1 ] ') "CREATED_DATE"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 1[ p o sitio n() = 1 ] ') "LAST_MODIFIED_DATE"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 2[ p o sitio n() = 1 ] ') "IO_ANALYSIS"

,extr a c t( a.XMLRECORD,'/row/c3 2 ') "IO_ANALYSIS_32"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 3[ p o sitio n() = 1 ] ') "RESERVED_2"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 4[ p o sitio n() = 1 ] ') "RESERVED_1"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 6[ p o sitio n() = 1 ] ') "OVERRIDE"

,extr a c t( a.XMLRECORD,'/row/c3 6 ') "OVERRIDE_36"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 7[ p o sitio n() = 1 ] ') "RECORD_STATUS"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 8[ p o sitio n() = 1 ] ') "CURR_NO"

,extr a c tValu e( a.XMLRECORD,'/row/c 3 9[ p o sitio n() = 1 ] ') "INPUTTER"

,extr a c t( a.XMLRECORD,'/row/c3 9 ') "INPUTTER_39"

,extr a c tValu e( a.XMLRECORD,'/row/c 4 0[ p o sitio n() = 1 ] ') "DATE_TIME"

,extr a c t( a.XMLRECORD,'/row/c4 0 ') "DATE_TIME_40"

Page 13
TAFJ-DB P e rf o r m a n c e

,extr a c tValu e( a.XMLRECORD,'/row/c 4 1[ p o sitio n() = 1 ] ') "AUTHORISER"

,extr a c tValu e( a.XMLRECORD,'/row/c 4 2[ p o sitio n() = 1 ] ') "CO_CODE"

,extr a c tValu e( a.XMLRECORD,'/row/c 4 3[ p o sitio n() = 1 ] ') "DEPT_CODE"

,extr a c tValu e( a.XMLRECORD,'/row/c 4 4[ p o sitio n() = 1 ] ') "AUDITOR_CODE"

,extr a c tValu e( a.XMLRECORD,'/row/c 4 5[ p o sitio n() = 1 ] ') "AUDIT_DATE_TIME"

FROM

"F_SEAT_SCRIPTS" a

Cha n g e qu ery

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');

Page 14
TAFJ-DB P e rf o r m a n c e

JDBC Fet c h siz e set u p

Goal
JDBC defines a parameter allowing to configure the fetch size, representing the number of
rows being transferred from the database during one access.

This parameter could make a significant difference in terms of performance and round trip
between application and database.

For example if the number of rows to be transferred is 1000 and the fetch size is 10, 100
network round trip are required where it would be only 10 with a fetch size of 100, which
would be most likely 10 times faster.

Although not supported by all JDBC drivers, this parameter could be defined
programmatically on the statement or result set object.

Our goal is to define it by configuration at application server level, more specifically at data
source connection level. However memory usage has to be considered as bigger the fetch
size at connection level is, bigger the memory usage will be at JDBC layer client level.

It will apply to every requests.

Oracl e
The default oracle driver fetch size is 10.

It could be changed by using the parameter defaultRowPrefetch as a connection property.

Web l o g i c
It has to be done at connection level in the custom property section.

Page 15
TAFJ-DB P e rf o r m a n c e

Scripting
if (dbDriver.startswith('oracle')):

cd('/JDBCSystemResources/T24/JDBCResource/T24/JDBCDriverParams/T24/Properties/T24')

cmo.createProperty('defaultRowPrefetch')

cd('/JDBCSystemResources/T24/JDBCResource/T24/JDBCDriverParams/T24/Properties/T24
/Properties/defaultRowPrefetch')

cmo.unSet('SysPropValue')

cmo.unSet('EncryptedValue')

cmo.setValue('100')

JBos s

Set a defaultRowPrefetch connection property in at data source level in the standalone-full

configuration of the server.

i.e.

<datasource jta="true" jndi-name="java:/jdbc/t24DS" pool-name="t24DS" enabled="true"

use-java-context="true" use-ccm="true" statistics-enabled="true">

<connection-url>jdbc:oracle:thin:@localhost:1521:R15</connection-url>

Page 16
TAFJ-DB P e rf o r m a n c e

<driver>ora12c</driver>

<connection-property name="defaultRowPrefetch">100</connection-property>

Scripting
/subsystem=datasources/data-source=t24DS/connection-
properties=defaultRowPrefetch:add(value="100")

Web p s h e r e
Set a connectionProperties named defaultRowPrefetch at data source custom property
level.

Data sources > TAFJ XA ORACLE Data source > Custom properties

Scripting
AdminConfig.create('J2EEResourceProperty',Datasource_props,[["name", "connectionProperties"],
["type","java.lang.String"],["value","defaultRowPrefetch=100"],["required","false"]])

Page 17
TAFJ-DB P e rf o r m a n c e

MS SQL
MSSQL driver is not supporting the fetch size setup and is returning all rows.

To avoid returning all rows, the default selectMethod should be changed from direct to
cursor or create a statement as specified below.

https://technet.microsoft.com/en-us/library/aa342344(SQL.90).aspx

Limit the Size of Your Result Sets

Consider using the setMaxRows method (or SET ROWCOUNT or SELECT TOP N SQL syntax) to
limit the number of rows returned from potentially large result sets. If you must deal with large result
sets, consider using a server cursor by setting the connection string property selectMethod=cursor.
Alternatively, you can create the statement with
com.microsoft.sqlserver.jdbc.SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONL
Y (2004) scrollability for forward-only, read-only access, and then use the setFetchSize method to tune
performance.

Page 18
TAFJ-DB P e rf o r m a n c e

DB 2

DB2 is using a default fetch size of 32KB and not a number of row.

The parameter to refine is called block size.

https://docs.oracle.com/cd/B40099_02/books/ImpAppsDB2390/ImpAppsDB2390_ImpPrep15
.html

Tuning DB2 Connect by Increasing the I/O Block Size

The RQRIOBLK database manager configuration parameter specifies the maximum size of the client I/O blocks used to
store the results of queries sent by a database client to a remote database.
To ensure that database queries which return large blocks of data do not cause DB2 Connect users to experience long
response times, you can change the value of the client RQRIOBLK database manager configuration parameter.
The default value for the RQRIOBLK parameter on DB2 Connect is 32 KB and the maximum size is 65 KB. DB2 for
z/OS V8 can support up to 10MB cursor blocks. If you are using DB2 for z/OS V8, you can increase the value of the
RQRIOBLK parameter to 65 KB and so improve performance.

To change the DB2 Connect I/O block size (RQRIOBLK) value

 Using the Command Line Processor, change the RQRIOBLK size by typing the following:

DB2 UPDATE DBM CFG USING RQRIOBLK 65535

Note

http://www.ibm.com/support/knowledgecenter/ssw_i5_54/rzaha/conprop.htm

block 0, 8, 16, This is the number of rows that are fetched at a time for a result set. For typical
size 32, 64, forward-only processing of a result set, a block of this size is obtained. Then the
128, 256, database is not accessed because each row is processed by your application. The
512 database requests another block of data only when the end of the block is reached.

This value is only used if the blocking enabled property is set to true.

Setting the block size property to 0 has the same effect as setting the blocking
enabled property to false.

The default is to use blocking with a block size of 32. This is a fairly arbitrary
decision and the default could change in the future.

Blocking is not used on scrollable result sets.

Page 19