CREATE SEQUENCE instruction

L'instruction CREATE SEQUENCE crée une séquence sur le serveur actuel.

Invocation pour CREATE SEQUENCE

Cette déclaration peut être intégrée dans un programme d'application ou émise de manière interactive. Il s'agit d'une instruction exécutable qui ne peut être préparée dynamiquement que si le comportement DYNAMICRULES RUN est actif. Pour plus d'informations, voir ID d'autorisation et SQL dynamique.

Autorisation pour CREATE SEQUENCE

L'ensemble de privilèges défini ci-dessous doit inclure au moins l'un des éléments suivants :

Le privilège CREATEIN sur le schéma
Droits d'accès SYSADM ou SYSCTRL
Système DBADM
Installation Autorisation SYSOPR (lorsque le SQLID actuel du processus est défini sur SYSINSTL)

L'ID d'autorisation qui correspond implicitement au nom du schéma a le privilège CREATEIN sur le schéma.

Jeu de privilèges :

Si la déclaration est intégrée dans un programme d'application, le jeu de privilèges est constitué des privilèges détenus par le propriétaire du plan ou du paquet. Si l'application est liée dans un contexte de confiance avec la clause ROLE AS OBJECT OWNER spécifiée, un rôle est le propriétaire. Sinon, un ID d'autorisation est le propriétaire.

Si l'instruction est préparée de manière dynamique, l'ensemble de privilèges est constitué des privilèges détenus par l'ID d'autorisation SQL du processus, à moins que le processus ne se trouve dans un contexte de confiance et que la clause ROLE AS OBJECT OWNER ne soit spécifiée. Dans ce cas, les privilèges définis sont ceux détenus par le rôle associé à l'ID d'autorisation principale du processus.

Si le type de données de la séquence est un type distinct, le jeu de privilèges doit inclure le privilège USAGE sur le type distinct.

Syntaxe pour CREATE SEQUENCE

Remarques :

¹ Une même clause ne doit pas être spécifiée plus d'une fois. Des virgules de séparation peuvent être spécifiées entre les attributs d'une séquence lors de la définition de celle-ci.

type de données :

type intégré :

Description pour CREATE SEQUENCE

nom-séquence: Nomme la séquence. Le nom, y compris les qualificatifs implicites ou explicites, ne doit pas identifier une séquence existante sur le serveur actuel, y compris les noms de séquence générés par Db2.
Le nom du schéma ne doit pas commencer par « SYS », sauf s'il s'agit de « SYSADM ».

AS type de données

Spécifie le type de données à utiliser pour la valeur de la séquence. Le type de données peut être n'importe quel type de données numériques exactes (SMALLINT, INTEGER, BIGINT ou DECIMAL avec une échelle de zéro), ou un type distinct défini par l'utilisateur pour lequel le type source est un type de données numériques exactes avec une échelle de zéro. Par défaut, lorsque AS n'est pas spécifié, INTEGER est utilisé. Si DECIMAL est spécifié, la valeur par défaut est DECIMAL(5,0).

START WITH numeric-constant

Spécifie la première valeur de la séquence. La valeur peut être toute valeur positive ou négative pouvant être affectée à la colonne a du type de données associé à la séquence sans que des chiffres non nuls n'existent à droite de la virgule décimale.

Si la clause START WITH n'est pas explicitement spécifiée avec une valeur, la valeur par défaut est MINVALUE pour les séquences ascendantes et MAXVALUE pour les séquences descendantes.

Cette valeur n'est pas nécessairement la valeur à laquelle une séquence reviendrait après avoir atteint la valeur maximale ou minimale de la séquence. La clause START WITH peut être utilisée pour démarrer une séquence en dehors de la plage utilisée pour les cycles. La plage utilisée pour les cycles est définie par MINVALUE et MAXVALUE.

INCREMENT BY numeric-constant

Spécifie l'intervalle entre les valeurs consécutives de la séquence. La valeur peut être toute valeur positive ou négative (y compris 0) qui pourrait être attribuée à une colonne du type de données associé à la séquence sans qu'aucun chiffre non nul n'existe à droite de la virgule décimale. Par défaut, il s'agit de 1.

Si INCREMENT BY est positif, la séquence est ascendante. Si INCREMENT BY est négatif, la séquence descend. Si INCREMENT est égal à 0, la séquence est traitée comme une séquence ascendante.

La valeur absolue de INCREMENT BY peut être supérieure à la différence entre MAXVALUE et MINVALUE.

MINVALUE ou NO MINVALUE

Spécifie la valeur minimale à laquelle une séquence descendante effectue un cycle ou cesse de générer des valeurs ou une séquence ascendante effectue un cycle après avoir atteint la valeur maximale. La valeur par défaut est NO MINVALUE.

MINVALUE numeric-constant

Spécifie la fin minimale de la plage de valeurs pour la séquence. La dernière valeur générée pour un cycle d'une séquence descendante sera égale ou supérieure à cette valeur. MINVALUE est la valeur à laquelle une séquence ascendante revient après avoir atteint la valeur maximale.

La valeur peut être toute valeur positive ou négative pouvant être affectée à la colonne a du type de données associé à la séquence sans que des chiffres non nuls n'existent à droite de la virgule décimale. La valeur doit être inférieure ou égale à la valeur maximale.

Pour connaître les effets de la définition de MINVALUE et MAXVALUE avec la même valeur, voir Définition d'une séquence constante.

NO MINVALUE

Indique que le point final minimal de la plage de valeurs de la séquence n'a pas été spécifié explicitement. Dans ce cas, la valeur par défaut de MINVALUE devient l'une des suivantes :

Pour une séquence ascendante, la valeur est la valeur de DÉBUT AVEC ou 1 si DÉBUT AVEC n'est pas spécifié.
Pour une séquence descendante, la valeur est la valeur minimale du type de données associé à la séquence.

MAXVALUE ou NO MAXVALUE

Spécifie la valeur maximale à laquelle une séquence ascendante effectue des cycles ou cesse de générer des valeurs ou une séquence descendante effectue des cycles après avoir atteint la valeur minimale. La valeur par défaut est NO MAXVALUE.

MAXVALUE numeric-constant

Spécifie la fin maximale de la plage de valeurs pour la séquence. La dernière valeur générée pour un cycle d'une séquence ascendante sera inférieure ou égale à cette valeur. MAXVALUE est la valeur à laquelle une séquence descendante revient après avoir atteint la valeur minimale.

Pour connaître les effets de la définition de MAXVALUE et MINVALUE avec la même valeur, voir Définition d'une séquence constante.

NO MAXVALUE

Spécifie le point final maximal de la plage de valeurs pour laquelle la séquence n'a pas été spécifiée explicitement. Dans ce cas, la valeur par défaut de MAXVALUE devient l'une des suivantes :

Pour une séquence ascendante, la valeur est la valeur maximale du type de données associé à la séquence.
Pour une séquence descendante, la valeur est la valeur START WITH ou -1 si START WITH n'est pas spécifié.

Pour trouver la valeur maximale possible pour un type de données donné, voir Limites dans Db2 for z/OS.

CYCLE ou NO CYCLE

Indique si la séquence doit continuer à générer des valeurs après avoir atteint sa valeur maximale ou minimale. La limite de la séquence peut être atteinte soit avec la valeur suivante qui se situe exactement sur la condition limite, soit en la dépassant. La valeur par défaut est NO CYCLE.

CYCLE: Spécifie que la séquence continue à générer des valeurs après que la valeur maximale ou minimale a été atteinte. Si cette option est utilisée, après qu'une séquence ascendante ait atteint sa valeur maximale, elle génère sa valeur minimale. Après qu'une séquence descendante ait atteint sa valeur minimale, elle génère sa valeur maximale. Les valeurs maximales et minimales de la séquence définie par les options MINVALUE et MAXVALUE déterminent la plage utilisée pour le cyclage.
Lorsque CYCLE est activé, des valeurs en double peuvent être générées par la séquence. Lorsqu'une séquence est définie avec CYCLE, tout outil de conversion d'applications d'autres plateformes vers l' Db2 , doit également spécifier explicitement les valeurs MINVALUE, MAXVALUE et START WITH.
NO CYCLE: Spécifie que la séquence ne peut pas générer plus de valeurs une fois que la valeur maximale ou minimale de la séquence a été atteinte. L'option NO CYCLE (par défaut) peut être modifiée en CYCLE à tout moment pendant la durée de vie de la séquence.
Lorsque la valeur suivante est générée pour une séquence, si la valeur maximale (pour une séquence ascendante) ou la valeur minimale (pour une séquence descendante) de la plage logique de la séquence est dépassée et que l'option AUCUN CYCLE est activée, une erreur se produit.

CACHE ou NO CACHE

Spécifie s'il faut ou non conserver en mémoire certaines valeurs pré-allouées pour un accès plus rapide. Il s'agit d'une option de performances et d'optimisation.

CACHE constante entière

Spécifie le nombre maximal de valeurs de la séquence qu' Db2 peut préallouer et conserver en mémoire. La préallocation des valeurs dans le cache réduit les E/S synchrones lorsque des valeurs sont générées pour la séquence. Le nombre réel de valeurs que l' Db2 e met en cache est toujours le plus petit du nombre en vigueur pour l'option CACHE et du nombre de valeurs restantes dans la plage logique. Ainsi, la valeur CACHE est essentiellement une limite supérieure pour la taille du cache.

La valeur minimale est 2. La valeur par défaut est CACHE 20.

Dans un environnement sans partage de données, si le système est arrêté (normalement ou suite à une panne), toutes les valeurs de séquence mises en cache qui n'ont pas été utilisées dans des instructions validées sont perdues (c'est-à-dire qu'elles ne seront jamais utilisées). La valeur spécifiée pour l'option CACHE est le nombre maximal de valeurs de séquence qui pourraient être perdues lors de l'arrêt du système.

Dans un environnement de partage de données, vous pouvez utiliser les options CACHE et NO ORDER pour permettre à plusieurs membres d' Db2 s de mettre en cache des valeurs de séquence simultanément.

NO CACHE

Spécifie que les valeurs de la séquence ne doivent pas être préallouées. Cette option permet de s'assurer qu'il n'y a pas de perte de valeurs en cas de panne du système. Lorsque NO CACHE est spécifié, les valeurs de la séquence ne sont pas stockées dans le cache. Dans ce cas, chaque demande d'une nouvelle valeur pour la séquence entraîne une E/S synchrone vers le journal.

ORDRE ou PAS D'ORDRE

Indique si les numéros de séquence doivent être générés par ordre de demande. La valeur par défaut est NO ORDER.

ORDRE

Spécifie que les numéros de séquence sont générés dans l'ordre de la demande.

Début du changement Dans un environnement sans partage de données, il n'y a aucune garantie que les valeurs soient attribuées dans l'ordre sur l'ensemble du serveur, à moins que NO CACHE ne soit également spécifié. ORDER ne s'applique qu'à une procédure de demande unique. Fin de la modification

Début du changement Dans un environnement de partage de données, si ORDER est spécifié, NO CACHE est implicitement défini, même si CACHE integer-constant est spécifié. Fin de la modification

NO ORDER

Spécifie que les numéros de séquence n'ont pas besoin d'être générés dans l'ordre de la demande.

Dans un environnement de partage de données, si les options de constantes entières NO ORDER et CACHE sont actives, plusieurs caches peuvent être actifs simultanément, et les demandes d'attribution de valeurs suivantes de différents membres d' Db2 s peuvent ne pas aboutir à l'attribution de valeurs dans un ordre strictement numérique. Par exemple, si les membres DB2A et DB2B utilisent la même séquence, et que DB2A obtient les valeurs de cache 1 à 20 et DB2B obtient les valeurs de cache 21 à 40, l'ordre réel des valeurs attribuées serait 1, 21, 2 si DB2A demandait la valeur suivante en premier, puis DB2B, puis DB2A. Par conséquent, pour garantir que les numéros de séquence sont générés dans un ordre numérique strict parmi plusieurs membres d' Db2 s utilisant simultanément la même séquence, spécifiez l'option ORDER.

Notes pour CREATE SEQUENCE

Privilèges du propriétaire

Le propriétaire est autorisé à modifier (privilège ALTER) ou à utiliser (privilège USAGE) la séquence et à accorder ces privilèges à d'autres personnes. Voir l'instruction GRANT (privilèges de séquence ). Pour plus d'informations sur la propriété de l'objet, voir Autorisation, privilèges, permissions, masques et propriété de l'objet.

Relation entre MINVALUE et MAXVALUE

MINVALUE ne doit pas être supérieur à MAXVALUE. Bien que MINVALUE soit généralement inférieur à MAXVALUE, MINVALUE peut être égal à MAXVALUE. Si START WITH avait la même valeur que MINVALUE et MAXVALUE, la séquence serait constante. La demande de la valeur suivante dans une séquence constante semble n'avoir aucun effet car toutes les valeurs générées par la séquence sont en fait la même valeur.

Définition des séquences qui se répètent

Lorsque vous définissez une séquence, vous pouvez choisir de la faire défiler automatiquement ou non lorsque la valeur maximale ou minimale de la séquence a été atteinte.

Définir implicitement ou explicitement une séquence SANS CYCLE empêche la séquence de se répéter automatiquement une fois la limite atteinte. Cependant, vous pouvez utiliser l'instruction ALTER SEQUENCE pour faire défiler la séquence manuellement. ALTER SEQUENCE vous permet de redémarrer ou de prolonger la séquence, ce qui entraîne la poursuite de la génération des valeurs de séquence.
Définir explicitement une séquence avec CYCLE entraîne le cycle automatique de la séquence une fois la limite atteinte. Les valeurs de séquence continuent d'être générées après les cycles de séquence.
Lorsqu'une séquence est définie pour être répétée automatiquement, la valeur maximale ou minimale générée pour une séquence peut ne pas être la valeur réelle MAXVALUE ou MINVALUE spécifiée si l'incrément est une valeur autre que 1 ou -1. Par exemple, la séquence définie par START WITH=1, INCREMENT=2, MAXVALUE=10 générera une valeur maximale de 9, et ne générera pas la valeur 10.

Lorsqu'une séquence est définie avec CYCLE, tout outil de conversion d'applications (pour convertir des applications d'autres plateformes de fournisseurs vers l' Db2) doit également spécifier explicitement MINVALUE, MAXVALUE et START WITH.

Définition d'une suite constante

Vous pouvez définir une séquence de telle sorte qu'elle renvoie toujours la même valeur (ou une valeur constante). Pour créer une séquence constante, utilisez l'une de ces techniques lors de la définition de la séquence :

Spécifiez une valeur INCREMENT de zéro et une valeur START WITH qui ne dépasse pas MAXVALUE. Utilisez cette option dans la plupart des cas, et en particulier si la séquence constante est souvent utilisée, comme pour les charges de travail transactionnelles.
Spécifiez la même valeur pour START WITH, MINVALUE et MAXVALUE, et spécifiez CYCLE. Cette option nécessite davantage d'écriture dans le journal et peut entraîner des temps d'attente plus longs, en particulier si la séquence est utilisée fréquemment.

Une séquence constante peut être utilisée comme variable numérique globale. Vous pouvez utiliser ALTER SEQUENCE pour ajuster les valeurs générées pour une séquence constante.

Valeurs consommées d'une séquence

Après qu' Db2 ait généré une valeur pour une séquence, on peut dire que cette valeur est « consommée », qu'elle soit utilisée ou non par l'application. La valeur n'est pas réutilisée dans le cycle en cours. Une valeur consommée peut ne pas être utilisée lorsque l'instruction qui a provoqué la génération de la valeur échoue pour une raison quelconque ou est annulée après la génération de la valeur. Les valeurs générées mais non utilisées peuvent constituer des trous dans une séquence.

Intervalles dans une séquence

Les valeurs consécutives d'une séquence diffèrent de la valeur constante INCREMENT BY spécifiée pour la séquence. Cependant, des écarts peuvent survenir dans les valeurs attribuées à un objet de séquence par l' Db2.

Les situations suivantes sont des exemples de la manière dont des écarts peuvent être introduits dans la séquence des valeurs :

Une transaction a avancé dans la séquence, puis est revenue en arrière.
L'instruction SQL conduisant à la génération de la valeur suivante échoue après la génération de la valeur.
L'expression NEXT VALUE est utilisée dans l'instruction SELECT d'un curseur dans un environnement DRDA où le client utilise le bloc-fetch et où toutes les lignes récupérées ne sont pas récupérées par l'application.
La séquence est modifiée puis la modification est annulée.
La séquence (ou une table de colonnes d'identités) est supprimée, puis la suppression est annulée.
L'espace de la table d' SYSIBM.SYSSEQ s est arrêté ou fermé pour une raison quelconque (y compris lorsque DSMAX est atteint)
Le sous-système d' Db2 s est arrêté ou tombe en panne

Les valeurs de ces écarts ne sont pas disponibles pour le cycle en cours, à moins que la séquence ne soit modifiée et relancée d'une manière spécifique pour les rendre disponibles.

Une séquence est incrémentée indépendamment d'une transaction. Ainsi, une transaction donnée qui incrémente la séquence deux fois pourrait voir un écart dans les deux nombres qu'elle reçoit si d'autres transactions incrémentent simultanément la même séquence. La plupart des applications peuvent tolérer ces cas de figure, car il ne s'agit pas vraiment de lacunes.

Valeurs de séquence en double

Il est possible que les valeurs en double soient générées pour une séquence. Les valeurs en double sont plus susceptibles de se produire lorsqu'une séquence est définie avec l'option CYCLE, qu'elle est définie comme une séquence constante ou qu'elle est modifiée. Par exemple, les situations suivantes pourraient entraîner des valeurs de séquence en double :

Une séquence est définie avec les attributs START WITH=2, INCREMENT BY 2, MINVALUE=2, MAXVALUE=10, et CYCLE.
L'instruction ALTER SEQUENCE permet de redémarrer la séquence avec une valeur déjà générée.
L'instruction ALTER SEQUENCE permet d'inverser le sens croissant d'une séquence en changeant la valeur INCREMENT BY d'un positif à un négatif.

Utilisation des séquences

Une séquence peut être référencée à l'aide d' une référence de séquence. Une référence de séquence peut apparaître dans la plupart des endroits où une expression peut apparaître. Une référence de séquence peut spécifier si la valeur à renvoyer est une valeur nouvellement générée ou la valeur précédemment générée. Une expression de séquence NEXT VALUE est utilisée pour générer une nouvelle valeur. Une expression de séquence PREVIOUS VALUE est utilisée pour obtenir la dernière valeur affectée d'une séquence. Pour plus d'informations, voir Référence de séquence.

Syntaxe alternative et synonymes

Pour assurer la compatibilité avec les versions précédentes d' Db2 , ou d'autres produits de la famille d' Db2 , Db2 prend en charge les mots-clés suivants :

NOMINAL (mot-clé unique) comme synonyme de NON MINIMAL
NOMAXVALUE (mot-clé unique) comme synonyme de NO MAXVALUE
NOCYCLE (mot-clé unique) comme synonyme de NO CYCLE
NOCACHE (mot-clé unique) comme synonyme de NO CACHE
NOORDER (mot-clé unique) comme synonyme de NO ORDER

Exemples pour CREATE SEQUENCE

Exemple 1

Créer une séquence nommée « org_seq » qui commence à 1, augmente de 1, ne boucle pas et met en cache 24 valeurs à la fois :

   CREATE SEQUENCE ORDER_SEQ
      START WITH 1
      INCREMENT BY 1
      NO MAXVALUE
      NO CYCLE
      CACHE 24;

INCREMENT 1, NO MAXVALUE et NO CYCLE sont des valeurs par défaut et n'ont pas besoin d'être spécifiées.

Exemple 2

L'exemple suivant montre comment créer et utiliser une séquence nommée « order_seq » dans une table nommée « orders » :

   CREATE SEQUENCE ORDER_SEQ
      START WITH 1
      INCREMENT BY 1
      NO MAXVALUE
      NO CYCLE
      CACHE 20;
     INSERT INTO ORDERS (ORDERNO, CUSTNO)
       VALUES (NEXT VALUE FOR ORDER_SEQ, 123456);

ou pour mettre à jour les commandes :

   UPDATE ORDERS
      SET ORDERNO = NEXT VALUE FOR ORDER_SEQ
      WHERE CUSTNO = 123456;

Exemple 2

L'exemple suivant montre comment utiliser le même numéro de séquence comme valeur de clé unique dans deux tables distinctes en référençant le numéro de séquence avec une expression NEXT VALUE pour la première ligne afin de générer la valeur de séquence et avec une expression PREVIOUS VALUE pour les autres lignes afin de faire référence à la valeur de séquence générée le plus récemment.

   INSERT INTO ORDERS (ORDERNO, CUSTNO)
      VALUES (NEXT VALUE FOR ORDER_SEQ, 123456);
      INSERT INTO LINE_ITEMS (ORDERNO, PARTNO, QUANTITY)
        VALUES (PREVIOUS VALUE FOR ORDER_SEQ, 987654, 100);

Si NEXT VALUE est invoqué dans la même instruction que PREVIOUS VALUE, alors quel que soit leur ordre dans l'instruction, PREVIOUS VALUE renvoie la valeur précédente (non incrémentée) et NEXT VALUE renvoie la valeur suivante.