Re: How to increment a ID field that is not a ID field.

Darryl,
DDS-based physical files are really Db2i database tables internally, so you can also use SQL to alter the table definition. 

For example, in STRSQL issue:
ALTER TABLE yourlib/yourfile ADD COLUMN ident INT NOT NULL WITH DEFAULT

Then, issue:

ALTER TABLE yourlib/yourfile ALTER COLUMN ident SET GENERATED ALWAYS AS IDENTITY

Now, to cause SQL to generate correct ident values for all existing rows, issue:
UPDATE yourlib/yourfile SET ident = DEFAULT

If the file already had a primary key, you can drop that constraint:
ALTER TABLE yourlib/yourfile DROP PRIMARY KEY CASCADE


Finally, you may want to add a primary key constraint to make "ident" the primary key:
ALTER TABLE yourlib/yourfile ADD PRIMARY KEY (ident)


And, Bob's your Uncle!  :-)
Hope that helps,
Mark S. Waterbury
On Wednesday, November 11, 2020, 11:15:06 AM EST, a4g atl <a4ginatl2@xxxxxxxxx> wrote:

I have a file with fields: ID, field1, field2. ID is the key and is unique.

I have a SQL script to read a view and insert records into this file. ID is
a numeric field so I tried to use the following to auto generate the next
ID number.

INSERT into TARGET_FILE
(ID, FIELD1, FIELD2)
(Select (Select Max(ID) + 1 from TARGET_FILE), FLD1, FLD2 from SOURCE_FILE)

The problem is it builds the ( Select Max(ID) + 1 from TARGET_FILE) upfront
and so the value is the same for each record in the source_file.

Are there suggestions on how to achieve this?
I want to avoid using a RPG program to get this to work.
I am not able to define the PF as a table so the field will remain a
numeric field.

TIA, Darryl Freinkel