[freetds] Re: Freetds freebcp

R. Klemme bob.news at gmx.net
Mon Jun 14 06:27:55 EDT 2004

Hi again,

> On Fri, 11 Jun 2004, "R. Klemme" <bob.news at gmx.net> wrote:
> > 
> > the Big O database servers don't have anything like bcp
> > 
> > They have!  ... you can do bulk load in two modes; "direct mode" (or
> > "direct load") bypasses the TL and performs better [...]
> > If you have PK violations it's a real mess since you can't
> > simply rollback...
> When I looked into this, I found only commands similar to Microsoft's
> "Bulk Load" command.  That is, the server reads data from its own disk.  I
> didn't find a protocol optimization, no equivalent to a bcp client
> utility.  Happy to be enlightened, however.  ;-)

The tool is called "SQL*Loader".  You can find the documentation here:

Otherwise I can mail it to you directly (2.9MB).

Documentation about the "direc path" load can be found in chapter
"Conventional Path Export Versus Direct Path Export".

> The missing primary key situation is of course very similar to dropping
> and re-creating the primary key, and just as messy.  But, it might be more
> effiecient than using a separate table.  

I guess that depends on whether PK violations are rare or frequent.  We
decided to switch off direct path for Oracle since PK violations are
expected and the repair seemed to awkward to us.

> When you first described the Oracle tool, you said it just logged error
> rows.  Maybe I'm reading too much into what you're saying, but if the PK
> enforcement is turned off, how are PK violations detected and logged?  Or
> is it also able to log only data conversion errors?  I'm sure I'm missing
> something.  

With Oracle's "direct path" loads the PK is simply left in state
inconsistent and SQL Loader tells you so.  Then you have to query the table
to find duplicate records, delete them and after that, activate the PK
again.  "Messy" is the correct term for this IMHO.  I'd say, "direct load"
is for cases only where you're quite sure that there are *no* PK violations
(probably because you don't have a PK at all :-)).

With standard loads error lines are simply logged to the error file.  The
behavior is different from Oracle's because the error file receives lines
with client detectable errors (data format) as well as lines with server
errors (PK violation).

> > > A little experimentation and log
> > > perusal reveals that when the server rejects the batch, it doesn't say
> > > which row was the offender.  
> > 
> > Now, that's interesting.  I think MS's bcp spits out a message to stdout
> > about which row was rejected.  Or was it just the batch rejected?  
> The latter, at least in the version I tested.  

Yeah, my current tests yield the same result.

> > We considered another option: using PreparedStatement.addBatch() (we're
> > using jTDS) and basically implement the bulk load ourselves.  The JDBC
> > protocol states, that you can get the information which rows were
> > imported and which had an error.  
> If it *is* built on the bcp protocol, you'll get batch-level rejections. 
> If it's not, you'll get transaction-log constrained performance.  That's a
> lose-lose proposition.

Exactly. :-)

>  You'd be as well off writing a stored procedure to
> do a one-row insertion, and call it once per data file row.  
> > Now, that's interesting that you even consider to involve Mr. Rumsfeld. 
> 	;-)
> There are problems we know we can solve, and there are problems we know we
> can't solve.  Then there are the problems we don't know we can solve, and
> the problems we don't know we can't solve.  
> Let's just hope this is one of that last kind.  One of those is enough.  

I'd prefer it to fall into one of the first two categories - it's simply
better to decide what to do with known facts at hand than with guesses.

But, of course, from a philosophical point of view we mainly know that we
don't know anything for certain (-> Sokrates).  Our version of "truth" is a
rather pragmatic approximation most of the time (-> Watzlawick,
Constructivism).  So terms like "I know that X" and "I don't know that Y" do
make sense in colloquial language.

You wrote in an earlier posting:

> My experiments with Microsoft's BCP show that a batch
> containingduplicate keys, when applied to a table whose
> index was created "with IGNORE_DUP_KEY", is rejected in
> its entirety.

Now here are my findings: if I have a table with a PK and add an index with
the same conditions as the PK but with option "IGNORE_DUP_KEY" set, the
error message changes and duplicates are simply dropped (tested with MS's
bcp) but the other rows are inserted ok.  Of course it's quite useless to
have the same index twice, but the same happens if you drop the PK.

So in any case, this is the Oracle like behavior, which really helps us.  We
didn't find this because we looked only at bcp options. :-)  Now I just have
to check whether we have to face any any disadvantages when we exchange the
PK for a normal index.

Thanks for your time and all the insights!

Kind regards


"Sie haben neue Mails!" - Die GMX Toolbar informiert Sie beim Surfen!
Jetzt aktivieren unter http://www.gmx.net/info

More information about the FreeTDS mailing list