DSXchange

Dear Experts

This is my first post please guide me if I am not following the rules.

I got a parellel job designed where i used the stages like

DataSet ------->Tranformer--------->ODBC ------>DataSet(Reject link)

The job runs fine and all the log entries are green with out any warnings.

The performance statistics shows all the records are inserted with out any rejections.

But when I go to SQL Server DataBase and check the count the count mismatches . Some of the records are not getting inserted in to the table.

But I was unable to find what the issue was through PX.

So what I done was I designed the same job in the Server Edition and run the job, there I am getting the warnings saying 'unable to insert the record due to foreign key constraint'.

I wonder where it was going wrong in PX.

Any help will be much appreciated.

Thanks very much in advance.

Regards

pklcnu

In your PX job, do you get the failed records written to the dataset?

miwinter wrote:In your PX job, do you get the failed records written to the dataset?

Thanks for your quick reply

No records are written to Reject DataSet



Regards

pklcnu

And you are doing a write method of Upsert with the option "Output Reject Records = True" set?

miwinter wrote:And you are doing a write method of Upsert with the option "Output Reject Records = True" set?

Yes

Thanks

If you have no rejects coming down that link (dataset is empty post-run) and the log (having checked every entry in there, green or not) shows no record of any rejected rows, then I think you definitely have a bug with the ODBC stage there. These should be fatals to the job. I'd refer to your service provider / raise an IBM PMR as soon as possible.

Can you work backwards and see why those records aren't being written (try doing a manual SQL insert with the data). Why are the being rejected by the database - i.e. constraint integrity or some other reason. Once that cause is known we might be able to see if this is a bug.

ArndW wrote:Can you work backwards and see why those records aren't being written (try doing a manual SQL insert with the data). Why are the being rejected by the database - i.e. constraint integrity or some othe ...

Hi ArndW

Thanks very much for your feed back.
You are right the records are not getting inserted in to the table because of the foreign key constraint. As I said in my original post neither the warnings are shown nor the records getting rejected in PX job, the log says the records got inserted and the performance statistics says the same aswell. But the same job works fine in Server Edition, there I can see the Warnings saying records cann't be inserted due to foreign key constraint.

Until I designed the same PX job in Server i don't know what was the problem , after seeing the Server job log only i came to know the foreign key constraint issue. But my question was why PX is behaving like that.....not showing the warnings or rejections but inserting the correct number of records. Hope I made the point clear.

Is your Array size and commit set to 1? Strange behaviour indeed - the reject link should be receiving rows. Are you certain that is correctly specified? Can you force a different type of error to see if that works, i.e. put an invalid date into a date field?

I've seen this same behaviour in the OE stage, certain combinations of options will result in issues (like FK constraints) being neither logged nor causing the job to abort. All is green and only a very close examination of the logs shows the issue.

Seems like a 'feature'.

ArndW wrote:Is your Array size and commit set to 1? Strange behaviour indeed - the reject link should be receiving rows. Are you certain that is correctly specified? Can you force a different type of error to see if that works, i.e. put an invalid date into a date field?

Hi ArndW

The Array size was set to default value of 2000, but after seeing your post i did change the Insert Array Size and Row Commit Interval preperties both set to 1 and tried once again. But the same issue the job log don't have any warnings at the same time no rejections to rejection file.

Thanks very Much

Craig's comment there is interesting - only close examination of the log reveals anything.

Back to my earlier post, pklcnu, have you checked every entry in the log to ensure there is no mention of data leakage, whether it's informational or otherwise?

miwinter wrote:Craig's comment there is interesting - only close examination of the log reveals anything.

Back to my earlier post, pklcnu, have you checked every entry in the log to ensure there is no mention of data leakage, whether it's informational or otherwise?

Hi All

I am posting the log. Your input will be appreciated.


Starting Job Test. (...)
Attached Message Handlers: (...)
Environment variable settings: (...)
Parallel job initiated
Parallel job default NLS map ISO-8859-1, default locale OFF
main_program: Ascential DataStage(tm) Enterprise Edition 7.5.2 (...)
main_program: orchgeneral: loaded (...)
main_program: APT configuration file: /apps/ascential/Ascential/DataStage/Configurations/default.apt (...)
oTest_Insert_LA,0: ROWS INSERTED :0
oTest_Insert_LA,0: ROWS REJECTED :0
oTest_Insert_LA,1: ROWS INSERTED :0
oTest_Insert_LA,1: ROWS REJECTED :0
main_program: Step execution finished with status = OK.
main_program: Startup time, 0:02; production run time, 0:03.
Parallel job reports successful completion
Finished Job Test.

Thanks very much

What I would see are green Info entries like this:

Oracle_Enterprise_child,3: Records inserted: 0
Records used for update: 0
Records rejected: 1
Total records: 1.

Oracle_Enterprise_child,0: Records inserted: 7
Records used for update: 0
Records rejected: 2
Total records: 9.

Followed by the ever popular:

main_program: Step execution finished with status = OK.
Parallel job reports successful completion

Do you have any filters set when diplaying your log? Are you seeing any informational messages?