zsap db2 11 z13 hybrid sap apps chicago - ibm.com · and analytical sap applications johannes...

Chicago, May 19, 2015

DB2 11 and z13 for Hybrid Transactional and Analytical SAP applicationsJohannes Schuetzner [email protected]

Senior Technical Staff Member, IBM Systems

SAP on DB2 for z/OS Development

© 2015 IBM Corporation 2

Agenda

• Evolving SAP Workloads and SAP Core Data Services

• DB2 11 Optimizations for SAP Core Data Services

• SAP Rapid Replenishment Planning

• z13 SMT for SAP Analytical Workloads

• SAP Zero Downtime Option


Market Trends Foster Growing SAP Workloads

SAP

Database

Application Single global instance

Mobile accessAnywhere, Anytime

Need to scale and to avoid downtime


<code>

Data-To-Code:Intensive

computations in

APPLICATIONlayer

Code-To-Data:Intensive computations

in DATABASElayer

From Traditional Approach• Avoid costly computation on

database level• Transfer data to application server

and compute there

To Data Centric Approach• Get the algorithm to the data• Only transfer computation results to

the application server

A Paradigm Change in SAP Application Programming

ApplicationProgrammingModels

Database


SELECT id, name, deliveryAddress.zipCode

FROM Employee WHERE ...

SAP Core Data Services Motivation

What application developer intends to express:

Capturing intent requires:

� declarative languages (vs. imperative ones)� domain-specific languages� sufficiently rich level of semantics



SELECT c.id, c.name, a.zipCode FROM Customer c

LEFT OUTER JOIN Customer2Address c2a ON c2a.customer = c.id LEFT OUTER JOIN Address a ON c2a.address = a.id

AND a.type = ’deliveryAddr’WHERE ...

What application developer has to write in plain SQL:

… because of :lacking support for relationships as first-class citizen



customers = SELECT * FROM Customer

foreach c in customers do

write c.id

write c.nameaddresses = SELECT * FROM Address a, Customer2Address c2a

WHERE a.id = c2a.address AND c2a.customer = :c.id

foreach a in addresses do

if a.type = ‘deliveryAddr’ then write a.zipCode

end

end

What we frequently see as actual code:

… with detrimental effects on performance due to imperative programming!

Pulldatamodelingaswellasretrievalandprocessingofdatatoahighersemanticlevel:Close totheconceptualthinking ofdomain experts

Motivation for SAP Core Data Services


SAP Core Data ServicesOverview about Architecture & Setup

• ABAP in Eclipse with SAP plug-in• Connected to an SAP system• Changes end up in SAP Data Dictionary

• CDS DDL sources from ABAP containing CDS views are translated to DB2 views• Created like any other DDIC object• Additional features like SAP-supplied

scalar UDFs are created in DB2 during CDS initialization ABAP

HANA

CDS-DDL Backend

Eclipse

DB2 and others

DDL Query Lang CDS-DDL Backend

DDL Query Lang

RDL Backend

ABAP in Eclipse

CDS Editor(text &

graphical)

HANA Studio

CDS Editor(text &

graphical)


SAP Core Data Services Key Principles

• CDS is…– Domain-specific à DSLs, close to conceptual thinking, ER Modeling– Intentional à declarative, functional approach– Extensible à both on a model- (thru extensions) and meta

model level (thru annotations)– Reflexive à as SQL, extended to annotations

• …, while it is not(!):– Rocket science à SQL, ER Modeling, … as others

– Abstraction à it enhances SQL, not on top of SQL

– Does not change SQL INSERT / UPDATE / DELETE on tables


SAP Core Data ServicesData Definition Language

CDS DDL = SQL DDL + …• Entities with structured and custom-defined types • Associations i.e. captured relationships• Annotations to extend the meta models• Calculated Fields pre-defined in data models

type Amount { value : Decimal; currency : Association to Currency;

}

entity Address { streetAddress; zipCode; city; // snipped type defskind : enum { home, business };

}

entity Employee {addresses : Association[0..*] to Address;homeAddress = addresses[kind=home];salary : Amount;

}

Calculated Field

Association

Custom-defined Type

Entity

Structured Field


SAP Core Data ServicesIntrinsic View Building

• Views are projections on other entities– Follow concept of reflexive view definitions in standard SQL

define view EmployeesInOrg asSELECT from Employee {

ID, name,salary,orgunit {

name,manager,

},homeAddress

}

Query in CDS QL


SAP Core Data ServicesQuery Language (QL)

CDSQL=SQLSELECT+…

� Pathexpressionsalongassociations

� XPath-likefilterexpressionsinpaths

� Nestedprojectionclausesà structuredresultsets

…thusleveragingtherichsemanticsfromDDL.

SELECT id, name, deliveryAddress.zipCode FROM Customer WHERE ...

SELECT name, addresses[type=deliveryAddr].city AS deliveryAddress,addresses[type=invoiceAddr].city AS invoiceAddress FROM Customer …

SELECT id, name, deliveryAddress { streetAddress, zipCode, city

} FROM Customer WHERE ...

• DB2processesstandardSQLstatementsforCDSDDL+QL

SAP database interface maps to DB2 SQL


CDS View Example 1

DDL source incl. expression and annotation

DB2 DDL


CDS View Example 2

DDL source with CURRENCY CONVERSION

DB2 DDL

CREATE VIEW "ZZJCMTSTCDSV2“AS SELECT

"SFLIGHT"."MANDT" AS "MANDT",

"SFLIGHT"."CARRID",

"SFLIGHT"."CONNID",

"SFLIGHT"."PAYMENTSUM" AS "ORIGINALSUM",

"SFLIGHT"."CURRENCY" AS "ORIGINALCURRENCY",

CAST( ROUND( CAST( CURRENCY_CONVERSION( "SFLIGHT",

"SFLIGHT"."CURRENCY",

CAST( G'USD' AS VARGRAPHIC ( 000005) ),

CAST( G'20010101' AS VARGRAPHIC ( 000008) ),

G'M',

CAST( G'000' AS VARGRAPHIC ( 000003) ),

G'X',

G'X',

G'X',

G'X',

G' ',

G'FAIL_ON_ERROR' )

AS DECIMAL ( 000017, 000002) ),

CAST( 000002 AS BIGINT ) )

AS DECIMAL ( 000026, 000005) ) AS "CONVERTEDSUM",

G'USD' AS "TARGETCURRENCY“

FROM

"SFLIGHT" "SFLIGHT"

WHERE

"SFLIGHT"."CURRENCY" = G'EUR'


CDS View Example 3CREATE FUNCTION

"ZZJCMTSTCDSV3"

( "IV_CONNID" VARGRAPHIC (000004) )

RETURNS TABLE

(

"MANDT" VARGRAPHIC (000003),

"CARRID" VARGRAPHIC (000003),

"CONNID" VARGRAPHIC (000004),

"FLDATE" VARGRAPHIC (000008)

)

LANGUAGE SQL

SPECIFIC "ZZJCMTSTCDSV3"

READS SQL DATA NO EXTERNAL ACTION NOT DETERMINISTIC

PARAMETER CCSID UNICODE

RETURN

SELECT

"SFLIGHT"."MANDT" AS "MANDT",

"SFLIGHT"."CARRID",

"SFLIGHT"."CONNID",

"SFLIGHT"."FLDATE"

FROM "SFLIGHT" "SFLIGHT" WHERE

"SFLIGHT"."CONNID" = "ZZJCMTSTCDSV3"."IV_CONNID"

DB2 DDL

ABAP SQL Query

DDL source for view with parameter


CDS - Strategic SAP data modelling

• Available across SAP database platforms• Defines business objects as database views that contain SQL queries

• CDS not applicable to cluster- or pool-tables (i.e. RFBLG, ATAB)

• Eclipse as development tool for CDS (not SE80)• Application code easier to read with better performance

• CDS heavily utilizes database views• Hierarchical stack of views – up to 10 levels of views or more

– Views can use scalar and table functions of database• Application logic running within the database via scalar UDFs

– With DB2: SQLPL UDFs with static SQL – UDFs contain no SQL or very simple SQL lookup queries– CREATE FUNCTION automatically performs bind in DB2

• SQL statements get more complex - analytical queries • Less traffic between database server and application server


-Full 64-bit runtime support-Reducing internal latch -contention-Workfile spanned records, PBG support, and in-memory enhancements-Auto-stats-Default SAP settings for DB2-Access path stability and hints enhancements-Hash access path-Parallel index update at insert-Numerous optimizer enhancements-Enabling MEMBER CLUSTER for UTS-Query parallelism enhancements: lifting restrictions-More granular DBA privileges-More online schema changes for table spaces, tables and indexesvia online REORG-Automatically delete CF structures before/during first DB2 restart-Allow non-NULL default values for inline LOBs-Loading and unloading tables with LOBs-Full Decimal Floating Point support-‘Last committed’ locking semantics-Easier SQL paging through result sets-Online REORG for LOB-Online add log…..

DB210

-Optimistic locking-Modify early code without requiring an IPL-APPEND option for inserts-Relief for sequential key insert-LOB performance and scalability-Utilities CPU reduction-Faster restart of data sharing-CLONE Table: fast replacement of one table with another-Renaming column, index, and schema-Table space that can add partitions, as needed for growth-Improve ability to create an index online and rebuild it-Online reorganization with no BUILD2 phase-Parallel unload and reload during REORG-Automatic objects creation-64-bit exploitation by DDF-Autonomic re-optimization-Index compression-Database ROLEs-Trusted security context-Cross query block optimization…..

DB29

-- VS Constraints / - Unicode-- Automate BackupRecovery-- 64bit DB2 Connect for zLinux-- Multiple DISTINCT Clauses-- Lock Contention on SAP Cluster Tables-- Fast Retrieval of Most Recent Value-- Create Deferred Index Enhancement-- Provide DSTATS Functionality-- Convert Column Type-- Altering CLUSTER Option-- Adding Columns to Index-- Index-only Access Path for VARCHAR-- Changing Number of Partitions-- Partitioning Nonclustering Keys-- Control Center Enhancement-- DRDA Performance-- Longer Table Names-- Transparent ROWID-- Array Fetch, Insert

-…

DB2V8

50+featuresexplicitly

requested by SAP

40+ featuresfor SAP

40+ features for SAP

-Out-of-the-box CPU savings-Index-ability for mismatched data type/length-Transform OR predicates to IN-list also for "IS NULL" -Reduction of overflow records -Index skipping-Index look-aside for DPSIs-LIKE predicate enhancements for CHAR/GRAPHIC-Suppress NULL index entries-Real-time stats on demand-Enhance DSNACCOX -Inline stats support all Runstats options-REORG without reclustering (SORTDATA NO SHRLEVEL CHANGE)-New buffer pool statistics-Default profile for RUNSTATS USE PROFILE-Stored procedure ADMIN_COMMAND_MVS-IFCID 225: IRLM virtual storage monitoring-Enhanced workfile monitoring-Enhanced client info fields-DROP COLUMN-REORG gets drain on whole partititioned tablespace-Streamlined REORG switch phase-REORG can remove data sets of empty PBG partitions-Automatically cleanup pseudo-deleted index entries-Online ALTER REBALANCE-Online ALTER limitkey-…..

DB211

40+ features for SAP

DB2 for z/OS – Optimized for SAP: History of tight collaboration


SAP Core Data ServicesExploiting Sophisticated SQL FunctionalitySAP NetWeaver 7.40 SP5• Aggregate functions:

• MIN, MAX, AVG, SUM, COUNT • GROUP BY & HAVING • Literals in SELECT list & ON-condition• ON-condition with !=, <, >, ... • New capabilities via "built-in" annotations• Buffering

• End-user texts for elements• "Key"-elements in views • Reference fields for Amounts / Quantities

• "Simple" CASE (in SELECT list) • Arithmetic Operators: -, + and *• Number Functions: CEIL(), MOD()• Type Conversion: CAST() to DDIC types • String Functions: SUBSTRING(), LPAD()

SAP NetWeaver 7.40 SP8• Views with parameters

• Conversion Functions (Currency, Unit)

• Arithmetic Support: "Open Division" • For INT-, DEC- and FLTP-types

• Support of additional Functions • Number Functions: ROUND, FLOOR,

CEIL, ABS• String Functions: REPLACE,

CONCAT, COALESCE, LIKE

• Searched CASE

Fullysupported byDB210&11


DB2 11 Optimizations for SAP Core Data Services• CDS allows application developers to perform arithmetics in SELECT

statements• DECFLOAT type becomes more relevant – casting for better precision• DB2 11 optimizes DECFLOAT processing by

– Exploiting DECFLOAT hardware support of z Systems– Streamlining normalization of DECFLOAT values

• CDS allows applications to define and consume complex views, UDFs (user-defined functions) and table UDFs • DB2 11 introduces support for multiple SET control statements

– SET (X1, X2) = (SELECT C1, C2 FROM T1 WHERE ... )

• DB2 11 introduces global variables– Named memory variables that can be read and written to from within a DB2 thread

• DB2 10+ APAR PI12047 enables SELECT on table UDF without correlation name• DB2 10+ APAR PI29116 enables calling UDFs if DECFLOAT rounding mode does

not match


DB2 11 Optimizations for SAP Core Data Services• CDS can result in applications submitting more complex queries

• DB2 11 more efficiently exploits indexes– DB2 internally transforms predicates so that they become indexable, e.g.:

WHERE YEAR(DATE_COL) = 2012 à WHERE DATE_COL BETWEEN ‘2012-01-01’ AND ‘2012-12-31’

WHERE SUBSTR(CITY,1,3) <= ‘ROMA’à WHERE CITY <= (< DB2_derived_boundary>)

– DB2 internally transforms query for single matching index access, e.g.:WHERE C1 = ? OR C1 IS NULL à WHERE C1 IN (?, NULL)

WHERE C1 IN (1, 2) OR C1 IS NULL à WHERE C1 IN (1,2,NULL)

– DB2 can use index access for certain CASE expressions, e.g.:SELECT * FROM T1WHERE COL = CASE (CAST(? AS INT))

WHEN (1) THEN 'CA'WHEN (2) THEN 'NY'

ELSE 'AL' END;


DB2 11 Optimizations for SAP Core Data Services

• CDS can result in applications submitting more complex queries• DB2 11 allows pruning of partitions for query processing in joins

• DB2 11 enables more comprehensive push down of predicates to subqueries

• DB2 11 optimizes performance of SQL GROUP BY and DISTINCT clauses – Better sort avoidance, early out processing– Index skipping:

Use index lookaside (current leaf high key and non-leaf) to get the next key greater than current key


SAP Rapid Replenishment Planning• Example for SAP application exploiting CDS and code pushdown to database

• Part of SAP Retail industry solution

• Performance optimization of existing SAP ERP Retail Replenishment • ERP Retail Replenishment: Transaction WRP1 (ABAP report RWRPLPRO)• Rapid Replenishment Planning: New transaction WRP1R (new ABAP report RWRPLRRP)

• Parallel usage with classic replenishment planning possible• No migration efforts from classic replenishment planning (besides tests and job

definition) • Existing master data, transaction data and customizing is used without any changes

• CDS-based replacement of core functionality in existing replenishment process• Some simplifications of the planning process (e.g., consideration of MM forecast only)


SAP Rapid Replenishment Planning: Detailed comparison of sample run

OldSAPERPRetailReplenishment job:

System: QQ2 Instance: lsemt01_QQ2_00

Record: 10:08:23 - 10:13:05 RWRPLPRO

Analysis of time in work process

Total time in workprocs 282.197 ms

Wait for work process 1 ms

Processing time 110.713 ms

Load time 13 ms

Generating time 0 ms

Roll (in+wait) time 1 ms

Database request time 171.462 ms

Enqueue time 9 ms

Total DB2 CPU time: 28.400 ms

NewSAPRapidReplenishmentPlanning job:

System: QQ2 Instance: lsemt01_QQ2_00

Record: 09:44:17 - 09:45:44 RWRPLRRP

Analysis of time in work process

Total time in workprocs 87.233 ms

Wait for work process 1 ms

Processing time 7.818 ms

Load time 12 ms

Generating time 0 ms

Roll (in+wait) time 1 ms

Database request time 79.398 ms

Enqueue time 4 ms

Total DB2 CPU time: 62.400 ms


SAP Rapid Replenishment Planning• Rapid Replenishment Planning executes different types of batch jobs

• Measurements done on same hardware (CPU / memory)

• CDS-based optimization shipped as part of SP08 for ERP 6.0 EHP 7• See SAP Notes 2051280 and 2142546

UsecaseMeasurementsonDB2forz/OS

Runtimeclassicjob

RuntimeCDS-based job

Acceleration

1(sequential) 0.17sec 0.15sec 1.1x2(sequential) 0.39 sec 0.23sec 1.7x3(sequential) 14.26sec 4.60 sec 3.1x4(sequential) 300.45sec 85.84 sec 3.5x5(sequential) 417.22 sec 88.93 sec 4.7x6(parallel) 571.66 sec 121.63 sec 4.7x


z13 SMT for SAP Analytical Workloads

• SMT-2 enables second hardware thread per core• Can overlap each other for more effective pipeline use• Appears as another logical processor to the operating system

• Individual threads run slower but dispatcher delays are reduced• zIIP with SMT is about 1.4x of zIIP without SMT

WorkloadrunningSAPCDSanalyticalqueries

Configuration

#ofjobs

Elapsedtime

Externalthroughput rate

(per hour)

%CPutilization

%zIIPutilization

Internalthroughput

rate(perhour)

6CPs+2zIIPs 4 1491.7sec 2606.4 79.0% 48.0% 3659.5

6CPs+2zIIPswithSMT 4 1390.0sec 2797.1 77.7% 46.2% 4004.9

6CPs+3zIIPs 4 1388.5 sec 2800.2 77.8% 45.7% 4175.0

More capacity:9.5% total38% for zIIPs


Brid

ge

Sch

ema

Normalproductionn Norestrictions

CustomerSystem

Release n+m

Normalproductionn Norestrictions

Upgradeuptimen Systemused

productivelyn Restrictionsapply

BridgePhasen OnlineUserswitchto

differentScheman Tableseither

n Copiedn Clonedw/replayn Sharedw/viewsn Read-only

Release n

Release n

time

Release n + m

Shadowsystemcreationn Export/Cloned contentn AdaptRepository Tablesn ShadowImportn BridgeSetup

Shad

ow

Upgraden Application Importn Conversionsn AIMsandXPRAsn Customertransports

Release n + m

Releasen+m Test

Release n

Alias+View SmartSwitchUserSwitch

Shadow/UpgradeSystem

SmartSwitch

SAP Zero Downtime Option (ZDO) to enable SAP Online Lifecycle Management


DB2 11 Optimizations for SAP Zero Downtime Option

• To non-disruptively switch tables accessed by applications, SAP introduces Smart Switch concept

• Needs to rename tables under the covers• This needs to work while SQL DML statements access the table and also if views exist• DB2 11 APAR PI32291 enables smart invalidation of views for RENAME TABLE

statements

• SAP defines triggers requiring certain semantics• DB2 11 introduces autonomic stored procedure with own commit scope• CREATE PROCEDURE ... AUTONOMOUS• DB2 11 APAR PI36733 enables immediate DROP TRIGGER

• Support for DROP COLUMN• ALTER TABLE .... DROP COLUMN as pending DDL SAPZDObasedon

DB211


Thank you

Johannes [email protected]


TrademarksThe following are trademarks of the International Business Machines Corporation in the United States and/or other countries.BlueMixCICS*COGNOS*DB2*

MQSeries*OMEGAMON*Parallel Sysplex*RACF*

DS6000*DS8000*FICON*GDPS*

* Registered trademarks of IBM Corporation

Notes: Performance is in Internal Throughput Rate (ITR) ratio based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput that any user will experience will vary depending upon considerations such as the amount of multiprogramming in the user's job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve throughput improvements equivalent to the performance ratios stated here. IBM hardware products are manufactured from new parts, or new and serviceable used parts. Regardless, our warranty terms apply.All customer examples cited or described in this presentation are presented as illustrations of the manner in which some customers have used IBM products and the results they may have achieved. Actual environmental costs and performance characteristics will vary depending on individual customer configurations and conditions.All statements regarding IBM's future direction and intent are subject to change or withdrawal without notice, and represent goals and objectives only.Information about non-IBM products is obtained from the manufacturers of those products or their published announcements. IBM has not tested those products and cannot confirm the performance, compatibility, or any other claims related to non-IBM products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.

These are trademarks or registered trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. A current list of IBM trademarks is available on the web at “Copyright and trademark information” at: http://www.ibm.com/legal/us/en/copytrade.shtml

The following are trademarks or registered trademarks of other companies.LinkedIn, the LinkedIn logo, the IN logo and InMail are registered trademarks or trademarks of LinkedIn Corporation and its affiliates in the United States and/or other countries.Java and all Java based trademarks and logos are trademarks or registered trademarks of Oracle and/or its affiliates.Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries.Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.Oracle and Java are registered trademarks of Oracle and/or its affiliates.UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.OpenStack is a trademark of OpenStack LLC. The OpenStack trademark policy is available on the OpenStack website.SAP, R/3, SAP NetWeaver, ByDesign, SAP BusinessObjects Explorer, StreamWork, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE in Germany and other countries.

All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.

GUARDIUM*HyperSwapIBM*IBM (logo)*

Rational*Redbooks*SmartCloud*Tivoli*

UrbanCodeWebSphere*Z13zEnterprise*

z/OS*zSecurez Systemsz/VM*


Legal informationPlease note

IBM’s statements regarding its plans, directions, and intent are subject to change or withdrawal without notice at IBM’s sole discretion. Information regarding potential future products is intended to outline our general product direction and it should not be relied on in making a purchasing decision.

The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for our products remains at our sole discretion.

Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. Theactual throughput or performance that any user will experience will vary depending upon many factors, includingconsiderations such as the amount of multiprogramming in the user’s job stream, the I/O configuration, the storageconfiguration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve resultssimilar to those stated here.

Availability. References in this presentation to IBM products, programs, or services do not imply that they will be available in all countries in which IBM operates.The workshops, sessions and materials have been prepared by IBM or the session speakers and reflect their own views. They are provided for informational purposes only, and are neither intended to, nor shall have the effect of being, legal or other guidance or advice to any participant. While efforts were made to verify the completeness and accuracy of the information contained in this presentation, it is provided AS-IS without warranty of any kind, express or implied. IBM shall not be responsible for any damages arising out of the use of, or otherwise related to, this presentation or any other materials. Nothing contained in this presentation is intended to, nor shall have the effect of, creating any warranties or representations from IBMor its suppliers or licensors, or altering the terms and conditions of the applicable license agreement governing the use of IBM software.All customer examples described are presented as illustrations of how those customers have used IBM products and the results they may have achieved. Actual environmental costs and performance characteristics may vary by customer. Nothing contained in these materials is intended to, nor shall have the effect of, stating or implying that any activities undertaken by you will result in any specific sales, revenue growth or other results.

zsap db2 11 z13 hybrid sap apps chicago - ibm.com · and analytical sap applications johannes...

Documents