Archive by Author | thomasjungsap100

Enterprise Geeks Podcast – Code Jam and SAPPHIRE/ASUG Annual Conference Prep

We are ready to rock to Van Halen at SAPPHIRE in our kilts

Season 4 Episode 13

Its luck episode number 13 of season 4 — if only we could have done the recording last Friday the 13th.  After several weeks of travel, we finally got most of the gang together to record a podcast.  We discuss the SAP Database and Mobility press conference from the previous week as well as upcoming plans for SAPPHIRE/ASUG Annual Conference. Craig also announces the new Code Jam events and talks about his vision for where this series of events will be headed.

Running Time: 45:23

If you have questions, suggestions, or topic ideas for future episodes, give us a shout here.

Developer’s Journal: ABAP/HANA Connectivity via Secondary Database Connection

Introduction

In this first edition of this HANA Developer’s Journey I barely scratched the surface on some of the ways which a developer might begin their transition into the HANA world. Today I want to describe a scenario I’ve been studying quite a lot in the past few days: accessing HANA from ABAP in the current state.  By this, I mean what can be built today.  We all know that SAP has some exciting plans for ABAP specific functionality on top of HANA, but what everyone might not know is how much can be done today when HANA runs as a secondary database for your current ABAP based systems.  This is exactly how SAP is building the current HANA Accelerators, so it’s worth taking a little time to study how these are built and what development options within the ABAP environment support this scenario.

HANA as a Secondary Database

The scenario I’m describing is one that is quite common right now for HANA implementations.  You install HANA as a secondary database instead of a replacement for your current database.  You then use replication to move a copy of the data to the HANA system. Your ABAP applications can then be accelerated by reading data from the HANA copy instead of the local database. Throughout the rest of this blog I want to discuss the technical options for how you can perform that accelerated read.

ABAP Secondary Database Connection

ABAP has long had the ability to make a secondary database connection.  This allows ABAP programs to access a database system other than the local database. This secondary database connection can even be of a completely different DBMS vendor type. This functionality is extended to support SAP HANA for all the NetWeaver release levels from 7.00 and beyond. Service Note 1517236  (for SAP Internal) or Note  1597627  (for everyone) lists the preconditions and technical steps for connection to HANA systems and should always be the master guide for these preconditions, however I will summarize the current state at the time of publication of this blog.

Preconditions

  • SAP HANA Client is installed on each ABAP Application Server. ABAP Application Server Operating System must support the HANA Client (check Platform Availability Matrix for supported operating systems).
  • SAP HANA DBSL is installed (this is the Database specific library which is part of the ABAP Kernel)
  • The SAP HANA DBSL is only available for the ABAP Kernel 7.20
    • Kernel 7.20 is already the kernel for NetWeaver 7.02, 7.03, 7.20, 7.30 and 7.31
    • Kernel 7.20 is backward compatible and can also be applied to NetWeaver 7.00, 7.01, 7.10, and 7.11
  • Your ABAP system must be Unicode

Next, your ABAP system must be configured to connect to this alternative database. You have one central location where you maintain the database connection string, username and password.  Your applications then only need to specify the configuration key for the database making the connection information application independent.

This configuration can be done via table maintenance (Transaction SM30) for table DBCON. From the configuration screen you supply the DBMS type (HDB for HANA), the user name and password you want to use for all connections and the connection string. Be sure to include the port number for HANA systems. It should be 3<Instance Number>15. So if your HANA Database was instance 01, the port would be 30115.

DBCON can also be maintained via transaction DBACOCKPIT. Ultimately you end up with the same entry information as DBCON, but you get a little more information (such as the default Schema) and you can test the connection information from here.

 

Secondary Database Connection Via Open SQL

The easiest solution for performing SQL operations from ABAP to your secondary database connection is to use the same Open SQL statements which ABAP developers are already familiar with. If you supply the additional syntax of CONNECTION (dbcon), you can force the Open SQL statement to be performed against the alternative database connection.

For instance, let’s take a simple Select and perform it against our HANA database:

  SELECT * FROM sflight CONNECTION ('AB1')
    INTO TABLE lt_sflight
   WHERE carrid = 'LH'.

The advantage of this approach is in its simplicity.  With one minor addition to existing SQL Statements you can instead redirect your operation to HANA. The downside is that the table or view you are accessing must exist in the ABAP Data Dictionary. That isn’t a huge problem for this Accelerator scenario considering the data all resides in the local ABAP DBMS and gets replicated to HANA. In this situation we will always have local copies of the tables in the ABAP Data Dictionary.  This does mean that you can’t access HANA specific artifacts like Analytic Views or Database Procedures. You also couldn’t access any tables which use HANA as their own/primary persistence.

Secondary Database Connection Via Native SQL

ABAP also has the ability to utilize Native SQL. In this situation you write you database specific SQL statements.  This allows you to access tables and other artifacts which only exist in the underlying database.  There is also syntax in Native SQL to allow you to call Database Procedures.  If we take the example from above, we can rewrite it using Native SQL:

EXEC SQL.
    connect to 'AB1' as 'AB1'
  ENDEXEC.
  EXEC SQL.
    open dbcur for select * from sflight where mandt = :sy-mandt and carrid = 'LH'
  ENDEXEC.
  DO.
    EXEC SQL.
      fetch next dbcur into :ls_sflight
    ENDEXEC.
    IF sy-subrc NE 0.
      EXIT.
    ELSE.
      APPEND ls_sflight TO lt_sflight.
    ENDIF.
  ENDDO.
  EXEC SQL.
    close dbcur
  ENDEXEC.
  EXEC SQL.
    disconnect 'AB1'
  ENDEXEC.

Its certainly more code than the Open SQL option and a little less elegant because we are working with database cursors to bring back an array of data.  However the upside is access to features we wouldn’t have otherwise. For example I can insert data into a HANA table and use the HANA database sequence for the number range or built in database functions like now().

    EXEC SQL.
      insert into "REALREAL"."realreal.db/ORDER_HEADER"
       values("REALREAL"."realreal.db/ORDER_SEQ".NEXTVAL,
                   :lv_date,:lv_buyer,:lv_processor,:lv_amount,now() )
    ENDEXEC.
    EXEC SQL.
      insert into "REALREAL"."realreal.db/ORDER_ITEM" values((select max(ORDER_KEY)
        from "REALREAL"."realreal.db/ORDER_HEADER"),0,:lv_product,:lv_quantity,:lv_amount)
    ENDEXEC.

The other disadvantage to Native SQL via EXEC SQL is that there are little to no syntax checks on the SQL statements which you create. Errors aren’t caught until runtime and can lead to short dumps if the exceptions aren’t properly handled.  This makes testing absolutely essential.

Secondary Database Connection via Native SQL – ADBC

There is a third option that provides the benefits of the Native SQL connection via EXEC SQL, but also improves on some of the limitations.  This is the concept of ADBC – ABAP Database Connectivity.  Basically it is a series of classes (CL_SQL*) which simplify and abstract the EXEC SQL blocks. For example we could once again rewrite our SELECT * FROM SFLIGHT example:

****Create the SQL Connection and pass in the DBCON ID to state which Database Connection will be used
  DATA lr_sql TYPE REF TO cl_sql_statement.
  CREATE OBJECT lr_sql
    EXPORTING
      con_ref = cl_sql_connection=>get_connection( 'AB1' ).

****Execute a query, passing in the query string and receiving a result set object
  DATA lr_result TYPE REF TO cl_sql_result_set.
  lr_result = lr_sql->execute_query(
    |SELECT * FROM SFLIGHT WHERE MANDT = { sy-mandt } AND CARRID = 'LH'| ).

****All data (parameters in, results sets back) is done via data references
  DATA lr_sflight TYPE REF TO data.
  GET REFERENCE OF lt_sflight INTO lr_sflight.

****Get the result data set back into our ABAP internal table
  lr_result->set_param_table( lr_sflight ).
  lr_result->next_package( ).
  lr_result->close( ).

Here we at least remove the step-wise processing of the Database Cursor and instead read an entire package of data back into our internal table at once.  By default the initial package size will return all resulting records, but you can also specify any package size you wish thereby tuning processing for large return result sets.  Most importantly for HANA situations, however, is that ADBC also lets you access non-Data Dictionary artifacts including HANA Stored Procedures.  Given the advantages of ADBC over EXEC SQL, it is SAP’s recommendation that you always try to use the ADBC class based interfaces.

Closing

This is really just the beginning of what you could with this Accelerator approach to ABAP integration into SAP HANA. I’ve used very simplistic SQL statements in my examples on purpose so that I could instead focus on the details of how the technical integration works.  However, the real power comes when you execute more powerful statements (SELECT SUM … GROUP BY), access HANA specific artifacts (like OLAP Views upon OLTP tables), or database procedures.  These are all topics which I will explore more in future editions of this blog.

Shout out to BCO6181

While at the Mastering SAP Technologies event in Sydney last week, loyal Enterprise Geek Tony de Thomasis (otherwise known as c821311 on Twitter) asked a few of us to give some advice to his students in BCO6181. It sounds like Tony has a great group of students which are already well on their way to creating the next generation of Enterprise Geeks.

Enterprise Geeks Podcast – Before We Hit The Road Edition

After what Craig saw at CeBIT, could thought controlled gaming be right around the corner?

Season 4 Episode 12

After a couple of weeks off our podcast schedule we return to record again before several of us hit a bad travel schedule.  We hit major topics around the iPad 3 announcement, Win8 preview, and CeBIT conference. If you want a little more SAP centric topics, don’t worry. Over the next few weeks Craig and I will be recording podcasts from DKOM and Mastering SAP Technologies which promise to just be dripping with SAP only content.

Running Time: 56:58

If you have questions, suggestions, or topic ideas for future episodes, give us a shout here.

Enterprise Geeks – Biggest Loser Style Contest

Thanks to a series of colds that ran through our group last week and some heavy travel and meeting schedules this week, we haven’t been able to quite keep on our usual podcast recording schedule. I thought I would take this slow down in our regular schedule to blog about a great idea which was brought up by KK Ramamoorthy.  We had a podcast earlier this year were we discussed the challenge of staying fit while working in the IT field. In particular, those of us who travel frequently have an added challenge. KK suggested that we run a Biggest Loser style contest sponsored by the Enterprise Geeks.  We believe anything that helps our fellow Enterprise Geeks get healthier is worthwhile and KK’s suggestion adds an extra layer of fun to the challenge.

The good part first: So the prize for the Enterprise Geek who loses the most weight, is your dinner free at the Enterprise Geeks dinner which takes place during TechEd. If you aren’t able to attend TechEd or live in a location where we won’t have a Enterprise Geeks dinner, we will come up with some suitable alternative prize. How better to reward someone for losing weight than to buy their way into an all-you-can-eat buffet? 🙂
IMG_7475

The Rules: we will use My Fitness Pal to track weight loss.  You don’t have to put all your details into My Fitness Pal, but you need at least a beginning weigh in and a final weigh in. If you want to participate, just friend me (ABAPFreak) on My Fitness Pal. The person who loses the most weight between the date of the posting of this blog and the Friday before the start of TechEd Las Vegas (October 12th 2012) wins.  No weigh ins on site will be required.  We trust that all our Enterprise Geeks out there will be honest with their weigh ins online.

Hopefully this adds a little motivation to everyone’s drive to get healthy this year! Track what you eat, eat a little less, exercise a little more.  It doesn’t take much to make a start.  Who knows. You might find eternal glory in being the first Enterprise Geeks Biggest Loser!

Developer’s Journal: First Steps into the SAP HANA World

Introduction

A long time ago when I first started blogging on SDN, I used to write frequently in the style of a developer journal. I was working for a customer and therefore able to just share my experiences as I worked on projects and learned new techniques. My goal with this series of blog postings is to return to that style but with a new focus on a journey to explore the new and exciting world of SAP HANA.

At the beginning of the year, I moved to the SAP HANA Product Management team and I am responsible for the developer persona for SAP HANA. In particular I focus on tools and techniques developers will need for the upcoming wave of transactional style applications for SAP HANA.

I come from an ABAP developer background having worked primarily on ERP; therefore my first impressions are to draw correlations back to what I understand from the ABAP development environment and to begin to analyze how development with HANA changes so many of the assumptions and approaches that ABAP developers have.

Transition Closer to the Database

My first thought after a few days working with SAP HANA is that I needed to seriously brush up on my SQL skills. Of course I have plenty of experience with SQL, but as an ABAP developer we tend to shy away from deeper aspects of SQL in favor of processing the data on the application server in ABAP. For ABAP developers reading this, when was the last time you used a sub-query or even a join in ABAP? Or even a select sum? As ABAP developers, we are taught from early on to abstract the database as much as possible and we tend to trust the processing on the application server where we have total control instead of the “black box” of the dbms. This situation has only been compounded in recent years as we have a larger number of tools in ABAP which will generate the SQL for us.

This approach has served ABAP developers well for many years. Let’s take the typical situation of loading supporting details from a foreign key table. In this case we want to load all flight details from SFLIGHT and also load the carrier details from SCARR. In ABAP we could of course write an inner join:

However many ABAP developers would take an alternative approach where they perform the join in memory on the application server via internal tables:

This approach can be especially beneficial when combined with the concept of ABAP table buffering. Keep in mind that I’m comparing developer design patterns here, not the actual technical merits of my specific examples. On my system the datasets weren’t actually large enough to show any statistically relevant performance different between these two approaches.

Now if we put SAP HANA into the mixture, how would the developer approach change? In HANA the developer should strive to push more of the processing into the database, but the question might be why?

Much of the focus on HANA is that it is an in-memory database. I think it’s pretty easy for most any developer to see the advantage of all your data being in fast memory as opposed to relatively slow disk based storage. However if this were the only advantage, we wouldn’t see a huge difference between processing in ABAP. After all ABAP has full table buffering. Ignoring the cost of updates, if we were to buffer both SFLIGHT and SCARR our ABAP table loop join would be pretty fast, but it still wouldn’t be as fast as HANA.

The other key points of HANA’s architecture is that in addition to being in-memory; it is also designed for columnar storage and for parallel processing. In the ABAP table loop, each record in the table has to be processed sequentially one record at a time. The current version of ABAP statements such as these just aren’t designed for parallel processing. Instead ABAP leverages multiple cores/CPUs by running different user sessions in separate work processes. HANA on the other hand has the potential to parallelize blocks of data within a single request. The fact that the data is all in memory only further supports this parallelization by making access from multiple CPUs more useful since data can be “fed” to the CPUs that much faster. After all parallization isn’t useful if the CPUs spend most of their cycles waiting on data to process.

The other technical aspect at play is the columnar architecture of SAP HANA. When a table is stored columnar, all data for a single column is stored together in memory. Row storage (as even ABAP internal tables are processed), places data a row at time in memory.

This means that for the join condition the CARRID column in each table can be scanned faster because of the arrangement of data. Scans over unneeded data in memory doesn’t have nearly the cost of performing the same operation on disk (because of the need to wait for platter rotation) but there is a cost all the same. Storing the data columnar reduces that cost when performing operations which scan one or more columns as well as optimizing compression routines.

For these reasons, developers (and especially ABAP developers) will need to begin to re-think their applications designs. Although SAP has made statements about having SAP HANA running as the database system for the ERP, to extract the maximum benefit of HANA we will also need to push more of the processing from ABAP down into the database. This will mean ABAP developers writing more SQL and interacting more often with the underlying database. The database will no longer be a “bit bucket” to be minimized and abstracted, but instead another tool in the developers’ toolset to be fully leveraged. Even the developer tools for HANA and ABAP will move closer together (but that’s a topic for another day).

With that change in direction in mind, I started reading some books on SQL this week. I want to grow my SQL skills beyond what is required in the typical ABAP environment as well as refresh my memory on things that can be done in SQL but perhaps I’ve not touched in a number of years. Right now I’m working through the O’Reilly Learning SQL 2nd Edition by Alan Beaulieu. I’ve found that I can study the SQL specification of HANA all day, but recreating exercises forces me to really use and think through the SQL usage. The book I’m currently studying actually lists all of its SQL examples formatted for MySQL. One of the more interesting aspects of this exercise has been adjusting these examples to run within SAP HANA and more importantly changing some of them to be better optimized for Columnar and In-Memory. I think I’m actually learning more by tweaking examples and seeing what happens than any other aspect.

What’s Next

There’s actually lots of aspects of HANA exploration that I can’t talk about yet. While learning the basics and mapping ABAP development aspects onto a future that includes HANA, I also get to work with functionality which is still in early stages of development. That said, I will try and share as much as I can via this blog over time. Already in the next installment I would like to focus on my next task for exploration – SQLScript.

 

 

 

Enterprise Geeks Podcast – Programmers’ Valentines

If that doesn’t say “I Love You”, I don’t know what does

Season 4 Episode 11

Warning: After several weeks of no technical problems with Pamela Call Recorder for Skype, the software has decided to go crazy once again. This podcast contains a slight skipping echo on my voice only. Unfortunately since Craig couldn’t join us this week, we didn’t get a backup recording and must post with this technical problem.  I apologize for the technical snafu but figured the only other alternative was to not post at all this week.  If anyone has suggestions how to fix this in Pamela, I’m certainly listening.

  • Dice Salary Survey puts ABAP at the top of the list
  • NetWeaver Release Levels – we explain the difference between NetWeaver 7.01, 7.02, 7.03, 7.10, 7.20, 7.30 and 7.31, plus talk about the future beyond NetWeaver Enhancement Packages
  • Programmers’ Valentine’s Day – using the comparison between relationships and programming languages, we each share stories about our first programming loves
    • A little impromptu discussion broke out about JavaScript in the server side as used in the SAP HANA XS Engine
  • Nvidia 3D Vision Glass – I give a quick review of the gift I got from my Valentine’s Sweatheart

Running Time: 53:07

If you have questions, suggestions, or topic ideas for future episodes, give us a shout here.