Upgrade Guide › Upgrading Databases › Performance Enhancements for Searches and Reports › Stored Procedure: wgn_queryflags_migrate

Stored Procedure: wgn_queryflags_migrate

(Optional) You can run a post-upgrade stored procedure (Wgn_Queryflags_Migrate) on the CMS to optimize CA DataMinder search and report performance.

This stored procedure (SP) migrates key data to a new database column and can take a long time. The CMS runs as normal while data is being migrated. However, we recommend that you schedule the SP to run during off-peak periods, typically outside of office hours.

After the SP has completed, we strongly recommend that you defragment your database indexes (see below).

Note: During testing, we observed migration rates of up to 6 million events per hour. However, actual migration rates in a production environment depend on your hardware configuration and data distribution.

Run the Wgn_Queryflags_Migrate SP

The procedure differs, deepnding on your database engine.

Oracle CMSs

In Oracle SQL*Plus, connect to the CA DataMinder database as the schema owner. If there is no separate accoutn for the schema owner, connect as the primary user.

Important! If your Oracle database has separate accounts for the primary user and the schema owner, you must run the Wgn_Queryflags_Migrate SP as the schema owner (for example, WGNOWNER). Do not run this SP as the primary user (for example, WGNUSER). This is because the SP is created under the schema owner and there are no grants or synonyms. If you do not have separate accounts for the primary user and the schema owner, run the SP as the primary user.

Run the following commands:

SQL> set serveroutput on size unlimited
SQL> EXEC wgn_queryflags_migrate;

SQL Server CMSs

Log in to SQL Server Management Studio as the primary user. Then run the following command:

EXECUTE dbo.wgn_queryflags_migrate

Prototype

The SP prototype is shown below:

Wgn_Queryflags_Migrate (
  BatchSize IN NUMBER,
  UpdateBatchSize IN NUMBER
  TimeOutMins IN NUMBER
  RestartMigrate IN NUMBER
  );

Where:

Batchsize

Defaults to 10,000. Specifies the number of events migrated in each batch.

UpdateBatchsize

(SQL Server only) Defaults to 10,000. Specifies the maximum number of rows updated in each batch.

TimeOutMins

Defaults to zero. Specifies the period (in minutes) before the SP times out. Use this parameter to restrict how long the SP runs. For example, if you schedule the SP to start at midnight, set this parameter to 180 (four hours) to stop the SP at 3am.

When you restart the SP after a timeout, the SP resumes migrating events at the point where it stopped.

If this parameter is set to zero, the SP never times out.

RestartMigrate

Defaults to zero. Restarts the SP process.

Oracle Example: This example sets the batch size to 50,000 with a three hour timeout. You must run a preliminary command before executing the SP:

SQL> set serveroutput on size unlimited
SQL> EXEC Wgn_QueryFlags_Migrate (Batchsize => 50000, TimeOutMins => 180);

SQL Server Example: This example sets the batch size to 50,000 with a three hour timeout and limits the number of updated rows in each batch to 10,000:

EXECUTE dbo.Wgn_Queryflags_Migrate @Batchsize = 50000, @UpdateBatchsize = 10000, @TimeOutMins = 180

Defragment the Database Indexes

After the SP has completed, some database indexes will be heavily fragmented becuase of the data updates. We strongly recommend that you run your standard index maintenance routines at the earliest convenient opportunity to remove any temporary performance degradation due to this index fragmentation.