TAFJ Database Performance

Understanding our database is essential for effectively managing data, as well as for analyzing and performing the necessary maintenance as data volume increases. This is key to ensuring efficient communication with the application layer and having the peace of mind that our applications perform optimally when retrieving or storing information in the database. In this post, we will discuss some of the recommended maintenance tasks that can be performed on a T24 database.

We would like to share two important operations for improving database performance: promoted columns, and dropping and recreating views.

TAFJ Promoted Columns

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.

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

Oracle

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)

SQL Server

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)

DB2

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

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.

The examples for recreates the view are below:

Oracle

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

DB2

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"
FROM
"FBNK_CURRENCY" a

Script GroupThe SCRIPT.GROUP as a multi-valued column, It allows scripts to be classified by purpose, the following example is to retrieve that type of query.

JQL

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

SQL

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

There are countless scripts and queries we can use to improve our database performance.

If you need further assistance in researching and identifying the necessary methods to implement performance improvement processes tailored to your database's future needs, feel free to contact Performetriks. Keep up the great work! Happy Performance engineering!

#T24 #Databaseperformance #DB2