DB2 V7 Performance Topics

1
DB2 UDB Server Version 7 for z/OS and OS/390
Performance Topics
Jan Vandensande Jeeves Consulting N.V E-mail
janv_at_jeeves.be
2
DB2 V7 Performance Topics

• AGENDA
• SQL Enhancements
• Subsystem performance
• Availability capacity
• Utilities
• Data sharing
• External enhancements
• QA

3
DB2 V7 Performance Topics
SQL Enhancements
4
SQL Enhancements

• Scrollable Cursors
• Scrolling concept moved into SQL, standards
• Allows application to scroll forwards or
  backwards
• Defined by using the SCROLL keyword on declare
  cursor

5
SQL Enhancements

• Scrollable Cursors
• DECLARE CURSOR defines the sensitivity of the
  cursor to changes
• SENSITIVE changes made to the base table after
  the cursor is materialized CAN be made visible to
  the cursor
• INSENSITIVE changes made to the base table after
  the cursor is materialized will NOT be visible to
  the cursor

6
SQL Enhancements

• Scrollable cursors
• New keywords on FETCH control the new position
  for the cursor and a direction in which to scroll
• NEXT/PRIOR position the cursor on next/previous
  row relative to the current position
• FIRST/LAST position the cursor on the first/last
  row of the result table
• ABSOLUTE 'n' positions the cursor at row 'n' of
  the result table. (row 'n' from the bottom if 'n'
  is negative)
• RELATIVE 'n' positions the cursor 'n' rows after
  the current position (before if 'n' is negative)

7
SQL Enhancements

• Scrollable cursors
• New keywords on FETCH

8
SQL Enhancements

• Scrollable cursors

• DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
• SELECT CUST_NO, CUST_NAME, CRED_LIM
• FROM CUST
• WHERE TOWN 'LONDON'
• OPEN C1
• FETCH C1 INTO WK-CNO, WK-CNM, WK-CRED

9
SQL Enhancements

• Scrollable cursors

10
SQL Enhancements

• Selfreferencing update/delete
• Target tables are allowed within subqueries in
  the WHERE or SET clauses
• This enhancement does not support UPDATE and
  DELETE WHERE CURRENT OF when the cursor
  definition contains a self referencing subquery.

11
SQL Enhancements

• Selfreferencing update/delete

12
SQL Enhancements

• Selfreferencing update/delete
• In previous versions of DB2, this
  self-referencing would not be allowed. The
  following 118 SQLCODE would be returned, and the
  statement would fail
• -118 THE OBJECT TABLE OR VIEW OF THE DELETE OR
  UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM
  CLAUSE
• DB2 now will accept the syntax of this statement,
  and the SQLCODE -118 will not be returned.

13
SQL Enhancements

• MIN/MAX enhancement.
• Before V7
• SELECT MIN(col) and there is an ascending index
  on col, then DB2 can use a one-fetch index access
  (ACCESSTYPE I1) to retrieve the MIN value.
• SELECT MAX(col) and if there is no descending
  index on col, only an ascending index, then DB2
  performs a non-matching index scan (ACCESSTYPE
  I, MATCHCOLS 0) to locate the MAX value.

14
SQL Enhancements

• MIN/MAX enhancement.
• V7
• Evaluate MAX function
• by a one-fetch Index Access on Ascending Index
• Evaluate MIN function
• by a one-fetch Index Access on Descending Index

15
SQL Enhancements

• MIN/MAX enhancement.
• Index , root page and 2 leaf pages
• The larger the index , the greater the
  performance improvement
• Review if indexes in support of min and max can
  be dropped
• Rebind packages

16
SQL Enhancements

• Union anywhere
• Possible to code a UNION or UNION ALL in
• a view
• a table expression
• predicates
• an INSERT statement
• the SET clause of an UPDATE statement
• a declared temporary table.

17
SQL Enhancements

• Union anywhere

18
SQL Enhancements

• Union anywhere

19
SQL Enhancements

• CREATE VIEW CUST_X AS
• SELECT CUST_NO, CUST_NAME, TOWN, CRED_LIM
• FROM CUST
• WHERE TOWN 'MANCHESTER'
• UNION ALL
• SELECT CUST_NO, CUST_NAME, TOWN, CRED_LIM
• FROM CUST
• WHERE CRED_LIM gt 1000
• UNION ALL
• SELECT CUST_NO, CUST_NAME, TOWN, CRED_LIM
• FROM CUST
• WHERE CUST_NAME LIKE 'S'

DSNT408I SQLCODE -154, ERROR THE STATEMENT IS
INVALID BECAUSE THE VIEW OR TABLE DEFINITION
CONTAINS A UNION, A UNION ALL, OR A REMOTE OBJECT
V7 OK
20
SQL Enhancements
21
SQL Enhancements

• Union anywhere
• Union of 10 non partitioned tables
• Each table 1 000 000 rows
• Partitioned table, 10 000 000 rows
• Each partition 1 000 000 rows

22
SQL Enhancements

• Select 10 million rows
• Select 200 000 rows
• Select 1 row

23
SQL Enhancements

• Joining on columns of different data types
• DB2 V6 allowed a join predicate to be Stage 1 and
  potentially Indexable even if there was a
  mismatch on data type or length. This enhancement
  was limited to string data types only.
• DB2 V7 allows a join predicate to be Stage 1 and
  potentially indexable when there is a data type
  or length mismatch for numeric columns.
• In order for this to be true you must, in your
  SQL, cast one column to the data type of the
  other.

24
SQL Enhancements

• Joining on columns of different data types

25
SQL Enhancements

• Row value expressions
• Prior to DB2 DB2 V7, a predicate could compare a
  single column to a single expression.
• DB2 V7 supports row value expressions, and so a
  predicate can compare a list of columns to a list
  of expressions.

• SELECT FROM SYSIBM.SYSINDEXES T1WHERE
  (T1.TBCREATOR, T1.TBNAME) (PAOLOR2,
  ACCOUNT) ORDER BY T1.CREATOR, T1.NAME

26
SQL Enhancements
27
SQL Enhancements

• Row value expressions
• If the number of expressions or columns returned
  on the right hand side do not match the number of
  elements on the left hand side, then SQLCODE -216
  will be returned

• -216 THE NUMBER OF ELEMENTS ON EACH SIDE OF A
• PREDICATE OPERATOR DOES NOT MATCH.
• PREDICATE OPERATOR IS op.

28
SQL Enhancements

• Correlated subquery to join transformation

Select a1 from A where a1 in (select b1 from b
where b2a2)
transformed into
Select a1 from A,B where a1b1 and a2b2
Join more efficient than subquery optimizer
can choose best table join sequence
29
SQL Enhancements

• Correlated subquery to join transformation
• In DB2 V7 the correlated subquery transformation
  will also take place for
• Searched update/deletes
• Non unique subquery result
• EXISTS is supported

30
SQL Enhancements

• Parallelism for index IN-list access
• V6
• Parallellism for IN-list index access for inner
  table in a join
• V7
• Parallellism for IN-list index access without
  restrictions
• the maximum degree of parallelism that can be
  chosen is determined as follows
• For I/O intensive queries the smaller of the
  number of values in the IN-list and the number of
  partitions
• For CPU intensive queries the smaller of the
  number of values in the IN-list and the number of
  CPU engines.

31
SQL Enhancements

• Parallelism for index IN-list access
• 4 500 000 rows
• 7 values in de IN list
• Degree 7
• The performance results show the normal pattern
  for successful use of parallelism a large
  reduction in elapsed time in return for a small
  increase in CPU time.

32
SQL Enhancements

• Sort avoidance

SELECT C1,C2,C3,C4 FROM T1 WHERE C25 AND C4
7 AND C5 2 ORDER BY C1,C2,C3,C4
SELECT C1,C2,C3,C4 FROM T1 WHERE C25 AND C4
7 AND C5 2 ORDER BY C1,C3
C2, C4, C5 can be removed from the ORDER BY
without impacting the results.If an index on
C2,C1,C5,C4,C3 existed, it can now be used
avoiding a sort.
33
SQL Enhancements

• Fetch first n rows
• N rows and only n rows can be fetched
• Sqlcode 100 for fetch n1
• use FETCH FIRST 1 ROW ONLY for existence
  checking.

SELECT 1 INTO WK-NUM FROM ORD WHERE AMOUNT
gt1000 FETCH FIRST 1 ROW ONLY
34
SQL Enhancements

• Bind improvements
• Improvements to the DB2 Optimizer processing will
  result in significantly reduced storage
  requirements in the DBM1 address space and also
  reduced CPU use during the BIND command execution
  when performing access path selection for an SQL
  statement that joins more than 9 tables.

35
SQL Enhancements Summary

• SQL Enhancements
• Scrollable cursors
• Self referencing update/delete
• Min/max enhancement
• Union everywhere
• Joining on columns of different data types
• Row value expressions
• Correlated subquery to join transformation
• Parallelism for index IN-list access
• Sort avoidance
• Fetch first n rows
• Bind improvements

36
Quiz time

• Are columns with different numeric data type
  indexable ?
• Yes, if you cast one column to the data type of
  the other

37
DB2 V7 Performance Topics
Subsystem Performance
38
Subsystem performance

• Asynchronous preformat
• Before V7
• When insert processing is touching the limit of
  the (pre)formatted area, synchronous
  preformatting need to be done.
• This takes typically 0.2 to 1 second and during
  this time period insert processing is held up.
• Preformat will format 2 cylinders at a time on a
  3390 device unless the allocated space is smaller
  then a cylinder then only 2 tracks will be
  formatted

39
Subsystem performance

• Asynchronous preformat
• V7
• Insert activity will use the formatted space in a
  table space/index/partition pageset until a
  threshold is reached. Hitting this threshold
  triggers a preformat service task.
• DB2 supports up to 20 preformat/extend service
  tasks to perform preformat/extend processing in
  parallel.
• Only one preformat/extend service task can be
  active on a given table space/index/partition
  pageset at one time.

40
Subsystem performance
Asynchronous preformat
41
Subsystem performance

• Asynchronous preformat
• Elapsed time reduction can be estimated from data
  extend wait time in accounting class 3
• I/O bound insert - the deferred write task and
  the preformat service task will compete for the
  same I/O resources (UCB ...).
• In this situation it is better to use
  LOAD/REORG preformat

42
Subsystem performance
CPU bound test case sequential insert of 8
million rows, 200 bytes per row
43
Subsystem performance
I/O bound test case sequential insert of 2
million rows, 800 bytes per row
44
Subsystem performance

• Parallel data set open

45
Subsystem performance

• Parallel data set open
• Before DB2 V5, there was only a single task to
  perform open/close processing of DB2 database
  data sets.
• DB2 V5 10 parallel tasks for open/close
  processing and this number was increased to 20 in
  DB2 V6.
• Parallel data set open during restart was
  introduced in DB2 V6
• Parallel open/close of partitions within the same
  partitioned table space/index comes with DB2 V7.

46
Subsystem performance
Ten parallel jobs each accessing 20 separate
partitions of a partitioned table space with 200
partitions 2.2 times elapsed time reduction
47
Subsystem performance
V2.3
V3
V4
V5
V6
V7

• Most customers expected to go to V6
• Migration direct from V5 to V7 possible
• Primarily for later migrations
• Includes Fallback Data Sharing Coexistence
• Maximum of two releases
• V7 SPE PQ34467 required for fallback, data
  sharing, and strongly recommended for all

48
Subsystem performance

• Catmaint
• Migration V6 -gt V7
• Migration V5 -gt V7
• Catmaint performance
• 10 times elapsed time improvement migrating from
  DB2 V6 to DB2 V7 catalog compared to migration
  from DB2 V5 to DB2 V6 catalog
• 16 elapsed time improvement migrating from DB2
  V5 to DB2 V7 catalog compared to migration from
  DB2 V5 to DB2 V7 catalog via DB2 V6 catalog

49
Subsystem performance
50
Subsystem performance
51
Subsystem performance

• Logonly recovery improvement
• The recover base RBA field on the pageset header
  page, representing the starting log RBA value for
  a logonly recovery, was updated when
• pseudo close or close happened for an object
• stop command was issued for the object
• QUIESCE, LOAD utility runned against an object
• In DB2 V7 the HPGRBRBA value is updated every
  nth checkpoint were n is controlled by the value
  of the DSNZPARM parameter DLDFREQ.

52
Subsystem performance

• Logonly recovery improvement
• Logonly recovery will benefit from limiting the
  amount of log records to be scanned by the
  recover utility. Especially logonly recoveries
  with data, restored from copies made during a log
  suspend/resume interval , will benefit from this
  enhancement.

53
Subsystem performance

• Storage statistics report
• The components that are responsible for the major
  part of the virtual memory consumption in the
  DBM1 address space are now reported in section
  STORAGE STATISTICS of the DB2PM statistics long
  report.

54
Subsystem performance
55
Subsystem performance

• Evaluate uncommitted
• A new DSNZPARM parameter, EVALUNC, has been
  introduced this new subsystem parameter enables
  DB2 to take fewer locks during query processing
• Data that does not satisfy the predicate during
  evaluation but then, because of undo processing
  (ROLLBACK or statement failure), reverts to a
  state that does satisfy the predicate is missing
  from the answer set.

56
Subsystem performance

• Reduced logging for variable-length rows
• In DB2 V7 the data logged starts at the first
  byte of the first changed column to the last byte
  of the last changed column.
• Restrictions
• The update does not change the row length
• No hardware compression is done nor editprocs
  are used

57
Subsystem performance

• DDL concurrency improvement
• Row level locking for catalog table spaces is now
  allowed for those catalog table spaces that do
  not contain links.
• This enhancement should further improve parallel
  DDL

58
Subsystem performance

• Summary
• Asynchronous preformat
• Parallel data set open
• Catmaint utility
• Logonly recovery improvement
• Storage statistics report
• Evaluate uncommitted
• Reduced logging for variable-length rows
• DDL concurrency improvement

59
Quiz time

• What amount of space is preformatted by the
  async. preformat task on a 3390 device ?
• 2 cylinders or 2 tracks if space allocation less
  than a cylinder

60
DB2 V7 Performance Topics
Availability Capacity
61
Availability Capacity

• Online DSNZPARM
• Before DB2 V7, subsystem parameters were only
  loaded at startup
• New command allows users to load a new DSNZPARM
  module without recycling the subsystem
• Load module granularity
• Allows for online change of many, but not all,
  parameters (about 60 that can be changed)
• New sample DSNTEJ6Z provides report of parameter
  settings
• SET SYSPARM command

62
Availability Capacity

• Online DSNZPARM
• SET SYSPARM LOAD (load module name)
• loads the specified module
• loads DSNZPARM if load module name not specified
• SET SYSPARM RELOAD
• reloads the last named subsystem parameter load
  module
• SET SYSPARM STARTUP
• resets loaded parameters to their start-up values

63
Availability Capacity
64
Availability Capacity
-SET SYSPARM LOAD(DSNZPAR1) Message DSNZ014I is
generated if the value for an unchangeable
parameter differs from the startup value
65
Availability Capacity

• Consistent restart
• In DB2 V6, back out during DB2 restart could be
  postponed to make DB2 available faster after an
  outage.
• The postponed units of recovery left some objects
  in RESTP.
• To resolve this status you had to issue the
  RECOVER POSTPONED command

66
Availability Capacity

• Consistent restart
• DB2 V7 introduces further enhancements.The
  -RECOVER POSTPONED CANCEL allows you to cancel
  postponed units of recovery. Impacted page
  sets/partitions are marked Refresh Pending (REFP)
  and LPL.
• The REFP,LPL status can be resolved in one of the
  following ways
• LOAD REPLACE the impacted pagesets/partitions
• RECOVER TO (LOGPOINT, LOGRBA, TOCOPY)
• START DB() SPACE() ACCESS(FORCE)

67
Availability Capacity

• Log manager
• Log Suspend/resume
• Temporarily "freezes" all updates to a DB2
  subsystem
• Allow fast copy of entire DB2 system with
  snapshot/flashcopy
• Minimal disruption to take backup for disaster
  recovery, suspend updates for a brief period
  while the system is 'snapped
• Straightforward and rapid restart at a secondary
  site. When DB2 is started forward and backward
  recovery completes as normal
• As fast as DB2 restart following a crash at the
  local site

68
Availability Capacity

• Log manager
• Effects of SET LOG SUSPEND command
• A system checkpoint is taken ( non data sharing
  only, in data sharing a system checkpoint could
  cause a system hang due to the log suspend of the
  other members)
• A log write latch is obtained
• The log buffers are flushed
• The BSDS is updated with the highest written RBA
• A highlighted message, DSNJ372I, is issued to the
  console

DSNJ372I DB2A DSNJC09A UPDATE ACTIVITY HAS
BEEN SUSPENDED FOR DB2A AT RBA 00000031CB60, LRSN
00000031CB60, PRIOR CHECKPOINT RBA
000000317B3A DSN9022I DB2A DSNJC001 -SET LOG
NORMAL COMPLETION
69
Availability Capacity

• Log manager
• Effects of SET LOG RESUME command
• The log write latch is released
• The highlighted log suspend message is deleted
• The log resumed message is issued.

DSNJ373I DB2A DSNJC09A UPDATE ACTIVITY HAS BEEN
RESUMED FOR DB2A DSN9022I DB2A DSNJC001 -SET
LOG NORMAL COMPLETION
70
Availability Capacity

• Log manager
• Retry critical log read access errors
• prevent DB2 termination for temporary log access
  errors during must complete operations
• highlighted error message
• WTOR to retry log read request

71
Availability Capacity

• Log manager

DSNJ104I - DSNJR206 RECEIVED ERROR STATUS
00000004 FROM DSNPCLOC FOR DSNAMEDSNC710.ARCHLOG1
.A0000049 DSNJ104I - DSNJR206 RECEIVED ERROR
STATUS 00000004 FROM DSNPCLOC FOR
DSNAMEDSNC710.ARCHLOG2.A0000049 DSNJ153E -
DSNJR006 CRITICAL LOG READ ERROR CONNECTION-IDTES
T0001 CORRELATIOND-IDCTHDCORID001 LUWID
V71A-SYEC1DB2.B343707629D10 REASON-CODE00D10345
26 DSNJ154I - DSNJHR126 REPLY Y TO RETRY LOG
READ REQUEST, N TO ABEND
72
Availability Capacity

• Log manager
• Time interval system checkpoint frequency
• LOGLOAD system checkpoint frequency
• SET LOG LOGLOAD(50000)
• TIME checkpoint frequency
• SET LOG CHKTIME(10)

DB2A SET LOG CHKTIME(20) DSNJ339I DB2A DSNJC009
SET LOG COMMAND COMPLETED, CHKTIME (20) DSN9022I
DB2A DSNJC001 -SET LOG NORMAL COMPLETION
73
Availability Capacity

• Log manager

DB2A DIS LOG DSNJ370I DB2A DSNJC00A LOG
DISPLAY CURRENT COPY1 LOG DB2V710B.LOGCOPY1.DS01
IS 52 FULL CURRENT COPY2 LOG
DB2V710B.LOGCOPY2.DS01 IS 52 FULL H/W RBA
00000117F4A2 H/O RBA 000000000000 FULL LOGS TO
OFFLOAD 0 OF 6 OFFLOAD TASK IS
(AVAILABLE) DSNJ371I DB2A DB2 RESTARTED 115006
NOV 10, 2000 RESTART RBA 000000825000 CHECKPOINT
FREQUENCY 20 MINUTES LAST SYSTEM CHECKPOINT TAKEN
183136 NOV 10, 2000 DSN9022I DB2A DSNJC001
-DIS LOG NORMAL COMPLETION
74
Availability Capacity

• Log manager
• Long running UR warning
• new warning message based on the number of log
  records written by in-flight UR
• message and trace record repeated each time
  threshold is reached
• URLGWTH zparm parameter

DSNJ031I DB2A DSNJW001 WARNING - UNCOMMITTED
UR HAS WRITTEN 1000 LOG RECORDS - CORRELATION
NAME PAOLOR6E CONNECTION ID BATCH LUWID
DB2A.SCPDB2A.B4ECE117BB5C 91 PLAN NAME
DSNTEP71 AUTHID PAOLOR6 END USER ID
TRANSACTION NAME WORKSTATION NAME
75
Availability Capacity

• Statistics history
• DB2 V7 has a new parameter for the RUNSTATS
  utility that allows you to gather runstats
  information into history catalog tables
• trend analysis
• in support of Visual Explain for OS/390
• cleanup - MODIFY STATISTICS

76
Availability Capacity

• Statistics history
• The data is saved in the following new DB2
  catalog tables
• SYSIBM.SYSCOLDIST_HIST
• SYSIBM.SYSCOLUMNS_HIST
• SYSIBM.SYSINDEXES_HIST
• SYSIBM.SYSINDEXPART_HIST
• SYSIBM.SYSINDEXSTATS_HIST
• SYSIBM.SYSLOBSTATS_HIST
• SYSIBM.SYSTABLEPART_HIST
• SYSIBM.SYSTABLES_HIST
• SYSIBM.SYSTABSTATS_HIST

77
Availability Capacity

• Real Time Statistics
• DB2 provides the necessary statistics that can be
  used to determine which objects require REORG,
  RUNSTATS or COPY
• DB2 will always generate in-memory statistics for
  each table space and index space in DB2.
• DB2 will only externalize these statistics when
• The required database, table space, tables and
  indexes are created. The objects are created with
  the specified object names, schema name and the
  specified attributes
• The Real Time Statistics (RTS) database is
  started in RW mode immediately after creation
  this database is stopped

78
Availability Capacity

• Real Time Statistics
• DSNACCOR stored procedure
• This stored procedure will query the new DB2 Real
  Time Statistics (RTS) tables to determine which
  DB2 objects should be
• Reorganized
• statistics updated
• image copied
• or have
• exceeded number of extents
• The default scope for this stored procedure is to
  scan "all" data in the RTS tables and provide
  recommendations for "any" condition mentioned
  above.
• Used by control center and

79
Availability Capacity

• Adding workfiles
• CREATE and DROP workfile table space without
  having to STOP the workfile database
• This enhancement reduces performance problems and
  improves availability in a 24x7 environment

80
Availability Capacity

• Online change of time out value
• new modify command option to dynamically change
  the time out value.
• IRLMRWT not changeable via zparm

/F DB2GIRLM,SET,TIMEOUT65,DB2G Response on the
MVS console DXR177I IRLG001 THE VALUE FOR TIMEOUT
IS SET TO 65 FOR DB2G
81
Availability Capacity

• Subsecond deadlock detection
• Current lower limit is 1 sec
• Speed of processors increases
• The number of transactions per second is
  increasing
• Deadlock detection interval can now be specified
  in milliseconds
• Lower limit is 100 msec
• Measurements done with deadlock cycles of 100
  msec and 1000msec show no appreciable performance
  impact

MODIFY irlmproc,SET,DEADLOCKnnnn
82
Availability Capacity

• Cancel thread nobackout
• NOBACKOUT was added to the -CANCEL THREAD
  command
• possibly leaves affected objects in an
  inconsistent state. These objects are also marked
  REFP,LPL.
• The same actions as with the recover postponed
  cancel command can be taken to resolve the
  REFP,LPL status.

83
Availability Capacity

• Summary
• Online DSNZPARM
• Consistent restart
• Log manager
• Statistics history
• Real time statistics
• Adding workfiles
• Online change of time out value
• Cancel thread nobackout

84
Quiz time

• What is the status of objects impacted by a
  RECOVER POSTPONED CANCEL command
• Refresh pending ( REFP)

85
DB2 V7 Performance Topics
Utilities
86
Utilities

• Dynamic utility jobs
• LISTDEF - run utilities against a pattern of
  DB2 objects
• TEMPLATE - dynamically allocate data sets
• OPTIONS - validate and control LISTDEF and
  TEMPLATE
• RESTART - LIST and TEMPLATE info are
  checkpointed

87
Utilities
88
Utilities
89
Utilities
90
Utilities

• Dynamic utility jobs

DSNU105I DB2A DSNUGDIS - USERID PAOLOR5 MEMBER
UTILID DSNTEX PROCESSING UTILITY STATEMENT
1 UTILITY RUNSTATS PHASE UTILINIT COUNT
0 NUMBER OF OBJECTS IN LIST 40 LAST OBJECT
STARTED 11 STATUS ACTIVE DSN9022I DB2A
DSNUGCCC '-DIS UTIL' NORMAL COMPLETION
91
Utilities

• Online Load Resume
• The new online LOAD RESUME, will allow loading of
  data concurrently with user transactions with
  minimal impact by introducing a new LOAD option
  SHRLEVEL NONE or SHRLEVEL CHANGE.
• SHRLEVEL NONE
• specifies that LOAD operate as in previous
  releases with no user access to the data during
  the LOAD.
• SHRLEVEL CHANGE
• allows users to have read and write access to the
  data during LOAD
• is valid only in conjunction with LOAD RESUME YES
  and will functionally operate like SQL inserts

92
Utilities
Insert of 2000000 rows 37 CPU reduction
93
Utilities

• Online Load Resume
• CPU reduction by avoiding the insert api overhead
• Classic load resume loads rows at the end
• Online load resume insert rows in free space as
  close to the clustering order as possible
• Lock contention is avoided
• DB2 manages the commit scope, DB2 will
  dynamically monitoring the current locking
  situation.

94
Utilities

• Load partition parallelism
• Prior to DB2 V7, when PIB is activated via
  SORTKEYS, one single RELOAD task pipes the
  key/RID pairs to multiple SORT/BUILD subtasks.
• With DB2 V7, when partition parallel Load is also
  activated, DB2 supports that multiple RELOAD
  tasks, roughly dependent on the number of
  partitions, can pipe their key/RID pairs to the
  SORT/BUILD subtasks (one per index).

95
Utilities
96
Utilities

• Load partition parallelism

97
Utilities

• Online reorg enhancements
• FASTSWITCH
• In the UTILINIT phase, DB2 creates shadow data
  sets. The fifth qualifier of these data sets is
  now J0001.
• In the SWITCH phase, DB2 updates the catalog and
  the object descriptor (OBD) from I to J to
  indicate that the shadow object has become the
  active or valid data base object.
• After the SWITCH phase, the applications can
  resume their processing, now on the new J0001
  data sets.
• In the UTILTERM phase, DB2 deletes the obsolete
  original data sets with the instance node I0001.

98
Utilities
- 0.7 to 2 seconds in V6 switch time - 0.05 to
0.2 seconds with V7 fast switch
99
Utilities

• Online reorg enhancements
• BUILD2
• REORG against one partition or a range of
  partitions
• In the BUILD2 phase NPIs are updated by parallel
  subtasks.

- 26 columns with a total record length of 119
bytes. 20 millions rows, 20 partitions, each
partition has 1 million row. 1 partition index,
5 NPI
100
Utilities

• Online reorg enhancements
• DRAIN_WAIT
• Specifies the maximum number of seconds that the
  utility will wait when draining.
• RETRY
• Specifies the maximum number of times that the
  drain will be attempted.
• RETRY_DELAY
• Works in conjunction with RETRY and specifies the
  minimum duration in seconds between retries.

101
Utilities

• COPYTOCOPY
• creates an image copy from an image copy
• leaves target object ( TS or IX) n R/W
• registers image copy in SYSCOPY
• The SYSCOPY columns, ICDATE, ICTIME, START_RBA
  will be those of original entries in SYSCOPY row
• DSNAME will be the dsname of the COPYTOCOPY job.
• The COPYTOCOPY utility does not support the
  following catalog and directory objects
• DSNDB01.SYSUTILX, and its indexes
• DSNDB01.DBD01, and its indexes
• DSNDB06.SYSCOPY, and its indexes

102
Utilities

• UNLOAD
• UNLOAD utility is faster than both, the DSNTIAUL
  sample program and REORG UNLOAD EXTERNAL
• With the SHRLEVEL CHANGE option, unloading table
  spaces does not stop your SQL
• UNLOAD is easier to use than REORG UNLOAD
  EXTERNAL

103
Utilities

• UNLOAD
• Unload from a list of table spaces
• Unload specific tables
• Unload certain rows only
• Unload certain columns only
• Unloading from copy data sets
• table space must exist

104
Utilities
1 million row table with 50 columns, each
defined as CHAR(4) 1 million row table with 50
columns, each defined as DECIMAL 1 million row
table with 50 columns, each defined as INTEGER
105
Utilities

• Cross loader
• EXEC SQL in combination with LOAD utility. This
  enhancement is called Cross Loader. The output of
  any SQL SELECT statement can be loaded directly
  into a table on DB2 V7.

• EXEC SQL
• CREATE TABLE PAOLOR6.TEST_EMP LIKE DSN8710.EMP
• ENDEXEC
• EXEC SQL
• DECLARE X1 CURSOR FOR
• SELECT FROM DSN8710.EMP
• ENDEXEC
• LOAD DATA
• INCURSOR(X1)
• REPLACE
• INTO TABLE PAOLOR6.TEST_EMP

106
Utilities

• Summary
• Dynamic utility jobs
• Online load resume
• Load partition parallelism
• Online reorg enhancements
• Copytocopy
• Unload
• Cross loader

107
Quiz time

• What is the meaning of the SHRLEVEL option with
  the load utility ?
• SHRLEVEL NONE LOAD operates as before V7
  SHRLEVEL CHANGE online LOAD RESUME

108
DB2 V7 Performance Topics
Data Sharing
109
Data sharing

• CF Name class queues
• Name Class Queues allows the CFCC to organize the
  GBP directory entries into queues based on DBID,
  PSID and partition number.
• Group attach enhancements
• Bypass Group Attach processing on local connect
• Support for local connect using STARTECB
• Group Attach support for DL/I Batch
• CICS group attach
• IMS group attach

110
Data sharing

• Immediate write
• IMMEDWRI
• YES/PH1/NO
• IMMEDWRITE column in SYSPLAN and SYSPACKAGE
• IMMEDWRI installation/DSNZPARM parameter

111
Data sharing

• RESTART LIGHT
• The Light Restart brings DB2 up with a minimal
  memory footprint. Retained locks are freed as
  part of the forward recovery and backward
  recovery processes and once this is done DB2
  terminates. No new work is allowed. All retained
  locks are freed except for
• indoubt units of recovery.
• postponed abort units of recovery.

112
Data sharing

• RESTART LIGHT
• No EDM/RID pool, LOB manager, RDS or RLF
• Reduced number of service tasks
• Only primary buffer pools with
  VPSIZEmin(vpsize,2000)
• VDWQT 0.
• CASTOUT(NO) for shutdown
• PCYES for IRLM if autostarted by DB2

113
Data sharing

• Persistent Coupling Facility Structure sizes
• DB2 V7 uses the currently allocated size of the
  SCA, Lock and GBP structures
• When allocating a new coupling facility structure
  instance in response to a structure rebuild
• When allocating a secondary structure to support
  duplexing
• When allocating a new coupling facility
  structure, after the size was changed by a SETXCF
  START,ALTER command and the structure was
  subsequently deallocated
• DB2 now stores the currently allocated size of
  the SCA and GBP structures in the BSDS

114
Data sharing

• Notify incomplete URs during shutdown
• if indoubt or postponed abort units of recovery
  exists message DSNR046I will be issued during
  normal DB2 member shutdown
• Efficient message handling for CF/structure
  failure
• Reduced communication between MVS and DB2 during
  a CF/structure failure
• Auto Alter
• This new function of OS/390 V2 R10
• You can define a structure's minimum and maximum
  size and Auto Alter will expand or contract the
  structure within the specified boundaries.

115
Data sharing

• Purge retained locks
• MODIFY irlmproc,PURGE
• releases IRLM locks retained due to a DB2, IRLM,
  or system failure.
• CF lock structure duplexing
• IRLM lock structure duplexing if in the CF policy

116
Data sharing

• Summary
• Coupling facility name class queues
• Group attach enhancements
• IMMEDWRITE
• RESTART LIGHT
• Persistent coupling facility structure sizes

117
Quiz time

• Whats the size of the bufferpools when
  restarting with the LIGHT option ?
• Only primary buffer pools with
  VPSIZEmin(vpsize,2000)

118
DB2 V7 Performance Topics
External Enhancements
119
External enhancements

• Z/Series
• 64 GB central storage
• Buffer pools in data spaces
• EDM pool global dynamic statement cache
• Larger number of faster processors
• higher degrees of parallelism
• Compression
• better hardware compression performance, allowing
  3 to 4 times fewer cycles than the G6 servers.
• VSAM striping
• PAV Multiple allegiance

120
External enhancements
Query scanned 45 million rows from 1,5 million
table space pages
121
External enhancements
7 million rows representing 1GB data were
updated hiperpool and data space buffer pool
sized to hold the 1GB data
122
External enhancements
160 million rows scan from 4.6 million
getpages Compression ratio of 21 Elapsed time
and CPU reduction by 60 from G6 to z/900.
123
External enhancements
4.4 million rows insert compression ratio of
55 40 cpu reduction, elapsed time reduction of
30
124
External enhancements
125
External enhancements

• VSAM striping
• A data set is splitted into stripes and spread
  across multiple volumes.
• DFSMS in OS/390 V2R10
• hardware striping facilitates parallel access to
  multiple physical disks in the same disk array
• DFSMS striping or software striping enables
  parallel access to multiple channels, to multiple
  disk arrays, and to multiple storage controllers.

126
External enhancements

• VSAM striping candidate data sets
• DB2 active log data sets
• non-partitioned table spaces
• non-partitioning indexes
• workfile table spaces
• Recommendation
• The usage of VSAM striping for the DB2 active
  logs is tested, verified and approved
• VSAM striping for DB2 user data is still under
  investigation wait for the results and
  recommendations from the DB2 labs before using
  VSAM striping for DB2 data.

127
External enhancements

• VSAM striping - DB2 active log

128
External enhancements

• Parallel access volumes multiple allegiance
• up to 256 UCBs for the same logical volume
• static PAV
• The association between the base UCB and the
  aliases is predefined and fixed.
• Static PAV was introduced in OS/390 V2R3 and
  DFSMS 1.3
• Dynamic PAV
• The association between the base UCB and the
  aliases is predefined but can be reassigned.
• The reassignment of the alias addresses is
  governed by WLM and based on the concurrent I/O
  activity for a volume.
• Dynamic PAV was introduced in OS/390 V2R7 and
  DFSMS 1.5
• The effect of PAV on I/O response time is that
  IOSQ time almost disappears

129
External enhancements
130
External enhancements

• PAV

131
External enhancements

• VSAM striping vs. PAV
• VSAM I/O striping alleviates contention on the
  data set level while PAV alleviates contention
  on the volume level whether the I/O is done
  against the same or different data sets. The DB2
  optimizer is not aware of VSAM I/O striping nor
  of PAV. The access path selection will not take
  any of these features into account.

132
External enhancements

• Summary
• z/Series
• VSAM data striping
• PAV, Multiple Allegiance

133
References

• Redbooks
• DB2 for z/OS and OS/390 Version 7Performance
  Topics, SG24-6129
• DB2 UDB Server for OS/390 and z/OS
• Version 7 Presentation Guide, SG24-6121
• DB2 for z/OS and OS/390 Version 7 Using the
  Utilities Suite, SG24-6289

134
Jan Vandensande Jeeves Consulting
N.V. Mechelsesteenweg 542 1800 Vilvoorde e-mail
janv_at_jeeves.be