Skip to Content

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    😎



6 Comments
You must be Logged on to comment or reply to a post.
  • 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 !!

  • 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. ?

  • 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

    • 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;