Table maintenance with ABAP in BW

Summary:

In this article we will provide you how to maintain table/index/view in ABAP. Same functionality you can achieve using transaction SE14 (Utility for Database Tables).

Introduction:

There is standard transaction (SE14) in BW to maintain table. But there is not mentioned how to use this function if you want to write a custom program/solution and maintain table with ABAP code.

Target Audience:

Main target of this article are BW consultants who have basic knowledge with ABAP and realizing complex business customer requirements.

Business scenario:

We have a specific solution relevant for better performance during loading data into ODS. We use a secondary index while data are loaded into ODS with end routine and direct update of data in this end routine. For the purposes of this article, it is not necessary to describe in detail this logic of data loading

We faced problems with query performance if secondary index exists in ODS. We noticed that without secondary index performance of query was normal (data extraction takes time under one minute). When we have created the index performance went down (same query, around 15 minutes). We find out in DB02 that query select was using new index what causes performance issues. If the index was dropped everything was normal.

Solution:

After identification what causes the problem we decided to write a program which can be used in process chain to create index before data loading and after that drop the index so that query performance was not affected.

We were looking for a solution on internet but without success. I decided to debug SE14 to find out how SE14 works. After few attempts I found the right function module(FM) which provide database operations and can be used in separate custom program.

FM: DD_DB_OPERATION

You can write a simple program with few variables. For example ‘ZBW_INDEX_CREATE_DROP’:

Code exeample:

PARAMETERS:
            p_fct TYPE tbatg-fct,
            p_oname TYPE tbatg-tabname,
            p_idname TYPE dd12l-indexname,
            p_otype TYPE tbatg-object DEFAULT 'INDX'.
DATA:
      i_prid TYPE sy-tabix VALUE 0,
      i_status TYPE ddxtt-modeflag VALUE 'D'.  

CALL FUNCTION 'DD_DB_OPERATION'
  EXPORTING
   FCT        = p_fct
   ID_NAME    = p_idname
   OBJ_NAME   = p_oname
   OBJ_TYPE   = p_otype
   PRID       = i_prid
   STATUS     = i_status

You have to fill at least this variables in function module:

FCT – type of action CRE = create, DEL = delete (CRE’, ‘DEL’, ‘CNV’, ‘ICN’, ‘MDF’)

ID_NAME – ID of the view od index ID

OBJ_TYPE  – INDX = index, TABL = table (‘TABL’, ‘SQLT’, ‘INDX’, ‘VIEW’)

PRID – use default value 0

We will create two variants with variables for create and drop index which will be used in process chain

Now we can use the variants in process chain in step ABAP Program

One variant for create index

One variant for drop index

Usage in processchain:

Result:

As you can see with function module DD_DB_OPERATION table can be maintained with ABAP coding. Than this logic used in process chain to automate the process. FM can used in a lot of business scenarios depend on your needs.