I am having a very strange issue and looking for some ideas on next
troubleshooting steps.

Summary:
running a delete statement that matches multiple rows fails or
partially fails to delete those rows.

Description:
I am running Sybase ASE 15.0.0 and Solaris 10 with multiple zones. I
BCP 1.5 million rows of
data into a ProductStage table before running the following two
statements -

DELETE Product
FROM Product,
ProductStage (INDEX XPKProductStage)
WHERE Product.BatchNumber LIKE @LikeArgument
AND Product.BatchNumber = ProductStage.BatchNumber
AND Product.Month = ProductStage.Month
AND Product.Agency = ProductStage.Agency


INSERT INTO Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
SELECT BatchNumber,Month,Agency,Address,CRP,CAM,WAL
FROM ProductStage (INDEX XPKProductStage)
WHERE BatchNumber LIKE @LikeArgument

Note: batch numbers are three characters followed by three digits
like the following XFD001, XFD002, XFD003... so
a specific example of the run may look like this -

DELETE Product
FROM Product,
ProductStage (INDEX XPKProductStage)
WHERE Product.BatchNumber LIKE 'XFD%'
AND Product.BatchNumber = ProductStage.BatchNumber
AND Product.Month = ProductStage.Month
AND Product.Agency = ProductStage.Agency


INSERT INTO Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
SELECT ProductNumber,Month,Agency,Address,CRP,CAM,WAL
FROM ProductStage (INDEX XPKProductStage)
WHERE ProductNumber LIKE 'XFD%'

There are around 1500 different three character prefixes.

I can usually run this process about 10 times each time with similar
if not identical data without an issue. The process being
BCPing 1.5 million records into the ProductStage and then running
the delete and insert statements for each 'Batch'.
There are usually around 1500 batches with about 1000 rows each.
Eventually I will get the following error -
"Attempt to insert duplicate key row in object 'Product' with unique
index 'XPKProduct'". First how is that possible given
the previous delete and insert statements? The delete should make
sure that there are no duplicated records in the Cusomter table.

After getting this error once, the loads will always return the
duplicate index error for the same Batch. I can get it to work again
by BCPing out the data that is in Product and ProductStage, dropping
and recreating the tables, BCPing the data back into those tables
The next time I run the staging process it will run without issue
and work for around another 10 trys.

I did some more research and by running the delete and insert
manually for a given batch (LIKE 'XFD%') after getting the
"Attempt to insert duplicate key row in object 'Product' with unique
index 'XPKProduct'". error I found that sometimes it fails to
delete or sometime it will partially delete the rows in Product that
match the WHERE Product.BatchNumber LIKE 'XFD%'. In one particular
case there was 879 rows in bacth "LIKE 'XFD%'" running the delete
statement using isql would return (734 rows affected). I would then
run
the delete statement again and it would report (145 rows affected).
It would take two runs of the delete statement to delete the full
879 rows it should have!

I ran DBCC on the involved tables and they check out fine. I am at a
loss and not sure where to go from here. Any ideas?

A few questions (I'm sure you've thought of...):

1) Do you have rowcount set?
2) Are you checking @@error / @@rowcount after the DELETE operation?
3) Do you have a DELETE trigger which could be interfering?
4) The DELETE & INSERT ... I assume they are in the same batch and you
are using implicit tx's (i.e. auto-commit)?


Keith




On Jun 3, 9:19*pm, alacr...@gmail.com wrote:
> *I am having a very strange issue and looking for some ideas on next
> troubleshooting steps.
>
> *Summary:
> *running a delete statement that matches multiple rows fails or
> partially fails to delete those rows.
>
> *Description:
> *I am running Sybase ASE 15.0.0 and Solaris 10 with multiple zones. I
> BCP 1.5 million rows of
> *data into a ProductStage table before running the following two
> statements -
>
> * DELETE Product
> * FROM * Product,
> * * * * *ProductStage (INDEX XPKProductStage)
> * WHERE *Product.BatchNumber LIKE @LikeArgument
> * AND * *Product.BatchNumber = ProductStage.BatchNumber
> * AND * *Product.Month = ProductStage.Month
> * AND * *Product.Agency *= ProductStage.Agency
>
> * INSERT INTO Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
> * SELECT BatchNumber,Month,Agency,Address,CRP,CAM,WAL
> * FROM *ProductStage (INDEX XPKProductStage)
> * WHERE BatchNumber LIKE @LikeArgument
>
> * Note: batch numbers are three characters followed by three digits
> like the following XFD001, XFD002, XFD003... so
> * a specific example of the run may look like this -
>
> * DELETE Product
> * FROM * Product,
> * * * * *ProductStage (INDEX XPKProductStage)
> * WHERE *Product.BatchNumber LIKE 'XFD%'
> * AND * *Product.BatchNumber = ProductStage.BatchNumber
> * AND * *Product.Month = ProductStage.Month
> * AND * *Product.Agency *= ProductStage.Agency
>
> * INSERT INTO Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
> * SELECT ProductNumber,Month,Agency,Address,CRP,CAM,WAL
> * FROM *ProductStage (INDEX XPKProductStage)
> * WHERE ProductNumber LIKE 'XFD%'
>
> * There are around 1500 different three character prefixes.
>
> * I can usually run this process about 10 times each time with similar
> if not identical data without an issue. The process being
> * BCPing 1.5 million records into the ProductStage and then running
> the delete and insert statements for each 'Batch'.
> * There are usually around 1500 batches with about 1000 rows each.
> Eventually I will get the following error -
> * "Attempt to insert duplicate key row in object 'Product' with unique
> index 'XPKProduct'". First how is that possible given
> * the previous delete and insert statements? The delete should make
> sure that there are no duplicated records in the Cusomter table.
>
> * After getting this error once, the loads will always return the
> duplicate index error for the same Batch. I can get it to work again
> * by BCPing out the data that is in Product and ProductStage, dropping
> and recreating the tables, BCPing the data back into those tables
> * The next time I run the staging process it will run without issue
> and work for around another 10 trys.
>
> * I did some more research and by running the delete and insert
> manually for a given batch (LIKE 'XFD%') after getting the
> * "Attempt to insert duplicate key row in object 'Product' with unique
> index 'XPKProduct'". error I found that sometimes it fails to
> * delete or sometime it will partially delete the rows in Product that
> match the WHERE *Product.BatchNumber LIKE 'XFD%'. In one particular
> * case there was 879 rows in bacth "LIKE 'XFD%'" running the delete
> statement using isql would return (734 rows affected). I would then
> run
> * the delete statement again and it would report (145 rows affected).
> It would take two runs of the delete statement to delete the full
> * 879 rows it should have!
>
> * I ran DBCC on the involved tables and they check out fine. I am at a
> loss and not sure where to go from here. Any ideas?

Instead of dialect, have you considered Standard SQL and using
ISO-11179 naming conventions? For example MONTH is a reserved word,
agency could be anything from agency_size, agency_type, agency_name,
etc.

DELETE FROM Products
WHERE Products.batch_nbr LIKE @like_argument
AND EXISTS
(SELECT *
FROM ProductStages AS S
WHERE Product.batch_nbr = S.batch_nbr
AND Product.something_month = S.something_month
AND Product.agency_something = S.agency_something);

This will avoid a duplicate row problem you will find with the dialect
syntax.

Thank you for your reply.

> 1) Do you have rowcount set?

I have verified that rowcount is not set prior to running these
commands

> 2) Are you checking @@error / @@rowcount after the DELETE operation?

I am checking @@error / @@rowcount. No error is being reported.

> 3) Do you have a DELETE trigger which could be interfering?

No delete triggers.

> 4) The DELETE & INSERT ... I assume they are in the same batch and you
> are using implicit tx's (i.e. auto-commit)?

These commands are run using implicit transactions.


Since I made the first post I tried changing the locking scheme from
page to row level and I am no longer seeing this issue. Not sure why.

On Jun 4, 8:11*am, Keith <keith.wing...@ronin-capital.com> wrote:
> A few questions (I'm sure you've thought of...):
>
> 1) Do you have rowcount set?
> 2) Are you checking @@error / @@rowcount after the DELETE operation?
> 3) Do you have a DELETE trigger which could be interfering?
> 4) The DELETE & INSERT ... I assume they are in the same batch and you
> are using implicit tx's (i.e. auto-commit)?
>
> Keith
>
> On Jun 3, 9:19*pm, alacr...@gmail.com wrote:
>
> > *I am having a very strange issue and looking for some ideas on next
> > troubleshooting steps.
>
> > *Summary:
> > *running a delete statement that matches multiple rows fails or
> > partially fails to delete those rows.
>
> > *Description:
> > *I am running Sybase ASE 15.0.0 and Solaris 10 with multiple zones. I
> > BCP 1.5 million rows of
> > *data into a ProductStage table before running the following two
> > statements -
>
> > * DELETE Product
> > * FROM * Product,
> > * * * * *ProductStage (INDEX XPKProductStage)
> > * WHERE *Product.BatchNumber LIKE @LikeArgument
> > * AND * *Product.BatchNumber = ProductStage.BatchNumber
> > * AND * *Product.Month = ProductStage.Month
> > * AND * *Product.Agency *= ProductStage.Agency
>
> > * INSERT INTO Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
> > * SELECT BatchNumber,Month,Agency,Address,CRP,CAM,WAL
> > * FROM *ProductStage (INDEX XPKProductStage)
> > * WHERE BatchNumber LIKE @LikeArgument
>
> > * Note: batch numbers are three characters followed by three digits
> > like the following XFD001, XFD002, XFD003... so
> > * a specific example of the run may look like this -
>
> > * DELETE Product
> > * FROM * Product,
> > * * * * *ProductStage (INDEX XPKProductStage)
> > * WHERE *Product.BatchNumber LIKE 'XFD%'
> > * AND * *Product.BatchNumber = ProductStage.BatchNumber
> > * AND * *Product.Month = ProductStage.Month
> > * AND * *Product.Agency *= ProductStage.Agency
>
> > * INSERT INTO Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
> > * SELECT ProductNumber,Month,Agency,Address,CRP,CAM,WAL
> > * FROM *ProductStage (INDEX XPKProductStage)
> > * WHERE ProductNumber LIKE 'XFD%'
>
> > * There are around 1500 different three character prefixes.
>
> > * I can usually run this process about 10 times each time with similar
> > if not identical data without an issue. The process being
> > * BCPing 1.5 million records into the ProductStage and then running
> > the delete and insert statements for each 'Batch'.
> > * There are usually around 1500 batches with about 1000 rows each.
> > Eventually I will get the following error -
> > * "Attempt to insert duplicate key row in object 'Product' with unique
> > index 'XPKProduct'". First how is that possible given
> > * the previous delete and insert statements? The delete should make
> > sure that there are no duplicated records in the Cusomter table.
>
> > * After getting this error once, the loads will always return the
> > duplicate index error for the same Batch. I can get it to work again
> > * by BCPing out the data that is in Product and ProductStage, dropping
> > and recreating the tables, BCPing the data back into those tables
> > * The next time I run the staging process it will run without issue
> > and work for around another 10 trys.
>
> > * I did some more research and by running the delete and insert
> > manually for a given batch (LIKE 'XFD%') after getting the
> > * "Attempt to insert duplicate key row in object 'Product' with unique
> > index 'XPKProduct'". error I found that sometimes it fails to
> > * delete or sometime it will partially delete the rows in Product that
> > match the WHERE *Product.BatchNumber LIKE 'XFD%'. In one particular
> > * case there was 879 rows in bacth "LIKE 'XFD%'" running the delete
> > statement using isql would return (734 rows affected). I would then
> > run
> > * the delete statement again and it would report (145 rows affected).
> > It would take two runs of the delete statement to delete the full
> > * 879 rows it should have!
>
> > * I ran DBCC on the involved tables and they check out fine. I am at a
> > loss and not sure where to go from here. Any ideas?