Upload Process
Data upload process compares the data included in the blueprint file (generated by the extract process) and 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 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 the obsolete data in 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 "-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 upgrade in the target database should be same as that in the database from which the binary file was extracted.
Be careful while selecting the table and the selection criteria because to compare the data, the utility, for each table, first loads the data from the binary file and the database in the memory. If a table has huge amount of data and selection criteria set causes the utility to work on large quantity of data, it may run out of memory.
To avert 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 till either all the statements in iteration are executed successfully or they 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 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 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 about the action it is performing.
• -h Help.
This option will list all the accepted parameters with a brief description.
Note:
It is recommend that the implementers execute the upload process first in the verification mode and review the SQL before running the tool in the modification mode.