Operating System - OpenVMS

Multiple errors SQL process

 
odwillia
Frequent Advisor

Multiple errors SQL process



Our programmers are seeing an issue wherein some of our users are getting different (incorrect) SQL results. All of them are running the same query in the same environment but for whatever reason the result set is different. They believe that this is due to a profile issue. However these users have run the same process previously without error. To test we matched profiles with a user that received the correct results but the test user still got the same incorrect SQL results.

Then the programmer received the following errors for the processusing a test account we created:

RDB-F-SYS_REQUEST, error from system services request

The programmer tried the SQL under TEST_DB & executed to create the CSV file out on PCDATA. This is the error message at the bottom of the SQLOUT file found in this user account home directory.

%RDB-F-SYS_REQUEST, error from system services request
-COSI-E-OPENERR, cannot open file
-RDMS-F-ERROPENOUT, error opening RMADSK04:[SORT_WORKSPACE_2]SORTWORK.TMP; as output
-RMS-E-PRV, insufficient privilege or file protection violation

When the programmer logged out of the test account she received these errors:

%BAS-E-FATSYSIO_, Fatal system I/O failure │ │[ ] 80 │
-BAS-I-ON_CHAFIL, on channel 5 for file TT:[].; at user PC 00000000 │[
________________________________________
] 132 │
-RMS-F-IFI, invalid internal file identifier (IFI) value──────────┘ └────────┘
TEST_DB logged out at 13-FEB-2009 15:44:28.44─────────────────────────┐


Let me know if you need more info or this is not clear.

We believe this is an application issue.

As always I appreciate your assistance.
17 REPLIES 17
Hein van den Heuvel
Honored Contributor

Re: Multiple errors SQL process

>> They believe that this is due to a profile issue.

Please try to explain in OpenVMS terms what you mean with 'profile'. SYSUAF account attributes? Logical names / symbols perhpas set up by a login.com? A logical name table being included? Some RDB settings?

>> However these users have run the same process previously without error.

So something changed. That 'profile'?
Or maybe just 'more data' requireing an external sort now, versus memory sort before?

>> To test we matched profiles with a user that received the correct results but the test user still got the same incorrect SQL results.

Can you identify the difference between a workgn and failing 'profile'?



>> -RDMS-F-ERROPENOUT, error opening RMADSK04:[SORT_WORKSPACE_2]SORTWORK.TMP; as output
>> -RMS-E-PRV, insufficient privilege or file protection violation

That's a clear, and hard, error. Does it make sence?
- $DIRECTORY/SECURITY RMADSK04:[000000]SORT_WORKSPACE_2.DIR
- What identifiers does the user have?
- If you temporarely open remove protection on that directory, does that problem go away?


>> When the programmer logged out of the test account she received these errors:
>> %BAS-E-FATSYSIO_, Fatal system I/O failure >> -BAS-I-ON_CHAFIL, on channel 5 for file ->> RMS-F-IFI, invalid internal file identifier (IFI) value

Don't worry too much about that, just now.
It means BASIC asked RMS to close a file using a FAB which was either completely invalid, or had already been closed.
Typically it suggest that some code stomped over dynamic memory corrupting some of the BASIC RTL structures. Most likely an error handler. This is not good, and ought to be figured out some say, but it is likely to be a secondary problem just now.

Hope this helps some,
Hein.
EdgarZamora_1
Respected Contributor

Re: Multiple errors SQL process

I'm not sure if it's related to your SQL problems, but your SORT "privilege" error is usually caused when you redefine SYS$SCRATCH to be a common directory for your users. It becomes a timing issue with the .tmp files being created there. You will find it's an intermittent error depending on timing of processes using SORT. IMO, SORT should be using uniquely named temp files but that's not the case right now. You probably have good reason for making SYS$SCRATCH a common area (such as disk space reasons) but you might wanna try deassigning it temporarily for test purposes to see if that's your problem. You would need sufficient disk space for the home directories of the users if you deassign SYS$SCRATCH.
odwillia
Frequent Advisor

Re: Multiple errors SQL process

>>Please try to explain in OpenVMS terms what you mean with 'profile'. SYSUAF account attributes? Logical names / symbols perhpas set up by a login.com? A logical name table being included? Some RDB settings?

By profile I mean the SYSUAF account attributes.

>>So something changed. That 'profile'?
Or maybe just 'more data' requireing an external sort now, versus memory sort before?

We tested by making the user accounts match exactly.


>>Can you identify the difference between a workgn and failing 'profile'?

No difference that I can see.



>> -RDMS-F-ERROPENOUT, error opening RMADSK04:[SORT_WORKSPACE_2]SORTWORK.TMP; as output
>> -RMS-E-PRV, insufficient privilege or file protection violation

That's a clear, and hard, error. Does it make sence?

- $DIRECTORY/SECURITY RMADSK04:[000000]SORT_WORKSPACE_2.DIR
- What identifiers does the user have?
- If you temporarely open remove protection on that directory, does that problem go away?

I'll test this.



>>Hope this helps some,
Hein.

Every little bit helps. Thanks.
odwillia
Frequent Advisor

Re: Multiple errors SQL process

>>I'm not sure if it's related to your SQL problems, but your SORT "privilege" error is usually caused when you redefine SYS$SCRATCH to be a common directory for your users. It becomes a timing issue with the .tmp files being created there. You will find it's an intermittent error depending on timing of processes using SORT. IMO, SORT should be using uniquely named temp files but that's not the case right now. You probably have good reason for making SYS$SCRATCH a common area (such as disk space reasons) but you might wanna try deassigning it temporarily for test purposes to see if that's your problem. You would need sufficient disk space for the home directories of the users if you deassign SYS$SCRATCH.

How can I tell if I have redefinned SYS$SCRATCH?

EdgarZamora_1
Respected Contributor

Re: Multiple errors SQL process

$ SHOW LOGICAL SYS$SCRATCH

normally SYS$SCRATCH is defined to be your login directory. Some sites define a system-wide (or group-wide, etc.) logical for SYS$SCRATCH to a common area for all users.

I've seen this SORT problem a few times... a user owns a sort work temp file in the directory... another user tries to open the same file name and gets the privilege error. All my users have their own accounts. If you have multiple users using the same account name, you'd probably have a more subtle problem.
odwillia
Frequent Advisor

Re: Multiple errors SQL process

This what I get for a logical for SYS$SCRATCH and the disk with the error.

$ SHOW LOGICAL SYS$SCRATCH
"SYS$SCRATCH" = "CLUSTERDISK:[CONRES]" (LNM$JOB_8213F8C0)

$ sh logical RMADSK04
"RMADSK04" = "$1$DGA858:" (LNM$SYSTEM_TABLE)
$
EdgarZamora_1
Respected Contributor

Re: Multiple errors SQL process


It looks like "CLUSTERDISK:[CONRES]" is your login home directory. That doesn't match the work directory shown in your original post (RMADSK04:[SORT_WORKSPACE_2]). What I'm trying to tell you is that the accounts running the application (and experiencing the problem) must be using/sharing the same SORT temp directory. You might wanna try to separate that (just to see if that's your problem).
Chris Barratt
Frequent Advisor

Re: Multiple errors SQL process

I believe that there is also a logical that can be used to point Rdb sortwork files to a particular directory. Given that RMADSK04:[SORT_WORKSPACE_2] is not your home directory, I would be checking to see whether that logical is turned on. I don't remember it's name, but a "Show log rdm*" should pick it up - or just check the documentation to be sure.

It could be that the working user has accessto this directory, whereas the other users do not.

Why then would it have worked previously ?
- maybe the logical has only been defined recently
- maybe the query didn't use to require a sort work file, but something has changed in the optimisation (often cardinalities), and now it does.

Just one more idea...

cheers,
chris
odwillia
Frequent Advisor

Re: Multiple errors SQL process

Thanks Chris. I will look into that as well.