How to delete table entries from dbmcli?

[Former Member]

Hello!

I have just restored a database backup from a system called AT8 to a system called AD8. I am using the note# 129352 - "Homogeneous system copy with MaxDB" as a guideline, but now I have gotten to a point where I need some help.

At 2c in the note, it says:

c) After the restore, delete all entries from the following tables so that the CCMS in the newly structured system does not display any actions (such as: backups and update statistics runs) from the original system: CNHIST, CNREPRT, CNMEDIA, DBSTATHADA, DBSTATIHADA, DBSTATIADA,DBSTATTADA, SDBAADAUPD

But how do I do that?

The next point on the list, is to do this:

dbmcli -d <database_name> -u <dbm_user>,<password> load_systab u2013u <sysdba_user>,<sysdba_user_password>

Is the sysdba_user = the "sap<sid>" user or the "control" user?

[lbreddemann · Accepted solution]

Hi Peter,

> At 2c in the note, it says:

> c) After the restore, delete all entries from the following tables so that the CCMS in the newly structured system does not display any actions (such as: backups and update statistics runs) from the original system: CNHIST, CNREPRT, CNMEDIA, DBSTATHADA, DBSTATIHADA, DBSTATIADA,DBSTATTADA, SDBAADAUPD

>

> But how do I do that?

You will need to logon via an SQL session (use SQL Studio, DB Studio or sqlcli for that) as the SAP-Schemauser. Ususally that's SAP<SID> or SAPR3.

Whenn logged in, you may just run the commands:

truncate table cnhist

//

truncate table cnrprt

//

...

> The next point on the list, is to do this:

>

> dbmcli -d <database_name> -u <dbm_user>,<password> load_systab u2013u <sysdba_user>,<sysdba_user_password>

>

> Is the sysdba_user = the "sap<sid>" user or the "control" user?

The sysdba_user in SAP installations is called 'superdba'.

So it's not the control user and not the sap<sid> user!

regards,

Lars

-

---

We just updated note 129352. Now there is a script attached to perform the truncates.

So you don't have to type in the commands yourself anymore.

Edited by: Lars Breddemann on Feb 24, 2009 1:59 PM

[former_member208623 · Accepted solution]

Hi Lars,

i have just a question regarding truncate mechanism and space deallocation in MaxDB.

We currently perform a split of a System, whereby we carve out the HR part (separate client) by performing a systemcopy.

After the copy we want to speed up the client deletion of the ERP client (SCC5) by truncating the big player of pure ERP tables which doesn't contain any HR data. (13day --> 3 days).

As a result the database is now just about 400GB (original 5,6TB). We can see that DB02 occupied area is continually decreasing from 5,xTB --> to now 2,xxTB.

If i summarize the table information in DB02 i get the information that there are just ~300GB occupied (without indexes).

To speed up the releasing process of the obviously not used space we tried to make a backup restore. However the backup is still 2,xx TB?!?

Can you give me some more information how the backup calculates its data? Can we speed up the "reorganization" process of the unused space shown in DB02?

thx, Christoph