IBM DB2: 'Who is who' in the SAP environment

[johannes_heinrich]

Database and Operating System compose the foundation on which all SAP software is running. We therefore often talk about the (DB-, OS-)'platform' of an SAP system. Names of database management systems, like names for any product, change over time. In this post I'd like to help you understand the official names for the IBM DB2 databases as well as naming conventions used in the SAP environment. Based on this I will also present an example for database dependent coding in ABAP as well as in Java.

The IBM DB2 product family

Talking about DB2 we need to consider in fact three products with a different codebase. There is one DB2 running on mainframes using z/OS (formerly known as OS/390), one DB2 running on the operating system i5 (aka as OS/400) and one DB2 for Linux, UNIX and Windows.  The current official names for these IBM products are:

• DB2 for z/OS
• DB2 for i5/OS
• DB2 9 for Linux UNIX and Windows ('Linux UNIX and Windows' is sometimes abbreviated as 'LUW')

All of these products were renamed in the past. For DB2 9 for Linux Unix and Windows in particular the names 'DB2/6000', 'DB2 Common Server' and 'DB2 Universal Database' (or 'DB2 UDB') may sound familiar to you. Note that with the latest release a name change took place and the term 'UDB' was dropped. So IBM UDB Version 8.2 became DB2 9. As with every name change, it takes some time until it arrives in all the written material. This means that you will still find the term 'IBM UDB' in a lot of places.

Database platform names in SAP systems

The SAP basis offers very good abstractions of the underlying operating and database system. If you do your development in OpenSQL which is available for ABAP and Java you don't have to care about specifics of the DBMS. Your coding will be portable between all SAP supported platforms. In most cases this works perfect and you should stay with OpenSQL. Seldom it is necessary to exploit a database specific feature - something which is either available just on one database or on more than one database, but accessed each time in a different way. Typically everything which affiliated to the Database Meta data belongs into this category, although in some cases java.sql.DatabaseMetaData can help here on the WebAS Java. Anyway, the first step if you need to issue a database dependent SQL statement is to find out on which database system your program is actually running, and secondly you need a way to send an SQL statement directly to the database, bypassing OpenSQL.
Let's take a simple example and assume we'd like to find out programmatically on which version of DB2 for Linux UNIX and Windows and on which fixpack level we are running.

For ABAP the following piece of code will do this for us:

...DATA: release(128) TYPE c.DATA: fixpack TYPE i.IF sy-dbsys = 'DB6'.  EXEC SQL.    SELECT service_level, fixpack_num    INTO :release, :fixpack    FROM TABLE(SYSPROC.ENV_GET_INST_INFO()) AS instinfo  ENDEXEC.ENDIF.

The system field sy-dbsys contains a three letter abbreviation for the database system on which we are currently running. Defining such an abbreviation is an interesting task if you take into account that there are three different products named 'DB2'. Here is the outcome that can be understand best if you consider the historical context:

• 'DB2' is the three letter abbreviation for DB2 for z/OS.
• 'DB4' was chosen for DB2 for i5/OS. The '4' in DB4 was borrowed from AS/400, the midrange server product line on which this particular DB2 ('DB2/400' integrated into the operating system 'OS/400') was running at the time of developing the port.
• 'DB6' is the choice for DB2 for Linux UNIX and Windows. The '6' in DB6 was borrowed from RS/6000, IBM's server product line on which this DB2 was available on AIX at the time the development for this port started at SAP.

So that's the explanation why you will find 'DB6' in a lot of places at SAP (e.g. in SAP notes) when we talk about DB2 for Linux UNIX and Windows. See the table below for the abbreviations for all SAP supported platforms.

Back to our example: After checking that we are on the correct database platform we use a Native SQL statement which is send directly to the database. The user defined function (UDF) ENV_GET_INST_INFO  is used here to fetch the information about the DBMS release and fixpack number into the variables release and fixpack. Note that a native SQL statement should in general only be used after checking that the program runs on the expected database platform.

This looks easy in ABAP, but what about Java? Consider the following piece of code which will accomplish the same in a WebAS Java.

Connection conn;Statement stmt;ResultSet rs;String db2version;int db2fixpack;...if (NativeSQLAccess.getVendorID(conn) == NativeSQLAccess.VENDOR_DB2_UDB){ stmt = NativeSQLAccess.createNativeStatement(conn); rs = stmt.executeQuery("SELECT  service_level, fixpack_num FROM TABLE(SYSPROC.ENV_GET_INST_INFO()) AS instinfo"); rs.next(); db2version = rs.getString(1); db2fixpack = rs.getInt(2); rs.close(); stmt.close();}...

With the help of the static method getVendorID of the class com.sap.sql.NativeSQLAccess it is possible to find out on which database we are running. There are constants defined for all SAP supported DBMS. Assuming that conn is a normal OpenSQL connection retrieved from a DataSource we can get a native SQL Statement on this connection with the static createNativeStatement method. Acquiring a native prepared statement and a native callable statement is possible as well. Note that obtaining and closing the connection and exception handling is not shown in the code snippet above.

Here is a table with the ABAP and Java identifiers for the database systems supported by SAP.

Database System	ABAP: sy-dbsys	Java: NativeSQLAccess constant
DB2 for z/OS	DB2	VENDOR_DB2_UDB_OS390
DB2 for i5/OS	DB4	VENDOR_DB2_UDB_AS400
DB2 9 (for Linux UNIX and Windows)	DB6	VENDOR_DB2_UDB
Oracle	ORA	VENDOR_ORACLE
Microsoft SQL Server	MSS	VENDOR_MS_SQL_SERVER
MaxDB (aka SAP DB, Adabas)	ADA	VENDOR_SAPDB
Informix	INF	VENDOR_INFORMIX

As you can see the Java constants do not (and - due to compatibility reasons - can not) reflect the latest name changes. The same is of course true for the content of sy-dbsys in ABAP.

Summary

• With the latest release IBM DB2 for Linux Unix and Windows got a new name. The term 'UDB' was dropped, the product is now called 'DB2 9' (or 'DB2 9 for Linux UNIX and Windows') instead of 'DB2 UDB'.
• There are similar name changes for the other DB2 products.
• Three letter abbreviations are used inside ABAP to differentiate between the various database systems. Here DB2 9 is identified as 'DB6'. That's why 'DB6' appears in much of the material provided by SAP (e.g. in SAP notes).
• For Java, constants in com.sap.sql.NativeAccess are used to identify the current database platform.
• Native SQL can be used to send database specific SQL statements to the DBMS. Programs doing so are no longer portable. Every time before sending such statement you should check if the code runs on the database it is written for.