Overview
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.
Causation
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)
More Info
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
Top Tips
Here are my top tips for BDLS improvements.
- Naturally run in noarchivelog mode in Oracle or simple backup mode for SQLServer
- Build bdls helper indexes on all tables with logical system fields. Kick this off as soon as the system is started and let it run whilst you are performing the other refresh activities (profiles, printers, users etc)
- Executing BDLS conversions in parallel in background with variants eg A-K, L-S, T, U-Z , <not A-Z>.
- Optionally don't convert (exclude) really BIG tables if they contain transactional data that wont be referenced in your test landscape if so use individual named exclusion list in the BDLS variants and include those in the the "EXCLUDETABLENAME" sql index build script
- Once completed drop the bdls helper indexes by simply extracting the "drop index" from the build script below to create a "drop_bdls_index.sql" and run it.
Oracle
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), "
Oracle Localization
Before running the script you need to localize a few items
- Schema owner: Globally replace the schema owner string sapr3 with the schema of your system eg sapsr3 or sapprd
- Tablespace Name: Globally replace the tablespace name "PSAPSR3" with a tablespace of your choice that has sufficient space to create indexes; aim for about 1/20 of the total size of your tables. So if you have 10TB of table data you will need 500GB free of table-space.
- Bitmap or Btree: Consider moving 'create index' statements to 'create bitmap index' it should offer small indexes with improved lookup times. However I have used b*tree indexes by default.
- Parallelism: Change globally the PARALLEL 12 to approximately the number of CPU cores available in your server. I have seen improvement with setting PARALLEL to double the number of cores, but this is very server specific.
- PSAPTEMP: Make sure you have a very healthy PSAPTEMP again around 1/20 of the size of your table data.
DB2
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);
-----------------------
Localization for DB2
- Instance Name: Replace globally PRD with your DB instance name.
- Password: Update password with the real cleartext password
- Object Owner : Replace globally the string sapprd with the name of your database owner which normally remains the old prd schema for DB unless you do a schema conversion.
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
DB index cleanup is with another simple bdls drop.
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
SQL Server
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
---------------------------------------------------------------------------------------
SQL Server Localization
- Owner: Globally replace owner string 'ers' with the owner of your tables eg sapprd or srq.
- Parallelism: : Replace MAXDOP 8 with the parallelization your require maybe 4*CPU cores.
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
HANA
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
Final Comments.
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.
- SAP Managed Tags:
