To develop ABAP applications for SAP HANA, it’s essential to have a basic knowledge of the SAP NetWeaver AS ABAP architecture—and especially Open SQL—as well as the corresponding development tools. Moreover, native database access takes on greater importance when working with an SAP HANA database.

3Database Programming Using SAP NetWeaver Application Server ABAP

When using ABAP in combination with SAP HANA, database accesses from ABAP programs play a decisive role; after all, they are the interface between application and data. The main difference between SAP HANA and traditional databases is the available set of queries and operations that you can execute on the existing data.

This chapter introduces database programming in ABAP and, in particular, explains the specific aspects that are significant for development on SAP HANA irrespective of the ABAP version. While the basic ABAP database architecture for SAP HANA doesn’t differ from other SAP-supported database systems, we’ll describe the options (and limitations) of classic ABAP database programming in this chapter. Chapter 6 will introduce some additional technologies that require ABAP 7.4 (with a sufficiently high support package level).

Let’s start by contemplating a simple ABAP program as shown in Listing 3.1.

DATA: wa TYPE scarr.
SELECT-OPTIONS: carrier FOR wa-carrid.
SELECT * FROM scarr INTO wa WHERE carrid IN carrier.
WRITE: / wa-carrid , wa-carrname.
ENDSELECT.

Listing 3.1Simple Database Access from ABAP via Open SQL

Based on a selection of codes for airlines (e.g., “LH”), the full names of these airlines (e.g., “LH Lufthansa”) are displayed.

This simple example shows some fundamental qualities of database access from ABAP that aren’t available in this form in most other development environments:

In this chapter, we’ll first describe the technical aspects of a connection between the SAP NetWeaver AS ABAP and the database. We’ll then explain how ABAP developers can access the database efficiently based on a few examples. And, finally, we’ll describe tools that can be used when developing database accesses.

Two components play an important role when accessing the database from ABAP:

Because database access is of paramount importance in the context of SAP HANA and is enhanced by some new aspects, it’s important to understand fully the interaction of ABAP and the SAP HANA database. Experienced ABAP developers may already be familiar with some of the information provided in this chapter.

For the examples throughout this book, we used a model that is available in every ABAP system—the well-known SAP NetWeaver flight data model (SFLIGHT). Appendix A introduces the technical details and business aspects of this application and describes the database tables and their relationships. This chapter only uses the tables SCARR (airlines), SFLIGHT (flights), SCUSTOM (flight customers), and SBOOK (flight bookings).

3.1SAP NetWeaver Application Server ABAP Architecture

The database plays an integral role for the ABAP AS. This server can’t be operated without a running database. Ultimately, all technical and business data (except for a few configuration and log files of the server components) are database contents in AS ABAP; even the ABAP source code and other development objects are maintained in database tables.

In this section, you’ll find a short description of the basic structure of an ABAP system. An ABAP system can comprise one or several application servers. Several application servers are deployed for a scale-out scenario to provide high availability and avoid overload situations. To coordinate several application servers, central services such as the start service, the message server (load distribution), or the enqueue server (lock management) are available.

Requests received on a server are forwarded to a work process by the dispatcher, where the request in question is processed by an ABAP program. There are different types of work processes, such as dialog (running ABAP programs in the dialog), update (executing update modules in case of a COMMIT WORK), background (running batch jobs), or enqueue (executing lock operations to synchronize database operations). You can configure the number of available work processes, which depends on the hardware resources and scenario requirements (e.g., the number of concurrent users).

ABAP programs are executed by the runtime environment in the ABAP kernel. Within the kernel, several components are in use when executing ABAP statements; not all of those components will be explained in detail within this book, but here are a few sample scenarios:

Database access using the SELECT statement is explained in detail in the next section. In the next section as well, Figure 3.1 shows the basic server architecture of an ABAP system. Further details on installation and operation of the components are available in SAP NetWeaver AS ABAP—System Administration by Frank Föse, Sigrid Hagemann, and Liane Will, (SAP PRESS, 2012).

3.1.1Database Interface

This section describes in detail how the ABAP application server accesses the database. In this context, there are three important components:

Every ABAP work process is connected to the database via an active connection. If the database is accessed from an ABAP program, the DBI in the ABAP kernel is responsible for the first processing steps. The DBI is independent of the concrete database system.

AS ABAP Architecture

Figure 3.1AS ABAP Architecture

One of its main responsibilities is translating Open SQL (see Section 3.2.2) into native SQL, which is then passed to the database via the DBSL (and the database driver).

In addition to processing SQL queries, the DBI provides the following functions:

There is a specific library for every database system supported by SAP: the DBSL. This library is linked dynamically to the ABAP kernel and integrates the respective database driver for the technical connection to the database.

You can install several of those libraries on an application server. This makes it possible to establish connections to other databases besides the ABAP system database. This is important in the context of SAP HANA when implementing the side-by-side scenarios described in Chapter 1, Section 1.4. The technical aspects of such secondary connections are described in more detail in Section 3.2.5. The prerequisites and steps for installing the SAP HANA DBSL on an existing system are described in SAP Note 1597627. Figure 3.2 shows how the DBI, DBSL, and database driver interact.

Interaction of the DBI, DBSL, and Database Client

Figure 3.2Interaction of the DBI, DBSL, and Database Client

3.1.2Role of the Database for the ABAP Application Server

AS ABAP stores all data in exactly one specific schema within the database catalog. This schema is also referred to as the system schema or ABAP schema. You can think of a schema as a kind of namespace within the database. In traditional ABAP development, the database schema is irrelevant. In the context of SAP HANA, however, the schema is relevant for two reasons: First, when replicating tables to SAP HANA, the replicated data are often stored in different database schemas to separate them from the system data. Second, a series of technical schemas in SAP HANA play an important role in native development in SAP HANA (see Chapter 2, Section 2.4.3, and Chapter 4).

As mentioned already, every ABAP work process is connected to the database. For the standard database connection, a technical database user is used.

[Ex]ABAP Schema and Technical Database User

The name of the ABAP schema is usually composed of the system ID (SID) and the prefix “SAP”. The default schema name of the ABAP system “NSP” would be SAPNSP, for example. ABAP tables such as the SFLIGHT table can thus be addressed in the database catalog using SAPNSP. SFLIGHT.

This schema also comprises a database user SAPNSP, which is used by the AS ABAP to establish the standard database connection.

Every database uses a transaction concept to consider the consequences of interactions as a logic unit (logical unit of work [LUW]) and guarantee atomicity, consistency, isolation, durability (ACID) qualities for this unit. Database transactions are usually relatively short-lived operations and are always focused on the technical consistency of table contents (during parallel access, in error situations, etc.). Business transactions (e.g., creating a new customer in the system), on the other hand, are often associated with a longer lifetime and additional requirements with regard to data consistency because the data must also be consistent from a business perspective. The transaction concept of the database is hardly suitable to meet these additional requirements.

To ensure consistent changes to data models in business applications, ABAP provides the LUW concept. With this concept, changes to data records are collected first and are then, at a defined point in time, either written to the database by a COMMIT WORK statement or discarded by means of a ROLLBACK WORK. By collecting changes, changes in transactions that comprise several dialog steps or even several application servers can be bundled (see Section 3.2.2). Because there is currently no equivalent concept in SAP HANA, only the transaction concept of the database can be used for native implementations in SAP HANA (e.g., via SQLScript). For recommendations on this topic, please read Chapter 14.

Physical locks are used automatically by every relational database system to synchronize parallel changes to table contents. In addition, AS ABAP uses a logical lock concept that is focused on business aspects. With this concept, lock objects can be used to indicate that a data record is unavailable for certain accesses (e.g., for changes) for a certain time period. Locks can be created or queried at runtime using special function modules that manage lock entries via the enqueue work process.

For example, when booking a flight, it isn’t possible to perform another booking for the same flight to make sure it’s not overbooked. Because these logical locks don’t lead to physical locks on the database (so tables can technically still be changed), the effectiveness of the locks is based on conventions and guidelines for application development. These aspects must also be considered when modifying ABAP tables outside the context of an ABAP program (e.g., with SQLScript in the case of SAP HANA).

3.1.3Data Types

As an ABAP developer, you may not have given data types a lot of thought in the past and simply used the types that were available. In many situations, however, complex conversions and interpretations are performed in the background that can lead to unexpected results if they aren’t used properly.

Before you learn about the different types of systems and their properties, we’ll introduce the topic briefly using a few examples. We start in Listing 3.2 with a simple database access using Open SQL.

DATA: lv_carrier TYPE string.
SELECT SINGLE carrname FROM scarr INTO lv_carrier
WHERE carrid = 'LH'.

Listing 3.2Implicit Data Type Conversions

This simple ABAP program already uses different data types and conversions. The CARRNAME column of table SCARR is based on the S_CARRNAME data element in the DDIC, which is defined as type CHAR (i.e., string) with a length of 20. In the database, the data type of this column is NVARCHAR(20) (NVARCHAR is a string of variable length). A selection is made into an ABAP variable of type String; in addition, a constant (literal) LH is used in the WHERE clause, which is checked against the CARRID column of type CHAR(3). The result of this selection is the name of the airline “Lufthansa.” If you now replace the filter condition in Listing 3.2 with the expression WHERE carrid = 'LH abcd', the result may not be obvious at first glance. Because the CARRID field contains only three characters, the record is found in this case as well.

For character-type data types with special semantics (e.g., a date or a number as a string), there are some aspects that need to be considered. Listing 3.3, for instance, determines the names of all passengers who booked a flight within the past 30 days (FLDATE column of type DATS) and received a discount of more than 20% (DISCOUNT column of type NUMC).

DATA: lv_date TYPE d,
lv_name TYPE string.

lv_date = sy-datlo - 30.
SELECT DISTINCT name FROM sbook AS b
INNER JOIN scustom AS c ON b~customid = c~id
INTO lv_name
WHERE fldate > lv_date AND fldate <= sy-datlo
AND c~discount >= '20'.

WRITE: / lv_name.
ENDSELECT.

Listing 3.3Relevance of Semantic Properties of Data Types

When calculating a time difference in days or handling the string “20” as a number for the discount, this depends on the semantics of the data types. If you execute the corresponding expression in Native SQL via the SQL console in SAP HANA Studio, for example, you’ll get different results.

For the code pushdown paradigm presented in Chapter 1, Section 1.5.2, where certain calculations are moved to the database, it’s important that the data are semantically treated and understood identically; otherwise, the calculations may lead to wrong results. This is relevant, for instance, for the rounding behavior and internationalization aspects. You must especially make sure that there are no unexpected effects after changing an existing program to improve performance.

[»]SAP HANA Supports Only Unicode

Another aspect of handling text data types is the technical encoding of characters using code pages. SAP HANA only supports Unicode installations. Non-Unicode installations must be converted to Unicode before migrating them to SAP HANA. The differences between Unicode and non-Unicode systems are beyond the scope of this book.

We’ll now describe the different type systems. As an ABAP developer, you’re probably already familiar with the type system of the ABAP language and the DDIC, but you may have paid little attention to the mapping of those types to the database’s type system in the past.

The type system of the ABAP language defines the data types that can be used in ABAP programming. It’s designed in such a way that it can be mapped consistently to the supported operating systems for the application server. The following built-in types form the basic structure of the ABAP type system:

The type system of the DDIC defines which data types can be used in structures, tables, and so on, in the DDIC. It’s defined in such a way that it can be uniquely mapped to all supported database systems via SQL. This is the primary type system for database accesses from ABAP. The mapping of the DDIC types to the basic types of the ABAP language is described in Table 3.1.

The internal type system of the database defines the possible column types for tables and the corresponding operations. It’s the primary type system for queries or implementations in the database (e.g., by means of database procedures). Each database system uses slightly different data types or treats data types slightly differently.

Table 3.1 shows the mapping of DDIC types to ABAP types. The (fixed or variable) length of the corresponding ABAP type is indicated in parentheses.

Dictionary Type

Description

ABAP Type

Example

ACCP

Accounting period

N(6)

'201310'

CHAR

String

C(n)

'ABAP'

CLNT

Client

C(3)

'000'

CUKY

Currency key

C(5)

'EUR'

CURR

Currency field

P(n)

'01012000'

DATS

Date

D

'01012000'

DEC

Calculation/amount field

P(n)

100.20

DF16_RAW

Decimal floating point number (normalized; 16 digits)

decfloat16

100.20

DF16_SCL

Decimal floating point number (scaled; 16 digits)

decfloat16

100.20

DF34_RAW

Decimal floating point number (normalized; 34 digits)

decfloat34

100.20

DF34_SCL

Decimal floating point number (scaled; 34 digits)

decfloat34

100.20

FLTP

Floating point number

F(8)

3.1415926

INT1

1-byte integer

internal

1

INT2

2-byte integer

internal

100

INT4

4-byte integer

I

1.000

LANG

Language

C(1)

'D'

LCHR

Long character string

C(m)

'ABAP is …'

LRAW

Long byte string

X(m)

F4 8F BF

NUMC

Numeric text

N(m)

'123'

QUAN

Quantity field

P(n)

100

RAW

Byte sequence

X(m)

F48FBFBF

RAWSTRING

Byte sequence

XSTRING

272927450108018F8F8F8F

SSTRING

String

STRING

'ABAP'

STRING

String

STRING

'ABAP is …'

TIMS

Time

T

'123000'

UNIT

Unit key

C(m)

'KG'

Table 3.1Mapping of ABAP Data Dictionary Types and ABAP Types

The example in Figure 3.3 shows the mapping of the DDIC types to SAP HANA data types (based on a custom technical table that uses most of the native DDIC types). As described in Chapter 2, Section 2.4.3, you can display the structure of a database table in SAP HANA Studio by double-clicking a table in the database catalog.

In addition to the SQL data type, this table also shows the specific data type used in the column store in SAP HANA. However, this type plays only a minor role for ABAP development on SAP HANA.

Mapping of ABAP Data Dictionary Types to SAP HANA Types

Figure 3.3Mapping of ABAP Data Dictionary Types to SAP HANA Types

It’s important to note that there is no representation of the NULL value from SQL in the ABAP type system. However, there is an initial value for every ABAP and DDIC data type, for example, an empty string for string types or 0 for numeric types. This is particularly relevant for certain join variants (outer joins), as you’ll see in Section 3.2.2.

Certain data types with a binary representation usually can’t be used directly in implementations in SAP HANA. These are, for example, floating point numbers of type DF16_RAW and DF16_SCL (as well as the corresponding types with a length of 34). Another example is the data cluster in ABAP, which is a special table type allowing you to read and write any kind of data record via the ABAP commands EXPORT TO DATABASE and IMPORT FROM DATABASE. The associated data is stored in the database in a column of type LRAW in a proprietary format that can only be unpacked via the ABAP kernel. You must take these aspects into account when considering moving parts of the logic to the database.