IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Table not allowing indexed DML operations


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 11:55 am
Reply with quote

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jun 24, 2011 12:28 pm
Reply with quote

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
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 12:55 pm
Reply with 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.

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
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Jun 24, 2011 1:06 pm
Reply with quote

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 icon_cool.gif
so You have no reason for being pissed icon_evil.gif
Back to top
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Fri Jun 24, 2011 1:06 pm
Reply with quote

How about running RUNSTATS for tables and indexes.
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 1:15 pm
Reply with quote

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. icon_smile.gif
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 1:26 pm
Reply with quote

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).

icon_smile.gif hopefully this off the topic discussion is rested.
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 1:29 pm
Reply with quote

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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Jun 24, 2011 1:44 pm
Reply with quote

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
View user's profile Send private message
Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Fri Jun 24, 2011 1:51 pm
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri Jun 24, 2011 1:52 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 2:02 pm
Reply with quote

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
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 2:16 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 2:24 pm
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri Jun 24, 2011 2:26 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Jun 24, 2011 2:26 pm
Reply with quote

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
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 2:28 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 2:45 pm
Reply with quote

Well then probably there are duplicates in the table and the rebuild will fail again.
get ready to somehow find and resolve duplicates icon_smile.gif
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 3:22 pm
Reply with quote

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
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Fri Jun 24, 2011 4:23 pm
Reply with quote

Quote:
"don't care, just make it so I can drive."
and then people are surprised the car doesn't behave as they expect.


icon_biggrin.gif .. 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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jun 24, 2011 4:38 pm
Reply with quote

I'm not blaming the coders, DBA should explain the risk of forcing an index.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top