your sap on ibm i solutions with netweaver 7.5 and beyond€¦ · sap on ibm power summit dach 2016...

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

Christian Bartels, Eric Kass, SAP on IBM i Development Team

November 18th, 2016

Your SAP on IBM i Solutions with NetWeaver 7.5 and Beyond


SAP NetWeaver 7.5 and Beyond – Agenda

Support of the latest SAP and IBM releases

Considerations for upgrading to SAP NetWeaver 7.5

7.50 Generation Database Driver

The new SAP on IBM i Community

2







3


SAP Release News for IBM i – Overview

4

December2016*)

Downward-compatible kernel release 7.49 forSAP NetWeaver releases 7.40 and 7.5(SAP Note 2350788) – IBM i 7.1, 7.2 and 7.3

SAP NetWeaver ABAP 7.51 Innovation Package

with kernel release 7.49 for IBM i 7.2 and above

(SAP Note 2329005) – NetWeaver only

Downward-compatible kernel release 7.45 for

SAP NetWeaver release 7.40 (SAP Note 2251972)

– IBM i 7.1, 7.2 and 7.3

March2016

October2016

*) SAP plans may change without notice

https://launchpad.support.sap.com/#/notes/2350788/

https://launchpad.support.sap.com/#/notes/2329005

https://launchpad.support.sap.com/#/notes/2251972


SAP Application, NetWeaver, Kernel and OS Dependencies

5

ERP 6.0EHP 6

ERP 6.0EHP 7

ERP 6.0EHP 8

ERP 6.0EHP 5

ERP 6.0EHP 4

ERP 6.0

SAP NW7.03

SAP NW7.40

SAP NW7.5

SAP NW7.51

SAP NW7.02

SAP NW7.01

SAP NW7.00

Kernel7.20/7.22

Kernel7.40/7.45

Kernel7.45

Kernel7.49

Kernel7.20/7.22

Kernel7.01/7.22

Kernel7.00/7.22

≥ IBM i7.1

≥ IBM i7.1

≥ IBM i7.2

≥ IBM i7.2

≥ i5/OSV5R4M0

≥ i5/OSV5R4M0

≥ i5/OSV5R3M0


SAP Kernel and IBM i Release Support November 2016

6

Kernel

OS7.20

7.20

EXT7.21

7.21

EXT7.22

7.22

EXT7.40 7.41 7.42 7.45 7.49

V5R3 Base Base

V5R4 Base Base Base

IBM i 6.1 Base Base Base

IBM i 7.1Patch

49

Patch

49Base Base Base Base Base Base Base Base Base

IBM i 7.2Patch

300Base

Patch

31Base Base Base

IBM i 7.3Patch

626

Patch

101

Patch

100Base

SAP Note 68440 – IBM i: How do I upgrade to a later OS release?

Color code: Supported No longer in maintenance DCK 12/2016 – SAP Note 2350788









7


SAP NetWeaver 7.5 – General Considerations

8

SAP NetWeaver 7.5 / ERP 6.0 EHP8

• Unicode only

• No dual stack support

• No central instance

(except for single-instance systems)

• Transparent tables only (no pool

or cluster tables – see next slides)

SAP Kernel 7.45 Unicode

IBM i 7.2 or 7.3

SAPJVM 8

Unicode conversion prior to 7.5:

SAP Note 1051576 (General)

SAP Note 800791 (In-place)

SAP Note 928729 (CU&UC)

Dual-Stack split:

SAP Note 1797362

SWPM ≥ 1.0 SP06 →

SAP NetWeaver → Database →

Additional SAP System instances

→ Split off ASCS Instance






ASCII/Unicode Conversion for Releases 7.3x and 7.4x

9

„Inplace“ Method:

For single codepage

systems in Latin-1,

transparent tables do not

need to be converted.

ASCII

database

→ Unicode

Export files

(Unicode)

Export /

conversion

Import Pool and

cluster tables

Documentation:SAP Note

800791

(Attachment)

SAP Note

1051576

(Attachments)

SAP Note

928729

(Attachments)

CU&

UC





SAP Pool and Cluster Tables

10

SAP dictionary:

Database view:

Technical goal:

Pool tables Cluster tables

Reducing number

of tables in database

Reducing table size in

database by compression

Physical pool Physical cluster

Many small tables Few, partially very large tables


Advantages and Disadvantages of Clustered Tables

11

Advantages of clustered tables:

+ High data compression rate on disk.

+ Combined processing of logically joined data.

+ Reduced number of tables and joins in database.

Shortcomings of clustered tables:

- Row selection by common key only.

- No analytical use of data.

- Enhanced data processing only in application server.

→ Clustered tables unusable for CDS Views.

→ Declustering optional as of SAP NW 7.40 SP03.

→ Declustering advised for SAP NetWeaver 7.5.

SAP Note 1835008

– Activate Additional

Database Performance

Optimizations for SAP

ERP

SAP Note 1892354

– SAP Strategy for

Cluster and Pool

Tables




Declustering – What to Expect?

12

Total database size may grow – extend depends heavily on application

type and customer data.

Estimation: Report RSDB4_EST_CLUSTER_TABSIZE (SAP Note 2236867).

Some tables may grow beyond 1.7 TB – table partitioning required:

Table Recommendation

BSEG Archiving with object FI_DOCUMNT (consider retention period)

Partitioning by key BUKRS

CDPOS Archiving with object CHANGEDOCU: SAP Note 1257133

Partitioning by key OBJECTID (or maybe key OBJECTCLAS)

EDID4 Archiving: SAP Note 1572522 – declustering: SAP Note 2305441

KONV Partitioning by keys KDATU or KNUMV






Considerations for Table Partitioning

13

Hash partitioning:

Definition by number of partitions.

Adding partitions requires

redistribution of data.

No support for „data aging“

or preferred storage location.

Non-partitioned („spanning“)

index over partitioning key.

Key range partitioning:

Concept for key ranges required.

May need additional partitions as

data gets added to the table.

Adding partitions fast and simple.

Optional: Data aging, fast delete,

preferred storage location.

Partitioned index over partitioning key.

5770SS1 Option 27 – DB2 Multisystem required

SAP Note 2187681 – IBM i: Table partitioning in non-BW systems

SAP Transaction TAANA – Table Analysis: Administration

Consider service „DB2 for i Very Large Database (VLDB) Assessment“



Declustering Process

14

Run ABAP reports SDBI_CLUSTER_CHECK,

SDBI_POOL_CHECK, SDBI_CHECK_BCD_NUMBERS

Run ABAP report RSDB4EST_CLUSTER_TABSIZE

Define partitioning for very

large tables (SE11/SE14)

Partitioning

required?

Use SE11/SE14 to

decluster all tables

Perform system copy/refresh

(SWPM 1.0 SP9)

to decluster all tables

SAP Note

1784377

SAP Note

2187681

SAP Note

2227432

No Yes

SAP

Note

2236867











15



16

New Package Caches: Optimized for both Static and Ad-Hoc SQL

Transparent SQL Package Spillover : “Bottomless”

Cursor Close State Harmonized between DB and SAP


Goal: Steady Performance and Stable Package Resource Consumption

17


SAP 7.49 Dynamic DBSL: SQL Package Handling

SQL Source “Code”

Database Engine

“Program” – The Plan

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8...

SQL package S4PB2Z000H/SAPMSSY2

STATEMENT NAME: STMT.____.A.JAG0L6DAJAM43.1052076.20160615060847

SELECT "EVENTID" , "EVENTCOUNT" , "ACTIVATED" , "EVENTPARM" , "JOBNAME" ,

"JOBCOUNT" , "PROCMODE" FROM "BTCEVTJOB" WHERE "EVENTID" = ? AND "ACTIVATED" =

? AND ( "EVENTPARM" = ? OR "EVENTPARM" = ? ) /* ISO: UR */

SQL4021 Access plan last saved on 11/11/16 at 13:14:29.

SQL4020 Estimated query run time is 0 seconds.

SQL402D Query attributes from query options file QAQQINI in library R3PB2DATA.

SQL4027 Access plan was saved with DB2 Multiprocessing installed on the system.

SQL4017 Host variables implemented as reusable ODP.

SQL4008 Index BTCEVTJOB used for table 1.

SQL4011 Index scan-key row positioning used on table 1.

SELECT "BTCSERVER" , "CTLOBJ" , "BTCSYSTEM" , "TIMSTMPDT" , "TIMSTMPTM" ,

"TRACELEVEL" , "TRCTIMSTDT" , "TRCTIMSTTM" , "TRCCHUSR" , "OPMODE" ,

"OPMODTSTDT" , "OPMODTSTTM" , "OPMODCHUSR" FROM "BTCCTL" WHERE "BTCSERVER" = ?

AND "CTLOBJ" = ? LIMIT 1 /* ISO: UR */






SQL4008 Index BTCCTL used for table 1.


REPORT Z_TEST.

DATA: SVERSLocal like SVERS.

SELECT * from SVERS into SVERSLocal

WRITE: / SVERSLocal-VERSION.

ENDSELECT.

ABAP Report

A typical SAP System

consists of 100,000’s

of Statements in 1000’s

of packages

SQL Package

18


DBSL considers three flavors of SQL statements …


19


REPORT Z_TEST.

***********************

* Query with OPEN SQL *

***********************

DATA: SVERSLocal like SVERS.

SELECT * from SVERS into SVERSLocal WHERE VERSION ='700'.WRITE: / SVERSLocal-VERSION.

ENDSELECT.


SQL Statements appearing

“statically” in an ABAP Report


20


REPORT Z_FORALLENTRIES.

DATA: SFLIGHTLocal LIKE SFLIGHT OCCURS 20 WITH HEADER LINE.

DATA: CarrierIDTable LIKE SFLIGHT OCCURS 20 WITH HEADER LINE.

* ------------------------------------------ *

* Build Internal Table with Selection Filter *

* ------------------------------------------ *

CarrierIDTable-CARRID = 'AF'.

CarrierIDTable-PlaneType = 'A330-300'.

APPEND CarrierIDTable.

CarrierIDTable-CARRID = 'LH'.

CarrierIDTable-PlaneType = 'A319'.

APPEND CarrierIDTable.

* -------------------------------------- *

* Single Column - For All Entries "Join" *

* -------------------------------------- *

SELECT * from SFLIGHT into table SFLIGHTLocal

FOR ALL ENTRIES in CarrierIDTable

WHERE CARRID eq CarrierIDTable-CARRID .

LOOP AT SFLIGHTLocal.

WRITE: / SFLIGHTLocal-CARRID,

SFLIGHTLocal-PlaneType.

ENDLOOP.


SELECT

"MANDT" , "CARRID" , "CONNID" , "FLDATE" , "PRICE" , "CURRENCY" ,

"PLANETYPE" , "SEATSMAX" , "SEATSOCC" , "PAYMENTSUM" , "SEATSMAX_B" ,

"SEATSOCC_B" , "SEATSMAX_F" , "SEATSOCC_F"

"SEATSOCC_F" FROM "SFLIGHT" WHERE "MANDT" = ? AND

"CARRID" IN ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )

Generated SQL

Statements


21


REPORT Z_LONG_STATEMENT.

DATA: LongSQL TYPE String.

connectionREF TYPE REF TO cl_sql_connection,

resultREF TYPE REF TO cl_sql_result_set,

sqlerrREF TYPE REF TO cx_sql_exception,

prepstmtREF TYPE REF TO cl_sql_prepared_statement,

Version LIKE SVERS-Version,

VariableREF TYPE REF TO DATA .

CREATE OBJECT connectionREF .

TRY.

LongSQL = 'SELECT VERSION FROM SVERS WHERE 1 = 1' .prepstmtREF = connectionREF->prepare_statement( LongSQL ) .

WRITE: / 'SELECT VERSION FROM SVERS: ' .

resultREF = prepstmtREF->execute_query( ) .

GET REFERENCE OF Version INTO VariableREF .

resultREF->set_param( VariableREF ) .

WHILE resultREF->next( ) > 0 .

WRITE: / Version .

ENDWHILE .

resultREF->close( ) .

CATCH cx_sql_exception into sqlerrREF.

ENDTRY.

connectionREF->close( ) .


Ad-Hoc SQL

Statements


22


Static SQL

Direct Map

Transient Round

Robin Cache(Single Execution)

Generated

Reusable SQL

Dynamic SQL Ad Hoc

Persistent

Statement Cache

DBSL Package Switching Logic


23


SAP 7.49 Dynamic DBSL: Statement Cache Statistics

C +-----------------------------------------

C | DBSL Statement Cache Stats for Conn: 0

C | ----------------------------------------

C | Prepare SQL Text Cache Ratio: 79%

C | Open DirectMap Ratio: 46%

C | Open STMTID Cache Ratio: 12%

C +-----------------------------------------

C | DBSL Cursor Cache Stats for Conn: 0

C | ----------------------------------------

C | Reopen Open Ratio: 0%

C | Optimistic Close Ratio: 97%

C +-------------------------------

C | DBSL Global ALL Cache Quality

C +-------------------------------

C | ALL Cache Ratio: 87%

24


SQL Package

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8...

SQL package S4PB2Z000H/SAPMSSY2

STATEMENT NAME: STMT.____.A.JAG0L6DAJAM43.1052076.20160615060847

SELECT "EVENTID" , "EVENTCOUNT" , "ACTIVATED" , "EVENTPARM" , "JOBNAME" ,

"JOBCOUNT" , "PROCMODE" FROM "BTCEVTJOB" WHERE "EVENTID" = ? AND "ACTIVATED" =

? AND ( "EVENTPARM" = ? OR "EVENTPARM" = ? ) /* ISO: UR */






SQL4008 Index BTCEVTJOB used for table 1.


SQL904

STOP

Resource limit

exceeded

SAP 7.49 Dynamic DBSL: Automatic SQL Package Spillover

Transient Round

Robin Cache(Single Execution)

DBSL Package Switching LogicSELECT "MANDT" , "CARRID" …

25



Rare Situations

26



Work Process Developer Trace: dev_w0

C *** ERROR => DbSlPrepare96: Statement->Prepare() rc=99 sqlcode=-904

C *** ERROR => DbSlPrepare96: Redo Prepare as Single Execution

M ***LOG D52=> Package: S4N51X000H/ZDRTABSVER

C DbSlPrepare96: ADVISE dbsl_tablehint:

C DbSlPrepare96: Table: ZDRTAB_SVERS

C DbSlPrepare96: NoSTMTID=true

Avoiding Spillover by

modifying DBSL SQL

package switching logic

Rare Situations

27


Cursors of

different jobs

Table ALTER and Add

Trigger require

Exclusive locks

SAP 7.49 Dynamic DBSL: Harmonized Cursor Close State

IBM i 7.2 Database

Lock Queue needs

to know Real

Cursor Close State

28







29


SAP on IBM i Community

30

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

Tags:• IBM i• IBM DB2 for i• ibm i devnews

Scro

ll dow

n…

Blog

Questions& Answers



Asking Questions in the SAP on IBM i Community

31

Ask a Question:

• Select a primary tag (required):IBM DB2 for i; IBM i

• Select additional tag(s):<selection list>

• Add or select user tags:

• <free text>


SAP NetWeaver 7.5 and Beyond – Summary

32

Latest SAP releases: SAP NetWeaver 7.51, SAP kernel 7.22 EXT for all

SAP NetWeaver releases prior to 7.40, SAP kernel 7.49 as downward-

compatible kernel for SAP NetWeaver 7.40, 7.5 and 7.51 coming soon.

SAP NetWeaver 7.5: Consider Unicode conversion, dual-stack split,

central services instance and declustering/depooling when upgrading.

7.50 Generation Database Driver: Near zero SQL package

maintenance, faster exclusive table locks with kernel release 7.49

SAP on IBM i Community: New link, tags IBM i, IBM DB2 for i


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]



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.

34


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.

35

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

your sap on ibm i solutions with netweaver 7.5 and beyond€¦ · sap on ibm power summit dach 2016...

Documents