File still locked after being closed

[Stephen Daddona]

I've got a nightly job that unloads a bunch of tables from our database. It uses the RMU/UNLOAD command in Oracle Rdb. The "root" DCL script submits a job for each table which in turn submits a job that FTPs the unload file. I had a problem last night with the unload file still being "locked" after the unload command finished - the FTP failed. The strange part is that the unload job waits 5 seconds after finishing the unload before submitting the FTP job. The FTP job tries 5 times to FTP the file, waiting 5 seconds between each try. The message that the FTP returns is:
"The process cannot access the file because it is being used by another process."

Have any of you run across this, and is there anything that I can do about it? Might it be a VMS problem?

Thanks!

[Ian Miller.]

is this system in cluster?

what does SHOW DEV/FILES show for that device?

[Stephen Daddona]

It's a stand-alone system.

By the time I got the notice on my pager and got logged in, the FTP worked. The SHOW DEV/FILES didn't show the file.

[Dale A. Marcy]

Is it possible that multiple unloads are going to the same filename (i.e., cut and paste and forget to change output filename)? You might want to add the show dev/files command into the error code that retries the ftp after it fails so that you can see what has the file locked.

[Stephen Daddona]

It's 99.999% that it's not a problem with multiple jobs having the file open. The "root" job submits an entry for each table that gets unloaded and those jobs only know about the one file. Plus, it's been running fine for months and I hadn't changed anything. I will try to add some debugging stuff like SHOW DEV/FILES.


Thanks!

[Hoff]

Would you be using the FTP utility and a script and not the DCL command COPY/FTP? If so, are the lock-related errors on the FTP script file itself?

(This is a guess.)

I've seen similar sequences and errors here, and switching over to the COPY/FTP command makes for easier coding, and it removes the reliance on an FTP script-based mechanism.

COPY/FTP was first available in V6.2, with a compliant IP stack.

If this isn't it, can you post the version info for OpenVMS and IP and Rdb, and an example of the particular LOCKED failure, of the command(s) involved, and the related error messages?

[Stephen Daddona]

I use FTP with a script file that I create with the username, password and the PUT command. (I'm sending the unloaded data file to a Windows server.) Here's some snippets from the two log files (note the timestamp displays):

1) This is the job that unloads the data and submits the FTP job:

$ wo f$time()
7-MAR-2007 22:43:42.84
$ rmu/unload/virtual_fields/record_definition=-
(file=DISK$DGA207:[DATABASE_UNLOAD]COURSE_DATA,format=delimited_text,-
prefix="",separator="|",suffix="")-
STUDENT_db COURSE_DATA DISK$DGA207:[DATABASE_UNLOAD]COURSE_DATA.unl
%RMU-I-DATRECUNL, 3004445 data records unloaded.
$ wo f$time()
7-MAR-2007 23:02:18.96
$!
$! We don't care about the record description (.RRD) file
$ delete/log DISK$DGA207:[DATABASE_UNLOAD]COURSE_DATA.rrd;*
%DELETE-I-FILDEL, DISK$DGA207:[DATABASE_UNLOAD]COURSE_DATA.RRD;1 deleted (171 blocks)
$!
$ if f$mode() .eqs. "INTERACTIVE"
$ else
$ logfile_name := -
sys$common:[sysmgr]database_unload_ftp.log_COURSE_DATA
$ wait 00:00:05
$ submit-
/log="SYS$COMMON:[SYSMGR]DATABASE_UNLOAD_FTP.LOG_COURSE_DATA"/noprint -
/queue = STUDENT_unload_ftp$batch -
/param=("STUDENT","COURSE_DATA","DISK$DGA207:[DATABASE_UNLOAD]") -
sys$manager:database_unload_ftp
Job DATABASE_UNLOAD_FTP (queue STUDENT_UNLOAD_FTP$BATCH, entry 3627) started on
STUDENT_UNLOAD_FTP$BATCH
$ endif


2) This the FTP job:
.
.
.
$ open/write ftp_commands -
sys$manager:database_unload_ftp.cmd_COURSE_DATA
$ write ftp_commands username
$ write ftp_commands password
$ write ftp_commands "enable log"
$ write ftp_commands "enable reply"
$ write ftp_commands "put DISK$DGA207:[DATABASE_UNLOAD]COURSE_DATA.unl"
$ write ftp_commands "quit"
$ close ftp_commands
$!
$ wo "Starting FTP of STUDENT.COURSE_DATA at 7-MAR-2007 23:02:24.67"
Starting FTP of STUDENT.COURSE_DATA at 7-MAR-2007 23:02:24.67
$ set noon
$ loop_count = 0
$!
$DO_THE_FTP:
$ ftp TAHOE -
/input=sys$manager:database_unload_ftp.cmd_COURSE_DATA
220 tahoe Microsoft FTP Service (Version 5.0).
Connected to TAHOE.
331 Password required for fusr_stu.
230-Sierra College SIS FTP Site
230-Unauthorized Access Prohibited
230 User fusr_stu logged in.
Bell off.
Debugging on (debug=1).
Reply on.
Verbose mode on.
---> PORT 10,10,2,154,206,111
200 PORT command successful.
---> STOR course_data.unl
550 course_data.unl: The process cannot access the file because it is being used
by another process.
---> QUIT
221
$ if ($status .eqs. "%X176499F2") - ! The UNL file is still open
.or. ($status .eqs. "%X17649B42")
$ then
$ save_status = $status
$ loop_count = loop_count + 1
$ if loop_count .gt. 5
$ endif
$ wait 00:00:05
$ goto do_the_ftp
(does this up to 5 times before giving up)

[Hoff · Accepted solution]

I'd still switch to COPY/FTP as that will simplify the batch job. Significantly.

I'd check the input with a SHOW DEVICE/FILE prior to the COPY/FTP, and I'd take a very careful look to determine if this is an error being returned from the Microsoft FTP server or from the Windows box.

To test for the latter error case, I'd change the RMU export and the COPY/FTP to select a different filename on OpenVMS (and I'd probably also pick a more unique filename), and pass over the name course_data.unl as the target filename.

Something like this:

$ copy/ftp -
rmu_unload_'pid_time_string'.data -
x.y.z"user pass"::course_data.unl

The error resulting from a failure of this FTP command will tell you if the error was from the rmu_unload-whatsit file on the OpenVMS end, or from the course_data.unl file on the Windows end.

Right now, it's not entirely clear (to me) which is triggering this. But I'd tend to guess that it's the Windows box. The 550 class errors from FTP are usually returned from the target; from the remote host. Permission errors. Read-only errors. Etc. I'd not expect an FTP client to return a 550 for a local file access problem.

[Stephen Daddona]

I hadn't thought of the problem being on the Windows side!

The reason that I was using FTP/input was to have the Windows password in the command file so it doesn't show up in the .LOG files. (I delete the command file that FTP uses right after the FTP is done.)

Changing the target file name is an interesting idea. I'd have to modify the DTS job on the Windows box to look for the "other" file name. Plus I'm sure there would be other gotchas!

[Steven Schweda]

> The 550 class errors from FTP are usually
> returned from the target;

All the numbered messages are returned from
the server/target/remote system. The arrows,
"--->", identify what the client is sending
out. The things without arrows are the
server's responses. This is a problem on the
FTP server. The server's message may or may
not accurately describe the problem, but
that's where it is.

COPY /FTP /VERBOSE should show you all that
junk, too, so that's not a good excuse for
avoiding COPY /FTP. A "more unique filename"
(which is to say, a unique file name) sounds
like a good idea to me.

[Hoff]

>>>
Changing the target file name is an interesting idea. I'd have to modify the DTS job on the Windows box to look for the "other" file name.
<<<

Why? I'd rename the temporary file on the OpenVMS side from the RMU up through the COPY/FTP for testing purposes, and I'd transfer the file over (with an explicit rename operation, since you can pick the target file during COPY/FTP) under the original and classic filename; what the current Windows software expects. You have full control over the OpenVMS side, after all.

[Stephen Daddona]

Thanks for all of the responses