Several Oracle database tables are exhibiting incorrect length semantics following the AL32UTF8 migration.

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Several Oracle database tables are exhibiting incorrect length semantics following the AL32UTF8 migration; columns remain defined by BYTE instead of CHAR.

Environment

Automic Automation Engine 24.X

Database: Oracle

Cause

Configuration.

During the UTF-8 migration the parameter NLS_LENGTH_SEMANTICS=CHAR must be added to all sqlDriverConnect= connection strings.

For Oracle only: Code page settings must correspond to those of the database. NLS_LENGTH_SEMANTICS must be set to CHAR to avoid inconsistencies. Either use the environment variable NLS_LANG or extend the connection string with Session Parameters like this: "SP=NLS_LANGUAGE=language,NLS_TERRITORY=area,CODESET=character set,RECONNECT=interval"

Resolution

Ensure the parameter is added to the configuration files listed below:

ucsrv.ini
ucybdbun.ini
ucybdbre.ini
ucybdbar.ini
ucybdbld.ini
ucybdbrt.ini
ucybdbrr.ini

Additional Information

If the database was created using incorrect semantics, the setting can be modified at the session or system level.

The following SQL statement displays all Automic tables:

 

SELECT
     table_name,
     column_name,
     data_length AS byte_length,
     char_length,
     char_used
  FROM
     ALL_TAB_COLUMNS
  WHERE
   owner     = 'Automic_DB_Owner' -- Switch the user to the Automic schema owner
  ORDER BY
     table_name, column_id;

This displays all tables and columns and their respective semantics.

The example below shows the output for table AH, where four columns were set to BYTE instead of CHAR:

AH	AH_FILENAMEDST	1024	1024	B
AH	AH_FILENAMESRC	1024	1024	B
AH	AH_ARCHIVE1	512	512	B
AH	AH_ARCHIVE2	512	512	B

All tables for consistency can be compared here.

If there are any inconsistencies, please contact the Support team.