DSXchange

There is one input file that is being processed by one job. Few records do not meet to some known condition. those can be captured in some error file. but if few records are getting rejected due to some unknown sytem related problem. how can i capture them with suitable cause.

-Nripendra

Please give an exact example of what you mean by system error. If a row is unable to be read from a sequential text file because of invalid column definitions, then there's no way to trap that to a file. If you haven't accurately described in the sequential stage what a row looks like, how can you write an errant row to an error file when your definition doesn't seem to work as to what a row is?

If you put a constraint in a transformer, yes, you can discriminate rows on some criteria. DB rejected rows can go to reject files, but that's a function of the database.

Not sure what you mean by 'unknown system related problem', could you provide an example? What is the target of your job?

In general, any kind of error should be capturable via a Reject link... unless there is some sort of job ending, catastrophic error of course.

Input rows are never rejected. Only output rows can be rejected. You can adapt your design to capture these by having a Transformer stage driving the output. Create an extra output link from this Transformer stage, and check the "Reject" box in the constraints grid for the new link.

Ensure that this link is processed last.

Any rows that are rejected by the main output will be captured on this "rejects" output link. You can also capture diagnostic information by creating columns to receive the values for the SQLSTATE, DBMSCODE and LASTERR link variables of the other output link.

All of these techniques may be found by searching the forum.

suppose some records are to be loaded in Oracle tables. Due to some constraints few records are not getting loaded. how can i capture those records?

_Nripendra

As already noted - via a Reject link. Re-read Ray's post, it's all in there.

so is there no way to capture only DBMS related rejected records? i want to capture only DBMS stage rejected records. But if "Reject Link" is used, it will capture all rejected records.

-Nripendra

True, a 'pure' Reject Link will capture all rejects. However, that doesn't mean you can't capture just the database errors in a previous link.

Create yet-another-link that comes after your target links but before the Reject Link (if you have one). Add a contraint that checks the DBMS return code of the target link, something like:
Remember, link order is very important here. Capture some of the other Link Variables mentioned above and store them in your output, if desired - LASTERR and SQLSTATE, for example.

You'd have to experiment a little and see what codes you would need to check for your target database, but that should get you started. Any rows rejected by the database will get sent down this new link, and any remaining rejects will go out the 'real' Reject Link.

Looks great reply but suppose if i want to differentiate between different DBMS errors like
1) foreign key constraint failing
2)table not present
3)invalid table name

I mean suppose if i want to differentiate between DBMS errors.are there any provisions for doing it.

I guess if i have a consolidated list of all the DBMS error codes i can put them as different constraints on my links.

it would be really helpful if you can tell me where i can find the documentation for all the DBMS error codes.

The rejects output link from the Transformer stage is like any other; it can feed another Transformer stage, a text file, a database stage, and so on. Choose from any of these methods for segregating classes of error code.

You can get a list of DBMS error codes from your DBMS documentation or on-line help. This is not a DataStage capability, nor should it be.

if SQL related error need to be captured then code should be :

Linkname.SQLSTATE<>0

is it right?
what is difference between DBMSCODE and SQLSTATE erros?

-Nripendra

SQLSTATE is specific to ODBC. Values of SQLSTATE are defined in the ODBC standard, and returned only if ODBC protocols are in use (note that some "native" protocols actually sit on top of or mimic ODBC protocols). SQLSTATE is typically generated by the ODBC driver or the ODBC driver manager.

DBMSCODE is the error code generated by the database server. If a driver intercepted the problem and never contacted the database server, the value of DBMSCODE will be 0.

You do not need any constraint at all on the rejects link but, if you do, the most reliable test is that is, the value of the link's REJECTED link variable being "true".