Global temporary tables

They are temporary tables created on the fly and persist only through the life of the SP. The default schema name is SESSION. We can change it to reflect our SP name if needed. I am adding a code snippet from one netting SP written by Elise Wade for Arizona diamond.
* Please take a note on the indexes we could create on the fly for these tables.
The name of the SP is ccdb.netting_azd_inst_prof and is in production environment, PCFPROD.
DECLARE GLOBAL TEMPORARY TABLE SESSION.BATCHES(
batch_id decimal(16, 0) not null
) in USERTEMP8K
with replace
partitioning key (batch_id)
not logged
on commit preserve rows;


DECLARE GLOBAL TEMPORARY TABLE SESSION.CANDIDATES(
icn_num character(20) not null,
root_icn character(17),
root_icn_id decimal(17,0),
claim_type_cd character(2),
precedence smallint
) in USERTEMP8K
with replace
partitioning key (icn_num)
not logged
on commit preserve rows;

DECLARE GLOBAL TEMPORARY TABLE SESSION.DETAILS(
icn_num character(20) not null,
claim_id decimal(16,0) not null,
seq_num smallint not null,
claim_type_cd character(2) not null,
line_item_cd character(5),
line_item_sub_cd character(5),
charge_amt decimal(9,2),
paid_amt decimal(9,2),
error_cd character(8),
root_icn character(20),
precedence smallint
) in USERTEMP8K
with replace
partitioning key (claim_id, seq_num)
not logged
on commit preserve rows;

CREATE INDEX SESSION.DETAILS_ICN_IDX on SESSION.DETAILS (icn_num, claim_id, seq_num);
CREATE INDEX SESSION.DETAILS_WICN_IDX on SESSION.DETAILS(root_icn, line_item_cd);

DECLARE GLOBAL TEMPORARY TABLE SESSION.RESULTS(
icn_num character(20) not null,
claim_id decimal(16,0) not null,
seq_num smallint not null,
claim_type_cd character(2) not null,
error_cd character(8)
) in USERTEMP8K
with replace
partitioning key (claim_id, seq_num)
not logged
on commit preserve rows;

CREATE INDEX SESSION.RESULTS_CLM_IDX on SESSION.RESULTS(claim_id, seq_num);

* Another advantage that we can have is the usage of identity columns which can auto generate the sequence numbers to be used as a row_id, and we can work on a set of rows by selecting based on a range of these row_ids.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP1
(
row_id integer NOT NULL GENERATED ALWAYS
AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20, MINVALUE 1, MAXVALUE 99999999999999, NO CYCLE, NO ORDER),
xx…..
)

Although this can be done in cursor definition, it is just an easy way of letting DB2 do the brunt of work, and not worry about calculating the row_ids ourselves.

Return to Index

Exists Predicate

>>-EXISTS-(fullselect)-----------------------------------------><

The EXISTS predicate tests for the existence of certain rows. The fullselect can specify any number of columns, and:
• The result is true only if the number of rows that is specified by the fullselect is not zero.
• The result is false only if the number of rows specified by the fullselect is zero.
• The result cannot be unknown.
The SELECT clause in the fullselect can specify any number of columns because the values returned by the fullselect are ignored.
For convenience, use:
SELECT *

Unlike the NULL, LIKE, and IN predicates, the EXISTS predicate has no form that contains the word NOT. To negate an EXISTS predicate, precede it with the logical operator NOT, as follows:
NOT EXISTS (fullselect)
The result is then false if the EXISTS predicate is true, and true if the predicate is false. Here, NOT is a logical operator and not a part of the predicate. Logical operators are discussed in Search conditions.

Example 1: The following query lists the employee number of everyone represented in DSN8810.EMP who works in a department where at least one employee has a salary less than 20000. Like many EXISTS predicates, the one in this query involves a correlated variable.
SELECT EMPNO FROM DSN8810.EMP X WHERE EXISTS (SELECT * FROM DSN8810.EMP
WHERE X.WORKDEPT=WORKDEPT AND SALARY<20000);
Example 2: List the subscribers (SNO) in the state of California who made at least one call during the first quarter of 2000. Order the results according to SNO. Each MONTHnn table has columns for SNO, CHARGES, and DATE. The CUST table has columns for SNO and STATE.

SELECT C.SNO FROM CUST C WHERE C.STATE = 'CA' AND EXISTS (SELECT * FROM MONTH1
WHERE DATE BETWEEN '01/01/2000 AND '01/31/2000' AND C.SNO = SNO UNION ALL
SELECT * FROM MONTH2 WHERE DATE BETWEEN '02/01/2000 AND '02/29/2000'
AND C.SNO = SNO UNION ALL SELECT * FROM MONTH3 WHERE DATE BETWEEN '03/01/2000 AND '03/31/2000' AND C.SNO = SNO) ORDER BY C.SNO;

Notes :
A caveat is that, if we use this exists on a large set of data, the same case of huge amount of memory being dedicated to the locklist (an update lock is placed on row till it is committed) occurs and can slow down the system.

A code snippet from a netting SP in production.

update ccdb.inst_claim_dtl set ccdb.inst_claim_dtl.last_update_nm = 'NETTING',
ccdb.inst_claim_dtl.error_cd = (select SESSION.DETAILS.error_cd
from SESSION.DETAILS where ccdb.inst_claim_dtl.client_cd = '534' and SESSION.DETAILS.claim_type_cd = '01' and ccdb.inst_claim_dtl.claim_id = SESSION.DETAILS.claim_id and ccdb.inst_claim_dtl.seq_num = SESSION.DETAILS.seq_num) where exists (select 1 from SESSION.DETAILS where ccdb.inst_claim_dtl.client_cd = '534' and SESSION.DETAILS.claim_type_cd = '01' and ccdb.inst_claim_dtl.claim_id = SESSION.DETAILS.claim_id and ccdb.inst_claim_dtl.seq_num = SESSION.DETAILS.seq_num);

Back to Index

Applications review for BCU performance

The following post was targeted to be an index holder for the document involving Review of Application Performance for BCU (TP 12886). I will be adding extra chapters as we find new performance improvement methods.

Log of performance improvement methodologies

We had good amount of discussions with IBM and within our group and slowly added new techniques to a few netting procedures. The idea was to remove the cursor build code that was known to eat up lot of memory in both holding the data and for sorting them (when order by clause was used).

1. We used the ‘exists’ predicate in matching one on one for a set of data, I have copied the IBM information center’s comment on the syntax and how it works.

2. Global temp tables were used (very similar to cursor, but we got great performance improvements by coupling this with other advantages the ‘table’ in DB2 gives, compared to cursors.)

3. Table partitioning (range or hash)