Using multi-tenant-enabled sequences

You can create a sequence that is multi-tenant enabled. However, you cannot convert an existing non-multi-tenant sequence to be a multi-tenant sequence. You need to delete the sequence and recreate it in order to make it multi-tenant-enabled.

If a sequence is multi-tenant enabled, a separate current value, for the same sequence of values, is maintained for each regular tenant in the database. The built-in sequence statements and functions NEXT-VALUE, CURRENT-VALUE, DYNAMIC-NEXT-VALUE, and DYNAMIC-CURRENT-VALUE contain an optional parameter for specifying a particular tenant value, primarily for use when coding for a super tenant0

Syntax

[ NEXT-VALUE | CURRENT-VALUE ] ( sequence-name [ , logical-dbname
[, tenant-id ] ]) [ = expression ]

[ DYNAMIC-NEXT-VALUE | DYNAMIC-CURENT-VALUE ] ( sequence-name-expression
, logical-dbname-expression [, tenant-id ] ) [ = expression ]

An integer expression that only has meaning for a multi-tenant-enabled sequence and that evaluates to a valid tenant ID for a regular tenant. A super tenant can specify any regular-tenant ID. If a super tenant does not specify this argument, the statement or function applies to the effective tenant.

Typically, a regular tenant never uses this argument. If a regular tenant does use the tenant-id argument, the specified tenant must be the same as the tenant associated with the database connection. Otherwise, the AVM raises an error.

If the sequence is not enabled for multi-tenancy, and the tenant-id argument is specified, the argument is ignored.

For more information on using the typical arguments with these statements and functions, see Sequences.

A regular-tenant user, like the user of a non-multi-tenant database, typically accesses these statements and functions without specifying a tenant ID, because they can only access the sequence instance with their tenancy. However, database triggers can execute for both regular and super tenants. So, appropriately specifying the tenant ID in database triggers ensures that the correct sequence is used in all cases. The following example demonstrates the database create trigger that uses the tenant-id argument:

TRIGGER PROCEDURE FOR CREATE OF Customer:
ASSIGN Customer.CustNum
= NEXT-VALUE( NextCustNum, sports, BUFFER-TENANT-ID( Customer ) ).
END.

In the above example, Customer is a multi-tenant table. Use of the BUFFER-TENANT-ID function ensures that for a super tenant, the trigger increments the customer number using the multi-tenant NextCustNum sequence for the tenant that owns the current Customer record. For more information on the BUFFER-TENANT-ID function, see Managing access to tables with groups.

Note that because every regular tenant has its own instance of a multi-tenant sequence, the values that the sequence generates are unique only for multi-tenant table instances owned by that tenant. If the sequence is used to generate values for a shared table, those values will not be unique because every tenant generates the same values, and there is no automatic way to tell which tenants have generated the duplicate values. This means that care must be taken when deciding whether to make a sequence multi-tenant and where to use it.