|
View previous topic :: View next topic
|
| Author |
Message |
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
Our site uses views(which are updatable) for all DML operations on a table. In one of the test regions(all DDLs are same as production) the view is not retrieving the rows for DML operations through WHERE clause.(as in WHERE <field_name> = value). The query returns SQLCODE = 100
However, if I issue
SELECT * FROM <table_name> FETCH FIRST 10 ROWS ONLY;
it retrieves 10 rows.
It leads me to some conclusion that the table is not allowing indexed access.After some head scratching we found that it allows operations through translation and other workarounds.(as in WHERE DEC<field_name> = value). But the basic question remains why this is happening.
Any suggestions/pointers to rectify the issue would be great. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| Quote: |
It leads me to some conclusion that the table is not allowing indexed access.
|
the conclusion is just plain wrong, indexing has nothing to do with what data is being retrieved
indexing for a relational database is just an additional option ( for access path, performance, uniqueness and friends )
usually for small test cases ( few hundredths rows ) nobody generally cares about defining indexes ( apart those needed for uniqueness and referential integrity )
and you can be sure that the data returned will be consistent across any index structure which might be built later for performance reasons
the logic dictates that if one approach works in one environment it must work in all the clones of that environment
if the <thing> work in production then the environment where You are testing is not an exact clone!
| Quote: |
But the basic question remains why this is happening.
Any suggestions/pointers to rectify the issue would be great. |
review and check that your assertion all DDLs are same as production really holds |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
When I say that DDLs are same I mean that the structure of database objects is same. Obviously size of pools(buffer,sort rid...) and DASD space allocations isn't same as production.
All the DDLs are indeed same as production and this has been verified and a lot of DBAs are already puzzled over this.I put this post so that if anybody had overcome such an issue before, their suggestions would help me in resolving this issue quicker.
We all know that something somewhere is indeed wrong and that's why it isn't working.Just wanted to know where and what to look for from a collaborative community. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
| Quote: |
| When I say that DDLs are same I mean that the structure of database objects is same. Obviously size of pools(buffer,sort rid...) and DASD space allocations isn't same as production. |
stupid remark, it is clear that I was referring only to tha table' s and views definitions
taking You example ...
if in some environment You use WHERE somecolumn = somevalue
and somewher else You have to use WHERE DEC(somecolumn) = somevalue
as per publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc.sqlref/src/tpc/db2z_bif_decimal.htm
even the most groggy DBA should be able to understand that something is different
| Quote: |
| .Just wanted to know where and what to look for from a collaborative community. |
a community is collaborative if the questions asked are supported by evidence
otherwise we can only shoot in the dark and guess 
so You have no reason for being pissed  |
|
| Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
| How about running RUNSTATS for tables and indexes. |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| Quote: |
the conclusion is just plain wrong, indexing has nothing to do with what data is being retrieved
|
Sorry I missed to post some info.
This issue occurs,whenever I use WHERE, on a column that has an unique index defined on it .
When I use WHERE on a column which does not have any unique index defined on it the query works.
enrico, hopefully my conclusion stands now.  |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| Quote: |
| You have no reason for being pissed |
I am not pissed. I have a huge task at hand and can't afford to digress myself by over interpreting or drawing extrapolated conclusions(even if you call me stupid).
hopefully this off the topic discussion is rested. |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| Quote: |
How about running RUNSTATS for tables and indexes.
|
Is it a guess or if you can explain how will this resolve SQLCODE =100 it would be great. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| as in WHERE DEC<field_name> = value). But the basic question remains why this is happening. |
if the 'workaround' is a CAST (DECIMAL scalar function)
and
the DDL for the different environments is the same
maybe your literal is different. |
|
| Back to top |
|
|
Dsingh29
Active User
Joined: 16 Dec 2008
Posts: 132
Location: IBM
|
|
|
|
| Was going through some articles regarding your problem came through two utilities Reorg and Runstats which might resolve issue. This might not be a sure shot solution but still worth giving a try. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Do these 10 rows
| 1 wrote: |
if I issue
SELECT * FROM <table_name> FETCH FIRST 10 ROWS ONLY;
it retrieves 10 rows. |
have any value which satisfies the "<field_name> = value" in your where clause?
| 2 wrote: |
| view is not retrieving the rows for DML operations through WHERE clause.(as in WHERE <field_name> = value). |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
most likely there is a problem with the index :
- is it RBPD ?
- was it RBPD and was it forced
- is it a unique index, someone did a load enforce no (with duplicates) : the rebuild index fails and then nobody cares.
- or it became corrupted is some mysterious way
when you do a select without where => DB2 does a tablescan
when you use a column funtion like dec() => DB2 does a tablescan
do or let someone do a REBUILD index:
REBUILD INDEX (creator.indexname) |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
Anuj
That's a good question.The interesting part is that let's say I use FETCH FIRST 10 ROWS and I get the 10 rows.(one row shown below for illustration).
col1 col2
---- -----
111 ABC
.
.
.
Then if I issue
SELECT * FROM <table_name> WHERE col1 = 111 it returns SQLCODE = 100
where as
SELECT * FROM <table_name> WHERE DEC(col1) = 111 returns the row successfully. Remember that col1 has unique index on it.(as this issue is happening only for indexed columns) |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Trust me : your index is corrupt , probably empty because of a failed rebuild.
Thus if DB2 tries to use that index , it doesn't find an entry with that field = ? and returns +100 |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
Thanks, I just try to see if we are not missing the obvious, as I've learned hard way that the things which are easy to see are also easy to miss!
I just read about RBPD and REORG and I believe, GuyC has the solution for your problem. Please check if that works for you. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rocky,
| Quote: |
| the view is not retrieving the rows |
Are you accessing the views through a program ?
| Quote: |
However, if I issue
SELECT * FROM <table_name> FETCH FIRST 10 ROWS ONLY;
it retrieves 10 rows. |
I guess you gave this SELECT in SPUFI.
Did you get any rows when querying using VIEW in SPUFI. If you have got rows, check the QUALIFIER for the package, it may access different database, where the table might be empty.
Thanks,
Sushanth |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
GuyC
You have echoed what I have been thinking of recommending this to the DBAs all this while.(don't have a REBUILD INDEX access) Not sure whether they have tried this or not. I am going to ask them whether they have tried this.
Yes it was RBPD and was FORCED. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Well then probably there are duplicates in the table and the rebuild will fail again.
get ready to somehow find and resolve duplicates |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| rocky_balboa wrote: |
| Yes it was RBPD and was FORCED. |
A mechanic tells you your brakes are broken.
You tell him: "don't care, just make it so I can drive."
and then people are surprised the car doesn't behave as they expect. |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| Quote: |
"don't care, just make it so I can drive."
and then people are surprised the car doesn't behave as they expect. |
 .. Agree, but a lot of time the coders do not have access to the tools to make the car drivable inspite of the knowledge that they have..... and you are coerced into driving a broken car.....hence such issues... |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| I'm not blaming the coders, DBA should explain the risk of forcing an index. |
|
| Back to top |
|
|
|
|
