Re: Help needed with GENERATED ALWAYS AS IDENTITY -- MIDRANGE-L

Thanks Dave - that gives me the complete solution.

Thanks also to the others who came up with the BY DEFAULT option - I had not seen that in any of the "how to convert" stuff I'd found.

After receiving this response I had cause to go googling again and came up with this page - which for any others looking at a migration scenario may be useful. I had no idea there were so many dang differences in data types!

https://www.ibm.com/developerworks/data/library/techarticle/dm-0606khatri/

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Feb 9, 2017, at 3:32 PM, dlclark@xxxxxxxxxxxxxxxx wrote:

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 02/09/2017
03:20:51 PM:

I believe that AUTO_INCREMENT needs to be replaced by GENERATED
ALWAYS AS IDENTITY but I also have existing data that has to be
loaded into the table and when I use this qualifier it refuses to
allow data input for the column. I tried removing the qualifier,
inserting the data and then doing an ALTER TABLE but it is not happy
with that either.

So - question is - how do you do this in DB2 - it seems to work in
Oracle, SQL Server, MySQL, etc. so I'm guessing it can be done but
I'm stumped.

You want: GENERATED BY DEFAULT AS IDENTITY
After that, if you want GENERATED ALWAYS then you can alter the table.
Just be sure to reset the next identiy number to one greater than the
highest identity you load into the table.

ALTER TABLE SHR460.IMIOVEXT
ALTER COLUMN IMIOVEXT_ID
RESTART WITH 24;

ALTER TABLE SHR460.IMIOVEXT
ALTER COLUMN IMIOVEXT_ID
SET GENERATED ALWAYS AS IDENTITY;

Sincerely,

Dave Clark
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331

*********************************************************************************************
This email message and any attachments is for use only by the named
addressee(s) and may contain confidential, privileged and/or proprietary
information. If you have received this message in error, please
immediately notify the sender and delete and destroy the message and all
copies. All unauthorized direct or indirect use or disclosure of this
message is strictly prohibited. No right to confidentiality or privilege
is waived or lost by any error in transmission.
*********************************************************************************************
--
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.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD