dev204_15495_gebhardtb_1

Public

Stefan Bresch, Boris Gebhardt, Jens Lieberum, Johannes Marbach

PI Technology Core Platform

DEV204 Custom ABAP Code Get Ready for SAP HANA

2014 SAP SE or an SAP affiliate company. All rights reserved. 2 Public

Disclaimer

This presentation outlines our general product direction and should not be relied on in making a

purchase decision. This presentation is not subject to your license agreement or any other agreement

with SAP. SAP has no obligation to pursue any course of business outlined in this presentation or to

develop or release any functionality mentioned in this presentation. This presentation and SAP's

strategy and possible future developments are subject to change and may be changed by SAP at any

time for any reason without notice. This document is provided without a warranty of any kind, either

express or implied, including but not limited to, the implied warranties of merchantability, fitness for a

particular purpose, or non-infringement. SAP assumes no responsibility for errors or omissions in this

document, except if such damages were caused by SAP intentionally or grossly negligent.


Agenda

Motivation

Prepare custom code for SAP HANA

ABAP Test Cockpit

Optimize custom code for SAP HANA

SQL Monitor

SQL Performance Tuning Worklist

Summary

Motivation


SAP Business Suite powered by SAP HANA What makes HANA so Unique?

100% In-Memory computing Allow OLTP & OLAP processing in Real-Time

5-20x Compression

Column based compression

Up to 10,000x Faster

Massive parallel scaling


SAP Business Suite powered by SAP HANA Migrate to SAP Business Suite powered by SAP HANA

SAP HANA Database

AS ABAP 7.4

SAP Business Suite powered by

SAP HANA

Any Database

AS ABAP

SAP Business Suite

Migrate

based on ABAP 7.4 = the to-go release for all SAP HANA based ABAP applications


SAP HANA

Upgrade

Upgrade SAP Business

Suite

Custom Code


SAP HANA

Custom Code

Custom code is a central part of the

business functionality

How can customers smoothly migrate and

optimize their code for SAP HANA?

Adapt

Visit DEV 262 Evolution of the ABAP Programming

Language


Migrate to SAP Business Suite powered by SAP HANA Impact on custom ABAP development

Which of my ABAP code must be changed to

avoid potential functional issues?

Which of my ABAP code shall be optimized to

ensure good performance?

Which of my main business processes have

potential for HANA so that I can speed up those

massively?

And how can I find all this ABAP code easily

and do the adaption efficiently?

Prepare Custom Code for SAP

HANA ABAP Test Cockpit and Static Checks


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code

In general existing ABAP code runs on SAP HANA as before

Only if ABAP code relies on technical specifics of the old database, ABAP code changes

might be necessary

Which of my ABAP code must be changed to avoid potential functional issues


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code

Technical changes with SAP HANA that may affect existing DB specific ABAP code

See SAP note 1912445 for details

Technical Change Details and Examples Effect on DB specific ABAP code

DB migration Each DB has specific features and unique technical behavior

DB specific code may rely on these features of the used database

SAP HANA architecture Column based architecture - as a consequence e.g. secondary DB indexes are

less important

DB specific code may rely on the existence/usage of certain DB indexes

Depooling/Declustering During the migration to SAP HANA most pool and cluster DB tables are

transformed to transparent DB tables (depooling / declustering) so that the tables

can be used in analytic scenarios

DB specific code may rely on the technical specifics of pool and cluster tables


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code Details and examples

Example for native SQL Example for a DB hint

Technical Change ABAP code which must be checked / corrected Recommended solution

DB migration Native SQL, Hints, ADBC which uses DB specific features Use Open SQL if possible, adapt native SQL and DB hints if necessary


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code Details and examples

Read Table BINARY SEARCH works only for a correctly sorted internal table.

But no explicit sorting of the query result is done

-> Error in code which may work by chance

because of DB Index usage

Correction

Add explicit sorting by adding ORDER BY to

the SELECT or by using an ABAP SORT

Example for ABAP code which

relies on sorted DB query

results

Technical Change ABAP code which must be checked / corrected Recommended solution

SAP HANA

architecture

Transparent DB tables:

Relying on not guaranteed / implicit sorting of DB query results

(Rare problem) Analysis of technical DB index information using function modules 'DB_EXISTS_INDEX, 'DD_INDEX_NAME

Recommendation:

Never rely on any implicit sorting - no robust code! Always use ORDER BY or SORT BY to sort query result sets explicitly

Try to get rid of DB index analysis

Depooling/

Declustering

Pool/Cluster tables:

Relying on not guaranteed / implicit sorting of DB query results

(Rare problem) Direct access to physical clusters / pools

Recommendation:

Never rely on any implicit sorting - no robust code! Always use ORDER BY or SORT BY to sort query result sets explicitly

Get rid of direct access to physical clusters / pools


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code Static Checks I

For all main functional error categories we provide Code Inspector checks to find the issues easily

Check variant FUNCTIONAL_DB - SAP note 1935918


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code Static Checks NW 740 SP5 - Details

Search problematic statementwithout ORDER BY check Preferred check to find code which relies on sorted DB content searches for statements like READ BINARY SEARCH, DELETE

ADJACENT DUPLICATES, accessing unsorted DB content works for transparent and pool/cluster DB tables low false positive ratio

Search DB Operations in Pool/Cluster Tables check Searches for DB operations on the physical pool/cluster DB tables Check is available starting with 740 SP5 or via note 1920165 .

Recommendation

Run both checks for your custom code Replace access to physical pool/cluster DB tables Concentrate on findings (priority 1 and 2) of the Search problematic

statementwithout ORDER BY check

See Appendix for corresponding static checks in NW 740 SP2 SP4


Prepare your custom code for SAP HANA ABAP Test Cockpit Run your Code Inspector checks in a new tool

ABAP Test Cockpit (ATC) is a

framework for running static checks on

ABAP objects to find: functional,

usability, performance, security, bugs

ABAP Test Cockpit is based on Code

Inspector and adds new quality gates,

quality processes and better usability

Lab Preview: Starting with NW 740

SP8, the developer role of the ABAP

Test Cockpit is fully integrated in ABAP

Development Tools

Visit DEV 165 Code Better with ABAP in Eclipse

Demo ATC + Static Checks


Prepare your custom code for SAP HANA Encore Functional corrections Runtime Checks

Runtime Checks supplement

static checks to find additional

errors during runtime

Availability starts with NW 740

SP2 (see notes 1912445 and

1931870 for additional info)

Production / Test System

1. Activate runtime checks in

production (negligible

performance overhead)

3. Analyze logged records

4. Correct the code

2. Wait 4 weeks


Summary - Prepare your custom code for SAP HANA ABAP Test Cockpit and static checks

In general existing ABAP code runs on SAP HANA as before.

Only DB specific ABAP code must be analyzed

New static Code Inspector Checks and Runtime Checks are

available which help to find the code which must be analyzed

ABAP Test Cockpit (ATC) is the new standard tool for static

ABAP code checks

ATC can be used now to prepare the custom ABAP code for

HANA and to improve the general quality of the code

ATC availability starts with NW 702 SP12 / NW 731 SP5.

In older releases the Code Inspector can be used

Optimize Custom Code for SAP

HANA SQL Monitor, Static Checks and SQL Performance Tuning Worklist


Optimize Custom Code for SAP HANA Tooling

How can I find the ABAP code which shall be optimized or which has potential

for massive acceleration using code push down to SAP HANA

In general no changes are necessary if your SQL code follows the golden Open SQL rules

Use static checks to find SQL patterns that violate the golden Open SQL rules

Add runtime performance data from production to rank the findings and to find potential for

massive acceleration


Optimize Custom Code for SAP HANA Classic golden SQL rules


Optimize Custom Code for SAP HANA Shift in priorities of classic golden SQL rules for SAP HANA

Shift in priorities for SAP HANA

Avoid selecting large numbers of not

required columns

Prefer array operations for INSERT,

UPDATE und DELETE when changing

many records

Avoid nested SELECT loops

In most cases SAP HANA does not require

secondary indices

Keep unnecessary load away from the

database

Code pushdown for data-intensive

calculations to benefit from SAP HANA


Optimize Custom Code for SAP HANA Static Performance Checks

All golden SQL rules are covered by static checks. Check

appendix for the release where the checks are available.


Optimize Custom Code for SAP HANA New Static Performance Checks

Minimize amount of transferred data and number of data transfers

SELECT * (used columns vs. selected columns) Nested SELECT statements (across modularization units) SELECT + SELECT FOR ALL ENTRIES

which can be transformed into JOIN or SUBQUERY

SELECT * check

No field used pure existence check

Nested SQL check

SELECT in LOOP (across

modularization unit)

SELECT + SELECT FOR ALL ENTRIES

transform into JOIN or SUBQUERY


Custom Code Base

Optimize Custom Code for SAP HANA Static Performance Checks

Covered by Static Checks

Not covered: Code generated at

runtime, dynamic code, too

complex code

Static performance checks mainly

provide performance hints and

recommendations

Static checks cannot predict the

performance benefit of a

recommended correction

Therefore, static checks are not

sufficient as a single source for

performance optimizations

We need to add runtime data from

the productive system to find the

relevant code

Not relevant

for performance

optimizations

Relevant

for performance

optimizations

Unused

Code


Optimize Custom Code for SAP HANA Monitoring Tools

Covered by SQL Monitor

after a week/month running in

production, quarter-end or year-

end processes may not be

covered

Use monitoring and trace tools to

measure the usage of ABAP and

SQL in your productive system

1. Use Usage Procedure Logging

(UPL) to separate unused code

(dead code)

2. Use the standard performance

trace tools (ST05, SAT, ) to analyze your core business

processes

3. Find the performance hotspots with

the SQL Monitor

Custom Code Base

Performance

Hotspot

Unused

Code

Not relevant

for performance

optimizations

Relevant

for performance

optimizations


Example: A productive ERP System with 6000 concurrent users

Two weeks in this system means 50k different business processes 130k different ABAP SQL statements

(>15% in customer code) 13B SQL requests (~1B / day) 140B records (~10B / day) read or changed

Where to start an optimization ?



New SQL Monitor allows to get performance data for each and every

SQL executed in production...

Answer questions like What are the most expensive and most

frequently executed SQLs?

Which SQL reads/writes millions of records?

What is the SQL profile of my business process VA01?

Total transparency of the SQL running in production!


Demo SQL Monitor


Summary - Optimize Custom Code for SAP HANA SQL Monitor Aggregation and SQLM data records

Analysis by Request

Displays SQL performance data on request level Includes work process times of requests (740 SP5)

Analysis by Source Position

Displays SQL performance data by source position Includes ABAP Source Code Fragment (740 SP5)

SQLM data records

Contain source position, request, table names For all SQL statements (740 SP5)


Summary - Optimize Custom Code for SAP HANA SQL Monitor DB performance profile of the business processes

Business process list ranked by total DB time SQL Monitor

Displays performance data on process level (report, transaction, ) Allows to drill down from process level to the single DB operations

Drill down to the SQL profile of report

ZSQLM_TEST3 running as batch job

(6 SQL statements)

Navigate to the code


Summary - Optimize Custom Code for SAP HANA SQL Monitor Lab Preview 740 SP8 Time Series & SQL Trace

View the time series for the

selected DB operation

Time series

Analyze historical data Explore performance and usage of a DB operation over time

Integrated SQL Trace

Get more detailed information for a selected ABAP DB operation like actual parameters and ABAP call stack

Select time interval to be

analyzed (optional)

All measures available for time series


Summary - Optimize Custom Code for SAP HANA SQL Monitor

SQL Monitor collects performance data for each and

every SQL executed in an ABAP application

SQL Monitor can run in a productive system without

disturbing business processes (performance overhead


Optimize Custom Code for SAP HANA Guided performance optimization

New SQL monitor (SQLM)

Can run in production; also before migration Captures aggregated runtime data (number of executions,

database times, tables, transactions)

Runtime data

Correlate and optimize

Define context from business perspective (entry point, transaction, involved data)

Find code with best cost/benefit ratio Fix performance bugs and unexpected database

accesses

Find code which can be pushed down to SAP HANA (e.g. via a SQL script procedure)

SQL Performance Tuning Worklist (SWLT)

Improved / new code inspector checks, e.g.

Nested SELECT statements across modularization units Used columns vs. selected columns

Static code

analysis


Optimize Custom Code for SAP HANA Guided performance optimization System view

Production System

Database

Development / Q- System

Database

SQL Monitor

ABAP Test Cockpit


Download / RFC

Demo SQL Performance Tuning Worklist


Tune your custom code Get ready for SAP HANA Summary Process view Tool usage

Use ABAP Test Cockpit to correct potential functional issues and run

ATC regularly to avoid regressions

Use SQL Monitor and Runtime Checks in the SAP HANA test

system ( + SWLT in the development system) to correct functional

issues and to tune the top custom SQL statements

Use ABAP Test Cockpit for your quality assurance (avoid regressions)

Use SQL Monitor and Runtime Checks in the SAP HANA

productive system ( + SWLT in the development system) to correct

findings and to iteratively tune the custom SQL statements.

Additionally the SQL Monitor can help to detect and realize potential

for code pushdown to SAP HANA

Use ABAP Test Cockpit to evaluate / correct potential functional

issues in your custom code (old system or sandbox system)

Use SQL Monitor in the old productive system ( + SWLT in the development system) to tune the top custom SQL statements in your

system

Eliminate unused custom code to reduce migration effort

Define your main business processes for testing and tuning

Evaluate in sandbox system

Plan SAP HANA

migration

Prepare SAP

HANA migration

SAP Business Suite

Any Database

Development / Test System

SAP HANA Database

Upgrade

Migrate

SAP HANA

migration

SAP Business Suite

Any Database

Production System

SAP HANA Database

Upgrade

Migrate

SAP Business Suite

Any Database

Sandbox System

SAP HANA Database

Upgrade

Migrate


Tune your custom code Get ready for SAP HANA Short overview Realize SAP HANA potential - Code pushdown

AS ABAP

SAP HANA Database

Calculation

Calculation

Data to Code Code to Data

Code pushdown means delegating data intense calculations to the database

layer e.g. by using advanced Open SQL, ABAP CDS Views, and ABAP

Managed Database Procedures (AMDP)

Visit DEV 263 Building ABAP Applications Using

Code Pushdown to the Database

Visit TEC 201 Time to Rethink your Code Patterns -

New Boundary Conditions in Application


Tune your custom code Get ready for SAP HANA Key takeaways

In general existing ABAP code runs on SAP HANA as before.

Only DB specific ABAP code must be analyzed

ABAP Test Cockpit can be used to find and adapt DB specific

ABAP custom code easily

In general the well known golden Open SQL rules are still

valid on SAP HANA - only some priorities have shifted

For effective SQL tuning and to find HANA potential in

existing ABAP code new monitoring tools like the SQL

Monitor are available

The preparation of your custom code can start now before the migration to SAP HANA


Further Information

SAP Public Web Best Practice Guide - Considerations for Custom ABAP Code During a Migration to SAP HANA

http://scn.sap.com/docs/DOC-46714

SAP note 1912445 contains the main recommendations for the topic Custom Code for SAP HANA https://css.wdf.sap.corp/sap/support/notes/1912445

Optimizing Custom ABAP Code for SAP HANA The New ABAP SQL Monitor.pdf http://scn.sap.com/docs/DOC-47444

SAP note 1885926 contains the main recommendations for the topic SQL Monitor https://css.wdf.sap.corp/sap/support/notes/1885926

SQL Monitor Unleashed

http://scn.sap.com/community/abap/hana/blog/2013/11/16/sql-monitor-unleashed

Community ABAP for SAP HANA

http://scn.sap.com/community/abap/hana

ABAP Test Cockpit

http://scn.sap.com/docs/DOC-31773


Further Information

SAP Education and Certification Opportunities

www.sap.com/education

Watch SAP d-code Online

www.sapcode.com/online

SAP Public Web

scn.sap.com

www.sap.com

2014 SAP SE or an SAP affiliate company. All rights reserved.

Thank you

Contact information:

Stefan Bresch, SAP SE, [email protected]

Boris Gebhardt, SAP SE, [email protected]

Jens Lieberum, SAP SE, [email protected]

Johannes Marbach, SAP SE, [email protected]


SAP d-code Virtual Hands-on Workshops and SAP d-code Online Continue your SAP d-code education after the event!

SAP d-code Online

Access replays of keynotes, Demo Jam, SAP d-code live interviews, select lecture sessions, and more!

Hands-on replays

http://sapdcode.com/online

SAP d-code Virtual Hands-on Workshops

Access hands-on workshops post-event

Starting January 2015

Complementary with your SAP d-code registration

http://sapdcodehandson.sap.com

Appendix A Availability "Guided performance optimization"


Tools in the context of guided performance optimization Availability (1/5)

ABAP Test Cockpit

The ABAP Test Cockpit (ATC) is a tool for doing static and dynamic quality checking of ABAP code and associated repository objects.

SAP_BASIS 7.00 SAP_BASIS 7.01 SAP_BASIS 7.02 SAP_BASIS 7.10,

7.11, 7.20, 7.30

SAP_BASIS

7.31

SAP_BASIS 7.40

SAP_BASIS not planned not planned SP12 not planned SP5 SP0


7.11, 7.20, 7.30

SAP_BASIS 7.31 SAP_BASIS 7.40

SAP_BASIS SP0 SP0 SP0 SP0 SP0

SP0

Code Inspector

The Code Inspector is a tool to check repository objects in terms of performance, security, syntax and compliance with naming conventions.


Tools in the context of guided performance optimization Code Inspector checks for the SAP Hana migration (2/5)

Area Check title Availability (SAP_BASIS Version)

Obligatory functional

checks

Please use the checks of Code Inspector variant FUNCTIONAL_DB (note 1912445 and note 1935918 for details)

740 SP3

740 SP2 via special transport(note 1875529)

731 SP9

702 SP14

Additional functional

checks (optional)

E.g. Unsecure use of FOR ALL ENTRIES ( Please use Code Inspector variant FUNCTIONAL_DB_ADDITION -

note 1912445 and note 1935918 for details)

740 SP2

731 SP5

702 SP12

701 SP14

700 SP29

Performance Search problematic SELECT * statements 740 SP2

731 SP9

702 SP14

Performance Search SELECT .. FOR ALL ENTRIES-clauses to be transformed 740 SP2

731 SP9

702 SP14

Performance Search DB Operations in loops across modularization units (reading and changing SQLs)

740 SP2:

Search SELECTs in loops across modularization units

Changing Database Accesses in Loops (no cross stack analysis !)

Old releases:

SELECTs in Loops (no cross stack analysis !)

Changing Database Accesses in Loops (no cross stack analysis !)

740 SP3

731 SP9

702 SP14

Performance Search SELECT statement with DELETE statement 740 SP2

731 SP9

702 SP14



SQL Monitor

The SQL Monitor is a monitoring tool for ABAP based SAP systems which allows to collect performance data of all executed SQL statements in a

productive environment. It exists in two flavors: one is delivered via SAP_BASIS, one via ST-PI plug-in. In release 7.40 we recommend to use the

SQL monitor delivered via SAP_BASIS.


The SQL Performance Tuning Worklist allows to combine static performance check results with relevant runtime data coming e.g. from a productive

system.

SAP_BASIS 7.00,

Kernel 7.21

(PL>118)

SAP_BASIS 7.01,

Kernel 7.21

(PL>118)

SAP_BASIS 7.02,

Kernel 7.21

(PL>118)

SAP_BASIS 7.10,

7.11, 7.20, 7.30,

Kernel 7.21

(PL>118)

SAP_BASIS 7.31,

Kernel 7.21

(PL>118)

SAP_BASIS 7.40,

Kernel 7.40

SAP_BASIS not planned not planned SAP_BASIS SP14 not planned SAP_BASIS SP9 SAP_BASIS SP2

ST-PI ST-PI 2008_1_*

SP8

ST-PI 2008_1_*

SP8

ST-PI 2008_1_*

SP8

ST-PI 2008_1_*

SP8

ST-PI 2008_1_*

SP8

ST-PI 2008_1_*

SP8


7.11, 7.20, 7.30

SAP_BASIS 7.31 SAP_BASIS 7.40

SAP_BASIS not planned not planned SP14 not planned SP9 SP2


SQL Monitor Features

Availability (4/5)

Feature Details/Comments Availability (SAP_BASIS Version)

Full coverage of all ABAP DB operations

Load Table Buffer, EXPORT/IMPORT, COMMIT/ROLLBACK WORK, AMDP 740 SP5

Total Work Process Time of Requests

740 SP5

ABAP Code Fragment

740 SP5

SQL Monitor Snapshots

Upload, Display, Manage 740 SP5

Time Series

Default, can be switched off. Legacy data has to be deleted before activation 740 SP8

Integrated SQL Trace

Activate for selected SQL statements 740 SP8



Further remarks

The current SAP Support Package Schedule can be found here: https://service.sap.com/~sapidb/011000358700000294692004E

SAP note 1912445 contains the main recommendations for the topic Custom Code for SAP HANA

SAP note 1885926 contains the main recommendations for the topic SQL Monitor

Appendix B Static Checks for NW 740 SP2 SP4


Prepare your custom code for SAP HANA Functional corrections for existing ABAP code static checks II (before 740 SP5)

Search problematic statementwithout ORDER BY check Preferred check to find code which relies on sorted DB content searches for statements like READ BINARY SEARCH, DELETE

ADJACENT DUPLICATES, accessing unsorted DB content works for transparent and pool/cluster DB tables low false positive ratio

Depooling/Declustering: Search forw/o ORDER BY check Searches for SELECT without ORDER BY but no

further analysis of the data flow is done

works only for pool/cluster DB tables (before and after depooling/declustering)

high false positive ratio

Recommendation

Run both checks for your custom code Concentrate on findings (priority 1 and 2) of the Search problematic

statementwithout ORDER BY check Treat findings of Depooling/Declustering: check with reduced priority


2014 SAP SE or an SAP affiliate company. All rights reserved.

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an

SAP affiliate company.

SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE

(or an SAP affiliate company) in Germany and other countries. Please see http://global12.sap.com/corporate-en/legal/copyright/index.epx for additional trademark

information and notices.

Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors.

National product specifications may vary.

These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP SE or its

affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP SE or

SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing

herein should be construed as constituting an additional warranty.

In particular, SAP SE or its affiliated companies have no obligation to pursue any course of business outlined in this document or any related presentation, or to develop or

release any functionality mentioned therein. This document, or any related presentation, and SAP SEs or its affiliated companies strategy and possible future developments, products, and/or platform directions and functionality are all subject to change and may be changed by SAP SE or its affiliated companies at any time for

any reason without notice. The information in this document is not a commitment, promise, or legal obligation to deliver any material, code, or functionality. All forward-

looking statements are subject to various risks and uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place

undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.

dev204_15495_gebhardtb_1

Documents

sap hana sap hana database

sap se

sap hana impact

abap sap business suite

sap affiliate company

public sap business

sap hana abap test cockpit

abap code changes