This document is an instruction regarding how to speed up BDLS when you finished one system refresh with huge database special for huge ERP system refresh.
Please be aware of this instruction is only a general tips, not for all circumstance because all performance issues might be caused by many factors like hardware(CPU, memory), inappropriate table index and SQL statement and so on.
To optimize BDLS performance, please take the following 5 steps in general. The example is ARLA system named SS2. OS and Database is AIX and DB2.
1. Using old BDLS functionality instead of only run transaction BDLS, because the trx BDLS does not support parallel runs as of NW640 system.
To use old ‘BDLS’, run the report RBDLS2LS using SE38. After this report run for the first time, a new program RBDLS400 (400 is the client number for SS2) was generated automatically which is the program you want for later use.
2. Create temporary indexes for any big tables which included logical systems fields.
For instance in SS2 table GLPCA has more than 5000000000 entries and 200 GB, and GLPCA has 2 fields LOGSYS and AWSYS which refer to logical system. In this case, create 2 temporary secondary index Z50 on field MANDT and LOGSYS, Z51 on field MANDT and AWSYS.
Another example is table VBAP which has more than 8000000000 entries and 400GB, and 1 field LOGSYS_EXT. In this case, create 1 temporary secondary index on field MANDT and LOGSYS_EXT. Continue to do so for the other large tables, like VBUK, COEP and so on.
You can use DB20 combined with one BDLS test run to find which tables you want to create temporary indexes. I will not explain more on this here.
3. Modify BDLS program if necessary.
This step is optional because it might be depend on other factors like hardware (CPU, Memory). Here I have made tests on different systems SS2 and CS1 with different hardware (CS1: 80 CPUs and 230GB memory SS2: 40 CPUs and 34GB). On both systems, I created the same index on table VBAP (index Z50 on MANDT, LOGSYS_EXT). But when I run the BDLS job for VBAP on both systems, there’s huge difference on runtime. The speed of table conversion on SS2 was far slower than on CS1. This result was mainly caused by hardware configuration.
In this case, modification for program like was required on SS2. Make a copy from RBDLS400 to ZRBDLS400 and make the following modification.
ZRBDLS400:
PERFORM PROCESS_INFO_OUT USING ' ' 'VBAP'.
READ TABLE STATISTICS WITH KEY TABNAME = 'VBAP'
FLDNAME = 'LOGSYS_EXT'.
IF SY-SUBRC = 0 AND STATISTICS-COUNT_0 > 0 AND STATISTICS-COUNT_1 = 0.
TAB_INDEX = SY-TABIX.
READ TABLE NO_UPDATES WITH KEY TABNAME = STATISTICS-TABNAME
FLDNAME = STATISTICS-FLDNAME.
IF SY-SUBRC <> 0.
IF STATISTICS-COUNT_0 < MAXIMUM.
UPDATE VBAP SET LOGSYS_EXT = NEW_LS
WHERE LOGSYS_EXT = OLD_LS.
IF SY-DBCNT = 0.
APPEND STATISTICS TO NO_UPDATES.
ELSE.
ADD SY-DBCNT TO STATISTICS-COUNT_1. COMMIT WORK.
ENDIF.
ELSE.
CLEAR FLG_FAULT.
DO.
* SELECT * FROM VBAP UP TO MAXIMUM ROWS
* BYPASSING BUFFER INTO TABLE T_VBAP
* WHERE LOGSYS_EXT = OLD_LS.
SELECT VBELN POSNR LOGSYS_EXT FROM VBAP UP TO MAXIMUM ROWS
BYPASSING BUFFER INTO CORRESPONDING FIELDS OF TABLE T_VBAP
WHERE LOGSYS_EXT = OLD_LS.
IF SY-DBCNT = 0. EXIT. ENDIF.
LOOP AT T_VBAP.
UPDATE VBAP SET LOGSYS_EXT = NEW_LS
WHERE POSNR = T_VBAP-POSNR
AND VBELN = T_VBAP-VBELN.
This modification is in order to decrease the number of data volume for the select statement (only select 3 fields instead of select *). After modification, runtime of BDLS on SS2 is close to CS1, but still a little bit slow. You can use ST05, ST04 to trace the SQL statement, no more detailed was explained here.
4. Increase as many BTC work processes as you can which depends on how many jobs you want to run parallel and how many tables were split.
After increase BTC work processes, you can run the program BDLS program (RBDLS400 or ZRBDLS400) using SE38. In the selection screen, you can choose tables in the field ‘Tables for Conversion’ by using single table like VBAP, GPLCA or table range with exclude option like A* - Z* with exclude VBAP. You can do this by creating variant, but please make sure there’s no overlap and missed table in your variant. And then Run the program in background for these variant in parallel.
5. Clean up temporary indexes.
Result:
After finished steps 1- 3, BDLS for table VBAP only run 5 – 7 days on SS2 (4 – 5 days on CS1) to finished rather than run more than 30 days.
