Oracle connector stage not rejecting data truncations errors

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Lavanya B
Participant
Posts: 20
Joined: Mon Oct 30, 2006 12:32 am

Oracle connector stage not rejecting data truncations errors

Post by Lavanya B »

Hi,

I am loading data to oracle database using Oracle connector stage as my target.
The source column is varchar 20 and target oracle column is varchar2(10).
I have put a reject link from the target oracle connector stage and have checked the data truncation errors check box in the reject link. But the source records that have data greater than 10 characters are also getting loaded into the target table when actually they need to be rejected. I am using Insert mode to load the records.
Below is the job design
File->Transformer--------->OracleConnector-->RejectLinktoFile

Is any configuration missing in the oracle connector stage or is any environment variable needs to be set?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Not that I am aware of, that should be all it takes as far as I know. I'd check with support, may be a known issue in your version with a patch available.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Can you try an INSERT statement with more than ten characters into this field using some other client, such as TOAD or Data Studio? If Oracle will accept (and, presumably, truncate) over-length strings, then DataStage cannot detect in the Oracle Connector that this problem has occurred.

You may be able to raise the level of error reporting in Oracle.

Otherwise, you could reject these rows in your Transformer stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Lavanya B
Participant
Posts: 20
Joined: Mon Oct 30, 2006 12:32 am

Post by Lavanya B »

I have tried inserting into Oracle table using TOAD, but I get an error saying that the number of characters exceeding the length.
But I am surprised why am I not getting this error while running the job in DataStage.
vmcburney
Participant
Posts: 3593
Joined: Thu Jan 23, 2003 5:25 pm
Location: Australia, Melbourne
Contact:

Post by vmcburney »

I assume DataStage is truncating the data before it sends it to Oracle and thus avoiding the error. Do you see any truncate warning messages in the DataStage job log?
Lavanya B
Participant
Posts: 20
Joined: Mon Oct 30, 2006 12:32 am

Post by Lavanya B »

When I increase the length of the field ()i.e varchar 15) in the Input tab of Oracle connector, then the record gets rejected from Oracle connector stage.

Yes even I think DataStage is truncating the data before sending it to Oracle but I dont see any warning messages in the log.

Is there any patch/environment variable setting to supress this truncation?
Lavanya B
Participant
Posts: 20
Joined: Mon Oct 30, 2006 12:32 am

Post by Lavanya B »

Hi,

Any patch/environment variable setting is available to suppress the truncation of data?
Please provide suggestions.
Post Reply