How can I catch Sybase bcp errors reliably?

Question

We're using named pipes with Sybase bcp so that we can compress output on-the-fly.

This is a paraphrase of the error handling idiom, some error checking in the non-bcp parts of the script has been removed to shorten the example.

while :
do
    {
        rm -f $fifo
        mkfifo $fifo
        cat $fifo &
        CatPid=$!

        bcp $db.$owner.$table out $fifo -c $db_creds >$log 2>&1
        grep -qi deadlock $log || break

        # Must have been a deadlock, clean up.
        kill $CatPid
    } > $output
done

Basically, if the word 'deadlock' appears in bcp output messages, we try again.

Two questions

1. Does this approach look reasonable?
2. What other bcp errors than deadlock might we need to worry about?

I'm specifically interested in handling of intermittent bcp errors - no need to worry about table not found, permissioning, credentials wrong, etc. - they shouldn't happen in this case. (Having said that, when a permissioning error did once come up with this code the fact that the error was not caught and reported caused much consternation.)

We use a compound statement so that we can insert headers and footers around the bcp data before the compression, but I've omitted that to simplify the example.

Answer 1

Is that really going to do what you want? My understanding of the bcp commandline tool is that there is no transaction - ie. if you are loading M rows, but inserting row N fails for any reason (constraints etc.), the first N-1 rows have been inserted. So restarting the whole file isn't a great idea.

You can use the -m X option to allow bcp to carry on in the face of up to X errors, and then try to identify which rows failed to insert and retry them.

You could also look into Michael Peppler's Sybase::BCP Perl module, but our investigations suggest that it may have issues with ASE 15.

Answer 2

I had idea use named pipe for bcp out and compress data from Sybase ASE to file and then load into Sybase IQ using LOAD TABLE statement. Unfortunately there was a big performance lost. LOAD TABLE from named pipe was 10x slower than LOAD TABLE from file on HP-UX :-( I vote for implementing simple compress alghoritm directly into OC utils (bcp,isql).

Answer 3

So all you need is just reliable fail fast bcp. Bcp for some of Sybase versions has a command argument controlling max error-count. 1) if you set error count = 1, then it will fail more reliably. 2) The problem then boils down to trapping the exit code of bcp process, launched on background with &. I dont know what shell syntax should be used for this exactly, but there might be some common shell technique for this.