Archives de catégorie : SQL cours

SQL cours

Instruction SQL CREATE INDEX

L’instruction CREATE INDEX est utilisée pour créer des index dans les tables.

Les index sont utilisés pour récupérer les données de la base de données plus rapidement qu’autrement. Les utilisateurs ne peuvent pas voir les index, ils sont juste utilisés pour accélérer les recherches/requêtes.

La mise à jour d’une table avec des index prend plus de temps que la mise à jour d’une table sans (car les index ont également besoin d’une mise à jour). Donc, ne créez des index que sur les colonnes qui seront fréquemment recherchées.

CREATE [UNIQUE] INDEX nom-index
        ON nom-table (nom-colonne [ASC | DESC])
        [CLUSTER | NOT CLUSTER]
        [PARTITIONED]
        [PADDED | NOT PADDED]
        [using-specification]
        [free-specification]
        [DEFINE YES | NO]
        [COMPRESS YES | NO]
        [PARTITION BY RANGE partition-element
                      using-specification
                      free-specification]
        [BUFFERPOOL nom-bp]
        [CLOSE YES | NO]
        [DEFER YES | NO]
        [PIECESIZE integer]
        [COPY YES | NO]


using-specification
        USING
              VCAT nom-catalogue |
              STOGROUP nom-stogroup
                  PRIQTY integer
                  SECQTY integer
                  ERASE YES | NO

free-specification
        FREEPAGE integer
        PCTFREE integer

partition-element
        PARTITION integer
        ENDING AT (constant/MAXVALUE/MINVALUE)

UNIQUE:

Cela empêche la table de contenir deux lignes ou plus avec la même valeur de la clé d’index.

ASC :

  • Spécifie que les entrées d’index doivent être conservées dans l’ordre croissant des valeurs de colonne.
  • Ce sont les paramètres par défauts.

DESC :

Spécifie que les entrées d’index doivent être conservées dans l’ordre décroissant des valeurs de colonne.

CLUSTER ou NOT CLUSTER :

Spécifie si l’index est l’index de clustering de la table ou non.

PARTITIONED :

Spécifie que l’index est des données partitionnées

PADDED ou NOT PADDED :

Spécifie comment les colonnes de chaîne de longueur variable doivent être stockées dans l’index.

  • PADDED : spécifie que les colonnes de chaîne de longueur variable dans l’index sont toujours remplies avec le caractère de remplissage par défaut jusqu’à leur longueur maximale.
  • NOT PADDED : spécifie que les colonnes de chaîne de longueur variable ne doivent pas être remplies à leur longueur maximale dans l’index.

Clause USING :

  • Pour les index non partitionnés, la clause USING indique si les ensembles de données de l’index doivent être gérés par l’utilisateur ou gérés par DB2.
  • VCAT : spécifie que le premier ensemble de données pour l’index est géré par l’utilisateur et que les ensembles de données suivants, si nécessaire, sont également gérés par l’utilisateur.
  • STOGROUP : Spécifie que DB2 définira et gérera les ensembles de données d’index. 
  • PRIQTY : spécifie l’allocation d’espace primaire minimum pour l’ensemble de données gérées DB2.
  • SECQTY : spécifie l’allocation d’espace secondaire minimum pour l’ensemble de données gérées DB2.
  • ERASE : Indique si les ensembles de données gérés par DB2 doivent être effacés lors de la suppression de l’index.

FREEPAGE :

Spécifie la fréquence à laquelle laisser une page d’espace libre lors de la création d’entrées d’index.

PCTFREE :

Détermine le pourcentage d’espace libre à laisser dans chaque page non-feuille et page feuille lorsque des entrées sont ajoutées à l’index.

DEFINE :

Spécifie quand les ensembles de données sous-jacents pour l’index sont physiquement créés.

  • OUI : les ensembles de données sont créés lors de la création de l’index. OUI est la valeur par défaut.
  • NON : les ensembles de données ne sont pas créés tant que les données ne sont pas insérées dans l’index.

COMPRESS :

Spécifie si la compression des données d’index sera utilisée.

  • NO : spécifie qu’aucune compression d’index ne sera utilisée. C’est la valeur par défaut.
  • YES : spécifie que la compression d’index sera utilisée.

PARTITION BY RANGE :

Spécifie que l’index est l’index de partitionnement.

  • PARTITION integer : une clause PARTITION spécifie la valeur la plus élevée de la clé d’index dans une partition d’un index de partitionnement.
  • ENDING AT : Spécifie qu’il s’agit de l’index de partitionnement et indique comment les données seront partitionnées.
    CONSTANT : spécifie une valeur constante avec un type de données qui doit être conforme aux règles d’attribution de cette valeur à la colonne.
    MAXVALUE : spécifie une valeur supérieure à la valeur maximale pour la clé de limite d’une limite de partition.
    MINVALUE : spécifie une valeur inférieure à la valeur minimale de la clé de limite d’une limite de partition.

BUFFERPOOL :

Identifie le pool de mémoire tampon à utiliser pour l’index.

CLOSE :

Spécifie si l’ensemble de données peut ou non être fermé lorsque l’index n’est pas utilisé et que la limite du nombre d’ensembles de données ouverts est atteinte.

  • OUI : Admissible à la fermeture. C’est la valeur par défaut
  • NON : Non éligible à la fermeture.

DEFER :

Indique si l’index est créé lors de l’exécution de l’instruction CREATE INDEX.

  • NON : L’index est construit. C’est la valeur par défaut.
  • OUI : L’index n’est pas construit.

PIECESIZE :

Spécifie l’adressabilité maximale de chaque ensemble de données pour un index.

COPY :

Indique si l’utilitaire COPY est autorisé pour l’index.

  • NON : N’autorise pas l’image complète ou les copies simultanées. NON est la valeur par défaut.
  • OUI : Autorise l’image complète ou les copies simultanées.

Exemple 1:

Créez un index sur la colonne TAB1_COL2 d’une table TB_TAB1.

CREATE INDEX IX_TAB1_COL2
       ON TB_TAB1(TAB1_COL2);

Créez un index nommé NOM_IX_PROJET sur la table TB_PROJET. Le but de l’index est de s’assurer qu’il n’y a pas deux entrées dans la table avec la même valeur pour le nom du projet (NOM_PROJET). Les entrées d’index doivent être dans l’ordre croissant.

CREATE UNIQUE INDEX NOM_IX_PROJET
       ON TB_PROJET(NOM_PROJET);

Créez un index nommé IX_EMPLOYE_JOB_DEPT sur la table TB_EMPLOYE. Organisez les entrées d’index dans l’ordre croissant par intitulé de poste (EMPLOYE_JOB) au sein de chaque département (EMPLOYE_DEPT).

CREATE INDEX IX_EMPLOYE_JOB_DEPT
       ON TB_EMPLOYE (EMPLOYE_DEPT, EMPLOYE_JOB);

Exemple 2 :

Créez un index unique, nommé DSN8910.IX_DEPT, sur la table DSN8910.TB_DEPT. Les entrées d’index doivent être dans l’ordre croissant par la colonne unique DEPT_NO. DB2 doit définir les ensembles de données pour l’index, à l’aide du groupe de stockage DSN8G910.

Chaque ensemble de données doit contenir au maximum 1 Mégaoctet de données. Utilisez 512 Ko comme allocation d’espace primaire pour chaque ensemble de données et 64 Ko comme allocation d’espace secondaire. Remplir l’index.

Les ensembles de données peuvent être fermés lorsque personne n’utilise l’index et n’ont pas besoin d’être effacés si l’index est supprimé.

CREATE UNIQUE INDEX DSN8910.IX_DEPT
        ON DSN8910.TB_DEPT
              (DEPT_NO ASC)
        PADDED
        USING STOGROUP DSN8G910
                       PRIQTY 512
                       SECQTY 64
                       ERASE NO
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 1M;

Explication:

Les ensembles de données sous-jacents pour l’index seront créés immédiatement, ce qui est la valeur par défaut (DEFINE YES). En supposant que la table DSN8910.TB_DEPT est vide, si nous voulions différer la création des ensembles de données jusqu’à ce que les données soient insérées pour la première fois dans l’index, nous spécifierions DEFINE NO au lieu d’accepter le comportement par défaut.

La spécification de PADDED garantit que les colonnes de chaînes de caractères de longueur variable dans l’index sont complétées par des blancs.

Exemple 3 :

Créez un index de cluster, nommé IX_EMP, sur la table TB_EMP dans la base de données DSN8910. Mettez les entrées dans l’ordre croissant par la colonne EMP_NO. Laissez DB2 définir les ensembles de données pour chaque partition à l’aide du groupe de stockage DSN8G910. Faites en sorte que l’allocation d’espace principal soit de 36 kilo-octets et autorisez DB2 à utiliser la valeur par défaut pour SECQTY. Si l’index est supprimé, les ensembles de données n’ont pas besoin d’être effacés.

Il doit y avoir 4 partitions, avec des entrées d’index réparties entre elles comme suit :

  • Partition 1 : entrées jusqu’à H99
  • Partition 2 : entrées au-dessus de H99 jusqu’à P99
  • Partition 3 : entrées au-dessus de P99 jusqu’à Z99
  • Partition 4 : entrées au-dessus de Z99

Associez l’index au pool de mémoire tampon BP1 et autorisez la fermeture des ensembles de données lorsque personne n’utilise l’index. Activez l’utilisation de l’utilitaire COPY pour l’image complète ou les copies simultanées.

CREATE INDEX DSN8910.IX_EMP
        ON DSN8910.TB_EMP
            (EMP_NO ASC)
        USING
            STOGROUP DSN8G910
                PRIQTY 36
                ERASE NO
        CLUSTER
        PARTITION BY RANGE
            (PARTITION 1 ENDING AT('H99'),
             PARTITION 2 ENDING AT('P99'),
             PARTITION 3 ENDING AT('Z99'),
             PARTITION 4 ENDING AT('999'))
        BUFFERPOOL BP1
        CLOSE YES
        COPY YES;
SQL cours

Instruction SQL CREATE TABLE

L’instruction CREATE TABLE est utilisée pour créer une nouvelle table dans la base de données.

Les tables peuvent être créées de deux manières :

  1. En spécifiant explicitement les colonnes et leurs types de données.
  2. Création basée sur la table existante.

Voici la syntaxe de création de table en spécifiant explicitement les colonnes et leurs types de données :

CREATE TABLE table-name
             column-definition
             table-constraint-clause
             physical-storage-clause

column-definition:
nom-colonne data type
    [WITH DEFAULT expression]
    [NULL|NOT NULL]
    [column-constraint-clause]
    [, nom-colonne data type [WITH DEFAULT expression] [NULL|NOT NULL]
    [column-constraint-clause]...]    

Constraint (column-constraint-clause or table-constraint-clause)
[CONSTRAINT constraint-name]
    [REFERENCES table-name [(column-name)]
          [ON DELETE {RESTRICT | CASCADE| SET TO NULL}]
    [UNIQUE]
    [PRIMARY KEY]
    [CHECK (check-condition)]

physical-storage-clause
IN database-name.tablespace-name

Default:

  • Lorsqu’une ligne est insérée dans la table et qu’aucune valeur n’est fournie pour la colonne, la valeur spécifiée dans la clause par défaut est insérée.
  • Si l’expression est manquante dans la clause par défaut, la valeur par défaut définie par le système pour le type de données de la colonne sera remplacée.

NULL / NON NULL :

  • NULL est une valeur par défaut.
  • NOT NULL : empêche la colonne de contenir des valeurs nulles. L’omission de NOT NULL implique que la colonne peut contenir des valeurs nulles.

Constraints :

Les contraintes sont définies à deux niveaux possibles.

  • Niveau colonne :Une contrainte au niveau de la colonne fait référence à une seule colonne et est définie avec la colonne. Celles-ci sont également appelées “contraintes en ligne”
  • Niveau tableau :Une contrainte au niveau de la table fait référence à une ou plusieurs colonnes et est définie séparément après la définition de toutes les colonnes. Celles-ci sont appelées contraintes hors ligne.
  • Vous devez utiliser une contrainte au niveau de la table si vous contraignez plusieurs colonnes.

Constraint-name :

  • Nom de la contrainte et est facultatif.
  • Si aucun nom de contrainte n’est spécifié, un nom de contrainte unique est généré.
  • Si le nom est spécifié, il doit être différent des noms de toute contrainte référentielle, de vérification, de clé primaire ou de clé unique précédemment spécifiée sur la table.

Contrainte FOREIGN KEY

  • La contrainte FOREIGN KEY est définie avec le mot clé REFERENCES.
  • La contrainte FOREIGN KEY (contrainte d’intégrité référentielle), assure que les valeurs de la clé étrangère correspondent aux valeurs d’une clé primaire.
  • Lors de la définition d’une contrainte FOREIGN KEY sur une table, le nom de colonne n’a pas besoin d’être identique au nom de colonne auquel il fait référence.
  • Par défaut, la contrainte de clé étrangère est de type DELETE RESTRICT : les lignes parentes ne peuvent pas être supprimées si des lignes enfants existent.
  • ON DELETE CASCADE : permet la suppression de la ligne de clé primaire et supprime également les lignes de clé étrangère qui s’y rapportent.
  • SET TO NULL : autorise la suppression de la ligne de clé primaire et, au lieu de supprimer toutes les lignes de clé étrangère associées, définit les colonnes de clé étrangère sur NULL.

Contrainte UNIQUE:

  • Pour appliquer des valeurs uniques à une colonne individuelle ou à un groupe de colonnes.
  • La colonne de contrainte UNIQUE ne doit pas contenir de valeurs NULL.
  • Une table peut contenir une ou plusieurs contraintes UNIQUE.

Contrainte PRIMARY KEY :

  • La PRIMARY KEY (CLÉ PRIMAIRE) garantit que toutes les valeurs de la ou des colonnes sont uniques.
  • La clause ne doit pas être spécifiée plus d’une fois et les colonnes identifiées doivent être définies comme NOT NULL.

Remarque : Si vous ne créez pas d’index unique pour une clé primaire ou pour une contrainte d’unicité, une clé incomplète est définie pour la table, rendant la table inaccessible.

CHECK :

Les contraintes CHECK appliquent des expressions logiques sur la ou les colonnes, qui doivent être évaluées comme vraies pour chaque ligne de la table.

Créer des tables basées sur d’autres tables.

Voici la syntaxe de création de table basée sur la table existante :

CREATE TABLE nom-table-destination
       LIKE nom-table-source
       IN nom-database.nom-tablespace

Créez une table TB_TAB1 dans la base de données DB_DB1 et l’espace de table TS_TS1 avec les spécifications suivantes avec les contraintes au niveau des colonnes et avec les noms de contraintes implicites.

Nom de colonneType de donnéesLongueurContrainteRemarques
TAB1_COL1IntegerClé primaire
TAB1_COL2IntegerNon nul
TAB1_COL3Varchar5clé étrangère à la colonne ZIP de la table ZIPCODE – si une ligne de la table ZIPCODE est supprimée, toutes les lignes avec le même code postal doivent être supprimées de la table TAB1
TAB1_COL4DateLa date actuelle doit être insérée par défaut
TAB1_COL5Char20Unique
TAB1_COL6IntegerDoit accepter les valeurs inférieures à 100. La valeur nulle est autorisée.

Voyons comment coder une instruction CREATE TABLE ci-dessous,

CREATE TABLE TB_TAB1
    (TAB1_COL1 INTEGER NOT NULL PRIMARY KEY,
     TAB1_COL2 INTEGER NOT NULL,
     TAB1_COL3 VARCHAR(5) REFERENCES ZIPCODE(ZIP)
          ON DELETE CASCADE,
     TAB1_COL4 DATE WITH DEFAULT,
     TAB1_COL5 CHAR(20) NOT NULL UNIQUE,
     TAB1_COL6 INTEGER CHECK(TAB1_COL6 < 100))
     IN DB_DB1.TS_TS1;

Explication:

  • TAB1_COL1 INTEGER NOT NULL PRIMARY KEY : Lorsque vous définissez une colonne comme clé primaire, elle doit être définie avec “Not Null” (la colonne de clé primaire ne doit pas contenir de valeurs nulles).
  • TAB1_COL5 CHAR (20) NOT NULL UNIQUE : Lorsque vous définissez une colonne avec la contrainte d’unicité, elle doit être définie avec “Not Null” (la colonne de contrainte d’unicité ne doit pas contenir de valeurs Null).
  • TAB1_COL4 DATE WITH DEFAULT: Lors de l’insertion, si vous ne fournissez pas de valeur pour cette colonne, la valeur par défaut de “Date actuelle” pour la variable “Date” sera insérée.

Exemple 2 :

Créez une table TB_TAB1 dans la base de données DB_DB1 et l’espace de table TS_TS1 avec les spécifications suivantes avec les contraintes au niveau de la table en nommant les contraintes de manière exclusive.

Nom de colonneType de donnéesLongueurContrainteRemarques
TAB1_COL1IntegerClé primaire
TAB1_COL2IntegerNon nul
TAB1_COL3Varchar5clé étrangère à la colonne ZIP de la table ZIPCODE – si une ligne de la table ZIPCODE est supprimée, toutes les lignes avec le même code postal doivent être supprimées de la table TAB1
TAB1_COL4DateLa date actuelle doit être insérée par défaut
TAB1_COL5Char20Unique
TAB1_COL6IntegerDoit accepter les valeurs inférieures à 100. La valeur nulle est autorisée.

Voyons comment coder une instruction CREATE TABLE ci-dessous,

CREATE TABLE TB_TAB1
      (TAB1_COL1 INTEGER NOT NULL,
      TAB1_COL2 INTEGER NOT NULL,
      TAB1_COL3 VARCHAR(5),
      TAB1_COL4 DATE WITH DEFAULT,
      TAB1_COL5 CHAR(20) NOT NULL,
      TAB1_COL6 INTEGER NOT NULL,
          CONSTRAINT TAB1_COL1_PK PRIMARY KEY(TAB1_COL1),
          CONSTRAINT TAB1_COL3_FK FOREIGN KEY(TAB1_COL3)
      REFERENCES ZIPCODE(ZIP),
          CONSTRAINT TAB1_COL5_COL6_UK UNIQUE(TAB1_COL5,TAB1_COL6),
          CONSTRAINT TAB1_COL6_CK CHECK(TAB1_COL6 < 100))
      IN DB_DB1.TS_TS1;

Explication:

CONSTRAINT TAB1_COL5_COL6_UK UNIQUE(TAB1_COL5,TAB1_COL6): Comme les deux colonnes TAB1_COL5 et TAB1_COL6 doivent avoir des contraintes uniques, vous avez combiné et créé cette contrainte et ces deux colonnes sont définies avec la clause NOT NULL.

Exemple 3 :

Créez une table TB_TAB1 dans une base de données DB_DB1 et dans un espace table TS_TS1, qui se comporte exactement comme la table TB_TAB2.

CREATE TABLE TB_TAB1 LIKE TB_TAB2
        IN DB_DB1.TS_TS1;
SQL cours

Instruction SQL ALTER TABLE

L’instruction SQL ALTER TABLE est utilisée pour ajouter, supprimer ou modifier des colonnes dans une table existante.

L’instruction ALTER TABLE est également utilisée pour ajouter et supprimer diverses contraintes sur une table existante.

Voici la syntaxe de modification de table :

ALTER TABLE nom_table 
    [ADD column-definition
         table-constraint-clause]
    [ALTER COLUMN column-alteration]
    [DROP CONSTRAINT nom-contraint|
          PRIMARY KEY|
          UNIQUE (nom-colonne [,nom-colonne...])]
    [RENAME COLUMN nom-colonne-source
                TO nom-column-cible]

column-alteration
    [SET DATA TYPE (altered-data-type)]
    [SET default-clause]
    [DROP DEFAULT]

Exemple 1:

La colonne NOM_DEPT dans la table DSN8910.TB_DEPARTMENT a été créée en tant que VARCHAR(36). Augmentez sa longueur à 60 octets. Ajoutez également la colonne CODE_DEPT à la table DSN8910.TB_DEPARTMENT. Décrivez la nouvelle colonne comme une colonne de chaîne de caractères de longueur 5.

ALTER TABLE DSN8910.TB_DEPARTMENT
      ALTER COLUMN DEPT_NAME
            SET DATA TYPE VARCHAR(60)
      ADD DEPT_BLDG CHAR(5);

Exemple 2 :

Modifiez la table TB_PRODINFO pour définir une clé étrangère qui fait référence à une clé unique non primaire dans la table de version du produit (TB_PRODVER). Les colonnes de la clé unique sont NOM_PRODVER et RELNO_PRODVER.

ALTER TABLE TB_PRODINFO
      FOREIGN KEY (NOM_PRODINFO, VERNO_PRODINFO)
      REFERENCES TB_PRODVER (NOM_PRODVER, RELNO_PRODVER)
      ON DELETE RESTRICT;
SQL cours

Instruction SQL CREATE DATABASE

L’instruction CREATE DATABASE est utilisée pour créer une nouvelle base de données DB2.

La syntaxe de création de base de données est la suivante

CREATE



DATABASE nom-database
BUFFERPOOL nom-bp
INDEXBP nom-idx
STOGROUP nom-stogroup
CCSID ASCII/EBCDIC/UNICODE

Explication de la syntaxe :

  • BUFFERPOOL :
    Spécifie le nom du pool de mémoire tampon par défaut à utiliser pour les espaces table créés dans la base de données.
    Si vous omettez la clause BUFFERPOOL, le BP par défaut, BP0 est utilisé.
  • INDEXBP :
    Spécifie le nom du pool de mémoire tampon par défaut à utiliser pour les index créés dans la base de données.
    Si vous omettez la clause INDEXBP, le BP par défaut, BP0 est utilisé.
  • STOGROUP :
    Spécifie le groupe de stockage à utiliser pour prendre en charge les exigences d’espace DASD pour les espaces table et les index dans la base de données. 
    La valeur par défaut est SYSDEFLT.
  • CCSID (Coded Character Set ID) :
    Spécifie le schéma de codage par défaut pour les données stockées dans la base de données.
    Les schémas de codage sont ASCII, EBCDIC, UNICODE.

Exemple 1:

Créez une base de données DSN003 (DATABASE). Spécifiez DSN003 comme groupe de stockage (STOGROUP) par défaut à utiliser pour les espaces table et les index de la base de données. Spécifiez le pool de mémoire tampon de 8 Ko BP8K1 comme pool de mémoire tampon par défaut à utiliser pour les espaces table de la base de données (BUFFERPOOL) et BP2 comme pool de mémoire tampon par défaut à utiliser pour les index de la base de données (INDEXBP).

CREATE


DATABASE DSN003
STOGROUP DSN003
BUFFERPOOL BP8K1
INDEXBP BP2;

Exemple 2 :

Créez une base de données DSN1TEMP. Utilisez les valeurs par défaut pour les noms de groupe de stockage et de pool de mémoire tampon par défaut. Spécifiez ASCII comme schéma de codage par défaut pour les données stockées dans la base de données.

CREATE DATABASE DSN1TEMP CCSID ASCII;
SQL cours

Instruction SQL CREATE TABLESPACE

Vous trouverez l’ensemble des explication concernant le CREATE TABLESPACE.

La syntaxe de création d’espace table est la suivante :

CREATE





















TABLESPACE nom-tablespace
IN nom-database
using-block
free-block
DEFINE YES/NO
LOGGED | NOT LOGGED
TRACKMOD YES/NO
DSSIZE integer
MAXPARTITIONS integer
MEMBER CLUSTER
NUMPARTS integer
PARTITION integer
using-block
free-block
BUFFERPOOL nom-bp
CCSID ASCII/EBCDIC/UNICODE
CLOSE YES/NO
COMPRESS YES/NO
LOCKMAX integer
LOCKSIZE ANY/TABLESPACE/TABLE/PAGE/ROW
MAXROWS integer
SEGSIZE integer
using-block:




USING
VCAT nom-catalog
STOGROUP nom-stogroup
PRIQTY integer
SECQTY integer
ERASE YES/NO
free-block:
FREEPAGE integer
PCTFREE integer

Explication de la syntaxe :

nom-database : si vous omettez nom-database, la base de données par défaut, DSNDB04, est utilisée. Mais il est conseillé de spécifier la base de données.

USING:

VCAT :
Indique que le premier ensemble de données de l’espace table est géré par l’utilisateur et que les ensembles de données suivants, si nécessaire, sont également gérés par l’utilisateur.

STOGROUPE :
Spécifie le groupe de stockage dans lequel les ensembles de données de l’espace table seront définis et gérés par DB2.

  • PRIQTY : spécifie l’allocation d’espace primaire minimum pour un ensemble de données géré par DB2.
  • SEQQTY : spécifie l’allocation d’espace secondaire minimum pour un ensemble de données géré par DB2.
  • ERASE : Indique si les ensembles de données gérés par DB2 pour l’espace table doivent être effacés lorsque l’espace table correspondant est supprimé.
    NON : Il n’efface pas les ensembles de données. C’est la valeur par défaut.
    OUI : Efface les ensembles de données.

FREEPAGE :
Spécifie la fréquence à laquelle une page d’espace libre doit être laissée lorsque l’espace table est chargé ou réorganisé.

Vous devez spécifier un nombre entier compris entre 0 et 255.

Si vous spécifiez 0, aucune page n’est laissée comme espace libre. Sinon, il reste une page libre toutes les n pages, où n est l’entier spécifié.

PCTFREE :
Indique le pourcentage de chaque page à laisser comme espace libre lorsque l’espace table est chargé ou réorganisé.

L’entier peut aller de 0 à 99.

Le premier enregistrement de chaque page est chargé sans restriction. Lorsque des enregistrements supplémentaires sont chargés, il reste au moins un pourcentage entier d’espace libre sur chaque page.

DEFINE:
Indique à quel moment (quand) les ensembles de données sous-jacents de l’espace table sont physiquement créés.
OUI : les ensembles de données sont créés lors de la création de l’espace table. C’est la valeur par défaut.
NON : les ensembles de données ne sont pas créés tant que les données ne sont pas insérées dans l’espace table. DEFINE NO s’applique uniquement aux ensembles de données gérés par DB2 (spécification USING STOGROUP). DEFINE NO est ignoré pour les ensembles de données gérés par l’utilisateur (spécification USING VCAT).

LOGGED:

  • LOGGED : indique que les modifications apportées aux données dans l’espace table spécifié sont enregistrées dans le journal.
  • NOT LOGGED : indique que les modifications apportées aux données dans l’espace table spécifié ne sont pas enregistrées dans le journal.

TRACKMOD :
Indique si DB2 suit les pages modifiées dans les pages de mappe d’espace de l’espace table.
OUI : suivi. C’est la valeur par défaut.
NON : Non suivi.

DSSIZE :
Spécifie la taille maximale de chaque ensemble de données.

MAXPARTITIONS :
Indique le nombre maximal de partitions dans un espace table partitionné.

MEMBRES CLUSTER :
Spécifie que les données insérées par une « opération d’insertion » ne sont pas mises en cluster par l’index de clustering. Au lieu de cela, DB2 choisit l’emplacement des données dans l’espace table en fonction de l’espace disponible.

Index clusterisé et non clusterisé :
Les index sont organisés selon la structure B-Tree.

Index groupé :

  • Le niveau feuille (le niveau le plus bas de l’arborescence) correspond aux données.
  • Pour une table qui a un index clusterisé, les données sont en fait stockées dans l’ordre de l’index.

Index non clusterisé :

  • La feuille contient des signets vers les données réelles.
  • Les signets des index non clusterisés sont au format RID (Row ID : ID de ligne), c’est-à-dire des pointeurs directs vers l’emplacement physique dans lequel la ligne est stockée.

NUMPARTS :

  • Indique que l’espace table est partitionné.
  • L’entier est le nombre de partitions.

PARTITION:

  • Spécifie à quelle partition s’applique le bloc using ou le bloc free suivant.
  • L’entier peut aller de 1 au nombre de partitions donné par NUMPARTS.

BUFFERPOOL:
Identifie le pool de mémoire tampon à utiliser pour l’espace table.

CCSID :
Spécifie le schéma de codage des tables dans le TS.

CLOSE:
Lorsque la limite du nombre d’ensembles de données ouverts est atteinte, spécifie la priorité dans laquelle les ensembles de données doivent être fermés.
OUI : Éligible pour la fermeture d’ensembles de données. C’est la valeur par défaut.
NON : éligible à la fermeture après la fermeture de tous les ensembles de données CLOSE YES.

COMPRESS : 
Indique si la compression des données s’applique aux lignes de l’espace table.
OUI : spécifie la compression des données.
NON : spécifie aucune compression de données.

LOCKMAX : 
Indique le nombre maximal de verrous de page ou de ligne qu’un processus d’application peut contenir simultanément dans l’espace table.

LOCKSIZE : 
Spécifie la taille des verrous utilisés dans l’espace table.

MAXROWS : 
Indique le nombre maximal de lignes que DB2 envisagera de placer sur chaque page de données.

SEGSIZE :

  • Spécifie que l’espace table sera segmenté.
  • Entier spécifie le nombre de pages à affecter à chaque segment de l’espace table. L’entier doit être un multiple de 4 compris entre 4 et 64 (inclus).

Exemple 1:

Créez l’espace table DSN8S91D dans la base de données DSN8D91A. Laissez DB2 définir les ensembles de données à l’aide du groupe de stockage DSN8G910. L’allocation d’espace primaire est de 52 kilo-octets ; le secondaire, 20 kilo-octets. Les ensembles de données n’ont pas besoin d’être effacés avant d’être supprimés. Le verrouillage des tables dans l’espace doit avoir lieu au niveau de la page. Associez l’espace table au pool de mémoire tampon BP1. Les ensembles de données peuvent être fermés lorsque personne n’utilise l’espace table.

CREATE TABLESPACE DSN8S91D
       IN DSN8D91A
             USING STOGROUP DSN8G910
                            PRIQTY 52
                            SECQTY 20
                            ERASE NO
             LOCKSIZE PAGE
             BUFFERPOOL BP1
             CLOSE YES;

Exemple 2 :

Supposons qu’une grande application de base de données de requêtes utilise un tablespace pour enregistrer les données de ventes historiques pour les statistiques marketing. Créez un grand espace de table SALESHX dans la base de données DSN8D91A pour l’application. Créez-le avec 82 partitions, en spécifiant que les données des partitions 80 à 82 doivent être compressées.

Laissez DB2 définir les ensembles de données pour toutes les partitions de l’espace de table, à l’aide du groupe de stockage DSN8G910. Pour chaque ensemble de données, l’allocation d’espace primaire est de 4 000 kilo-octets et l’allocation d’espace secondaire est de 130 kilo-octets. À l’exception de l’ensemble de données pour la partition 82, les ensembles de données n’ont pas besoin d’être effacés avant d’être supprimés. Le verrouillage sur la table doit avoir lieu au niveau de la page.

Il ne peut y avoir qu’une seule table dans un espace de table partitionné. Associez l’espace de table au pool de mémoire tampon BP1. Les ensembles de données ne peuvent pas être fermés lorsque personne n’utilise l’espace de table. S’il n’y a pas d’ensembles de données CLOSE YES à fermer, DB2 peut fermer les ensembles de données CLOSE NO lorsque le DSMAX est atteint.

CREATE TABLESPACE SALESHX
       IN DSN8D91A
             USING STOGROUP DSN8G910
                            PRIQTY 4000
                            SECQTY 130
                            ERASE NO
             NUMPARTS 82
             (PARTITION 80
             COMPRESS YES,
             PARTITION 81
             COMPRESS YES,
             PARTITION 82
             COMPRESS YES
             ERASE YES)
             LOCKSIZE PAGE
             BUFFERPOOL BP1
             CLOSE NO;
SQL cours

Instruction SQL ALTER STOGROUP

Vous trouverez ici les explication concernant ALTER STOGROUP.

La syntaxe suivante est utilisée pour modifier le groupe de stockage :

ALTER      STOGROUP nom-groupe-stockage 
           ADD VOLUMES(id-volume) | 
           REMOVE VOLUMES(id-volume) 
           DATACLAS nom-cc 
           MGMTCLAS nom-mc 
           STORCLAS nom-sc

Explication de la syntaxe :

  • ADD VOLUMES : ajoute des volumes au groupe de stockage.
  • REMOVE VOLUMES : Supprime des volumes du groupe de stockage.

Exemple 1:

Modifier le groupe de stockage DSN001. Ajoutez les volumes DSNV04 et DSNV05.

ALTER        STOGROUP DSN001 
             ADD VOLUMES (DSNV04,DSNV05);

Exemple 2 :

Modifier le groupe de stockage DSN001. Supprimez les volumes DSNV04 et DSNV05.

ALTER        STOGROUP DSN001 
             TO REMOVE VOLUMES (DSNV04,DSNV05);
SQL cours

Instruction SQL CREATE STOGROUP

Vous trouverez ici les information concernant l’instruction SQL CREATE STOGROUP.

La syntaxe suivante est utilisée pour créer un groupe de stockage :

CREATE




STOGROUP nom-groupe-stockage
VOLUMES (volume-id,…) ou VOLUMES (*)
VCAT nom-catalogue
DATACLAS nom-dc
MGMTCLAS nom-mc
STORCLAS nom-sc

Explication de la syntaxe :

  • VOLUMES : définit les volumes du groupe de stockage.
  • (*) : indique que SMS (Storage Management Subsystem) gérera les volumes à utiliser.
  • VCAT : identifie le catalogue d’installations de catalogue intégré pour le groupe de stockage.
  • DATACLAS : identifie le nom de la classe de données SMS à associer au groupe de stockage.
  • MGMTCLAS : identifie le nom de la classe de gestion SMS à associer au groupe de stockage.
  • STORCLAS : identifie le nom de la classe de stockage SMS à associer au groupe de stockage.

Exemple:

Créez un groupe de stockage, DSN001, des volumes COB002 et COM003. DSNCAT est le nom du catalogue de l’installation de catalogue intégré.

CREATE

STOGROUP DSN001
VOLUMES (COB001,COM002)
VCAT DSNCAT;
SQL cours

Clause SQL FETCH

Comment limiter les résultats des requêtes pour les bases de données DB2 ?

Il existe deux façons de limiter le résultat.

  1. LIMIT / OFFSET
  2. FETCH FIRST

Pourquoi avons-nous besoin de limiter les résultats de la requête ?

Supposons que vous écriviez une application qui nécessite des informations uniquement sur les 10 étudiants ayant obtenu les notes totales les plus élevées. Pour renvoyer uniquement les lignes de la table Students pour ces 20 étudiants, cela peut être réalisé par la clause FETCH FIRST ou LIMIT.

De plus, cette clause est utile sur les grandes tables avec des milliers d’enregistrements. Le renvoi d’un grand nombre d’enregistrements peut avoir un impact sur les performances.

Clause d’extraction DB2 :

La clause Fetch permet de limiter le nombre de lignes renvoyées par la requête.

Syntaxe:

SELECT select_list FROM nom_table 
  OFFSET n ROWS
  FETCH {FIRST | NEXT } m {ROW | ROWS} ONLY

Où,

  • ‘n’ est le nombre de lignes à ignorer.
  • ‘m’ est le nombre de lignes à retourner. FIRST et NEXT, ROW et ROWS sont respectivement interchangeables. Ils sont utilisés à des fins sémantiques.

Base de données DB2 :

Vous trouverez ci-dessous une sélection de la table “Produit” de la base de données DB2.

ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7001SourisAccessoires75,00
7002Disque dur65,00201 300
7003ClavierAccessoires36.00331 118,00
7004RAMComposants23.5016376,00
7005VTTvélos1 200
7006STYLO7.451074,50
7007Lecteur CDAccessoires75,00
7008MicroAccessoires75,00

Exemple 1:

La requête SQL suivante utilise la clause FETCH pour obtenir les 4 premiers produits de la table “Produits”.

SELECT *
FROM Produit
  FETCH FIRST 4 ROWS ONLY;
Résultat:
ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7001SourisAccessoires75,00
7002Disque dur65,00201 300
7003ClavierAccessoires36.00331 118,00
7004RAMComposants23.5016376,00

Exemple 2 :

Pour ignorer les 2 premiers produits et retourner les 3 produits suivants, nous utilisons la clause FETCH OFFSET comme suit :

SELECT *
  FROM Produit
  OFFSET 2 ROWS
  FETCH NEXT 3 ROWS ONLY;
Résultat:
ID-ProduitNom-ProduitCategoriePrixStockValeur-Totale
7003ClavierAccessoires36.00331 118,00
7004RAMComposants23.5016376,00
7005VTTvélos1 200
SQL cours

Les Opérateurs SQL

Opérateurs arithmétiques SQL :

OpérateurLa description
+Ajout
–Soustraction
*Multiplication
/Division
%Modulo

Opérateurs de comparaison SQL :

OpérateurLa description
=Égal à
>Supérieur à
<Inférieur à
>=Supérieur ou égal à
<=Inférieur ou égal à
<>Pas égal à

Opérateurs logiques SQL :

OpérateurLa description
ORVRAIE si l’une des conditions séparées par OR est VRAIE
ANDVRAIE si toutes les conditions séparées par AND sont VRAIES
ANYVRAIE si l’une des valeurs de la sous-requête répond à la condition
BETWEENVRAIE si l’opérande est dans la plage de comparaisons
EXISTSVRAIE si la sous-requête renvoie un ou plusieurs enregistrements
INVRAIE si l’opérande est égal à l’une d’une liste d’expressions
LIKEVRAIE si l’opérande correspond à un modèle
NOTAffiche un enregistrement si la ou les conditions sont FAUSSES
ALLVRAIE si toutes les valeurs de la sous-requête remplissent la condition
SOMEVRAIE si l’une des valeurs de la sous-requête répond à la condition
SQL cours

Instruction SQL GRANT

Ici, vous retrouverez les informations concernant l’instruction SQL GRANT.

Accorde des privilèges à un utilisateur pour accéder à un objet DB2.

Émis par un utilisateur pour accorder certains privilèges sur les ressources à un autre utilisateur.

Le format général de la commande est le suivant.

Syntaxe:

GRANT privilege-liste/All
    [ON resource-type resource-liste] TO
    autorisation-id-liste/PUBLIC [WITH GRANT OPTION]

Exemple:

Pour accorder des privilèges SELECT sur la table TB_ETUDIANT à l’utilisateur TOTO

GRANT SELECT
      ON TB_ETUDIANT TO USER TOTO