During migration, all data about the statements executed are stored in the statements table. Every row represents a single statement. If a statement fails during execution, information about the error is stored in the statement_errors table.

The table_id field is a foreign key to the migrate_tables table. This foreign key enables you to find all statements belonging to a single table. Most statements belong to a table that will be migrated. However, some statements, like switching all triggers on and off, operate on the entire database. Other statements, like the statements handling change tracking, are executed for all tables, not only for the tables that will be migrated. If a statement does not refer to a table, or if it is a statement for all tables, the foreign key table_id is NULL.

Isah aims to perform as many statements in single-user mode as possible. However, creating a fulltext index does start a background process. Therefore, creating fulltext indexes is done in a multi-user mode pass that will be executed after the single-user mode pass. Statements that need multi-user mode have the multi_user flag set. Multi-user statements will be created for a table during statement generation for that table. To avoid switching between single-user mode and multi-user mode, it is required to execute all multi-user statements for all tables after all single-user statements for all tables are already performed. Hence, the order in which the statements will be generated is not the same as the order in which these need to be executed. The primary key id reflects the generation order. If you want to list the statements for a run in execution order, you need to run the statement below.

select *

from dbo.statements

where run_id = 6

order by [multi_user] asc, id asc;

If the statement in statements performs the actual data transfer, the field is_transfer is set. Because the transfer statement performs the actual data transfer, this is the most important statement. Therefore, it is marked separately.

During the execution phase, when a statement starts, the exec_started field will be assigned the current date and time. When the statement is finished, the field exec_ended will also be assigned the current date and time.

Whenever the execution of a statement fails, the error is logged in the statement_errors table. The statement_id field is the foreign key to the statements table.

The statement_errors table has an insert trigger trg_statement_errors_ins that sets the exec_error flag of the failed statement in the statements table. If the failed statement belongs to a table, also the exec_error flag for this table is set in the migrate_tables table. Furthermore, if the failed statement is a transfer statement, the transfer_error flag for this table is set in the migrate_tables table.

Isah provides the all_statements view to enhance retrieving all data of the statements belonging to a single table or to an entire migration run. Example:

select *

from ANSI_Migration.dbo.all_statements S

where S.run_id = 6

and S.table_name = N'[dbo].[T_Action]'

order by S.multi_user, S.id;