alter hana sequence

[mario_galeano]

When trying to re-sequence a sequence I get the following error:

ALTER SEQUENCE "_SYS_SEQUENCE_283138_#0_#" RESTART WITH 1;

[mcc2]

SAP please implement a fix for HANA. We have experienced the same problem in production.

DML such as this are not allowed:

ALTER SEQUENCE "_SYS_SEQUENCE_225390_#0_#"
RESTART WITH 1500000 ;

They yield [Code: 383, SQL State: HY000] SAP DBTech JDBC: [383] (at 15): invalid identifier: _sys_ prefix not allowed: _SYS_SEQUENCE_225390_#0_#: line 1 col 16 (at pos 15)

The SEQUENCE with prefix _SYS_ is auto created by HANA when the table is created with code such as at bottom for our current release. If you need to modify the table say change from ROW storage to COLUMN storage the SEQUENCE reset to start at 1 and created lots of problems due to trying to create duplicate keys.

The use of SEQUENCE is an SQL Best practice to atomically create an identity key which is auto generated is unique. Oracle, SQL Server, Postgres, Maria, DB2 for SEQUENCEs built verses. Some allow use of a named SEQUENCE as a default value for a primary key field. The named sequence, which cannot be prefixed with _SYS_, allows the restart clause such as ALTER SEQUENCE SEQ1 RESTART WITH 150000;

CREATE ROW TABLE "OWDATA"."WEBLOG"
( "KEYTYPE" VARCHAR(8) CS_STRING DEFAULT ' ' NOT NULL,
"KEYID" DECIMAL(10) CS_FIXED DEFAULT 0 NOT NULL,
"USERID" INT CS_INT NOT NULL,
"MSGID" CHAR(7) CS_FIXEDSTRING DEFAULT ' ' NOT NULL,
"LOGDATE" DAYDATE CS_DAYDATE DEFAULT CURRENT_DATE NOT NULL,
"LOGTIME" SECONDTIME CS_SECONDTIME DEFAULT CURRENT_TIME NOT NULL,
"ROWID" DECIMAL(10) CS_FIXED GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 9999999999),
PRIMARY KEY ( "ROWID" ) ) ;

[pfefferf · Accepted solution]

I guess the sequence is created based on an identity column.

Please check SAP note 2728304 - "ALTER SEQUENCE" command fails with error: "invalid identifier: _sys_ prefix not allowed" ... for more details.