Add call to CL program from UBE

[jean_driscoll]

We have one program that has taken a performance hit, due to an IBM ptf that had to be applied (and cannot be removed). We have discovered two things: 1) If we delete the SQL package before this UBE runs, the UBE runs as fast as it used to 2) the problem is caused by row security. If we have a user who has no row security records run the UBE, the UBE runs as fast as it used to (we've tried inclusive row security - no help). The UBE is submitted to batch, and the first thing it does is create this SQL package. I'm looking for a way to insert a call to a CL program, before this SQL package is built. Does anyone know exactly when in the UBE processing that the SQL package is built? It is before/after the first business function has been processed? Can anyone tell me the first business function that is executed in a UBE and can it be modified? Conversely, does anyone know how to substitute the user id submitting the UBE with another user id?

 

[DBohner-(db)]

Jean,

When all else fails - cheat... Here might be a work-around...

Instead of calling the UBE directly from JDE - call a 'calling' ube. It works like this... create a UBE that calls a CLP that does a runube. The chain would look like this
R55RUNCLP
-> CLP (remove SQL Package | Call RUNUBE)
---> the RUNUBE'd UBE initiates a new SQL package...

Will this work for you?

Also - which PTF. Others may be very interested to know (like me)

db

 

[jean_driscoll]

Thanks for the idea, I'll add it to the list. With this solution, it looks
like you would submit via World/As/400, which would work if this was a
scheduled report, but we have 20 accountants submitting these through
OneWorld at will (as they close a facility), and they don't use World. JDE
suggested scheduling using the OW scheduler which, if we were to schedule
them, we could use because you can change the user id that it submits under.
I will talk to the users about whichever solutions come up.

We have a new imaging product, sold by Information Integration Systems, that
uses VB script and ADO to access the AS/400. The AS/400 had some SQL
problems with their implementation and we had to apply an assortment of
PTF's. The PTF that caused this particular problem was a test PTF and it's
co-requisites, SI04884, SI04908 (SI04916, SI04907). It fixed the imaging
problems so we cannot uninstall this ptf.



Jean Driscoll
AS/400 Co-existent Xe 17.1, Update 4/A73Cum12

 

[shazbot]

Jean,

The SQL statement is generated after the Initialise event of your main report section - if you put your call to the CL program/command in here, it'll be executed before the SELECT is run.

In this event, put a call to business function B34A1030 - Execute External Program. Passes some error flags and message ids as well as your command line - DLTSQLPKG.....

Things to remember when using this business function:
- the command must be native to the platform your UBE will run on (so when you're testing on your thick client it'll process PC commands - only when you've deployed to the enterprise server will an AS/400 command work). I got around this by setting the command up as a processing option - each time I want to run it I can change the command around when submitting the version (so if I make a boo-boo I don't have to change the source & do another deployment to fix it).
- the AS/400 command you're running must be in the library list of the JDE kernel. From experience that's a pretty small library list, though the command you want to run (DLTSQLPKG) is in QSYS anyway so you should be ok. To get around this, you could call a CL program that's in QSYS (or one of the other available libraries) that does some library manipulation, runs the programs or commands you want, then resets the library to the original contents - not sure if this might affect anything else using that kernel though - haven't explored that far yet.

Hope this is useful to you.
Sharon

 

[jean_driscoll]

sharon rules! We'll give this a try.



Jean Driscoll
AS/400 Co-existent Xe 17.1, Update 4/A73Cum12