Pre Database Verification

Deleting Govern Stored Procedures

This is the step in the process where existing Stored Procedures (stored procs) are deleted

System Stored Procedures

This process creates system stored procedures. These stored procedures are required by the Data Access Block (DAB).
Stored procedures are saved to SY_STORED_PROC_LIST and the parameters used in the search or modification are saved to the SY_STORED_ PROC_PARAM table.
The procedures in SY_STORED_PROC_LIST can begin with the following:

• s_: Select
• i_: Insert
• d_: Delete
• u_: Update

Examples of parameters stored in SY_STORED_PROC_PRM are:

• @na_id: This parameter is equal to the current NA_ID
• @user_id: This parameter is equal to the current user ID
• @internet_acct: This parameter is equal to the current Internet account

Miscellaneous Corrections

The Post Miscellaneous Corrections process performs the following actions:

1. Removes the blank spaces from the CODE column, In the VT_USR_ STATE table. All values in this column are state and provincial codes and are two characters in length.
2. Creates the records, in the VT_SY_ECOMP_FIELDS table, using a stored procedure.
3. Creates records, under the Validation Table Brule heading, in the VT_SYSTEM table.
4. Creates records, under the Validation Table LangIdent heading, in the VT_SYSTEM table.
5. Creates records, under the Web_PayType heading, In the VT_SYSTEM table.
6. Encrypts the nonencrypted number in the CC_CC_WEB table.
7. Updates the codes, under the table <month>, in VT_SYSTEM.
8. Transfers old column data to new ones.

Verifying Database Objects

For tables structure, refer to the ‘MSGDatabaseStructureMods.xml’ file.

Tables

This process will:

• Create missing tables with action=ADD.
• Delete existing tables with action=DROP.

Delete Indexes of type

This option allows the deletion of Govern indexes, or All indexes.
Select the Govern option (default) to delete only Govern indexes (Prefix: ‘PK_’ or ‘GV_’).
These indexes are automatically rebuilt. Selecting the All option will delete all indexes including those of user created tables.

Columns

This process will verify the columns.
It will:

• Create missing columns with action=ADD.
• Delete existing columns with action=DROP.
• Ensure that existing columns types are correct

Create missing BE Attributes

In the Columns verification process, it will check for Entities map to the table that the column is been added to.
If those Entities don’t already have an Attribute map to the newly added column, it will create it.

Indexes

This process will verify the indexes.
It will:

• Create missing indexes with action=ADD.
• Delete existing indexes with action=DROP.
• Recreate existing indexes if columns don’t match
• NEW! Recreate existing indexes if change to clustered; i.e. any existing non-clustered indexes will be recreated if they are changed to clustered in the system files during deployment.

USR_KEY_MASTER Stored Procedures

This process will create the Stored Procedure: ‘sy_Gov_GetAKey’.

New VT_System Table Records

This process will verify VT_System records. Refer to ‘MSGDatabaseStructure_SystemTables.xml‘ for structure.
It will:

• Create missing VT_System Tables with action=ADD.
• Create missing VT_System records action=ADD.
• Delete existing VT_System Tables with action=DROP.
• Create existing VT_System records action=DROP.
• Verify existing VT_System for properties changes.
  • If the record needs an update, an error will occurs with the required informations to execute an SQL update manually.

New VT_User Table Records

This process will verify VT_User records. Refer to ‘MSGDatabaseStructure_UserTables.xml‘ for structure.
It will:

• Create missing VT_User Tables with action=ADD.
• Create missing VT_User records action=ADD.
• Delete existing VT_User Tables with action=DROP.
• Delete existing VT_User records action=DROP.
• Verify existing VT_User for properties changes.
  • If the record need an update, an error will occurs with the required informations to execute an SQL update manually.

Functions

This process will verify Govern Functions. Refer to sections ‘functions_SQL‘ and ‘functions_Oracle‘ of the ‘MSGDatabaseStructureMods.xml’ file.
It will drop (if already existing) and create the function base on server type.
Views
This process will verify Views. Refer to the ‘ViewsScripts.xml‘ files.
It will:

• Create missing Views with action=ADD.
• Delete existing Views with action=DROP.
• Delete and recreate existing with.

Add validation Tables Poco Mapping View

During the Views verification process it will also add (recreate if existing) views used for mapping to validation table which are NOT included in the viewscripts.

USR_KEY_MASTER IDs Update

This process will verify IDs in table USR_KEY_MASTER. Refer to the ‘UsrKeyMasterIDsReferences.xml‘ files for IDs mapping.
For each IDs, it will:

• Check the highest number from all References.
• Update the Database field if the number obtain is higher than the Database one.

If ‘hascheckdigit=”True”‘ the last digit in the number obtain from the reference is remove before the compare is made.

System Data

This process will verify System Data. Refer to the ‘MSGDatabaseStructure_SystemData.xml‘ file.
It will add data (records) into given tables. If there’s a ‘vtuser‘ node it will add an new VT_USER as well.

Post Database Verification

Post Miscellaneous Corrections

This script is a final clean up process for the Miscellaneous Corrections.

Post System Stored Procedures

This is a correction script containing clean up procedures, for Govern Stored Procedures.

Govern Health Check

This process will execute multiple Health Check.

• Check Countries Correction
• Bankruptcy Health Check
• Building Parcel Link Health Check
• Attribute Encryption Health Check

Related Topics

User Key Dynamics
System Data Dictionary
View Query

What’s New!

NEW! Implementation of Clustered Indexes (Release 6.1.1811)

As of Release 6.1.1811, clustered indexes have been implemented on some system tables. These indexes will be recreated during the next Database Verification Process. Due to the nature of the procedure, a significant amount of time will be required to perform the process. The recommendation is that this procedure should be performed over a week-end, or a period of low activity. Although all applications are able to perform without the use of clustered indexes, the implementation of this new methodology can improve overall system performance.

Addition of VB6 Fail safe Notification for Verify Database Process

Users that are prompted with a notification when they are upgrading from Govern for Windows to OpenForms need to be made aware that upon running the Verify Database Process, their database will be irreversibly changed. This means that they will not be able to perform a rollback that will allow them to once again run Govern for Windows on their database. As a result of the impact of this action, they must be prompted with a dual confirmation message. The message will remind them of the importance of performing a full backup of their database prior to running the process. They must also confirm that they are about to proceed with an irreversible process. Suitable notification to this effect have been added. Now when the user clicks START [>], and the system performs a compatibility check, they will first be prompted with the following message:

Versions of the application and the database are not compatible.
You must run a Database Verification before continuing. This will update your database to <updateVersionNumber>.

RE / PP Tax Conversion Scripts to add required “RE” Cycle Codes

In Govern for Windows a Cycle Code was not mandatory. As a result of the architectural change in Govern for .NET, a Cycle code in Real Property (RE) and Personal Property (PP) is mandatory. The default Cycle Code is “RE”. A script will need to be provided in order to create a Cycle code and change all existing Data to this cycle code. See document below for details on the scripts.
103-ut-005_DB_Verification-RE_PP_Tax_Conversion_Script.pdf

New Process for Addition of System Columns
[Pending validation of release versions] When system columns have been added to the database, during the GNA Verify Database process, after the columns have been added, the process must scan all entities, and add a new attribute in all entities that use tables with added columns.
These changes are available in the following releases:

• 6.0.1608.x (6.0 Dev.)
• 6.0.1606.x (6.0 Rel.)
• 6.1.1608.x (6.1 Dev.)
• 6.1.1606.x (6.1 Rel.)

Standardized Storage of Boolean Data Types in Govern

In all releases of Govern, Boolean datatypes were stored in the database with three possible values:
FALSE = 0
TRUE = 1 or -1 (dependent on the version of Govern)
Newer tables predominantly used 1, whereas older tables used -1. In accordance with Governs’ ongoing standardisation process henceforth Boolean datatypes will be stored, WITHOUT EXCEPTION, as follows:
FALSE = 0
TRUE = -1
This standard will be carried out for all tables. This standard will be effective as of the following versions:

• 6.0.1608.0019 (6.0 Dev.)
• 6.0.1606.0023 (6.0 Rel.)
• 6.1.1608.0043 (6.1 Dev.)
• 6.1.1606.0042 (6.1 Rel.)

BA’s and Developers please refer to Confluence for additional details.

New Process in Database Verification Process

[6.0 / 6.1 Multiple Versions]

During the Database Verification process, at the ‘Check Column’ subprocess the application will now manage the description of fields in (Table:SY_DICT_DESC). The process is as follows, the Database Verification process reads the MSGDatabaseStructMods.xml file and reviews the SY_DICT_DESC. Each column is checked in the table to see whether the (ENG / FRA) descriptions exist, and if it needs to create (add) or remove (drop) them. In situations when a complete removal of a table is required, the process will remove any descriptions that are not connected.
Only the following fields are updated

• LANGUAGE
• TABLE_NAME
• FIELD_NAME
• DESCRIPTION
  • Uses the property ‘description’ limited to 50 characters; all additional characters are truncated
• DETAIL
  • Uses the property ‘description’

These changes are available in the following releases:

• 6.0.1603.0410
• 6.0.1606.0010
• 6.0.1608.0006
• 6.1.1603.0481
• 6.1.1606.0021
• 6.1.1608.0016

[Release 6.1.1509.117]

Need to perform a verify database process. This is to apply a correction made to change the NULL 0 in (Table: VT_SYSTEM) INVISIBLE.

[Release 6.0.1412]

The following will occur when a new database is created:
.Make sure “Govern Users” role exists, if not create it
.Make sure “govern” User exists, if not create it with the password govern
.Make sure “govern” is in the “Govern Users” role
.Make sure “Govern Users” role has full access to GSM AND GNA

Property to Re-create Indexes for Extended Tables

A new property called hasextendedtables has been added in the Tables section of the MSGDatabaseStructureMods.xml. This property is used to recreate indexes for extended tables like (Table: MA_BUILDINGS) that can support additional tables like MA_BUILDINGS_2 to MA_BUILDINGS_9.

Below is an example of the property in the MA_BUILDINGS table definition in the MSGDatabaseStructureMods.xml file.
<table name=”MA_BUILDINGS” description=”Mass Appraisal Building.” action=”add” gnaaversion=”4.7.3.0″ dbversion=”10.7″ hasextendedtables=“True“>

Currently this property has been added for the following tables:
· MA_BUILDINGS
· MA_LAND
· MA_MRA
· MA_INCOME PP_ASSESSMENT”

6.0 R and 6.1 R and in their development branches( 6.0 dev and 6.1 dev).
This new attribute is available in the following releases:

• 6.0.1606.x
• 6.0.1608.x
• 6.1.1606.x
• 6.1.1608.x