Declustering / decoupling of SAP’s DB tables

Cluster and pooled tables are present in SAP NetWeaver ABAP stack based system for ages. They addressed requirements of SAP functional applications to present data from several database tables together. SAP introduced following table types in ABAP Dictionary to support that:

Cluster table - data logically belonging together of several cluster tables is stored together in a table cluster in the database. It represents a diagram type in the ABAP Dictionary. Such ABAP dictionary tables allow efficient access to a whole application object without using extensive join operations in database. Cluster tables are used very much in SAP’s HR module.

Pooled table - data of several pooled tables is stored together as a table pool in the database. Such ABAP dictionary tables are trying to solve a problem with large numbers of individual database objects (e.g. large number of small customizing tables).

However, these concepts are obsolete when it comes to modern databases. Organizing data in such a way in ABAP application server is no needed anymore. SAP is trying to remove these concepts by declustering / decoupling of such an objects. This means that all tables are converted to transparent tables. For any SAP NetWeaver system running on HANA DB such a tables are declustered and depooled per default. Also starting with SAP NetWeaver 7.5 on any DB the declustering / decoupling is mandatory by default. Operation of converting tables is doen during installation of NetWeaver 7.5 based systems. Manual conversion can be done in t-code SE11 together with SE14 and/or in ICNV.

One more thing worth mentioning is performance of declustering / decoupling operation. During SAP system installation it is usually very time consuming task. Therefore, it must be considered while preparing plans for installing of new systems.

More information:

1892354 - SAP Strategy for Cluster and Pool Tables

2227432 - How to: Declustering and depooling with NW 7.4 SP03 and higher on databases other than SAP HANA

1784377 - Checking pool tables and cluster tables

BW: Conversion of DB tables to from Row Store to Column Store

Within a move of SAP NetWeaver based system from any DB to HANA DB there are many steps involved. Also a step of converting tables from row to column based is one of them.

Running the system on HANA DB you can have the table regardless of it store type (either row store or column store). Then depending on type of the system (OLTP or OLAP) majority of tables are set to one of the store types. E.g. in case of OLAP system (e.g. SAP BW) most of the tables are type of column store tables because it delivers a better performance in case of data warehouse data processing (running queries, transforming the data, etc). Also it delivers better compression rate in case of OLAP. On other side in the systems like OLTP it requires balanced approach whereas for some table a row store might be better and for others column store concept.

During HANA migration (or even before that to minimize downtime) an ABAP report called RSDU_MOVE_TO_COLUMN is used convert the tables based on row store to column store. However the program can be run even after the migration to find out what are tables that have potential to be converted to column store. The report has several modes of runtime. In the “Check only” mode a list of biggest row store tables is provided.

Also to see to what store type a particular table is set; see table DD09L (DD: Technical settings of tables) and its field (ROWORCOLST - For distinction between Row or Column Store) in particular.

More information:

ABAP on HANA: How to check what tables are type of columnar?

2044047 - Pre-BW7.4 SP08 RowStore-to-ColumnStore conversion

Converting Oracle DB format of date to SAP date format

There are many data flows picking up the data from "external database" based source systems in BW. One of most used DB system is Oracle. To connect BW system to Oracle DB a technology called DB Connect is very often used. As Oracle and SAP are different systems designed by two different companies there are many differences in there while comparing each other. The differences are there as well as from date and time related fields representation.

Therefore there are challenges while integrating data from Oracle DBs into SAP BW. Especially when converting date and time fields in Oracle and into format compatible with SAP BW. Let's take an Oracle date format for example. It has a format as DD-MMM-YY means it has 7 characters (e.g. today's date is represented by: 03-AUG-15). Whereas SAP format is 8 characters long in format YYYYMMDD (see Data Element SYDATUM; Domain SYDATS or Data Type DATS). If we just simply assign Oracle date field to SAP one it won't get proper data because the fields are not compatible.

Now; how to solve this? On SCN there are many discussions (e.g. here or here) on this. Most of it suggest parsing value as they come from Oracle and concatenate it at the end to field in SAP format of date. However this is not proper approach. The way how Oracle produce the format of the data field depends on national language settings (NLS) used with the database connection. Therefore the date field can come in many different flavors as per many configuration settings. What actually needs to be done is to force Oracle to produce the date field in desired format. This can be achieved by adding function TO_CHAR into e.g. SELECT statement while we extracting the data. E.g. a separate view can be created in Oracle to add TO_CHAR function. Such a SELECT statement would look like:

select  to_char(,'YYYYMMDD') as dat from where ;

For more information see:

518241 - DB Connect in BW for an external Oracle database

How to find the table size in SAP system from SAP GUI

1. t-code DB02 -> Space -> Segments -> Detailed Analysis -> enter values into fields:

Segment / Object = table name

Type = TABLE

2. t-code DBACOCPIT -> Space -> Segments -> Detailed Analysis -> enter values into fields:

Segment / Object = table name

Type = TABLE

3. ABAP report RSTABLESIZE. On it selection screen just specify table into field "Table Name ". Notice this doesn't work for BW objects e.g. starting with prefix /BIC/* for which do DB statistics wasn't calculated yet.

4. Table DBSTATTORA which displays actual size of tables on the database. Just enter the table name into the field TNAME. Table size is shown in field OCCBL - Used blocks of a table in KB. Again to have this value in the table DB statistics must be up2date.

5. t-code TANA. Start first table analysis for desired table. Once it is finished display the analysis with the same t-code. Notice this t-code just gets no of entries of the table instead of table size.

6. Function module GET_TABLE_SIZE_. This is DB specific function. As SAP systems are basically "Any DB" systems which mean several major DB vendors are supported you just need to find proper FM for your DB.

Adabas/SAP DB                  ada/sdb         GET_TABLE_SIZE_ADA

Microsoft SQL Server          mss              GET_TABLE_SIZE_MSS

Oracle                                        ora               GET_TABLE_SIZE_ORA

IBM DB2/390                     db2              GET_TABLE_SIZE_DB2

IBM DB2/400                     db4              GET_TABLE_SIZE_DB4

IBM DB2 UDB                    db6              GET_TABLE_SIZE_DB6

Informix                           inf                GET_TABLE_SIZE_INF

Sybase ASE                       syb               GET_TABLE_SIZE_ALL

HANA Database                  hdb              GET_TABLE_SIZE_ALL

PS: let me know in case you are aware of any other options of how to find out table size form SAP GUI.

ABAP: Dynamically generated WHERE conduction in SELECT

Sometimes ABAP programmers needs to do dynamical generation of WHERE conditions in SELECT statement. To do this the WHERE condition can be built by either CONCATENATE statement or by && string expression. Example in SAP documentation is provided here. Basically string variable is filled up with column name in which the lookup is performed and by lookup value:

cond_syntax = column &&  ` = value`. 

More over there are couple of function modules which can do this automatically. There are following FMs for that:

RH_DYNAMIC_WHERE_BUILD

CONVERT_SELECT_INTO_WHERE

Last note to this topic is related to lookups of string values. Usually while we want to search in SAP (e.g. in matchcodes) we use asterisk (*) as wildcard which matches one or more characters. However in SQL language instead of the asterisk a percent sign (%) is used for the same. Therefore while the WHERE condition needs to be build up to look up matches one or more characters before and after EMAIL literal in column USRID it has to look like following:

USRID like '%EMAIL%'

Unknown table in tcode SM50

Few days back I was debugging SAP standard function module in HR module of ECC. When I finished what I wanted to see in the debugger I let it run till the end. The FM was not progressing so I turned to TA SM50 to see whether it is doing something. Suddenly I saw that my process is running but what got my attention was Table column indicating that table is unknown (Tunknown). It took few more seconds and the FM and work process finished correctly and entry in the SM50 disappeared. I didn't pay attention anymore to that but I just tweeted about it.

My twitter friend Ram Manohar Tiwari however after some conversation pointed me to SAP Note 1524630 which discuss this issue in detail. Actually while using ADBC (ABAP Database Connectivity) API for Native SQL interface it can lead to situation in which system can’t really determine what table is actually used. In my case usage of ADBC is indicated by class CL_SQL_STATEMENT which is ADBC related.

As the notes suggests there always will be Tunknown entries in the SM50 as there are cases where “guessing” of used tables by DbSl interface (database SQL library) doesn't help.

Oracle DB versioning

Oracle as database is quite popular within SAP customers. It is fact for many years. The reason why customers are running their SAP system on Oracle database is that Oracle is quite stable, scalable and SAP is really optimized for Oracle. Although both companies do not like each other very much when it comes to database SAP is really taking a lot of effort to optimize their software for Oracle. Not sure how long this will be true as SAP will certainly shift its focus on their own homegrown HANA database but for time being it is valid.

Speaking of BW there are many features that are optimized for BW running on Oracle DB. You can find a lot of SAP Notes discussing those features. There are many RSADMIN parameters just for Oracle as DB. Once you want to use some of these features you may need to check first fi you have valid version of Oracle DB for which feature is valid. This here comes the tricky part. Oracle DB versioning is different from its DB’s marketing names. You can see in the particular note (e.g. 1287382) that you need Oracle DB 11g. What you see in your SAP system under menu System-> Status is that you have 11.2.0.3.0. So is 11.2.0.3.0 really 11g? Well version number starts with 11 but what about g?

Unfortunately I haven’t found any official material on how to match between real version number and its marketing name. But speaking very high level we can match those two as follows:

name	number
Oracle v2	2.3
Oracle v3	3.1.3
Oracle v4	4.1.4.0-4.1.4.4
Oracle v5	5.0.22, 5.1.17, 5.1.22
Oracle v6	6.0.17-6.0.36
Oracle7	7.0.12–7.3.4
Oracle8	8.0.3–8.0.6
Oracle8i rel 1	8.1.5.0–8.1.5.1
Oracle8i rel 2	8.1.6.0–8.1.6.3
Oracle8i rel 3	8.1.7.0–8.1.7.4
Oracle9i rel 1	9.0.1.0–9.0.1.5
Oracle9i rel 2	9.2.0.1–9.2.0.8
Oracle Database 10g Rel 1	10.1.0.2–10.1.0.5
Oracle Database 10g Rel 2	10.2.0.1–10.2.0.5
Oracle Database 11g Rel 1	11.1.0.6–11.1.0.7
Oracle Database 11g Rel 2	11.2.0.1–11.2.0.4
Oracle Database 12c Rel 1	12.1.*

Source of information: Wikipedia page.