bcp generating duplicate rows?

[Mike Gagnon]

I am using bcp to copy data from a ASE 11.9.2 db to my ASA 8.0.0.2213 db.
When I try to add the indexes back to the tables, some of the unique indexes
fail due to duplicate data. Since the source table has the same unique
index, I would find it most disturbing if there are duplicate rows in there
somehow. Also, adding one particular non-unique index generates the
following error message:

*** ERROR *** Assertion failed: 102300 (8.0.0.2213)
File associated with given page id is invalid or not open

I have to shutdown the ASA server, but no apparant corruption is caused
(validate returns no errors).

Here are the steps I am using:

1. In Unix:
${SYBASE}/bin/bcp DTO1..dm_sysobject_s out
tmp/dm_sysobject_s.txt -c -t'|' -r '\r\n' -Uuser -Ppwd -Ssrvr

2. I then ftp the data to my local PC.

3. In ISQL for ASA:

LOAD TABLE dbo.dm_sysobject_s
FROM 'C:\\Data\\dm_sysobject_s.txt'
DELIMITED BY '|' FORMAT ASCII QUOTES OFF STRIP ON

I have re-create the db from scratch JIC there was some form of corruption
that validation doesn't catch.

Thanks,
Mike

[Mike Gagnon]

Heh, I already tried that for the smaller tables. Unfortunately, the table
in question has about 2.7 million rows, and is very wide as well (row size
is about 800 bytes). The size of the bcp file is 1.6GB. After letting it run
for several hours (over 8), I ended up killing the process. By removing the
indexes, the LOAD TABLE finishes in just a few minutes, surprisingly.

There are users actively working on the system during the bcp process, which
unfortunately isn't preventable. Could this be the cause for duplicates?

Thanks,
Mike

"Scott Kruer" <skruer@SPAM_SPAM_GO_AWAY_sybase.com> wrote in message
news:Xns92027758B91B...@199.93.177.77...
> I am sure someone will have a better answer, but just in case not... One
> other way might be to create 'proxy' tables in your ASA database pointing
> to your ASE database and then 'select into' your ASA 'real' tables from
> the 'proxy' tables. Then blow away the proxy tables...
>
> "Mike Gagnon" <mg...@dcx.com> wrote in
> news:E#a6U5d8...@forums.sybase.com:

[Mike Gagnon]

Argh! Can you say "embedded newlines." Using bcp -c works great for the
tables that don't have them, but I wasn't aware that the comments field
allowed multi-line comments. Nice of someone not to mention that fact ;)
Chances are, I can use bcp without the -c option and it will work just fine,
or alternately, use a non-\r\n row delimiter. Sorry for the red herring.

Mike

[nick.elson@sybase.com Nick Elson]

A few things to review/consider, to make certain you haven't
missed a step, a feature misalignment or haven't hit a (possibly BCP) bug
(see comments in-line below)

> When I try to add the indexes back to the tables, some of the unique
indexes
> fail due to duplicate data. Since the source table has the same unique
> index, I would find it most disturbing if there are duplicate rows in
there
> somehow.

Make sure the ASE 'unique' index is not a clustered index
(with or without the allow_dup_row option). If that is a
clustered index grandfather data may be present that allows
duplicate pkeys. On the same measure triple check the pkey
or unique key columns match the ASA definition. [also consider
if null column values in your key data plays a role]

>Also, adding one particular non-unique index generates the
> following error message:
>
> *** ERROR *** Assertion failed: 102300 (8.0.0.2213)
> File associated with given page id is invalid or not open
>
> I have to shutdown the ASA server, but no apparant corruption is caused
> (validate returns no errors).

The Assertion is indicative of a serious problem. This may
not be a BCP/load table issue. It could be a corruption on
the freelist inside the database. It could also be due to a
index corruption that hasn't showed up for other reasons
[you could already have a duplicate row].

Because of this possibility you should see what happens after
rebuilding the database [which gives you a clean free list and
a sanity check on everything else].

> 1. In Unix:
> ${SYBASE}/bin/bcp DTO1..dm_sysobject_s out
> tmp/dm_sysobject_s.txt -c -t'|' -r '\r\n' -Uuser -Ppwd -Ssrvr

The cmdline is okay. Switching to -t ',' is probably a little bit
more compatable with ASA

Make sure you have the highest level EBF for Open Client 11.1.1.
O/C 11.1.x was a complete port of ISQL and BCP off of the
ancient DB-Library API to the CT-Library API [so today you
will often see the 11.1/12.x versions of these utils called CT-ISQL
and CT-BCP to differentiate them].

Unfortunately this was a significant port for BCP and a number of
problems surfaced. The version of O/C shipped with ASE 11.9.2
was pretty stable for most client applications but a major BCP clean-up
was not completed until a number of EBFs following that release.

So the short story is: EBF up O/C 11.1.1 or switch to a much newer
release of O/C (12.5.x or a few EBFs off of 12.0.x)

ALSO - Does BCP IN work with this data file when ran against
the 11.9.2 server? If not, there is likely a bug in the output process.

> 2. I then ftp the data to my local PC.

Make sure you are in binary mode (ie. BIN) when transfering that.
[this should only cause the load to fail but it could be loose-end]

> 3. In ISQL for ASA:
>
> LOAD TABLE dbo.dm_sysobject_s

> . . . FORMAT ASCII . . .

Have you not had success using FORMAT BCP?

and 4 --

Since ver. 7.0.2 I have had success using BCP IN
with some problematic data loads. You need to
set up a DSEDIT definition for ASA8 that matches
your DBN (DatabaseName) for the Server (aka
ServiceName) but that allows a slow BCP to operate.

That way you may be able to side step a difficult
'data content' issue.

[Scott Kruer]

I am sure someone will have a better answer, but just in case not... One
other way might be to create 'proxy' tables in your ASA database pointing
to your ASE database and then 'select into' your ASA 'real' tables from
the 'proxy' tables. Then blow away the proxy tables...

"Mike Gagnon" <mg...@dcx.com> wrote in
news:E#a6U5d8...@forums.sybase.com:

> I am using bcp to copy data from a ASE 11.9.2 db to my ASA 8.0.0.2213

[David Kerber]

Yes, I've run into that problem, too, with exporting to .txt files. I
never realized bcp would trip over that, too.

--

Dave Kerber
Edit the return address by removing the ns_ before e-mailing

This message was entirely written using recycled electrons.

REAL programmers write self-modifying code, leading to:
Why there is ALWAYS one more bug (courtesy of O.D. Williams):
1. Debuging code is always much harder than writing it.
2. Therefore, if your code is as clever as you are capable of making
it, you are, by definition, inadequate to debug it.