Symptom
The deletion of a client (by using Transaction SCC5) takes too long. Before you copy a client, you want to delete extensive tables in the target client in order to obtain better performance.
Additional key words
SCC5, clientremove, client delete, long runtime
Cause and prerequisites
There is no optimal deleting algorithm that ensures stability and performance on all databases and for all tables. In a conflict case, stability takes precedence in the standard system.
For this reason and in order to avoid resource problems, the standard system deletes the tables in packets. However, needless database accesses, which can take very long, may occur (FULL TABLE SCAN).
A FULL TABLE SCAN sometimes is the best access method and the Optimizer of the database can select this access method. This depends for example on the number and the size of the clients and on the status of the statistics. However, this access is very unfavorable if the system contains a large client having a smaller client number than the target client. This can even cause performance problems if the target client itself is small.
Under ORACLE, for example, you can usually avoid a FULL TABLE SCAN by switching to 'Rule based optimizer' (optimizer_mode = rule in init.ora). Sometimes there is no solution for other databases; or other deleting algorithms hold more advantages.
Solution
Refer also to Note 70643.
Subsequently, you must always call Transaction SCC5 in order to delete the client completely.
Unfortunately, SAP cannot supply a general optimum solution for the efficient deletion of a client.
SAP provides a number of tools. You have to decide which tool suits you best.
If you have significant experience of the methods described and have evaluated how useful (or not) they are, please let us know. If applicable, we shall include your experiences into this note or into our standard programs.
To do this, we need the following information:
- The Basis Release version with the relevant Support Package,
- the database you use and its version number,
- the names of the tables,
- the size of the tables in client and system,
(To determine the size, you can e.g. use report YSTABSIZ from Note
118823.),
- the order and the scale of the clients in the system.
For deletion of critical tables, this note provides several special reports which optimize the deletion.
Alternatively, you may use the R3trans with option 'clientremove' (Refer to Note 68896; Solution; 1.a). For problems and restrictions regarding the R3trans, consult the corresponding documentation and the corresponding notes.
If you use DB2 for OS/390, you can also proceed according to Note 143998.
1. For tables with less than 2.000.000 rows in the client (or for tables whose entries in the client fit completely into the roll area of the database (Key word: ROLLBIG refer to Note 12621).
Using report YKDELCLS as a template, you can create a report which deletes a group of tables. You will need the name of the client field of each table. You can find the names by using Transaction SE11.
2. For databases having limited space for the rollback area (for example ORACLE having MULTI-LEVEL CONSISTENCY LOCKING SEMANTICS)
If you have a Basis Release prior to Release 6.1, you can create the attached report 'ZTABDELE' for critical and large tables. To do this, you must replace all XXXX in the text by the name of the table. You can do this best in the ABAP Editor via 'Find and Replace' (binoculars icon or 'STRG + F').
The report then optimizes the reading in blocks from the database for this table (In order to avoid problems regarding rollback areas and working memory usage, the system has to delete in blocks in order to be able to send commits. To do this, the system has to read the complete data records prior to Basis Release 6.1. As of 6.1 or in report 'ZTABDELE' however, the system only needs to read the key fields.)
You can optimize the report for your system and your table via parameter NROWS (in the source code). With increasing size the runtime shortens; however, the demands on working memory (of the application server) and on rollback size (in the database) increase, too. In addition, the possibility of a TIME OUT increases.
3. For databases with row logging (for example DB2/390 and INFORMIX)
The attached report ZSDELTAB locks the table system-wide. You thus only use it if it is possible to access the table exclusively. Generally, you should run it over night or during the weekend.
The report is well suited if large clients exist that have a smaller client number than the target client.
Please be aware that this report does not work correctly in the DEBUG mode, since every COMMIT removes the lock. This may cause problems with LOGGING.
4. For tables whose second key field shows low selectivity
The attached report ZSDELDIS deletes all table entries with identical second key field in groups. If e.g. the second key field contains the company code and the system has three company codes, the table is deleted in three steps. Therefore only the particular largest company code needs to fit into the rollback area of the database.
If the second key field is very selective (if e.g. it contains a time stamp), the report should not be used, since it would delete every single line individually. Furthermore the report must not be used for POOL and CLUSTER tables.
5. Delete in blocks with OPEN CURSOR
The report ZSDELCUR writes the key fields of the table entries in blocks into an internal table. After the block is deleted, a COMMIT is executed. During this COMMIT, a OPEN CURSOR WITH HOLD is used to determine the beginning of the next block. In this way, you can avoid
a FULL TABLE SCAN.
This can yield particular performance advantages under DB2/390, for example.
However, keeping a cursor open via a COMMIT does not correspond to the ANSI standard. Under Oracle, for example, this code may cause an
ORA-1555 "SNAPSHOT TOO OLD", if the table was not reorganized or if there are too few rollback segments. Compare solution 2.b) of Note 96296.
Reports ZSDELTAB, ZSDELDIS and ZSDELCUR generate a report named ZDELnnnn (nnnn are the first four characters of the table name) to delete the table transferred as parameter; this report is then executed and deleted afterwards. If the report is not deleted, because the program terminated, you can delete it in the initial screen of SE38 (select 'Local object' in the popup).
If you have already started a deletion run (by using Transaction SCC5), which processes the same table, you should exit this deletion run. If you use several parallel processes (as of Release 4.6), you can also call report ZSETONPROCESSED for this table first; subsequently you can exit the process that processes the table (for example via Transaction SM50). The other processes will then delete all tables except for this one.
You can start the deletion reports parallel, but only one per table.
Reports YKDELCLS, ZSDELDIS and ZSDELTAB should be scheduled as background processes, since otherwise a TIME-OUT may occur.
In reports ZTABDELE and ZSDELCUR, you can increase the value for variable NROWS in the program in order to read and delete more rows in one action. However, this increases the request to the rollback area and the main memory of the application server. The width of the table and the size of NROWS influence the memory requirement in a linear way.
