Re: Current identity value for GENERATED ALWAYS AS IDENTITY column

Oh well worth a shot :)

What are you attempting to do? Why do you think this value would be useful?

If you're trying to get the identity value that will be used on the next
insert, just do the insert and then use the Identity_Val_Local() John
mentions. Or "Final table".

The trouble as I see it is that any "next value" you could get from the DB
may not be right.

If caching is used, the DB may have a "next value" of 20, yet your job or
another job may insert records 15,16,17,18,19 before you actually get to 20.

If you've done an operation, CPYF comes to mind, where the current max
value in the table doesn't match the Db's next value...then just reset it
after the operation.

At one point, I built a utility that I ran as a post-install exit program
for a CMS that would check to see if any tables that had been depoloyed had
identities and if so would run an ALTER TABLE to reset the "next value" ==
max(id) + 1;

IIRC, the CMS in question eventually added better support..

Charles



On Wed, Nov 22, 2017 at 9:00 AM, Dan <dan27649@xxxxxxxxx> wrote:

Thanks Charles. This seems to correspond with DSPFFD output, and the
"Current START WITH" shown there is 1, but when I do an insert on this
table, the value placed in the column is 9,442,900, which is one more than
the previous record written to the table. Both DSPFFD and the QDBRTVFD API
refer to "Original START WITH value" and "Current START WITH value", so one
could assume that the latter value should provide the current identity
value. I'll try to whip up something Q&D to see what the API divulges.

I also queried SYSCOLUMNS, but strangely enough, it defines only one
"IDENTITY_START" value. I wonder if there's another SYS* table/view that
has more IDENTITY-type info. Perhaps the "Original START WITH value" and
"Current START WITH value" shown in DSPFFD and the QDBRTVFD API are
populated from the same "IDENTITY_START" value? FWIW, we're on v7r1,
fairly current with TRs and PTFs.

- Dan

On Wed, Nov 22, 2017 at 10:29 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Not positive...

But it appears the the *Qdb_Qddfidcl *section of the Retrieve Database

File

Description (QDBRTVFD) API may have this as "Current STARTS WITH"

--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://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