db2 11 for z/os: hands-on experiences (redbook authors)

DB2 11 for z/OS: Hands-on experiences (redbook authors)

Cristian Molaro MConsulting Belgium

Session Code: B12 Wednesday, 16 October 2013 14:30 - 15:30 | Platform: DB2 for z/OS

Agenda

WHAT IS NEW?

WHAT DB2 11 LOOKS LIKE?

SECURITY ENHANCEMENTS

PERFORMANCE EXPECTATIONS

PLAN MANAGEMENT

SYSTEM TOPICS

CLOSING

[email protected] ©® 2013 2

About the speaker

Cristian Molaro, MConsulting Bvba, Belgium

– Independent DB2 specialist and an IBM Gold Consultant

– Recognized by IBM as an Information Champion in 2009, 2010,

2011, 2012 and 2013

– Recognized by IBM as "TOP" EMEA Consultant at IDUG EMEA

DB2 Tech Conference Prague 2011

– Co-author of 8 IBM Redbooks related to DB2, including the

recent "DB2 11 for z/OS Technical Overview". Holder of the merit

badge “Platinum IBM Redbook Author”

Reachable at

– [email protected]

– be.linkedin.com/in/cristianmolaro/

DISCLAIMER

PLEASE BE AWARE THAT THE ACTUAL PROGRAMMING TECHNIQUES,

ALGORITHMS AND ALL NUMERICAL PARAMETERS USED IN EXAMPLES

GIVEN IN THIS PRESENTATION ARE SUBJECT TO CHANGE AT SOME FUTURE

DATE EITHER BY A NEW VERSION OF DB2, A NEW RELEASE, A SMALL

PROGRAMMING ENHANCEMENT (SPE) OR A PROGRAMMING TEMPORARY

FIX (PTF).

THE INFORMATION CONTAINED IN THIS PRESENTATION HAS NOT BEEN

SUBMITTED TO ANY FORMAL REVIEW AND IS DISTRIBUTED ON AN “AS IS”

BASIS WITHOUT ANY WARRANTY EITHER EXPRESS OR IMPLIED. THE USE

OF THIS INFORMATION OR THE IMPLEMENTATION OF ANY OF THESE

TECHNIQUES IS A CUSTOMER RESPONSIBILITY AND DEPENDS ON THE

CUSTOMER’S ABILITY TO EVALUATE AND INTEGRATE THEM INTO THE

CUSTOMER’S OPERATIONAL ENVIRONMENT. WHILE EACH ITEM MAY HAVE

BEEN REVIEWED FOR ACCURACY IN A SPECIFIC SITUATION, THERE IS NO

GUARANTEE THAT THE SAME OR SIMILAR RESULTS WILL BE OBTAINED

ELSEWHERE. CUSTOMERS ATTEMPTING TO ADAPT THESE TECHNIQUES TO

THEIR OWN ENVIRONMENTS DO SO AT THEIR OWN RISK.

IMPORTANT: Information regarding potential future products is intended

for information only, and it should not be relied on in making a

purchasing decision.

[email protected] ©® 2013

What is new?


www.redbooks.ibm.com

DB2 11 announcement

letter

Possible contents at a glance

Part 1. Subsystem

– Chapter 1. DB2 11 for z/OS at a glance

– Chapter 2. Synergy with System z

– Chapter 3. Scalability

– Chapter 4. Availability

– Chapter 5. Data sharing

Part 2. Application functions

– Chapter 6. SQL

– Chapter 7. Application enablement

– Chapter 8. XML

– Chapter 9. Connectivity and administration routines

Part 3. Operations and performance

– Chapter 10. Security

– Chapter 11. Utilities

– Chapter 12. Installation and migration

– Chapter 13. Performance

Part 4. Appendixes

– Information about IFCID changes

– Summary of relevant maintenance

– Additional material


What is new?

Certification Test 000-312 - IBM DB2 11 DBA for z/OS

Top 11 DB2 11 highlights: my shopping list

1. Code stability

2. Performance improvements + CPU cost savings + more zIIP

3. Security features

– DB2/RACF authorization control enhancements

4. Application compatibility

5. A lot more functionality than just Expanded RBA/LRSN

6. Plan stability improvements

– No pre-V9 bound packages

7. Optimizer input to RUNSTATS

8. REORG avoidance: automatic cleanup of index psuedo deleted e.

9. BIND/REBIND, DDL break into persistent threads

– Compensate expansion of RELEASE(DEALLOCATE) usage

10. Cancel DDF Threads new FORCE option

11. More online schema changes

But there is a lot more value in DB2 11!

Top 11 DB2 11 highlights: my shopping list

1. Code stability

2. Performance improvements + CPU cost savings + more zIIP

3. Security features

– DB2/RACF authorization control enhancements

4. Application compatibility

5. A lot more functionality than just Expanded RBA/LRSN

6. Plan stability improvements

– No pre-V9 bound packages

7. Optimizer input to RUNSTATS

8. REORG avoidance: automatic cleanup of index psuedo deleted e.

9. BIND/REBIND, DDL break into persistent threads

– Compensate expansion of RELEASE(DEALLOCATE) usage

10. Cancel DDF Threads new FORCE option

11. More online schema changes

DB2 security in RACF: Special considerations

There are differences between DB2 and RACF security

BINDAGENT privilege is not supported

AUTOBIND needs all privileges -904 that disguises -551

– AUTOBIND processing is performed under SYSOPR authority

– RACF checks the invoker, not the owner

No invalidation when authorization is revoked

PUBLIC AT ALL LOCATIONS is not supported

In some cases DB2 cannot determine a valid RACF USERID to be

used for authorization checking in DSNX@XAC, for example:

– IMS transactions with dynamic SQL

– CICS transactions with SEC=NO

– DB2 commands passed from z/OS master console

IMPORTANT: you MUST understand how DB2 and RACF work together


DYNAMICRULES and DB2 Security in RACF

DB2 DYNAMICRULES BIND behaviour is not supported when

using RACF exit AND CACHEDYN=YES

– Because RACF does not support secondary IDs in DB2 10

– Invoker must be authorised to all tables accessed by program

Options today

– You could use DB2 Roles to exploit this flexibility

• DB2 Roles are DB2 objects

– Keep security native in DB2

– Disable (?) DYNAMIC STMT CACHE

This alone can be a NO-GO for migrating

the DB2 security to RACF!


DB2 11 and Security in RACF Enhancements

AUTOBIND, BIND, REBIND present PKG-owner ACEE to RACF

– ACEE: Accessor Environment Element

Dynamic SQL authorisation checking:

– When DYNAMICRULES not equal to RUN

• DB2 presents AUTHID to RACF

– DYNAMICRULES defines whether AUTHID is

• PKG owner

• ID that defined the routine

• ID that invokes the routine

New zparm AUTHEXIT_CHECK

– Specifies whether the owner or the primary authorization ID is

used for authorization checks whith DB2 security in RACF

– PRIMARY: DB2 provides the ACEE of the primary auth. ID

– DB2: ACEE of package owner, DB2 honors DYNAMICRULES


DB2 11 and Security in RACF Enhancements

Invalidate cached authorization info when RACF changes are made

Also invalidate static SQL packages

New zparm AUTHEXIT_CACHEREFRESH

– Controls refresh of cache entries of:

• Package authorization

• Routine authorization

• Dynamic statement

• Invalidates dependent package

– NONE: DB2 does not refresh the cache

– ALL: DB2 refreshes all caches

ENF: RACF Event Notifications

TIP:


DB2 11: Sync RACF Changes to DB2 Cache

RACF DSNX@XAC

Exit

RACLIST

Classes

PKG Auth Cache ENF 62

ENF 79

ENF 71 Routine Auth Cache

DYN STMT Cache

PERMIT DB2P.CRIS00.%.ALTER CLASS(MDSNTB) RESET

DB2

10

DB2

11

Almost instantaneous

authorisation updates

RACF DSNX@XAC

Exit

RACLIST

Classes

PKG Auth Cache

Routine Auth Cache

DYN STMT Cache

PERMIT DB2P.CRIS00.%.ALTER CLASS(MDSNTB) RESET


DB2 11 Column Masking Enhancements

Remove SQLCODE -20478 reason code 5

– If column mask contains a scalar fullselect or an aggregate

function, allow to apply the column mask under an aggregate

function

Remove SQLCODE -20478 reason codes 3,4,6

– If column mask contains non-grouping columns, allow to apply

the column mask when there is a GROUP BY clause

THE STATEMENT CANNOT BE PROCESSED BECAUSE COLUMN MASK mask-name (DEFINED FOR COLUMN column-name) EXISTS AND THE COLUMN MASK CANNOT BE APPLIED OR THE DEFINITION OF THE MASK CONFLICTS WITH THE REQUESTED STATEMENT. REASON CODE reason-code.

CREATE MASK M1 ON SYSADM.EMP1 FOR COLUMN SALARY RETURN CASE WHEN (SALARY < 30000) THEN CAST((SALARY+BONUS)/2 AS DEC(9,2)) ELSE CAST(50000 AS DEC(9,2)) END SELECT SALARY FROM EMP GROUP BY SALARY;

DB2 10: SQLCODE -20478 rc 4

DB2 11: Returns the result


DB2 11 Column Masking Enhancements

Correct implementation of aggregate function with DISTINCT

– DB2 10: with column access control users may receive

inconsistent result from

• SELECT DISTINCT

• Aggregate function with DISTINCT keyword when the

column mask is applied to a query

– In DB2 10, DISTINCT operates on the masked values and

aggregation on masked value

– In DB2 11, DISTINCT operates on unmasked value and

aggregation on masked value

DISTINCT Aggregation

DB2 10 On masked value On masked value

DB2 11 On unmasked value On masked value

SELECT COUNT(DISTINCT SALARY) FROM EMP1;


Performance expectations (MIGRATE + REBIND)

OLTP: 0% to 10% CPU reduction

– CM mode after REBIND

– Better for write intensive workload

– Better for statements processing large number of columns

– Better if accessing single or a few partitions out of >500

partitions and using REL(COMMMIT)

Data warehousing queries: 5% to 40% CPU reduction

– Higher improvement with access path improvements

– Better if the tables are compressed

– Better with table space scan

– Higher improvement if sort intensive workload

Update Intensive Batch: 5% to15% CPU reduction

– Better in data sharing especially NFM EXTENDED format

IMPORTANT: Additional CPU savings may been seen by taking advantage of

other DB2 11 capabilities 19

Performance expectations

REM

EM

BER

: it d

ep

en

ds!

0 5 10 15 20 25 30 35 40 45

Update intensive Batch

Query (Compr. Tables)

Query (Non-Compr. Tables)

Complex OLTP

Simple OLTP

%CPU savings

Expected CPU savings by workload type DB2 11 CM vs. DB2 10 NFM


-10

0

10

20

30

40

50

OLTP Query Insert Batch (AVG) Utility (AVG)

CP

U c

ha

ng

es %

Workload type

DB2 10 to 11 migration CPU reduction - IBM Early Look

-10

0

10

20

30

40

50

OLTP Query Insert Batch (AVG) Utility (AVG)

CP

U c

ha

ng

es %

Workload type

DB2 9 to 10 migration CPU reduction - IBM Benchmarks

Performance expectations

REM

EM

BER

: it d

ep

en

ds!


Decompression performance improvement

Decompression can be the single most expensive instruction

– Executed by SELECT, FETCH, UPDATE, DELETE,

UTILITIES

– Higher impact with simple queries scanning large number of

rows

New decompression routine

– Efficient processor cache utilization when handling dictionary

– Special optimization to speed up the decompression

– No change on compression ratio

– Only for data compression, not for index page compression

– Compatible with existing compression, no user action

necessary

IBM early prototype evaluation

– 8% to15% overall CPU reduction in multiple query workloads


What about zIIPs?

zIIPs: System z Integrated Information Processor

Why should you care?

Some DB2 10 enhancements in this area:

– Offload 100 % of prefetch and deferred write engines

– Offload 99 % of RUNSTATS CPU

DB2 11: even more more zIIP exploitation

– zIIP usage expanded. No new area been added

– Utility and system tasks


WARNING: Calling for help

If the zIIP processors are over-committed

– The zIIP processor can request help from the GP CPs

– This time will be recorded as IIPCP time in RMF or SMF30 rec.

Review IIPHONORPRIORITY settings

– WARNING: may cause increased wait for CPU

IMPORTANT: you can “FORCE” the zIIP eligible workload to be

executed on the zIIP engine. Same for zAAP on zIIP


IMPORTANT: with defaults, and with HIPERDISPATCH=YES, may wait for up

to 3.2 milliseconds before receiving help from standard processors

DB2 11 and Query Performance

Stage 1 predicates: Fast and low CPU

– Could be indexable

Stage 2 predicates: Slow and high CPU

– Can not be indexable

DB2 11 Query transformation improvements

– Promote some common STAGE 2 predicates

to indexable

– Improved indexability for OR COL IS NULL

– Enhanced pruning of "always true" and "always false" predicates

I/O

RDS

DM

BM

STG1

STG2

SELECT COL1 FROM CRIS.TBL1 WHERE COL1 = :H1 AND DATE(COL2) = :H2

YEAR(COLx) = ? DATE(COLy) = ? SUBSTR(COLz,1,x) = ? VALUE BETWEEN COLa AND COLb

TIP:


To REBIND or NOT to REBIND? That is the question..

The ubiquitous DB2 discussion…

IMPORTANT: IBM recommends REBIND at each new release

GETTING THE REBIND BENEFITS AT THE LOWER RISKS

Performance

Scalability

Access paths

…

Performance degradation

Concurrency issues

…

TIP: DYNAMIC SQL is out of SCOPE implicit REBIND at least at first

execution

26

DB2 PLAN MANAGEMENT: OVERVIEW

REBIND … PLANMGMT(BASIC)

REBIND … PLANMGMT(EXTENDED)

New Copy Current Copy Previous

Copy

New Copy Current Copy

Original Copy

Previous

Copy

If no Original

Copy available


DB2 PLAN MANAGEMENT: SWITCH

Performance degradation due to access path change:

SWITCH(ORIGINAL) vs. SWITCH(PREVIOUS)

END TIME PLAN AUTHID CORRID ELAPSED CPU # STMTS GETPAGE REASON ----------- -------- -------- -------- -------- -------- ------- ------- ------ 13:58:46 PDB2PLN1 CRIS CRISLRXX 7,254 ms 2,025 ms 19,211 13,497 OK

Current Copy

Original Copy

Previous

Copy

Current Copy

Original Copy

Previous

Copy

SWITCH(ORIGINAL) SWITCH(PREVIOUS)


APREUSE: "Access Path Reuse”

Allows a package to reuse access paths for static SQL

– APREUSE(NO) Default

– APREUSE(ERROR)

• Effectively operates at the package level

• RC=8

• Package processing ends

– APREUSE(WARN) NEW in DB2 11

• Effectively operates at the statement level

• Access paths kept on all statements that took the HINT

• Fresh access paths for statements on which the HINT failed

• All packages rebound successfully

Some limitations apply: Consider the feature as being using HINTS

– Will not always work: reported 1% to 5% “failure” ratio

– A single failed query will fail the whole package

IMPORTANT: Allows to obtain DB2 11 updated runtime structures without

changing access path. Needs Explain Data Block (EDB) 29

RELBOUND

K

L

M

To REBIND or NOT To REBIND? Case study

Case study: OLTP environment of a financial institution, DB2 9

– Use the DB2 Catalog to report package release bound status

PROBLEM: Often, users avoid or delay REBIND

SELECT RELBOUND, COUNT(*) FROM SYSIBM.SYSPACKAGE WHERE VALID <> 'N' AND OPERATIVE <> 'N' -- DB2 10: -- WHERE LASTUSED >= -- (CURRENT_DATE - 1 MONTH) GROUP BY RELBOUND WITH UR ;

SYSIBM.SYSPACKAGE

30 [email protected] ©® 2013

Effects of mass REBIND: Case study

CPU changes after mass REBIND > 2000 packages

2 important packages went wrong

– MIX access path creating RID failures at run time

Operations protected by PLAN STABILITY

AF

TE

R

31 [email protected] ©® 2013

Application SQL compatibility

New DB2 releases can introduce SQL behavior changes which can

break existing applications

– Like changes for SQL standards compliance

– DB2 10 CHAR function with decimal (BIF_COMPATIBILITY)

DB2 11 Application Compatibility addresses these challenges

BIND/REBIND options for Packages

– APPLCOMPAT(V10R1 / V11R1)

zParm for default BIND Option

S. R. for Dyn SQL: CURRENT APPLICATION COMPATIBILITY

Warnings provided when program uses incompatible SQL

IFCID 366 reports on Packages affected

IMPORTANT:


APPLCOMPAT

New column APPLCOMPAT

– SYSIBM.SYSPACKAGE and SYSIBM.SYSPACKCOPY

– V10R1 SQL statements have V10R1 compatibility behaviour

– V11R1 SQL statements have V11R1 compatibility behaviour

---------+---------+---------+---------+---------+---------+

SELECT

CAST(COLLID AS CHAR(10)) AS COLLID,

CAST(NAME AS CHAR(10)) AS NAME,

VALID, OPERATIVE, APPLCOMPAT

FROM SYSIBM.SYSPACKAGE

WHERE COLLID = 'DSNTIA11'

WITH UR;

---------+---------+---------+---------+---------+---------+

COLLID NAME VALID OPERATIVE APPLCOMPAT

---------+---------+---------+---------+---------+---------+

DSNTIA11 DSNTIAD Y Y V11R1

DSNE610I NUMBER OF ROWS DISPLAYED IS 1

DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100

---------+---------+---------+---------+---------+---------+


Break-in option on persistent threads

New break-in option on persistent RELEASE(DEALLOCATE) threads

– Allows otherwise failing BIND / DDL / ONLINE REORG

– During commit processing: persistent thread automatically

detects operations that would like to break-in

• If detected, then RELEASE(DEALLOCATE) will behave like

RELEASE(COMMIT)

– Requires zParm PKGREL_COMMIT=YES

• Default is YES

Release of resources after COMMIT/ROLLBACK only if other DB2

operations, like BIND / DDL / ONLINE REORG, are waiting for

exclusive control to the package

Packages resume normal RELEASE(DEALLOCATE) behavior after

the break-in operation completes

TIP: Idle threads are handled automatically


DB2 11 10 byte RBA and LRSN

Old 6 byte RBA and LRSN

– Since the initial version of DB2: 256 TB of log record capacity

– Some users exhaust log capacity regularly: disruption to fix

New 10 byte RBA and LRSN

– 123456789ABCDEF1 00000000123456789ABCDEF1(RBA)

– RBA addressing capacity of 1 yottabyte (2**80)

– LRSN extended on left by 1 byte, on the right by 3 bytes

– >30,000 years and 16Mx more precision

NFM only

– 6 byte RBA/LRSN continues to be used in CM

Prerequisites for extended RBA/LRSN format

– Convert BSDS : Stand-alone utility DSNJCNVT

– Convert pagesets to new page format

IMPORTANT: conversion 6 to 10 = overhead!


Global Variables

Named memory variables

Can be accessed and modified through SQL

Share relational data between SQL statements

– Without the need for application logic to maintain the data

CREATE VARIABLE

– Creates a new variable definition in the DB2 catalog

– Definition is shared by all applications

Variables instantiated and maintained at the application thread level

CREATE VARIABLE PAY INTEGER DEFAULT 0; SET PAY = (SELECT AVG(SALARY) FROM...) + (SELECT AVG(BONUS) FROM...); SELECT employee_name FROM...WHERE COMPENSATION > PAY;

IMPORTANT: The variable content is only shared among SQL statements within

the same connection


Autonomous Transactions

Autonomous Transaction is a native SQL Procedure that can

commit work OUTSIDE the commit scope of the calling program

Executes independently from the calling application

– Always commits its updates before returning to the caller

– But does not commit changes in the calling application

– Useful for event or audit logs

>------CREATE PROCEDURE---procedure-name-------------------------------------------> option-list: .---COMMIT ON RETURN NO---. >----------+-------------------------+---------------------------------------------> |---COMMIT ON RETURN YES--| '---AUTONOMOUS------------'

INSERT INTO T1; NOT COMMITTED CALL SP1 (autonomous); UPDATE T2; COMMITTED ROLLBACK;


SUMMARY


Agenda

WHAT IS NEW?

WHAT DB2 11 LOOKS LIKE?

SECURITY ENHANCEMENTS

PERFORMANCE EXPECTATIONS

PLAN MANAGEMENT

SYSTEM TOPICS

CLOSING


Cristian Molaro Mconsulting Bvba [email protected]

B12 DB2 11 for z/OS: Hands-on experiences (Redbook author)

db2 11 for z/os: hands-on experiences (redbook authors)

Documents