I was recently asked if it was possible to speed up migrations of the Rule Team Server (RTS) database between versions of IBM Operational Decision Manager (ODM). Given that the RTS database maintains a version history for each artefact, if you've been using the product for a while and actively making changes, it's possible that you've generated a large amount of history that will then be time-consuming to migrate.
In this article, I will provide a step-by-step example of how to use the supplied archiving tools to minimise the size of your RTS database prior to migration. The archiving tools work by copying all active rules and any rule history up to a date you specify into a new database schema, effectively performing a pruning operation. The new schema is created within your existing database, and then SQL statements move the data between the existing and new tables. All baselines which either reference an active rule or contain history more recent than the supplied archiving date are preserved and any data stored in an extension model will also be preserved.
In this example, I am using WebSphere ILOG JRules 7.1.1 on RHEL 6.3 with DB2 10.5 and WebSphere Application Server 8.5. Hopefully you should be able to easily transpose these instructions onto your own environment.
There is a page in the InfoCenter which covers this topic, which you can review here.
Step by step example
1) Prepare your environment
The archiving tools make use of some Ant tasks, so start by making sure you're environment is configured correctly to use Ant.
2) Backup your RTS database
The archiving process involves creating a new database schema, and then running a number of SQL scripts against your RTS database. Before you start, take a backup of your RTS database using your favourite method. For DB2, I stopped all connections to the database using db2 force application all and used the DB2 backup command in a newly created directory: db2 backup database <db_name>.
3) Create a new database user
This user will be used to create the new schema. For DB2 on RHEL, this was as simple as running adduser <newuser> (I used archive on my third attempt, if it's been a while, I suggest you read the DB2 user name restrictions).
4) Create a new schema in your existing database for the archive
To create the new schema into which your active rules and selected history will be copied you have two choices:
Use the Decision Center Enterprise Console Installation Settings wizard.
Use the set-extensions Ant task
Both methods create the new schema based on the username supplied for the RTS JEE data source. The new schema is given the same name as the user who's credentials are used to access the data source. The new schema must be created in the same database as the existingl schema.
I used the Installation Settings wizard. To do this, I first changed the JDBC name of my existing RTS datasource from jdbc/ilogDataSource to jdbc/ilogDataSourceOriginal and then created a new data source "jdbc/ilogDataSource" using a new J2C authentication alias for component-managed authentication pointing at my new database user.
(the rtsArchive J2C ID has the username archive)
After a stop and restart of WAS, visiting the Enterprise Console presented me with the Installation Settings wizard so that I could run through the steps.
If you use the set-extensions Ant task instead, this connects to the data source you specify and then executes SQL to create the tables and upload extensions. Here's an example:
ant set-extensions -Dserver.url=http://localhost:9080/teamserver -DdatasourceName=jdbc/ilogDataSource -DextensionModel=defaultExtension.brmx -DextensionData=defaultExtension.brdx
IMPORTANT: If you're existing RTS database uses an extension model, you must create the new schema using the same extension model and data (the defaults reside in <install_dir>/teamserver/bin).
5) Run the gen-archive-repository-role Ant task and execute the resulting SQL
This Ant task generates a SQL script that creates a new role, and then grants privileges to that role to perform certain operations on your existing RTS database. The generated SQL must be executed against your RTS database by a user with administrative rights.
I'm providing a fully-qualified command line version here for you to modify and use, but note that you can set any of the -D properties in the <install_dir>/teamserver/bin/teamserver-anttasks.properties file instead. If you do this, you can omit them from the command line.
ant gen-archive-repository-role -Dserver.url=http://localhost:9080/teamserver -DdatasourceName=<target_datasource> -DoldDatabaseSchemaName=<existing_db_username> -DoutputFile=gen-archive-repository-role.sql
I ran the generated SQL against DB2 using the command line:
su - <admin_db_user>
db2 connect to <RTS_db>
db2 -tvf gen-archive-repository-role.sql
6) Run the gen-archive-repository-script Ant task and execute the resulting SQL
This Ant task generates the SQL which copies artefacts from the existing RTS database and into the new schema. Once the script has been generated you can either execute it with the execute-schema Ant task or use your favourite database tooling.
ant gen-archive-repository-script -Dserver.url=http://localhost:9080/teamserver -DdatasourceName=<target_datasource> -DoldDatabaseSchemaName=<existing_db_username> -DarchiveDate=<YYYY-MM-DD> -DoutputFile=gen-archive-repository-script.sql
For our purposes here of pruning the history, the key property is archiveDate. If this was set to 2013-11-11, in addition to all active rules, history for all artefacts newer than this date will be copied into the new schema.
Again, I ran the generate SQL against DB2 using the command line:
su - <db_user>
db2 connect to <RTS_db> user <new_user>
db2 -tvf gen-archive-repository-script.sql
IMPORTANT: Ensure you run the generated SQL in this script as the new database user created in step 3.
Once this script had completed, I went back to the Enterprise Console and checked out the history for one of my rules: