db2 for ibm i 7.3 and sap dbsl enhancements for ibm i€¦ · db2 for ibm i 7.3 and sap dbsl...

SAP on IBM POWER Summit DACH 2016 - © IBM Corporation 2016

What have we done for you lately

Dorothea Stein – Software Engineer, SAP

Scott Forstie – DB2 for i Business Architect

DB2 for IBM i 7.3 and SAP DBSL Enhancements for IBM i


IBM-SAP Development Partnership

Roadmaps

Features Details

Agenda

2



DB2 for IBM i Team

Watch the market

Watch/Influence Industry Standards

Participate in DB2 Family

Create specification or suggest alternatives

Create a feature

Delivery:

Release

DB2 PTF Group

PTF

SAP on IBM i Team

Watch (general) SAP requirements

Suggest improvements

Represent DB2 for IBM i

Write requirement and prioritize

Early Reviews/Tests

SAP Info APAR update

Integrate

Describe (SAP Note)

SAP or SL Tool Release

Patch

Discuss

You’ve seen this before…


7.1 and earlier

……… i next…

2010 2011 2013 20142012 20162015

7.37.2

• Temporal Tables• Generated

Columns for Auditing

• New OLAP Specifications

• Row Permissions• Column Masks• Media Preference• Memory

Preference• EVI Only Access

• Field Procedures• Implicitly hidden columns• Range and Hash Partitioning• Row Change Timestamp• Triggers & Constraints• And more…

Data Centric solutions


Data Security solutions

7.1 and earlier

……… i next…

2010 2011 2013 20142012 20162015

7.37.2

• Generated Columns for Auditing

• Authority Collection

• Row Permissions• Column Masks• Query journals

with SQL• Function Usage

• Field Procedures• Guardium Database Activity

Monitor• Guardium Vulnerability

Assessment• Guardium Classifier


Data Application Development

7.1 and earlier

……… i next…

2010 2011 2013 20142012 20162015

7.37.2

• Generated Columns• JSON• OLAP• DB2 tooling in ACS• IBM i Services

• Free Format RPG & SQL• Named and Default

Parameters on functions• DB2 Built-in Global

Variables• LIMIT & OFFSET• Regular Expressions• Obfuscation• CREATE OR REPLACE…

• Named and Default parameters on procedures

• Global Variables• XML• HTTP Functions• Pipelined Functions• Dynamic Compound

Statements


Database Performance

7.1 and earlier

……… i next…

2010 2011 2013 20142012 20162015

7.37.2

• Light-weight Database Monitoring

• DB2 SMP and OLAP• DB2 SMP and index

builds

• New SQE I/O Costing Model

• SQE supports Native Queries

• EVI Only Access• Inlined UDTFs• Improved Inlining of UDFs• Statement Deterministic

functions

• Media Preference• Memory Preference• Improved SQL code gen• Automation of DBE tasks• Visual Explain drill-down• Index advice to live plan cache• QSYS2.OVERRIDE_TABLE()


SAP: Transition to a Data Centric Programming Model

Small(er) amounts of specific data

Database Software is not very capable

Perform as much work in ABAP

Use DB as a Store only (Pool/Cluster Tables)

Parallelization done by SAP (application)

Big Data !

Powerful Database Software

Process the data where it is (Database)

Make data available (Declustering)

Scale out on the database server

ABAP

Database

ABAP

Database


ABAP Core Data Services

Massive Extensions of

The Open SQL Query Language

Data Dictionary (View Definitions)

Session Variables[Global Variables]

ArithmeticExpressions

Aggregate Functions

HAVING

CDS Functions[UDFs & Scalar DB Functions]

Database Independent!

GROUP BY

LEFT/RIGHTINNER/OUTER JOINs

„Views over Views“

Views w/ Parameters[UDTFs]

CASE Expressions

Arbitrary JOIN ORDER

Literals

Higher Limits

INSERT … SELECT

CTEs

AssociationsDCLCreated Global Temporary Tables


ABAP Core Data Services – How and Where to Start?

Usable, for example, to optimize custom SAP code to any extend !

DB

REST

OData

Open SQL

ABAP platform

Client

CDS

BOPF

SAP Gateway

SADL

1. “I want it all, I want it now”:

Create a complete Virtual Data Model (VDM)

to be used by SAP FIORI type applications

Define a “system” of CDS Views (VDM)

Use annotations to create descriptive metadata

Create SAP FIORI applications

(e.g. using BOPF/SADL).


ABAP Core Data Services – How and Where to Start?

Usable, for example, to optimize custom SAP code to any extend !

1. More Surgically:

Review individual long-running custom reports

to rewrite SQL, but keep the UI strategy.

Create your own reporting elements!

Check custom code for long (application server)

runtimes

Create some CDS Views (or not)

Rewrite inefficient ABAP processing code to use

SQL instead

select

sum(

currency_conversion(

paymentsum,

currency,

'EUR',

'20161118', ..., ))

from sflight

into table @itab.

select * from sflight

into table @itab.

loop at itab.

<calc converted SUM in ABAP>

endloop.



Roadmaps

Features Details

Agenda


2015

Enhancements in 2014:• LPAD & RPAD functions

• Faster iASP vary on

• QSYS2.SysPackageStmtStat catalog

• PREVENT_ADDITIONAL_CONFLICTING_LOCKS QAQQINI control

• Automatically reposition rows within partitioned tables

• QSYS2.GENERATE_SQL() procedure

• Improved support for Turkish users

• Improved Reliability, Availability & Serviceability for SAP clients

2016 2017

Enhancements in 2015:

• Default *SQLPKGs to 1G

• *SQLPKG utilization improvements

• QSQPRCED() QAQQINI controls

• Increased parameter limits for functions

SQL Query Engine (SQE)

• EVI Only Access (EOA) support

• LIMIT & OFFSET

7.1 - TR8 & TR97.2 – GA & TR1

7.1 – TR10 & TR117.2 – TR2 & TR3

Plans are subject to change without notice

Possible enhancements in 2017:

• Enhanced SQL built-ins

• Other high priority requests from SAP


• More performance improvements

7.2 – TR4 & TR57.3 – GA & TR1

DB2 for – Enhancements for SAP

Enhancements in 2016:

• Enhanced SQL built-ins

• Database health insight

• Improved capabilities for Visual Explain

• Improved lock failure insight

• Other high priority requests from SAP


• Inlined UDTFs

• Improved Inlined UDFs

• Statement Determinism

• Other performance improvements

7.2 – TR6 & TR77.3 – TR2 & TR3


nZDM for iMulti-Action Trigger

Upgrade PerformanceFair Lock OptionALLOW_DDL_WHILE_OPEN(Delete control for table scans)

AdminDeflated Tables, II

CDS 7.40 SP8Scalar DB FunctionsLPAD/RPADUser defined FunctionsGENERATE_SQL()PTF_INFO()

CDS 7.50Table Functions / UDTF LimitsMore UDFsVARCHAR_BIT_FORMAT()CGTT Building Block BIGINTINSERT … SELECTGlobal VariablesSQL Naming

New DBACOCKPITVisual ExplainClient Specified Registers

2014 2015 2016 2017

All future deliveries are subject to change w/o notice

CDS Performance, IUDTF InliningUDF Cache ReuseUNION Performance LEFT OUTER JOIN PerformanceStatement DETERMINISTICImproved Code Generation

CDS 7.51LIMIT OFFSETUPPER/LOWERCTEs

DBACOCKPITNew catalogsSYSLIMITS enhancedImproved DB History

AdminPrim. lang. TurkishDeflated Tables, I

CDS Performance, IIVOLATILE in DDICEOA…

SAP on DB2 for IBM i Roadmap

Kernel 7.4Defaults for……CURRENTLY COMMITTED…Fair Lock Option

Transparent DB Features#objects in a library#members in a viewSQL Package Size 1GBSQL Package CompressionSQL0913 add. Info



Roadmaps

Features Details

Agenda


Views with Parameters as User Defined Table Functions (UDTF)

DB2 UDTFs:

Generic concept to allow for little user programs

returning a set of records (== result set)

Result can come from anywhere, which is why UDTFs

are (configurable) black boxes by default.

SAP Views w/ Parameters:

… are really just SQL views

(== persistent SELECT statements) with variables

Thus: Single-line programs!

Can’t we exploit that knowledge to give

the optimizer more information?

John Doe

Scott Forstie

Doro Stein

…

Speakers'20161118‘

create function Speakers (pDate char(8))

returns table (

firstName char(30),

Name char(30))

language sql

return select Sp.firstName, Sp.Name

from SpeakerTab Sp join DateTab Dt

on Sp.id = Dt.id

where Dt.Date = Speakers.pDate

select * from table ( Speakers(‘20161118’)) as Speakers

UDTF


SQL Table Function (UDTF) Inlining

• SQL Table functions (UDTF) processing was enhanced in April, 2016

to allow SQL UDTFs with a single RETURN statement to be INLINE

eligible

• When a function is inlined, the invoking query is combined with the

query included on the function's RETURN statement



1. Apply the enabling DB2 PTF Group level (SF99702 or SF99703)

2. Use NO EXTERNAL ACTION

3. Recreate or Alter the function

CREATE OR REPLACE FUNCTION LICCHK(EXPIRATION_DATE_TO_CHECK DATE)RETURNS TABLE(RTN_PRODUCT_ID VARCHAR(7), RTN_LICENSE_TERM VARCHAR(6), RTN_RELEASE_LEVEL VARCHAR(6), RTN_LICENSE_EXPIRATION DATE, RTN_PRODUCT_TEXT VARGRAPHIC(50) CCSID 1200) LANGUAGE SQL NO EXTERNAL ACTIONNOT DETERMINISTIC RETURN SELECT PRODUCT_ID, LICENSE_TERM, RELEASE_LEVEL, PRODUCT_TEXT, LICENSE_EXPIRATION FROM QSYS2.LICENSE_INFO

WHERE LICENSE_EXPIRATION <= EXPIRATION_DATE_TO_CHECK;



• Before inlining… the *SRVPGM is invoked, SQL_TABLE_CURSOR is

processed, for every reference

• After inlining… the highlighted steps disappear

UDTF


Implementation of CDS Functions

CDS offers two types of functions:

(1) Commonly Known Functions (like MAX/MIN, CONCAT, LPAD/RPAD, CAST, HEX, …)

(2) Proprietary SAP Functions - Tied to SAP datatypes or functionality (like DATS_ADD_DAYS, TSTMP_TO_DATS, CURRENY_CONVERSION,…)

Functions of type (2) are natural candidates to be implemented as UDFs.

Functions of type (1) however could also be offered by the database vendor,

thus offering much better integration and performance.


DB2 for i response…

3. Provide more SQL built-in functions

(LPAD/RPAD, UPPER/LOWER, VARBINARY_FORMAT, …)

2. Accelerate more SQL built-in functions via the SQL Query Engine (SQE)

1. Generate faster ILE C Code for LANGUAGE SQL functions


UDF Function Performance

CDS Functions of type (2) being implemented as UDFs are black boxes,

too, but not single row programs! This might involve expensive

calculations or even execution of SQL.

What can we do to avoid the calculation?

Actually, there are two subgroups:

(2a) Deterministic UDFs

Plan Cache keeps calculated results

“infinitely”!

create function sum (

val1 integer,

val2 integer

)

returns integer

language sql

deterministic

begin

declare result integer;

set result = val1 + val2;

return result;

end

Calculateonce – use

forever!

Example for (2a)


UDF Function Performance

Much tougher:

(2b) Environment Sensitive UDFs

… should pick up changes “some time soon” ( SAP Buffer Synchronization Time)

Examples:

UNIT_CONVERSION / CURRENCY_CONVERSION should recognize

new units/currencies (tables T006, TCUR* resp.)

Some TSTMP* functions have to recognize timezone configuration changes (tables TTZ*)

Defining them as NOT DETERMINISTIC would make them VERY expensive!

2 min


DB2 for i response…

• Deterministic – Fastest Car to drive

Result doesn’t change

• Non-Determinstic – Slowest Bus you ever rode

Results always need to be calculated

• Statement Deterministic – All the cool kids want one

Repeated use within a query can be cached


SQL Scalar Function (UDF) Inlining

• SQL Scalar functions now enjoy much of the same inline potential

as SQL Table functions

• Many of the restrictions for inlining have been removed

CREATE OR REPLACE FUNCTION IMPROVE_IT(NAME VARCHAR(10))RETURNS VARCHAR(20)LANGUAGE SQL DETERMINISTICRETURN NAME CONCAT 'abulous!';


Measuring Inlining

• How do I find good opportunities for inlining?

SQL Performance Monitors and SQL Plan Cache Snapshots can be

analyzed to find high impact queries where inlining did not occur.

Sort on “Scalar UDFs Not Inlined”

Drill into SQE Summary


UDF Function Performance – Sneak Preview!

Go back to CURRENCY_CONVERSION for example:

Many different values for AMOUNT.

Few different values for the other

input parameters.

Split function into two parts:

STATEMENT DETERMINISTIC

currency_rate_conversion(...)

INLINE

currency_conversion(amount)

CREATE FUNCTION "CURRENCY_CONVERSION“

(

AMOUNT DECFLOAT,

SOURCE_CURRENCY CHAR(5),

TARGET_CURRENCY CHAR(5),

EXCHANGE_RATE_DATE CHAR(8),

EXCHANGE_RATE_TYPE CHAR(4),

CLIENT CHAR(3),

ROUND CHAR(1),

DECIMAL_SHIFT CHAR(1),

DECIMAL_SHIFT_BACK CHAR(1),

CONVERT CHAR(1),

TARGET_IS_FOREIGN CHAR(1),

ERROR_HANDLING CHAR(20)

)

Fewexpensive

calls

Manycheapcalls


LEFT OUTER JOIN Performance

ID firstName Name

13 Eric Kass

42 Scott Forstie

43 Dorothea Stein

ID Comment

42 The cool guy from the Rochester lab

43 That silly SAP person again…

define view commentView

select from speakersTab as tl

left outer join speakerCommentTab as tr

on tl.id = tr.id

{ tl.id as id,

t1.firstName as firstName,

t2.name as Name,

t2.comment as comment }

NULL

The Data Model ensures:

0 or 1 records in TR

for each record in TL!

select firstName, Name from commentView Eric Kass

Scott Forstie

Dorothea Stein Why look at all at the right-handside table ??


DB2 for i response

Sorry, top secret stuff…


LEFT OUTER JOIN Performance

define view commentView

select from speakersTab as tl

left outer join MANY TO ONE speakerCommentTab as tr

on tl.id = tr.id

{ tl.id as id,

t1.firstName as firstName,

t2.name as Name,

t2.comment as comment }

Same works for:

… Association [0..1]

DB2 can take advantage of your Data Model!

Binding Optimizer

Hints


LIMIT OFFSET

UI Paradigm Change:

Be able to collaborate and change devices any time!

Use a stateless protocol between UI and backend

The Penalty:

Page-wise reading of a set of records results in

a brand-new query execution! DB

REST

OData

Open SQL

ABAP platform

Client

CDS

BOPF

SAP Gateway

SADL

N rows

discard N rows

discard discard N rows

First Block

Second Block

Third Block

read N rows

read 2 x N rows

read 3 x N rows


LIMIT and OFFSET support is popular, but non-standard

However, the DB2 Family decided to add the support

Most useful for those cases where you only need a page of data

LIMIT is the same as FETCH FIRST n ROWS ONLY

Syntax Alternative Syntax Action

LIMIT x FETCH FIRST x ROWS ONLY Return the first x rows

LIMIT x OFFSET y OFFSET y ROWS FETCH FIRST x ROWS ONLY Skip the first y rows and

return the next x rows

LIMIT y , x OFFSET y ROWS FETCH FIRST x ROWS ONLY Skip the first y rows and

return the next x rows

LIMIT OFFSET


Connect,

SELECT…OFFSET 0 LIMIT 5

Fetch 5 rows, Close, Disconnect

Connect,



Connect,



Result set

Row

Number

Ordering

Data

Unique key

(Encrypted)

1 Abcd 1234

2 Abdc 3214

3 Acbd 4131

4 Acdb 2143

5 Bacd 1243

6 Bacd 2341

7 Bcad 4213

8 Bcda 3142

9 Bdac 1423

10 Bdca 2431

11 Bdca 3412

12 Cadb 1324

13 Cbad 4321

OFFSET and LIMIT for Stateless Pagination


How to get those improvements?

SAP Note 2075068 lists all mentioned (and many more) improvements for

CDS (PTFs, Kernel Patches, ABAP Changes)

RSDB4CDSTOOLS can check system readiness programmatically

Individual Features:– (Table) Function Inlining: Transparent to SAP (PTF-06)

– Statement Deterministic – SAP Note 2387167 (PTF-13) & (SAP-16)

– LEFT OUTER JOIN Perf – SAP Note 2387196 (PTF-12), (KRN-09), (SAP-17)

– LIMIT OFFSET: NW 7.51 base



Stay current on your DB2 PTF Group

---- Derive the IBM i operating system level and then -- determine the level of currency of PTF Groups--With iLevel(iVersion, iRelease) AS(select OS_VERSION, OS_RELEASE from sysibmadm.env_sys_info)SELECT P.*

FROM iLevel, systools.group_ptf_currency PWHERE ptf_group_release =

'R' CONCAT iVersion CONCAT iRelease concat '0'ORDER BY ptf_group_level_available -

ptf_group_level_installed DESC;



What do you see?


Questions? – Thank You!Contact: [email protected]

[email protected]

See our blog and discuss with us at http://go.sap.com/community/topic/ibm-i.html

mailto:[email protected]

mailto:[email protected]

http://go.sap.com/community/topic/ibm-i.html


Legal Information – Please 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. The actual throughput or

performance that any user will experience will vary depending upon many factors, including 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 results similar 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 IBM or 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.

38


Legal Information – Trademarks, Copyrights

Information contained in this material has not been submitted to any formal IBM review and is distributed on “as is” basis without any warranty either

expressed or implied. The use of this information is a customer responsibility.

IBM MAY HAVE PATENTS OR PENDING PATENT APPLICATIONS COVERING SUBJECT MATTER IN THIS DOCUMENT. THE FURNISHING OF

THIS DOCUMENT DOES NOT IMPLY GIVING LICENSE TO THESE PATENTS.

The client examples cited are presented for illustrative purposes only. Actual performance results may vary depending on specific configurations and

operating conditions. It is the user’s responsibility to evaluate and verify the operation of any other products or programs with IBM products and

programs.

IBM, the IBM logo, ibm.com, AIX, BatchPipes, BladeCenter, DB2, DB2 Connect, Netezza, GDPS, GPFS, Netfinity, Intelligent Miner, OpenPower,

Parallel Sysplex, POWER, RACF, Redbooks, RETAIN, System Storage, System p, System x, System z, Tivoli, WebSphere, zEnterprise, z9, z10, and

z/OS 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

Linux is the registered trademark of Linus Torvalds in the U.S. and 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.

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.

39

http://www.ibm.com/legal/us/en/copytrade.shtml

db2 for ibm i 7.3 and sap dbsl enhancements for ibm i€¦ · db2 for ibm i 7.3 and sap dbsl...

Documents