Handling TO_DATE() and TO_CHAR() SQL functions for the Oracle database type

The Dynamic RDBMS stage uses TO_DATE and TO_CHAR Oracle SQL functions to fetch data and write data for InfoSphere® DataStage® Date, Time and Timestamp data types.

For example, when this stage is configured to auto-generate SELECT statement and column C1 on the output link is defined as Date column, column C2 as Time column and column C3 as Timestamp column, they will appear in the select list of the generated SELECT statement respectively as:

TO_CHAR(C1, 'YYYY-MM-DD')
TO_CHAR(C2, 'HH24:MI:SS')
TO_CHAR(C3, 'YYYY-MM-DD HH24:MI:SS')

If the stage is configured to auto-generate an INSERT statement and column C1 on the input link is defined as Date column, column C2 as Time column and column C3 as Timestamp column, they will appear in the parameter list of the generated INSERT statement respectively as:

TO_DATE(:1, 'YYYY-MM-DD')
TO_DATE(:2, 'HH24:MI:SS')
TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS')

The user-defined SQL statements in Dynamic RDBMS stage also use TO_CHAR and TO_DATE SQL functions for Date, Time and Timestamp columns.

The DRS Connector stage does not require these functions for Date, Time and Timestamp columns. It exchanges data with the database using internal Oracle date and timestamp datatypes so no conversion to and from character data is involved.

When a Dynamic plug-in stage with TO_CHAR and TO_DATE functions in user-defined SQL statements is migrated to the DRS Connector stage, the jobs can fail due to this difference. The problem does not occur for migrated stages with auto-generated SQL statement, because the SQL statement that the DRS Connector stage will generate at runtime will not use TO_CHAR and TO_DATE functions like if those functions were used in the auto-generated statement generated at runtime by the Dynamic RDBMS stage in the original job. This is because auto-generated statements are always generated from scratch when the job runs and the mechanism used by the Dynamic RDBMS stage does not have effect on the mechanism used by the DRS Connector stage.

The preferred way for resolving job failures dues to use of TO_CHAR and TO_DATE SQL functions in the user-generated SQL statement of the Dynamic RDBMS stage after migrating it to DRS Connector stage is to remove those functions from the statement.

For example if the user-defined SELECT statement contains expression TO_CHAR(C1, 'YYYY-MM-DD') in the select list, replace it with C1.

As another example, if the user-defined INSERT statement contains expression TO_DATE(:2, 'HH24-MI-SS') in the parameter list, replace it with :2.

Another way for resolving the problem is to modify types of the columns on the link that correspond to these SQL functions. The types should be changed to character type (such as Char or VarChar) with the length that corresponds to the date/time format used in the SQL function.

For example if the user-defined SELECT statement contains expression TO_CHAR(C1, 'YYYY-MM-DD') in the select list, change the data type for column C1 on the link from Date to Char(10) since 10 is the total number of character positions for date values in 'YYYY-MM-DD' format (for example 2010-09-27).

As another example, if the user-defined INSERT statement contains expression TO_DATE(:2, 'HH24-MI-SS') in the parameter list, replace the column on the link that corresponds to first parameter from Time to Char(10) since 10 is the total number of character positions for time values in 'HH24-MI-SS' format (for example 11:26:45).

Finally in cases when modifying the existing statements or column definitions on the links is not convenient, another option is to define CC_ORA_BIND_DATETIME_AS_CHAR environment variable in the InfoSphere DataStage project and set it to value TRUE. Alternatively it can be defined at the project level with the default value of FALSE and added to individual jobs as job parameter and overridden to value TRUE for them.

When CC_ORA_BIND_DATETIME_AS_CHAR environment variable is defined and set to TRUE, the DRS Connector stage uses character representation for Date and Timestamp values exchanged with the Oracle database, using the same date and time formats used by the Dynamic RDBMS stage. That way the connector mimics the behavior of the Dynamic RDBMS stage. The use of this environment variable is not recommended in general case since it forces the behavior in the DRS Connector which is added only for backward compatibility with Dynamic RDBMS stage and which involves data conversions that are normally not necessary and which in turn can have significant negative impact on the performance.