Need to collect errors in a thread.

[jdecoder]

Here is the deal guys. I have some multiple foreign tables that need to insert / update and I am using TC to get this done. Now if there is an error it should be all or none. I was not able to put a transaction boundary around the individual TCs. Now I can write a fancy C BSFN to load the table conversion environment and do transaction processing but that’s my last option. Also the File IO Status in these TC is not reliable, meaning the Insert to foreign table fails (with foreign key constraint [don’t ask me why they have constraints on an interface file]) but the File IO Status says SUCCESS and I can clearly see in the log that the insert failed.
Now here my plan:
1. I will run the TC (multiple) in proof mode from a wrapper UBE.
2. After each TC completes I will call a business function that will look back into the logs or thread till that point in time and tell me if anything is in error(file IOs).
3. Based on what this magic business function says I will run the final mode.
I need all your help to make this magic error detecting bsfn.

Thanks for all your help in advance:
JDE 8.12
Tools 8.98.47
AS400 i5 Version 7 Release 1

Foreign environment:
SQL Server on Windows server.

 

[BOster]

Now I can write a fancy C BSFN to load the table conversion environment and do transaction processing

To me it sounds like less work then everything else you described. I guess I would be more inclined to make that my first option.

 

[jdecoder]

I agree I would have done that too.. the TC's existed when I came into this and they wanted me use them. I recommended to throw them away and gave the estimate on the CBSFN. They did not want to throw away the TC and redo the mappings. This what they said literally "those TC's are running wild, just tame them".

 

[craig_welton]

Can you spend a little time investigating why no error is thrown on the insert? Is it possible Buffered Inserts is turned on?

If you can't use a transaction, is there a unique batch # for each set of data? Manually do the rollback using that # as a key, perhaps.

Craig

 

[jdecoder]

Is it possible Buffered Inserts is turned on?

This is a a good pointer. I can see it is turned on and let me try and turning it off and see if i can detect error.

Manually do the rollback using that # as a key, perhaps.

And yes there is a Batch number but its not getting inserted into the foreign table (bad design I guess) and hence cannot roll back.

 

[craig_welton]

Is there an unused column in the interface table? shove a batch number in there and blank it out at the end if everything works properly. if not, delete the batch.

 

[jdecoder]

There is no future fields and the third party is not very co - operative to change tables.
But the turning off the Buffered Inserts did the trick... File IO Now shows error for the inserts and I can run a proof mode and save it from creating integrity issue at their end.

Thanks Everyone,

 

[jdecoder]

spoke too soon.... Proof mode does not perform the actual insert statement and hence does not give any error. But at least I am getting error on insert now. I will have to work with that