Skip to Content
0
Nov 26, 2020 at 06:21 PM

HANA Sequences incrementing by last identity value

51 Views

We have an addon that did not present this issue in SAP HANA 9.3 and earlier. Since upgrading to version 10, we have noticed this issue.

We use the SAP.Data.Hana.v4.5.dll to make ADO.Net connections and perform CRUD on non SAP tables. These tables have INT GENERATED BY DEFAULT AS IDENTITY columns (eg. SEQNO) so there are _SYS system sequences created to manage these auto-incrementing primary keys.

The scenario is, we perform some direct INSERTS into a table (eg. TableA), if we check the NEXTVAL on the system sequence, it is incrementing properly. Next, we add an SAP document which triggers the SBO_TRANSACTION_NOTIFICATION procedure. Under certain circumstances, it will INSERT into TableA as well. After this operation we check NEXTVAL and it is properly incremented. After this, we perform an UPDATE to TableA and check the NEXTVAL, and it has jumped by approximately double what the largest SEQNO in TableA currently is. So the next INSERT to TableA, contains a SEQNO that has an ever increasing gap from the previous until we get a numeric overflow error in the INT column.

What could be causing the system sequence to be advanced in this fashion. We've been debugging this for a few days now and although we can re-create the issue, we have no idea what could be causing this issue.