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).
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.
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;
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.
LIMIT / OFFSET
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-Produit
Nom-Produit
Categorie
Prix
Stock
Valeur-Totale
7001
Souris
Accessoires
75,00
7002
Disque dur
65,00
20
1 300
7003
Clavier
Accessoires
36.00
33
1 118,00
7004
RAM
Composants
23.50
16
376,00
7005
VTT
vélos
1 200
7006
STYLO
7.45
10
74,50
7007
Lecteur CD
Accessoires
75,00
7008
Micro
Accessoires
75,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-Produit
Nom-Produit
Categorie
Prix
Stock
Valeur-Totale
7001
Souris
Accessoires
75,00
7002
Disque dur
65,00
20
1 300
7003
Clavier
Accessoires
36.00
33
1 118,00
7004
RAM
Composants
23.50
16
376,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;
Comment concaténer deux colonnes dans une requête DB2?
La fonction DB2 CONCAT combinera deux expressions distinctes pour former une expression de chaîne unique.
Vous pouvez également combiner deux expressions distinctes pour former une seule expression de chaîne en utilisant ‘||’ notation (double tuyau).
Concaténation : Il s’agit de joindre des valeurs ensemble (en les ajoutant les unes aux autres) pour former une seule valeur longue. Dans les instructions SQL SELECT, vous pouvez concaténer des colonnes à l’aide d’un opérateur spécial “||” ou en utilisant la fonction CONCAT.
Syntaxe 1 : Utilisation des champs de la base de données
SELECT CONCAT(champ_1, champ_2) FROM nom_table ;
ou
SELECT champ_1 || champ_2 FROM nom_table ;
Syntaxe 2 : Utilisation d’expressions de chaîne
SELECT CONCAT(chaîne_1, chaîne_2) FROM nom_table ;
ou
SELECT chaîne_1 || chaîne_2 FROM nom_table ;
Syntaxe 3 : Utilisation des champs de base de données et des expressions de chaîne
SELECT CONCAT(chaîne_1, chaîne_2) FROM nom_table ;
ou
SELECT chaîne_1 || chaîne_2 FROM nom_table ;
Base de données DB2 :
Vous trouverez ci-dessous une sélection de la table “Produit” de la base de données DB2.
ID-Produit
Nom-Produit
Categorie
Prix
Stock
Valeur-Totale
7001
Souris
Accessoires
75,00
7002
Disque dur
Composants
65,00
20
1 300
7003
Clavier
Accessoires
36.00
33
1 118,00
7004
RAM
Composants
23.50
16
376,00
7005
VTT
vélos
1 200
7006
STYLO
Papeterie
7.45
10
74,50
7007
Lecteur CD
Accessoires
75,00
7008
Micro
Accessoires
75,00
Exemple 1 : Utilisation de || Opérateur
SELECT Nom_Produit || ' - ' || Categorie AS ProduitType
FROM Produit ;
Voici la liste des fonctions de manipulation de date et d’heure couramment utilisées :
Fonction
La description
Exemple
CHAR
Renvoie une représentation sous forme de chaîne de son premier argument.
CHAR(client_embauchedate,États-Unis)
DAYS
Renvoie une représentation entière de son argument.
DAYS(‘2008-01-01’)
YEAR
Renvoie la partie année de son argument
YEAR(client_embauchedate)
MONTH
Renvoie la partie mois de son argument
MONTH(client_embauche)
DAY
Renvoie la partie jour de son argument
DAY(client_embauchedate)
HOUR
Renvoie la partie heure de son argument
HOUR(CURRENT TIME)
MINUTE
Renvoie la partie minute de son argument
MINUTES (CURRENT TIME)
SECOND
Renvoie la partie minute de son argument
SECOND(CURRENT TIME)
MICROSECOND
Renvoie la partie microseconde de son argument
MICROSECOND(CURRENT TIMESTAMP)
DATE
Renvoie la date dérivée de son argument
DATE(‘2008-01-01’)
TIME
Renvoie le temps dérivé de son argument
TIME(’13:00:00′)
TIMESTAMP
Renvoie timestamp dérivé de son argument
TIMESTAMP(CURRENT DATE)
Voyons les exemples ci-dessous,
Exemple 1:
Comment obtenir la date, l’heure et l’horodatage actuels ?
SELECT current date FROM sysibm.sysdummy1;
SELECT current time FROM sysibm.sysdummy1;
SELECT current timestamp FROM sysibm.sysdummy1;
Exemple 2 :
Comment obtenir l’année, le mois, le jour, l’heure, les minutes, les secondes et les microsecondes à partir de timestamp actuel ?
SELECT YEAR (current timestamp) FROM sysibm.sysdummy1;
SELECT MONTH (current timestamp) FROM sysibm.sysdummy1;
SELECT DATE (current timestamp) FROM sysibm.sysdummy1;
SELECT TIME (current timestamp) FROM sysibm.sysdummy1;
SELECT DAY (current timestamp) FROM sysibm.sysdummy1;
SELECT HOUR (current timestamp) FROM sysibm.sysdummy1;
SELECT MINUTE (current timestamp) FROM sysibm.sysdummy1;
SELECT SECOND (current timestamp) FROM sysibm.sysdummy1;
SELECT MICROSECOND (current timestamp) FROM sysibm.sysdummy1;
Exemple 3 :
Comment effectuer des calculs de date et d’heure ?
SELECT current date + 2 YEAR FROM sysibm.sysdummy1;
SELECT current date + 1 YEARS + 10 MONTHS FROM sysibm.sysdummy1;
SELECT current date + 1 YEARS + 5 MONTHS + 10 DAYS FROM sysibm.sysdummy1;
SELECT current time + 5 HOURS ‑ 3 MINUTES + 10 SECONDS FROM sysibm.sysdummy1;
Exemple 4 :
Comment trouver le nombre de jours entre deux dates, vous pouvez soustraire des dates comme ci-dessous :
SELECT days (current date) ‑ days (date('1990‑09-02')) FROM sysibm.sysdummy1;
Exemple 5 :
Si vous souhaitez concaténer des valeurs de date ou d’heure avec un autre texte, vous devez d’abord convertir la valeur en une chaîne de caractères. Pour ce faire, vous pouvez simplement utiliser la fonction CHAR() :
SELECT "Current Date -" || char(current date) FROM sysibm.sysdummy1;
SELECT "Current Time -" || char(current time) FROM sysibm.sysdummy1;
SELECT "Current Date with 12 hours -" char(current date + 10 hours) FROM sysibm.sysdummy1;
Exemple 6 :
Si vous souhaitez convertir une chaîne de caractères en une valeur de date ou d’heure, vous pouvez utiliser comme ci-dessous :
SELECT TIMESTAMP ('1990‑09‑02‑12.00.00.000000') FROM sysibm.sysdummy1;
SELECT TIMESTAMP ('1990‑09‑02 12:00:00') FROM sysibm.sysdummy1;
SELECT DATE ('1990‑09‑02') FROM sysibm.sysdummy1;
SELECT DATE ('02/09/1990') FROM sysibm.sysdummy1;
SELECT TIME ('10:00:00') FROM sysibm.sysdummy1;
SELECT TIME ('10.00.00') FROM sysibm.sysdummy1;
Exemple 7 :
Comment changer le format date/heure ? Voyons le formatage de la date dans différents formats.
SELECT
CHAR(DATE (CURRENT TIMESTAMP),ISO) AS date_en_iso,
CHAR(DATE (CURRENT TIMESTAMP),USA) AS date_en_usa,
CHAR(DATE (CURRENT TIMESTAMP),EUR) AS date_en_eur,
CHAR(DATE (CURRENT TIMESTAMP),JIS) AS date_en_jis
FROM sysibm.sysdummy1;
Résultat:
Nom de colonne
Valeur
date_en_iso
2022-09-02
date_en_usa
2022/09/02
date_en_eur
2022.09.02
date_en_jis
2022.09.02
Exemple 8 :
Voyons comment convertir le format de date à l’aide de la fonction VARCHAR_FORMAT.
SELECT
VARCHAR_FORMAT(CURRENT TIMESTAMP,'YYYY-MM-DD') AS VCHAR_FORMATE,
DATE(CURRENT TIMESTAMP) AS DATE_VALEUR
FROM sysibm.sysdummy1;
Résultat:
VCHAR_FORMATE
DATE_VALEUR
2013-04-11
04/11/2013
Exemple 9 :
L’instruction SQL suivante montre comment utiliser la fonction VARCHAR_FORMAT dans la clause where.
SELECT * FROM Employe_table
WHERE VARCHAR_FORMAT (date_col,'YYYY-MM-DD') = '1990-09-02'
Les autres fonctions importantes de date et d’heure sont les suivantes :
DAYNAME : renvoie une chaîne de caractères à casse mixte contenant le nom du jour (par exemple, vendredi) pour la partie jour de l’argument.
DAYOFWEEK : renvoie le jour de la semaine dans l’argument sous la forme d’une valeur entière comprise entre 1 et 7, où 1 représente le dimanche.
DAYOFWEEK_ISO : renvoie le jour de la semaine dans l’argument sous la forme d’une valeur entière comprise entre 1 et 7, où 1 représente le lundi.
DAYOFYEAR : renvoie le jour de l’année dans l’argument sous la forme d’une valeur entière comprise entre 1 et 366.
JULIAN_DAY : renvoie une valeur entière représentant le nombre de jours entre le 1er janvier 4712 av. J.-C. (le début du calendrier julien) et la valeur de date spécifiée dans l’argument.
MIDNIGHT_SECONDS : renvoie une valeur entière comprise entre 0 et 86 400 représentant le nombre de secondes entre minuit et la valeur d’heure spécifiée dans l’argument.
MONTHNAME : renvoie une chaîne de caractères à casse mixte contenant le nom du mois (par exemple, janvier) pour la partie mois de l’argument.
TIMESTAMP_ISO : renvoie une valeur d’horodatage basée sur la date, l’heure ou l’argument d’horodatage.
TIMESTAMP_FORMAT : renvoie un horodatage à partir d’une chaîne de caractères qui a été interprétée à l’aide d’un modèle de caractères.
TIMESTAMPDIFF : renvoie une estimation du nombre d’intervalles du type défini par le premier argument, basé sur la différence entre deux horodatages.
TO_CHAR : renvoie une représentation sous forme de caractères d’un horodatage qui a été formaté à l’aide d’un modèle de caractères. TO_CHAR est un synonyme de VARCHAR_FORMAT.
TO_DATE : renvoie un horodatage à partir d’une chaîne de caractères qui a été interprétée à l’aide d’un modèle de caractères. TO_DATE est un synonyme de TIMESTAMP_FORMAT.
SEMAINE : Renvoie la semaine de l’année de l’argument sous la forme d’une valeur entière comprise entre 1 et 54. La semaine commence par le dimanche.
WEEK_ISO : renvoie la semaine de l’année de l’argument sous la forme d’une valeur entière comprise entre 1 et 53.
