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