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.