Re: fields in a PF

Did you read them? He created the file from scratch using SQL. So, I
guess that doesn't change the DDS in any way. But then again, DDS is just
a source file. It really has nothing to do with the actual physical file.
We often don't even copy the DDS to our production system.

Now, I suppose you could have an existing table, created with DDS. Much
like the table Chuck created with CREATE TABLE. Then modify it with SQL,
like Chuck did with
alter table id
add column i int not null generated always as identity
( start with 0 increment by 1 cycle)
add constraint id_i_pk primary key (i)
At this point I would either delete the original DDS or put an error in it
on purpose. Then put a comment it in that says "Don't create this file
with this DDS - you'll just bugger it up!!!" Some might go so far as to
use iNav's Generate SQL to create an sql statement that would recreate the
table. Then store this source over top of the old DDS. Then when someone
tries to run CRTPF on sql source it will blow to kingdom come. Then they
might read the source and see a comment in there that says "Don't use
CRTPF - use RUNSQLSTM instead to 'compile' this."

For example, if I have this DDS:
R CLUSTERR
MYALPHA 5A
MYDEC 5P 0
and I use CRTPF on it to generate a table called CLUSTERF. And I pop in a
few rows into that table:
MYALPHA MYDEC
A 1
B 2
C 3
Now I modify the structure of the table itself with
alter table rob/clusterf
add column i int not null generated always as identity
( start with 0 increment by 1 cycle)
add constraint id_i_pk primary key (i)
My table now looks like this:
MYALPHA MYDEC I
A 1 0
B 2 1
C 3 2

Notice the new column? Also notice that I didn't modify my existing DDS
source file? If I don't destroy that DDS source file member out there and
some buck toothed galoot comes along and recompiles it with CHGPF what's
going to happen to column I? (as in the letter I)?

But if I use iNav to generate sql and replace the DDS with
-- Generate SQL
-- Version: V7R1M0 100423
-- Generated on: 02/14/12 16:48:04
-- Relational Database: GDISYS
-- Standards Option: DB2 for i
CREATE TABLE ROB.CLUSTERF (
-- SQL150B 10 REUSEDLT(*NO) in table CLUSTERF in ROB ignored.
MYALPHA CHAR(5) CCSID 37 NOT NULL DEFAULT '' ,
MYDEC DECIMAL(5, 0) NOT NULL DEFAULT 0 ,
I INTEGER GENERATED ALWAYS AS IDENTITY (
START WITH 0 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
CYCLE NO ORDER
CACHE 20 )
,
CONSTRAINT ROB.ID_I_PK PRIMARY KEY( I ) )

RCDFMT CLUSTERR ;

GRANT ALTER , DELETE , INSERT , REFERENCES , SELECT , UPDATE
ON ROB.CLUSTERF TO PROGRAMMER WITH GRANT OPTION ;

GRANT DELETE , INSERT , SELECT , UPDATE
ON ROB.CLUSTERF TO PUBLIC ;

If that person tries to compile that with CHGPF it'll REALLY catch his
eye. At that time you might wish to put a comment in there that says "Use
RUNSQLSTM on this - not CHGPF."


Rob Berendt