Troubleshooting
Problem
When the region move tool is used to move the process region tables to an object store, if users launch a workflow, its F_UniqueId will start at value 1, but the value should actually be larger than any F_UniqueId value that currently exists in the database. This behavior only happens with Microsoft SQL Server databases.
Symptom
After using region move on a Microsoft SQL Server database, running peverify with scan or repair options against the destination workflow system will result in errors like the following examples :
dbo.VWObject1 [ERROR] sequence/identity table "VWObjectDynId1" - INVALID SEQUENCE/IDENTITY VALUE (1) DOES NOT MATCH WITH THE TABLE IDENTITY COLUMN "F_ObjInt" (value=100000000.000000)
dbo.VWQueue1_101 [ERROR] sequence/identity table "VWUniqueId1" - INVALID SEQUENCE/IDENTITY VALUE (1) DOES NOT MATCH WITH THE TABLE IDENTITY COLUMN "F_UniqueId" (value=4270054.000000)
Cause
The region move tool copies all VW tables into the specified destination database, however the sequence/identity values are not being handled correctly by the region mover tool. This is a bug in the region move tool.
Environment
CPE 5.0, CPE 5.1, CPE 5.2.1.x, CPE 5.5.0, CPE 5.5.1, CPE 5.5.2
Diagnosing The Problem
A. Verification steps - Let’s take a look into those 2 tables and their properties. Repeat these steps for each region that was moved.
In the SQL below, replace "<region_number>" with your actual region number, e.g. 1 or 2, or 999 etc.
Replace "<source_database_name>" with the correct source database name.
Replace "<dest_database_name>" with the correct destination database name.
Replace "<source_schema_name>" with the correct source schema name.
Replace "<dest_schema_name>" with the correct destination schema name.
1. Verification from the source
a) Check the identity value for VWUniqueId in the source database
use <source_database_name>
DBCC CHECKIDENT ('<source_schema_name>.VWUniqueId<region_number>', NORESEED)
Output:
Checking identity information: current identity value '80223', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note: The current identity value is '80223’.
b) Check the seed value VWObjectDynId in the source database
use <source_database_name>
SELECT seed_value, increment_value, last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID(‘<source_schema_name>.VWObjectDynId<region_number>');
Output:
seed_value increment_value last_value
100000000 1 NULL
Note: The seed_value is 100000000.
2. Verification from the destination database, after a successful region move execution.
a) Check the identity value for VWUniqueId in the destination database
use <dest_database_name>
DBCC CHECKIDENT (‘<dest_schema_name>.VWUniqueId<region_number>', NORESEED)
Output:
Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note: The current identity value is ‘NULL instead of '80223’ as seen in the source database, therefore the current identity value is incorrect. This is a confirmation that the problem exists in the destination database.
b) Check the seed value VWObjectDynId in the destination database
use <dest_database_name>
SELECT seed_value, increment_value, last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('<dest_schema_name>.VWObjectDynId<region_number>');
seed_value increment_value last_value
1 1 NULL
Note: The seed_value is 1 instead of 100000000 as seen in the source database, therefore the seed value is incorrect. This is a confirmation that the problem exists in the destination database.
Resolving The Problem
B. Use the following SQL to set the identity value back to the correct value as in the destination database. Repeat these steps for each region that was moved.
In the SQL below, replace "<region_number>" with your actual region number, e.g. 1 or 2, or 999 etc.
Replace "<database_name>" with the correct destination database name.
Replace "<schema_name>" with the correct schema name.
1. For the VWUniqueId<region_number> table
a) Reset the current identity for VWUniqueId
use <database_name>
set identity_insert [<schema_name>].[VWUniqueId<region_number>] on
insert into [<schema_name>].[VWUniqueId<region_number>] (F_UniqueId) values (100)
set identity_insert [<schema_name>].[VWUniqueId<region_number>] off
b) Verify the current identity is set correctly
use <database_name>
SELECT seed_value, increment_value, last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('<schema_name>.VWUniqueId<region_number>');
2. For the VWObjectDynId<region_number> table
a) Reset the current identity for VWObjectDynId
use <database_name>
set identity_insert [<schema_name>].[VWObjectDynId<region_number>] on
insert into [<schema_name>].[VWObjectDynId<region_number>] (F_ObjInt) values (100000)
set identity_insert [<schema_name>].[VWObjectDynId<region_number>] off
OR
use <database_name>
DBCC CHECKIDENT ('<schema_name>.VWObjectDynId<region_number>', RESEED, 100000)
b) Verify the new setting
use <database_name>
SELECT seed_value, increment_value, last_value
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('<schema_name>.VWObjectDynId<region_number>');
Output:
seed_value increment_value last_value
1 1 100000
OR
use <database_name>
DBCC CHECKIDENT ('<schema_name>.VWObjectDynId<region_number>', NORESEED)
Output:
Checking identity information: current identity value '100000', current column value 'xxxxxx'.
DBCC execution completed. If DBCC produces error messages, contract your system administrator.
Was this topic helpful?
Document Information
Modified date:
31 October 2018
UID
ibm10735443