In this edition of the SAP on IBM i news from development blog I would like to talk about decimal floating point. Of course this topic is not limited to SAP on IBM i, so users of other databases may want to have a look on this blog, too. But there are features that are specific to SAP on IBM i. I will present this features in the third part of this blog. 1. What is decimal floating point?
For most of us the „natural" representation of numbers is the decimal one. This representation is quite old (the oldest known sources are from India) and is used all over in everyday life. Other number representations like the sexagesimal system have only survived in small areas like trigonometry.
Unfortunately, all current computers are based on the binary system which makes it necessary to change the representation of all numbers that are transferred into the computer. And this change of representation yields some problems that are not easy to get around. First of all not every number with a fixed length representation in the decimal system has a fixed length representation in the binary system. As an example look at the decimal fraction 0.2. In the binary representation this is 0.(0011). Here the parantheses are indicating the period of the number. Some calculations that can be done without any loss of precision in the decimal representation cannot be done without loss of precision in the binary system. And of course the standard methods of rounding cannot be transferred into the binary world very easily. Calculations with fractions usually give results with a limited precision. But with different representations the results can vary.
For many applications these problems are not a big deal. Using floating point numbers is usually sufficient and increasing the number of binary digits usually increases precision as good as demanded. But for some business and scientific applications this does not work. One example are currency conversions. There are strict rules about rounding and the number of decimals to take into account. As these rules are based on the decimal system the binary calculations will not work in all cases. The solution to this issue are decimal floating point numbers (DFP) that represent numbers as decimal fractions in the binary world.
2. How is DFP integrated into SAP NetWeaver?
Most of the common programming languages like C/C++ and Java nowadays have been extended to offer support for DFP data types There are software libraries available which perform the DFP calculations on the different processor architectures.
Starting with SAP NetWeaver Release 7.0 EhP2 and 7.1 EhP1, the ABAP programming language and the data dictionary (DDIC) also support decimal floating point data types. Two DFP data types are available, decfloat16 and decfloat34:
Type | Precision | Digits | Exponent Range |
decfloat16 | Double (64 bits) | 16 | -398 to +369 |
decfloat34 | Quad (128 bits) | 34 | -6176 to +6111 |
You can take advantage of these data types in your reports and use them like the common data types you are used to. But although the DFP types are integrated into the DDIC, these types are not mapped to native decimal floating types on the database. This is not possible because ABAP OpenSQL code needs to be platform independent and not all database platforms offer a DFP data type yet.
In the example report you can see how to use this data type. Be aware that the first half of the report is specific to DB2 for IBM i but the second half is platform independent.
3. How to take advantage of DFP on IBM i?
With version 6.1 of the IBM DB2 for i database both decfloat16 and decfloat34 data types are natively supported. This allows you to store decimal floating point data in database tables without any type conversion and the risk of losing value precision. The numeric functions of the database are also supporting DFP. In the example report I have chosen a simple substraction and the sum() function to show this.
In addition, the IBM Power6 processor comes with a hardware decimal floating point unit, meaning it is capable of calculating decimal floating point numbers without any software emulation.
All together, the ABAP, database, operating system and processor support for DFP provides a thoroughly integrated DFP solution running with SAP on IBM i.
In order to take full advantage of DFP for your SAP ABAP application you can use ExecSQL and ADBC SQL interfaces to work with the native DFP database types. Note that you cannot create tables with native DFP columns from the DDIC. You have to either execute a DDL statement with native SQL or create the table natively on the database on your own.
One drawback of native SQL should not be forgotten. Native SQL is database specific code. If you are going for a database migration at some point in the future you will have to rewrite all of this code. Native SQL should never be used withour considering what implications that might have for future changes.
Example
The following ABAP code shows a basic example how you can use the DFP data type with native SQL. It's just an example so use at your own risk.
It shows how to create a table with column types decfloat16 and decfloat34, store data to the table from ABAP DFP variables and retrieve data from the database table into such variables. In addition to that some calculations are done directly in the database. At the end we drop the table to get to the original state again.
In addition, there is a short example where you can see that representation does matter. The decimal fraction 0.2 cannot be represented as a fixed length binary fraction. By adding up this fraction several times the tiny error gets more significant.
*&---------------------------------------------------------------------* *& Report ZDFPTEST *& *&---------------------------------------------------------------------* *& Demo: making use of decimal floating point support *& using native SQL *&---------------------------------------------------------------------* REPORT ZDFPTEST. TYPES: BEGIN OF DFP_ROW, I TYPE I, DF16 TYPE DECFLOAT16, DF34 TYPE DECFLOAT34, END OF DFP_ROW. DATA: EXC_REF TYPE REF TO CX_SY_NATIVE_SQL_ERROR, ERROR_TEXT TYPE STRING, I TYPE I VALUE '1', DF16 TYPE DECFLOAT16 VALUE '0.2345600', DF34 TYPE DECFLOAT34 VALUE '100.2001234', RESULT TYPE DFP_ROW. IF SY-DBSYS(3) <> 'DB4'. WRITE: / 'Native SQL demo is specific to DB2 for IBM i'. WRITE: / 'Skipped native SQL demo'. ELSE. *----------------------------------------------------------------------* * create a table with decfloat columns *----------------------------------------------------------------------* TRY. WRITE: / 'Creating table with decfloat columns: '. EXEC SQL. CREATE TABLE Z_TEST_DECFTAB (I INT NOT NULL WITH DEFAULT, DECF16 DECFLOAT (16) NOT NULL WITH DEFAULT, DECF34 DECFLOAT (34) NOT NULL WITH DEFAULT) ENDEXEC. WRITE ICON_GREEN_LIGHT. *----------------------------------------------------------------------* * insert decfloat data to the table *----------------------------------------------------------------------* WRITE: / 'Inserting data into table: '. EXEC SQL. INSERT INTO Z_TEST_DECFTAB (I, DECF16, DECF34) VALUES(:I, :DF16, :DF34) ENDEXEC. WRITE ICON_GREEN_LIGHT. *----------------------------------------------------------------------* * get decfloat data from the table *----------------------------------------------------------------------* WRITE: / 'Reading data from table: '. EXEC SQL. SELECT i, DECF16, DECF34 FROM Z_TEST_DECFTAB INTO :RESULT ORDER BY I ENDEXEC. WRITE: 'I:', RESULT-I, 'DECF16:', RESULT-DF16, 'DECF34', RESULT-DF34, ICON_GREEN_LIGHT. *----------------------------------------------------------------------* * calculations with decfloat data inside the database *----------------------------------------------------------------------* WRITE: / 'Calculating with DFP data inside the database '. DO 10 TIMES. I = I + 1. EXEC SQL. INSERT INTO Z_TEST_DECFTAB (I, DECF16, DECF34) VALUES (:I, '0.2', :DF34) ENDEXEC. ENDDO. EXEC SQL. SELECT SUM(DECF16) FROM Z_TEST_DECFTAB INTO :DF16 WHERE (DECF34 - DECF16) > 100. ENDEXEC. WRITE: '(Sum of 10 decfloat values of 0.2): ', DF16, ICON_GREEN_LIGHT. *----------------------------------------------------------------------* * drop table with decfloat columns *----------------------------------------------------------------------* WRITE: / 'Dropping table: '. EXEC SQL. DROP TABLE Z_TEST_DECFTAB ENDEXEC. WRITE ICON_GREEN_LIGHT. CATCH CX_SY_NATIVE_SQL_ERROR INTO EXC_REF. ROLLBACK WORK. WRITE ICON_RED_LIGHT. WRITE: / 'SQL-Error ', EXC_REF->SQLCODE, EXC_REF->SQLMSG. MESSAGE 'SQL-Error' TYPE 'I'. ENDTRY. ENDIF. "DB/OS release check *----------------------------------------------------------------------* * calculations with decfloat variables *----------------------------------------------------------------------* SKIP. ULINE. WRITE: / 'Comparison of float with decfloat variables'. WRITE: / 'Calculate sum 0.2 + 0.2 + ... + 0.2 and product 10 * 0.2'. ULINE. DATA: PRODUCT TYPE F, SUM TYPE F, PRODUCT_DFP TYPE DECFLOAT16, SUM_DFP TYPE DECFLOAT16. PRODUCT = '0.2' * 10. DO 10 TIMES. SUM = SUM + '0.2' . ENDDO. WRITE: / 'With data type float: sum = ', SUM, '; product = ', PRODUCT. IF SUM <> PRODUCT. WRITE ICON_YELLOW_LIGHT. ELSE. WRITE ICON_GREEN_LIGHT. ENDIF. PRODUCT_DFP = '0.2' * 10. DO 10 TIMES. SUM_DFP = SUM_DFP + '0.2' . ENDDO. WRITE: / 'With data type decfloat16: sum = ', SUM_DFP, '; product = ', PRODUCT_DFP. IF SUM_DFP <> PRODUCT_DFP. WRITE ICON_YELLOW_LIGHT. ELSE. WRITE ICON_GREEN_LIGHT. ENDIF.
