Update Transformed to WHERE Clause on All Columns
-
My customer is confused (as are we) because this same SQL "works" in Development but not in Production. SQL Server accesses DB2 for z/OS via ODBC and Shadow (Neon) Server.
The difference we see is this:
In Dev, simple update statement like UPDATE table SET COL02 = 'x' WHERE COL01 = 'y'
gets executed as is.
In Prod, the update statement gets "changed" to
WHERE COL01 = 'y' AND COL02 = 'm' AND COL03 = 'n' AND COL04 = 'o' AND ....
for EVERY column in the table.
At first, I figured that there was no unique index or primary key, but the table has all that.
Who is doing the DB2 Catalog query to fetch all columns and changing the Update?
Is there a parameter in SQL Server for that?
-
david my knee jerk reaction is the same as yours; i'd be checking whether there is there a primary key or not on the base table.
i've seen query builders reference every column when the table has no primary key, and also when the internally typed dataset has no primary key, regardless of the real table having one or not.
do you have access tot he actual program code?
you are sure the application code is the same, right? could it be the code applied on dev has a PK on a typed dataset, but an older/different codebase for the application is different?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell,
I'm not familiar with "internally typed dataset." How do I find out what it is and if it's correct?
Dave
-
well here's an example from visual studio:
the dataset is used to strongly type the column data types, and a query is used to fill the dataset.
the easiest way to add a table to a dataset is to drag and drop, and Visual Studio builds the dataset to mirror the table that was dragged and dropped.
but you can build a table manually, or change the table once it's int he dataset...
so a table in this dataset might not have a primary key, even though the real table does.
the dataadapter build all the select/update/delete statements based on the design in the dataset....so no PK means it needs to reference every column in order to find a unique row.
if the dataset has a primary key, it knows the select/update/delete statements need to simply say WHERE PK = value to identify one row uniquely.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply