SAP HANA Sequences FAQ's
1) Explains sequences in SAP HANA environments?
A sequence is a database object in SAP HANA environments which can be used to generate unique numbers.
2) What are the indications existing for problems with sequences?
SQL: 'HANA_Configuration_MiniChecks' throws potentially critical issue (C = 'X') for one of the following individual checks:
| Check ID | Details |
| M1210 | DDLOG sequence cache size |
| M1940 | Sequences with insufficient caching |
| M2510 | Orphan SLT sequences |
3) How can we maintain and use sequences?
We can use following command in order to maintain sequences be maintained and used:
| Command | Details |
|
ALTER SEQUENCE "<sequence_name>" ... |
Modifiny theexisting sequence (e.g. in terms of caching or values) |
|
CREATE SEQUENCE "<sequence_name>" ... |
Creation of a sequence Without further options, a sequence starting with number 1 and without caching is implemented |
|
DROP SEQUENCE "<sequence_name>" ... |
Drop of a sequence |
|
"<sequence_name>".NEXTVAL |
Retrieve the values of next sequence (e.g. in context of SELECT or INSERT) |
Note: Creating database objects in productive systems is not in line with security best practices.Therefore as an alternative, we can create a sequence as a design time object, .hdbsequence, in our development system and transport it.
4) What are the contexts in which sequences are used by SAP?
SAP uses sequences in the following contexts:
| Scenario | Details |
|---|---|
| ABAP buffer synchronization | The ABAP buffer synchronization table DDLOG uses sequence DDLOG_SEQ. |
| SLT | In SLT contexts changes to source tables are recorded via triggers in logging tables. In this context sequences are used following the naming convention SEQ_<logging_table>. |
5) How to implement sequences?
The values of current sequence are stored in a specific internal table in the SAP HANA master node. If in case no sequence caching is used, then all the NEXTVAL requests have to contact the master in order to retrieve and increment the sequence value.
If the caching is used (i.e. sequence is defined with "CACHE <cache_size>") the defined number of sequence values is cached and the cache is always transferred to the SAP HANA node where there most recent NEXTVAL request was executed.
6) What are some other implementations which are similar to sequences?
The following implementations are used in a similar way like sequences:
- SAP HANA identities (GENERATE [BY DEFAULT] AS IDENTITY, CURRENT_IDENTITY_VALUE())
- SAP ABAP number range buffering (NRIV table)