Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Saturday, February 06, 2016

SAP SLD Change Log Cleanup Table Reorg

Print Friendly and PDF
When you apply changes or content updates to the SLD (system landscape directory) in SAP, the change log grows.
It's possible to see the change log entries from the SLD administrator page.
Due to this growth, the underlying database table will benefit from reorganisation at some point.
In our scenario, this was on DB2.
We decided to run the cleanup, but first here was the size of the table BC_SLD_CHANGELOG:
db2prd> db2 "SELECT TABNAME, NPAGES FROM SYSCAT.TABLES
ORDER BY NPAGES DESC"
TABNAME                       NPAGES
---------------------------- --------------------
BC_SLD_CHANGELOG              14162
BC_SLD_INST                   12830
J2EE_CONFIGENTRY              5870
BC_SLD_ASSINST                3905
EP_ATTR_VALUES3               3082
J2EE_CONFIG                   2163
DBH_STG_PKG_CACHE_METRICS     1421
SYSCOLDIST                    1193
Then delete the change log in http://<server>/sld
Select "Administration -> Maintenance -> Clean Up Change Log -> Remove Entries".
I then scheduled a standard job to do this work from the "Cleanup Task Configuration" tab.
Finally, reorg the following tables to release the space:
db2 "REORG INDEXES ALL FOR TABLE SAPPODDB.BC_SLD_CHANGELOG"

db2 "runstats on table SAPPODDB.BC_SLD_CHANGELOG AND INDEXES ALL"

db2 "SELECT TABNAME, NPAGES FROM SYSCAT.TABLES WHERE TABNAME = 'BC_SLD_CHANGELOG'"

TABNAME                       NPAGES
---------------------------- --------------------
BC_SLD_CHANGELOG              1205
That’s it.






2 comments:

Unknown said...

Schedule the job through SLD. Job ran and completed successfully without deleting the data from bc_sld_changelog table. In log getting below message

Job started on Tue, 18 Sep 2018 22:46:17:194 EDT
, Scheduler: e1f7a830a4a711dcc06c001708504156
, Job ->
, TaskName: Changelog Cleanup Task
, JobDefName: ChangeLogCleanupJob
, JobName: ChangeLogCleanupJob
, TaskId: 1252077bb76311e8a5ce000001acd50e
, JobId: 2e682a9ebbb611e89ccf000001acd50e
, JobDefId: ba623dbecfbc11e39e4400000075fdaa
, Node: Server 00 02_281039
, JMS MessageID: ID:66D60100000165E7-000000000033
, Current Attempt: 1
, Attempts Left: 4
, Re-Delivery Delay Interval: 2000 milliseconds Info 2018.09.18 22:46:17 EDT
The cleanup job has been started. Info 2018.09.18 22:46:17 EDT
The cleanup job will try to delete entries older than Thu Mar 22 22:46:17 EDT 2018 on behalf of user J2EE_ADMIN Info 2018.09.18 22:46:17 EDT
Detected versioned export performed on Wed Mar 29 02:03:37 EDT 2017, hence the retention period will be adjusted accordingly. Info 2018.09.18 22:46:17 EDT
Start the removal of change log entries in namespace sld/active, older than Wed Mar 29 02:03:37 EDT 2017 Info 2018.09.18 22:46:17 EDT
An error occurred during cleaning the SLD change log entries in namespace sld/active. Info 2018.09.18 23:02:23 EDT
The cleanup job execution has finished. Info 2018.09.18 23:02:23 EDT
Job.onJob() execution finished successfully
, JobUsedCpu: 31.08 seconds , JobUsedMemory: 7738274.5 KBs Info 2018.09.18 23:02:23 EDT
MDBJobDelegateImpl.onMessage():
, onMessageUsedCpu: 31.11 seconds, onMessageUsedMemory: 7740146 KBs Info 2018.09.18 23:02:23 EDT
Job ended on Tue, 18 Sep 2018 23:02:23:571 EDT

Darryl said...

Hi,

What version of NW is this on?

You should be able to check the SLD log from the administration page.
If necessary you can turn the trace level to "fine" before retrying the deletion.

Also check the logs under j2ee/cluster/server[0-9]/log/*.log

Regards,

Darryl