Applicative DB2 9 features @ KBC

Applicative DB2 9 features @ KBC GSE 02/11/2010 Dirk Beauson KBC Global Services

Agenda • Utilities • SQL • Design • Table • Tablespace • Index • Features • Performance/Accesspath

DB2 9 Utilities @ KBC • LOAD • CopyDictionary

CopyDictionary • In DB2 9 • Allows the LOAD utility to copy an existing compression dictionary from a specified partition to other partitions of a partitioned tablespace • Only compatible on classic partitioned and UTS partition-by-range tablespaces • Using : • LOAD COPYDICTIONARY 1 INTO TABLE PART 3 REPLACE INTO TABLE PART 5 REPLACE

Use • Providing a dictionary for partitions with bad or no data • Use @KBC : • Very useful during the activation of an applicative software upgrade • Instead of loading data on all partitions without data, and afterwards deleting the data • We simply can load just one single partition and copy the dictionary from that partition to all other partitions • This in combination with the migration of statistics from another environment

DB2 9 SQL @ KBC • Merge • Truncate • Intersect & Except • New build-in functions

DB2 V8 • SELECT to check if a row exists • If yes then UPDATE • If no then INSERT • If the row exists most of the times • try UPDATE, if it fails then INSERT • If the row doesn’t exist most of the times • Try INSERT, if it fails then UPDATE • Always at least 2 SQL statements needed

DB2 9 • Merge statement • Modifies a target table • With the specified input data • Based on matching specifications • Rows that match • the input data are updated • Rows that don’t match • the input data are inserted • All in 1 SQL statement

USE • MERGE INTO CERATEST.CBTBEV53 AS TUSING (VALUES (:h, :h, :h, :h, :h)) AS S (USER_KD, VNS_NR, DT_NTR_KD, BD_NTR_KD, PTR_NM)ON (T.USER_KD = S.USER_KD)WHEN MATCHED THEN UPDATE SET T.VNS_NR = S.VNS_NRWHEN NOT MATCHED THEN INSERT (USER_KD, VNS_NR, DT_NTR_KD, BD_NTR_KD, PTR_NM) VALUES (S.USER_KD, S.VNS_NR, S.DT_NTR_KD, S.BD_NTR_KD, S.PTR_NM) ; • Use @KBC : • Can be used • In build tools that support this syntax • Significant less SQL-statements • But more complex for developperto write

Truncate Table • Before DB2 9 • Mass delete • DELETE FROM table WITHOUT WHERE-clause • Load Replace • REORG SHRLEVEL CHANGE DISCARD • In case of DELETE TRIGGER on the table : • DROP TRIGGER • Delete rows • CREATE TRIGGER  Otherwise fire of the trigger

Truncate Table • In DB2 9 • The TRUNCATE statement address issues of trigger • Deletes all rows for base tables or declared global temporary tables without activating Delete trigger • No need to drop and recreate delete triggers for faster processing • Empty a table permanently without going through the commit phase • Reuse storage

Use • Use @ KBC : • Alternative next to load replace to cleanup data of a table or partition • But, will not be used in the near future • We will not convert all existing cleanup processes • Alignment with ORACLE way of working in a platform independent environment ?

UNION • DB2 V8 : • UNION (DISTINCT / ALL) • If UNION ALL is specified, the result consists of all rows in R1 and R2. With UNION DISTINCT, the result is the set of all rows in either R1 or R2 with the redundant duplicate rows eliminated. In either case, each row of the result table of the union is either a row from R1 or a row from R2.

EXCEPT • DB2 9 : • EXCEPT (DISTINCT / ALL) • If EXCEPT ALL is specified, the result consists of all rows from only R1, including significant redundant duplicate rows. • With EXCEPT DISTINCT, the result consists of all rows that are only in R1, with redundant duplicate rows eliminated. • In either case, each row in the result table of the difference is a row from R1 that does not have a matching row in R2.

INTERSECT • DB2 9 : • INTERSECT (DISTINCT / ALL) • If INTERSECT ALL is specified, the result consists of all rows that are both in R1 and R2, including significant redundant duplicate rows. • With INTERSECT DISTINCT, the result consists of all rows that are in both R1 and R2, with redundant duplicate rows eliminated. • In either case each row of the result table of the intersection is a row that exists in both R1 and R2.

Examples CBTDBEU1 000001 000001 000001 000002 000002 000002 000003 000004 000004 000005 CBTDBEU2 000001 000001 000003 000003 000003 000003 000004

Example UNION ALL Result : KOLOM ------ 000001 000001 000001 000001 000001 000002 000002 000002 000003 000003 000003 000003 000003 000004 000004 000004 000005 QUERY : SELECT KOLOM FROM CBTDBEU1 UNION ALL SELECT KOLOM FROM CBTDBEU2

Example UNION DISTINCT Result : KOLOM ------ 000001 000002 000003 000004 000005 QUERY : SELECT KOLOM FROM CBTDBEU1 UNION (DISTINCT) SELECT KOLOM FROM CBTDBEU2

Example EXCEPT ALL Result : KOLOM ------ 000001 000002 000002 000002 000004 000005 QUERY : SELECT KOLOM FROM CBTDBEU1 EXCEPT ALL SELECT KOLOM FROM CBTDBEU2

Example EXCEPT DISTINCT Result : KOLOM ------ 000002 000005 QUERY : SELECT KOLOM FROM CBTDBEU1 EXCEPT (DISTINCT) SELECT KOLOM FROM CBTDBEU2

Example INTERSECT ALL Result : KOLOM ------ 000001 000001 000003 000004 QUERY : SELECT KOLOM FROM CBTDBEU1 INTERSECT ALL SELECT KOLOM FROM CBTDBEU2

Example INTERSECT DISTINCT Result : KOLOM ------ 000001 000003 000004 QUERY : SELECT KOLOM FROM CBTDBEU1 INTERSECT (DISTINCT) SELECT KOLOM FROM CBTDBEU2

Conclusion • Use @ KBC : • Can be used • In build tools that support this syntax • Better performance • Easier to code

SOUNDEX • Interesting for searching in text and sound is known but the precise spelling is not SELECT SOUNDEX(PSN_NM), PSN_NM FROM CBVPSN01WHERE SOUNDEX(PSN_NM) = SOUNDEX('LETTELIER') • Result: L346 Letellier Leon L346 Letelier Noël L346 L’Atelier Vandertesten BVBA … • Result is CHAR(4) • Use @ KBC : • Further investigation • Work with phonemes

DB2 9 Table Design @ KBC • Clone Tables • Skip Locked Data • Row Change Timestamp • APPEND

What is Clone Table ? • Copy of a current table,in the same tablespace, that has the same attributes, structure, … • Available for UTS with 1 single table only • Not involved in RI!!!

What can you do with it ? • Every SQL and utility operation can run separateon the clone • Exchange between base and clone • Switches underlying datasets associated with base and clone table • Only the data instances (in DB2-catalog) will change • No data will be copied • Transparent to the application • Does not require any application changes

What can’t you do with it ? • Cannot… • Have RI relations • Create indexeson clone • Indexes created on base table apply to both the base and clone • Renamea base table that has a clone relationship • Alter table or column attributes of a base table or clone tablewhen the objects are involved with cloning • RunRUNSTATSon clone tabIe (RTS stats for base are invalidated during exchange) • Drop an AUX table or index on an object involved in cloning • Createbefore triggerson clone • Can be created on the base table but not on the clone table • Before triggers created on base table apply to both base and clone tables • Alter max number of partitionswhen a clone table resides in a partition-by-growth tablespace • (Clone anRTS table) • (Clonecatalog and directorytables)

What can’t you do with it (2)? • Cannot… • Havemore than ONEclone per table • Table must be createdin a UTS • Base table cannot be a MQT nor a created or declared global temporary table • NO AFTER trigger • No pending changes to the table structure • No active versions • Table definition must be complete • Pagesets must be either “I” or “J”(not a mix ?!) • All physical datasets must be available • Creator.Clone_name must be unique in the subsystem • Create Index on base … also created on clone and RBDP- set

Conclusion • Use @ KBC : • Will not be used in the near future • Still to many restrictions for our “normal operational” environment • We now have our own framework to work with : • Flip/flop tables or partitions

What is SKIP LOCKED DATA ? • New clause on SELECT, UPDATE, DELETE, …. • Avoids waiting for rows that are locked by other applications • No warning or SQL code ! • “Everything is perfect !” • only reported in IFCID 018, not application • Not possible in UR or RR • Only with CS or RS • Which applications can deal with “part-of-the-data”??? • Use @ KBC : • No use, accurate data needed • On some platforms we work with UR

What is it ? • A new and automatically filled “real” or “dummy” column • That represents the timestamp of the insert or the last update in V9 • Syntax : • ROW CHANGE TIMESTAMP FOR TABLE/VIEW • Result can be null • Can be used in predicates like any other column • Main purpose : stimulate optimistic locking

Decisions and advices • For new tables created in V9 • Interesting to use and to exploit in SQL • Use @ KBC : • Will be used • Instead of MUT-DS provided by the application, but application has to be aware of implications • New technique supporting optimistic locking

What is it ? • For tables • where mass-insert is done • and clustering of the rows is not important • new keyword “APPEND YES” on the CREATE/ALTER TABLE statement • Behaves like a “LOAD RESUME” utility • Together with relief for sequential key insert (50/50 split becomes 90/10 split) a nice new feature

Partitions ??? • APPEND • Ignore clustering sequenceduring SQL insert and online LOAD processing • Rows are appendedat the end of the table… or appropriate partition • For range-partitioned tablespaces, the appropriate partition is the one dictated by thevalue in the row’s partitioning column • For partition-by-growth tablespaces, the appropriate partition isany partition with space at the end

Conclusion • Tables with logging in REG_TS order • And random retrieve : column =key • Suggestions in presentations :Can be altered “yes” just before long batchjob, followed by reorg, then switched to “no” • Indexes still must be maintained of course • Use @ KBC : • Good idea to use from now on ! • Every time we design tables where we want to insert at the end of a table or partition • Log-tables • High volume insert tables • But waiting on Fix (PK81471 ?)

DB2 9 Tablespace Design @ KBC • Not Logged Tablespace • Universal Tablespace

What is “Not Logged” ? • Not logging updates • NOT a performance boost • Has implications : • recoverability • rollback/backout : • If a transaction has updated a NOT LOGGED object and is deadlocked with one which hasn’t : • The SECOND transaction will be aborted • • Use @ KBC : • No use in our operational environment, because we want everything to be logged (audit, problem solving) • Will be used in the informational environment

DB2 9 Tablespace Design @ KBC • Not Logged Tablespace • Universal Tablespace

Best of both worlds Combination of segmented and partitioned TS

UTS - General • Only 1 table per TS • Up to 128 TB possible (32K page size) • MEMBER CLUSTER no longer allowed • Two types of partitioning: • Partitioning by Range (key) : “R” • Partitioning by Growth : “G”

Applicative DB2 9 features @ KBC