
One of the least exciting requirements of being an SAP Technical consultant is the execution of BDLS scripts after system copies, normally with the refresh of QA system landscapes from a copy of Production. With modern SAN replication it is quite possible to copy a 14TB system online within hours, yet need to wait BDLS for 3~5days whilst the system executes BDLS but just running /nBLDS with default settings.
The problem is of course that SAP will perform a full scan every table with a logical system name field each time a BDLS conversion is run, on very large systems this results in runtimes of days particularly when converting multiple source logical system names (eg BWPCLNT100, PRDCLNT100, SCPCLNT100, CRPCLNT100)
There is quite some depth but more complex solution(s) in the articles from: muniraju.hBDLS IN LESS THAN 2 HOURS - Part 1
muniraju.h
Here are my top tips for BDLS improvements.
Here is my bdls.sql script that generates another sql script which contains create statements indexes on LOGSYS index tables scripts for Oracle.
--------------- bdls.sql --------------------------
set pagesize 0
set lines 255;
set feedback off
column createline format a255;f
column dropline format a255;
column aline format a255;
column rownum noprint
column table_name noprint
spool create_bdls_ind.sql
prompt spool create_bdls_ind.log
prompt set echo on
prompt set feedback on
prompt set timing on
select rownum, logsys.table_name,
'create index sapr3."'||
'bdls_index_newc'||rownum||'" on sapr3."'||
logsys.table_name ||
'"( "'||
client.column_name ||
'" , "' ||
logsys.column_name ||
'" ) PARALLEL 12 NOLOGGING TABLESPACE PSAPSR3;' createline,
'alter index sapr3."'||
'bdls_index_newc'||rownum||'" NOPARALLEL ;' aline,
'analyze index sapr3."'||
'bdls_index_newc'||rownum||'" estimate statistics sample 2 PERCENT;' aline,
'-- drop index sapr3."'||
'bdls_index_newc'||rownum||'" ;' dropline
from dba_tab_columns logsys, dba_tab_columns client
where (logsys.table_name,logsys.column_name)in
( select tabname, fieldname
from sapr3.dd03L
where domname in ('LOGSYS','EDI_PARNUM') )
and client.column_name in ('MANDT','CLIENT','RCLNT','MANDANT')
and logsys.table_name = client.table_name
and logsys.table_name not in
('EXCLUDETABLENAME1_EG_VBAK',
'EXCLUDETABLENAME2_EG_BKPF',
'EXCLUDETABLENAME2_EG_COPE' )
union
select rownum, logsys.table_name,
'create index sapr3."'||
'bdls_index_newnc'||rownum||'" on sapr3."'||
logsys.table_name ||
'"( "' ||
logsys.column_name ||
'" ) PARALLEL 12 NOLOGGING TABLESPACE PSAPSR3;' createline,
'alter index sapr3."'||
'bdls_index_newnc'||rownum||'" NOPARALLEL ;' aline,
'analyze index sapr3."'||
'bdls_index_newnc'||rownum||'" estimate statistics sample 2 PERCENT;' aline,
'-- drop index sapr3."'||
'bdls_index_newnc'||rownum||'" ;' dropline
from dba_tab_columns logsys
where (logsys.table_name,logsys.column_name)in
( select tabname, fieldname
from sapr3.dd03L
where domname in ('LOGSYS','EDI_PARNUM') )
and not exists (select 1 from dba_tab_columns client
where client.column_name in ('MANDT','CLIENT','RCLNT','MANDANT')
and logsys.table_name = client.table_name )
and logsys.table_name not in
('EXCLUDETABLENAME1_EG_VBAK',
'EXCLUDETABLENAME2_EG_BKPF',
'EXCLUDETABLENAME2_EG_COPE' )
order by 1,2
/
prompt spool off
spool off;
prompt Now start script create_bdls_ind.sql
exit
---------------------------------
For those who are not Oracle V7 masters :smile: , script doesn't change anything it is very safe ...it simply builds another sql script called "create_bdls_ind.sql" script that is a long list of create index bdls_index_<uniquenumber> on tables which have logical system name fields (LOGSYS) which BDLS scan and updates.
sqlplus sapr3/password @bdls.sql
<review the output file create_bdls_ind.sql>
You then (make sure this is on your target system) run the create_bdls_ind.sql with the command
sqlplus sapr3/password @create_bdls_ind.sql
(Naturally just ignore any errors with errors when it tries to create indexes on views rather than table ...you can prevent this with a by adding command like "and exists ( select 1 from dba_tables tab where logsys.table_name = tab.table_name), "
Before running the script you need to localize a few items
Here is my DB2 bdls.clp script, admittedly it is less refined and doenst have the union search for tables that have logsys but no client (which is relevant to BI/BW systems )but does the job ( dont hesitate to improve it and post your update)
-------- bdls.clp -------------
connect to PRD user sapprd using password ;
values 'connect to PRD user sapprd using password ; ' ;
values 'set current degree = ''10'' ; ' ;
select 'create index sapprd.'||
'bdls_index_newcd'||row_number() over()||' on sapprd."'||
logsys.tbname ||
'"( '||
client.name ||
' , ' ||
logsys.name ||
' ) collect statistics ; ' createline
from sysibm.syscolumns logsys, sysibm.syscolumns client
where (logsys.tbname,logsys.name)in
( select tabname, fieldname
from sapsrp.dd03L
where domname in ('LOGSYS','EDI_PARNUM') )
and client.name in ('MANDT','CLIENT','RCLNT','MANDANT')
and logsys.tbname = client.tbname
and logsys.tbname in ( select name from sysibm.systables);
-----------------------
run the script with the sh script command like this , which simply runs the clp command and then strips the first 6 characters from the output log to create the index build bdls_run.clp command.
mv bdls_run_log.clp bdls_run_log.clp.$$
mv bdls_run.clp bdls_run.clp.$$
db2 -tpxnf bdls.clp -z bdls_run_log.clp
cat bdls_run_log.clp | sed '1,6d' > bdls_run.clp
Running the index build shell script
mv bdls_run.log bdls_run.log.$$
db2 -tpxvnf bdls_run.clp -z bdls_run.log
drop_bdls.clp
connect to PRD user sapprd using password ;
values 'connect to PRD user sapprd using password ; ' ;
select 'drop index '|| name || ';' dropline
from sysibm.sysindexes ind
where upper(ind.name) like upper('bdls_index%');
do_drop.sh
mv bdls_do_drop.log bdls_do_drop.log.$$
db2 -tvpxnf bdls_do_drop.clp -z bdls_do_drop.log
Using SQL Server simply use cut-n-paste from within the SQL Management Studio
---------------------------------------------------------------------------------------
select concat('create index "' ,
'bdls_index_newc' , ROW_NUMBER() OVER (ORDER BY (SELECT 'A')), '" on ers."' ,
logsys.TABLE_NAME ,
'"( "',
client.COLUMN_NAME ,
'" , "' ,
logsys.COLUMN_NAME ,
'" ) WITH (MAXDOP=8) ; ' ) createline,
concat('drop index "' ,
'bdls_index_newc', ROW_NUMBER() OVER (ORDER BY (SELECT 10000)) , '" on ers."', logsys.TABLE_NAME,'" ; ') dropline
from INFORMATION_SCHEMA.COLUMNS logsys, INFORMATION_SCHEMA.COLUMNS client
where concat(logsys.TABLE_NAME , '||' , logsys.COLUMN_NAME ) in
( select concat( TABNAME , '||' , FIELDNAME )
from ers.DD03L
where DD03L.DOMNAME in ('LOGSYS','EDI_PARNUM') )
and client.COLUMN_NAME in ('MANDT','CLIENT','RCLNT','MANDANT')
and logsys.TABLE_NAME = client.TABLE_NAME
and logsys.TABLE_NAME not in
('EXCLUDETABLENAM1_EG_VBAK',
'EXCLUDETABLENAME2_EG_BKPF',
'EXCLUDETABLENAME2_EG_COPE' )
and exists ( select 1 from INFORMATION_SCHEMA.TABLES tables where logsys.TABLE_NAME = tables.TABLE_NAME and TABLE_TYPE = 'BASE TABLE')
union
select
concat('create index "',
'bdls_index_newnc', ROW_NUMBER() OVER (ORDER BY (SELECT 10000)) , '" on ers."',
logsys.TABLE_NAME ,
'"( "' ,
logsys.COLUMN_NAME ,
'" ) WITH (MAXDOP=8) ; ') createline,
concat('drop index "',
'bdls_index_newnc',ROW_NUMBER() OVER (ORDER BY (SELECT 10000)), '" on ers."', logsys.TABLE_NAME,'" ;') dropline
from INFORMATION_SCHEMA.COLUMNS logsys
where concat(logsys.TABLE_NAME , '||' , logsys.COLUMN_NAME ) in
( select concat(TABNAME , '||' , FIELDNAME )
from ers.DD03L
where DOMNAME in ('LOGSYS','EDI_PARNUM') )
and not exists (select 1 from INFORMATION_SCHEMA.COLUMNS client
where client.COLUMN_NAME in ('MANDT','CLIENT','RCLNT','MANDANT')
and logsys.TABLE_NAME = client.TABLE_NAME )
and logsys.TABLE_NAME not in
('EXCLUDETABLENAM1_EG_VBAK',
'EXCLUDETABLENAME2_EG_BKPF',
'EXCLUDETABLENAME2_EG_COPE' )
and exists ( select 1 from INFORMATION_SCHEMA.TABLES tables where logsys.TABLE_NAME = tables.TABLE_NAME and TABLE_TYPE = 'BASE TABLE')
order by 1,2
---------------------------------------------------------------------------------------
Normally I simply run the script and cut the createline output into another sql window and run it, delete index is done with the dropline
For SAP ECC systems tables still in row store would benefit from bdls indexes as well. Even in memory is improved with optimal access of data without needing to perform huge in memory buffer scans. That being said the HANA systems I have worked on so far are all BW analytics so I have yet to need to perform regular system refreshes. Please post your experiences
Looking forward to everyone to comment and suggest improvements or give warnings. I welcome comments.
Further improvements can be made with doing updates directly with sql statements using parallel statements eg
update /*+ PARALLEL(VBAK,12) */ VBAK set LOGSYS='TSTCLNT200' where LOGSYS='PRDCLNT100';
However this is of course updating SAP directly by database which is traditionally discouraged.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
19 | |
19 | |
9 | |
9 | |
7 | |
7 | |
5 | |
5 | |
5 | |
5 |