Converting Reports After Updating to 8.00A

Reports of tasks, agents, server processes etc. consume a lot of AE databaseA database is an organized collection of data including relevant data structures. space. This data is specifically converted when the database is upgraded from Automation Engine version 6.00A to 8.00A. This conversion process can take several hours depending on the amount of data involved. You can also reduce your system's downtime by subsequently copying the reports. For this purpose, it is not even necessary to shut down your system.

Only the reports of MS SQL Server and Oracle databases can be subsequently copied. Note that ILMStands for Information Lifecycle Management, which refers to a wide-ranging set of strategies for administering storage systems on computing devices. must not be activated and the computers on which the database scripts and the database run must not be shut down.

Reports can only be subsequently updated if the database has been updated to version 8.00A. If you update from version 6.00A to 9.00A, you must convert the report data during the updating process.

Important note: Do not reorganize the AE database during the whole procedure.

Follow the instructions that are provided in the update installation guide Updating an AE System until you reach the step "Updating the AE database".

The subsequent steps require the manual execution of AE database commands. Automic recommends that only database administrators execute these commands or at least that they provide assistance in doing so. A database user is required who has the relevant rights as described in the chapter Setting up the Database. Select the correct database type and switch to the section "Creating a database user" or "Defining an AE user." Note that the right to execute stored procedures is required.

You can also update your system without including the reports and delete them instead. In this case, execute step 1 and delete the tables RH_OLD and RT_OLD afterwards. Note that you cannot restore these reports.

Procedure

	1.	Before updating the AE database

• Change to the \db\general\8.00A directory.
• The DB directory is supplied in IMAGE:DB and must be stored in a line parallel to the utilities' BIN directory.
• Open the file UC_UPD.TXT.
• Change the line "process_sql_file do_report.sql" to "message process_sql_file do_report.sql." As a result, the reports (except for agents) are not converted during the updating process.
  The reports of agents are already converted with the statements that are included in the file "switch_report.sql". Subsequent changing of this data has the effect that previous reports are no longer available when the agent starts.
• Start the utility AE DB Load and select the file UC_UPD.TXT (see: Updating an AE System - Updating the AE Database). The database updating process starts.
• Perform all the remaining steps as described in the Update Installation Guide.

	2.	Converting the reports after the AE updating process

• Attention: Verify that there is sufficient space and that the RT/RH tables can be stored in the database before you convert the reports. RH stores the records of reports and RT stores the report text.
• Log on to the database with the user that has all the required rights.
• Manually execute the content of the file "do_report.sql" which was previously skipped. It converts the report data. The conversion process includes two stored procedures that have been created with the script "switch_report.sql".
• Execute the stored procedure UC_PREPARE_COPY_REP using the following command:
  Oracle: EXECUTE UC_PREPARE_COPY_REP;
  MS SQL Server: exec UC_PREPARE_COPY_REP
  
  In doing so, the temporary RH_TEMP and RH_TEMP2 tables are created. RH_TEMP is filled with the contents of the RH_OLD table which includes the original report records. All agent records that have already been converted using the stored procedures UC_GET_LAST_AGENT_LOG and UC_WRITE_LAST_AGENT_LOG are deleted from RH_TEMP.
  
  
• Call the stored procedure UC_WRITE_OLD_REP. Enter the number of records that should be copied at once followed by the subsequent waiting time in seconds.
  Oracle: EXECUTE UC_WRITE_OLD_REP(10000,1);
  MS SQL Server: exec UC_WRITE_OLD_REP '10000', '00:00:01'
  
  In doing, the first 10000 records of the RH_TEMP table are loaded to RH_TEMP2. These records are copied from RH_OLD / RT_OLD to RH / RT which already has the new dataset of version 8.00A. The procedure then deletes the copied records from the temporary tables. If no error occurs, this procedure continues until all data has been copied.

	3.	Handling "ah for rh not found" errors

• Repair procedures can only be executed if the reports have been subsequently manually converted.

Follow the steps below if the error message "ah for rh not found - use repair procedures" occurs in the stored procedure UC_WRITE_OLD_REP.

• Execute the stored procedure UC_REPAIR_FK_RH in the AE database:
  Oracle: EXECUTE UC_REPAIR_FK_RH;
  MS SQL Server: exec UC_REPAIR_FK_RH
  
  Each record that is no longer available in the statistics (AH) is deleted from the RH_TEMP table.
• Use the following command again:
  Oracle: EXECUTE UC_WRITE_OLD_REP(10000,1);
  MS SQL Server: exec UC_WRITE_OLD_REP '10000', '00:00:01'
  
  
• Repeat these steps if this error occurs again.

	4.	Handling "rh for rt not found" errors

Follow the steps that are described below if the error message "rh for rt not found - use repair procedures" occurs in the stored procedure UC_WRITE_OLD_REP.

• Execute the stored procedure UC_REPAIR_FILL_TEMP2. Enter the number of records that have already been used for UC_WRITE_OLD_REP.
  Oracle: EXECUTE UC_REPAIR_FILL_TEMP2(10000);
  MS SQL Server: exec UC_REPAIR_FILL_TEMP2 10000
  
  The temporary RH_TEMP2 table is filled with the records that caused an error.
• Execute the following command:
  Oracle: EXECUTE UC_REPAIR_FK_RT_ANY_DUPL;
  MS SQL Server: exec UC_REPAIR_FK_RT_ANY_DUPL
  
  Erroneous records of the RH_TEMP2 table are removed from RT/RH. The RH_TEMP2 table is emptied.
• Use the following command again:
  Oracle: EXECUTE UC_WRITE_OLD_REP(10000,1);
  MS SQL Server: exec UC_WRITE_OLD_REP '10000', '00:00:01'
  
  
• Repeat these steps if this error occurs again.

	5.	Deleting the temporary tables

• The temporary tables can be deleted from the database if the procedure has successfully been completed (stored procedure UC_WRITE_OLD_REP). Doing so is important because it creates disk space.
• The file "do_report.sql" includes some commented DROP commands. Remove the comments and execute the required lines manually in the AE database.
• The following commands are available:
  
  Oracle:
  drop table ah_temp;
  drop table rh_temp;
  drop table rh_temp2;
  drop table rt_old;
  drop table rh_old;
  
  MS SQL Server:
  drop table ah_temp
  GO
  drop table rh_temp
  GO
  drop table rh_temp2
  GO
  drop table rt_old
  GO
  drop table rh_old
  GO

Flow Chart of the Whole Procedure