How to find row that causes error

[gmorey]

Hi,

I've got a job that does a SQL Server insert (ODBC) and am getting the following error:

SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client]Non-numeric data was found where numeric required

The table has about 40 fields in it. The total number of rows is around 150,000.

I'm looking for a simple way to find the bad data. The data is coming from a text file that was extracted from Unidata.

How can I find out which row is causing the problem? I should be able to find the field if given the row number.

Thanks.

[ketfos]

Hi,

One simple way is to put a REJECT constraint in the transformer and write the output of REJECT constraint link to a file.

All the rejected rows will be written to a sequentila file.

Ketfos

[gmorey]

Ketfos,

Thanks.. you can see I'm a newbie (still), I just haven't had much time to devote to DataStage.

So I've tried that now, but I can't get the rejects to reject.

Please check out my screenshots here:

http://www.gregmorey.com/jti.html

[DeepakCorning]

I underdstand that its pretty difficult to find out which row has the error , but what I usually do in case of this error is pull out only the numeric fields in a sequential file and just scroll down to check if anything Char is present in it or not.

Pretty Difficult in your case as you have more than 100000 rows.

[meena]

Hi,
Check with the datatypes and the data you are assigning. if you want to catch the rejected records you can use Link varibales/ouput/rejected or DBMS.CODE or SQLCODE.

Thanks.. you can see I'm a newbie (still), I just haven't had much time to devote to DataStage.

So I've tried that now, but I can't get the rejects to reject.

Please check out my screenshots here:

http://www.gregmorey.com/jti.html

[gmorey]

Hi,
Check with the datatypes and the data you are assigning. if you want to catch the rejected records you can use Link varibales/ouput/rejected or DBMS.CODE or SQLCODE.

Meena,

Thanks, but I don't understand what you mean here -> use Link varibales/ouput/rejected or DBMS.CODE or SQLCODE.

[Perwezakh]

Hi,
Check with the datatypes and the data you are assigning. if you want to catch the rejected records you can use Link varibales/ouput/rejected or DBMS.CODE or SQLCODE.

Meena,

Thanks, but I don't understand what you mean here -> use Link varibales/ouput/rejected or DBMS.CODE or SQLCODE.

You can use reject file in your job. So if the condition pass you will do the normal process and if any of the codition failed in you transformer than the record will com to reject file. Here you can see the trouble record

[gmorey]

You can use reject file in your job. So if the condition pass you will do the normal process and if any of the codition failed in you transformer than the record will com to reject file. Here you can see the trouble record

Perwezakh,

I'm already doing this; it's not working - I probably set it up incorrectly. Please take a look at the screenshot. The link is in the 3rd message in this thread.

Thanks.

[Jay]

i have never worked with SQL Server...

in Oracle i manually insert a row using TOAD taking a row from the flat file ... if you can insert one row you can insert pretty much all of them....

if problem persists, you may divide your file into smaller parts and get the problem rows...or get an idea which rows might be a problem...sometimes maybe a null column ...for that you have the transformer...

hope it helps...

[gmorey]

i have never worked with SQL Server...

in Oracle i manually insert a row using TOAD taking a row from the flat file ... if you can insert one row you can insert pretty much all of them....

if problem persists, you may divide your file into smaller parts and get the problem rows...or get an idea which rows might be a problem...sometimes maybe a null column ...for that you have the transformer...

hope it helps...

Jay,

The source Unidata database has no built in type-checking, so a number field can have letters and such (I believe), and I often have dates that are not valid, as well. So your "one row" technique doesn't apply here. That makes it tough, doesn't it? That's the system I inherited.

I tried isolating the problem rows, but the ones I suspected looked fine, so I'm stumped.

I think what's key here is to get the reject stage working. Here's the screenshot again:

http://www.gregmorey.com/jti.html

Thanks.

[meena]

Hi,
Your job is right(for capturing rejects). But I need to check with your output columns. Can you give a screen shot of JobOSP_OUT.

And in previous post I meant to use Link variable(you can find it by right clicking in the expression editor in tranformer stage). There are options in Link variable where you can catch rejects based on SQLCode error,DBMS.CODE error).

For example : go to the constraints option, next go to JobOSP_ErrOut expression editor right click and select Link variables next select Outputs next select JobOSP_OUT.(Here you will find the options (dbms code,sqlcode, rejected,rejected code)) based on you error try use them. And unselect the Reject Row option if you are going use the link variable.

Hope I am clear this time.

[kris007]

From your job it appears to me that you won't be able to get hold of any bad records because you didn't declare or define any constraints in the Transformer. The way usually a reject link works best is you define constraint(s)(rules by which you want to capture output) and then you collect rejects based on that. The row in question isn't getting rejected because it qualifies as a legible output(because you didn't define any constraints) but having problems at the database level. Since, the error is happening in a numeric field, I would recommend that you create a test table in your target schema just for debugging purposes with all the numeric fields in it. Avoid the varchar fields as I don't think they are the area of concern. Now, run the job and try to insert the records into SQL server with a constraint something like this

Code: Select all

@INROWNUM<TotalNumofRows/2

That way you will know in which half your records are present and go about debugging by changing the Inrow numbers. It might be tedious but will get you what you need at the end of the day.

HTH

[gmorey]

I've solved the problem, but I'd still like to get the Reject link working at some point.

The last column is inserted as an int, but one of the records has a zero-length string, and after changing the destination column to a varchar, it's going in.

So I need to go to the source data, fix the zero-length string, change the last column back to an int, and run it again.

As far as the Reject link goes, I did try a few things out of page 7-18 in the DataStage Server Job Developer's Guide Version 5.1, like [linkname].REJECTED, [linkname].REJECTEDCODE, and leaving the constraint blank as a catch-all (p. 7-19), but these didn't work.

Thank you all for your suggestions.

[ray.wurlod]

That's surprising, as the link variables work well with ODBC stage types.

Let's say your output link to SQL server from the Transformer is called SendData and your output link to your rejects file from the Transformer is called Rejects.

You have marked Rejects as handling rejects by checking the box in the constraints grid. This link has no constraint expression.

You ought to be able to create columns on the Rejects link to capture SQLState, DBMS code and error text by using the output link variables SendData.SQLSTATE, SendData.DBMSCODE and SendData.LASTERR respectively.

Are warnings from SQL Server logged in the job log? Is your number of rows per transaction set to 1? (This is sometimes necessary for accurate error capture.)