Transcript DB2 Internals Changes that DBAs Should Know

DB2 Internals Changes that DBAs
Should Know
—
Jim Dee
BMC Software
November 4/ 2014
Acknowledgements / Disclaimers
IBM®, DB2®, z/OS® are registered service marks
and trademarks of International Business
Machines Corporation, in the United States
and/or other countries
The information contained in this presentation
has not been submitted to any formal review
and is distributed on an “As Is” basis. All
opinions, mistakes, etc. are my own.
3
Agenda
DB2 11 Changes to the DB2 Catalog/Directory
Expanded RBA/LRSN
– Overview
– Impact to page formats
– Modifications to logging
New function provided in DB2 11
4
DB2 11 CHANGES TO THE CATALOG
AND DIRECTORY
•
Comments on CATMAINT
•
New Catalog Tables in DB2 11
•
Changed Catalog Tables and Spaces in DB2 11
5
Comments on CATMAINT
Two CATMAINT jobs
Going into CM Mode CATMAINT
– My output was terse
– Seemed to add new DB2 11 objects – spaces,
tables, columns
ENFM CATMAINT
– My output was verbose
– Most changes were documented in the output
– Seemed to process changes to existing DB2
objectss
6
Versioned
Catalog
Tables
in
DB2
11
SYSTSDAT SYSDATATYPES
SYSTSDEP
SYSTSIPT
SYSTSIXS
SYSTSPEN
SYSTSPKC
SYSTSPKG
SYSTSPKS
SYSTSPLN
SYSTSQRP
SYSTSQRY
SYSTSTAB
SYSTSTPT
SYSDEPENDENCIES
SYSINDEXPART
SYSINDEXES - PI10808 / UI17720
SYSPENDINGDDL
SYSPACKCOPY
SYSPACKAGE
SYSPACKSTMT
SYSPLAN
SYSQUERYPLAN
SYSQUERY
SYSTABLES
SYSTABLEPART
7
New Catalog Tables in DB2 11
SYSIBM.SYSINDEXCLEANUP
– Used to control timing of the Index PseudoDelete clean up Daemon
– Talked about later in the presentation
SYSIBM.SYSQUERYPREDICATE
SYSIBM.SYSQUERYSEL
– Used in conjunction with SYSIBM.SYSQUERY
for extended optimization
8
New Catalog Tables in DB2 11
SYSIBM.SYSVARIABLES
– Contains one row for each created GLOBAL
VARIABLE
– SYSIBM.SYSVARIABLES_DESC (Aux Table for
SYSVARIABLES)
– SYSIBM.SYSVARIABLES_TEXT (ditto)
SYSIBM.SYSVARIABLEAUTH
– Has a row for each privilege for each authid on
Changed Tables/Tablespaces in DB2
9
11
Moved out of DSNDB06.SYSCOPY
– SYSIBM.SYSCOPY to DSNDB06.SYSTSCPY
– Remained EBCDIC
Moved out of DSNDB06.SYSRTSTS
– SYSIBM.SYSINDEXSPACESTATS to SYSTSISS
– SYSIBM.SYSTABLESPACESTATS to SYSTSTSS
Moved out of DSNDB06.SYSSTR
– SYSIBM.SYSCHECKS to SYSTSCKS
– SYSIBM.SYSCHECKS2 to SYSTSCHX
Changed Tables new columns of
note
10
SYSCOPY – MODECREATED (CHAR 2)
–
–
–
–
‘C
‘E
‘N
‘
‘
‘
‘
‘
– Conversion Mode or CM*
– Enabling Mode or ENFM*
– New Function Mode
– Before DB2 11
– Vendor Utilities that update SYSCOPY should
set this
– Seems to be cosmetic at this point
Changed Tables new columns of
note
11
SYSTABLEPART and SYSINDEXPART –
RBA_FORMAT
–
–
–
–
‘B’ – Basic
‘E’ – Extended
‘U’ – Undefined on DEFINE NO spaces
‘ ‘ – For migrated objects (basic or undefined)
SYSPENDINGDDL
– COLNAME and COLUMN_KEYWORD,
12
Changed columns of note
RBA/LRSN columns increased from 6 bytes to 10
bytes
–
–
–
–
–
–
–
–
SYSCHECKS
SYSCOPY
SYSINDEXDES
SYSINDEXSPACESTATS
SYSTABLEPART
SYSTABLES
SYSTABLESPACESTATS
These are changed during ENFM regardless the
13
EXPANDED RBA/LRSN
Overview
Impact to page formats
Modifications to logging
Expanded RBA/LRSN – History –
Early 1980’s
DB2 Version 1 – 6 byte RBA
– RBA – Relative Byte Address
– From the beginning of logging of this
subsystem
– Central to DB2 Recovery design
– Ever increasing
– Max x’FFFFFFFFFFFF’ – 281 Trillion bytes
We’ll never approach that, right?
14
Oops… we got there.
15
Machines got faster
Concurrent Threads on a single DB2 increased
The LRSN spin has been removed in many cases
– Introduced multiple records at the same LRSN
and increased density
More log records
– Mobile Phone and digital access to data
exploded – more transactions
– Diagnostic, Trace, and NO OP log records
Expanded RBA/LRSN – History –
1995
16
DB2 Version 4 – 6 byte LRSN
– LRSN – Log Record Sequence Number
– Based upon the z/OS Store Clock
• High order 6 bytes of the 8 byte Store Clock
– Ever increasing
– Central to DB2 Recovery Design in Data Sharing
• Used to synchronize log records across a group
– Low order bit ~ 16 microseconds
– RBAs in the CATALOG are now LRSNs if Data
Sharing
17
Oops… DELTA
Design of Data Sharing
– If Non-Data Sharing RBA > LRSN when convert
to Data Sharing
– The DELTA is the difference between the two
– DELTA is added to the z/OS Store Clock to
derive LRSN
– DELTA is recorded in the BSDS
Data Sharing Disablement
– Conditional Restart to x‘C4C9E2C1C2D3’
18
DB2 11 Expanded RBA/LRSN
Expanded RBA/LRSN OPTIONALLY available in
DB2 11
RBA increases from 6 to 10 bytes
– A FULLWORD added in front (left) of the
existing RBA
– Range increases from 2(48) to 2(80)
– From 281 Trillion Bytes 281,474,976,701,656
• Terabytes
19
DB2 11 Expanded RBA/LRSN
Will also increase LRSN from 6 to 10 bytes
– One byte added before existing LRSN
• My math – ~36,352 years.
• Take that September 2042!
– Existing 6 byte LRSN
– Three bytes precision added at the end
• Current LRSN has ~16 microsecond precision
(10-6)
• Current z/OS STCK has precision to ~244
picosecond (10-12)
20
LRSN / Extended LRSN / Timestamp
Comparison
LRSN
CC7F1D54A740
CC7F1D54A740
CC7F1D54A740
CC7F1D54A741
EXTENDED LRSN
00CC7F1D54A740000000
00CC7F1D54A740000001
00CC7F1D54A740000200
00CC7F1D54A741000000
TIMESTAMP
2014-01-01-00.00.00.000000000000000
2014-01-01-00.00.00.000000000000953
2014-01-01-00.00.00.000000000488281
2014-01-01-00.00.00.000016000000000
base for comparison
lowest precision supported by Extended LRSN
my CPU speed at the time I created this
presentation
– Other CPUs can be faster or slower
lowest precision supported by basic LRSN
Okay… How do I get to Expanded
RBA/LRSNs?
Must be in NFM
Job to convert each BSDS
– DSNJCNVT
– Can be done one member at a time in a data
sharing group
– Member must be down
DSNZPARM / DSN6SPRM
– OBJECT_CREATE_FORMAT = EXTENDED
– UTILITY_OBJECT_CONVERSION = EXTENDED
REORG, REBUILD, LOAD REPLACE
21
22
Order of conversion?
You have flexibility here
– You can do spaces and BSDS conversions in any
order
– Can have some members BSDS converted and
others not
The real trick is getting converted to EXPANDED
BEFORE you reach the basic limit on any
member of a group or on a non-data sharing
system.
23
Order of Conversion Resources
SAP Performance Report asserts 22% increase in
logging when converted to extended (report
URL in notes).
There is overhead related to normalization of
the 10 byte internal RBA/LRSN to fit on the 6
byte pages and write 6 byte log records (IDUG
DB2 11 for z/OS Technical White Paper – URL in
notes)
24
Order of Conversion Resources
Net of my opinion
If Non-Data Sharing and nearing end of log
(RBA), Converting BSDS first will shorten the
time to reach the soft and hard limits.
If Data Sharing and converting because of LRSN
Delta and you don’t have an RBA shortage
issue, Do BSDS’ first.
RBAs/LRSNs in Table and Index
Space Pages
25
PGHEAD/PGTAIL
– PGHEAD - First x’14’ bytes of the page
• Has PGLOGRBA – 6 byte RBA/LRSN of the last
time the page was updated
– PGTAIL – Last x’02’ bytes of the page
• Free RID pointer
• Has a parity bit which corresponds to a bit in
the PGHEAD
Change for EXPANDED RBA/LRSN Implies fewer
bytes on each page
Space map implications
Basic - PGTAIL
26
***
BEGINNING OF PAGE NUMBER 00000128
Page Header
|- Parity flag bit b’00010000’
| PGLOGRBA
0000 100007D4 D17DC300 00012800 00220FB4 00001401 0100C800 2E01C4C9
F0C5D7F0
...
Rid Map
0FC0 00000000 00000000 00000000 00000000 00000000 00000E24 001404C4
058C0654
PGTAIL
0FE0 03FC01A4 0EEC026C 033400DC 0B040C94 0D5C0BCC 071C0A3C 097408AC
07E400D5
PAGE: # 00000128 ----------------------------------------------------------------------------DATA PAGE: PGCOMB='10'X PGLOGRBA='0007D4D17DC3'X PGNUM='00000128'X PGFLAGS='00'X PGFREE=34
PGFREE='0022'X PGFREEP=4020 PGFREEP='0FB4'X PGHOLE1='0000'X PGMAXID='14'X PGNANCH=1
PGTAIL: PGIDFREE='00'X PGEND='N'
27
Extended - PGBIGTAIL
***
BEGINNING OF PAGE NUMBER 00000128
Page Header
|- Parity flag bit b’00010000’
| PGLOGRBA
0000 18000000 00000000 00012800 09E60FAA 00001A17
C4C4C2F6
...
0FA0 40404080 01D7D540 00CCD9C4 C1E2E9E2 40408000
0D340C62
0FC0 0B900ABE 09EC891A 877686A4 85D28500 842E8000
80E68014
PGBIGTAIL(unused)
0FE0 80008000 80008000 80008848 00000000 00000000
C6000052
DATA PAGE:
PGBIGTAIL:
0200D200 0007D9D4
Rid Map
80000000 0ED80E06
8000835C 828A81B8
PGBIGRBA
00CB5227 FDB88F7D
PGCOMB='18'X PGBIGRBA='00CB5227FDB88F7DC600'X PGNUM='00000128'X PGFLAGS='00'X
PGFREE=2534 PGFREE='09E6'X PGFREEP=4010 PGFREEP='0FAA'X PGHOLE1='0000'X
PGMAXID='1A'X PGNANCH=23
PGPRETAIL='000000000000000000CB5227FDB88F7DC600'X PGIDFREE='00'X PGEND='52'X
PGTAIL versus PGBIGTAIL
implications
28
18 Fewer bytes on every DB2 page for data
– 18 Byte bigtail (plus unused old pglogrba, but
those aren’t new bytes)
– Minor reformatting of every page in table and
index spaces
– Slightly smaller space per page for actual data
• Regular spaces, XML, LOBs, indexes, etc.
Fewer pages potentially covered by a space map
page
– More space map pages per partition
Logging implications in Extended
Format
29
16 fewer data bytes on every physical log page
– Seemed to be a wash between the additional
CI size versus Freespace
– Was about 1.3% to 1.5% combined of every
page
42 more bytes logged for EVERY record in the
LRH
6 more bytes logged for every DM log record
A Septillion
Bytes Check Point log
More bytes for Savepoint
and
records
30
NEW FUNCTION PROVIDED IN DB2 11
Performance
Availability
•
•
•
•
•
•
•
Exclude NULL key indexes
Buffer pool enhancements
Faster recovery of catalog and
directory objects
Automatic cleanup of pseudodelete
Free space management
enhancements
Online schema enhancements
Compression dictionary logging
Ease of Use
•
DSN1COPY validation
New application features
•
•
Autonomous stored procedures
Support for user-defined global
variables
31
EXCLUDE NULL KEY INDEXES
Basically, keys which are all NULL are not
physically included in the index
CREATE INDEX – EXCLUDE NULL KEYS
– All Columns must be nullable
– Cannot be specified on several INDEX types
• UNIQUE, BUSINESS_TIME WITHOUT OVERLAP,
XML, or key-expression
ALTER INDEX – ADD COLUMN – must be nullable
SYSIBM.SYSINDEXES.SPARSE = ‘X’
Update of Key Value behaves the same(ish)
32
Bufferpool Enhancements
New parameters to give further control over
bufferpool sizing and location
FRAMESIZE – to specify a pool is to use 4K, 1M,
or 2G frames
VPSIZEMIN/VPSIZEMAX – to specify minimum
and maximum number of buffers for a given
pool with AUTOSIZE(YES).
Maximum of 1TB of buffers across all pools
33
Faster Recovery of Catalog and Directory
Objects
Directory Object updates are now recorded in
SYSLGRNX
– SYSCOPY events also recorded
Started in DB2 10 with PTFs (PM35190,
PM55333, more)
– CATMAINT UPDATE UNLDDN apar number
– Was a subset of the directory
– Subsequent APARs were to ensure catalog
row(s) consistency with object definition
34
Automatic cleanup of pseudo-deleted
index entries
Pseudo deleted index entries caused
performance to degrade and more frequent
REORGs to clean up
Daemon now running in background
– Controllable via SYSIBM.SYSINDEXCLEANUP
• By default, Daemon is running for all indexes
• Can limit times when Daemon processes index
spaces by adding/modifying rows
• DBNAME, INDEXSPACE,ENABLE_DISABLE,
MONTH_WEEK, DAY, START_TIME, END_TIME
• SHOULD BE USED AS AN EXCEPTION BASIS
35
Automatic cleanup of pseudo-deleted
index entries
IFCID 377 introduced to monitor index daemon
activity
– Includes DBID, PSID and partition number of
the index and page number being cleaned
– Indicators/Fields
• If full page is empty, indicates age deleted from
index tree
• Otherwise field to show number of pseudo
deleted entries
• One record written per index page cleaned
• Not included in any trace class since its volume
FREE SPACE Management
Enhancements
36
PCTFREE FOR UPDATE
– Added to CREATE and ALTER TABLESPACE
– The historic PCTFREE and PCTFREE FOR
UPDATE can not add up to be more than 99%
AUTO (-1) or 0 to 99
– AUTO uses RTS and starts out as 5% of
PCTFREE if not specified
DSNZPARM – DSN6SPRM.PCTFREE_UPD
37
Online Schema Enhancements
DROP COLUMN
– Implemented as a PENDINGDDL change versus
versioned row like ALTERs of existing columns
or ALTER ADD COLUMN
– You cannot RECOVER to a PIT prior to the
instantiating REORG at this point.
ONLINE ALTER LIMIT KEY
– Also a new PENDINGDDL capability
38
Online Schema Enhancements
Allowed to PIT across instantiating REORG
– If REORG done in DB2 11 NFM
– Did not change Tablespace Type or Hash
Organization
Cannot PIT through a DROP COLUMN
instantiation
Rows inserted into SYSIBM.SYSPENDINGDDL to
39
Compression Dictionary Logging
Actually, OLD Compression Dictionary Logging
Targeted to IFI based log processors
LOAD or REORG that rebuilds dictionary will log
prior dictionary
New SYSCOPY row records where old dictionary
logged
Validation after DSN1COPY style
data migration
40
Mismatches detected during first physical open
DBID, PSID, OBID
SEGSIZE, PAGESIZE
Tablespace Type Definition
–
–
–
–
–
Segmented
Classic Partitioned
PBR or PBG
Member Cluster
XML Version
Validation after DSN1COPY style
data migration
41
REPAIR CATALOG utility can correct the
following
Record Format (BRF / RRF)
RBA Format (Basic / Extended)
Data Version Number (same function as REPAIR
VERSIONS)
Hash Space (or not)
42
Autonomous STORED PROCEDUREs
COMMIT scope of the stored procedure can be
independent of the invoking Unit of Recovery
Available for Native SQL Procedures
CREATE and ALTER PROCEDURE (native SQL)
– AUTONOMOUS added as option instead of
• COMMIT ON RETURN NO / YES
Stored procedure can now commit and invoking
URID roll back or vice versa
Really did not impact log processing tools
Support for User Defined GLOBAL
VARIABLEs
43
CREATE VARIABLE DDL
– Rows stored in SYSIBM.SYSVARIABLES (and aux
LOBs)
– SYSIBM.SYSVARIABLESAUTH has GRANTed
privleges
Two part name – schema.variable name
– Defaults to CURRENT_SCHEMA if not qualified
Jim Dee
BMC Software
[email protected]
DB2 11 Internals Changes that DBAs
should know
BMC Software GSE Presentations
Tuesday 4th November
12:00 – 13:00
DB2
Internals: Changes that a system DBA must know – Jim Dee
Wednesday 5th November
10:45 – 11:45
DB2
How to save REAL money with SQL tuning - Phil Grainger
14:00 – 14:45
DB2
Explain yourself & improve DB2 performance - Jim Dee
12:00 – 13:00
Large Systems
zSeries scalability & availability improvements - Don Zeunert
15:15 – 16:00
zCMPA
WLM – Everything is fine isn’t it? - Don Zeunert