topic Re: RdB; Inability to execute complex queries in detached process in Operating System - OpenVMS

RdB; Inability to execute complex queries in detached process

Willem Grooters — Wed, 08 Apr 2009 10:50:23 GMT

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)

Re: RdB; Inability to execute complex queries in detached process

Jean-François Piéronne — Wed, 08 Apr 2009 11:28:02 GMT

Willem,

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

JF

Re: RdB; Inability to execute complex queries in detached process

Jean-François Piéronne — Wed, 08 Apr 2009 11:31:58 GMT

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

JF

Re: RdB; Inability to execute complex queries in detached process

Volker Halle — Wed, 08 Apr 2009 11:58:20 GMT

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.

Re: RdB; Inability to execute complex queries in detached process

Hoff — Wed, 08 Apr 2009 12:37:59 GMT

>>> 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.

Re: RdB; Inability to execute complex queries in detached process

Willem Grooters — Wed, 08 Apr 2009 13:11:33 GMT

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 :)

Re: RdB; Inability to execute complex queries in detached process

Hoff — Wed, 08 Apr 2009 14:08:34 GMT

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.

Re: RdB; Inability to execute complex queries in detached process

Craig A — Wed, 08 Apr 2009 14:43:57 GMT

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

Re: RdB; Inability to execute complex queries in detached process

John Gillings — Wed, 08 Apr 2009 21:11:40 GMT

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.

Re: RdB; Inability to execute complex queries in detached process

Hoff — Wed, 08 Apr 2009 21:55:33 GMT

>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.

Re: RdB; Inability to execute complex queries in detached process

Richard J Maher — Wed, 08 Apr 2009 22:14:49 GMT

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

Re: RdB; Inability to execute complex queries in detached process

Willem Grooters — Fri, 10 Apr 2009 17:23:57 GMT

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.

Re: RdB; Inability to execute complex queries in detached process

Willem Grooters — Fri, 10 Apr 2009 17:29:42 GMT

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.