# Upload Process

Data upload process compares the data included in the blueprint file (generated by the extract process) with that extracted from a target database and generates output SQL to synchronize them.

The java utility `OraSdUpg` in `OraDBI.jar`, included in this package, is used by the process utility to compare and synchronize the data in the target database with that in the input blueprint file.

`OraSdUpg` reads an input parameter file for the list of the tables to be upgraded along with the selection criteria and upgrade rules for each table.

Each table has a corresponding record in the file with following 6 fields separated by semi-colon:

- Table Name
- The instance of the table. This number should be always set to 1. The cases where more than one instances of a table are processed are extremely rare and are not discussed here.
- Selection Criteria for the table.
- Insert allowed indicator (T/F): Whether records should be inserted into the target database table if they are missing in the database but exist in the binary file.
- Update allowed indicator (T/F): Whether records should be updated in the table if they have different values than in the binary file.
- Delete allowed indicator (T/F): Whether there is obsolete data in the table in the target database. Obsolete records exist in target database but not in the binary file.
- Fresh Install Indicator (T/F): Whether the table should be seeded during the very first install. This indicator is only used when the utility is invoked with a `-f` switch.
- List of columns not updated can be specified in the sixth field. Use a comma to separate the column names if multiple columns are to be ignored during updates. These columns will be inserted but will not be updated during the data synchronization process.

Following is the example of how these records should look like in the file:

`CI_LOOKUP;1;LANGUAGE_CD = 'ENG';T;T;F;T;DESCR`

A sample file `upload_cmsystbls.lst` has been included in this package. Implementer can make a copy of this file and edit it to match their requirements.

Before making connection to the target database, the utility reads the header from the blueprint and sets `NLS_LANG` environment variable on the client machine. It then validates this character set setting to the character set of the target db after making a connection and warns user if there is a mismatch.

The utility can be executed in verification and modification modes. In verification modes, the action SQL statements are simply written to the log file but in modification mode they are applied the target database.

It is very important to note that the primary requirement for `OraSdUpg` is definition (column and primary key) of tables being upgraded in the target database, which should be the same as in the database from which the binary file was extracted.

Be careful while selecting the table and the selection criteria; to compare the data and the utility for each table first loads the data from the binary file and the database in the memory, so if a table has a significant amount of data and selection criteria set, the handling of this data may cause the memory to run out.

To avert the unique key constraint violation error that can be caused by improper sequence of data deletion and insertion on a table and also the foreign key issues, the utility first gathers all the generated action statements for all the tables before executing them. The execution of all the generated statements is done in multiple iterations. After each iteration, all the failed statements during that iteration are collected and executed again in the next iteration. The iterations are repeated until either all the statements in iteration are executed successfully, or fail.

The utility disables and enables all the triggers on the tables being upgraded before and after applying database changes. No triggers get executed during the system data upgrade.

`OraSdUpg` accepts the following parameters:

- `-d <Connect String>`

  Where the Connect String contains:

- Schema owner name (say `CISADM`).
- Password for schema owner.
- Database name.

  This is a mandatory parameter. If not entered, the utility will prompt the user to build the connect string.

  Connect String should be entered in the following format:

  `CISADM,CISADMPSWD,DBNAME`

  (Comma-separated and no space).

- `-b` bypass the database character set validation.

  Before upgrading data in database, the utility validates character set stored in the blueprint by `OraSDBp` against that of the target database. The user can bypass this validation step by setting this switch.

- `-p <Input Parameter file name>`.

  Name of the input parameter file that the utility reads to get the list of tables and their selection criteria. This parameter is mandatory.

- `-i <Input Binary File>`.

  This is the name of the binary file that the utility reads to extract the data that it then uses to upgrade the target database. This is a mandatory parameter.

- `-f`

  Treats the data synchronize process as a new install. When set, the flag forces `OraSdUpg` to use "fresh install indicator" for the tables where `INSERT` indicator is set to `false` and compels it to insert missing records in all of them. Optional.

- `-u`

  Makes `OraSdUpg` run in the modification mode. Optional.

- `- q`

  Provide to run in silent mode (non-interactive mode). Optional.

- `-m`

  If it is provided, the DB triggers are not disabled. Optional.

- `-l <Log File Name>`.

  This is the name of the file that `OraSdUpg` creates (if it is missing), and starts appending the information to about the action it is performing.

- `-h` Help.

  This option will list all the accepted parameters, along with a brief description.

Note: It is recommended that the implementers execute the upload process first in the verification mode and review the SQL before running the tool in the modification mode.
Parent topic: CM System Data Packaging Tools