How do I fix the error "ORA-01843: not a valid month" on the RRC ETL Purge Job?
In IBM Rational Insight 1.1.1.3, when running the RM_ODS4.0.5_FullLoad the Purge Job fails with the following error:
[PROGRESS - 13:55:14] Procedure Node 105 'purge'; reported the following:
DM-DBM-0400 UDA driver reported the following on connection Rational Data Warehouse:
UDA-SQL-0107 A general exception has occurred during the operation "execute immediate".
ORA-01843: not a valid month
[PROGRESS - 13:55:14] Procedure Node 105 'purge'; failed
One answer
This is caused by an incorrect timestamp format setting in the oracle client configuration.
To solve the error you need to specify the NLS_TIMESTAMP_FORMAT variable for the Oracle client systems.
1) In the Windows machine where the IBM Cognos Data Manager client is installed, set the following operative system environment variable:
2) On the IBM Rational insight ETL server set the following WAS environment entries of the ETL Server profile:
To solve the error you need to specify the NLS_TIMESTAMP_FORMAT variable for the Oracle client systems.
1) In the Windows machine where the IBM Cognos Data Manager client is installed, set the following operative system environment variable:
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS
2) On the IBM Rational insight ETL server set the following WAS environment entries of the ETL Server profile:
NLS_LANG=American_America.UTF8Note: on Linux without NLS_LANG explicitely set the NLS_TIMESTAMP_FORMAT is not picked up.
NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS