Re: How/Where do I use COMMIT in SQL*Loader

Hi,

    Have you considered cleaning up the garbage in your input file?

Since it is always the last record in the input file what assurance do you have that you are getting all of the records you should? If I were in your position I would be screaming, ranting, raging, and threatening the person who wrote the program that creates your input file until they fixed it.

If they refuse to fix it, or perhaps don't know how to fix it, you should write or have written a utility that will go in and clean up the last record in your input file.

Get rid of the garbage and then you can run a high speed load.

regards
Jerry Gitomer

IIFThen wrote in message ...
|I tried that but it ran too slow for 2.7M records. BTW, I'm
using Direct
|Path.
|Is there another alternative?
|
|IIFThen
|
|Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message
|news:933183973.4732.0.pluto.d4ee154e_at_news.demon.nl...
|> rows=1 on the command line.
|>
|> Hth,
|>
|> Sybrand Bakker, Oracle DBA
|>
|> IIFThen <msessions_at_aldensys.com> wrote in message
|> news:nnDn3.5559$i5.48073_at_news4.mia...
|> > I'm still trying to solve my problem loading my data with
that has
|partial
|> > records at the end of the file. How do I commit each record
one at time?
|> > What is the syntax? Where is used? CTL file or SQL*Loader
command line?
|> >
|> > Thanks,
|> > IIFThen
|> >
|> > See responses below for background on my problem:
|> >
|> > Joseph E. Steinhauser <jsteinha_at_us.oracle.com> wrote in
message
|> > news:3794E411.85693F85_at_us.oracle.com...
|> > > > A few suggestions
|> > >
|> > > ...although I would ad that I have usually run SQL*Loader
on unix...
|> > >
|> > > 1) If you think that your control file is doing fine,
then focus on
|> your
|> > data
|> >
|> > that is where I think the problem is. I have a junk
character at the end
|> of
|> > my data file and SQL*Loader is reporting that all records
were loaded
|> > successfully but when I query the Oracle table for these
records they do
|> not
|> > exist. Weird..huh?
|> >
|> > > 2) Try creating a 10 record file of 'test' data, that you
have set up
|> by
|> > hand,
|> > > and then see how successfully you can load that into
the table.
|If
|> > you
|> > > can load that finne, then your .CTL file is working.
|> >
|> > I've verified this and for records that do not contain any
eof chars it
|> > works fine.
|> >
|> > > 3) For testing, use the Loader option to commit EACH
record, one at a
|> > time.
|> > > This way, any problems you have with records later in
the data
|file
|> > cannot
|> > > hurt you with the records before that.
|> >
|> > I'll try this.. this might be a good workaround. Where is a
good place
|to
|> > put this COMMIT in my control file[see 8)]?
|> >
|> > > 4) examine your 'log' files in detail
|> >
|> > Check...
|> >
|> > > 5) verify the exact record lengths, paying specific
attention to bytes
|> > used as
|> > > [EOL] characters (ie: record delimiters & CR/LF )
|> >
|> > Check..
|> >
|> > > 6) check you data, to make sure that no tool/app that you
are using
|> would
|> > have
|> > > modified the formatting at all/ or added any sort of
"header
|info"
|> > etc.
|> >
|> > Check. data looks fine.
|> >
|> > > 7) Try using the options "skip" and "load" to tell
SQL*Loader to skip
|> over
|> > > a few records in the file - then load several records
for
|testing -
|> > and
|> > > quit
|> > > before you get to the [EOF]
|> >
|> > I'll try this if 3) doesn't do it for me.
|> >
|> > > 8) If you haave any further questions from the
newsgroup - try to
|> include
|> > > further details about the structure of the data,
|> > >> Variable records possible
|> > where it came from,
|> > >> mainframe
|> > the
|> > > control file & command line options that you are
using, error
|logs,
|> > etc.
|> >
|> > control file:
|> > -- Requires CR/LF on last line of control file.
|> >
|> > OPTIONS (DIRECT=TRUE, PARALLEL=FALSE)
|> > UNRECOVERABLE
|> > LOAD DATA
|> > INFILE 'aithw.dat'
|> > TRUNCATE
|> > INTO TABLE AIT.AITHW
|> >
|> > ("STATE" POSITION (01:02) CHAR,
|> > "AREA" POSITION (03:09) CHAR,
|> > "IMPORT_DAT" POSITION (10:15) CHAR,
|> > "CPR" POSITION (16:21) CHAR,
|> > "FRC" POSITION (25:29) CHAR,
|> > "FLOOR" POSITION (30:31) CHAR,
|> > "FRAME_ID" POSITION (32:40) CHAR,
|> > "YR" POSITION (41:42) CHAR,
|> > "QTY" POSITION (43:50) CHAR,
|> > "MAT_COST" POSITION (51:62) CHAR,
|> > "INPL_COST" POSITION (63:74) CHAR,
|> > "ECN" POSITION (75:79) CHAR,
|> > "ECN_INPL" POSITION (80:91) CHAR,
|> > "BASIC" POSITION (94:105) CHAR,
|> > "PART" POSITION (106:132) CHAR)
|> >
|> > command line(run from NT server):
|> > sqlldr80 userid=ait/admin_at_aldenorcl control=aithw.ctl
discard=aithw.dsc
|> > data=%1
|> >
|> > >
|> > > ... I hope this helps...
|> >
|> > Me to. thanks!
|> >
|> > IIFThen
|> >
|> > >
|> > >

Joseph
|> Steinhauser
|> > >
|> > >
|> > >
|> > > > > > Can SQL*Loader (used on NT server) handle partial
records at the
|> END
|> > of a
|> > > > data file?
|> > > > > >
|> > > > > > SQL*Loader log file reports records successfully
added but when
|I
|> > query
|> > > > the data with
|> > > > SQL Worksheet it returns a count of 0. Can anyone
out there
|try
|> > > > inserting a character at
|> > > > the end of their datafile and then run
SQL*Loader against
|it.
|> > Then
|> > > > query to see if ALL
|> > > > non-partial records were loaded.
|> > >
|> > > > 1) SQL*Loader reports Error 524: Partial records
found at the
|> end
|> > of
|> > > > the data file,
|> > > > 2) SQL*Loader log file says that all records were
successfully
|> > loaded.
|> > > > 3) When I query the table, no records are there.
|> > > >
|> > >
|> >
|> >
|> >
|> >
|>
|>
|
|
Received on Thu Jul 29 1999 - 12:06:44 CDT