Records getting rejected

[Scorpio007]

I am using ODBC_Enterprise stage to pull data from database - SQL Server to the target stage Oracle_Enterprise Database - Oracle. I have over 20,000 records to pull from source to target. There are 2 records getting rejected with sqlcode:-1438 .

I am suspecting this is a data size/precision issue. I am mapping data from datatype - money size - 8 in SQLServer to Float in Oracle. I have size big enough to accomodate the value in Oracle. When I do a direct insert with the same value in Oracle it works fine but fails using the datastage Any help will be appreciated

[hiral.chauhan]

I googled your sqlcode.

-1438: value larger than specified precision allows for this column.

Does that ring a bell?

[Scorpio007]

I have done that myself. My datatype in Oracle table(Size and precision) match what I have in Datastage and I am able to insert the same value directly in Oracle with an insert statement.

[hiral.chauhan]

I would examine the reject records first and then try to get a clue from the director log.

What does the director log say about the two reject records? any warnings or error messages?

[Scorpio007]

The director log says

-1438: value larger than specified precision allows for this column

not of much help.

[asorrell]

Actually, that's quite a bit of help. What is means is that a number in one of those records is too big for the decimal mask you are using. For example, you are trying to write out 12345.78 in a Decimal 6,2 field. Check out some of the larger numbers against the masks you are using. If you need help, look for some previous posts I know some of us have written on sizing decimal masks.

Note: Do not assume that DataStage and Oracle use exactly the same syntax when specifying decimal masks...

[Scorpio007]

The 2 records that are getting rejected are the ones that have largest values in the amount column.
I used in my target Oracle database and the target stage Float(25,5)

The value I have in my source database SQL Server(money is 1000000000.0000
All the records(including the 2 reject records) passes though all the intermediate stages but gets rejected at the target Oracle_enterprise stage.

[dxk9]

Try to collect those two rejects in a sequential file and examine the values and length of all the fields in those records.

This will help you in detecting where the problem is actually with.

Regards,
Divya

[hiral.chauhan]

I used in my target Oracle database and the target stage Float(25,5)

Even if you define your column as a Float(25,5) in your target Oracle database stage, your database will reject out the rows. In order for these two rows to land in your database, you will have to change the properties (meaning make your column a Float(25,5)) in your Database.

Hope this helps.

-Hiral

[Scorpio007]

I think I said
"target Oracle database and the target stage Float(25,5)"

sorry about not communicating clearly.

[hiral.chauhan]

hmmmm. Sorry about that..

I would probably analyze those two records on the source side then. I would try to find out what makes them different than the rest. We had that kind of issue once and it turned out that we had some hex characters in our incoming record that made the target field bigger than what it was. May be you want to send the rejected records into a sequential file, download that file as a txt and view it under a hex editor.

Have you tried to see if those records make it to a varchar? What happens if you tried to bring it through just as text?