[{TableOfContents }]

!!!Copy Pilot Data to Prod Database

Since the data within the PILOT database will continue to be changed by the implementation team, we need to periodically take the data to the PROD or CONV database to have the conversion process run smoothly. 

The following steps are used to copy the copy the contents of the PILOT database to PROD before the conversion process can start. These steps will have to be done each time the PILOT database has changed, and these changes will affect the data being converted.

!Step 1: Export
Export the data from the PILOT database. The following command can be used to export the entire contents of the database:

{{EXP SYSTEM/<PASSWORD>@PILOT FULL=Y FILE=PILOT.DMP LOG=PILOT.LOG CONSISTENT=Y}}

Note: you will have to replace <PASSWORD> with the actual password of the SYSTEM user.

This will create two files in the current working directory of the system the command is run from. The first file, PILOT.DMP, will be used to load the data into the production database (PROD). The second file, PILOT.LOG is only used as a reference, if something should go wrong during the export process.

NOTE: if you are a “Quick Start” customer, the database is located on an Oracle server in Toronto. High Line will have to export the data and provide this on the FTP server for downloading.

!Step 2: Drop [{$applicationname}]
Drop the contents of the PROD database. The database must be empty before new information can be loaded into it with Oracle import utility, IMP. Provided with the application is an SQL script for removing all the objects from the Oracle database owned by the P2K user. To run this script, you must run SQL Plus, log on as P2K and issue the {{@DB_DROP}} command. This script can take some time to run, but should finish in about 20 to 30 minutes. 

You may want to back up the database, prior to running this script. This SQL script will destroy all the [{$applicationname}] data in the database you are logged into. Make sure you have logged onto the correct database before running this script.

!Step 3: Import
Load the data from the PILOT database (or the Quick Start database) into PROD. The following DOS command can be used to import the data to the new database:

{{IMP SYSTEM/<PASSWORD>@PROD FILE=<DUMPFILE.DMP> LOG=PROD_IMP.LOG TOUSER=P2K FROMUSER=P2K}}

Note:
# This command must be entered onto one line. It was been wrapped into two lines in this example
# The <PASSWORD> must be replaced with the actual password of the SYSTEM user.
# The <DUMPFILE.DMP> must be replaced with the actual file name (PILOT.DMP in step 1).

The import process will generate errors during the creation of some of the Oracle objects. Generally, these errors can be ignored. The next step will clean up the data that did not get created correctly. If the clean up process does not correct all the Oracle objects the log file, list above, will need to be sent to High Line Support to identify the problem. 

!Step 4: Recompile
Clean up import errors generated when loading the data. The IMP command loads all the objects into the new database in a very specific order. This order does not match the order High Line uses to create a new database from scratch. This different load order attempts to create views and functions the reference functions that do not exist yet. To resolve these reference errors in the loaded objects, High Line has provided an SQL script that will recompile all the stored objects that are invalid. To run this script, you must load onto SQL Plus as P2K and issue the {{@DB_RECOMPILE}} command. 

This SQL script makes no changes to the data in the database. It has only three functions, rebuild the public synonyms, re-issue grants to the [P2K_USER] role and to compile and invalid Oracle objects. At the end of the script you should see a line the states “NO ROWS SELECTED”. This indicated that there are no invalid objects remaining in the database.

If there are still errors at the end of this process forward a copy of the log file generated by this script to High Line support. A copy of the DMP file and the log generated by the export may also be needed.

!Step 5: UNPILOT and Person Delete
Delete the test employees from the production database. The conversion process will only insert new employees into the database. If any employee information remains in the database prior to running the conversion process, some of the employees may get bypassed. 

Deleting the employee data is done in two steps. The first step is to delete the employees’ payroll information. This is done using a High Line supplied SQL script named DB_UNPILOT.SQL. Again, this script must be run in SQL Plus as P2K. The second step is to delete the employees them self. Again High Line has supplied an SQL script to do this called DB_PERSON_DELETE.SQL. This script is slightly different than the first, in that it will prompt for an employee to delete. Simply replying with a single percent sign [%] will delete all employees.

!Step 6: Reconcile Security
Since the users in the PILOT database may not exist as users in the PROD database, or may not exist with the same privileges, we need to recreate the security settings for the users. High Line has created an SQL script that can be used to read the users information and recreate them at the Oracle database level. Start an SQL Plus session, log on as P2K and run the DB_UMRS.SQL script.

!Step 7: Analyze the Database
Since the information in the database has changed significantly, it is recommended that you analyze the data and compute the statistics. Again, use the High Line supplied script DB_ANALYZE_SEL.SQL script to do this.