Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Is it possible to query an index?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
anjoos20

New User


Joined: 09 Apr 2008
Posts: 14

PostPosted: Tue Mar 03, 2009 10:27 am    Post subject: Is it possible to query an index?
Reply with quote

Can any one explain me whether we can query an index or not?
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 03, 2009 10:57 am    Post subject:
Reply with quote

Hello,

Your question may have multiple answers depending on what you are looking for.

Please provide an example and it will help us answer.
Back to top
View user's profile Send private message
dharmendra_kp

New User


Joined: 10 Mar 2005
Posts: 33
Location: Lewiston

PostPosted: Tue Mar 03, 2009 11:50 am    Post subject:
Reply with quote

Is it an interview question?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Mar 03, 2009 2:22 pm    Post subject:
Reply with quote

Anjoos,

I don't think you can query an index. That would be my answer, FIRST.
But, by the way D.sch replied
Quote:
Your question may have multiple answers depending on what you are looking for.

I am thinking like, is it possible ?

Sushanth
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Mar 03, 2009 8:45 pm    Post subject:
Reply with quote

Hi Sushanth,

Quote:
is it possible
This will depend on the actual question. . .

One thought is that the question is really about satisfying a query using only the index. Another is to how ask db2 for info about some index(es). Another might be how to make sure a query uses an index (or how to prevent a full table scan).

Once some clarification has been provided, we should be better able to reply.
Back to top
View user's profile Send private message
anjoos20

New User


Joined: 09 Apr 2008
Posts: 14

PostPosted: Wed Mar 04, 2009 10:30 am    Post subject:
Reply with quote

Hi Dick,
My question is can we use select statement on a table index.

Thanks
Anju

dick scherrer wrote:
Hi Sushanth,

Quote:
is it possible
This will depend on the actual question. . .

One thought is that the question is really about satisfying a query using only the index. Another is to how ask db2 for info about some index(es). Another might be how to make sure a query uses an index (or how to prevent a full table scan).

Once some clarification has been provided, we should be better able to reply.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 10:41 am    Post subject:
Reply with quote

Hello,

Quote:
My question is can we use select statement on a table index.
That is just a re-post of the original question. It is not any more clear. Keep in mind that while your question is completely clear to you, it may not be to others.

From what you have posted, the only safe answer is "no". . .

What you want to do is likely possible, but until you better define what you want to do, we can't offer any better suggestions.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Mar 04, 2009 10:41 am    Post subject:
Reply with quote

D.sch,

Quote:
the actual question. . .

Question is "Is it possible to query an index".
Query can be like,
select * from myindex
or
EXTRACTing the data inside an index by a method.
This is "THE HOW PART ?".

Quote:
One thought is that the question is really about satisfying a query using only the index.

No, its not about satisfying an query, but indexes are to be designed in such a way, a query will use an index and retreive data efficiently in good amount of time. In the end, query has to be satisfied in the best possible way.

Quote:
Another is to how ask db2 for info about some index(es).
Information about the structure of an index can be got from Catalog Tables.
Only the structure.

Quote:
Another might be how to make sure a query uses an index (or how to prevent a full table scan).
Use indexed columns in the predicates.

Sushanth.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 11:04 am    Post subject:
Reply with quote

Hi Sushanth,

Those "bullets" i posted were meant to generate ideas about how to clarify the original question.

Quote:
Query can be like,
select * from myindex
Has this been tested? What is myindex?

It will help if Anjoos posts some sample table/columns, the index definitions for the table and some sample data that would be quesied against.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Mar 04, 2009 12:06 pm    Post subject:
Reply with quote

D.sch,

Quote:
Those "bullets" i posted were meant to generate ideas

It did. And it is still continuing............

select * from myindex
Is an dummy example INDEX. Without testing i can say 'It wont' work'. I tested on a existing index.
I got an error
Code:
 SQLCODE = -204, ERROR:  SHANDB.DIXEMP1 IS AN UNDEFINED NAME   

Reason : It is an index, not an table.

what i know is, its not possible by a SELECT.
What iam really interested in knowing is, if the safe answer is no, what would be the unsafe answer ?

For the Learning,
Sushanth
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Mar 04, 2009 8:02 pm    Post subject:
Reply with quote

Hi Sushanth,

Quote:
what would be the unsafe answer
Ah, maybe yes would be unsafe icon_wink.gif

As you demonstrated, the select needs column names from tables.

I believe that if Anjoos posts a better example/description of what is desired, there may well be a way to do it. . .
Back to top
View user's profile Send private message
anjoos20

New User


Joined: 09 Apr 2008
Posts: 14

PostPosted: Thu Mar 05, 2009 2:13 pm    Post subject:
Reply with quote

Quote:
I believe that if Anjoos posts a better example/description of what is desired, there may well be a way to do it. . .



What i meant is : Is it possible to see the pointer for a particular value of my key.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Mar 05, 2009 2:17 pm    Post subject:
Reply with quote

Anju,

I think an index will contain indexed columns of a table & ROWID.
Is ROWID or POINTER you want to see.

Sushanth
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Mar 05, 2009 3:58 pm    Post subject:
Reply with quote

Quote:


I think an index will contain indexed columns of a table & ROWID.


Indexes has pointers to the data rows of a table .... the index is structured as a b-tree with the leaf pages containing the pointers ... this pointer is called a RID or record id... you cannot view this pointer ...
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Mar 05, 2009 4:43 pm    Post subject:
Reply with quote

Thank You ASHIMER,
Till now i was thinking as ROWID.

Sushanth
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Mar 05, 2009 9:24 pm    Post subject: Reply to: Is it possible to query an index?
Reply with quote

Thanks Ashimer icon_smile.gif

When i thought it "might be possible", i had a different requirement in mind icon_wink.gif

d
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Mar 06, 2009 10:25 am    Post subject:
Reply with quote

Guyz,

Quote:
When i thought it "might be possible", i had a different requirement in mind

Again!. It is possible to get data which is inside the index. RIGHT.

Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us