Deleting Multiple Siebel Repositories

It's a common problem in a Siebel implementation. You and your team are following good practices. You use ADM or Dev2Prod to migrate your repository from a development environment to test, qa or production. As the project life cycle goes, you may end up with 10, 20 or even more Siebel Repositories in each of your environments. This overload of repositories will bring the enterprise to a crawl. EIM jobs might not start, Assignment Manager will not load, and many other problems will ensue. To alleviate this, you must clean out your repositories via deletion.



Compounding the problem of needing to delete repositories is the fact that it never fails you need to do this quickly due to some kind of outage or other time constraint. The only facility you have in Siebel is using Siebel Tools to one by one delete a record from the Repository object. Deletes of a single repository can take as little as 30 minutes or up to 4 hours, EACH!



Below is a T-SQL script I use to delete all repositories except the Siebel Repository. It can be easily modified to fit other target database platforms.



Back up your database first and then try it out in your test environment. Naturally, there are improvements which could be made, but as a quick solution, this certainly works quite well!



---Repository Delete

SET NOCOUNT ON
DECLARE @rw_id varchar(15), @table varchar(100), @err_num int, @err_msg VARCHAR(100)

DECLARE c1 cursor FOR SELECT ROW_ID FROM S_REPOSITORY WHERE NAME <> 'Siebel Repository'

DECLARE ct cursor FOR SELECT DISTINCT NAME FROM S_TABLE WHERE TYPE = 'Repository' AND NAME <> 'S_REPOSITORY' ORDER BY NAME

OPEN c1
FETCH NEXT FROM c1 INTO @rw_id

WHILE @@FETCH_STATUS = 0
BEGIN ----- 1st block ----

UPDATE S_REPOSITORY SET NAME = 'Deleting ...' WHERE ROW_ID = @rw_id
PRINT ' Deleting Repository with Row_Id -->' + @rw_id

OPEN ct
FETCH NEXT FROM ct INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN ------ 2ND BLOCK -----

PRINT 'Deleting from table -->' + @table
UPDATE S_REPOSITORY SET NAME = 'Deleting from table -->' + @table WHERE ROW_ID = @rw_id
EXEC('DELETE FROM ' + @table + ' WHERE REPOSITORY_ID = ''' + @rw_id + '''')
FETCH NEXT FROM ct INTO @table
END
CLOSE ct
DEALLOCATE ct

PRINT ' Deleting Repository Record with Row Id -->' + @rw_id
DELETE FROM S_REPOSITORY WHERE ROW_ID = @rw_id

PRINT 'Repository with Row_Id -->' + @rw_id + ' deleted.'

FETCH NEXT FROM c1 INTO @rw_id

END
CLOSE c1
DEALLOCATE c1