Unusual RunUbe Problem

[JonSellers]

To JDELIST,

We are having an unusual problem with RunUbe.

Denver has been unable to solve this problem, so any suggestions from the JDELIST would be greatly appreciated.

Here are some facts about the problem:

UBE's operate correctly when executed via Batch Versions (BV) or LaunchUBE.exe. We experience the error only when using RUNUBE.

UBE performs all processing (updates) correctly but ends with either one of these error messages:

1) "Function check. MCH3402 unmonitored by QSQLCLS at statement *N, instruction X'008D'."

2) "Application error. *N unmonitored by *N at statement *N, instruction X'4000'"

On the "View Submitted Jobs" screen, UBEs that receive the first error message show to be in status "Error", UBEs that receive the second error message are shown as status "Done". Either way, the UBE is not finishing normally.

Problem appears to only affect custom UBE's. JDE-written UBE appear to work ok (still verifying this).

Error only occurs when updating the table that the UBE is based upon. Example: Custom columnar UBE is based upon the F0101 business view "V0101C". In the Do Section there is code to update a field on the F0101, such as:

VA rpt_UserReservedNumber = "2"
F0101.Update
BC Long Address Number = TK Long Address Number
VA rpt_UserReservedNumber -> TK User Reserved Number


AS400 SQL Package is deleted prior to running UBE

Error occurs only when Updating records. Custom UBEs that only Insert or Delete records have no trouble.

The UBEs are not complex. They are simple columnar reports created using the RDA wizard. They use no "Smart fields", or custom BSFN's. They use standard ER commands (Like UPDATE) to perform the table maintenance functions.

UBE runs ok (no error) when Updating less than 80,000 records
UBE Produces the error message when updating more than 80,000 records

Here are the complete error messages:

First Error Message:

900 - CALL PGM(B7333SYS/JDB_1) /* The CALL command contains parameters */
- RETURN /* RETURN due to end of CL program */
Member F5445 added to file PRINTQUEUE in B7333SYS.
Pointer not set for location referenced.
Pointer not set for location referenced.
Library SVM7333 already exists in library list.
Exception recursion detected.
Application error. *N unmonitored by *N at statement *N, instruction
X'4000'.


Second Error Message:


900 - CALL PGM(B7333SYS/JDB_1) /* The CALL command contains parameters */
- RETURN /* RETURN due to end of CL program */

Cursor CURSOR25 already open.
Tried to refer to all or part of an object that no longer exists.
SQL system error.
Tried to refer to all or part of an object that no longer exists.
Function check. MCH3402 unmonitored by QSQLCLS at statement *N,
instruction X'008D'.
SQL system error.
Cursor CURSOR25 already open.
Member F3078 added to file PRINTQUEUE in B7333SYS.




A Side Note:

We do not have the luxury of simply not using Runube and using BV/Launchube instead. The production job scheduling package we are using starts all UBEs via the "runube" command and therefore we must fix the problem.


Thanks,

Jon Sellers
One World XE B7333, Update 3, SP 16.1 rev 3
As/400 (enterprise)
NT 4.0 (client)
WTS

 

[Zoltan_Gyimesi]

Hi Jon,

Although I amn't experienced with RUNUBE but I have an idea.
You mentioned that the problem occures only when you update the table and this table is the same then the UBE based on.

You will never know how sophisticated way does the system use the existing indicies to evaluate the query of the UBE, further the Long Address Number (which is updated) is one of the indicies of F0101 table. It is very suspicious that this cause the problem, don't you know.

My suggestion is: frame you F0101 Table I/O statements with Open/Close or with Open Handle/Close Handle, maybe it will solve your problem. If not, then an other solution could be not to use BSVW based section but write your selection/sequencing logic with ER and manage the process from the main Driver section (without BSVW). You can receive values for the reqired selection criterias as well from Processing Options as from Report Interconnect.

Hope, one of the two solution will solve your problem. Please, let us know anyway, Thanks.

Good luck,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[JonSellers]

A few more items to clarify the problem:

This problem is not limited to the F0101. It occurs when
updating a table if that table is what the ube is based on.
F0101 is just one example.

The UBEs have no processing options.

The UBE have no report interconnects.

Thanks.

Jon Sellers

 

[Christian Audet]

Jon,

I have the same feeling than Zoltan about your issue. I can't be sure of the problem, but I can be sure that you normaly you get problem if you try to update the table that is from your business view and the best solution is to use HANDLE for yout UPDATE.

I go so many problem with this that MY NEW RULE is "if you read and update the same table, then use HANDLE"

Give us some feedback about it.

Christian Audet

Implementing B7333 (Xe) SP16.1, SQL
(Support B732, B7331 and B7332)

 

[JonSellers]

Zoltán,

Thanks for replying so quickly.

Denver also suggested using open/close and handles which I tried -
It did not seem to make any difference.

I will try your idea about updating the table in a section that is not tied to the business view. Interesting approach. I will let you know the results.

Thanks.

Jon

 

[JonSellers]

Perry,

Thanks for replying.

No it has never worked - It is a brand new ube.

I have not checked security. Good point. I assumed that the security would be the same between runube, BV, & launchube (again, the UBE works fine with BV and launchube). Several cnc guys with 'full' security have run it via runube with the same results (updates everything but ends in an error status).

The problem is not limited to F0101. The other tables I have used do not have table triggers - and the problem still occurs.

All of the records are being processed and the updates are occuring normally - The UBE just ends with a "unsucessful" status.

Not using handles or "advanced" table i/o (open/close, etc.)

regards.

Jon

-----Original Message-----
From: Perry
Sent: Thursday, December 13, 2001 12:29 PM
To: jon
Subject: FW: Unusual RunUbe Problem
Importance: High


Has it ever worked? Was it working previously on another release?

Did you verify security area? Maybe the profile you are submitting it with
does not
have the proper security against it for an update?

Also did you activate a trigger against the f0101? Could be allowance of
that and it
can't find the trigger program or associated tables.

Are any records being updated? If so, confirm you are closing the
tables/handles as desired.
It could be running out of available handles for processing and I've had
this same error for that. It was an NER that never closed the tables but was
executed hundreds of times.

You may want to check all these areas.

Good luck.

 

[Zoltan_Gyimesi]

Hi Jon,

Thanks for the updates.

It is a general rule of thumb that do not update key field(s) of an index which index could be used in a query.

As I mentioned, how to evaluate of a query based on the existing indicies is generally a private property of the DB system(s).

Please, let me an other idea (isn't sure that it works at all):

Maybe you can force the UBE and DB engine somehow that do not use the index in the query which is used in the UPDATE.
Try to force the system to use a single well defined index with selection and sequencing criterias and which is not the primary unique one. Select the primary unique key in your UPDATE statement. Use Open/Close also.

For example: Select an index and set your sequencing on the same manner as the index does AND/OR set your selection for all fields in the same order as they appear in the index. Of course, most of these selection could be "dummy" selection, e.g "Line Number is greater then '-1'".

I very well know, this isn't a sure solution, further isn't applicable for all situations and/or all tables, further could be behave differently on different OW releases, SP levels and mainly on different DB engines.

It was just a weak thought.

Good luck again,

Zoltán
P.S.: Could be useful to examine the issued SQL statement(s) in the jdedebug.log to find the better solution if ther is any at all.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[JonSellers]

Zoltan,

I am not updating the ALKY field. I am using the ALKY index to get the record and then I am updating the URAB field.

In other tests I have run, I have used the primary key to access the record that I wanted to update and then updated some other field.
I got the same results (all the records were updated but the ube ended in an error status).


Good point, though.

Jon

 

[Zoltan_Gyimesi]

Hi Jon,

Excuse me, I wasn't enough careful when I have read your initial long post and missed that ALKY was in the selection and not updated. Sorry.

I have an idea again ;-)

Place your update outside from the UBE into a simple NER Business Function and call this BSFN from the UBE. Pass the primary key and the values for update fields to the BSFN and pass back the File_IO_Status from the BSFN to the caller UBE. Of course, use the primary key for selection in the update statement inside the BSFN.

On the other hand, some question:
1.) Do you run this UBE on the Server, am I right?
2.) What about running it on the client (just for curiosity)?
3.) Have you tried to rebuild it into a package with re-created (Added, not Copied) version(s) and re-deployed it?

Good luck again,

Zoltán





B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[JonSellers]

Perry,

I will look into the security/printing aspects. But I doubt thats it, since the error occurs only when I update more than 80,000 records (or so). Security should be the same regardless of the number of records being processed. Ditto for printing.

CNC guys tell me no errors when deployed.

I have run the ubes from within OW (Batch Versions - BV) prior
to using runube. Good point to remember though.

Thanks.

-----Original Message-----
From: Perry
Sent: Thursday, December 13, 2001 1:03 PM
To: jon
Subject: RE: Unusual RunUbe Problem
Importance: High


hmmmm...would verify the security for the profile and make sure it has full
authority
especially since f0101 is security heavy table...also verify their library
lists

and maybe its having some issue printing the report vs. on update then?

and no problem on deployment?

also 1st make sure you run the ube via oneworld environment 1st. this so the
server job can access the version. we always have problems not doing that
1st...

Good luck!

 

[JonSellers]

Zoltan,

It was a long post - easy to miss a small detail

Good idea about the bsfn. Denver suggested using a outside bsfn to do the updates as well. Have not tried that yet. On the list of "thing to try".

Answers to questions:

1.) Running on the AS400

2.) Is only a problem with 'runube' - which is not on my client.
Ube Runs fine locally.

3.) I have created and deployed brand-new UBEs to test this problem.
Same results.

BTW - I am waiting for our CNC group to deploy two ubes to test your two suggestions:
1) using open/close/update with handles to update the records
2) perform update in a section that does not have a business view

Thanks.

Jon

 

[Zoltan_Gyimesi]

Hi Jon and Perry,

Excuse me this off-topic reply.

It seems to me that it is very confusing that Perry answers to Jon privately while Jon's re-replies go to the Forum under his thread and this post appears as an answer to an other reply.
Is it confusing only for me?

Perry, why don't you reply on the Forum/List?
You would be welcome here and your help will be also greatly appreciated!

Come on!

Regards,

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)

 

[JonSellers]

Sorry for any confusion.

I copied/pasted Perrys direct email and replied to it via the "post it" screen so that everyone could benefit from or add to Perrys comments.

Jon

 

[Zoltan_Gyimesi]

Hi Jon,

I am really sorry that I wasn't able to provide you with other hints than that you have already received from Denver but I don't give up

Next hint:
1.) Create a work file with a Next Number field, unique key values of the original table and values for update field in he original table.
2.) Create a primary unique index for this table from the Next Number field the the unique key fields of the original table.
3.) In your main UBE:
* retrieve Next Number value
* delete worktable records with partial key update with the Next Number value
* create a record in the work table for all (e.g.) F0101 record which have to update (presumably obviuos how to fill the fields)
* create an other UBE with an interconnect data structure receiving the Next Num value and passing back counters about succes and failed and/or status flag(s) about the running.
* filter the work table for Next Num (could be based on a BSVW with the worktable behind it) and update (e.g.) F0101 record based on the values in the work table
* call this new UBE from the main UBE based on (e.g.) F0101 after created all work table entries
* * delete worktable records again with partial key update with the Next Number value in your main UBE after the new UBE completed.

I suppose you know, Next Num solves to separate records in the worktables for more concurrent sessions.

Really hope, you will notify us about the results of your experiments even it solved the problem or not.

Good luck again,

Zoltán
P.S.: Although I don't know how to produce something debug log on AS400 but it would be useful if you won't be able to get rid of this problem.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)<P ID="edit"><FONT SIZE=-1>Edited by Zoltan_Gyimesi on 12/13/01 12:35 PM.</FONT></P>

 

[peter_hamilton]

I'll throw my 2 cents worth into this.
The first suggestion is that jobs submitted by scheduler (i.e. runube) should immediately use the launchube command to kick off the actual ube you want to run.
Thus, as you originally stated, since it works OK under launchube, then this "should" get around the issue (just not solve it).

Question.
When you state that it works OK running locally, is that when it updates OVER 80,000 records, since it seems to be on a large update run that you hit the problem.
Check commitments, or maybe space, since it may run out of temp file space (for logging, or when running the job.

My only other suggestion, is, if the update can be "simulated in an SQL stattement, that you run the same SQL statement, and make sure that the error is not being caused by SQL (instead of oneworld) and then it would be an IBM issue. I.e. Either one SQL statement that updates URAB on ALL F0101, or, an SQL script, that runs an SQL statement for an individual update for each AB record.



Hope this helps,


Peter Hamilton
B7332 SP14, Windows NT, Unix

 

[JonSellers]

Peter,

Thanks for replying.

Unfortunately, Launchube is not an option for our shop.
We have production batch jobs that run on 4 platforms - 390 Mainframe, AS/400, Unix, and NT servers. We are using a third-party job scheduling package called "Control-M", which can schedule batch jobs across these multiple platforms. For One World, Control-M uses "runube" to submit batch jobs (ubes).

The UBE only fails when submitted via runube and executed the server (AS400) and if it processes more than 80,000 records.
The ube updates all of the records - it just ends with an 'error' status.

The UBE works perfectly when submitted via Launchube/Batch Version and
executed on the server, regardless of the numbers of records processed.

Updating the records directly via SQL works fine but is not practical in this situation. In the real production UBE, updating the table is not the main purpose of the ube.

Thanks.

Jon

 

[JonSellers]

Status:

Zoltán coding change#1 - Using Open/Close and update with handles yielded no change. Same error.

Zoltán coding change#2 - Moving table update to a section in the ube that is not tied to a business view also yielded no change . Same error.

Zoltán coding change#3 - Ceating a simple NER business function to perform the updates and then calling that BSFN from the ube
appears to have fixed the problem !!!

I am going to deploy this solution to our CRP (Prototype) environment for further testing.

I will update the the group with the results.

Thanks for the input.

Jon

 

[David Robertson]

Christian,

Your rule "if you read and update the same table, then use HANDLE" I believe is actually the documented JDE rule. I'm not sure where I found this, but I had similar problems. If you don't use habdles, you get corrupted pointers, and general craziness happening.

Regards,
David

Independant Technical Consultant
CNC and Development
Currently in Europe on B732sp16 RS6000 Oracle

 

[JonSellers]

To JDELIST,

Using Zoltan's #3 suggestion,

"Place your update outside from the UBE into a simple NER Business Function and call this BSFN from the UBE. Pass the primary key and the values for update fields to the BSFN and pass back the File_IO_Status from the BSFN to the caller UBE. Of course, use the primary key for selection in the update statement inside the BSFN."


appears to have solved the problem.

Thanks for all of the input.

Jon