Additional Blogs by Members
cancel
Showing results for 
Search instead for 
Did you mean: 
muniraju_h
Explorer
7,540

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    :cool:



6 Comments
martin_E
Active Contributor
0 Kudos

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

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 !!

former_member183872
Participant
0 Kudos

Excellent Article.

former_member183829
Participant
0 Kudos

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

muniraju_h
Explorer
0 Kudos

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

Former Member
0 Kudos

Excellent explanation and solution for this always time consuming refresh activity :smile:

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

Former Member
0 Kudos

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;