Rance_Hall
asked on
strange changes to values in a db2 table
I have a very disconcerting problem on my hands and I think my db2 database is about to take a long walk off of a short pier.
I use MS Access to look at the tables in an IBM DB2 database
Im using IBM DB2 Version 5.2. Yes I know its ancient, I have a real problem that prevents upgrading.
(just so you have the whole picture, I have custom app that needs to be rewritten to either a linux or windows platform before I can upgrade my db2 version. The custom app runs our production facility and was written on OS/2 --a good choice for its day, but its long overdue to get rid of it. Anyway, I have the most current version of db2 that still has a client package that runs on OS/2, just one version newer and I'd have a linux client, but alas)
MS Access is used to look at the data in this database via linked tables, and for my product_master table (yes, I said product_master, the smallest but singularly most vital table in the entire database.) MS Access can still conduct sql queries on the product_master table, but can not show any of the data in table view, ALL VALUES show as "#DELETED" but they aren't deleted. Again, sql statement against the data appear to work correctly, but opening the entire table in access table view is not possible.
Next some key values in the product master table have been changing without an update statement that can be linked to the change.
Im constantly restorying from backup (ive taken to exporting out the product_master to an ixf as part of the nightly backup, just so a restore is easier in case that comes up.
Our application has some "protected values" in the product_master" table which it will not allow to be changed, in fact the sql statement embedded in the app dont even include all the field names in the update statements on purpose.
yet Ive been having data changing over night.
One day a product is in service, the next it isnt.
One day a product wll have a certain tare weight, and the next it will have something else.
One day a product will have a customers pre-pricing information, and the next day it wont.
(these are just some examples of the problem Ive been seeing.
The only conclusion that I can come to is that the data structure is somehow hosed.
dropping the table and resorting with an ixf file doesnt fix it, it merely slows down the propagation of these changes for a short time (about a month or so)
There isnt a db2 log anywhere in my history that shows an update statement that would have changed my system in the way my system has changed.
This is becoming a critical issue. But I dont know what to do about it.
we had to stop production in the plant for 10 minutes one day waiting for me to get the product master fixed. (just guess how much that 10 minutes cost)
I need some advice, I can't upgrade db2, yet, not till the port to windows is finished.
In the mean time, it appears that my database structure is falling apart around me.
One other clue that may help you, there are two indexes on the product master table, and one of them is in a constant state of statistically needing a reorg.
Not sure why there are two conflicting indexes but when you reorg a table you must specify an index as priority, and no matter which one you pick, the other will fail the next reorgchk
maybe this will help you figure something out, but to me its just another part of the puzzle.
Anybody have any ideas.
I use MS Access to look at the tables in an IBM DB2 database
Im using IBM DB2 Version 5.2. Yes I know its ancient, I have a real problem that prevents upgrading.
(just so you have the whole picture, I have custom app that needs to be rewritten to either a linux or windows platform before I can upgrade my db2 version. The custom app runs our production facility and was written on OS/2 --a good choice for its day, but its long overdue to get rid of it. Anyway, I have the most current version of db2 that still has a client package that runs on OS/2, just one version newer and I'd have a linux client, but alas)
MS Access is used to look at the data in this database via linked tables, and for my product_master table (yes, I said product_master, the smallest but singularly most vital table in the entire database.) MS Access can still conduct sql queries on the product_master table, but can not show any of the data in table view, ALL VALUES show as "#DELETED" but they aren't deleted. Again, sql statement against the data appear to work correctly, but opening the entire table in access table view is not possible.
Next some key values in the product master table have been changing without an update statement that can be linked to the change.
Im constantly restorying from backup (ive taken to exporting out the product_master to an ixf as part of the nightly backup, just so a restore is easier in case that comes up.
Our application has some "protected values" in the product_master" table which it will not allow to be changed, in fact the sql statement embedded in the app dont even include all the field names in the update statements on purpose.
yet Ive been having data changing over night.
One day a product is in service, the next it isnt.
One day a product wll have a certain tare weight, and the next it will have something else.
One day a product will have a customers pre-pricing information, and the next day it wont.
(these are just some examples of the problem Ive been seeing.
The only conclusion that I can come to is that the data structure is somehow hosed.
dropping the table and resorting with an ixf file doesnt fix it, it merely slows down the propagation of these changes for a short time (about a month or so)
There isnt a db2 log anywhere in my history that shows an update statement that would have changed my system in the way my system has changed.
This is becoming a critical issue. But I dont know what to do about it.
we had to stop production in the plant for 10 minutes one day waiting for me to get the product master fixed. (just guess how much that 10 minutes cost)
I need some advice, I can't upgrade db2, yet, not till the port to windows is finished.
In the mean time, it appears that my database structure is falling apart around me.
One other clue that may help you, there are two indexes on the product master table, and one of them is in a constant state of statistically needing a reorg.
Not sure why there are two conflicting indexes but when you reorg a table you must specify an index as priority, and no matter which one you pick, the other will fail the next reorgchk
maybe this will help you figure something out, but to me its just another part of the puzzle.
Anybody have any ideas.
ASKER
how big is the table?
about 1200 records x about 50 fields or so each.
we have box table that tracks the cases that are produced and what lot number is in them, we have box records going back to 1997 or so and the box table is the largest table we have and it doesn't have a single problem.
takes forever for a select statement to parse the data, but thankfully we almost never select from the table, its all insert/delete stuff
I'd have to find a way to script the table definition. cant do it by hand since I have to do it via a script that is executed remotely on a sunday since we dont work on sunday. I didnt write the table definitions and some of the documentation is lacking, so I'm not comfortable dropping the table witout a fool proof way of getting the right field names and setups back.
thats why I went with ixf in the first place.
I could just do an insert into select from type sql statement to do the restore if I could get that far.
As far as recreating the indexes, same for them as goes for the field names, not without a fool proof way of getting the data into a text file or some other way to not leave anything out.
There is no RI, its all done in the app, so its not hard to do what you suggest, just a little scary to do with out a safety net, thats all.
about 1200 records x about 50 fields or so each.
we have box table that tracks the cases that are produced and what lot number is in them, we have box records going back to 1997 or so and the box table is the largest table we have and it doesn't have a single problem.
takes forever for a select statement to parse the data, but thankfully we almost never select from the table, its all insert/delete stuff
I'd have to find a way to script the table definition. cant do it by hand since I have to do it via a script that is executed remotely on a sunday since we dont work on sunday. I didnt write the table definitions and some of the documentation is lacking, so I'm not comfortable dropping the table witout a fool proof way of getting the right field names and setups back.
thats why I went with ixf in the first place.
I could just do an insert into select from type sql statement to do the restore if I could get that far.
As far as recreating the indexes, same for them as goes for the field names, not without a fool proof way of getting the data into a text file or some other way to not leave anything out.
There is no RI, its all done in the app, so its not hard to do what you suggest, just a little scary to do with out a safety net, thats all.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just as a point of clarification - when I said that "it takes forever for a select statement to parse the data" I was referring specifically to our boxes table which has all those boxes going back several years.
select statements against the product_master table are as close to "instantaneous" as you can get on this equipment.
Answers to Lowfats questions:
Access 97 is the version, one of our computers has Access 2000 which does not seem to be vulnerable to the "#DELETED" business, so it may be irrelevant, but it may not be, so I included the information for the sake of completeness
yes the table as a primary key, its the product_code, and they are all unique.
Because of version conflicts with the db2 for windows client, and windows XP Pro then not all of the db2 client package works correctly.
the cli interface is fine, but the graphical one doesnt work reliably enough to use.
Answers to qhp7000's questions:
Yes, I do remember when it started.
it turns out that we had about five product records that were for products that we dont make, never did make, and the codes conflicted with some new products that we were going to make.
I issued a delete from product_master where product_code in ('list') sql statement and got rid of the offending products.
then we added the new products with the codes that were the same as the ones I had just deleted.
At the time there was no indication of any trouble, and everything seemed to work fine, but that was the last "major" change to the product_master table before I began to notice this problem.
No, Im not looking at uncommitted data, our app commits data after each insert, update, and delete type statement, the data needs to be written to disk for our logging function to work correctly.
I dont think Access is my problem with the product master, for most users the user access uses to connect to the data has read_only privileges on the product_master table.
I agree that updates dont occur witout an update statement, this is why im looking at structure compromise, because the changes to the data DO occur, and NO SQL statement was sent to the server that updates THOSE FIELDS, at least not according to db2's own logs.
Im not worried about the access issues specifically, like I said, its included in the discussion for the sake of completeness.
I'm not a proficient db2 admin, this is my first db2 database, however, I am rather proficient with other databases, I was an oracle admin for a couple of years before coming here.
and I run some databases for other clients that are rather small in size.
I guess what Im saying here is that the techincal details about how to tell db2 something are new, but the concepts arent, and I have a 7.2 db2 manual that so far has been very close in its references, and very good about telling me that this feature was included in version X so I know if I have it or dont.
I'm confident that if I can at least get started in the right direction I can figure out whatever you guys have to say on the subject.
select statements against the product_master table are as close to "instantaneous" as you can get on this equipment.
Answers to Lowfats questions:
Access 97 is the version, one of our computers has Access 2000 which does not seem to be vulnerable to the "#DELETED" business, so it may be irrelevant, but it may not be, so I included the information for the sake of completeness
yes the table as a primary key, its the product_code, and they are all unique.
Because of version conflicts with the db2 for windows client, and windows XP Pro then not all of the db2 client package works correctly.
the cli interface is fine, but the graphical one doesnt work reliably enough to use.
Answers to qhp7000's questions:
Yes, I do remember when it started.
it turns out that we had about five product records that were for products that we dont make, never did make, and the codes conflicted with some new products that we were going to make.
I issued a delete from product_master where product_code in ('list') sql statement and got rid of the offending products.
then we added the new products with the codes that were the same as the ones I had just deleted.
At the time there was no indication of any trouble, and everything seemed to work fine, but that was the last "major" change to the product_master table before I began to notice this problem.
No, Im not looking at uncommitted data, our app commits data after each insert, update, and delete type statement, the data needs to be written to disk for our logging function to work correctly.
I dont think Access is my problem with the product master, for most users the user access uses to connect to the data has read_only privileges on the product_master table.
I agree that updates dont occur witout an update statement, this is why im looking at structure compromise, because the changes to the data DO occur, and NO SQL statement was sent to the server that updates THOSE FIELDS, at least not according to db2's own logs.
Im not worried about the access issues specifically, like I said, its included in the discussion for the sake of completeness.
I'm not a proficient db2 admin, this is my first db2 database, however, I am rather proficient with other databases, I was an oracle admin for a couple of years before coming here.
and I run some databases for other clients that are rather small in size.
I guess what Im saying here is that the techincal details about how to tell db2 something are new, but the concepts arent, and I have a 7.2 db2 manual that so far has been very close in its references, and very good about telling me that this feature was included in version X so I know if I have it or dont.
I'm confident that if I can at least get started in the right direction I can figure out whatever you guys have to say on the subject.
I'm just guessing here, but when you made the delete statements and then ran the update statements, did all applications disconnect from the database first?
It may be that if the applications did not disconnect BEFORE you made the changes, those applications might have old data in their variables, and these old variables are what is being sent back to the db. This may explain how you end up with unexpected data in the tables.
Regarding the db2 logs, I don't think you'll see the update statements in the logs unless perhaps you are running at diaglevel 4, perhaps in version 5 it was different.
>>Because of version conflicts with the db2 for windows client, and windows XP Pro then not all of the db2 client package works correctly.
What does the above statement mean? If you have client mismatch against the database, or running a client version which is not supported vs the db version, then all bets are off, you cant predict with any degree of accuracy what is going to happen. Either downgrade the client version or upgrade the db version.
It may be that if the applications did not disconnect BEFORE you made the changes, those applications might have old data in their variables, and these old variables are what is being sent back to the db. This may explain how you end up with unexpected data in the tables.
Regarding the db2 logs, I don't think you'll see the update statements in the logs unless perhaps you are running at diaglevel 4, perhaps in version 5 it was different.
>>Because of version conflicts with the db2 for windows client, and windows XP Pro then not all of the db2 client package works correctly.
What does the above statement mean? If you have client mismatch against the database, or running a client version which is not supported vs the db version, then all bets are off, you cant predict with any degree of accuracy what is going to happen. Either downgrade the client version or upgrade the db version.
ASKER
as far as the apps disconnecting before running the delete / insert statements, NO they didnt disconnect first, if you mean that they didnt have a connection to the database open. If you mean that they had a lock on the product_master table because there was a product related window open, then yes, there was no active link against the product master table at the time the statements were run.
by version conflicts I mean that db2 5.2 client for windows is for windows NT
windows has changed alot since then, and even though the client for windows NT works on xp pro, not all of it works, for example when you try to open a command center window you get a windows system error and the application fails to start.
But, the command line processor works flawlessly, thats what im talking about when I mention version conflicts.
IM NOT talking about using a client package that doesnt match my database version. It's all 5.2
by version conflicts I mean that db2 5.2 client for windows is for windows NT
windows has changed alot since then, and even though the client for windows NT works on xp pro, not all of it works, for example when you try to open a command center window you get a windows system error and the application fails to start.
But, the command line processor works flawlessly, thats what im talking about when I mention version conflicts.
IM NOT talking about using a client package that doesnt match my database version. It's all 5.2
Hi Rance_Hall,
I agree with ghp7000's suggestion to put a couple of triggers on the table and simply record the changes.
You stated that the problem first occurred when you noticed phantom products in the table. That's probably due to a new primary key being inserted into the table instead of an update, but that's not a guarantee.
I'd start with an insert trigger on the table's primary and/or product key, recording the timestamp and primary key to another table whenever a key is added or changed.
Kent
I agree with ghp7000's suggestion to put a couple of triggers on the table and simply record the changes.
You stated that the problem first occurred when you noticed phantom products in the table. That's probably due to a new primary key being inserted into the table instead of an update, but that's not a guarantee.
I'd start with an insert trigger on the table's primary and/or product key, recording the timestamp and primary key to another table whenever a key is added or changed.
Kent
no no thats not what I mean. I dont know how your app is coded, but consider this scenario:
The application reads a control table for example, then stores the values retreived into memory. These memorized variables are used in different parts of the application, usually this is done to avoid re reading the same table over and over again. If your product_master table is such a control table, then the values stored in memory by the application dont correspond any more to the values in the table. Now, if these erroneous values arethen used in insert/update statements....., well you can see the problem.
Therefore, when you make changes to the data in the control table, all ther applications have to disconnect beforehand and then re connect in order to read the new values.
The application reads a control table for example, then stores the values retreived into memory. These memorized variables are used in different parts of the application, usually this is done to avoid re reading the same table over and over again. If your product_master table is such a control table, then the values stored in memory by the application dont correspond any more to the values in the table. Now, if these erroneous values arethen used in insert/update statements....., well you can see the problem.
Therefore, when you make changes to the data in the control table, all ther applications have to disconnect beforehand and then re connect in order to read the new values.
ASKER
I want to take this opportunity to thank all of you for your ideas.
Ive kept all this in mind while discussing my situation with the programmer for this custom program.
Its out database, but its his program kind of a thing
I apologize for leaving this open so long
since youve all given food for thought im going to follow the suggestion of the cleanup volunteer and split the points between you
Thanks again.
Ive kept all this in mind while discussing my situation with the programmer for this custom program.
Its out database, but its his program kind of a thing
I apologize for leaving this open so long
since youve all given food for thought im going to follow the suggestion of the cleanup volunteer and split the points between you
Thanks again.
Wow. What a mess....
How big is the table in question? If it's not too large, I'd make a copy of the table on the database so that if/when you have more data issues you simply reload it from the other table. That's bound to be faster than restoring a backup.
Older versions of DB2 had issues that I don't even remember (or never knew). I don't know if there's a DB2 bug at 5.2 that could be causing this.
On the other hand, I believe that I'd do a complete, ground up rebuild of the table. Not a restore, a rebuild.
-- Copy the table to another table.
-- Clean up any data issues.
-- Drop the original table.
-- Insert the rows in the backup table.
-- Create the indexes.
-- Define any RI that needs to be reinstated.
Good Luck!
Kent