Reset the Catalog contents of the MDB

book

Article ID: 141565

calendar_today

Updated On:

Products

CA Service Catalog

Issue/Introduction

Sometimes, for example when making new tests on a development machine, it is necessary to empty the Service Catalog environment and start clean - however you may wish to leave other Service Management products intact on the database. Here is a step-by-step guide to doing so.

Environment

Release : 17.2

Component : CA SERVICE CATALOG

Resolution

These are the steps required to empty all Catalog data out of an MDB database, without either losing the tables relating to other Service Management products or needing to recreate the database from scratch.

Run any command-line scripts from a Service Catalog Command Prompt so all the necessary path and environment variable settings are correct.

1) Run the "uninstall MDB" script 

"%USM_HOME%\view\scripts\MDB\uninstallUSMDB.bat"

this empties all the USM-related database tables, but importantly does not delete them. If you want to see what it's doing, it calls usm_MDB_delete.dll, which is simply a big list of DELETE FROM commands for all the usm_* tables.

So you now have an empty database. If necessary, the log information for this step is in "%USM_HOME%\logs\install\usm_mdb_delete.log"

2) Next, you need to manually populate the Catalog default data into the database 

"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\view\scripts\MDB\SQL"

"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\view\scripts\MDB_Leh\SQL"

"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\view\scripts\MDB_SantaFe\SQL"

"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\catalog\scripts\MDB\SQL"

"%USM_HOME%\scripts\seeddata.bat" -n "%USM_HOME%/config.properties" "%USM_HOME%\accounting\scripts\MDB\SQL"

Seeddata loading writes its log to "%USM_HOME%\logs\install\seeddata.log"

3) In order to get subsequent scripts to run correctly, you need to tell scripts what release of Catalog you are. Run the SQL insert

insert into USM_SCHEMA_VERSION values ('17','0','0')

4) Then you need to manually re-apply SQL patches. Under the REPLACED folder there are a number of unconfigureMDB sql scripts, of the form

"%USM_HOME%\REPLACED\CA_SLCM_(version)\unconfigureMDB\configureMSSQLMDB-(version).sql"

so for the example of 17.2.03, these are the following (there are no SQL updates in the 17.2.0.2 patch)

"%USM_HOME%\REPLACED\CA_SLCM_r17.2.OLD\unconfigureMDB\configureMSSQLMDB-SP1.sql"

"%USM_HOME%\REPLACED\CA_SLCM_r17.2.OLD\unconfigureMDB\configureMSSQLMDB-SP2.sql"

"%USM_HOME%\REPLACED\CA_SLCM_r17.2.0.1.OLD\unconfigureMDB\configureMSSQLMDB-SP.sql"

"%USM_HOME%\REPLACED\CA_SLCM_r17.2.0.3.OLD\unconfigureMDB\configureMSSQLMDB-SP.sql"

It is necessary to execute each of these in turn. If cutting and pasting from the .sql directly into an SQL Management Studio query window, you will see an error from the "exit" command at the end of the batch script; this is a normal side-effect of how they were originally run through the command line.

Running the command

select * from usm_schema_version

will show that the system is now patched up to 17.2 again.

5) (optional) At this point, the Service Catalog system can be restarted, and is an entirely blank environment in terms of Service Offerings, request data etc. If you are migrating other data back in, then this may be all you need. It is also the last time to make changes to last ID values before data starts being created in the system.

However, if you need to recreate the default offerings that come out of the box, then you'll need to tell the system to redeploy them.

i) Launch the Service Catalog Setup Utility page.

ii) Skip straight to the Components tab - it is not necessary to redeploy the Database or Security (EEM).

iii) Set your Business Unit again, and click the Catalog and/or Accounting options, before selecting the content required.

iv) Click save, then finish.

Service Catalog will now restart, and you can log in to your clean system.