Previous Topic

Next Topic

Inhoudsopgave

Book Index

Tables migrate_tables and skip_tables

In the migrate_tables table, information is stored about the tables that will be migrated. Only tables that have ANSI_NULLS = OFF are suited for migration. Note that not all table types are supported (please refer to the list of unsupported table types). In the skip_tables table, information is stored about tables that have ANSI_NULLS = OFF but it cannot be migrated.

You can use the tables_not_migrated view to retrieve a list of tables that are skipped during migration, including the reason why the table is skipped.

Example:

select *

from dbo.tables_not_migrated

where run_id = 9;

ANSI: Migrate_tables_skip_tables

When data is transferred from the old to the new table, this can be done in two different ways. The data can be copied from the old to the new table, or the data can be transferred using a meta data switch. Using the meta data switch is much faster, but cannot be done in all circumstances. If a meta data switch is not possible, the migration script will set the do_copy flag, otherwise, the script will set the do_switch flag. As explained in tables objects_before and objects_after, there are two reasons why actual copying of the data is needed. Either, the table to migrate contains a column with the wrong ANSI_PADDING setting, or the table to migrate contains a computed column with a corrupted definition. The migration script will set the flags ansi_padding and/or suspect_column if one or both of these situations apply.

If one of the statements connected with a table fails, the migration script will set the exec_error flag in migrate_tables. If, for instance, a fulltext index of a table cannot be recreated, or if one of the foreign keys cannot be recreated, this bit will be set.

If the failing statement is the statement that transfers the data to the new table, the migration script will set both flags exec_error and transfer_error. The migration script will try to undo the changes. When the undo actions succeed, you can still use all Isah applications. However, the ANSI migration will fail for this table, so the database patch cannot be executed. Note that if a table has a clustered index that is not the primary key, this index is not dropped on the old table. Still, the migration script will try to recreate the clustered index. This will fail because the clustered index already exists. If another error occurs (apart from a failed data transfer with a successful undo), you need to address it.

If both the data transfer and the undo of the data transfer fail, the migration script stops the actions on this table. All the flags exec_error, transfer_error, and undo_error will be set. It means an unexpected error occurred.

In the skip_tables table you can find the names of the tables that are not going to be migrated and the reason for that.

The original_table field does not seem to belong to one of the unsupported table types.

This is true. Note that during the migration, tables will be renamed. For instance, the T_Action table will be renamed to tmp_ms_sql_server_T_Action. During the data transfer, a fatal error might occur. After migration, the database will contain the tmp_ms_sql_server_T_Action table. If the user accidently tries to migrate this database again, the renamed tables need to be excluded, because the whole process will fail again. Moreover, tmp_ms_sql_server_T_Action would be renamed again to tmp_ms_sql_server_tmp_ms_sql_server_T_Action. Instead, renamed tables will be skipped and the original_table flag will be set for these tables.