Transcript Titel van de presentatie - GSE Young Professionals

Applicative DB2 9 features @ KBC
GSE 02/11/2010
Dirk Beauson
KBC Global Services
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

2

20/07/2015 
DB2 9 Utilities @ KBC
 LOAD


3
CopyDictionary

20/07/2015 
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

4

20/07/2015 
Use
 Providing a dictionary for partitions with bad or no
data
 Use @KBC :


5

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
20/07/2015 
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

6

20/07/2015 
DB2 9 SQL @ KBC
 Merge
 Truncate
 Intersect & Except
 New build-in functions

7

20/07/2015 
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

8

20/07/2015 
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

9

20/07/2015 
USE
 MERGE INTO CERATEST.CBTBEV53 AS T
USING (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_NR
WHEN 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 :





10 

Can be used
In build tools that support this syntax
Significant less SQL-statements
But more complex for developper to write
20/07/2015 
DB2 9 SQL @ KBC
 Merge
 Truncate
 Intersect & Except
 New build-in functions

11 

20/07/2015 
Truncate Table
 Before DB2 9


12 

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
20/07/2015 
Truncate Table
 In DB2 9





13 

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
20/07/2015 
Use
 Use @ KBC :





14 

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 ?
20/07/2015 
DB2 9 SQL @ KBC
 Merge
 Truncate
 Intersect & Except
 New build-in functions

15 

20/07/2015 
UNION
 DB2 V8 :
 UNION (DISTINCT / ALL)


16 

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.
20/07/2015 
EXCEPT
 DB2 9 :
 EXCEPT (DISTINCT / ALL)




17 

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.
20/07/2015 
INTERSECT
 DB2 9 :
 INTERSECT (DISTINCT / ALL)




18 

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.
20/07/2015 
Examples

19 

CBTDBEU1
CBTDBEU2
000001
000001
000001
000002
000002
000002
000003
000004
000004
000005
000001
000001
000003
000003
000003
000003
000004
20/07/2015 
Example UNION ALL
Result :
QUERY :
SELECT KOLOM
FROM CBTDBEU1
UNION ALL
SELECT KOLOM
FROM CBTDBEU2

20 

20/07/2015 
KOLOM
-----000001
000001
000001
000001
000001
000002
000002
000002
000003
000003
000003
000003
000003
000004
000004
000004
000005
Example UNION DISTINCT
Result :
QUERY :
SELECT KOLOM
FROM CBTDBEU1
UNION (DISTINCT)
SELECT KOLOM
FROM CBTDBEU2

21 

20/07/2015 
KOLOM
-----000001
000002
000003
000004
000005
Example EXCEPT ALL
Result :
QUERY :
SELECT KOLOM
FROM CBTDBEU1
EXCEPT ALL
SELECT KOLOM
FROM CBTDBEU2

22 

20/07/2015 
KOLOM
-----000001
000002
000002
000002
000004
000005
Example EXCEPT DISTINCT
Result :
QUERY :
SELECT KOLOM
FROM CBTDBEU1
EXCEPT (DISTINCT)
SELECT KOLOM
FROM CBTDBEU2

23 

20/07/2015 
KOLOM
-----000002
000005
Example INTERSECT ALL
Result :
QUERY :
SELECT KOLOM
FROM CBTDBEU1
INTERSECT ALL
SELECT KOLOM
FROM CBTDBEU2

24 

20/07/2015 
KOLOM
-----000001
000001
000003
000004
Example INTERSECT DISTINCT
Result :
QUERY :
SELECT KOLOM
FROM CBTDBEU1
INTERSECT (DISTINCT)
SELECT KOLOM
FROM CBTDBEU2

25 

20/07/2015 
KOLOM
-----000001
000003
000004
Conclusion
 Use @ KBC :





26 

Can be used
In build tools that support this syntax
Better performance
Easier to code
20/07/2015 
DB2 9 SQL @ KBC
 Merge
 Truncate
 Intersect & Except
 New build-in functions

27 

20/07/2015 
SOUNDEX
 Interesting for searching in text and sound is known but the
precise spelling is not
SELECT SOUNDEX(PSN_NM), PSN_NM
FROM CBVPSN01
WHERE SOUNDEX(PSN_NM) = SOUNDEX('LETTELIER')
 Result:
L346 Letellier Leon
L346 Letelier Noël
L346 L’Atelier Vandertesten BVBA
…
 Result is CHAR(4)
 Use @ KBC :


28 

Further investigation
• Work with phonemes
20/07/2015 
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

29 

20/07/2015 
DB2 9 Table Design @ KBC
 Clone Tables
 Skip Locked Data
 Row Change Timestamp
 APPEND

30 

20/07/2015 
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 !!!

31 

20/07/2015 
What can you do with it ?
 Every SQL and utility operation can run separate
on the clone
 Exchange between base and clone




32 
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

20/07/2015 
What can’t you do with it ?
 Cannot…
 Have RI relations
 Create indexes on clone
 Indexes created on base table apply to both the base and clone
 Rename a base table that has a clone relationship
 Alter table or column attributes of a base table or clone table
when the objects are involved with cloning
 Run RUNSTATS on clone tabIe (RTS stats for base are invalidated
during exchange)
 Drop an AUX table or index on an object involved in cloning
 Create before triggers on 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 partitions when a clone table resides in a
partition-by-growth tablespace
 (Clone an RTS table)
33
20/07/2015 catalog and directory tables)
(Clone




What can’t you do with it (2)?
 Cannot…












34 
Have more than ONE clone per table
Table must be created in 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

20/07/2015 
Conclusion
 Use @ KBC :




35 

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
20/07/2015 
DB2 9 Table Design @ KBC
 Clone Tables
 Skip Locked Data
 Row Change Timestamp
 APPEND

36 

20/07/2015 
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 :



37 
No use, accurate data needed
On some platforms we work with UR

20/07/2015 
DB2 9 Table Design @ KBC
 Clone Tables
 Skip Locked Data
 Row Change Timestamp
 APPEND

38 

20/07/2015 
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

39 

20/07/2015 
Decisions and advices
 For new tables created in V9
 Interesting to use and to exploit in SQL
 Use @ KBC :




40 

Will be used
Instead of MUT-DS provided by the application, but
application has to be aware of implications
New technique supporting optimistic locking
20/07/2015 
DB2 9 Table Design @ KBC
 Clone Tables
 Skip Locked Data
 Row Change Timestamp
 APPEND

41 

20/07/2015 
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

42 

20/07/2015 
Partitions ???
 APPEND





43 

Ignore clustering sequence during SQL insert and
online LOAD processing
Rows are appended at the end of the table
… or appropriate partition
For range-partitioned tablespaces, the appropriate
partition is the one dictated by the value in the row’s
partitioning column
For partition-by-growth tablespaces, the appropriate
partition is any partition with space at the end
20/07/2015 
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 :




44 

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 ?)
20/07/2015 
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

45 

20/07/2015 
DB2 9 Tablespace Design @ KBC
 Not Logged Tablespace
 Universal Tablespace

46 

20/07/2015 
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 :



47 
No use in our operational environment, because we want
everything to be logged (audit, problem solving)
Will be used in the informational environment

20/07/2015 
DB2 9 Tablespace Design @ KBC
 Not Logged Tablespace
 Universal Tablespace

48 

20/07/2015 
Best of both worlds
Combination of segmented and partitioned TS
SEGMENTED
PARTITIONED
Space-map has more information
about free space than partitioned
space map page.
Allows large tables
Mass delete performance is faster
than in other types of TS
Allows parallellism of accesses
Segments are ready for reuse
immediately after drop or mass
delete.

49 

20/07/2015 
UTS - General
 Only 1 table per TS
 Up to 128 TB possible (32K page size)
 MEMBER CLUSTER no longer allowed
 Two types of partitioning:



50 
Partitioning by Range (key) : “R”
Partitioning by Growth : “G”

20/07/2015 
Partition by range
 Same syntax as table based partitioned TS
 Segmented 

SEGSIZE clause in combination with NUMPARTS
 Automatically a LARGE TS (partition can > 64G)
 No index-controlled partitioning allowed : -662
 Better space management in conjunction with
varying-length rows
 Does not replace the regular partitioned TS
(ex. MEMBER CLUSTER for high volume inserts,
…)

51 

20/07/2015 
Partition by range
CREATE
TABLESPACE CBSXXX01
IN CBDXXX01
LOGGED
NUMPARTS 2
(
PART 1
USING STOGROUP CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 3
PCTFREE 20
GBPCACHE CHANGED
TRACKMOD YES
COMPRESS YES
,

52 

20/07/2015 
CREATE
TABLESPACE CBSXXX21
IN CBDXXX01
LOGGED
DSSIZE 4G
SEGSIZE 4
NUMPARTS 2
(
PART 1
USING STOGROUP CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 3
PCTFREE 20
GBPCACHE CHANGED
TRACKMOD YES
COMPRESS YES
,
Partition by growth
 Starts off as a one partition TS
 STOGROUP defined
 Partition is full (inserts/load):



another is automatically created (no -904)
free space, cache, define, log, trackmod: all partitions the
same
Compression dictionary copied from prior partition
 No partitioning key : only NPI’s
 No NUMPARTS
 MAXPARTITIONS : to specify limit of partitions

53 

20/07/2015 
Partition by growth
CREATE
TABLESPACE CBSXXX03
IN CBDXXX01
USING STOGROUP CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 3
PCTFREE 20
GBPCACHE NONE
COMPRESS YES
LOGGED
SEGSIZE 4
BUFFERPOOL BP1
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
CCSID EBCDIC
MAXROWS 255

54 

20/07/2015 
CREATE
TABLESPACE CBSXXX23
IN CBDXXX01
USING STOGROUP CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 3
PCTFREE 20
GBPCACHE NONE
COMPRESS YES
LOGGED
DSSIZE 4G
SEGSIZE 4
MAXPARTITIONS 2
BUFFERPOOL BP1
LOCKSIZE ANY
LOCKMAX SYSTEM
CLOSE YES
Partition by growth
 No parallellism during (un)load
 Allocation of new partitions is not rolled back
 Reorg may reclaim space in partition, but wil
never remove empty partitions
 If LOB column, reorg can not move rows between
partitions (data references to row location)
 COPY SHRLEVEL CHANGE: will not copy new
partition during copy process.

55 

20/07/2015 
SYSIBM.SYSTABLESPACE
NAME
PARTITIONS
SEGSIZE
TYPE
DSSIZE
MAXPARTITIONS
-----------CBSXXX01
CBSXXX02
CBSXXX03
CBSXXX21
CBSXXX23
---------2
2
0
2
1
------0
0
4
4
4
----
----------0
0
0
4194304
4194304
------------0
0
0
0
2

56 

20/07/2015 
R
G
Use
 Use @ KBC :





57 
The new standard in DB2
Benefit from both segmented TS and partitioned TS
Partitioning by growth : auto space management
• No more partition full problems
• When use of partitioned tablespace for space
reasons
Wait until internal processes and TOOLS can handle it
automatically

20/07/2015 
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

58 

20/07/2015 
DB2 9 Index Design @ KBC
 Asymmetrical index split
 Larger index pages
 Index compression
 Index on expression
 Index key randomization
 Summary

59 

20/07/2015 
What is it ?
 Before DB2 9


an index page can be done as a 100/0 split if keys are
inserted into the end of an index
or as a 50/50 split if keys are inserted in the middle of
the key range
 DB2 9





60 
the insert pattern in an index is detected.
Based on the insert pattern, DB2 9 for z/OS can split
an index page by choosing from several algorithms.
If an ever-increasing sequential insert pattern is
detected for an index, DB2 splits index pages
asymmetrically using approximately a 90/10 split.
No parameter, always activated.

20/07/2015 
Conclusion
 Did some testing and dsn1prnt, and it really
works !!!
 Sequential insert processing will benefit from this
technique :


61 

Less index splits
20/07/2015 
DB2 9 Index Design @ KBC
 Assymetrical index split
 Larger index pages
 Index compression
 Index on expression
 Index key randomization
 Summary

62 

20/07/2015 
What is it ?
 Prior Versions of DB2 for z/OS



Limit index page 4 K
Limits the number of index keys on an index page
Can cause contention in indexes that split frequently
 In DB2 9




63 

Expanded index page sizes of :
• 8K
• 16 K
• 32 K
possible
Allowing more index keys per index page
Reduces the frequency of index page splits
• So less contention
20/07/2015 
How to define and usage
 Using BUFFERPOOL keyword on the CREATE
INDEX or ALTER INDEX

To specify index bufferpools of :
• 8K
• 16 K
• 32 K
 Should be chosen for indexes with sequential
insert patterns

64 

20/07/2015 
Test : Impact on number of pages
CREATE
TABLESPACE CBSDBEP1
IN CBDIAR99
CREATE TABLE
CBTDBEP1
CREATE UNIQUE
INDEX CBX1DBEP1
ON CBTDBEP1
(
KLN_NR ASC,
OVK_TYPE_KD ASC,
OVK_NR ASC,
HOED_KD ASC
)
CLUSTER
PARTITION BY RANGE
( …

65 

20/07/2015 
CREATE UNIQUE
INDEX CBX2DBEP1
ON CBTDBEP1
(
OVK_NR ASC,
HOED_KD ASC,
OVK_TYPE_KD ASC,
KLN_NR ASC
)
USING STOGROUP
CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP2
CLOSE NO
COPY NO
PIECESIZE 2G
COMPRESS NO
;
CREATE
TABLESPACE CBSDBEP2
IN CBDIAR99
CREATE TABLE
CBTDBEP2
CREATE UNIQUE
INDEX CBX1DBEP2
ON CBTDBEP2
(
KLN_NR ASC,
OVK_TYPE_KD ASC,
OVK_NR ASC,
HOED_KD ASC
)
CLUSTER
PARTITION BY RANGE
( …
CREATE UNIQUE
INDEX CBX2DBEP2
ON CBTDBEP2
(
OVK_NR ASC,
HOED_KD ASC,
OVK_TYPE_KD ASC,
KLN_NR ASC
)
USING STOGROUP
CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP8K4
CLOSE NO
COPY NO
PIECESIZE 2G
COMPRESS NO
;
Test CBX2DBEP1 4K / CBX2DBEP2 8K
GDTC-R -------------------------Command ===>
CATALOG ROW
---------
FROM SYSIBM.SYSINDEXES
QUALIFIER: INDEX=CBX2DBEP1
Column Name
Column Value
----v----1----v----2----v----3----v----4----v----5----v-NAME . . . . . . . . . . . . . CBX2DBEP1
CREATOR . . . . . . . . . . . XXXXXXXX
TBNAME . . . . . . . . . . . . CBTDBEP1
TBCREATOR . . . . . . . . . . XXXXXXXX
UNIQUERULE . . . . . . . . . . U
COLCOUNT . . . . . . . . . . . 4
CLUSTERING . . . . . . . . . . N
CLUSTERED . . . . . . . . . . N
DBNAME . . . . . . . . . . . . CBDIAR99
INDEXSPACE . . . . . . . . . . CBX2DBEP
FIRSTKEYCARD . . . . . . . . . 2772538
FULLKEYCARD . . . . . . . . . 4269054
NLEAF . . . . . . . . . . . . 32342
NLEVELS . . . . . . . . . . . 3
BPOOL . . . . . . . . . . . . BP2
PGSIZE . . . . . . . . . . . . 4
CLUSTERRATIO . . . . . . . . . 37
STATSTIME . . . . . . . . . . 2010-06-09-09.13.23.420314
INDEXTYPE . . . . . . . . . . 2
PIECESIZE . . . . . . . . . . 2097152
SPACEF . . . . . . . . . . . . 136800
AVGKEYLEN . . . . . . . . . . 22
GDTC-R -------------------------Command ===>
66 

20/07/2015 
---------
FROM SYSIBM.SYSINDEXES
QUALIFIER: INDEX=CBX2DBEP2
Column Name
Column Value
----v----1----v----2----v----3----v----4----v----5----v-NAME . . . . . . . . . . . . . CBX2DBEP2
CREATOR . . . . . . . . . . . XXXXXXXX
TBNAME . . . . . . . . . . . . CBTDBEP2
TBCREATOR . . . . . . . . . . XXXXXXXX
UNIQUERULE . . . . . . . . . . U
COLCOUNT . . . . . . . . . . . 4
CLUSTERING . . . . . . . . . . N
CLUSTERED . . . . . . . . . . N
DBNAME . . . . . . . . . . . . CBDIAR99
INDEXSPACE . . . . . . . . . . CBX211C9
FIRSTKEYCARD . . . . . . . . . 2772538
FULLKEYCARD . . . . . . . . . 4269054
NLEAF . . . . . . . . . . . . 16050
NLEVELS . . . . . . . . . . . 3
BPOOL . . . . . . . . . . . . BP8K4
PGSIZE . . . . . . . . . . . . 8
CLUSTERRATIO . . . . . . . . . 37
STATSTIME . . . . . . . . . . 2010-06-09-09.14.04.394529
INDEXTYPE . . . . . . . . . . 2
PIECESIZE . . . . . . . . . . 2097152
SPACEF . . . . . . . . . . . . 129600
AVGKEYLEN . . . . . . . . . . 22
NLEAF / 2

CATALOG ROW
Test : Impact on Index Levels
CREATE
TABLESPACE CBSDBEB1
IN CBDIAR99
CREATE TABLE
CBTDBEB1

67 

20/07/2015 
CREATE UNIQUE
INDEX CBX1DBEB1
ON CBTDBEB1
(
PTF_NR ASC,
BWG_DS ASC,
BWG_ODW_KD ASC
)
USING STOGROUP
CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 10
PCTFREE 20
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP2
CLOSE NO
COPY NO
PIECESIZE 2G
COMPRESS NO
;
CREATE
TABLESPACE CBSDBEB2
IN CBDIAR99
CREATE TABLE
CBTDBEB2
CREATE UNIQUE
INDEX CBX1DBEB2
ON CBTDBEB2
(
PTF_NR ASC,
BWG_DS ASC,
BWG_ODW_KD ASC
)
USING STOGROUP
CBGRP001
PRIQTY 10800
SECQTY 10800
ERASE NO
FREEPAGE 10
PCTFREE 20
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP8K4
CLOSE NO
COPY NO
PIECESIZE 2G
COMPRESS NO
;
Test CBX1DBEB1 4K / CBX1DBEB2 8K
GDTC-R -------------------------Command ===>
CATALOG ROW
---------
FROM SYSIBM.SYSINDEXES
QUALIFIER: INDEX=CBX1DBEB1
Column Name
Column Value
----v----1----v----2----v----3----v----4----v----5----v-NAME . . . . . . . . . . . . . CBX1DBEB1
CREATOR . . . . . . . . . . . XXXXXXXX
TBNAME . . . . . . . . . . . . CBTDBEB1
TBCREATOR . . . . . . . . . . XXXXXXXX
UNIQUERULE . . . . . . . . . . P
COLCOUNT . . . . . . . . . . . 3
CLUSTERING . . . . . . . . . . Y
CLUSTERED . . . . . . . . . . Y
DBNAME . . . . . . . . . . . . CBDIAR99
INDEXSPACE . . . . . . . . . . CBX11LHR
FIRSTKEYCARD . . . . . . . . . 33500
FULLKEYCARD . . . . . . . . . 6398661
NLEAF . . . . . . . . . . . . 63987
NLEVELS . . . . . . . . . . . 4
BPOOL . . . . . . . . . . . . BP2
PGSIZE . . . . . . . . . . . . 4
CLUSTERRATIO . . . . . . . . . 99
STATSTIME . . . . . . . . . . 2010-06-14-09.54.40.784982
INDEXTYPE . . . . . . . . . . 2
PIECESIZE . . . . . . . . . . 2097152
SPACEF . . . . . . . . . . . . 288000
AVGKEYLEN . . . . . . . . . . 25

68 

20/07/2015 
GDTC-R -------------------------Command ===>
CATALOG ROW
---------
FROM SYSIBM.SYSINDEXES
QUALIFIER: INDEX=CBX1DBEB2
Column Name
Column Value
----v----1----v----2----v----3----v----4----v----5----v-NAME . . . . . . . . . . . . . CBX1DBEB2
CREATOR . . . . . . . . . . . XXXXXXXX
TBNAME . . . . . . . . . . . . CBTDBEB2
TBCREATOR . . . . . . . . . . XXXXXXXX
UNIQUERULE . . . . . . . . . . P
COLCOUNT . . . . . . . . . . . 3
CLUSTERING . . . . . . . . . . Y
CLUSTERED . . . . . . . . . . Y
DBNAME . . . . . . . . . . . . CBDIAR99
INDEXSPACE . . . . . . . . . . CBX1DBEB
FIRSTKEYCARD . . . . . . . . . 33500
FULLKEYCARD . . . . . . . . . 6398661
NLEAF . . . . . . . . . . . . 31521
NLEVELS . . . . . . . . . . . 3
BPOOL . . . . . . . . . . . . BP8K4
PGSIZE . . . . . . . . . . . . 8
CLUSTERRATIO . . . . . . . . . 99
STATSTIME . . . . . . . . . . 2010-06-14-09.55.49.445774
INDEXTYPE . . . . . . . . . . 2
PIECESIZE . . . . . . . . . . 2097152
SPACEF . . . . . . . . . . . . 280800
AVGKEYLEN . . . . . . . . . . 25
Impact on number of index levels !!!
Test : INSERT

Sequential (1000 inserts) :
• 4K index pages :
ELP
TIME
----1.264
ELP
TIME
DB2
------0.995
CPU
TIME
------0.757
CPU
TIME
DB2
------0.637
EWAIT
IO
------0.036
BP
GETPAGE
------20106
BP
SYNCRD
------24
K
INSERT
------1000
CPU
TIME
DB2
------0.614
EWAIT
IO
------0.073
BP
GETPAGE
------19050
BP
SYNCRD
------17
K
INSERT
------1000
• 8K index pages :
ELP
TIME
----1.498
ELP
TIME
DB2
------1.296
CPU
TIME
------0.735
• Conclusion
¬ Less Getpages / less CPU /

69 

20/07/2015 
Test : INSERT
getpages index

Sequential (1000 inserts) :
• 4K index pages :
BPool +---------- Getpage -------+
Creator
Name
Ty BPool HRatio Number
Time
% failed
--------- ------------------- -- ------ ------ ------ --------------------XXXXXXXX CBX1DBEB1
I BP2
99.9 %
4080 00.0189
2.1
0
• 8K index pages :
Creator
Name
Ty BPool HRatio Number
Time
% failed
--------- ------------------- -- ------ ------ ------ --------------------XXXXXXXX CBX1DBEB2
I BP8K4 99.8 %
3035 00.0584
4.9
0
• Conclusion
¬ Less getpages on index

70 

20/07/2015 
Test : READ

Sequential (1000 selects) :
• 4K index pages :
ELP
TIME
----1.227
ELP
TIME
DB2
------0.943
CPU
TIME
------0.954
CPU
TIME
DB2
------0.809
EWAIT
IO
------0.009
BP
GETPAGE
------23333
BP
SYNCRD
------22
K
OPEN
------1014
K
FETCH
------2028
EWAIT
IO
------0.005
BP
GETPAGE
------22319
BP
SYNCRD
------15
K
OPEN
------1014
K
FETCH
------2028
• 8K index pages :
ELP
TIME
----1.124
ELP
TIME
DB2
------0.906
CPU
TIME
------0.942
CPU
TIME
DB2
------0.797
• Conclusion
¬ Less getpages

71 

20/07/2015 
Test : READ
getpages index

Sequential (1000 selects) :
• 4K index pages :
BPool +---------- Getpage -------+
Creator
Name
Ty BPool HRatio Number
Time
% failed
--------- ------------------- -- ------ ------ ------ --------------------XXXXXXXX CBX1DBEB1
BP2
99.7 %
4056 00.0087
1.0
0
• 8K index pages :
Creator
Name
Ty BPool HRatio Number
Time
% failed
--------- ------------------- -- ------ ------ ------ --------------------XXXXXXXX CBX1DBEB2
I BP8K4 99.8 %
3042 00.0054
0.6
0
• Conclusion
¬ Less getpages on index

72 

20/07/2015 
Index Page Splits
Test insert 1000 rows at the same place in the
index
 Runstats before run :

CREATOR
-------XXXXXXXX
XXXXXXXX

LEAFNEAR
----------6838
3260
LEAFFAR
----------27
16
NLEAF
----------64012
31535
LEAFFAR
----------37
22
NLEAF
----------64021
31540
Runstats after run :
CREATOR
-------XXXXXXXX
XXXXXXXX

INDEXNAME
--------CBX1DBEB1
CBX1DBEB2
INDEXNAME
--------CBX1DBEB1
CBX1DBEB2
LEAFNEAR
----------6838
3260
Conclusion :
• Less NLEAF, LEAFNEAR and LEAFFAR
• So less index page splits !!!

73 

20/07/2015 
Conclusion
 Use @ KBC :



74 

Will be used
It’s The Performance ISSUE of DB2 9 (John Campbell)
• Reduces number of getpages
• Reduce of index levels
• Reduces painfull index splits
• Better insert behaviour
• Even pagesize of 8K gives very good results
• Possibility of compressing an index
20/07/2015 
DB2 9 Index Design @ KBC
 Assymetrical index split
 Larger index pages
 Index compression
 Index on expression
 Index key randomization
 Summary

75 

20/07/2015 
What is it ?
 In DB2 9




76 

Compress index without using a dictionary
Reduces the physical storage space
Index data can be compressed as soon as the first
index entries are added to an index
20/07/2015 
How to define ?
 Using :


COMPRESS YES/NO
On :
• CREATE INDEX
• ALTER INDEX
 The bufferpool used must be :



8K
16 K
32 K
 The physical page on disk will be 4 K

77 

20/07/2015 
When to be used ?
 Recommended for applications


That do sequential insert operations with few or no
delete operations.
Where the indexes are created primarily for scan
operations.
 Attention :


78 

Random insert and deletes can adversely effect
compression.
20/07/2015 
How does it work ?
 DB2 9 for z/OS only compresses the data in the
leaf pages.
 The technique used to compress is based on
eliminating repetitive strings
 Indexpages are stored on disk in their
compressed format

physical 4 KB indexpage on disk
 and are expanded when read from disk into :




79 

8 KB
16 KB
32 KB pages
20/07/2015 
How does it work ?
 Index compression always compresses down, whatever
index page size you choose, to a 4 KB page on disk.
 As inserts and deletes are processed, DB2 keeps track of
the available space remaining for compressed data as it
would fit on disk (4 KB).
 Once DB2 determines that there is no more available
space in the compressed version of the page, DB2 does
not allow additional inserts against the non-compressed
index page.
 This can cause you to waste space in the uncompressed
index page and your bufferpool pages.
 It is best to choose a bufferpage size that minimizes
unused bufferspace.
 One size does not fit all.

80 

20/07/2015 
Compression ratio
 25-75% compression is average
 Use DSN1COMP to estimate the effectiveness


81 

Do not choose a larger index page size than what is
recommended
20/07/2015 
Index compression & Bufferpool impact
 DB2 cannot compress the pages in place
 Transparently manages an “I/O work area" separate
from the bufferpool
 Deferred write engine will asynchronously compress
a leaf page from a buffer into an I/O work area and
then write the page to disk
 Conversely, pages are read from disk into the I/O
work area and then expanded into a buffer in the
bufferpool

82 

20/07/2015 
Some Figures

Report of DSN1COMP :
CBX1DBEC1 (not compressed index)

DSN1999I START OF DSN1COMP FOR JOB C4X00TYM DSN1CMP
DSN1998I INPUT DSNAME =
CERADTC.DSNDBD.CBDIAR99.CBX1DBEC.I0001.A001 , VSAM
---------------------------------------------
16
56
DSN1944I DSN1COMP INPUT PARAMETERS
PROCESSING PARMS FOR INDEX DATASET:
NO LEAFLIM WAS REQUESTED
44
43
DSN1940I DSN1COMP COMPRESSION REPORT
1,000
100,000
100,000
3,125
1,379
Index Leaf Pages Processed
Keys Processed
Rids Processed
KB of Key Data Processed
KB of Compressed Keys Produced
--------------------------------------------32
56
EVALUATION OF COMPRESSION WITH DIFFERENT INDEX
44
71
PAGE SIZES:
8
51
49

83 

---------------------------------------------K Page Buffer Size yields a
% Reduction in Index Leaf Page Space
The Resulting Index would have approximately
% of the original index's Leaf Page Space
No Bufferpool Space would be unused
----------------------------------------------
20/07/2015 
K Page Buffer Size yields a
% Reduction in Index Leaf Page Space
The Resulting Index would have approximately
% of the original index's Leaf Page Space
% of Bufferpool Space would be unused to
ensure keys fit into compressed buffers
---------------------------------------------
K Page Buffer Size yields a
% Reduction in Index Leaf Page Space
The Resulting Index would have approximately
% of the original index's Leaf Page Space
% of Bufferpool Space would be unused to
ensure keys fit into compressed buffers
---------------------------------------------
-
DSN1994I DSN1COMP COMPLETED SUCCESSFULLY,
PROCESSED
1,000
PAGES
Some Figures
FROM SYSIBM.SYSINDEXES
QUALIFIER: INDEX=CBX1DBEC1
Column Name
Column Value
----v----1----v----2----v----3----v----4----v----5
NAME . . . . . . . . . . . . . CBX1DBEC1
CREATOR . . . . . . . . . . . XXXXXXXX
TBNAME . . . . . . . . . . . . CBTDBEC1
TBCREATOR . . . . . . . . . . XXXXXXXX
UNIQUERULE . . . . . . . . . . P
COLCOUNT . . . . . . . . . . . 3
CLUSTERING . . . . . . . . . . Y
CLUSTERED . . . . . . . . . . Y
DBNAME . . . . . . . . . . . . CBDIAR99
INDEXSPACE . . . . . . . . . . CBX1DBEC
FIRSTKEYCARD . . . . . . . . . 737
FULLKEYCARD . . . . . . . . . 100000
NLEAF . . . . . . . . . . . . 1000
NLEVELS . . . . . . . . . . . 3
BPOOL . . . . . . . . . . . . BP2
PGSIZE . . . . . . . . . . . . 4
CLUSTERRATIO . . . . . . . . . 99
STATSTIME . . . . . . . . . . 2010-07-02-11.40.10.754746
INDEXTYPE . . . . . . . . . . 2
PIECESIZE . . . . . . . . . . 2097152
SPACEF . . . . . . . . . . . . 7200
AVGKEYLEN . . . . . . . . . . 25
COMPRESS . . . . . . . . . . . N
****************************** BOTTOM OF DATA ******

84 

20/07/2015 
FROM SYSIBM.SYSINDEXES
QUALIFIER: INDEX=CBX1DBEC2
Column Name
Column Value
----v----1----v----2----v----3----v----4----v----5----v-NAME . . . . . . . . . . . . . CBX1DBEC2
CREATOR . . . . . . . . . . . XXXXXXXX
TBNAME . . . . . . . . . . . . CBTDBEC2
TBCREATOR . . . . . . . . . . XXXXXXXX
UNIQUERULE . . . . . . . . . . P
COLCOUNT . . . . . . . . . . . 3
CLUSTERING . . . . . . . . . . Y
CLUSTERED . . . . . . . . . . Y
DBNAME . . . . . . . . . . . . CBDIAR99
INDEXSPACE . . . . . . . . . . CBX114HL
FIRSTKEYCARD . . . . . . . . . 737
FULLKEYCARD . . . . . . . . . 100000
NLEAF . . . . . . . . . . . . 493
NLEVELS . . . . . . . . . . . 3
BPOOL . . . . . . . . . . . . BP8K4
PGSIZE . . . . . . . . . . . . 8
CLUSTERRATIO . . . . . . . . . 99
STATSTIME . . . . . . . . . . 2010-07-02-11.44.48.412212
INDEXTYPE . . . . . . . . . . 2
PIECESIZE . . . . . . . . . . 2097152
SPACEF . . . . . . . . . . . . 7200
AVGKEYLEN . . . . . . . . . . 25
COMPRESS . . . . . . . . . . . Y
****************************** BOTTOM OF DATA *****
Some Figures

Full Index only scan
SELECT
FROM
ORDER
PTF_NR, BWG_DS, BWG_ODW_KD
CBTDBEC1/2
BY 1, 2, 3
BPool
Creator
Name
Ty BPool
--------- ------------------- -- -----XXXXXXXX CBX1DBEC1
I BP2
+Sync Read I/O+
Number
Time
------ -------7 00.0141
Synch Wrt
Number
--------0
Async I/O
number pages
-----------0
32
BPool
Creator
Name
Ty BPool
--------- ------------------- -- -----XXXXXXXX CBX1DBEC2
I BP8K4

85 

+Sync Read I/O+
Number
Time
------ -------7 00.0111
20/07/2015 
Synch Wrt
Number
--------0
+---------- Getpage -------+
HRatio Number
Time
% failed
------ ------ --------------------96.1 %
1005 00.0187
4.0
0
Max
part
---1
+---------- Getpage -------+
HRatio Number
Time
% failed
------ ------ --------------------97.0 %
498 00.0172
4.3
0
Async I/O
number pages
-----------0
8
Max
part
---1
Conclusion



This is NOT a Performance ISSUE !!!
Only ment to save DASD !!!
Use @ KBC :




86 

Index Compression will be used
Better sequential insert behaviour
Better sequential read behaviour
20/07/2015 
DB2 9 Index Design @ KBC
 Assymetrical index split
 Larger index pages
 Index compression
 Index on expression
 Index key randomization
 Summary

87 

20/07/2015 
What is it ?
 Instead of simply put a column in an index, you
now also can code a key-expression in an index
 Overhead :



88 

No Select overhead
Overhead depends on the kind of expression
• Impact :
¬ Insert
¬ Update on key value
¬ Load
¬ Rebuild index
¬ Reorg tablespace
¬ Check index
• No impact :
¬ Reorg index
20/07/2015 
Example
 Join CEVPSN01 and CEVVZA01



89 

KLN_NR in PSN01 : CHAR(7)
KLN_NR in VZA01 : CHAR(9) -> BUY_NO
• 2 first characters ’00’
20/07/2015 
Creation of extra index using
INDEX on EXPRESSION
CREATE UNIQUE
INDEX CEX2VZA01
ON CETVZA01
(
SUBSTR(KLN_NR,3,7) ASC,
DTB_KD ASC,
SMT_GRP_KD ASC
)
USING STOGROUP CEGRP001
PRIQTY 180
SECQTY 180
ERASE NO
FREEPAGE 0
PCTFREE 0
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP2
CLOSE YES
COPY NO
PIECESIZE 2G
COMPRESS NO
;

90 

20/07/2015 
Query
SELECT A.KLN_NR
, A.PSN_NM
, A.STR_NM
, A.HUIS_NR
, A.BUS_NR
, A.ADR_LAND_KD
, A.POST_KD
, A.GEM_NM
, B.DTB_KD
, B.SMT_GRP_KD
, B.FREQ_KD
, B.VZND_DG
, B.WK_DG_KD
, B.WK_PAR_KD
FROM CEVPSN01 A
, CEVVZA01 B
WHERE A.KLN_NR LIKE '12345%'
AND A.KLN_NR = SUBSTR(B.KLN_NR,3,7)

91 

20/07/2015 
Explain
GDTC
Command ===>
Explain Results for SQLTEXT
Scroll ===> CSR
More:
>
Actions: S H R RS RW RI XD XS W P T C D U IM SA
LBL
STMTNO
COST*RATE SQL-STATEMENT
XD01
0
20.445724 SELECT A.KLN_NR , A.PSN_NM , A.STR_NM , A.HUIS_NR
COST*RATE QB PL MIX QTYPE METH ACC MTCH IX TBNAME
IXNAME
XD01 10.298786 1 1
0 SELECT
0 I
1 N CETPSN01
CEX2PSN
XD01 10.146938 1 2
0 SELECT
1 I
1 N CETVZA01
CEX2VZA
******************************** Bottom of Data *******************************
Accesspath OK
Uses the new index containing the index on expression

92 

20/07/2015 
Error handling
 Errors for an index on expression are returned
when the expression is evaluated :





93 

Insert
Update on key value
Rebuild index
…
20/07/2015 
Conclusion
 Use @ KBC :



94 

Already in use
Can speed up (performance) processing of badly
designed/mapped applications
• In operational environment
• And informational environment
20/07/2015 
DB2 9 Index Design @ KBC
 Assymetrical index split
 Larger index pages
 Index compression
 Index on expression
 Index key randomization
 Summary

95 

20/07/2015 
What is it ?
 Index contention, especially on a hot page, can
be a major problem in a data sharing environment
and can limit scalability.
DB2 9 for z/OS new-function mode introduces a
randomized index key order.
 The randomized key order allows DB2 to spread
out index keys within the whole index tree,
instead of maintaining an ascending or
descending order,
thereby minimizing index page contention and
turning a hot index page into a cool index page.

96 

20/07/2015 
Problems
 In my test scenario’s index



97 

used when :
• IX-COL = value
Not used :
• All other situations
20/07/2015 
2e IX RANDOM
CREATE TABLE
CBTDBER2
(
CTR_NR CHAR(4) NOT NULL
FOR SBCS DATA
,KLN_NR CHAR(7) NOT NULL WITH
DEFAULT
FOR SBCS DATA
,TEKST CHAR(89) NOT NULL WITH
DEFAULT
FOR SBCS DATA
,
CONSTRAINT CBTDBER1PK
PRIMARY KEY
(
CTR_NR
)
)
IN CBDIAR99.CBSDBER2
CCSID EBCDIC
NOT VOLATILE
APPEND NO
;

98 

20/07/2015 
CREATE UNIQUE
INDEX CBX1DBER2
ON CBTDBER2
(
CTR_NR ASC
)
USING STOGROUP
CBGRP001
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
CLUSTER
BUFFERPOOL BP2
CLOSE NO
COPY NO
PIECESIZE 2G
COMPRESS NO
;
CREATE UNIQUE
INDEX CBX2DBER2
ON CBTDBER2
(
KLN_NR RANDOM
)
USING STOGROUP
CBGRP001
PRIQTY 720
SECQTY 720
ERASE NO
FREEPAGE 0
PCTFREE 5
GBPCACHE CHANGED
NOT CLUSTER
BUFFERPOOL BP2
CLOSE NO
COPY NO
PIECESIZE 2G
COMPRESS NO
;
2e IX RANDOM
PAGE: # 00000003 --------------------------------------------------------------TYPE 2 INDEX LEAF PAGE: PGCOMB='00'X PGLOGRBA='C6141783D4B6'X PGNUM='00000003
IPPE='E'
INDEX PAGE HEADER:
HEADER EXTENSION:
IPIXID='02CD'X IPFLAGS='40A8'X IPNKEYS=169 IPFREESP='0682
IPFCHSIZ='0000'X IPFCHNXT='0000'X IPKEYLN='0007'X IPPRNTP
IPLEVEL='01'X IPEXTLEN='06'X IPSEQNO=42 IPNDRIDS=169 IPD
IPLNXTPG='00000017'X IPLPRVPG='00000000'X
IPLOWBNDIDX=169 IPHIBNDIDX=6 IPEXTFLAGS='00'X IPNUMSEQINS=
UNIQUE KEYS FOLLOW:
KEY ENTRY: IPKMAP(XI)='081E'X
KEY:
011A0175 EADEB9
RID:
400000A017
KEY ENTRY: IPKMAP(XI)='003E'X
KEY:
011A01C0 872A86
RID:
0000003712

.......
.......
Each key is scrambled and that scrambled
value is handled as a std index
99 

20/07/2015 
Conclusion
 Use @ KBC :



100 

Index Key Randomization can be used in some
specific cases
Especially when we detect an application dealing with
a lot of contention problems due to a hot index page,
and redesign of the application isn’t an option
20/07/2015 
DB2 9 Index Design @ KBC
 Assymetrical index split
 Larger index pages
 Index compression
 Index on expression
 Index key randomization
 Summary

101 

20/07/2015 
Conclusion about new index stuff
 Use @ KBC :


102 

We will try to define a new standard defining indexes
• 8K + Compression + Append -> log-tables
• 8K + Compression -> standard IX
20/07/2015 
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

103 

20/07/2015 
DB2 9 Features @ KBC
 Open Datasets

104 

20/07/2015 
Open datasets command
 Before DB2 9

For high-volume tablespaces/partitions and indexes :
Open datasets before use via a SQL-select in a special job
 DB2 9


New “Open dataset ahead of use” command
-ACCESS DB(dbname) SPACE(spacename) PART(n)
MODE(OPEN)
 Use @ KBC :




Will be used in the future in specific cases
Much more easy then writing some SQL to perform the same
Each morning around 6 o’clock we now perform some SQL to
open the datasets for a very high insert application, because
all transactions of the application have very little time to
respond, also the first one
105 

20/07/2015 
Agenda
• Utilities
• SQL
• Design
•
•
•
Table
Tablespace
Index
• Features
• Performance/Accesspath

106 

20/07/2015 
DB2 9 Performance/Accesspath @
KBC
 Access Path Backup

107 

20/07/2015 
Access path backup
 Before DB2 9

OPTHINTs
 DB2 9



108 

Terminology
• Package stability
• Plan management
Allow to preserve multiple package copies and allow
users to switch back to a previous copy of the bound
package.
20/07/2015 
Different possibilities
 A new system parameter : PLANMGMT
 PLANMGMT(OFF)

No change to existing behavior. A package continues to
have one active copy.
 PLANMGMT(BASIC)

The package has one active, current copy, and one
additional previous copy is preserved.
 PLANMGMT(EXTENDED)


109 

It retains up to three copies of a package: one active
copy and two additional old copies (PREVIOUS and
ORIGINAL) are preserved.
20/07/2015 
Rollback Access Path
 Controlled by SWITCH option for REBIND PACKAGE
SWITCH(PREVIOUS)
• The PREVIOUS copy is activated
• Switches from the current to the previous copy
• Provides a means of falling back to last used copy
SWITCH(ORIGINAL)
• The ORIGINAL copy is activated. The current copy
moves to previous, and the original copy becomes
current.
• It provides a mean of falling back to the oldest known
package.



110 

20/07/2015 
Delete Old Package Copies
 A new FREE PACKAGE option called
PLANMGMTSCOPE allows you to free older
copies that are no longer necessary.



111 

PLANMGMTSCOPE(ALL)
To free the entire package including all copies. This is
the default.
PLANMGMTSCOPE(INACTIVE)
To free all old copies only
20/07/2015 
Use
 Use @ KBC :




112 
Not Easy to use
When stmts or stmtno’s in pg change, previous version
not usefull
Usefull when upgrading to a new version of DB2

20/07/2015 
Overall DB2 9 conclusion
 Good applicative enhancements in DB2 9
 Especially the new index, table and tablespace
stuff gives us some great opportunities to help our
applications perform better on different topics :





113 
Throughput
Concurrency
Performance
Space

20/07/2015 

114 

20/07/2015 