Activate and deactivate rowkey

If you want to extend an LU with some of the functionality like Custom Objects you need to activate the column called ROWKEY. ROWKEY is a column that s used by the framework to connect different database objects with each other. In a fresh install of IFS Applications 9 and IFS Application 10, ROWKEY is enabled by default. This means the ROWKEY is enabled for new rows in both fresh and upgrade installations. For an upgrade, enabling ROWKEY for existing rows is only performed on request.

Contents

Activate Rowkey

This step is an off line activity, meaning that you should do this step when you are alone on the system (in a maintenance window) or when there are little of almost none other activity in the system that can be affected. You need to find out all of the LU’s that that you want to extend with Custom Attributes and activate these LU’s on by one. What you do when you activate a LU is that you update the column ROWKEY in all your rows in this LU. The ROWKEY is updated to an Oracle GUID (32 byte string) using function SYS_GUID(). The more rows you have the more complicated update and the longer this operation takes. At the end the ROWKEY column is set to NOT NULL meaning that it must contain a value otherwise you will get an error. All IFS Applications LU’s is predefined with a ROWKEY column but they are not filled with data, since this would cause an upgrade of IFS Applications to take a lot longer time. Activating the ROWKEY also causes a small overhead whenever a new row is created. By letting our customers decide which LU’s should be activated we avoid lots of performance problems during the upgrade of IFS Applications and they also decide where this performance “problem” is worth taking. If you are unsure if activating a LU can lead to performance problems either test it in a test installation or discuss it with an IFS Consultant. All LU activations are done as a background job that makes 3 things:

  1. Update all the rows in the table where ROWKEY is null and set the ROWKEY to SYSGUID() column in batches of 10000 rows
  2. Set ROWKEY to NOT NULL and add a default value SYS_GUID()
  3. Recompile all invalid objects (packages and views dependent of the table we change).

Deactivate Rowkey

what you do when you deactivate ROWKEY is that you set ROWKEY to a nullable column and you remove the default value. This means that no value will be generated to new rows for this LU and it will be possible to have rows without any value in ROWKEY. Existing rows that might already have a value in column ROWKEY will be left as they are.

  1. Set ROWKEY to null and remove the default value

Note: Activation of Rowkey is considered to be an offline activity, meaning that it can disturb ongoing transactions in IFS Applications. You can compare Activating Rowkey with performing an upgrade or applying of a patch in IFS Applications.

Bulk-activation of Rowkey

Database Task "Update of rowkey columns"

The "Update of rowkey columns" database task may be scheduled for bulk activation of rowkey. When default parameters are used the database task populates unique rowkey values for a set of tables referred in the Database_Rowkey_Update_TAB working table. [A default configuration with active data of tables known to grow large in IFS Applications environment is preloaded in the table]. By overriding default parameters all rowkey applicable tables may be processed and rowkeys can, if possible, be set mandatory to satisfy all conditions that makes tables rowkey enabled.

In most respects the "Update of rowkey columns" works like the "Pre update of rowkey columns" database task used for rowkey update prior to an upgrade. The "Update of rowkey columns" database task differs in that is utilizes two where clauses.

The procedure accepts five arguments described below.

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_hrs_ NUMBER How long time in hours the job should execute. After the time has passed, the job will complete the current chunk and then quit. Default is 1.
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.

This can also been executed from prompt during a maintenance window, e.g. during a patch delivery. 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.

EXEC Database_SYS.Rowkey_Update_(100000, 'Y', 0.5, '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.