11 Tips for DB2 11 for z/OS
Cüneyt GöksuIBM
Session Code: XXX
Agenda
•
Global Variables
•
ALTER Partition Key Limits Online
•
Select from Directory
•
Drop Column
•
Auto Mapping Tables
•
Transparent Archiving
•
Runstats Enhancements
•
Recovery Support for Deferred schema changes
•
Pseudo deleted index key cleanup
•
LOAD SHRLEVEL CHANGE with PARALLEL option
•
New zParms
Global Variables
Long expected DB2 for z/OS feature
• Enable the sharing of data between SQL statements w/o the need for application logic.
• Maintained by DB2, available throughout the entire application scope.
• Have access controlled by GRANT and REVOKE statements.
• New CREATE VARIABLE statement, saved in DB2 catalog
• Associated with a specific application, value unique to the application
• The content is shared among the SQL statements within the same connection,
• Similar to DB2 special registers
• Initiated upon the first reference.
• If created with the DEFAULT clause, the default expression is evaluated during first access
• If no DEFAULT is specified, NULL is used as the default value
• Can appear in expression, predicates, and select list.
ALTER Partition Key Limits Online
REORG TABLESPACE REBALANCE or ALTER TABLE <limit keys>
• Online alter limit key = In Version 11, Change limit keys of a partitioned table space without impacting the availability of the data.
In previous versions of DB2, when limit key values are changed • Affected partitions are set to REORP.
• These partitions could not be accessed until reorg. In Version 11, when limit key values are changed
• Data remains available, applications can continue to access the data.
• The limit key changes are not materialized until the next REORG & apps keep on working... • The affected partitions are placed in (AREOR) status.
ALTER Partition Key Limits Online
ALTER LIMIT KEY IN DB2 11 – How does it work • Alter limit key is a pending alter in NFM. • The affected partitions are set to AREOR.
• Online REORG (REFERENCE or CHANGE) must be run to materialize the pending changes. • REORG SHRLEVEL NONE does not materialize the changes.
• UTS or table controlled partitioning is a prerequisite for this feature.
• The new limit keys are materialized in SYSTABLEPART in the SWITCH phase (new message DSNU2916I)
• If the table space is in a MQT relation, it is still possible to alter limit key online.
• RECOVER PIT is allowed, requires a subsequent REORG due to setting of REORP after the recovery. This is possible but needs attention because it is restrictive!...
ALTER TABLE <limit key> ; What if DBA channges the idea!...
ALTER TABLESPACE .... DROP PENDING CHANGES / REORG ... REBALANCE
---APAR PM89655 adds
Select from Directory
Historically, those tables were not accessible through SQL (SELECT ONLY)
V10 : SYSIBM.SYSLGRNX, SYSIBM.SYSUTIL, SYSIBM.SYSUTILX, SYSIBM.DBDR, SYSIBM.SPTR
V11 : SYSIBM.SCTR
• SYSIBM.DBDR: one row for each DBD section.
• SYSIBM.SCTR: Skeleton Cursor Tables (SKCT) information
• SYSIBM.SPTR: Skeleton Package Table (SKPT) information
• SYSIBM.SYSLGRNX: recovery log ranges that record the time an indexspace defined with
COPY YES or a table space was open for updates.
Select from Directory
• Some of the data in those tables are still internal
• Combining them to existing catalog tables, provides more information.
---+---+---+---+---SELECT NAME,COUNT(*) AS NUMBER_OF_SECTIONS FROM SYSIBM.DBDR A, SYSIBM.SYSDATABASE B WHERE A.DBID = B.DBID
GROUP BY NAME
ORDER BY NUMBER_OF_SECTIONS DESC;
Drop Column (R)evalution
•
Add column implemented in the very early versions. (V1)
•
Altering Column data type, renaming column name came up with V8 & V9.
•
Now we have Drop Column functionality in V11
WHY DO YOU NEED TO DROP COLUMN?
• Columns become obsolete as applications change.
• Leaving a column has cost, Space in the table and in every Image Copy.
• Potential space in the log records
Drop Column
HOW DO YOU DROP A COLUMN BEFORE V11
Very preliminary procedure to do that task, which is very sensitive and open to
human errors.
• Schedule an outage
• Unload Data
• Drop Table
• Alter DDL
• Create Table
• Load Data
Drop Column
UNDO Drop Column? - After Materializition, NO! - Before Materilaztion, YES ALTER TABLESPACE DROP PENDING CHANGESSuch as:
• The containing table space is NOT a Universal Table Space
• The table is a GTT, a system-period temporal table, a history table, MQT,...
• There are row permissions or column masks dependent on the table
Auto Mapping Tables
Current Issues
- Each Reorg Uses its own mapping table, can not be shared by other concurrent.
- Manual operation (During REORG and/or DB2 Migration)
- Scailibility Constraint (64 GB)
Auto Mapping Tables
Reorg Decision Process:
1. If mapping table specified & correct format then honour specification
2.
Else if specified but incorrect format then create new in same DB as original
2.1 MAPPINGDATABASE overrides ZPARM / implicit database if specified
3. Else if not specified and ZPARM REORG_MAPPING_DATABASE specified then
create in ZPARM DB
4. Else create in implicit DB
Auto Mapping Tables
REORG TABLESPACE GOLD123.TS1
SHRLEVEL CHANGE
COPYDDN(SCOPY)
MAXRO 240 LONGLOG CONTINUE DRAIN ALL DELAY 900
STATISTICS TABLE INDEX
PREFORMAT
FASTSWITCH YES
DSNU2902I -GTBG 170 06:19:36.77 DSNURMAP - MAPPING DATABASE
REORGMD
IS
INVALID
Transparent Archiving
•
DB2 11 Transparent Archiving is built on the (bi) temporal support.
•
Not a complete Archive Solution!
•
3 pieces : a table, the archive table and associate
•
What DB2 does : Move data from table to archive table & decides access between
tables
•
Global Variables
SYSIBMADM.MOVE_TO_ARCHIVE ( Y/ N / E )
Y : delete of a row in an archive-enabled table will result in storing a copy of the
row in the associated archive table.
SYSIBMADM.GET_ARCHIVE ( Y/ N )
Y : when a table-reference is an archive-enabled table, the table reference
includes rows in the associated archive table.
•
ARCHIVESENSITIVE(YES) Bind option
Transparent Archiving
Setup:
CREATE TABLE T1
(C1 SMALLINT, C2 INTEGER) ; CREATE TABLE T1_ARC LIKE T1;
ALTER TABLE T1 ENABLE ARCHIVE USE T1_ARC;
SELECT * FROM T1; ---+---+---C1 C2 ---+---+---1 111 5 222
SET SYSIBMADM.MOVE_TO_ARCHIVE = 'Y'; DELETE FROM T1 WHERE C1=1;
SELECT * FROM T1; SELECT * FROM T1_ARC;
SELECT * FROM T1;
---+---+---C1 C2
---+---+---5 222
SELECT * FROM T1_ARC;
---+---+---C1 C2
---+---+---1 111
SET SYSIBMADM.GET_ARCHIVE = 'Y'; SELECT * FROM T1 ;
SELECT * FROM T1_ARC;
SELECT * FROM T1; ---+---+---C1 C2 ---+---+---1 111 5 222
SELECT * FROM T1_ARC;
---+---+---C1 C2
Transparent Archiving
ALTER TABLET1ADD COLUMN NEW_COL SMALLINT; -- NEW_COL is added toT1_ARCas well...
The INSERT, UPDATE, and MERGE statements to archive enable table are
- all blocked in archive mode if SYSIBMADM.MOVE_TO_ARCHIVE = ‘Y’
- not blocked and business as usual if SYSIBMADM.MOVE_TO_ARCHIVE = ‘N’
- not blocked and archive works as usual if SYSIBMADM.MOVE_TO_ARCHIVE = ‘E’
How to disable ARCHIVEing?
ALTER TABLE ... DISABLE ARCHIVE
-
the packages and statements in DSC that reference archive table are invalidated.
Runstats Enhancements
Runstats is generally good for Access Paths.
It was costly before V10 for Distributes Stats
V10 Distribution Stats are zIIP Eligable - %99 with no additional parameters
V11 Inline Stats are zIIP Eligable - %30
Inline Stats even become more powerful:
Runstats Enhancements
- RESET ACCESSPATH does NOT reset the statistics currently in the _HIST tables for that object - HISTORY ACCESSPATH option, provides the possibility to write out to the _HIST tables
(SYSIBM.SYSTABLES_HIST for tables, SYSIBM.SYSINDEXES_HIST for indexes)
reset the existing statistics during a RUNSTATS utility
RUNSTATS TABLESPACE ... RESET ACCESSPATH
Access Path Stats are reset
RTS & Space Stats are NOT reset
Recovery Support for Deferred schema changes
- Deferred schema change / Online Schema Change allows to make schema changes at any time - - Defer the materialization of those changes until a REORG -
- V10 included a significant restriction relating to PIT recoveries. -
- Once the REORG had been run, it was not possible to perform a PIT recovery - - V11 NFM removes this restriction, allowing PIT recovery -
Such as
ALTER DSSIZE ALTER PAGESIZE ALTER SEGSIZE
ALTER MEMBER CLUSTER With restrictions... -
Recovery Support for Deferred schema changes
CREATE TABLE T1 (C1 SMALLINT) IN GOLD123.TS1; INSERT INTO T1 VALUES (4);
INSERT INTO T1 VALUES (6);
ALTER TABLE T1 ADD COLUMN C2 INTEGER ; INSERT INTO T1 VALUES (4,7);
INSERT INTO T1 VALUES (6,7);
Pseudo deleted index key cleanup
Definition
- When a data row is deleted, the index entry for the key to that row must be removed. - DB2 sets a bit in the index to mark the index entry as being pseudo-deleted
- Pseudo-deleted entries occupy space. The more you have, The more SQL performance gets weaker.
- Pseudo-empty index pages = pages that contain only pseudo-deleted index entries. Issues
- Performance Impact for maintaining for entries - More getpages
- Concurrency issues for INSERT, UPDATE and DELETE
Pseudo deleted index key cleanup
• DB2 autonomically deletes pseudo-empty index pages and pseudo deleted index entries by scheduling asynchronous service tasks.
• committed pseudo-deleted index entries!
• Service task overhead is not associated with any DELETE or UPDATE activity and have low CPU overhead.
• zIIP Eligible
• by Default in V11 CM
• Performed only on the indexes that have been opened for INSERT/DELETE/UPDATE • There can be large number of pseudo deleted entries, but if index is not opened for
INSERT/DELETE/UPDATE, the cleanup does not happen. • The cleanup rate depends
the rate that the pseudo deleted entries are generated, the number of threads allowed to run cleanup concurrently
the commit frequency of the unit of work which generates the pseudo deleted index entries. Control Options
Pseudo deleted index key cleanup
INDEX_CLEANUP_THREADS subsystem parameter
- #threads for the cleanup of pseudo deleted index entries.
- Between 0-128 & default 10
- 0 disables clean up process
- Checks RTS table (SYSIBM.SYSINDEXSPACESTATS(REORGPSEUDODELETES))
- Reduces the need for REORG INDEX
SYSIBM.SYSINDEXCLEANUP catalog table is checked 10 min intervals
- Process is enabled for ALL Indexes by default
- Specify time window for the process
- The catalog table includes
1- Name of databases and indexspace
2- Cleanup enabled or disabled
LOAD SHRLEVEL CHANGE with PARALLEL option
- LOAD SHRLEVEL CHANGE option higher CPU than SHRLEVEL NONE
- SHRLEVEL CHANGE stores the rows in cluster sequence (INSERTs...)
- Performance is crucial to space search algorithms & contention between parallel inserts.
- If the TS has enough free space, less time for searching for space and less contention.
- Parallelism provides more value for SHRLEVEL CHANGE
- Parallelism may significantly reduce the ET
- If there is contention, more significant increase in the CPU time and more CPU increase
LOAD DATA INDDN SYSREC RESUME YES PARALLEL SHRLEVEL CHANGE
New zParms in V11
LIKE_BLANK_INSIGNIFICANT = YES / NO
- all of the blanks at the end of a fixed-length string are ignored, DB2 strips trailing blanks from the data before performing the comparison.
- Needs rebind/prepare
SELECT c1 FROM T WHERE <Filter> C1 LIKE ’%123’; predicate will match the following fixed-length strings xyz123
xyz123b xyz123bb
C1 LIKE ’%123 ’;predicate will NOT match the following fixed-length strings
