To fully utilize functionality such as Custom Objects, Application Configuration Packages and Subscriptions it is important to enable rowkey in tables applicable to rowkey in conjunction with upgrade to IFS Applications 10. The time it takes to attain full rowkey coverage in a typical IFS Applications production environment most often exceeds planned downtime for upgrade. This document outlines a process for incremental rowkey update prior to upgrade that minimizes effects on planned upgrade downtime and prevents business disruption.
A table is considered "rowkey enabled" when the table has a uniquely indexed, mandatory ("NOT NULLABLE") column named "ROWKEY" of data type "VARCHAR2(50)" with default value "SYS_GUID()" and every record in the table contains a unique value for the rowkey column.
Most tables in IFS Applications 10 contain optional ("NULLABLE") rowkey columns and, as such, do not meet these conditions. There are exceptions. A small set of tables used for functionality with strict rowkey requirements are rowkey enabled by default. A small set of tables do not use nor contain a rowkey column.
We refer to tables in IFS Applications 10 having optional ("NULLABLE") rowkey columns as "rowkey optional" tables and tables that are rowkey enabled by default as "rowkey mandatory" tables. We refer to tables in IFS Applications 10 that are either optional or mandatory as "rowkey applicable" tables.
An IFS Applications environment in which all rowkey applicable tables are rowkey enabled is considered to have full rowkey coverage. A given IFS Applications environment can have no, partial or full rowkey coverage depending on the extent to which rowkey applicable tables in the environment are rowkey enabled .
Note: The rowkey concept was introduced in IFS Applications 8, in which most standard tables were extended with optional rowkey columns. Since IFS Applications 9 new records in rowkey applicable tables automatically get unique rowkey values. IFS Applications 8 or 9 environments that utilize rowkey dependant functionality such as Custom Objects have partial rowkey coverage. Environments of versions prior to IFS Applications 8 contain no rowkey columns and thus have no rowkey coverage.
Enabling rowkey for rowkey applicable tables in IFS Applications environments having large tables and no or partial rowkey coverage takes a lot of time. Most of the time is spent populating unique rowkey values for records in rowkey applicable tables to satisfy the conditions that make them rowkey enabled. Numerous factors influence the time required for enabling rowkey which makes the process unpredictable.
Rowkeys can be enabled as part of an upgrade. Options available for the installer is described in section "5. Execute Installation and Deployment Actions" in the Deploy IFS Applications Database Objects section of the technical documentation.
The time required to attain full rowkey coverage in typical IFS Applications environments most often exceeds planned downtime for upgrade. For this reason, IFS provides a process and functionality for incremental rowkey update in a production environment prior to upgrade to a new version.
Central to the process is a recurring, time framed (e.g. an hour every night) database task that populates unique rowkey values for records in a predefined set of tables. The database task is capable of updating rowkey for non-active (historical) data to prevent business disruption caused by locks in the database. A default configuration to process non-active data in a number of IFS Applications tables known to grow large is provided when the database task is installed.
Recurring execution of the incremental rowkey update database task amortizes the cost of enabling rowkey over time. It is recommended to initiate the process months in advance of an upgrade.
The rest of this document describes the incremental rowkey update process.
On a high level, a number of steps must be followed to attain full rowkey coverage in an IFS Applications environment with no or partial rowkey coverage. The number of steps required depends on the version of the upgrading environment.
The incremental rowkey update process described in this document is meant to handle the bulk of the work of step 2 prior to planned downtime for upgrade by population of unique values in tables known to grow large in typical IFS Applications environments. The process also provides support for step 1 and 3. It is recommended to perform steps 1 and 3 during planned downtime to avoid business disruption.
Note: Steps 1 and 3 perform DDL statements to Oracle database tables which cause compilation errors due to invalidation of dependant database objects and PL/SQL package code relying on strict table definitions. After DDL statements have been performed these database objects need to be recompiled. In general, errors that remain after a recompile are possible to amend by changing code with implicit table column referral to explicit table column referral (see section Compilation Errors Due To DDL Statements below). Due to the invalidation of database objects steps 1 and 3 are better performed during planned downtime. The tables in the default configuration of the incremental rowkey update process will not cause non-trivial compilation errors with IFS R&D delivered database objects in IFS Applications 7.5 after performing step 1. Compilation errors due to step 3 are normally solved by recompilation.
It is recommended to formulate a strategy for how and when to perform rowkey update and to define a rowkey coverage goal before initiating the process. Full rowkey coverage is recommended for IFS Applications 10 but not enforced. Partial rowkey coverage may be chosen if rowkey dependant functionality only is to be used in selected parts of IFS Applications. Small databases may be feasible to update during planned upgrade downtime but typical IFS Applications environments are suited to the pre-upgrade, incremental approach described here. It should be noted that the default configuration of the incremental rowkey update will update rowkey of the majority of the large tables in a database, but the IFS Applications environment will still only partially be covered by rowkey. Remaining rowkeys need to be enabled during upgrade or post-upgrade.
A technical prerequisite for the incremental rowkey update process is to run the PRE_FNDBAS_UpdateRowkeyColumns.sql script which deploys the database task, a number of database objects that support the work involved, and installs the default configuration:
Note: Running the PRE_FNDBAS_UpdateRowkeyColumns.sql script will rename any existing table called Rowkey_Prepare_TAB to Rowkey_Prepare_OLD. If a table called Rowkey_Prepare_OLD exists, it will be dropped.
Assuming successful installation of the PRE_FNDBAS_UpdateRowkeyColumns.sql script, the following steps are to be followed.
The tables and optional where clauses referred in the Rowkey_Prepare_TAB table identify tables and data to process in the incremental rowkey update. There is normally no need to change the default configuration but the content of the Rowkey_Prepare_TAB table can be customized, if needed: records may be removed, added or updated to exclude/include tables. Where clauses may be customized and new ones provided, where applicable. The incremental rowkey update database task is typically run during uptime. It is crucial that non-active data is referred in the Rowkey_Prepare_TAB table to avoid business disruption caused by database locks. The default configuration is guaranteed to only update non-active data.
Note: It is not supported to update rowkey of tables other than those in the default configuration when upgrading from IFS Applications 7.5 or earlier.
Minimize the size of tables included in incremental rowkey update. The more records to populate with unique rowkey values, the longer time the update will take. Ensure cleanup jobs are executed in a timely manner and according to customer requirements. Not running adviced cleanup jobs or running them without relevant job parameters will affect database size negatively. Also consider using IFS Data Archive or other means of archiving to move or delete obsolete data from tables before enabling rowkey.
Remove unused triggers on tables included in incremental rowkey update. Tables with triggers will be ignored by the incremental rowkey update database task.
Remove materialized view logs, if possible. Tables connected to materialized view logs will not be processed by the incremental rowkey update database task. Materialized view logs were historically used for Business Analytics but has since then been phased out. The patch for LCS bug #77385 (applicable when upgrading from IFS Applications 7.5 or earlier) removes obsolete materialized view logs used for Business Analytics and should, if needed, be applied prior to running the incremental rowkey update job.
This step is only applicable when upgrading from IFS Applications 7.5 or earlier.
Ensure each table subject to incremental rowkey update has a non-mandatory ("NULLABLE") rowkey column with data type "VARCHAR2(50)". When the PRE_FNDBAS_UpdateRowkeyColumns.sql is installed tables in the default configuration will have state "AddRowkey" in Rowkey_Prepare_TAB if no rowkey column is found. Manually invoking the "Rowkey_Prepare_API.Add_Rowkey" procedure (see section Helper Procedures below) will create rowkey columns suitable for incremental rowkey update. Be aware that the "Rowkey_Prepare_API.Add_Rowkey" procedure invokes Oracle DDL statements which cause compilation errors. All database objects dependent on a table having a rowkey column added will be invalidated and needs to be re-compiled. The system cannot be used until all invalid objects are re-compiled after rowkey columns have been added. It is recommended to perform this step during planned downtime to avoid business disruption.
Schedule the "Pre update of rowkey columns" database task in a timely manner to prevent business disruption: time periods of low system load are ideal. Refer to section Database Task "Pre update of rowkey columns" for detailed documentation.
Once the "Pre update of rowkey columns" database task is scheduled, unique rowkey values will incrementally be populated in applicable tables. At this point it is recommended to monitor progress and tune paramaters for optimal performance. See section Monitor Progress for detailed information on monitoring.
The incremental rowkey update process is coordinated with the Rowkey_Prepare_TAB table.
The "Pre upgrade of rowkey columns" database task will try to match each table referenced in the TABLE_NAME column of the Rowkey_Prepare_TAB table against tables in the environment in which the task is run. Matched tables are included in the incremental rowkey update. An optional where clause referred in the WHERE_CLAUSE column of the table is used to filter records to process. If no where clause is supplied the entire table is processed.
The value in the STATE column of the Rowkey_Prepare_TAB table is updated to reflect the progress of each individual table during the process. During deployment of the PRE_FNDBAS_UpdateRowkeyColumns.sql script states are defined applicable for the default configuration and the environment the script is deployed in. The section below describe possible states.
The tables referred in the default configuration constitute reasonable defaults. This configuration may, however, be tailored to customer specific requirements. For instance, the state of tables in Rowkey_Prepare_TAB table may be changed:
(UPDATE rowkey_prepare_tab SET state = "[state]" WHERE ...)
If tables applicable to rowkey additional to the ones in the default configuration are to be processed prior to upgrade, new table references may be added manually to the table using regular INSERT statements.
Note: Incremental rowkey update of tables other than the ones in the default configuration is not supported when upgrading from IFS Applications 7.5 or earlier.
This is the job that do the actual update of the table. It can be run online or as a scheduled background job, e.g. one hour every night. The job processes all tables with state 'ToDo' in the working table. The procedure accepts five arguments.
| Argument | Datatype | Comment |
|---|---|---|
| chunk_size_ | NUMBER | The number of records in each commit. Default is 100000. |
| parallel_ | NUMBER | Determines whether to parallelize processes for rowkey upgrade. Parallelizing processes speeds up the job. This option is only available from Oracle11. Up to (max_num_processes/2) processes are used for rowkey update. Default is Y. |
| exec_time_ | NUMBER | How long time in seconds the job should execute. After the time has passed, the job will complete the current chunk and then quit. Default is 900. |
| alter_tables_ | VARCHAR2 | Y or N, determining whether table should be altered (the rowkey column is set to "NOT NULLABLE" with default "SYS_GUID()") if all rowkeys in a table are filled with values. A Y(es) means changed dictionary, and invalidation of objects. For this reason it is adviced to perform the alter table update during planned downtime or the actual upgrade to IFS Applications 10. Default is N. |
| only_prio_tables_ | VARCHAR2 | Y or N, determining whether to only update tables in the working table using specified where clauses (if any) or update all tables containing optional rowkey columns. Default is Y. Updating all possible tables (using N) is not supported when upgrading from IFS Applications 7.5 or earlier. |
State is set to 'Loaded' when the records in a table identified with the WHERE_CLAUSE are wholly updated with values during job execution.
State is set to 'Finished' when all records in a table have unique rowkey values and the rowkey column has been set as mandatory ("NOT NULLABLE") with default "SYS_GUID()". This state will only be reached if no records without rowkey remains after a rowkey update and the alter_tables_ argument is set to Y.
Once initiated, it's possible to monitor progress of the incremental rowkey update process by querying the Rowkey_Prepare_TAB table. The value in the STATE column is updated to reflect the progress of each table included for processing. The TOTAL_NUM_RECORDS column will contain the total number of records each table had at the time of deployment of the PRE_FNDBAS_UpdateRowkeyColumns.sql script. Column RECORDS_WITHOUT_ROWKEY in Rowkey_Prepare_TAB is continously updated with the number of records that do not have unique rowkey values for each table. The COMMENT column will contain progress information.
Scheduled background jobs initiated from the "Pre update of rowkey columns" database task will log information on tables processed in the Database Background Job detail page. When the "Pre update of rowkey columns" database task has no more data to process 'No rowkeys to enable were found' is logged on the Database Background Job. At this point the database task can be disabled.
Besides scheduling the "Pre update of rowkey columns" database task it's possible to execute the job manually via invocation of the "Rowkey_Prepare_API.Rowkey_Update" procedure accepting the same arguments as the database task (note that the method does not accept default values in difference to the background job). When manually executed, the information normally logged in the Database Background Job detail page is output using DBMS_Output. This approach facilitates manual handling of rowkey update and/or table alteration to set rowkey mandatory ("NOT NULLABLE") during planned downtime (ie. regular maintenance windows) prior to upgrade.
The example below let the process run for half an hour, and it will alter completed tables afterwards, i.e. it actually enables the rowkey.
EXECRowkey_Prepare_API.Rowkey_Update(100000, 'Y', 1800, 'Y', 'N');
One option can be to add a call to this from the install.tem, typically in section IFS Applications functionality, generated for the build.
A number of helper procedures are provided for common tasks. These are manually invoked, if needed.
It's possible to process all tables in an environment having optional rowkey columns by running the "Pre update of rowkey columns" database task with parameter only_prio_tables_ set to 'N'. The job will then process all possible tables based on metadata in the Oracle database instead of referring to the Rowkey_Prepare_TAB working table. When updating this way, rowkey update results for all tables are logged on the scheduled background jobs. Results for tables referred in Rowkey_Prepare_TAB will also be logged in Rowkey_Prepare_TAB.
Processing all rowkey applicable tables is, due to the risk of compilation errors, not supported when upgrading from IFS Applications 7.5 or earlier.
There are some known limitations.
It is important to verify that only fully declared inserts are used in all PL/SQL packages for the tables where rowkey is added, i.e. insert statement cannot be written like this:
INSERT INTO [table] VALUES ('Xxx', 1, 5);
New columns cannot be added without getting errors with this incorrect syntax. Instead statement should look like this:
INSERT INTO [table] ([col1], [col2], [col3]) VALUES ('Xxx', 1, 5);
Tables in the default configuration for Rowkey_Prepare_TAB are guaranteed not to cause non-trivial compilation errors in upgrade from IFS Applications 7.5 environments. However, no guarantees can be made for for customized code and integrations.