MVSFORUMS.com

yadav2005 · Intermediate Joined: 10 Jan 2005 Posts: 348 Topics: 144

I have two base tables with millions of records.The view is a simple select of columns from the two different tables with a where condition.With the count of records being huge,I am trying to create a View which should select first 10 rows only but I am failing with the error -109.Is there a way to solve this problem:

kolusu · Posted: Thu May 26, 2011 10:33 am Post subject:

yadav2005,

FETCH FIRST clause is supported in z/OS DB2 V9 and higher. What version of DB2 are you running?

Kolusu

yadav2005 · Intermediate Joined: 10 Jan 2005 Posts: 348 Topics: 144

Kolusu,

I am in DB2 V8 only . Is there any way to achieve the same.

dbzTHEdinosauer · Posted: Fri May 27, 2011 12:53 am Post subject:

i may be way off base here, but:

is not a view only a description of data,
that does not exist until sql is invoked to select from the view?

I don't think any version of db2 would allow a FETCH clause in the declare/Create,
otherwise the view would not be a view, but instead a populated results table,
which a VIEW is not.

now, if the idea is to insure/restrict the 'base' view to only contain a few rows,
then olap functions / row number could be in the WHERE Clause - possibly.

don't have much experience with views (will experiment later)
but, even if a FETCH first n rows or ONLY,
which you are attempting to be a part of the VIEW definition (CREATE),
would still mean that the VIEW would be materialised (wrong terminology, probably)
with the millions of rows and then the FETCH restrictions would be applied.

and since your goal is to have the VIEW only FETCH (materialize) a few rows,
they would always be the same,
so why not restrict them in the CREATE VIEW WHERE clause with EMPNO < (or not gt) a literal value?
_________________
Dick Brenholtz
American living in Varel, Germany

dbzTHEdinosauer · Posted: Fri May 27, 2011 2:46 am Post subject:

ok, I received a -4700 error: attempt to use new function before new function mode.

so, I can only assume that my previous post is BS, and you can use the FETCH clause in a create view construct. i am at db2 9, obviously without new function mode.

so, not being able to test - disregard my previous post.

dbz
_________________
Dick Brenholtz
American living in Varel, Germany

GuyC · Supermod Joined: 27 May 2011 Posts: 35 Topics: 0

No, your first post was correct:

dbzTHEdinosauer · Posted: Mon May 30, 2011 2:17 am Post subject:

GuyC,
welcome to the forum and thanks for reminding us,
when all else fails, read the manual.
_________________
Dick Brenholtz
American living in Varel, Germany

kolusu · Posted: Mon May 30, 2011 9:36 am Post subject:

GuyC,

Hmm where did the error code -20211 show up?

Dbz

Here is a piece of info which talks about ORDER BY and FETCH FIRST in Subselects [DB2 9 for z/OS]

http://db2portal.blogspot.com/2007/04/order-by-and-fetch-first-in-subselects.html

If you want to test it out on UDB version here is an apar description of creating views with fetch first

https://www.ibm.com/support/docview.wss?uid=swg1IY82816

Kolusu

GuyC · Supermod Joined: 27 May 2011 Posts: 35 Topics: 0

it's a DB2 for z/OS v9 NFM message.
And it's not about subselects, it's on the outer select of a view that it isn't allowed.

Although following selects return the same result, the first is allowed, the second isn't :

GuyC · Supermod Joined: 27 May 2011 Posts: 35 Topics: 0

some errors in previous post where I forgot to change the column names and tables, but I guess editing ones own post isn't allowed on this forum.

kolusu · Posted: Tue May 31, 2011 10:54 am Post subject:

GuyC,

I have granted edit authority of your posts.

Kolusu