Converting Oracle Database to support multibyte languages

If you support multibyte languages, back up your database before you begin the upgrade. If the database conversion fails for any reason, you can restore from the backup, resolve the issue, and try again. Consult with your Oracle Database administrator and review the changes that must be made and review the logs to ensure that the database converts properly.

Before you begin

Read the IBM TRIRIGA Release Notes.

1. Verify that no IBM® TRIRIGA® application instances are connected to the database.
2. Convert the VARCHAR2 fields from BYTE to CHAR with the following script:
   1. Before you use the script, review it carefully to verify that it meets your specific situation.

      -- This alters the recurrence id and status columns in the scheduled
      -- events table to allow an index to continue to be used
      -- makes t table consistent to the metadata, do not change other t 
      -- tables this way; use the data modeler!!!
      alter table t_scheduledevents 
      modify (RECURRENCEID VARCHAR2(700 CHAR), 
        EVENTSTATUS  VARCHAR2(700 CHAR)) ;
      
      -- This alters the section_name and field_name columns, reducing the
      -- size to allow for an index to continue to be used
      alter table rep_template_columns 
      modify (section_name VARCHAR2(100 CHAR), 
        field_name VARCHAR2(100 CHAR));
      
      -- This loops through every varchar2 column in the user's tables and 
      -- changes the length semantics from byte to char
      DECLARE
       TYPE RefCurTyp IS REF CURSOR;
       alter_tbl VARCHAR2(200);
       tbl VARCHAR2(200);
       clmn VARCHAR2(200);
       dtyp VARCHAR2(200) ;
       dlth VARCHAR2(200);
       c RefCurTyp;
      BEGIN
       open c for 'select utc.table_name, utc.column_name, utc.data_type, 
       utc.data_length FROM user_tab_columns utc, user_tables ut WHERE 
       utc.data_type = ''VARCHAR2'' AND utc.char_used = ''B'' AND 
       ut.table_name = utc.table_name' ;
       loop
        FETCH c INTO tbl, clmn, dtyp, dlth;
        EXIT WHEN c%NOTFOUND;  
        EXECUTE IMMEDIATE 
         'alter table '||tbl||' modify ('||clmn||' '||dtyp||'('||dlth||' CHAR))' ;
       END LOOP;
       CLOSE c ;
      END ;

   2. If you added custom indexes, it is possible that the script might fail. If the script fails, you must restore from your backup database. Before you restart the script, remove the failing index. Afterward, add and adjust the index. Consider changing the T_table field sizes with the Data Modeler.
3. Export the converted database.
4. Create a database with the UTF-8 or UTF-16 character set. Set the NLS_LENGTH_SEMANTICS parameter to CHAR.
5. Import the converted database. Review the import log to verify that no data truncation or other issues occurred. This review is important in verifying the correct upgrade of the database.
6. Upgrade the IBM TRIRIGA Application Platform.