Select * from <tablename> returns wrong number of rows. Why?
subra_murali@ibm 2000000GHY Visits (10785)
Hello db2 DBAs
I recently had an interesting situation, which seemed on the face of it highly unusual. So I wanted to share with this group.
Then he ran the command
However this returned just one row!!! WHY ? db2 has encountered a bug or what?
My thought processes in debugging this was as follows:
1. Are we querying the right table under the right schema?
3. Any Row or column based access control in place against this table ?
4. Since the behavior was easily reproducible, could it be an optimizer issue?
That showed an interesting tidbit. The table definition had an "informational constraint" as follows:
Since this constraint was defined as "NOT ENFORCED" db2 does not enforce uniquness. It expects the end user to make sure the column in question does not have any duplicate values. This way db2 can avoid creating an index and use the index to return the actual values. This is ideal in data warehouse environment where you end up loading millions or billions of rows into the table. Time savings can be substantial.
However in this case cust had all the duplicate values for that column. So db2 optimizer was "assuming" this column value to be unique and returned just one row satisfying that condition.
MORAL of the story: Beware of the way db2 would interpret the data when they contain informational constraints and not enforced. You can shoot your foot if not careful.
The work around was to add the option DISABLE OPTIMIZATION for that constraint.
That way db2 will not use that constraint for query optimization and return correct results.
Here is the link to our db2 Info center which documents this behavior.
Some snippets from that page.
"Informational RI (referential integrity) constraints are used to optimize query performance, the incremental processing of REFRESH IMMEDIATE MQT, and staging tables. Query results, MQT data, and staging tables might be incorrect if informational constraints are violated."
Specify an informational constraint only (emphasis added..) if the table data is independently known to conform to the constraint. Because the DB2® database manager does not enforce uniqueness for these constraints, if the table data violates the not-enforced constraint, incorrect results can occur. You cannot reference not-enforced primary key constraints in any enforced referential integrity definitions.
I also came across this blog post during my research, which confirmed our analysis
Lesson well learned for both customer and me!!
Hope you enjoyed it!! Feel free to leave your comments in this page. I very much appreciate it.