Custom UBE extremely slow to end (after processing) on AS/400 side

I have a custom UBE that, when submitted to run on the AS/400, takes an extremely long time to end after it has finished processing. When I run it interactively from my Fat Client, it runs/finishes perfectly. In the XE sbmttd jobs window, it shows as complete, but it continues running on the AS/400 (trying to close, but not processing records anymore).

It's reading through a custom table (which I've deleted and recreated again). With 500 records or so in the table, it ends normally, but as the # of records in the table increases, it takes longer and longer for the UBE to close out on the 400 side after processing all records. With the normal 8k to 9k # of records, it's taking almost a full hour to end (using WRKACTJOB).

We have tons of custom UBEs, and this is the only one doing this. I've even rewritten the entire program, as well as the custom table it's based on.

Has anyone ever encountered this, or does anyone have any suggestions as to what's going on??? Of course, Oracle/JDE has been of no help!!!

Thanks,
Ray

Ray,

How many indexes? Is it keyed correctly? Is it setup for transaction
processing (need a commit at the end of updates/inserts)?

Share more - and I'm sure someone will have the right answer.

Make sure you update - when you resolve, so we all get to know!

(db)




--

db,
Thanks for getting back to me!

The custom table has only one index, and it's keyed correctly (only one key field). To be honest, I'm not sure what you mean regarding the transaction processing. I've never used a commit in any program before, so this one definitely doesn't have that.

The pgm reads sequentially through the custom table, validates the data, and then either updates or inserts records in the F060116 (Employee Master), and the Address Book tables, as well as the current record in the custom table itself (to mark it as processed). I've even gone so far as to disable the ERs that do the inserts and updates, and it still takes forever to close on the 400 side. The JDE user profile I'm using has QSECOFR authority level, so object permission shouldn't be an issue. I've also deleted the sql packages associated with the application, and that didn't help.

rkm

Ray,

I too have seen this problem, not only with custom UBE's but out of the box UBE's as well. I don't know what tools release you are on or your version, but we are running E810, 8.96 tools.

I have contacted Oracle and followed up many leads on this subject. The error I have tracked down and verified by Oracle has to do with the SQL CLI interface changes. When a UBE is submitted to the server and it uses a CLI call the UBE submits a "server" job that actually executes the the SQL. On the AS400 when this server job completes its SQL execution it attempts to execute an SQLFreeEnv, SQLFreeHandle instruction that fails (it fails, but not at a severity level to terminate the job).

This is very misleading because everything looks O.K. (the UBE does everything it should), however the server job never completes. By the way you can use WRKACTJOB and see the number of these jobs grow and grow.

According to Oracle the tools "version" of the 8.96 tools we are running has this problem, and we are doing an upgrade (still 8.96, but a later one) that is supposed to correct this problem.