Re: (SQL) Identity Crisis

[Mark S Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>]

Hi, Kurt:

An alternative that may be easier to manage than "identity" is to use an 
SQL "sequence" ... see:

    
http://www-03.ibm.com/systems/resources/systems_i_software_db2_pdf_db2sequence.pdf

All the best,

Mark S. Waterbury

> On 4/5/2013 1:33 PM, Anderson, Kurt wrote:
> Well, crisis somewhat averted, but I'm having an issue with an identity field in a SQL created file.
>
> Here's an excerpt from the table definition:
>     EditID     Dec            (10, 0)      NOT NULL GENERATED ALWAYS AS IDENTITY
>                                          (START WITH 1 INCREMENT BY 1 CYCLE),
>     Primary Key(EditID)
>
> The file was created last October and has been fine all along.   Today we added a new field to the file and recreated the table.  We copied the old records in, and they retained their EditID values, so that was great.  However, the moment a record was added, it attempted to start at 1 again and got a duplicate record error.   Crap!   To get around the issue, for the time being, I modified the EditID definition to "START WITH 8060."  We're off and running again.
>
> For every change to the file will the "START WITH" have to be modified?   Is there another way to get around this issue?
>
> I have a feeling the answer might be to use ALTER TABLE instead of recreating the file.  It's not that I'm against that, it's just that I'm already introducing a lot of change (new stuff) here, and I don't want that bubble to burst.   In addition, we don't have change management software (sigh), so I ended up creating a system to implement files across all of our clients' libraries. To have to redesign it to know if the RUNSQLSTM it runs is executing a CREATE or an ALTER is a big deal.
>
> I appreciate any input.
>
> We're on IBM i 7.1.
>
> Thanks,
> Kurt Anderson
> Sr. Programmer/Analyst
> CustomCall Data Systems, a division of Enghouse Systems Ltd.