RdB; Inability to execute complex queries in detached process

[Willem Grooters]

Environment: Alpha, OpenVMS 7.3-2, RdB 7.1
We run a CGI-based webservice, accessed after authentication. The user is passing a URL that contains a query to be executed, including the database on which the query is to be executed, and its parameters. The actual database access is done by a (locally created) procedure, containing all required assignments and definitions, which is next executed in a subprocess under the user that issued the request - thoug username is still the user under which the webserver runs:

$ run/uic=(UIC of remote_user)/INPUT=(command
procedure)/OUTPUT=(output-spec) sys$system:loginout

This works fine, but some users encounter a problem when queries become more complex. Tracing revealed this error on execution of the query:

! RDB Message Vector:
! 013881b4 00000000 056ec054 00000000 000184c4 00000908 ! %RDB-F-SYS_REQUEST, error from system services request?
%COSI-F-CREATERR, cannot create file?
%RMS-F-DEV, error in device name or inappropriate device type for operation?
%SYSTEM-W-NOSUCHDEV, no such device available


Since the query is complex, we anticipate RDB will need to create a (temporary) workfile and fails - because a logical is improperly
defined, or a device is completely missing. But we couldn't locate anything that may cause the problem.

However, if this user runs the very same query interactively, nothing is wrong. We have checked the logicals in both environments, for one user, and found no difference.

My questions:

Is a difference in behaviour between interactive and deteached processes?
What device may be expected here?
Is there some data, inside or outside the database, by which the location of workfiles is controlled?

(Crossposted on Oracle metalink forum)

[Jean-François Piéronne]

Willem,

You can try to defined RDMS$RUJ, if I remember correctly it is needed for a detached process.

JF

[Jean-François Piéronne]

SYS$SCRATCH is also used by Rdb for sortworks and temporary files.

JF

[Volker Halle]

Willem,

if this is reproducable, you may want to try the LNM$SDA SDA extension to trace logical name usage to find out, which logical name may be involved.

$ ANAL/SYS
SDA> LNM ! for help

Volker.

[Hoff]

>>> run/uic=(UIC of remote_user)/INPUT=(command
procedure)/OUTPUT=(output-spec) sys$system:loginout

Others have pointed to the logical names for the RUJ and potentially for the scratch area and such. You can get SYS$SCRATCH and SYS$LOGIN and such defined with the /AUTHORIZE qualifier.

http://labs.hoffmanlabs.com/node/318

And a second rule of thumb here: Anytime you have the option of specifying a process quota list, specify the full list.

Personally, I'd probably avoid the whole construct here, and toss the (vetted!) command over to a pool of locally-written servers waiting to perform the specified commands. Database connection times have dropped significantly over the years, but this sequence is still a comparatively heavyweight sequence for processing commands.

I'll presume you're familiar with SQL injection, too.

[Willem Grooters]

JFP:
We found SYS$SCRATCH already, and have that defined. But it didn't help much. RDMS$RUJ seems not a requirement; the process where the query succeeds doesn't have it defined. But it might be an idea to put it in as well.
VH: good idea - if this can be checked that way
SH: You're right on the quota list - but that is not the problem here (or RdB would give the wrong value). What on login procedures: are they executed when specified RUN/UIC in DCL? I think so, since quite a lot of logicals setup in the login sequence are actully defined (well, I didn't see SYS$SCRATCH, but that isn't defined in my interactive process either).
Oh: SQL injection: not an issue here, because part of the executable is checking the validity of the SQL. Plus: it's a closed environment :)

[Hoff]

Check around for any of the Rdb logs and dumps, and ensure that SYS$LOGIN, SYS$LOGIN_DEVICE, and SYS$SCRATCH are present (which is what /AUTHORIZE gets you), and ensure the RDMS$RUJ logical name and directory is present.

I'm loathe to use the /UIC and SET UIC knobs in general, as wacky things can happen; the old RSX-style UIC slam just doesn't map fully into the OpenVMS environment.

And I found that specifying the process quotas (and big enough) got rid of a very large percentage of the Rdb weirdness and database corruptions and crashes and hangs I'd been tussling with. YMMV, of course.

[Craig A]

If I recall correctly SYLOGIN doesn't get executed when RUN/DETACH is used (just in case there is any RDB setup calls in there).

Craig

[John Gillings]

Willem,

How many times does this need to be stated in public fora?

$ RUN/UIC=some-random-UIC

DOES NOT do what you think it does.
You get a detached process with YOUR username, and a DIFFERENT UIC. This construct doesn't make much sense on OpenVMS, leads to all manner of odd behaviours and should be avoided. Logical names don't line up properly, rights lists are wrong, etc... It's not worth trying to "fix" all the potential issues when there are supported means for achieving what you want.

You seem to know that, so why don't you suspect that is the cause of this problem?

>However, if this user runs the very same
>query interactively, nothing is wrong.

Most likely because when they run it interactively they ARE that user. When run with your RUN/UIC command THEY ARE NOT!

If you want a detached process running under a different USERNAME from the issuing process, the simplest supported means to achieving it is to encapsulate your RUN/DETACHED command in a command procedure (WITHOUT "/UIC=") and "SUBMIT/USER=username" that procedure.

You then get a brief batch job running CORRECTLY as your target user, which then starts the detached process CORRECTLY.

Please try your test using RUN/USER.

[Hoff]

>If you want a detached process running under a different USERNAME from the issuing process, the simplest supported means to achieving it is to encapsulate your RUN/DETACHED command in a command procedure (WITHOUT "/UIC=") and "SUBMIT/USER=username" that procedure.

The approach I've used (also fully documented and supported) is an application image that uses the persona system services to assume my way over to the target user context, launch the new process, and then use the persona call to revert.

[Richard J Maher]

Hi John,

>>>>>>>>>>>>>>>>>>
If you want a detached process running under a different USERNAME from the issuing process,
<<<<<<<<<<<<<<<<<<<

I don't think he does; I think Willem is just one of many trying to implement a very poor-man's server.

>>>>>>>>>>>>>>>>>>>
the simplest supported means to achieving it is to encapsulate your RUN/DETACHED command in a command procedure (WITHOUT "/UIC=") and "SUBMIT/USER=username" that procedure.
<<<<<<<<<<<<<<<<<<<

Perhaps the simplest but yet another poor choice that has been discussed many times. Poor in performance, o/head, error-handling, and functionality. Why not go crazy and actually write a little program? I know it's not scripting and no one does it anymore but just $persona_assume and the $creprc and off you go. VMSNOTES circa 6.2 15 years ago?

But let me go out on a limb here and suggest that what Willem really wants is to have a setup where he has transparent multi-threading and an active pool of VMS processes serving a far greater number of clients yet have the servers "become" the client whilst performing work on their behalf?

Look for t3$persona_assume in the attached file. And all with *not one* additional VMS privilege required for the server process!

Of course what goes hand in hand with VMS personae is the very useful rdb "session authorization" functionality as in: -

SQL> set session authorization persona :ws_integer

The problem unfortunately is that Rdb engineering (or at least one person in Rdb engineering) refuses to add the "persona" syntax and demands that you lug around VMS passwords *in the clear*.! A real shame :-(

Cheers Richard Maher

[Willem Grooters]

Consider it known that this solution isn't the brightest one. But I was called in too late to change matters. I just have to work around it. Sorry guys, but these things happen ;)

The problem is not that queries are not executed. They are - for ANY user. Just those that contained subqueries failed - depending on the size of the dataset. Simple as that.

The matter is: why.

Crossposting does have advantages. See the solution found.

[Willem Grooters]

Since teh problem happened on complex queries, thoughts were a lack of workspace. Since the program runs in a detached process, SYS$LOGIN, SYS$LOGIN_DEVICE, and SYS$SCRATCH are not defined. RDMS$RUJ seemed not needed (it's read-only).

Adding:

$ define/nolog sortfile0
$ define/nolog sortfile1
$ define/nolog sortfile2
...
$ define/nolog rdms$bind_work_file

before running the executable, as suggested on Oracle Metalink, solved the issue.