Clean up rxpubdocs and rxpubstatus tables

Rhythmyx Technical Discussion
1

Looks like those tables just grow and grow, and cms administrators need to consciously purge older publishing logs from time to time.

Anyone has some best practice or tools here?

THANKS

2

Hi Jason

I tend to have a stored procedure on daily schedule and pass in 5 as the number of days.

CREATE PROCEDURE delete_publication_log @days INT
AS

BEGIN TRANSACTION

    DELETE FROM dbo.RXPUBDOCS
     WHERE PUBSTATUSID IN (SELECT PUBSTATUSID FROM RXPUBSTATUS WHERE
           (DATEDIFF(dd,RXPUBSTATUS.STARTDATE, getDate()) > @days))

    DELETE FROM RXPUBSTATUS 
     WHERE (DATEDIFF(dd,RXPUBSTATUS.STARTDATE, getDate()) > @days);

COMMIT TRANSACTION
GO

Cheers
James

3

6.6 is supposed to have built-in support for this, but that’s at least a few months away.

We implemented and scheduled a similar procedure to this. The other part to think about are the filesystem files that get created along with these database records. See ${rx_home}/AppServer/server/rx/deploy/publogs.war directory. You can schedule a simple command or script in crontab for this, using find, exec, and rm together. Or just go in and wipe them out manually from time to time. Or let them build up until your disk is full :smiley:

4

Hello

I tried the pl/sql command:


delete  FROM RXPUBSTATUS
    WHERE (sysdate-RXPUBSTATUS.STARTDATE> 2);

delete  from rxpubdocs
where pubstatusid in (select pubstatusid from rxpubstatus where
(sysdate-rxpubstatus.startdate ) > 2)

However, when we log into the web application all of the publishing logs are still there. Did I miss something?

Thanks,
db.

PS. We have a publishing run going every ten minutes so doing this manually is not feasible.

5

However, when we log into the web application all of the publishing logs are still there. Did I miss something?

First guess… you didn’t “commit;” your delete.

Don’t forget to delete the corresponding log files from RhythmyxRoot/AppServer/server/rx/deploy/publogs.war/

6

I thought that too, but when preforming a

select count(*)  FROM RXPUBSTATUS
    WHERE (sysdate-RXPUBSTATUS.STARTDATE> 2);

I got the correct results.

7

You probably have the PHP configured to auto-commit. Depends on the function or package you’re using… For instance, the default behavior of the built-in http://us2.php.net/odbc_autocommit

8

I ran the sql directly from the sql console and deleted the log files from the directory as mentioned, but the log file still do not go away in the web interface. Any ideas?

9

delete FROM RXPUBSTATUS
WHERE (sysdate-RXPUBSTATUS.STARTDATE> 2);

delete from rxpubdocs
where pubstatusid in (select pubstatusid from rxpubstatus where
(sysdate-rxpubstatus.startdate ) > 2)

Might be the order they are run in (select pubstatusid from rxpubstatus where
(sysdate-rxpubstatus.startdate ) > 2) sub query should return nothing because you have already deleted the records with the first statement

Try running it this way round


delete  from rxpubdocs
where pubstatusid in (select pubstatusid from rxpubstatus where
(sysdate-rxpubstatus.startdate ) > 2)  

delete  FROM RXPUBSTATUS
    WHERE (sysdate-RXPUBSTATUS.STARTDATE> 2);
10

The problem was related to Oracle with regard to using some of its snapshot features.

The table was so huge that this would cause the transaction log and archiver to run out of space and prevent the delete from happening.

To clear this table with our configuration, the records have to be cleared without writing to the transaction log ( Oracle truncate or MS SQL bulk delete).

Hope this help others.