Re: DDL Record Select

Dave,

I think that you could think (for those releases before V6R1) of a view as
a "non-keyed LF" and an index as a keyed LF, but without the capability of
having a selection clause. Of course, SQL can (and will) use an appropriate
index if necessary when accessing a view.

There are some good info on the IBM website (although sometimes it is
a little hard to find). If I may, let me suggest you these two good papers:

http://www-03.ibm.com/servers/enable/site/bi/strategy/strategy.pdf
http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_Performance_DDS_SQL.pdf

BTW, RCDFMT did appear with V5R4 (Where I work we are at V5R3, so I still
have to use the RENAME method :-( )

HTH,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Mon, Mar 15, 2010 at 10:06 AM, <daparnin@xxxxxxxxxxxxxx> wrote:

Thanks to all for the responses. We are at V5R4 so I will probably have
to investigate the difference between an index and a view. The end result
that I want is a keyed, sorted logical file that does record selection.
I'm trying to "modernize" my approach to such things but I'm still getting
up to speed. Rob mentioned the record format. I've been creating the
file with the record format name and then renaming it to the real file
name so the record format stays with what I want it to be. I was thinking
that the ability to specify a record format name was a V6R1 thing. As for
why we're not on V6R1, it basically hasn't bubbled up on the priority list
for those allocating the resources.



Dave Parnin
--
Nishikawa Standard Company
Topeka, IN 46571
daparnin@xxxxxxxxxxxxxx





Michael_Schutte@xxxxxxxxxxxx
Sent by: midrange-l-bounces@xxxxxxxxxxxx
03/12/2010 04:45 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: DDL Record Select






Shouldn't he be creating a view instead?

Dave,

You cannot tell an index to select certain records. You will need to
create a view selecting where NCSTAT = 'D' and you may want to create an
index over NCSTAT. Then you can use that view in sql statement as if it
was a file.


--

Michael Schutte
Admin Professional



Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
Perfect for Thanksgiving, Christmas or holiday entertaining.
For more information, visit www.FarmhouseFeast.com<http://www.farmhousefeast.com/>



midrange-l-bounces@xxxxxxxxxxxx wrote on 03/12/2010 04:07:12 PM:

I would just use

CREATE INDEX V40NISCOF/NSCHGRQFL1 ON V40NISCOF/NSCHGRQF
(NCSTAT, NCNPROD) WITH 1 DISTINCT VALUES;

And change any native I/O (assuming this is possible ?) to use 'D' as
the first key.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
daparnin@xxxxxxxxxxxxxx
Sent: 12 March 2010 20:35
To: midrange-l@xxxxxxxxxxxx
Subject: DDL Record Select

I've got a file and index defined in DDL that I would like to only
select
records with a certain status. In DDS I would do something like:

A S NCSTAT CMP(EQ 'D')


How would I do this in DDL? My index is:

CREATE INDEX V40NISCOF/NSCHGRQFL1 ON V40NISCOF/NSCHGRQF
(NCNPROD) WITH 1 DISTINCT VALUES;



Dave Parnin
--
Nishikawa Standard Company
Topeka, IN 46571
daparnin@xxxxxxxxxxxxxx
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.