Initialize the value for an identity column - HANA SPS8?

[Former Member]

Can initialize the value for the sequence (_SYS_SEQUENCE_) used for an identity column?

Thank you

[Former Member]

You can use the following sql statement:

CREATE SEQUENCE SCI."SEQUENCE1" START WITH 1;

SELECT SCI."SEQUENCE1".NEXTVAL FROM DUMMY

Reference: CREATE SEQUENCE - SAP HANA SQL and System Views Reference - SAP Library

Regards,

Pablo Silva

[Former Member]

Hi Pablo

The indicated option does not takes advantage of using identity columns in

HANA SPS8. To implement identity columns in HANA SPS8, system sequences are

created (_SYS_SEQUENCE_). For these sequences I can indicate an initial

value using the clause: "generated by default as identity (start with xx)". I

need to know, how to change the "NEXTVAL" value, after the table (using

identity column) was created.

Thanks in advance

2014-11-06 3:01 GMT-06:00 Pablo Silva <experiencesaphana@sap.com>:

SAP HANA <http://www.saphana.com/index.jspa> Initialize the value

for an identity column - HANA SPS8? created by Pablo Silva

<http://www.saphana.com/people/C5185151> in SAP HANA One Support - View

the full discussion <http://www.saphana.com/message/13604#13604>

[Former Member]

Got it.

There are two ways a sequence can be reset:

1. alter sequence "UADMIN_USER"."_SYS_SEQUENCE_210253_#0_#" restart with 1
2. During a restart of the database, the system automatically executes the RESET BY statement and the sequence value is restarted with the value determined from the RESET BY subquery.

This sequence name I got from

But I also got the following error message when trying the first approach:

CREATE COLUMN TABLE TEST_TABLE (ID integer GENERATED BY DEFAULT AS IDENTITY(start with 2),COL2 VARCHAR(2));

select '"' || SCHEMA_NAME || '"."' || SEQUENCE_NAME || '"' AS SEQUENCE_NAME from SEQUENCES WHERE RESET_BY_QUERY LIKE '%TEST_TABLE%';

INSERT INTO TEST_TABLE(COL2) VALUES('r1');

INSERT INTO TEST_TABLE(COL2) VALUES('r2');

alter sequence "UADMIN_USER"."_SYS_SEQUENCE_210253_#0_#" restart with 1

Could not execute 'alter sequence "UADMIN_USER"."_SYS_SEQUENCE_210253_#0_#" restart with 1' in 259 ms 481 µs .

SAP DBTech JDBC: [383] (at 29): invalid identifier: _sys_ prefix not allowed: _SYS_SEQUENCE_210253_#0_#: line 1 col 30 (at pos 29)

Let me know if you find something better.

Regards,

Pablo Silva

[former_member185332]

There is a much easier way to reset/set the sequence:

A: determine the current value: select schema.sequencename.CURRVAL from dummy; 5

B: determine the value you want to start with (mostly a SELECT MAX(column) from table) for example 10

set the next value:

select schema.sequencename.NEXTVAL+6 from dummy

this will set the currval to 11 (10-5 +1)

NEXTVAL is (as CURRVAL) a function for the sequence. NEXTVAL sets the value. where CURRVAL gives you the value of the sequence.

[former_member185332]

the + doesn't work can i edit my previous post?

[mert_karakilic]

Right but the whole point is to reset the auto generated sequence by the ID column set as identity to auto increment. Those auto generated sequences are not allowing you to update them to reset. If you can reset it, then you can carry on with any insertions without providing any IDs as usual. I think he is trying to cover the case where you need to sometimes insert with some fixed IDs rather than using the next value.