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.
Okumaya devam et...
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.
Okumaya devam et...