Skip to Content
Author's profile photo Muniraju H

BDLS IN LESS THAN 2 HOURS – Part 4

This is in continuation of my previous blog http://scn.sap.com/people/muniraju.h/blog/2012/06/04/bdls-in-less-than-2-hours–part-3

Part – 4

Putting together all possible optimization and automation; below are the brief steps we  articulated:-

1

Put database in NOARCHIVELOG mode

2

Create PSAPBDLS tablespace of 5 GB

3

Enable parallelism for all tables invloved in BDLS

4

Enable parallellism for all indexes of the tables invloved in BDLS

5

Create bitmap indexes with PARALLEL NOLOGGING COMPUTE STATITICS for tables with total number of rows > 10000

6

Calculate rows to be updated by BDLS

7

Build CTAS command lines with decode funtions

8

Perform coversion of tables where rows to update > 1000 using CTAS

9

Save original index definitions

10

Drop original indexes

11

Rename original table to old

12

Rename newly created tables to original

13

Recreate indexes

14

Verify and Drop old table

15

Run BDLS in SAP using report RBDLS450 in parallel (A*….Z*, etc) using BDC sessions.

16

Clean up

17

Drop bitmap indexes

18

Revert parallelism to original state for all tables and indexes

19

Drop function created, if any

20

Drop extra tables created, if any

21

Run update statistics

22

Put database back on ARCHIVELOG mode

With automation and combining above optimization approach, BDLS was achieved in 1 hour and 25 minutes.


Activity

Time

Create PSAPBDLS tablespace of 5 GB

5 minutes

Bitmap indexes

30 Minutes

CTAS for 15 tables

15 Minutes

BDLS run in parallel

30 Minutes

Cleanup Bitmap indexes, tablespace, etc

5 minutes

TOTAL

1:25 hours

You may also plan to create bitmap indexes on source system so that these indexes are already available on target system. This will save 30 minutes.

And, BDLS is achievable in less than 1 hour    😎



Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Martin English
      Martin English

      An excellent acheivment. Glad to see you put 'my' idea, of creating the indexes in the source system, into practice 🙂

      Now all I need to do is find an equally lengthy BDLS on a DB/2 system, and write up an equivalent set of instructions / documentation !!

      Author's profile photo Anuj Nigam
      Anuj Nigam

      Excellent Article.

      Author's profile photo Consult sap
      Consult sap

      Great article.

      I have just one doubt.

      As I understand you have used CTAS(Create table as select) for selective tables to update LOGSYS via Database. This leads to many steps for table, index and cleanup related tasks.

      Once we know table and field to be updated, can’t we execute direct update of that field in database Eg>

      Update  sapsr3."EDIDC" set LOG_SYSTEM='<Target_LogSys_Name>' where LOG_SYSTEM='<Source_LogSys_name>';

      and avoid all steps around CTAS. ?

      Author's profile photo Muniraju Hanumanthiah
      Muniraju Hanumanthiah
      Blog Post Author

      Hello,

      SAP also does same, executing Update on table.

      If you have read part 2 of this article.

      http://scn.sap.com/people/muniraju.h/blog/2012/06/01/bdls-in-less-than-2-hours--part-2

      And point 3. says.

      3.  Update command is run to modify the logical system value. For e.g. S1P500 to S1Q500.

      UPDATE  "MKPF" SET   "AWSYS" = 'S1Q500' WHERE   MANDT = 500 AND "AWSYS" = 'S1P500' AND ROWNUM <= 100000;


      And in part 3, I have explained problem with updates on indexed fields.

      http://scn.sap.com/people/muniraju.h/blog/2012/06/04/bdls-in-less-than-2-hours--part-3

      Hope you have answers now.

      Kind Regards,

      Muniraju

      Author's profile photo Former Member
      Former Member

      Excellent explanation and solution for this always time consuming refresh activity 🙂

      Thanks for such good job and efforts taken.

      Can you please summarise your steps with corresponding SQL commands too?

      It wil be easy for us who are not Oracle experts.

      I have did for same may be you can update and correct for others, also if you can add step or provide query for tables u found > 10000 rows as that part is not much clear for me

      1-----------------------------------------
      Put database in NOARCHIVELOG mode

      alter database no archivelog;

      2------------------------------------------
      Create PSAPBDLS tablespace of 5 GB

      CREATE TABLESPACE PSAPBDLS
        DATAFILE 'tbs_perm_01.dbf'
          SIZE 5000M
        ONLINE;

      3-----------------------------------------------------------------------------------------------------------
      Enable parallelism for all tables invloved in BDLS

      ALTER TABLE COEP PARALLEL;

      4-----------------------------------------------------------------------------------------------------------
      Enable parallellism for all indexes of the tables invloved in BDLS
      ALTER INDEX "COEP~0" PARALLEL;

      5-------------------------------------------------------------------------------------------------------------
      Create bitmap indexes with PARALLEL NOLOGGING COMPUTE STATITICS for tables with total number of rows > 10000
       
      CREATE BITMAP INDEX COEP_BDLS_IDX_12345 ON COEP(MANDT, LOGSYSO) NOLOGGING PARALLEL TABLESAPCE PSAPBDLS COMPUTE STATISTICS;

      6-------------------------------------------------------------------------------------------------------------
      Calculate rows to be updated by BDLS

      7------------------------------------------------------------------------------------------------------------
      Build CTAS command lines with decode funtions

      CREATE TABLE EDIDC_BDLS_BKUP

      PARALLEL COMPRESS FOR OLTP NOLOGGING

      STORAGE ( INITIAL 3253M NEXT 50M MINEXTENTS 1 MAXEXTENTS UNLIMITED)

      TABLESPACE PSAPNXP

      AS

      (

      SELECT

      MANDT, DOCNUM, DOCREL, STATUS, DOCTYP, DIRECT, RCVPOR, RCVPRT,

         CASE LOGSYS

           WHEN 'S1P100' THEN 'S1Q100'

           WHEN 'S1P500' THEN 'S1Q500'

           WHEN 'BWP200' THEN 'BWQ200'

           WHEN 'PIP300' THEN 'PIQ300'

           ELSE  LOGSYS

         END LOGSYS,

      RCVSAD, RCVSMN, RCVSNA, RCVSCA, RCVSDF, RCVSLF, RCVLAD, STD,

      STDVRS, STDMES, MESCOD, MESFCT, OUTMOD, TEST, SNDPOR, SNDPRT,

      decode(MANDT||SNDPRN,'500S1P500','S1Q500',SNDPRN) SNDPRN,

      SNDSAD, SNDSMN, SNDSNA, SNDSCA, SNDSDF, SNDSLF, SNDLAD, REFINT,

      REFGRP, REFMES, ARCKEY, CREDAT, CRETIM, MESTYP, IDOCTP, CIMTYP,

      RCVPFC, SNDPFC, SERIAL, EXPRSS, UPDDAT, UPDTIM, MAXSEGNUM

      From EDIDC

      );

      8----------------------------------------------------------------------------
      Perform coversion of tables where rows to update > 1000 using CTAS

      9-------------------------------------------------------------------------------
      Save original index definitions

      set timi on

      set echo off

      set head off

      set long 5000

      set pagesize 0

      set linesize 150

      column DDL format A150

      select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EDIDC~0') DDL from dual;

      10--------------------------------------------------------------------------------
      Drop original indexes
      Drop index COEP~0;

      11-------------------------------------------------------------------------------
      Rename original table to old

      rename EDIDC to EDIDC_ORIG;

      12-------------------------------------------------------------
      Rename newly created tables to original
      rename EDIDC_BDLS_BKUP to EDIDC;

      13-----------------------------------------------------------------------------
      Recreate indexes
      Create as per step 9 and NOLOGGING PARALLEL COMPUTE STATISTICS
      check for the NULL constratints

      ALTER TABLE "EDIDC" MODIFY RCVPRN NOT NULL;
      ALTER TABLE "EDIDC" MODIFY SNDPRN NOT NULL;

      14------------------------------------------------------------------------------
      Verify and Drop old table
      Drop table EDIDC_ORIG;

      15------------------------------------------------------------------------------------------
      Run BDLS in SAP using report RBDLS<CLNT> in parallel (A*....Z*, etc) using Background sessions.

      16------------------------------------------------------------------------------
            Clean up
       
      17----------------------------------------------------------------------------------
      Drop bitmap indexes
      drop index COEP_BDLS_IDX_12345;

      18----------------------------------------------------------------------------------
      Revert parallelism to original state for all tables and indexes

      ALTER INDEX "EDIDC~0" NOPARALLEL;

      19----------------------------------------------------------------------------------
      Drop function created, if any

      20----------------------------------------------------
      Drop extra tables created, if any

      21----------------------------------------------------
      Run update statistics
      brconnect -u / -c -f stats -t system_stats

      brconnect -u / -c -f stats -t oradict_stats

      brconnect -u / -c -f stats -t all -p 15 -f nocheck -f collect

      22----------------------------------------------------
      Put database back on ARCHIVELOG mode

      alert database archivelog;

      Regards,
      Nitin Salunkhe

      Author's profile photo Former Member
      Former Member

      Hello,

      Also for second part following line I guess it should be dba_tables instead of user_tables

      Instead of using time consuming SELECT COUNT(*), we decided to use data from the column NUM_ROWS in the table USER_TABLES

      Following is the query I have used for finding tables to do all these tasks. (sapecc is our schema name)

      SELECT a.tabname, c.num_rows FROM sapecc.dd03l a, sapecc.dd02l b,  dba_tables c WHERE a.domname in ('LOGSYS','EDI_PARNUM') AND a.as4local = 'A' AND a.tabname = b.tabname AND a.tabname = c.table_name AND b.as4local = 'A' AND b.tabclass = 'TRANSP' AND c.num_rows > 10000 order by c.num_rows DESC;