Upgrade Script Standards

The purpose of this document is to describe the upgrade script standards used in IFS Applications. This document aims at guiding the developers when writing UPG, CDB, CRE, SQL and INS files.

Contents

• Introduction
• UPG Files
• CDB Files
• Project Delivery CDB Files
• SQL Files
  • PRE Script
  • Post Script
  • Clear Script
  • Drop Script
• CRE Files
• INS Files
   

Introduction

File Types Used During the Upgrade Process

UPG

UPG (upgrade) files are used when upgrading from a previous version (track) to a new version (track). These files are only executed during an upgrade process and not during a fresh installation. Depending on the current version of IFS Applications available in your environment you may need to execute several upgrade files in order to upgrade to the latest CORE version. Eg. IFS APP7 to IFS APP75

CDB

CDB files are created to fix DDL (Data Definition Language) and DML(Data Manipulation Language) issues in support versions. E.g. adding, removing and changing table columns and inserting or updating data into new or existing columns.

CDB files are merged to UPG files when a Core Version of IFS Applications is released. CDB files are executed:

• When upgrading to a new service pack from the current CORE release
• During a service pack upgrade . E.g.. APP75-SP1 to APP75-SP2
• When Installing Single patch.

SQL

There are different types of SQL files and they are used for different purposes. Such as:

• Pre-scripts : Pre-scripts are run during preliminary stages of an upgrade and this need to be done manually depending on the requirement.
• Post-scripts : There are two types of post scripts namely Automatic and Manual. Post script files are run after deploying all modules, compiling invalid object and granting security.
   
  • Manual Script: Manual scripts require editing, manual interaction and is run in special cases.
  • Automatic Scripts: These scripts are automatically executed by the installer and are mentioned in the deploy.ini file in the respective module section [POST_INSTALLATION_DATA]
• Drop Script: The Drop SQL file is used for removing an entire module from the database.
• Clear Script: Clear script files are used to remove unused columns and renamed tables that have been made obsolete.

CRE

Create files(.CRE) are mainly used during fresh installations and NOT during the upgrade process. However, there are some exceptions to this rule. For e.g. the Luconnection.cre files are run during an Upgrade even though it is a CRE file. CRE files are usually prefixed to the module that they belong and have the DDL statements to create the table structures such as DB Objects, tables index, constrains...etc used by that component. Some modules contain separate CRE file for creating Sequences, report tables (RPTs) that are used by the module.

UPG Files

General Section

• An UPG file should have this section included as follows:
  • File name
  • Module
  • Purpose
  • Design History
    • For new files, design history should be entered in date order (i.e. new entries first)
    • In some of the older files this may have been done in the reverse order. In such an instance the convention used in the file should be followed.
    • Every change done for an Epic (formerly IID) or Bug correction should have an entry for design history.
      • Bug ID or IDD should be mentioned as a reference to trace the reason for DDL or DML statements.
• SET SERVEROUTPUT ON statement
  • This statement allows the information messages that are generated by Database_SYS public interfaces to be visible in the installation log.
  • SET SERVEROUTPUT ON statement should be included at the beginning of the file after the design history section.
  • It is Not necessary to include this in every PL/SQL block and it is sufficient to have it once

  • SIZE should not be specified

    • Default SIZE is unlimited and therefore not necessary to manually override the setting

  Note  :SET SERVEROUTPUT OFF Statement should not be placed anywhere in the file

            The Installer will take care of the OFF statement.

Sequence Section

Creation of all database sequences  and modifications should be done under this section.

Remove obsolete objects Section

• Obsolete Views, packages, RPT tables, Temporary Tables...etc can be removed in this section
• Never drop any columns or tables
  • Dropping tables or columns may result in a data loss.
  • Usual practice is to rename the table and place it in <Component-short-name>cl.sql file
    • Renaming Standard : Assuming the table EXAMPLE_TAB has become obsolete for APPS8 in ACCRUL component (version of UPG file is 900),
      • The Table should be renamed to EXAMPLE_900 in UPG
      • Remove  EXAMPLE_900 in Accrulcl.sql
      • Removal of Obsolete columns can also be done in <Component-short-name>cl.sql file
      • If the column is a NOT NULL column, then the column should be made null-able in the UPG file before removing in the < Component-short-name >cl.sql
• It is possible to drop temporary tables and columns in a Report table (RPT) in UPG files

General Guidelines

• If possible everything regarding one table i.e. Table creation, alterations, data updates etc... should be grouped under one section.
  • It is easier to figure out all the changes for a particular table without having to scan through the complete file
• If this is not possible, a proper comment about the reason should be added.
• Each section should be started with a comment line (i.e. set of dashes) and an empty line.
•  The section should Start with a line like: "-- ***** Example_Tab Start *****"  and -- End with a line like: "-- ***** Example_Tab End *****"
  • Example:

    -- ****** Example_Tab Start *****

    <code to work with the example_tab table goes here>

    -- ****** Example_Tab End ******
• SQL/PLSQL Keywords should be in upper case
• Every PL/SQL block inside a section should have a proper PROMPT
  • These prompts are shown in the installation log files and it would be very useful for locating errors.
• Time Stamps
• Each PL/SQL block should be preceded by a Time stamp
  • In Projects, time stamps are added via a tool. Therefore it is not mandatory to add these manually.
  • In support however, these should be added manually.
  • More Info :TMF Documents and Guidelines
• Use of COMMIT statement
  • All DML statements should be followed by a COMMIT statement.
    • Note:- If there are several DML statements that should be treated as a single transaction, then the COMMIT statement may be included after the last of such statements.
  • If any PL/SQL Block consists purely of DDL statements, then COMMIT statement is not required.
    • DDL statements are implicitly committed and therefore COMMIT statement is unnecessary.
    • Note:- Any un-committed DML transactions will also get committed when a DDL statement gets executed
• Large Transactions
  • Not necessary to include partial commits – because
    • With oracle 10g and upwards, it is possible to set up rollback segments to automatically extend when required.
    • Therefore it is not necessary to free up roll-back-segments by introducing partial commits
    • Consider about the performance and write as efficiently as possible
• Use of ACCEPT and ‘&’ character
  • Use of ACCEPT keyword should be avoided
    • This halts the execution waiting for input
  • Use of ‘&’ character
    • Should only be used for defined values (either locally or in deploy.ini file of the component)
    • Should not be casually used even in comments
      • This may halt the execution waiting for input
    • Most of the time the upgrade scripts get executed unattended. Therefore the execution should not halt awaiting user intervention.
• Use of Default values for Table columns
  • It is not recommended to use default values for table columns (specially NOT NULL columns) .
    • Use of 4th parameter in the DATABASE_SYS. Set_Column_Values
  • Business logic should provide default values for columns
• Performance considerations
  • All DML statements should be optimized.
  • Use of Pure SQL statements whenever possible is highly recommended
    • Direct SQL instead of CURSOR FOR LOOP’s
  • More Info :TMF Documents and Guidelines
• Use of Database_SYS
  • Use public interfaces available in this package for your DDL statements
  • When calling methods in this package, the ‘show_info_’ parameter should be set to TRUE
    • Information regarding the success or failure of the method execution will be displayed in the installation logs.
• DML statements
  • References to views/packages (even in static components) should be avoided
    • View/package may not have been created at the time of upgrade
    • View/package may have become invalid during the installation
    • Extreme cases are moved to POSTINSTALLATION scripts
• Interactions between different components
  • Dependency type between the interacting components should be considered
    • Dynamic dependency
      • Use of a post script is required
        • Components with dynamic dependency gets deployed in parallel, therefore at the deployment time, the existence of database objects that are being referred to, cannot be guaranteed
    • Static Dependency – It is possible to directly refer to a Table of a Static Component by a dependant component.
  • Never manipulate data in another component. You may however read data from another component.
    • If it is required to read data from component A and conduct data manipulation in component B, then the code should be placed in component B
• Checking for the existence of another component, or database objects like packages, views, tables...etc
  • Use Database_SYS functions
  • Dictionary_SYS functions should be avoided since dictionary cache may not be up-to-date during an installation
• Data types used in Oracle tables
  • VARCHAR2
    • Maximum length is limited to 4000 bytes
      • Note:- In a database with a Multi Byte Character Set, it is not possible to store more than 4000 bytes in a VARCHAR2(4000) column even though the NLS_LENGTH_SEMANTICS parameter is set to CHAR.

                 For example, assuming that a character takes 4 bytes, only 1000 such characters can be stored in VARCHAR2(4000) database column.

  • NUMBER
    • Precision should not be specified
      • NUMBER - Correct
      • Eg. NUMBER(i,j) – Should be avoided
  • DATE
  • CLOB/BLOB
    • When creating a table, these data types cannot be used as column types
      • First, create the table without this column and add the column(s) with these types later
      • More Info: Large Objects (LOB) in IFS Applications 7
• Re-Executability
  • UPG files generally may not get deployed more than once, However,
    • It is mandatory that the statements in an UPG file should be re-deployable without generating errors or corrupting existing data.
• Output Statements
  • It is possible to include output statements such as dbms_output.put_line in PL/SQL blocks for giving relevant technical information.
  • It is recommended to avoid these statements inside loops since this may result in Buffer overflow error during an installation.
  • It is not necessary to include additional information messages for DDL statements executed via Database_SYS since the public interfaces in this package take care of this.
    • Passing TRUE to show_info_ parameter enables these messages
• Simple Syntax Errors
  • When SQL statements or PL/SQL blocks are written, care should be taken to avoid
    • Executing more than once
      • Pure SQL statement ending with a semicolon followed by slash (‘/’)
      • SQL statement executing more than once
    • Not executing at all
      • Pure SQL statement ending with a semi colon followed by a line comment (within the same line)
      • SQL statement not executing at all

• Truncating a table

  • All rows in the table will get permanently deleted therefore truncating a table is generally not recommended.

    • Note:- It is not possible to ROLLBACK truncated data

  • Support is not available via Database_SYS

  • It may however be used if needed, with full awareness of the consequences.

Support Specific Instructions

• In support, UPG files should only be changed in order to fix a problem such as Compilation errors, Performance issues, Data corruptions...etc. Ordinary DDL or DML statements should not be added to the UPG file by support teams. (Instead a CDB file should be provided).

Creating a Table

• Naming convention

  • Ordinary Tables - End with _TAB

    • Ex. EXAMPLE_TAB

  • Report tables – Ends with _RPT

    • Ex. EXAMPLE_RPT

  • Temporary Tables – Ends with _TMP

    • Ex. EXAMPLE_TMP

• Global Temporary Tables

  • Session specific Global Temporary Tables are not allowed

  • For creating Transaction specific Temporary tables, use method Database_SYS.Create_Temporary_Table.

• Storage parameters should not be specified

• Cannot create a table directly with a CLOB/BLOB column. Create the table without the CLOB/BLOB column and add it later.

  • Eg Database_SYS.AddLobColumn()

Examples

• Creating a Table

• Creating a Global Temporary Table

• Adding a CLOB Column to a Table

Creation/Recreation of Constraints

• Either primary key or unique constraints falls into this category

• Foreign key constraints should not be created.

  • This is handled by IFS framework through references in view comments

• Unique constraints should only be created to preserve uniqueness of one or more columns. If it is only required for performance reasons then, a unique index should be explicitly created.

• Naming Convention

  • For primary key constraints

    • Table name without the _TAB part followed by _PK

      • If table name is ‘EXAMPLE_TAB’ the primary key should be named as ‘EXAMPLE_PK’

    • For unique constraints

      • Table name without the _TAB part followed by _UCn where n can be a numeric value (1,2….)

        • If table name is ‘EXAMPLE_TAB’ the first unique constraint for the table should be named as ‘EXAMPLE_UC1’

• Use method Database_SYS.Create_Constraint

• It is not necessary to drop the constraint if it is required to be re-created. Instead pass TRUE to the ‘replace_’ parameter.

Example

• Creation/Recreation of Constraints

Creation/Recreation of Indexes

• Unique Indexes

  • Naming Convention

    • Table name without the _TAB part followed by _UXn where n can be a numeric value (1,2….)

    • If table name is ‘EXAMPLE_TAB’ the first Unique Index for the table should be named as ‘EXAMPLE_UX1’

• Standard Indexes

  • Naming Convention

    • Table name without the _TAB part followed by _IXn where n can be a numeric value (1,2….)

    • If table name is ‘EXAMPLE_TAB’ the first Index for the table should be named as ‘EXAMPLE_IX1’

  • Use method Database_SYS. Create_Index

    • It is not necessary to drop the Index if it needs to be re-created. Instead pass TRUE to the ‘replace_’ parameter.

Examples

• Creation/Recreation of Indexes

Adding /Modifying Database Columns

• Adding a NOT NULL column to a table with existing data

  • Steps

    • Nullable column should be added first

    • Update the column with appropriate values using a DML statement

    • Make the column NOT NULL

  • Adding the column with a default value is not recommended

    • Business logic should ensure a default value for this type of column

  • Modifying data type

    • If the column contains data, Oracle does not allow the data type to be modified. 

      • Steps (assuming column COL1 needs the data type change)

        • If COL1 is a not-null column, then make it a nullable column

        • Add a new Temporary column COL1_TMP with the correct data type

        • Update COL1_TMP from values contained in COL1

        • Update values of the COL1 to NULL

        • Modify Data type of the COL1

        • Update COL1 from values contained in COL1_TMP

        • If COL1 needs to be made NOT NULL Do it

        • Remove COL1_TMP in the clear script

• Increasing/Decreasing length of a column

  • It is possible to increase length of a VARCHAR2 column.

  • Decreasing the length of a VARCHAR2 column may not be allowed if there is a associated data loss and an error will be generated. Therefore it is not advisable to do so

    • Success is dependent on the available data

• Removing number column precision

  • It is possible to do this using normal column modification process without any problem.

• Renaming a column

  • Use Database_SYS.Rename_Column

Examples

• Adding a Columns To a Table

• Adding a NOT NULL Column To a Table

• Modifying a Column In a Table

• Removing a Default Value from a Table Column

Temporary Indexes

      It is possible to create temporary indexes in UPG files to support efficient execution of DML statements. Once the DML statements gets executed, these indexes can be dropped.

CDB Files

 General Section

• Every .CDB file should have this section included as follows:

  • File name

  • Module

  • Purpose

  • Design History

• SET SERVEROUTPUT ON statement

  • This statement allows the information messages that are generated by Database_SYS public interfaces to be visible in the installation log.

  • This statement should follow the design history.

  • It is Not necessary to include this in every PL/SQL block and it is sufficient to have it once

  • SIZE should not be specified

    • Default SIZE is unlimited and therefore not necessary to manually override the setting

  • SET SERVEROUTPUT OFF statement should be placed at the end of the file

  • It is not necessary to place a semicolon after this statement.

General Instructions

• Naming Standard

  • <<Date>>_<<BugId>>_<<Component-short-name>>.cdb

  • Date should be in the form YYMMDD

  • First Letter in the Component-short-name should be in uppercase and the rest in lowercase

    • Ex. ‘Genled’

  • Ex. 071109_67142_Genled.cdb

• CDB files needs to be written in such a way that it can repeatedly be executed

  • Should not generate errors

  • Should not corrupt existing data

• Time Stamps
• Each PL/SQL block should be preceded by a Time stamp
  • IMore Info :TMF Documents and Guidelines

• SQL/PLSQL Keywords should be in upper case

• PL/SQL Blocks

  • Should be preceded by a Timestamp and a Prompt explaining what is done by the block

    •  Timestamps and prompts are visible in the installation log file and will be very useful to locate performance bottlenecks and installation errors

  • At the end of the last PL/SQL block, Timestamp ‘Done’ should be placed.

• DB Patch Registration check and Patch Registration

  • Patch registration check is introduced to prevent re-execution of a CDB file when a particular file has already been deployed once in a database

    • Mainly to avoid time consuming statements from being deployed unnecessarily.

  • However without relying on this check, the CDB file should be re-deployable

  • Code in Each PL/SQL block should be enclosed in an IF block with patch registration check

    • Database_SYS.Is_Db_Patch_Registered(<<MODULE>>, <<BugId>>)

      • Returns TRUE if patch has been registered before

    • Enclosed code should be executed only if the patch has not been registered before.

  • In the last PL/SQL block, the patch registration statement should be placed just before the END IF corresponding to the Patch Registration Check

    • Database_SYS.Register_Db_Patch(<<Module>>, <<BugId>>, <<Brief Description>>)

    • This statement should be followed by a COMMIT statement even if the PL/SQL block has only DDL statements.

      • This is merely to commit the database changes done by the patch registration statement and has nothing to do with the DDL statements.

      • COMMIT should be inside the IF block

• Use of COMMIT statement
  • All DML statements should be followed by a COMMIT statement.
    • Note:- If there are several DML statements that should be treated as a single transaction, then the COMMIT statement may be included after the last of such statements.
  • If any PL/SQL Block consists purely of DDL statements, then COMMIT statement is not required.
    • DDL statements are implicitly committed and therefore COMMIT statement is unnecessary.
    • Note:- Any un-committed DML transactions will also get committed when a DDL statement gets executed
  • However in the last PL/SQL Block, COMMIT is necessary to merely Commit the patch registration statement as described previously
• Large Transactions
  • Not necessary to include partial commits – because
    • With oracle 10g and upwards, it is possible to set up rollback segments to automatically extend when required.
    • Therefore it is not necessary to free up roll-back-segments by introducing partial commits
    • Consider about the performance and write as efficiently as possible
• Use of ACCEPT and ‘&’ character
  • Use of ACCEPT keyword should be avoided
    • This halts the execution waiting for input
  • Use of ‘&’ character
    • Should only be used for defined values (either locally or in deploy.ini file of the component)
    • Should not be casually used even in comments
      • This may halt the execution waiting for input
    • Most of the time the upgrade scripts get executed unattended. Therefore the execution should not halt awaiting user intervention.
• Interactions between different components
  • Dependency type between the interacting components should be considered
    • Dynamic dependency
      • Use of a post script is required
        • Components with dynamic dependency gets deployed in parallel, therefore at the deployment time, the existence of database objects that are being referred to, cannot be guaranteed
    • Static Dependency – It is possible to directly refer to a Table of a Static Component by a dependant component.
  • Never manipulate data in another component. You may however read data from another component.
    • If it is required to read data from component A and conduct data manipulation in component B, then the code should be placed in component B
• Use of Database_SYS
  • Use public interfaces available in this package for your DDL statements
  • When calling methods in this package, the ‘show_info_’ parameter should be set to TRUE
    • Information regarding the success or failure of the method execution will be displayed in the installation logs.
• DML statements
  • References to views/packages (even in static components) should be avoided
    • View/package may not have been created at the time of installation
    • View/package may have become invalid during the installation
    • Extreme cases are moved to POSTINSTALLATION scripts

• Performance Considerations

  • DML statements should be written efficiently and consideration must be given for performance.

  • Use of Pure SQL statements instead of looping structures are highly recommended.

  • More Info: TMF Documents and Guidelines

• Checking for the existence of another component, or database objects like packages, views, tables...etc
  • Use Database_SYS functions
  • Dictionary_SYS functions should be avoided since dictionary cache may not be up-to-date during an installation
• Output Statements
  • It is possible to include output statements such as dbms_output.put_line in PL/SQL blocks for giving relevant technical information.
  • It is recommended to avoid these statements inside loops since this may result in Buffer overflow error during an installation.
  • It is not necessary to include additional information messages for DDL statements executed via Database_SYS since the public interfaces in this package take care of this.
    • Passing TRUE to show_info_ parameter enables these messages

• Removal of obsolete database objects

  • Can be used to remove obsolete database objects that do not cause data losses

    • Tables and table columns should not be dropped in these files

    • However It is possible to drop temporary /Report tables and remove columns in a report table (RPT)

    • When a NOT NULL Column needs to be made obsolete , the column should be made nullable in the CDB file and all references to the column should be removed from business logic

    • Clear script should be updated in both Support and Projects

• Use of Default values for Table columns
  • It is not recommended to use default values for table columns (specially NOT NULL columns) .
    • Use of 4th parameter in the DATABASE_SYS. Set_Column_Values
  • Business logic should provide default values for columns
• Data types used in Oracle tables
  • VARCHAR2
    • Maximum length is limited to 4000 bytes
      • Note:- In a database with a Multi Byte Character Set, it is not possible to store more than 4000 bytes in a VARCHAR2(4000) column even though the NLS_LENGTH_SEMANTICS parameter is set to CHAR.

                 For example, assuming that a character takes 4 bytes, only 1000 such characters can be stored in VARCHAR2(4000) database column.

  • NUMBER
    • Precision should not be specified
      • NUMBER - Correct
      • Eg. NUMBER(i,j) – Should be avoided
  • DATE
  • CLOB/BLOB
    • When creating a table, these data types cannot be used as column types
      • First, create the table without this column and add the column(s) with these types later
      • More Info: Large Objects (LOB) in IFS Applications 7

• Table creation

  • Use Database_SYS.Create_Or_Replace_Table

    • This method will create the table if the table does not exist in the database and otherwise, an alter table will be performed

      • No need to explicitly check for table existence and use Create_table and Alter_table separately

    • This method should not be used to modify database columns.

      • This will only add any missing columns to an existing table but will not modify column properties.

• If the same CDB file is used in different tracks

  • If CDB files need to be created for the same patch/bug in different tracks of the same component, then the content in all such files may need to be identical.

    • Reason

      • If a customer takes the correction from Track1 (eg. IFSAPP7) and then later tries to upgrade to Track2 (eg. IFSAPP75) the content in Track2 CDB file will not get deployed due to identical patch registration statements.

      • If there is a difference between the content, the differences would not get reflected in Track2

    • If the implementation is not identical in both the tracks, then separate bugs needs to be created.

• Modification of an existing CDB file

  • An existing CDB file created for a bug(which is in patched state) in support should not be modified.

  • The content in the existing CDB file should be made empty and a new CDB file with a new bug ID should be created.

  • Patch registration statement in the new CDB should correspond to the new bug ID.

  • The emptied file should have a design history comment stating that the content have been moved to the new CDB file (new CDB file name should be included)

  • File name of the old CDB file should be included in the [ObsoleteFilesRemove] section of the deploy.ini file for the component.
     

Creating a Table

• Naming convention

  • Ordinary Tables - End with _TAB

    • Ex. EXAMPLE_TAB

  • Report tables – Ends with _RPT

    • Ex. EXAMPLE_RPT

  • Temporary Tables – Ends with _TMP

    • Ex. EXAMPLE_TMP

• Global TemporaryTables

  • Session specific Global Temporary Tables are not allowed

  • Use Database_SYS. Create_Temporary_Table for creation

• Use of Database SYS.

• Storage parameters should not be specified

• Cannot create a table directly with a CLOB/BLOB column. Create the table without the CLOB/BLOB column and add it later.

  • Eg Database_SYS.AddLobColumn()

Examples

• Creating a Table

• Creating a Global Temporary Table

• Adding a CLOB Column to a Table

Creation/Recreation of Constraints

• Either primary key or unique constraints falls into this category

• Foreign key constraints should not be created.

  • This is handled by IFS framework through References given in view comments

• Naming Convention

  • For primary key constraints

    • Table name without the _TAB part followed by _PK

    • If the table name is ‘EXAMPLE_TAB’ the primary key should be named as ‘EXAMPLE_PK’

  • For unique constraints

    • Table name without the _TAB part followed by _UCn where n can be a numeric value (1,2….)

      • If table name is ‘EXAMPLE_TAB’ the first unique constraint for the table should be named as ‘EXAMPLE_UC1’

• Use Database_SYS. Create_Constraint method

• It is not necessary to drop the constraints if it is required to be re-created. Instead pass TRUE to the ‘replace_’ parameter.

Examples

• Creation/Recreation of Constraints

Creation/Recreation of Indexes

• Unique Indexes

  • Naming Convention

    • Table name without the _TAB part followed by _UXn where n can be a numeric value (1,2….)

    • If table name is ‘EXAMPLE_TAB’ the first Unique Index for the table should be named as ‘EXAMPLE_UX1’

• Standard Indexes

  • Naming Convention

    • Table name without the _TAB part followed by _IXn where n can be a numeric value (1,2….)

  • If the table name is ‘EXAMPLE_TAB’ the first Index for the table should be named as ‘EXAMPLE_IX1’

• It is not necessary to drop the Indexes if it is required to be re-created. Instead pass TRUE to the ‘replace_’ parameter

Examples

• Creation/Recreation of Indexes

Adding /Modifying Database Columns

• Adding a NOT NULL column to a table with existing data

  • Steps

    • A Nullable column should be added first

    • Update the column with appropriate values using a DML statement

    • Make the column NOT NULL

  • Adding the column with a default value is not recommended

    • Business logic should ensure a value for NOT NULL columns 

  • Modifying data type

    • If the column contains data, Oracle does not allow the data type to be modified. 

      • Steps (assuming column COL1 needs the data type change)

        • If COL1 is a not-null column, then make it a nullable column

        • Add a new Temporary column COL1_TMP with the correct data type

        • Update COL1_TMP from values contained in COL1

        • Update values of the COL1 to NULL

        • Modify Data type of the COL1

        • Update COL1 from values contained in COL1_TMP

        • If COL1 needs to be made NOT NULL Do it

        • Remove COL1_TMP in the clear script

• Increasing/Decreasing length of a column

  • It is possible to increase length of a VARCHAR2 column.

  • Decreasing the length of a VARCHAR2 column may not be allowed if there is a associated data loss and an error will be generated. Therefore it is not advisable to do so

    • Success is dependent on the available data

• Removing number column precision

  • It is possible to do this using normal column modification process without any problem.

• Renaming a column

  • Use Database_SYS.Rename_Column

Examples

• Adding Columns To a Table

• Adding a NOT NULL Column To a Table

• Modifying a Column In a Table

• Removing a Default Value from a Table Column

Temporary Indexes

• It is possible to create temporary indexes in CDB files to support efficient execution of DML Statements. Once the DML statements is executed, these indexes can be dropped.

Project Delivery CDB Files

General Information

• This type of CDB is used when merging project deliveries into CORE tracks
• There can be several PL/SQL blocks inside one CDB (assume as mini UPG) and each block is uniquely identified as a sub_section. This is the main difference comparing with the ordinary CDB
• The sub_section number has to be increased in each PL/SQL block

Naming Standard

• <<Date>>_<<Project_Name>>_<<Component-Name>>.cdb
• <<Date>> should be in the form YYMMDD
• The <<Project_Name>> is a three letter abbreviation and recommendation is to use Market name if applicable or short project name or similar
• First Letter in the <<Component-Name>> should be in uppercase and the rest in lowercase

            E.g 121005_MEP_Invent.cdb

Sub Section Registration

• The code in each PL/SQL block should be enclosed inside the "IF" block with sub_section registration check using function Db_Script_Register_Detail_API.Is_Sub_Section_Registered
• This function returns TRUE if sub_section has been registered already
• Enclosed code should be executed only if the sub_section has not been registered before
• At the end of the block, sub_section should be registered using Db_Script_Register_Detail_API.Register_Sub_Section

Exception Handling

• In each block EXCEPTION should he handled and error text is registered using Db_Script_Register_Detail_API.Register_Sub_Section
• The RAISE command should be used in each EXCEPTION section in order to throw Exception out of the block

Error Logging

• At the end of the file, error messages are spooled to the component installation log file using dbms_output.put_line
• In addition to that, raised error messages are also prompted to _ERROR_install.txt located in the database installation log folder

Post Bug Corrections

• When doing a bug correction in a already released/patched CDB
  • If sub_section is registered as "TRUE" (db_script_register_detail_tab.processed_OK) but still correction is needed then,
    • Empty the content of the erroneous sub_section in the existing CDB

      E.g

      • Empty sub_section
    • Create a new cdb for the correction. This correction can be treated as a ordinary bug correction using patch_registration concept
  • If sub_section is registered as "FALSE" (db_script_register_detail_tab.processed_OK) then,
    • Fix error inside the erroneous sub_section and this section will be registered correctly in the next execution
    • NOTE:
      •  It's important to re-consider existing ordinary CDBs that depends on the correction done in this sub_section (because existing CDBs can overwrite the new correction done)

Example

• Sample Template

SQL Files

General Section

• Every SQL file should have this section included as follows:

  • File name

  • Component

  • Pre-requisite

    • Should include information as to when this file should be executed and from which version/service pack...etc it has been upgraded.

    • Not Applicable for Automatic Scripts

  • Purpose

    • Brief description of what the script does in functional point of view and also what consequences may arise by not deploying the script.

  • Design History

• SET SERVEROUTPUT ON/OFF statement
  • SET SERVEROUTPUT ON statement should be included at the beginning of the file after the design history section.
  • SET SERVEROUTPUT OFF Statement should not be placed anywhere in the file
  • The Installer will take care of the OFF statement.

Pre<>.sql Files (Pre Scripts)

• Pre scripts are deployed before a particular patch or an upgrade is done

• Contains DML statements

• It is possible to have DDL statements to create temporary indexes to support efficient execution of the DML statements which can be removed at the end of the file

  • When creating Temporary indexes, Pass NULL as the Table space

• Creation of these scripts are discouraged and should be avoided as much as possible

• Naming Standard

  • PRE_<Component>_<To_ifsapp_version>_<description>.sql

    • <Component> : Module short name. First Letter should be in uppercase and the rest in lowercase

    • <To_ifsapp_version>:- IFS Application version name to be upgraded.

    • <description>:- Short description on what script does. First letter of the each word should be in uppercase and the rest in lowercase

    • Ex:- PRE_Subcon_App8_UpdateCostElements.sql

• These scripts require manual deployment.

  • Should be documented in installation roadmap

• In the file repository structure, these files exists in \<component>\manualdeploy\database\<component>

  • Ex. \genled\manualdeploy\database\genled

• If required, it is possible to spool the output as log files. Extension of the log file should adhere to the following standards.

  • .log : If the content of the log file is just information.

  • .sql : If the content of the log file has executable PL/SQL code.

  
   

  Note: The Technical Manager or the Technical Coordinator for the relevant product area should be contacted before introducing these files.

Post<>.sql files (Post Scripts)

• There are two types of post script files:

  • Manual Script

    • These scripts are not automatically deployed during an installation and therefore difficult to manage.

    • Should be avoided unless absolutely necessary

    • Naming Standard

      • POST_<Component>_<To_ifsapp_version>_<description>.sql

        • <Component> : Module short name. First Letter should be in uppercase and the rest in lowercase

        • <To_ifsapp_version>:- IFS Application version name to be upgraded.

        • <description>:- Short description on what script does. First letter of the each word should be in uppercase and the rest in lowercase

        • Ex:- POST_Proj_App8_UpdateCostElements.sql, POST_Risk_App8_UpdateCostBucket.sql

    • Should be documented in the installation roadmap.

    • In the file repository structure, these files exists in \<component>\manualdeploy\database\<component>

      • Ex. \genled\manualdeploy\database\genled

    • If required, it is possible to spool the output as log files. Extension of the log file should adhere to the following standards.

      • .log : If the content of the log file is just information.

      • .sql : If the content of the log file has executable PL/SQL code.

       

  • Automatic Script

    • Is deployed automatically during an installation

    • Deployment takes place after all the UPG/CRE, CDB, API/APY,RDF,INS files for all components involved in the installation process are deployed and after the re-compilation of invalid objects and refreshing of cache.

      • Therefore it is possible to include references to methods, views...etc without the reference objects being invalid.

      • If references to database objects in other components needs to be made, dependencies between components should be taken into account.

        • Dynamic or Static

    • Naming Standard

      • POST_<Component-short-name_<what>.sql

        • <What> : Short identification of what the script does. It should be in lowercase but with each starting letter of a significant word capitalized.

        • <Component-short-name> : First Letter in upper case and the rest in lowercase.

    • File name should be included in the [PostInstallationData] section of the deploy.ini file for the component

    • In the file repository structure, these files exists in \<component>\ \database\<component>

    • Ex. \genled\database\genled

    • The patch registration statement needs to be included if created in support

      • If the correction includes a .CDB file and a Post SQL file then the patch registration statement should be omitted from the Post Script.

      • If modifications are needed for an existing post script with patch registration, then the patch registration statement needs to be changed to the  corresponding new bug ID.

    • Should be able to  re-execute without corrupting data and without generating errors

    • Multiple post scripts may exist in a given component

    • Statements that requests manual intervention such as ACCEPT and also the use of ‘&’ character should be avoided.

    • The Technical Manager or Technical Coordinator for the relevant product area should be contacted before introduction of these files.

Clear Scripts (<Component-short-name>cl.sql)

• These scripts are used to remove obsolete database objects that cannot be removed using UPG or CDB files because it might cause data loss

  • E.g. Tables and Table columns

  • Views, Temporary Tables, Report Tables, Indexes, Constrains, Packages and Sequences can be dropped using the UPG /CDB files because there isn't any data losses associated.

• Naming Standard

  • <Component-short-name>cl.sql

• Obsolete tables should be renamed in UPG/CDB file before including it in this file

  • Renaming Standard:

    • ‘_TAB’ in the table name should be replaced by current component version

    • Assuming the table EXAMPLE_TAB become obsolete for APPS8 in ACCRUL component (Current component version of ACCRUL is 900)

      • Table should be renamed to EXAMPLE_900 in UPG/CDB

      • Note:- In support, UPG file should not be modified

• When NOT NULL columns need to be made obsolete, the columns should be made nullable in CDB/UPG files and all references to obsolete columns should be removed from the business logic

• Public interfaces provided in Database_SYS should be used.

• This file is modified in both support and project. 

Drop Scripts <component-short-name>dr.sql

• The purpose of these scripts are to drop the component by removing database objects.

• Usually generated automatically by a tool that reads information from a database, hence is not required to be created or edited manually.

• Not updated in support.

• All basic data may not be removed.

CRE Files

  <Component-short-name>.cre Files

General Section

• Each CRE file should have this section included as follows:

  • File name

  • Module

  • Purpose

  • Localization

  • Design History

    • For new files design history should be entered in date order (i.e. new entries first)

    • In some older files this may have been done in the reverse order. If this is the case, then the convention used in the file should be followed.

    • Every change (i.e. Epic, Bug correction...etc) should have a design history entry

    • The Bug ID or Epic (Formerly  IDD) Id should be mentioned as a reference to trace the reason for DDL or DML statements.

Instructions

• Naming Standard

  • <Component-short-name>.cre

    • First Letter in the component_short_name should be in uppercase.

  • E.g. Accrul.cre

• SET SERVEROUTPUT ON statement
  • SET SERVEROUTPUT ON statement should be included at the beginning of the file after the design history section.

  Note: SET SERVEROUTPUT OFF Statement should not be placed anywhere in the file

            The Installer will take care of the OFF statement.

• These files are executed during a  fresh installation to create database objects such as Tables, constraints, indexes...etc per component

  • If several report tables are required to be created per component this is done using the report.cre file of the component together with constraints and indexes

• These files are not modified in support. Use a cdb file to introduce database objects instead.

• Only DDL statements should be included in these files

  • There is no need to execute DML statements during a fresh installation

• Use of Database_SYS
  • Use public interfaces available in this package for your DDL statements
  • When calling methods in this package, the ‘show_info_’ parameter should be set to TRUE
    • Information regarding the success or failure of the method execution will be displayed in the installation logs.
• Use of ACCEPT and ‘&’ character
  • Use of ACCEPT keyword should be avoided
    • This halts the execution waiting for input
  • Use of ‘&’ character
    • Should only be used for defined values (either locally or in deploy.ini file of the component)
    • Should not be casually used even in comments
      • This may halt the execution waiting for input
    • Most of the time the upgrade scripts get executed unattended. Therefore the execution should not halt awaiting user intervention.
• SQL/PLSQL Keywords should be in upper case
• Use of Default values for Table columns
  • It is not recommended to use default values for table columns (specially NOT NULL columns) .
    • Use of 4th parameter in the DATABASE_SYS. Set_Column_Values
  • Business logic should provide default values for columns
• Data types used in Oracle tables
  • VARCHAR2
    • Maximum length is limited to 4000 bytes
      • Note:- In a database with a Multi Byte Character Set, it is not possible to store more than 4000 bytes in a VARCHAR2(4000) column even though the NLS_LENGTH_SEMANTICS parameter is set to CHAR.

                 For example, assuming that a character takes 4 bytes, only 1000 such characters can be stored in VARCHAR2(4000) database column.

  • NUMBER
    • Precision should not be specified
      • NUMBER - Correct
      • Eg. NUMBER(i,j) – Should be avoided
  • DATE
  • CLOB/BLOB
    • When creating a table, these data types cannot be used as column types
      • First, create the table without this column and add the column(s) with these types later
      • More Info: Large Objects (LOB) in IFS Applications 7

• If more than one .CRE file exists for a component (i.e. <Component-short-name>.cre, report.cre,sequence.cre ) then the deployment order needs to be specified in Deploy.ini file under [CapMergeFiles] section.

• Sequence Creation

  • If the sequence.cre file does not exist for a component, sequences may also be created in <component-shortname>.cre files. If a number of sequences needs to be created, it is possible to introduce a new sequence.cre file for the component.

  • Public interfaces in Database_SYS should be used to create database objects.

• In support, these files should not be modified unless it is used to fixing an error in the file. To create/modify database objects for a bug correction, CDB file should be used.

Creating a Table

• Naming convention

  • Ordinary Tables - End with _TAB

    • Ex. EXAMPLE_TAB

  • Report tables – Ends with _RPT

    • Ex. EXAMPLE_RPT

  • Temporary Tables – Ends with _TMP

    • Ex. EXAMPLE_TMP

• Global Temporary Tables

  • Session specific Global Temporary Tables are not allowed

  • For creating Transaction specific Temporary tables, use method Database_SYS.Create_Temporary_Table.

• Storage parameters should not be specified

• Cannot create a table directly with a CLOB/BLOB column. Create the table without the CLOB/BLOB column and add it later.

  • Eg Database_SYS.AddLobColumn()

Examples

• Creating a Table

• Creating a Global Temporary Table

• Adding a CLOB Column to a Table

Creation of Constraints

• Either primary key or unique constraints falls into this category

• Foreign key constraints should not be created.

  • This is handled by IFS framework through References given in view comments

• Naming Convention

  • For primary key constraints

    • Table name without the _TAB part followed by _PK

    • If the table name is ‘EXAMPLE_TAB’ the primary key should be named as ‘EXAMPLE_PK’

  • For unique constraints

    • Table name without the _TAB part followed by _UCn where n can be a numeric value (1,2….)

      • If table name is ‘EXAMPLE_TAB’ the first unique constraint for the table should be named as ‘EXAMPLE_UC1’

• Use Database_SYS. Create_Constraint method

• It is not necessary to drop the constraints if it is required to be re-created. Instead pass TRUE to the ‘replace_’ parameter.

Examples

• Creation of Constraints

Creation of Indexes

• Unique Indexes

  • Naming Convention

    • Table name without the _TAB part followed by _UXn where n can be a numeric value (1,2….)

    • If table name is ‘EXAMPLE_TAB’ the first Unique Index for the table should be named as ‘EXAMPLE_UX1’

• Standard Indexes

  • Naming Convention

    • Table name without the _TAB part followed by _IXn where n can be a numeric value (1,2….)

  • If the table name is ‘EXAMPLE_TAB’ the first Index for the table should be named as ‘EXAMPLE_IX1’

• It is not necessary to drop the Indexes if it is required to be re-created. Instead pass TRUE to the ‘replace_’ parameter

Examples

• Creation of Indexes

Adding/ Modifying table columns

• Column(s) can be directly added to the column list of the table in the cre file

• Column properties can be modified as required directly in the column list of the table creation.

Obsolete tables and table columns

• Obsolete table can be directly removed from the file by removing table creation and constraint/index creation statements

  • In a fresh installation, there will not be any data loss

• Obsolete columns can be removed from the column list without any problem.

Sequence.cre files

• These files contains DDL statements necessary for creating Sequences per component during a fresh installation

• In Some components this file may not exist and in such situations, the <component-short-name>.cre file may contain sequence creating statements. A separate file should be introduced when multiple sequences needs to be created.

• In support, this file should not be modified unless to fix an error in the file. To create/modify sequences for a bug correction, CDB file should be used

Report.cre files

• These files contains DDL statements for creating Report Tables (RPTs) and related constraints per component

• If there are multiple report tables in a component, a separate report.cre file can be inroduced without creating RPTs in <component-short-name>.cre file.

• Instructions related to <component-short-name>.cre files applies to these files as well.

• In support, this file should not be modified unless to fix an error in the file. To create/modify report tables, constarints for a bug correction, CDB file should be used.

<Component-short-name>Connection.cre files

• From IFS Applications 8 Onwards, this concept is obsolete. Use Conditional Compilation to accomplish the needs of creating these scripts.

INS Files

INS files (Insert Scripts)

• These files are used to insert basic data into logical units

• Direct manipulation of data in tables is not allowed. Public interfaces in a logical unit should be used instead

• These scripts should be re-deployable

• Should not contain DDL statements

Frequently Asked Questions (FAQ's)

1. What is the deployment order of the file types?

        Answer:

FRESH Installation	UPGRADE Installation	Service Pack Upgrade
(eg: Fresh Inst to APP8)	(eg: 2004-1 to APP8)	(eg: APP75 to APP75-SP7)
*.CRE	*.UPG	*.CDB
*.API	*.API	*.API
*.APY	*.APY	*.APY
*.INS, *.RDF and *.SQL	*.INS, *.RDF and *.SQL	*.INS, *.RDF and *.SQL

These files are executed in component-wise.

• In the FRESH installation, all database objects (eg: Tables, Views, Packages, Indexes, Constraints, Sequences, etc) are "newly created" in the Oracle database. One example is deploying APP8 in the new Oracle database where none of the IFS objects have been deployed before. In this scenario, CRE files will be deployed to create Oracle objects such as Tables, Constraints, Indexes and sequences.

• In the UPGRADE installation, IFS Application is upgraded from previous CORE release into the new CORE version. Upgrading IFS-Application from 2004-1 to APP8 is one example for this. In this upgrade, UPG files are deployed instead of CRE files to upgrade existing/new database objects into latest version.

• In the Service Pack upgrade from the previous CORE release, the CDB files are deployed to create database objects. Upgrading APP75-CORE to APP75-SP7 is one example for this.

• When performing Fresh installation with latest service pack, both CRE and CDB files would be deployed respectively (eg: Fresh installation to APP75-SP7).  

• The UPG and CDB files are deployed when doing an upgrade to the latest Service pack from the previous CORE/SP release (eg: upgrade from 2004-1 to APP75-SP7 or from 2004-1-SP1 to APP75-SP7).

2. When doing updates, do we need to update the column ROWVERSION in the UPG or CDB file?

          Answer: NO. Since the purpose of this column is to store the latest TIMESTAMP where columns are updated from client windows. 

3. Why drop statements that are not included in the UPG or CDB file?

          Answer: Never include drop statements for columns or tables in a .UPG or a .CDB file because the customer may require the data in these columns or tables. The drop statements should instead be included in a cleanup script. The tables should be renamed in the CDB or UPG file and dropped in the cleanup script <Module>cl.sql file ( Ordercl.sql). It is important to note that not null columns should be set as nullable in the UPG or CDB file. This will allow the customer to work with the application without running the cleanup script.

          Note: Drop statements for views or packages can straight away be included in the .UPG/CDB file.

  In both support and project track clear script is updated.

4. Why are method calls not used in UPG/CDB file?

Answer: During an installation process the state of the packages and views can be invalid.

5. Is it possible to use Transaction_SYS.Logical_Unit_Is_Installed() in *.UPG/CDB files?

Answer: No. The Transaction_SYS.Logical_Unit_Is_Installed() calls the method Dictionary_SYS.Logical_Unit_Is_Installed. To use Dictionary_SYS.Logical_Unit_Is_Installed method, it requires that the Dictionary Cache is up to date. During the installation and upgrade process there exists a possibility that the dictionary cache is not up to date (TRUNCATE Dict*.TAB in fndbas/400.upg). Hence, there is no guarantee of producing the expected results (these methods are reserved for runtime). Thus, the alternative would be to use methods in Database_SYS.

6. Do we update *.UPG/CRE files in Support tracks? If so, in what situations?

Answer: It is not necessary to update *.UPG/CRE files in Support unless a situation arises where:

• There is an error (e.g. missing Dynamic calls, erroneous data update, compilation error...etc) in that file itself.

• There is a performance problem.

Note: Sometime additional *.CDB files should be provided in addition to *.UPG files  in order to repair corrupted data.

7. What are the circumstances where Pre/Post SQL scripts have to be created instead of *.CDB/UPG files?

Answer: If a script has to be executed before starting the installation, then a Pre-script has to be created. Otherwise, the script has to be executed at the end of the installation after deploying ALL components. Thereafter you can refer to this script as Post-script.

There are two types of Pre/Post SQL scripts:

• Manual Scripts

  • When there is user interaction in the middle of the execution process it is referred to as a manual script. (User has to enter some values during the installation)

  • This script is created to prompt some business information that has to be executed during a later date (List some Customer Orders that has to be closed later through the application)
     

• Auto Scripts
  These scripts are automatically executed by the installer and are mentioned in the deploy.ini file in the respective module section [POST_INSTALLATION_DATA]

  • Calling methods in static components

  • The ONLY option is to call OWN method calls (method belong to same components as in UPG/CDB) or to execute method calls that are deployed later

    • No exceptions. All calls should be allowed in the auto scripts. Dynamic connections must of course be handled dynamic.

  • Rearrange data to suite new functionality (if UPG/CDB/INS is not valid)

8. Why should  you avoid using manual Pre/Post SQL as much as possible?

Answer: The main reason is the complexity of administration/upgrade of the database environments and overhead cost of maintaining these scripts. If it is necessary to use post script files, make the script auto deployable using [PostInstallationData] section in the Deploy.ini. However, in VERY rare situations it may be necessary to introduce manually deployable pre/post scripts (ex: user input in the script).

Note: You MUST contact your Technical Manager or Technical Coordinator before introducing any new pre/post scripts.

9. Are Cleanup (cl.sql) scripts updated in the Support tracks?

Answer: Yes, Cleanup scripts can be updated in the Support tracks. These scripts have to be updated during projects as well.(recommendation is to keep-on updating during the Implementation. i.e: not wait until the end of the project).

10. Are *.INS files updated in the support tracks?

Answer: The common practice is to update .INS files in support tracks. This does not work in all places. The alternative is to create a CDB file with the changed code only.

Important information when updating INS files:

• INS files are used to Insert or Update data.
  The INS files are used to insert basic data as well as update the existing data since .INS files are deployed both in fresh and upgrade installations. Therefore by deploying .INS files during the single patch installation or service pack upgrade there is a possibility to overwrite the existing data that is already modified/customized by the end user.

• Specific task .INS files (E.g.Schedule task .ins)
  Changes should be done to these files immediately. Ex: Order/CleanupofCustomerOrder.ins, CustomerOrderDetailStatistics.ins
• There are LU specific .INS files that support insert or insert/update.
• it is alright to update the INS file provided that the INS file does not update any customize date. Otherwise, the CDB file has to be used in the support track.
• When merging these files to the Core Project, the INS file should be updated and NOT the UPG

11. What is the purpose of adding TimeStamp to a *.UPG or CDB file?

Answer: The Timestamp (Installation_SYS.Log_Detail_Time_Stamp) is added before every PROMPT statement in the *.UPG/CDB file. After executing the UPG/CDB  file by checking the timestamps in the log files, it is possible to find out statements/commands which take a longer period to be executed. Later on, these statements can be optimized further for better performance.

12. What is the purpose of using Database_SYS in *.UPG/CDB file?

Answer: The Database_SYS package is the common interface provided by Foundation1 to create or modify data database objects.

13. What is the difference between [PostInstallationData] and [ PostInstallationObject] in deploy.ini?

Answer:

• [PostInstallationObject]
  Defines files that will be executed in the PostInstallationObject section in Install.tem. This will occur after all components have been deployed to the database. Mainly objects are considered in this section.
   
• [PostInstallationData]
  Defines files that will be executed in the PostInstallationData section in Install.tem. This will occur after all component have been deployed to the database after the PostInstallationObjects have been executed and refreshed. Mainly, data is considered in this section.

14. Is it necessary to add “ifs_assert_safe” annotation in *.UPG/CDB?

Answer: It is not necessary to add this annotation in these files since PL/SQL block cannot be executed from outside (ie: from Client window, Query option or third party tool) as a standalone. But, it’s mandatory to write the dynamic code in safer way.

References:

1. Installation Roadmap
2. Installation Guide
3. Performance Guide
4. Time Stamp Guide