Writing Migration Reports to Database Tables

Scope: All migration projects

The three migration reports (Summary Report, Form Mapping Summary Report and Form Mapping Detailed Migration Report) can be written to tables on your database server. The database server that you write the reports to does not need to be either your migration source or target server.

The report details are written to the following tables on your database:

• PB_REPORT_EXEC_SUMMARY
• PB_REPORT_EXEC_FORM_MIGRATION_SUMMARY
• PB_REPORT_EXEC_RECORD_MIGRATION

Precision Bridge supports writing reports to the following database server types:

• Microsoft SQL Server
• Oracle
• Postgres
• Hana
• MySQL

Configuring the Reporting Server Connection

You select the server and enter the connection details on the Report Target of the Project Properties window when creating a new project or opening an existing one.

Handling Errors on Selecting the Server Type

Database connections require configuration tasks before they can be used. If this is the case with one (or both) of the servers defined by the template you will get errors and will not be able to define a connection of this type.

If the DB Server Driver configuration is incomplete or incorrect, you will get an error as shown below. See Application configuration settings for detailed information about DB server configuration.

For more information specific to configuring different servers for reporting, see the relevant database type within the section:

Configuring Server Connections

Creating the Database Tables

Before reports can be written to the database, the tables must be created on the database server. When you have a project open in Design View with the Report Target connection configured then you can generate the tables using the Project > Generate > Generate Report Tables menu.

You will be prompted to confirm the creation of the three reporting tables on your database. Click OK and the tables will be created for you.

Configuring your Project to Write Reports to Database

When the report tables have been created on your database server, you can specify that the reporting can be written to them in the Execution settings for your migration.

You have the option to write reports to file, the reporting server or Precision Bridge Wed. You can select multiple options. To write the reports to the database, select the Write Reports to Reporting Server option. 

Once the migration project has been executed, you can query the tables on your database using your preferred client.

Querying the Database

Summary Report: PB_REPORT_EXEC_SUMMARY

Example query:

SELECT exec_identifier, source_server, target_server, status, create_count, update_count, 
error_count 
FROM PB_REPORT_EXEC_SUMMARY;

Form Mapping Summary Report: PB_REPORT_EXEC_FORM_MIGRATION_SUMMARY

Example query:

SELECT guid, exec_identifier, mapping_name, source_form, target_form, create_count, 
update_count, error_count 
FROM PB_REPORT_EXEC_FORM_MIGRATION_SUMMARY;

Can be joined to the Summary Report by matching the exec_identifier column, for example:

SELECT s.source_server, s.target_server, s.status, ms.mapping_name, ms.source_form, 
ms.target_form, ms.create_count, ms.update_count, ms.error_count
FROM PB_REPORT_EXEC_SUMMARY s 
JOIN PB_REPORT_EXEC_FORM_MIGRATION_SUMMARY ms ON s.exec_identifier = ms.exec_identifier;

Form Mapping Detailed Migration Report: PB_REPORT_EXEC_RECORD_MIGRATION

Example query:

SELECT source_id, target_id, result, status, message 
FROM PB_REPORT_EXEC_RECORD_MIGRATION;

Can be joined to the Form Mapping Summary Report through the form_migration_uid column, for example:

SELECT ms.mapping_name, ms.source_form, ms.target_form, r.source_id, r.target_id, r.result, 
r.status, r.message 
FROM PB_REPORT_EXEC_RECORD_MIGRATION r 
JOIN PB_REPORT_EXEC_FORM_MIGRATION_SUMMARY ms ON r.form_migration_uid = ms.guid;

You can also run a query with all three forms joined for a complete report, for example:

SELECT s.source_server, s.target_server, ms.mapping_name, ms.source_form, ms.target_form, 
r.source_id, r.target_id, r.result, r.status, r.message 
FROM PB_REPORT_EXEC_RECORD_MIGRATION r 
JOIN PB_REPORT_EXEC_FORM_MIGRATION_SUMMARY ms ON r.form_migration_uid = ms.guid
JOIN PB_REPORT_EXEC_SUMMARY s ON s.exec_identifier = ms.exec_identifier;

Related Articles

Migration Reports

Form Mapping Options

Web Reporting Functionality