8.8 Updating Oracle Sequences

The OFSBD framework uses Oracle sequences for BD datamap component. To this end, OFSBD provides the ability to maintain the Oracle sequences used in Behavior Detection.

This utility must be compulsorily run by clients who are upgrading from Informatica to OFSBD at least one time at the end of the stage 1 upgrade process. This utility also doubles up as a maintenance utility for these Oracle sequences.

The shell script which must be executed for invoking this utility is run_update_ora_seq.sh. This script in turn calls a database procedure P_UPDATE_ORACLE_SEQUENCE. The database procedure P_UPDATE_ORACLE_SEQUENCE contains the logic to set the correct start value of Oracle sequences. The procedure internally drops and re-creates Oracle sequences by getting the max value +1 of the seq_id column from the base table as specified in the TABLE_NM column of metadata table KDD_ORACLE_SEQUENCE.

Clients upgrading from previous version of OFSBD to 6.2.1 version must run the script run_update_ora_seq.sh without any parameters.

For maintenance work, the script can be executed either by not passing any parameter or by passing either the table name or the Oracle sequence name as its optional parameter.

For example:

1. Without any parameter: run_update_ora_seq.sh
2. Passing table name or Oracle sequence name as parameter:

   run_update_ora_seq.sh<TABLE_NAME> OR 
   run_update_ora_seq.sh<ORACLE_SEQUENCE_NM>

If the table name OR the sequence name is not specified, then the utility performs the maintenance activity for all sequences mentioned in the KDD_ORACLE_SEQUENCE metadata table. If the script is called by passing the table name or the Oracle sequence name as its parameter, then the maintenance activity is done only for that particular table / Oracle sequence.

Note:

Do not modify the KDD_ORACLE_SEQUENCE metadata table unless specifically requested by the Oracle support team.

The log for this script is written in the run_stored_procedure.log file under the <OFSAAI Installed Directory>/database/db_tools/logs directory.

This script is a part of database tools and resides in the <OFSAAI Installed Directory> /database/db_tools/bin directory.

Clients who are upgrading from Informatica to OFSBD must run this utility at the end of the stage 1 upgrade process. Also, this utility can be run anytime there is a maintenance work on the database affecting the Oracle sequences. Additionally, there can be scenarios when the database is recovered due to some fault in the database requiring run of this utility. Failure to comply with this may result in Unique Constraints violation errors when datamaps are executed.

Note:

When executing run_update_ora_seq_sh, it may fail and display the following error: ORA-04006: START WITH cannot be less than MINVALUE. To fix this error, update dim_country set N_COUNTRY_SKEY = 0 where N_COUNTRY_SKEY = -999