dgibbs_dba
asked on
Running multiple SQLLDR command lines from one script
I have six csv files we need to load every morning. I've written a script to create dynamic control files for each file, since they have the current date in the file name. I'm now trying to run this script as well as the six sqlldr scripts from one script. The script executes and creates the six control files with no problem. It also runs the first sqlldr script but aborts after it completes. I'm guessing this is because of bad records. I want the bad records to write to the .bad file (it does) and continue on to the next sqlldr script. It just stops. We're VMS, Oracle 9205. I've tried silent=all and that had no affect. Here are the last lines before it aborts. I'm thinking this may be because of a VMS error message. Thanks in advance for your time.
...
Commit point reached - logical record count 3392
Commit point reached - logical record count 3439
%NONAME-E-NOMSG, Message number 00000002
Here's a portion of my script:
$!
$@RUN_CR_SQLLDR_CTRL.COM
$!
$@run_sqlldr_hnama.com -- ABORTS after this one
$@run_sqlldr_hnamb.com
$@run_sqlldr_hnamc.com
$@run_sqlldr_hnamd.com
$@run_sqlldr_hname.com
$@run_sqlldr_hnamf.com
Here's the first script that aborts:
$ set verify
$!
$!
$!
$ DATE == F$CVTIME("YESTERDAY",,"DAT
$ show symbol DATE
$ YY == F$EXTRACT(2,2,DATE)
$ show symbol YY
$ MM == F$EXTRACT(5,2,DATE)
$ show symbol MM
$ DD == F$EXTRACT(8,2,DATE)
$ show symbol DD
$ MMDD = "''MM'''DD'"
$ show symbol MMDD
$ MMDDYY = "''MM'''DD'''YY'"
$ show symbol MMDDYY
$!
$ sqlldr control=hnamaload'''MMDDYY
userid=sla/xxx, discard=hnamaload'''MMDDYY
bad=hnamaload'''MMDDYY'.ba
$!
$!
$exit
$ exit
...
Commit point reached - logical record count 3392
Commit point reached - logical record count 3439
%NONAME-E-NOMSG, Message number 00000002
Here's a portion of my script:
$!
$@RUN_CR_SQLLDR_CTRL.COM
$!
$@run_sqlldr_hnama.com -- ABORTS after this one
$@run_sqlldr_hnamb.com
$@run_sqlldr_hnamc.com
$@run_sqlldr_hnamd.com
$@run_sqlldr_hname.com
$@run_sqlldr_hnamf.com
Here's the first script that aborts:
$ set verify
$!
$!
$!
$ DATE == F$CVTIME("YESTERDAY",,"DAT
$ show symbol DATE
$ YY == F$EXTRACT(2,2,DATE)
$ show symbol YY
$ MM == F$EXTRACT(5,2,DATE)
$ show symbol MM
$ DD == F$EXTRACT(8,2,DATE)
$ show symbol DD
$ MMDD = "''MM'''DD'"
$ show symbol MMDD
$ MMDDYY = "''MM'''DD'''YY'"
$ show symbol MMDDYY
$!
$ sqlldr control=hnamaload'''MMDDYY
userid=sla/xxx, discard=hnamaload'''MMDDYY
bad=hnamaload'''MMDDYY'.ba
$!
$!
$exit
$ exit
Mark Geerlings
As an alternative, could your script instead rename each of the six data files (one at a time) then load the first one using a static controlfile, then rename the resulting: *.bad, *.log and *.dis files, then go on the next file, etc.?
ASKER
Not sure I understand what you mean?
I think you may be on the right track with the error message. However, it has been a very very very long time since I have used VMS. Isn't there something that can be set to ignore warnings and errors? I cannot recall what it was, but I thought there was something that could be used as an error handler in case the command returned an error or warning. I think it is defaulting to exiting the script on an error/warning.
It seems, you have reached maximum number of insert errors to allow.
See ERRORS command line parameter:
ERRORS (errors to allow)
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.
ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value specified for ERRORS, then SQL*Loader terminates the load. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.
On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.
See ERRORS command line parameter:
ERRORS (errors to allow)
Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in Invoking SQL*Loader.
ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value specified for ERRORS, then SQL*Loader terminates the load. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.
On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.
My idea yesterday was in response to this line in your question: "I've written a script to create dynamic control files for each file, since they have the current date in the file name."
I wondered if you could instead use the same (static) control file for each of the data files. Like Johnsone, I also haven't worked with VMS scripts for many years, so I can't write the script file for you, but here's what I was thinking of in pseudo-code:
1. Check for a data file with the standard name and move, rename or delete it.
2. Find the first data file and rename it to a standard name.
3. Run SQL*Loader using a static script that expects a standard input file name.
4. Rename the data, log, bad and discard files to match data file #1
5. Repeat steps 2-4 for each of the other five data files.
I wondered if you could instead use the same (static) control file for each of the data files. Like Johnsone, I also haven't worked with VMS scripts for many years, so I can't write the script file for you, but here's what I was thinking of in pseudo-code:
1. Check for a data file with the standard name and move, rename or delete it.
2. Find the first data file and rename it to a standard name.
3. Run SQL*Loader using a static script that expects a standard input file name.
4. Rename the data, log, bad and discard files to match data file #1
5. Repeat steps 2-4 for each of the other five data files.
I was responding to this:
It also runs the first sqlldr script but aborts after it completes.
If I am reading this correctly, the issue is that the outer script that runs SQL*Load is aborting if one of the SQL*Loads aborts and that is the problem we are looking at. Even if there are too many bad records that cause the load to stop, the second SQL*Load should start.
It also runs the first sqlldr script but aborts after it completes.
If I am reading this correctly, the issue is that the outer script that runs SQL*Load is aborting if one of the SQL*Loads aborts and that is the problem we are looking at. Even if there are too many bad records that cause the load to stop, the second SQL*Load should start.
ASKER
I appreciate all the comments. I've done testing and the issue is the bad records; the script aborts at that point in the first sqlldr run. I altered the table to fix the bad data issue, reran the full script and it completed successfully.
ASKER
I found the answer - set NOON in the script and VMS ignores the errrors. Thanks to all to replied.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.