Re: how to make a key on a view?

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

You can't make the index from THIS source member - you need a CREATE 
INDEX - go to the SQL Reference and look that up - I don't have the 
exact syntax in my head right now.

BTW, this doesn't look like the entire contents of that source - is 
there an extra bit about a constraint at the end? That could be a 
primary index.

You need to create a different source member of type TXT - actually I 
prefer type SQL, but it doesn't matter.

Then put the CREATE INDEX statement you have worked out into that source 
member.

Then use RUNSQLSTM, specifying this new member, and you will have your 
index.

One thing - try this command - DSPDBR PROJ_MKWN1/DTAWHS - this will tell 
you about any logical files over the table - once you have those names, 
do a DSPFD on them and see if they have key fields. Maybe you already 
have an appropriate index.

If you have a tool like DBU, you can get this same information faster.

HTH
Vern

On 1/30/2014 6:14 PM, Hoteltravelfundotcom wrote:
> #5 I can make the index when using the RUNSQLSTM? I have been away from the
> i for a few years before this assignment since May. But they keep me mainly
> on the reporting tool. No not because I can't code in RPG but because of
> the workload.
>
>
> On Thu, Jan 30, 2014 at 7:11 PM, Hoteltravelfundotcom <
> hoteltravelfun@xxxxxxxxx> wrote:
>
> > right not. this table was created by a consultant. For purpose of
> > recreating many of our reports from the i, to the reporting tool.
> > I do not know much about what exactly he did there. I would need to review
> > the RPG programs we have seen in that list.
> > but for my purposes, I wanted to use in the reporting tool but as most
> > optimized as possible.THis table does exist. I believe there is a nightly
> > refresh to it.
> >
> >
> > On Thu, Jan 30, 2014 at 6:50 PM, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>wrote:
> >
> > > One thing - CHECK WHETHER DTAWHS EXISTS ALREADY IN THAT LIBRARY
> > > (PROJ_MKWN1).
> > >
> > > Please excuse the flaming - I'm not upset, just trying to make a very,
> > > very strong point.
> > >
> > > You probably do NOT want to wipe out existing data, right?
> > >
> > > :)
> > >
> > > On 1/30/2014 5:47 PM, Vernon Hamberg wrote:
> > > > You're jumping over things again - there are important gaps in what we
> > > > are hearing.
> > > >
> > > > 1. This member is NOT where the data resides. If that is what you think,
> > > > then disavow yourself of that notion instantly
> > > > 2. This member is the source code for CREATING the table where the data
> > > > will reside.
> > > >        a. That table is names DTAWHS and it is located in library
> > > PROJ_MKWN1
> > > > 3. I'm glad you keep asking questions, BTW, because it seems things that
> > > > are "obvious" to those who've been on the platform for awhile - that
> > > > they are not "obvious" to you - maybe you've been pushed into doing
> > > > these tasks, I think you've hinted at that.
> > > > 4. So you will need to use this member to create the table, and the
> > > > command for that is RUNSQLSTM, where you specify this member, and out
> > > > comes an SQL table (also know as a PF).
> > > > 5. You CAN make an index on that table, and if you are at a newer
> > > > release, there is more you can do, but I don't think you will need to.
> > > >
> > > > So now I have to ask, how does the data get into DTAWHS? Or does that
> > > > table already exist and has been in production?
> > > >
> > > > Good luck
> > > > Vern
> > > >
> > > > On 1/30/2014 5:36 PM, Hoteltravelfundotcom wrote:
> > > > > I wanted to index the invoice number as we are adding this processing.