When you have migrated HiRDB from 32-bit mode to 64-bit mode, an attempt to insert or update data in a check constraint table that was defined in the 32-bit mode will result in an error. To enable insertion and updating of data in such a table in the 64-bit mode, you must restart HiRDB in the 64-bit mode and then re-define the check constraint table. The following figure shows the basic procedure for migrating a check constraint table to 64-bit mode.
Figure 12-42 Basic procedure for migrating a check constraint table to 64-bit mode
![[Figure]](figure/zu120330.gif)
To migrate a check constraint table to 64-bit mode:
- Check for any check constraint tables.
To determine whether there are any check constraint tables, execute the following SQL statement:
SELECT TABLE_SCHEMA,TABLE_NAME FROM MASTER.SQL_TABLES WHERE N_CHECK > 0
If the number of resulting rows is 1 or greater, there is a check constraint table. In the search results, TABLE_SCHEMA indicates the owner of each check constraint table and TABLE_NAME indicates the name of each check constraint table.
- Check for a view table.
If a check constraint table is dropped, the view tables that used the check constraint table are also dropped. Therefore, you must check for any view tables that used a check constraint table. To check for any view tables that used a check constraint table, execute the following SQL statement:
SELECT VIEW_SCHEMA,VIEW_NAME FROM MASTER.SQL_VIEW_TABLE_USAGE
WHERE BASE_OWNER=owner-of-check-constraint-table AND TABLE_NAME=name-of-check-constraint table
If the number of resulting rows is 1 or greater, there is a view table that used the check constraint table. In the search results, VIEW_SCHEMA indicates the owner of a view table and VIEW_NAME indicates the name of a view table.
- Create a view definition statement.
Use the pddefrev command (create a definition SQL statement) to create a view definition statement.
- Check for a referencing table.
If a primary key has been defined for a check constraint table and a referencing table that references that primary key has been defined, that check constraint table cannot be dropped. The referencing table referencing that primary key must be dropped. To check for any referencing table that references the primary key of a check constraint table, execute the following SQL statement:
SELECT CONSTRAINT_SCHEMA,TABLE_NAME
FROM MASTER.SQL_REFERENTIAL_CONSTRAINTS
WHERE R_OWNER= owner-of-check-constraint-table AND R_TABLE_NAME=name-of-check-constraint table
If the number of resulting rows is 1 or greater, there is an applicable referencing table. In the search results, CONSTRAINT_SCHEMA indicates the owner of a referencing table and TABLE_NAME indicates the name of a referencing table.
- Check for a view table.
If the referencing table is dropped, any view tables that used the referencing table are also dropped. Therefore, you must check for any view tables that used the referencing table. To check for any view table that used a referencing table, execute the following SQL statement:
SELECT VIEW_SCHEMA,VIEW_NAME FROM MASTER.SQL_VIEW_TABLE_USAGE
WHERE BASE_OWNER=owner-of-referencing-table AND TABLE_NAME=name-of-referencing-table
If the number of resulting rows is 1 or greater, there is a view table that used the referencing table. In the search results, VIEW_SCHEMA indicates the owner of a view table and VIEW_NAME indicates the name of a view table.
- Create a view definition statement.
Use the pddefrev command (create a definition SQL statement) to create a view definition statement using the referencing table that references the check constraint table.
- Create a table definition statement, unload data, and drop the referencing table.
Use the pddefrev command (create a definition SQL statement) to create a table definition statement for the referencing table. After the table definition statement has been created, unload data from the referencing table that is to be dropped, then drop the referencing table.
- Create a table definition statement, unload data, and drop the check constraint table.
Use the pddefrev command (create a definition SQL statement) to create a table definition statement for the check constraint table. After the table definition statement has been created, unload data from the check constraint that is to be dropped, then drop the check constraint table.
- Re-define the check constraint table and index.
Use the table definition statement created in step 8 to re-define the check constraint table and index.
- Check for a referencing table.
In the same manner as in step 4, check for any referencing table that references the check constraint table re-defined in step 9.
- Re-define the referencing table and index.
If there is a referencing table that references the check constraint table re-defined in step 9, re-define the referencing table and index using the table definition statement created in step 7.
- Re-define the view table.
If there is a view table that used the check constraint table or that used the referencing table, re-define the view table using the view table definition statement created in steps 3 and 6.
- Execute ALTER ROUTINE.
Execute the ALTER ROUTINE definition SQL statement because the function may have been disabled due to dropping of tables and view tables.
- Reload data into the referencing table and check constraint table.
Reload data to the re-defined tables.