How To
Summary
This document explains how to set up the access to SAP CDS view entities in an ABAP system using SQL via ODBC.
Objective
As a prerequisite, the operating system needs to be a 64-bit Linux System with Db2 12.1 or newer. This document does not describe how to set up the ABAP ODBC functionality within the SAP application.
Environment
Linuxamd64
Steps
The example describes the steps to access a SAP CDS view. Based on this example, you can access SAP CDS views, that are defined for ODBC access. You need to adapt the nicknames for the federated access according to your setup.
Perform the following steps to enable the federated access:
Initial Setup Steps
1. Download SAP ODBC driver for ABAP and put it into the Db2 server that the Db2 instance user can access it.
Search for the components ODBC DRIVER FOR ABAP 1.0 and SAPCRYPTOLIB.
Choose a directory as your ODBC driver location and unpack the SAR files there.
(shriya@db2i-ShriyankaMohapatra-p9ul6-x86) /home/shriya/SAP_ABAP
$ pwd
/home/shriya/SAP_ABAP
(shriya@db2i-ShriyankaMohapatra-p9ul6-x86) /home/shriya/SAP_ABAP
$ ls
libicudata65.so libsapcrypto.so ODBC_driver_for_ABAP.so sapcrypto.lst
libicudecnumber.so libslcryptokernel.so ODBCforABAP.SAR sapcrypto.mf
libicui18n65.so libslcryptokernel.so.sha256 README.txt SAPCRYPTO.SAR
libicuuc65.so ODBC_driver_for_ABAP.h SAPCAR For detail information, please refer to Installation and Configuration of the ODBC Driver for ABAP (Data Federation Only) | SAP Help Portal or this SAP Blog: Using the ODBC driver for ABAP on Linux - SAP Community.
2. Copy the ABAP driver to db2 federation ODBC lib path
(shriya@db2i-ShriyankaMohapatra-p9ul6-x86) /home/shriya/SAP_ABAP
$ cp -r * /home/shriya/sqllib/federation/odbc/lib/3. Add the following section into ~/sqllib/cfg/odbc.ini, for example,
[ALY]
Driver=/home/shriya/sqllib/federation/odbc/lib/ODBC_driver_for_ABAP.so
HOST=25638c75-a54b-4658-8b04-3a1156f2c4f5.abap.eu10.hana.ondemand.com
PORT=443
CLIENT=100
LANGUAGE=EN
SERVICEPATH=/sap/bc/sql/sql1/sap/S_PRIVILEGED
TrustAll=true
CryptoLibrary=/home/shriya/sqllib/federation/odbc/lib/libsapcrypto.so
DriverUnicodeType=1
UidType=alias
TypeMap=semanticNote: Because the Data Direct driver manger libodbc.so is packaged with Db2, the SAP ODBC
driver for ABAP might not be fully compatible with the driver manager in terms of Unicode data
handling. In this case, you might get the error "Unicode converter buffer overflow".
If you remove DRIVERUNICODETYPE=1; the following error message is generated:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1822N Unexpected error code "HY000" received from data source "SERVER1".
Associated text and tokens are " Unicode converter buffer overflow".
SQLSTATE=560BD4. Add ODBCINI=~/sqllib/cfg/odbc.ini into ~/sqllib/cfg/db2dj.ini, for example,
$ cat db2dj.ini
DJX_ODBC_LIBRARY_PATH=/home/shriya/sqllib/federation/odbc/lib
DB2_FED_LIBPATH=/home/shriya/sqllib/federation/odbc/lib:/home/shriya/sqllib/federation/netezza/lib64
ODBCINST=/home/shriya/sqllib/cfg/odbcinst.ini
ODBCINI=/home/shriya/sqllib/cfg/odbc.ini
NZ_ODBC_INI_PATH=/home/shriya/sqllib/cfg
DB2LIBPATH=/home/shriya/sqllib/federation/odbc/lib
5. Add the following section into ~/sqllib/cfg/odbcinst.ini, for example,
[SAP_ABAP]
Driver=/home/shriya/sqllib/federation/odbc/lib/ODBC_driver_for_ABAP.so6. Set the db2 registry variable 'DB2LIBPATH'.
$ db2set DB2LIBPATH=/home/shriya/sqllib/federation/odbc/libIf the registry variable 'DB2LIBPATH' is not set correctly, the CREATE NICKNAME statement reports the error SQL1822N with the text "Specified driver could not be loaded".
7. Update the Db2 Database Manager Configuration to enable federation.
update database manager configuration using federated yes8. Restart Db2 to reload db2dj.ini and odbc.ini
db2stop
db2start
9. Connect to the database.
connect to mydb
Database Connection Information
Database server = DB2/LINUXX8664 12.1.2.0
SQL authorization ID = SHRIYA
Local database alias = MYDBMethod 1 : ODBC DSN(Data Source Name) Connection
1. Create the ODBC Wrapper
create wrapper odbc options(DB2_FENCED 'Y', module '/home/shriya/sqllib/federation/odbc/lib/libodbc.so')
DB20000I The SQL command completed successfully.2. Define the data source to the federated database with the CREATE SERVER statement
CREATE SERVER server1 TYPE SAP_ABAP VERSION 1 WRAPPER odbc OPTIONS (NODE 'ALY', DRIVER_UNICODE '1202')
DB20000I The SQL command completed successfully.Note: Please use the server option 'DRIVER_UNICODE' and set it's value to UCS-2 Codepage (1200 or 1202, depending on the system's endianness).
3. Use the CREATE USER MAPPING statement to map the federated server user ID to the ODBC data source user ID and password.
create user mapping for user server server1 OPTIONS (REMOTE_AUTHID 'ITAPC1_SQL_DB2_xxxx', REMOTE_PASSWORD 'xxxx')
DB20000I The SQL command completed successfully.4. Register a nickname for an ODBC table or view that you want to access.
create nickname NK_VIEW for server1.SYS.VIEW_COLUMNS
DB20000I The SQL command completed successfully.5. Test the access to the federated table.
DESCRIBE TABLE NK_VIEW
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMA_NAME SYSIBM VARCHAR 60 0 No
VIEW_NAME SYSIBM VARCHAR 60 0 No
COLUMN_NAME SYSIBM VARCHAR 60 0 No
SCHEMA_NAME_UPPER SYSIBM VARCHAR 60 0 Yes
VIEW_NAME_UPPER SYSIBM VARCHAR 60 0 Yes
COLUMN_NAME_UPPER SYSIBM VARCHAR 60 0 Yes
COLUMN_POSITION SYSIBM INTEGER 4 0 Yes
DESCRIPTION SYSIBM VARCHAR 500 0 Yes
IS_KEY SYSIBM VARCHAR 10 0 Yes
ODBC_DATA_TYPE SYSIBM SMALLINT 2 0 Yes
ODBC_TYPE_NAME SYSIBM VARCHAR 8 0 Yes
ODBC_COLUMN_SIZE SYSIBM INTEGER 4 0 Yes
ODBC_DECIMAL_DIGITS SYSIBM SMALLINT 2 0 Yes
ODBC_BUFFER_LENGTH SYSIBM INTEGER 4 0 Yes
ODBC_NUM_PREC_RADIX SYSIBM SMALLINT 2 0 Yes
ODBC_NULLABLE SYSIBM SMALLINT 2 0 Yes
DDIC_TYPE_NAME SYSIBM VARCHAR 8 0 Yes
DDIC_LENGTH SYSIBM INTEGER 4 0 Yes
DDIC_DECIMALS SYSIBM SMALLINT 2 0 Yes
19 record(s) selected.
SELECT * FROM NK_VIEW WHERE COLUMN_NAME = 'SCHEMA_NAME'
SCHEMA_NAME VIEW_NAME COLUMN_NAME SCHEMA_NAME_UPPER VIEW_NAME_UPPER COLUMN_NAME_UPPER COLUMN_POSITION DESCRIPTION IS_KEY ODBC_DATA_TYPE ODBC_TYPE_NAME ODBC_COLUMN_SIZE ODBC_DECIMAL_DIGITS ODBC_BUFFER_LENGTH ODBC_NUM_PREC_RADIX ODBC_NULLABLE DDIC_TYPE_NAME DDIC_LENGTH DDIC_DECIMALS
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- -------------- ---------------- ------------------- ------------------ ------------------- ------------- -------------- ----------- -------------
SYS_REPLICATION REPLICATION_METRIC SCHEMA_NAME SYS_REPLICATION REPLICATION_METRIC SCHEMA_NAME 2 - FALSE -9 CHAR 30 0 60 - 2 CHAR 30 0
SYS_REPLICATION VIEWS SCHEMA_NAME SYS_REPLICATION VIEWS SCHEMA_NAME 1 SQL Service Name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS VIEW_PARAMETERS SCHEMA_NAME SYS VIEW_PARAMETERS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS VIEW_COLUMNS SCHEMA_NAME SYS VIEW_COLUMNS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS VIEWS SCHEMA_NAME SYS VIEWS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS PROCEDURE_PARAMETER_COLUMNS SCHEMA_NAME SYS PROCEDURE_PARAMETER_COLUMNS SCHEMA_NAME 1 Schema name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS PROCEDURE_PARAMETERS SCHEMA_NAME SYS PROCEDURE_PARAMETERS SCHEMA_NAME 1 Schema name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS PROCEDURES SCHEMA_NAME SYS PROCEDURES SCHEMA_NAME 1 Schema name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS FUNCTION_PARAMETERS SCHEMA_NAME SYS FUNCTION_PARAMETERS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS FUNCTIONS SCHEMA_NAME SYS FUNCTIONS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS_REPLICATION REPLICATION_STATUS SCHEMA_NAME SYS_REPLICATION REPLICATION_STATUS SCHEMA_NAME 1 Schema Name FALSE -9 CHAR 30 0 60 - 2 CHAR 30 0
SYS_REPLICATION REPLICATION_PORTIONS SCHEMA_NAME SYS_REPLICATION REPLICATION_PORTIONS SCHEMA_NAME 1 - FALSE -9 CHAR 30 0 60 - 2 CHAR 30 0
12 record(s) selected.Method 2 : ODBC DSN-less (Data Source Name Less) Connection
db2 -tvf /home/shriya/test_scripts/sap_abap.sql
connect to mydb
Database Connection Information
Database server = DB2/LINUXX8664 12.1.2.0
SQL authorization ID = SHRIYA
Local database alias = MYDB
create wrapper odbc options(DB2_FENCED 'Y', module '/home/shriya/sqllib/federation/odbc/lib/libodbc.so')
DB20000I The SQL command completed successfully.
CREATE SERVER serverdsnlessmode TYPE SAP_ABAP VERSION 1 WRAPPER odbc OPTIONS (HOST '25638c75-a54b-4658-8b04-3a1156f2c4f5.abap.eu10.hana.ondemand.com', CUSTOMIZED_ODBC_ATTRIBUTES ';CLIENT=100;SERVICEPATH=/sap/bc/sql/sql1/sap/S_PRIVILEGED;TrustAll=true;CryptoLibrary=/home/shriya/sqllib/federation/odbc/lib/libsapcrypto.so;TypeMap=semantic;UidType=alias',DRIVER_UNICODE '1202')
DB20000I The SQL command completed successfully.
create user mapping for user server serverdsnlessmode OPTIONS (REMOTE_AUTHID 'ITAPC1_SQL_DB2_xxxx', REMOTE_PASSWORD 'xxxx')
DB20000I The SQL command completed successfully.
create nickname NK_VIEW_DSNLESS for serverdsnlessmode.SYS.VIEW_COLUMNS
DB20000I The SQL command completed successfully.
DESCRIBE TABLE NK_VIEW_DSNLESS
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMA_NAME SYSIBM VARCHAR 60 0 No
VIEW_NAME SYSIBM VARCHAR 60 0 No
COLUMN_NAME SYSIBM VARCHAR 60 0 No
SCHEMA_NAME_UPPER SYSIBM VARCHAR 60 0 Yes
VIEW_NAME_UPPER SYSIBM VARCHAR 60 0 Yes
COLUMN_NAME_UPPER SYSIBM VARCHAR 60 0 Yes
COLUMN_POSITION SYSIBM INTEGER 4 0 Yes
DESCRIPTION SYSIBM VARCHAR 500 0 Yes
IS_KEY SYSIBM VARCHAR 10 0 Yes
ODBC_DATA_TYPE SYSIBM SMALLINT 2 0 Yes
ODBC_TYPE_NAME SYSIBM VARCHAR 8 0 Yes
ODBC_COLUMN_SIZE SYSIBM INTEGER 4 0 Yes
ODBC_DECIMAL_DIGITS SYSIBM SMALLINT 2 0 Yes
ODBC_BUFFER_LENGTH SYSIBM INTEGER 4 0 Yes
ODBC_NUM_PREC_RADIX SYSIBM SMALLINT 2 0 Yes
ODBC_NULLABLE SYSIBM SMALLINT 2 0 Yes
DDIC_TYPE_NAME SYSIBM VARCHAR 8 0 Yes
DDIC_LENGTH SYSIBM INTEGER 4 0 Yes
DDIC_DECIMALS SYSIBM SMALLINT 2 0 Yes
19 record(s) selected.
SELECT * FROM NK_VIEW_DSNLESS WHERE COLUMN_NAME = 'SCHEMA_NAME'
SCHEMA_NAME VIEW_NAME COLUMN_NAME SCHEMA_NAME_UPPER VIEW_NAME_UPPER COLUMN_NAME_UPPER COLUMN_POSITION DESCRIPTION IS_KEY ODBC_DATA_TYPE ODBC_TYPE_NAME ODBC_COLUMN_SIZE ODBC_DECIMAL_DIGITS ODBC_BUFFER_LENGTH ODBC_NUM_PREC_RADIX ODBC_NULLABLE DDIC_TYPE_NAME DDIC_LENGTH DDIC_DECIMALS
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- -------------- ---------------- ------------------- ------------------ ------------------- ------------- -------------- ----------- -------------
SYS_REPLICATION REPLICATION_METRIC SCHEMA_NAME SYS_REPLICATION REPLICATION_METRIC SCHEMA_NAME 2 - FALSE -9 CHAR 30 0 60 - 2 CHAR 30 0
SYS_REPLICATION VIEWS SCHEMA_NAME SYS_REPLICATION VIEWS SCHEMA_NAME 1 SQL Service Name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS VIEW_PARAMETERS SCHEMA_NAME SYS VIEW_PARAMETERS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS VIEW_COLUMNS SCHEMA_NAME SYS VIEW_COLUMNS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS VIEWS SCHEMA_NAME SYS VIEWS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS PROCEDURE_PARAMETER_COLUMNS SCHEMA_NAME SYS PROCEDURE_PARAMETER_COLUMNS SCHEMA_NAME 1 Schema name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS PROCEDURE_PARAMETERS SCHEMA_NAME SYS PROCEDURE_PARAMETERS SCHEMA_NAME 1 Schema name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS PROCEDURES SCHEMA_NAME SYS PROCEDURES SCHEMA_NAME 1 Schema name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS FUNCTION_PARAMETERS SCHEMA_NAME SYS FUNCTION_PARAMETERS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS FUNCTIONS SCHEMA_NAME SYS FUNCTIONS SCHEMA_NAME 1 Schema name / ABAP SQL service name TRUE -9 CHAR 30 0 60 - 0 CHAR 30 0
SYS_REPLICATION REPLICATION_STATUS SCHEMA_NAME SYS_REPLICATION REPLICATION_STATUS SCHEMA_NAME 1 Schema Name FALSE -9 CHAR 30 0 60 - 2 CHAR 30 0
SYS_REPLICATION REPLICATION_PORTIONS SCHEMA_NAME SYS_REPLICATION REPLICATION_PORTIONS SCHEMA_NAME 1 - FALSE -9 CHAR 30 0 60 - 2 CHAR 30 0
12 record(s) selected.Notice:
1. server option CUSTOMIZED_ODBC_ATTRIBUTES needs to be added for DSN-less connection
method when creating nickname.
For example,
CUSTOMIZED_ODBC_ATTRIBUTES ';CLIENT=100;SERVICEPATH=/sap/bc/sql/sql1/sap/S_PRIVILEGED;TrustAll=true;CryptoLibrary=/home/shriya/sqllib/federation/odbc/lib/libsapcrypto.so;TypeMap=semantic;UidType=alias'2. TypeMap can be semantic, semanticDatsTimsAsWchar or native.
For more information about TypeMap, please refer to ABAP to ODBC Data Type Mappings
Additional Information
ABAP CDS view entities and Db2 federation data type mapping list.
| SAP ABAP data type | Db2 Federation data type | Comments |
| INT1 | SMALLINT | The range of small integers is -32768 to 32767. |
| INT2 | SMALLINT | The range of small integers is -32768 to 32767. |
| INT | INTEGER | The range of large integers is -2147483648 to +2147483647. |
| INT8 | BIGINT | The range of big integers is -9223372036854775808 to +9223372036854775807. |
| DEC | DECIMAL | The maximum precision is 31 digits. The scale cannot be negative or greater than the precision. |
| FLTP | DOUBLE | The number can be zero or can range from -1.7976931348623158e+308 to -2.2250738585072014e-308, or from 2.2250738585072014e-308 to 1.7976931348623158e+308. |
| DF16_DEC | DECIMAL | TypeMap=native, decimal. |
| DECFLOAT | TypeMap=semantic, decfloat(16). | |
| DECFLOAT | TypeMap=semanticDatsTimsAsWchar, decfloat(16). | |
| DF34_DEC | DECIMAL | TypeMap=native, decimal. |
| DECFLOAT | TypeMap=semantic, decfloat(34). | |
| DECFLOAT | TypeMap=semanticDatsTimsAsWchar, decfloat(34). | |
| DF16_RAW | BINARY | TypeMap=native, binary. |
| DECFLOAT | TypeMap=semantic, decfloat(16). | |
| DECFLOAT | TypeMap=semanticDatsTimsAsWchar, decfloat(16). | |
| DF34_RAW | BINARY | TypeMap=native, binary. |
| DECFLOAT | TypeMap=semantic, decfloat(34). | |
| DECFLOAT | TypeMap=semanticDatsTimsAsWchar, decfloat(34). | |
| DECFLOAT16 | DECFLOAT | decfloat(16). |
| DECFLOAT34 | DECFLOAT | decfloat(34). |
| CHAR | VARCHAR | As ODBC driver map ABAP char(n) to sql_wvarchar(n), so federation map it to varchar(2n). |
| STRING | CLOB | The clob length is 2147483646. |
| SSTRING | VARCHAR | As ODBC driver map ABAP char(n) to sql_wvarchar(n), so federation map it to varchar(2n). |
| XSTRING | BLOB | The blob length is 2147483647. |
| NUMC | VARCHAR | The ABAP numc is a numeric text field. 1-255. |
| RAW | BINARY | binary(n). |
| DATN | DATE | The date format is 'mm/dd/yyyy'. |
| DATS | VARCHAR | TypeMap=native, varchar(16). |
| DATE | TypeMap=semantic, date. | |
| VARCHAR | TypeMap=semanticDatsTimsAsWchar, varchar(16). | |
| TIMN | TIME | The time format is 'hh:mm:ss'. |
| TIMS | VARCHAR | TypeMap=native, varchar(12). |
| TIME | TypeMap=semantic, time. | |
| VARCHAR | TypeMap=semanticDatsTimsAsWchar, varchar(12). | |
| UTCL | TIMESTAMP | The timestamp format is 'yyyy-mm-dd-hh.mm.ss.SSSSSS'. |
| ACCP | VARCHAR | Posting period in the format 'yyyymm', varchar(12) |
| GEOM_EWKB | BLOB | Geometric data in EWKB representation, length is 2147483647. |
| CURR | DECIMAL | TypeMap=native, decimal. |
| DECFLOAT | TypeMap=semantic, decfloat(34). | |
| DECFLOAT | TypeMap=semanticDatsTimsAsWchar, decfloat(34). | |
| CUKY | VARCHAR | Currency key for currency fields, varchar(10). |
| QUAN | DECIMAL | decimal(10,3). |
| UNIT | VARCHAR | varchar(6). |
| LANG | VARCHAR | TypeMap=native, varchar(6). |
| VARCHAR | TypeMap=semantic, varchar(4). The ODBC driver returns the length 4. | |
| VARCHAR | TypeMap=semanticDatsTimsAsWchar, varchar(4). The ODBC driver returns the length 4. |
Note: When the value of a column with type UTCL contains is "0000-00-00T00:00:00,0000000", the value is set to NULL.
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB76","label":"Data Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSFHEG","label":"DB2 Enterprise Server Edition"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Connectivity-\u003EFederation"}],"ARM Case Number":"","Platform":[{"code":"PF016","label":"Linux"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
22 April 2025
UID
ibm17156469