DB2 UDB for
i5/OS, Administration
I/ Syntaxe SQL
| 
SQL/400 gestionnaire de base de données.
-----------------------------------------
ORDRES SQL EXECUTABLES SUR L'ECRAN SQL-INTERACTIF (strsql)
OU DANS UN SCRIPT SQL (lancé par RUNSQLSTM ou par ISeries Navigatror)
1/ CREATION d'UNE COLLECTION ou SCHEMA
CREATE COLLECTION/SCHEMA -nom-collection---------------
(exemple SQLDB), Sont alors créés :
Objet créé ! NOM ! Type OS/400
------------------------------------------------------
Bibliotheque ! SQLDB ! *LIB
! !
Journal ! SQLDB/QSQJRN ! *JRN
! !
récepteur ! SQLDB/QSQJRN0001! *JRNRCV
|
|
Contenu initial d'une collection SQL
Objet Type Attr Texte
------- ------- ---- -------------------------------
QSQJRN0001 *JRNRCV Base de données créée par SQL
QSQJRN *JRN Base de données créée par SQL
plus les fichiers suivants constituant le catalogue SQL
SYSCOLUMNS *FILE LF une ligne par colonne de table
SYSCST *FILE LF une ligne par contrainte
SYSCSTCOL *FILE LF une ligne par colonne de contrainte
SYSCSTDEP *FILE LF une ligne par dépendance de contrainte
SYSINDEXES *FILE LF une ligne par index
SYSKEYCST *FILE LF une ligne par clé de contrainte
SYSKEYS *FILE LF une ligne par clé d'index
SYSPACKAGE *FILE LF une ligne par package (pgm contenant du SQL)
SYSREFCST *FILE LF une ligne par référence de contrainte
SYSTABLES *FILE LF une ligne par table
SYSVIEWDEP *FILE LF une ligne par dépendance vue/table
SYSVIEWS *FILE LF une ligne par vue
SYSPROCS *FILE LF une ligne par procédure cataloguée
SYSFUNCS *FILE LF une ligne par fonction SQL (UDF)
SYSPARMS *FILE LF une ligne par paramètre (proc ou fonction)
|
CREATE TABLE
|
2/ CREATION D'OBJETS (Possible dans toute bibliothèque)
a) TABLE (Fichier physique sans clé, sauf contrainte)
CREATE TABLE nom
!--> (nomcolonne--FOR COLUMN-nom--type(lg)--------------
!
! >-------------------------------------------------
x ! !-Clause WITH DEFAULT--!
fois !
! >-------------------------------------------------)
--- !--contrainte liée à une colonne--!
>-------------------------------------------------
!--contrainte liée à plusieurs colonnes--!
>-------------------------------------------------
-!-NOT LOGGED INITIALLY--! (V6R10)
>-----------------------------------------------------.
-!-RCDFMT -nom-format-! (V5R40) !- UNIT SSD-(V7)-!
|
|
Exemple :
CREATE TABLE SQLDB/STGTBL
(NUMEROSTAGIAIRE for column NOSTAG DEC(3, 0) not null ,
NOMSTAGIAIRE for column NOM CHAR(15) not null ,
PRENOM CHAR(15) not null with default,
AGENCE NUMERIC(2, 0) not null REFERENCES FAGENCE,
NOTE NUMERIC(4, 2) not null with default, ENTREE DATE )
RCDFMT STGTBLF1
Explications :
Les noms longs sont acceptés :
NUMEROSTAGIAIRE est le nom SQL [30 c pour les colonnes,
128 c pour les objets]
NOSTAG est le nom système
il sont gérés comme des ALIAS pour les colonnes, pour les tables
le nom OS/400 est généré à partir des cinq premièrs caractères,
sauf à ajouter FOR SYSTEM NAME nom-sur-10 (nouveauté V7)
|
|
NOT LOGGED INITIALLY
indique que la table n'est pas journalisée automatiquement
sinon, la table est journalisée automatiquement, si possible :
La journalisation automatique pouvant être définie par :
1/ la présence d'un journal QSQJRN
2/ la présence d'une data area QDFTJRN indiquant le nom du journal
3/ le fait d'avoir utilisé la commande STRJRNLIB sur la bibliothèque
(ce dernier point est aussi nouveau en V6R10)
UNIT SSD
indique une préférence de stockage sur un disque SSD pour cette table.
|
|
Types de variable admis: (l=longueur, d=nbr de décimales)
+ NUMERIC(l, d) numérique étendu
+ DECIMAL(l, d)
ou DEC(l, d) numérique packé
+ SMALLINT binaire sur 2 octets
+ INTEGER binaire sur 4 octets
+ BIGINT binaire sur 8 octets (V4R50)
+ CHARACTER(l)
ou CHAR(l) alphanumérique
+ VARCHAR(x) ALLOCATE(y) alphanumérique à lg variable
x est la plus grande longueur admise
y est la longeur minimum allouée par le système.
[DSPFFD et DSPPFM montre la zone de longueur X]
|
|
+ FLOAT et REAL numérique virgule flottante
(notation scientifique)
+ DATE une date
+ TIME un horaire
+ TIMESTAMP horodatage (date+heure+microsecondes)
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP (V6R10)
attribut ajouté à une zone TIMESTAMP NOT NULL,
indique que cette colonne est modifiée avec le timestamp
en cours à chaque INSERT/UPDATE
Il ne peut y avoir qu'une seule colonne de ce type par table.
|
|
+ BLOB(n K|M) champ binaire (image/vidéo..) dont la
taille peut atteindre 2 Go.
+ CLOB champ texte (notion de CCSID) dont la
taille peut atteindre 2 Go.
+ (un-type) champ faisant référence à un type
créé par l'utilisateur à l'aide de :
CREATE DISTINCT TYPE
exemples :
CREATE DISTINCT TYPE IMAGE AS BLOB(512K)
CREATE DISTINCT TYPE EUROS AS DECIMAL(9, 2)
CREATE DISTINCT TYPE FRANCS AS DECIMAL(9, 2)
|
|
vous pouvez maintenant créer une table par
CREATE TABLE VOITURES
(CODE CHAR(10) NOT NULL PRIMARY KEY,
PRIXf FRANCS NOT NULL, PRIXe EUROS ,
PHOTO IMAGE )
la particularité de ces types est qu'ils sont fortement typés
c'est à dire que l'on ne PEUT PAS COMPARER DES FRANCS et DES EUROS.
WHERE PRIXF > PRIXE ou PRIXE > 50 sont syntaxiquement invalides !!!
le système assure une convertion avec le type d'origine,(CAST ou "type()")
WHERE CAST(PRIXF as DECIMAL(9, 2)) > 10000 est admis
WHERE PRIXF > FRANCS(10000) aussi
|
|
type de données(suite)
+ DATA LINK champ contenant un lien vers un fichier
>--------LINK TYPE URL----->
-------NO LINK CONTROL-----------------------------------
>----- - --
---FILE LINK CONTROL---- -
| INTEGRITY ALL * |
| READ PERMISSION FS |
| READ PERMISSION DB |
|-- WRITE PERMISSION FS ---|
WRITE PERMISSION BLOCKED
RECOVERY NO
ON UNLINK RESTORE
ON UNLINK DELETE
* indique si le fichier reste accessible au système de fichier et avec
quels droit (FS : les droits du système de fichier,
DB : les droits de la base uniquement) |
|
Avec FILE LINK CONTROL, le système vérifie la présence du fichier externe
et garantie son intégrité, tant qu'il est référencé par la base
Il ne peut être référencé que par une seule ligne.
sur AS/400, il faut
initialiser le gestionnaire DLFM (une fois par machine) par INZDLFM
démarrer ce service :
STRTCPSVR SERVER(*DLFM)
indiquer le(les) répertoire(s) dont il faut assurer l'intégrité :
ADDPFXDLFM PREFIX(('/AF4DIR))
enregistrer la bibliothèque contenant des tables avec DataLink :
ADDHDBDLFM HOSTDBLIB((AF400)) HOSTDB(AS400)
|
|
type de données(suite)
- clé générée automatiquement, deux syntaxes :
A/ ROWID. Il s'agit de variable VARCHAR(40) contenant des valeur généré
de manière non consécutive.
Create TABLE test1 (ID ROWID GENERATED ALWAYS ,
LIB CHAR(40), DATCRT DATE)
B/ AS IDENTITY , est associé à un champ numérique sans décimale,
Create TABLE test1 (ID INTEGER AS IDENTITY START WITH 100,
LIB CHAR(40), DATCRT DATE)
> dans les deux cas on peut préciser :
--ALWAYS-------- toujours
GENERATED --- --->
-- BY DEFAULT--- uniquement si la zone est nulle |
|
on peut ensuite préciser, avec AS IDENTITY :
--START WITH (valeur initiale)----------------->
>-INCREMENT BY (incrément)---------------------->
--MINVALUE (valeur mini) -
>-- ----------------->
--NO MINVALUE ---------- -
--MAXVALUE (valeur maxi) -
>-- ----------------->
--NO MAXVALUE ---------- -
-- CYCLE---------- ---ORDER-------
>-- ---- ------->
--NO CYCLE-------- --- NO ORDER---
avec CYCLE, quand MAXVALUE est atteint on recommence à MINVALUE.
NO ORDER indique une numérotation qui peut ne pas être réalisée dans
l'ordre des requêtes.
|
|
et enfin :
-- CACHE x ---- indique que DB2/400 peut garder
>---- -------------- . en mémoire les x dernières valeurs
--NO CACHE---- (plus rapide)
AS IDENTITY est un attribut d'un champ numérique
ROWID est un nouveau type de champ
Exemple :
CREATE TABLE TABCDE
(CDENO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 500
INCREMENT BY 1
CYCLE),
LIVREA VARCHAR (36) ,
CDEDAT DATE)
|
|
type de données(suite, V6)
+ DECFLOAT nouveau format numérique 16 ou 32 Chiffres, à la
norme IEEE 754R permettant le stockage de :
° valeurs décimales très grandes:
16: -9.999999999999999x10 p.384 à 9.999999999999999x10 p.384
32: -9.999999999999999999999999999999999x10 puissance 6144
à 9.999999999999999999999999999999999x10 puissance 6144
° le zéro signé + et -
° la valeur infinie (INFINITY) positive ou négative
° la valeur "quiet NaN" (not a number=NAN)
résultat d'un calcul invalide ne provoquant pas d'erreur
° la valeur "signal NaN"(SNAN)
résultat d'un calcul invalide provoquant une erreur
|
|
+ NCHAR ou NATIONAL CHAR
+ NVARCHAR ou NATIONAL VARCHAR
+ NCLOB ou NATIONAL CLOB
nouveaux types de donnée automatiquement en UNICODE (1200)
+ XML (V7)
Les champs de type XML peuvent faire jusqu'à 2 Go,
la totalité d'une ligne, ne peut pas dépasser 3,5 Go.
Ils sont stockés dans le CCSID indiqué par
SQL_XML_DATA_CCSID dans QAQQINI, UTF-8 (1208) par défaut.
On peut insérer une chaîne ou bien utiliser la fonction
GET_XML_FILE
le document doit être bien formé,
sinon vous recevrez l'erreur SQ20398
|
|
Contrôle de présence (val obligatoire/facultative)
-----------------------------------------------------------------------.
!-- NOT NULL---------------------------------------------------!
!------------------------- WITH DEFAULT---------------------!
!--'constante'----!
!---NULL----------!
!---USER----------!
!--CURRENT_DATE---!
!--CURRENT_TIME---!
!CURRENT_TIMESTAMP!
- pas d'indication = valeur nulle admise
- NOT NULL interdit la valeur nulle
- NOT NULL WITH DEFAULT initialise à valeur par dft
IMPLICITLY HIDDEN (V6, facultatif, NOT HIDDEN est le défaut)
cette colonne n'apparait pas sur un "select * from la-table",
mais uniquement si vous la demandez.
|
|
Contraintes d'entité:
- désignation d'une clé unique (pour les valeurs non nulles)
-------------------------------- UNIQUE--------------------
|--CONSTRAINT nom-contrainte-| |-(zonea, zoneb)-|
+ si CONSTRAINT nom-contrainte n'est pas renseigné le
système génère automatiquement un nom par défaut
+ utilisable associé directement à une colonne
CREATE TABLE (zone1 dec(3,0) UNIQUE,
zone2 char(30), ...
ou fin de définition de table si l'unicité est demandée sur
plusieurs colonnes:
CREATE TABLE (zone1 dec(3, 0),
zone2 char(30),
zone3 dec(2, 0),
UNIQUE(zone1, zone3)) |
|
un index est crée avec la table (il fait partie de la table)
- désignation d'une clé primaire (identifiant univoque).
-------------------------------- PRIMARY KEY---------------
|--CONSTRAINT nom-contrainte-| |-(zonea, ..)-|
+ les clés primaires doivent être définies NOT NULL
+ même syntaxe que l'unicité
+ il peut y avoir plusieurs contraintes d'unicité,
il ne peut y avoir qu'UNE SEULE clé primaire.
CREATE TABLE (zone1 dec(3, 0), ! CREATE TABLE (zone1 dec(3, 0),
zone2 char(30) ! zone2 char(30),
PRIMARY KEY, ! zone3 dec(2, 0),
zone3 dec(2, 0) ! PRIMARY KEY(zone1, zone2))
!
|
|
Contraintes d'intégrité référentielle:
Il s'agit de concrétiser le lien exitant entre deux tables.
(ce qui était réalisé jusqu'ici par programme)
par des déclarations de contraintes
syntaxe générale
---------------------------------------------------------------------->
| | | |
|-CONSTRAINT nom-contrainte-| |-FOREIGN KEY-(zone,...)-|
>------------REFERENCES autre-table-----------------------------
| |
|--(zone1 [, zone2 ...])-|
>---------------------------------------------------------------
| | | |
|--ON DELETE action-| |-- ON UPDATE action-|
|
|
Exemple :
create table command (NUMCDE DEC(6, 0) NOT NULL,
ARTCDE CHAR(6) REFERENCES article,
NUMCLI DEC(6, 0) REFERENCES clients)
ici le code article doit exister dans la table article
le n° de client doit exister dans la table clients.
Comme avec les contraintes d'unicité et de clé primaire, si la clause
CONSTRAINT n'est pas employée, le système génère un nom par défaut.
Ou bien (si la référence porte sur plusieurs colonnes)
FOREIGN KEY(zone1, zone2, ..) REFERENCES table
create table livraison (NUMLIV DEC(6, 0) NOT NULL,
NUMCDE DEC(6, 0) NOT NULL,
NOLIGN DEC(3, 0) NOT NULL,
FOREIGN KEY(numcde, nolign)
REFERENCES lcommand)
ici une livraison doit référencer une commande existante. |
|
ON DELETE, ON UPDATE :
que faire si l'on supprime une ligne du fichier parent
si l'on change la clé dans le fichier parent
NO ACTION : ne rien faire,l'événement est interdit.
le contrôle a lieu lors du COMMIT,
la journalisation est OBLIGATOIRE.
RESTRICT : ne rien faire,l'événement est interdit.
le contrôle est immédiat, la journalisation facultative
CASCADE : en cas de suppression, suppression des lignes associées
dans la table qui référence.
(en cas de suppression d'un client,
suppression de toutes ses commandes)
SET NULL : l'événement est autorisé et la clé étrangère de la table
qui référence est mise à NULL (NULL doit être autorisé)
SET DEFAULT: idem SET NULL avec la valeur/dft
|
|
Check Contraints ou contraintes de domaine :
il s'agit d'établir un contrôle sur une zone (doit être > à , <> de ...)
devant être TOUJOURS vérifié.
ces nouvelles contraintes seront vérifiés dans toutes les conditions
en cas d'erreur ==> message CPF502F
---------------------------------- CHECK ----------------------->
| |
|-CONSTRAINT nom-contrainte-|
---( test logique valide)---.
..........................................................
: Create table entcdep1 Datcde DATE , Datliv DATE :
: Check (datliv > datcmd), ... :
:........................................................:
|
|
la version 5.10 apporte la création de table par copie de structure :
CREATE TABLE2 LIKE TABLE1 (table2 est identique à table1)
CREATE TABLE3 (like table1 , autrezone char(10))
même liste de champs PLUS un/des champ(s) spécifique(s).
SQL admet, en plus, en V5R20 la syntaxe suivante, permettant
une duplication partielle de la liste des zones [du format] :
CREATE TABLE nom-fichier AS (SELECT de sous-sélection)
WITH [NO] DATA
une référence sur la table d'origine est faite (V7 + SF99701 level 24)
avec WITH DATA, les données sont dupliquées suite à la création.
Exemple :
CREATE TABLE CLITMP AS (SELECT nomcli, adr1, adr2, dept, ville from cli)
WITH NO DATA
|
|
Il y a alors des options de copie :
.---------------------------------------------------------------.
| .-COLUMN ATTRIBUTES-. |
| .-EXCLUDING IDENTITY--+-------------------+-. |
V | .-COLUMN ATTRIBUTES-. | |
>----+-+-INCLUDING IDENTITY--+-------------------+-+-------------+-+-->
| .-COLUMN-. |
| .-EXCLUDING--+--------+--DEFAULTS-. |
| | .-COLUMN-. | |
+-+-INCLUDING--+--------+--DEFAULTS-+-----------------------+
| '-USING TYPE DEFAULTS-------------' |
| .-COLUMN ATTRIBUTES-. |
| .-EXCLUDING IMPLICITLY HIDDEN -+-------------------+-. |
| | .-COLUMN ATTRIBUTES-. | |
+-+-INCLUDING IMPLICITLY HIDDEN -+-------------------+-+----+
| .-COLUMN ATTRIBUTES-. |
| .-EXCLUDING ROW CHANGE TIMESTAMP -+-------------------+-. |
| | .-COLUMN ATTRIBUTES-. | |
'-+-INCLUDING ROW CHANGE TIMESTAMP -+-------------------+-+-'
|
| 
Vous pouvez utiliser cette technique pour faire référence à un répertoire
Exemple :
CREATE TABLE ClientsAS (SELECT nomcli, adr1, adr2, dept, ville
from REPERTP1) WITH NO DATA
les REFFLD sont désormais générés pour la table clients
NOCLI CONDEN 6 0 4 1 E-S N°
CLIENT
Texte descriptif de la zone . . . . . . . : N° CLIENT
Information de référence
Fichier référencé . . . . . . . . . . . : REPERTP1
Bibliothèque . . . . . . . . . . . . : FORMATION1
Format référencé . . . . . . . . . . . : REPERTF1
Zone référencée . . . . . . . . . . . . : NOCLI
|
|
Vous pouvez renommer les zones par le biais d'un alias (AS)
Exemple :
CREATE TABLE ClientsAS (SELECT nomcli, adr1 AS ADRCLI , adr2, dept, ville
from REPERTP1) WITH NO DATA
les REFFLD sont désormais générés en conséquence
ADRCLI CONDEN 6 0 4 1 E-S N°
CLIENT
Texte descriptif de la zone . . . . . . . : Adresse client
Information de référence
Fichier référencé . . . . . . . . . . . : REPERTP1
Bibliothèque . . . . . . . . . . . . : FORMATION1
Format référencé . . . . . . . . . . . : REPERTF1
Zone référencée . . . . . . . . . . . . : ADR1
|
CREATE INDEX
|
b) INDEX (Fichier logique même format avec clé)
CREATE-(UNIQUE)-INDEX --nomindex-- ON -nomtable----
----(CLE1 ---ASC---------------,
I-DESC-I
-----CLE2 etc.....).
UNIQUE a la même signification que UNIQUE SDD
Pour chaque zone clé on peut définir un ordre de
classement croissant ou décroissant.
Les index sont de type b-tree.
|
|
+-------+
de A à N |AN - OZ| de O à Z
+-------+
/ \
/ \
+-------+
de A à M |AM - N.| (tout ce qui est Nxxx)
+-------+
/ \
+------+ (tout ce qui est NAxxx)
| A. | ____________
+------+ |TES=878787| <-- NANTES
/ \ /
+------+
| N. |
+------- ____________
\ |CY =124578| <-- NANCY
|
|
Comment ignorer la différence minuscules/MAJUSCULES lors des
recherches sur une chaîne avant la V6 :
1/
utiliser UCASE, par exemple :
Select * from VINS where ucase(cepage) like 'CABERNET%'
le problème c'est que SQL ne
peut réutiliser alors,
aucun index existant.
2/
Utiliser un critère de tri
particulier (paramètre SRTSEQ) : *LANGIDSHR
avec un séquence de tri à poids
partagés, les caractères a, A, â,
ä, à (etc
...) ont
tous aux yeux de SQL, la même valeur
héxadécimale, ce qui permet d'ignorer la casse ET
l'accentuation.
--> Si vous avez créé un index avec les mêmes
attributs,
il est utilisé !
Un index est créé avec ces attributs, si :
a/ avec
SDD, vous utilisez STRSEQ( ) et LANGID( ) sur la commande CRTLF
b/ sous
SQL lors du CREATE INDEX, la session
avait cette valeur en cours
Enfin, pour positionner ce paramètre
sur la session SQL :
- STRSQL
STRSEQ(*LANGIDSHR) LANGID(FRA)
->
identique avec RUNSQLSTM et les commandes de compilation
- sous ODBC,
paramétrez comme ceci
ou bien, indiquez dans la chaine de connexion
- SORTTYPE=2
- SORT WEIGTH=0
- LANGUAGEID = 'FRA'
- sous JDBC
- sort = language
- sort weigth = shared
- sort language = FRA
|
|
En V6, la création d'index subit de nombreux changements, les rendant
proches des fichiers logiques SDD (LF) et réglant le problème précédent.
A/ On admet les expressions en tant que clé
CREATE INDEX i1 on table T1 ( UCASE(NOM) as NOMMAJ )
la zone NOMMAJ est la clé de cet index.
toute requête utilisant WHERE UCASE(NOM) ..., utilisera l'index
l'expression ne peut pas contenir :
- des sous requêtes
- des fonctions agrégées (COUNT, AVG, SUM, etc ...)
- des fonctions NOT DETERMINISTIC (dont le résultat varie), comme
GENERATE_UNIQUE, CURTIME, DAYNAME, MONTHNAME, TIMESTAMPDIFF, etc...
- des UDF sauf celles liées à un nouveau type de données (UDT)
- la manipulation de SEQUENCE
|
|
B/ On admet la clause WHERE sur les index :
CREATE INDEX i1 on table T1 (NOM) WHERE NOM NOT LIKE 'VOL%'
la clause WHERE subit les mêmes règles que les expressions
C/ Vous pouvez préciser un format par la clause RCDFMT
suivi de la phrase suivante :
ADD ALL COLUMNS : toutes les colonnes du PF appartiennent au format
ADD KEYS ONLY : seules les zones clés appartiennent au format
ADD col1, col2 : ces zones font suite aux zones clés dans le format
ATTENTION : la valeur par défaut est ADD KEYS ONLY
et le nom de format par défaut est le nom de l'INDEX
(avant l'index avait le même format que la table)
|
|
Exemple récapitulatif :
CREATE INDEX logi1 on HTTPLOG
( SUBSTR(virtualhost, 1 , 10) VHOST2 )
Where virtualhost IS NOT NULL
RCDFMT httpfmt2 ADD host, logtime -- en plus de VHOST2
Ces index fonctionnent très bien quand il s'agit de retrouver un petit
nombre de clés dans un grand nombre d'enregistrements (accès RPG/COBOL)
mais ils sont assez lents si vous réalisez une requête retournant 80 %
des enregistrements, particulièrement lors des tris.
En effet les enregistrements doivent être retournés dans l'ordre des clés
et ils sont stockés dans physiquement sur le n° de RANG(dans la table)
==> Vous multipliez alors les E/S disque (sauf à utiliser RGZPFM)
Ce sont les seuls utisables à la fois par SQL et par les langages
(RPG, COBOL) en acces direct.
|
|
EVI enfin, qui est un concept Rochester (il y a un brevet), l'AS/400
étant le premier à l'utiliser.
EVI est une utilisation avancèe des index Bitmap
Vecteur
-------------------------- +-------------+
!rang client ville ! ........................... | code | rang |
!------------------------! : Table des symboles : +------+------+
!1 ! 1 ! NANTES ! :.........................: | 1 | 1 |
!2 ! 6 ! ANGERS ! :Valeur:code:Deb:Fin: nbre: | 2 | 2 |
!3 ! 3 ! RENNES ! : : : : : : | 3 | 3 |
!4 ! 7 ! CHOLET ! :NANTES: 1 : 1 : 6 : 2 : | 4 | 4 |
!5 ! 8 ! BREST ! :ANGERS: 2 : 2 : 2 : 1 : | 5 | 5 |
!6 ! 5 ! NANTES ! :RENNES: 3 : 3 : 7 : 2 : | 1 | 6 |
!7 ! 4 ! RENNES ! :CHOLET: 4 : 4 : 4 : 1 : | 3 | 7 |
!8 ! 2 ! VANNES ! :BREST : 5 : 5 : 5 : 1 : | 6 | 8 |
! ! ! ! :VANNES: 6 : 8 : 8 : 1 : +-------------+
!------------------------! :......:....:...:...:.....:
|
|
Le vecteur contient un poste par enregistrement, la position donnant le
n° de rang, MAIS on indique un code et non une valeur de clé.
On indique en plus une table des symboles qui contient
- la correspondance entre chaque valeur de clé et son code associé
- des statistiques destinées au "Query Governor" l'optimiseur de
requête
Il faut simplement indiquer le nombre de valeurs différentes lors de la
création afin de savoir s'il faut créér un vecteur sur 1,2 ou 4 octets
si vous ne connaissez pas ce nombre de valeurs différentes SQL va les
rechercher pour vous et la création de l'index sera un peu plus longu
CREATE ENCODED VECTOR INDEX on fichier(clé1, clé2, ...)
FOR x DICTINCT VALUES
|
|
V7
- Les index avec sélection d'enregistrement(avec une clause WHERE)
sont maintenant pleinement utilisés par le moteur SQL (SQE)
- possibilité d'inclure des fonctions agrégées (SUM,AVG,COUNT,VAR,STDDEV)
dans un index EVI.
CREATE ENCODED VECTOR INDEX EVI01 ON COMMANDES
(DATCMD, FAM)
INCLUDE (SUM(QTE) , COUNT(*) )
Avec le groupe PTF SF99701 level 18, les requêtes utilisant
GROUPING SET, ROLLUP ou CUBE, bénéficient aussi de ce type d'index.
|
CREATE VIEW
|
d) ALIAS (autre nom d'un fichier ou qualification d'un membre)
nouveauté liée à la version 4.30.
- gestion des ALIAS
un ALIAS permet de renommer un fichier (lui donner un nom long)
ou préciser un membre pour un fichier multi-membres.
CREATE ALIAS MABIB/MBR2_ALIAS FOR MABIB/FIC1P1 (MBR2)
mettre à jour MBR2_ALIAS revient à mettre à jour MBR2 dans FIC1P1
ATTTENTION : DROP TABLE nom-alias détruit le fichier PHYSIQUE
il faut écrire DROP ALIAS pour supprimer l'ALIAS
Un alias portant sur un fichier inconnu, peut exister.
|
|
Limitations :
+ n'est pas supporté pas ALTER TABLE, si c'est un ALIAS de membre.
+ n'est pas supporté par la clause FOREIGN KEY (int. référentielle),
si c'est un ALIAS de membre.
+ CREATE TABLE ne peut pas créer une table ayant le même nom qu'un alias
+ idem pour CREATE INDEX et CREATE VIEW.
+ DROP TABLE, GRANT et REVOKE ne sont pas admis pour un alias de membre
on entend par ALIAS de membre, un alias indiquant un membre particulier
|
Variables globales
|
e) Variable globale
on peut maintenant( V7.1), créer des variables globales
elles sont stockées en fait dans des programmes de service (*SRVPGM)
accessibles par toute personne ayant les droits sur l'objet.
le contenu est propre à la session .
Exemple:
CREATE VARIABLE profil CHAR(10) DEFAULT 'QSECOFR'
la variable PROFIL sera créé pour tous les travaux du système et
contiendra QSECOFR.
VALUES profil , permet de l'afficher.
VALUES 'CM' INTO PROFIL (ou bien SET), change son contenu pour le job
la variable est initialisée en début de job, et seul le job peut la
modifier. On dit que la "portée" est limité à la session.
|
|
une variable peut être utilisée dans un trigger et dans une vue :
CREATE VIEW admin as (select * from mesuser where nom = PROFIL)
select * from admin --> montre QSECOFR
set PROFIL = 'CM'
select * from admin --> montre CM
une variable peut être initialisée avec une autre variable
CREATE VARIABLE unprofil CHAR(10) DEFAULT PROFIL
le contenu initial de la variable (DEFAULT) peut être une valeur retournée
par un SELECT
CREATE VARIABLE nbrdeproducteurs INTEGER DEFAULT
(SELECT COUNT(*) FROM PRODUCTEURS)
Créer une variable = créer un programme de service qui peut être sauvegardé
et restauré.Elle est enregistrée dans SYSVARIABLES et SYSVARIABLEDEP.
|
Mise à jour du catalogue
|
3/ MISE A JOUR DU CATALOGUE SQL
a/ mise à jour des labels
LABEL ON TABLE STGTBL IS 'Fichier des stagiaires'
PACKAGE
(==> modification du texte de l'objet)
LABEL ON STGTBL
(NUMSTG IS 'n° de stagiaire',
NOM IS 'nom du stagiaire',
PRENOM IS 'prénom du stagiaire')
LABEL ON COLUMN STGTBL.AGENCE
IS 'n° agence ' [==> COLHDG]
LABEL ON COLUMN STGTBL.AGENCE
TEXT IS 'numéro agence' [==> TEXT)]
|
|
V5R40 LABEL ON INDEX STGTBLI1 IS 'index par nom'
V6R10 LABEL ON CONSTRAINT
FUNCTION
PROCEDURE
TRIGGER
TYPE
b/ mise à jour des commentaires
COMMENT ON ....
+ mise à jour des commentaires dans les catalogues SQL
(==> uniquement la zone REMARKS du catalogue)
même syntaxe que l'ordre LABEL ON , plus
PROCEDURE nom IS .... catalogue SYSPROCS
PARAMETRE procedure.paramètre IS .... catalogue SYSPARMS
|
Droits
|
4/ GESTION DES AUTORISATIONS
ACCORDER DES DROITS
I ---ALL------(tous les droits)---I
I ---ALTER----(*OBJALTER)---------I
I ---DELETE---(*OBJOPR + *DELETE)-I
GRANT ---I ---INDEX----(*OBJMGT)-----------I-------->
I ---INSERT---(*OBJOPR + *ADD)----I
I ---SELECT---(*OBJOPR + *READ)---I
I ---UPDATE---(*OBJOPR + *UPD)----I
I ---REFERENCE(*OBJREF)-----------I
----- ON nom(de table ou de vue)------------------->
----- TO -nomprofil------(1 profil utilisateur)---->
I-PUBLIC----I (*PUBLIC)
-------------------------------------------------.
I---WITH GRANT AUTORITY---I (donne le droit de gérer les droits)
|
|
4/ GESTION DES AUTORISATIONS
REVOQUER DES DROITS
I ---ALL------(tous les droits)---I
I ---ALTER----(*OBJALTER)---------I
I ---DELETE---(*OBJOPR + *DELETE)-I
REVOKE --I ---INDEX----(*OBJMGT)-----------I--------
I ---INSERT---(*OBJOPR + *ADD)----I
I ---SELECT---(*OBJOPR + *READ)---I
I ---UPDATE---(*OBJOPR + *UPD)----I
I ---REFERENCE(*OBJREF)-----------I
----- ON nom(de table ou de vue)-----
--- FROM -nomprofil---. (1 profil utilisateur)
I-PUBLIC----I (*PUBLIC)
|
|
4/ GESTION DES AUTORISATIONS à la colonne .
Depuis la V4R2 il est possible de gérer les droits à la colonne :
GRANT
SELECT ,
UPDATE(numtel,email)
ON TABLE personnel
TO richard
ici, on donne le droit de lectures (toutes colonnes) et le droit
de modifier les colonnes "numtel" et "email" uniquement.
les droits ne sont accordés que via SQL mais peuvent être
visualisés par DSPOBJAUT, EDTOBJAUT puis F16.
Ils sont modifiables aussi par OPERATION NAVIGATOR.
|
Divers
|
5/ GESTION DES VERROUILLAGES (libération lors du COMMIT)
LOCK TABLE -nomtable- IN --SHARE------- MODE
I-EXCLUSIVE--I
SHARE = ALCOBJ *SHRNUP
EXCLUSIVE = ALCOBJ *EXCL
6/ SUPPRIMER
I----TABLE----I
DROP --I----VIEW-----I---nomobjet.
I----INDEX----I
I--COLLECTION-I
I---PACKAGE---I
I----ALIAS----I
ATTENTION: Si vous supprimez une table, SQL supprime de
lui-même tous les index et les vues en relation.
|
|
8/ Renommer
--- TABLE (nom de table ou de vue)--
RENAME -- ---->
--- INDEX (nom d'index)--------------
>-- TO --- (nouveau-nom)-------------------------------
| | | |
| |--FOR SYSTEM NAME (nom OS/400)-| |
| |
|--SYSTEM NAME (nouveau-nom-OS/400)----------
|
II/ Outils de
conception de la base.
|
1/ en mode
caractère (Terminal 5250)
- Tapez STRSQL pour lancer SQL
Interactif et tapez vos commandes
- Si la commande est complexe, demandez de l'aide par F4 (ici CREATE
TABLE)
- En demandant O , dans
l'option CONSTRAINT, un autre écran s'affiche pour saisir la
FOREIGN KEY par exemple.
- Puis,
IMPORTATION / EXPORTATION
CPYTOIMPF (copy to imported file)
et
CPYFRMIMPF (from imported file)
permettent l'importation/exportation de fichiers physiques de/vers des
fichiers à plat avec séparateurs (type CSV).
le principe est de générer un fichier "à plat" à partir d'un fichier BD
(CPYTOIMPF)
ou d'insérer des lignes venant d'une autre base (CPYFRMIMPF)
la fonction CPYTOIMPF génère un fichier type CSV dans IFS.
|
Copier dans fich importation (CPYTOIMPF)
Indiquez vos choix, puis appuyez sur ENTREE.
Fichier d'origine:
Fichier . . . . . . . . . . . > AF4MBRP1 Nom
Bibliothèque . . . . . . . . > AF400 Nom, *LIBL, *CURLIB
Membre . . . . . . . . . . . . *FIRST Nom, *FIRST
Fichier BD de destination:
Fichier . . . . . . . . . . . Nom
Bibliothèque . . . . . . . . *LIBL Nom, *LIBL, *CURLIB
Membre . . . . . . . . . . . . *FIRST Nom, *FIRST
Fichier STREAM de destination . > '/dbfimport/af400bis'
Remplacement ou ajout enregs . . *ADD *ADD, *REPLACE
Cela ressemble à une commande Copie (CPYF), mais :
vers un fichier en interne TOFILE
OU vers un fichier "PC" TOSTMF
|
viennent ensuite les critères de séparateurs et de format :
Délimiteur d'enregistrement . . > *CRLF Valeur alpha, *EOR, *CRLF.
Format fichier d'importation . . *DLM *DLM, *FIXED
Délimiteur de chaîne . . . . . . '"' Valeur alpha, *NONE
Délimiteur de zone . . . . . . . ',' Valeur alpha
Indicateur de zone indéfinie . . *NO *NO, *YES
Symbole décimal . . . . . . . . *PERIOD *PERIOD, *COMMA
Format de date . . . . . . . . . *ISO *ISO, *USA, *EUR, *JIS, *Y
Format d'heure . . . . . . . . . *ISO *ISO, *USA, *EUR, *JIS
depuis la V5R10 :
STMFCODPAG = pour le choix du code page lors des exportations.
*PCASCII = 1252 => l'ANSI de MS-Windows
*STDASCII = 850 => l'ASCII DOS.
RPLNULVAL = remplace les valeurs nulles par les valeurs par défaut.
la commande d'importation (CPYFRMIMPF) fonctionne sur le même modèle
|
|
2/ tapez vos scripts dans un
membre source
- S'il le faut
- créez la
bibliothèque (CRTLIB)
- créez le
fichier source (CRTSRCPF qsqlsrc, par
exemple
)
- lancez PDM (strpdm),
prenez l'option 3 et indiquez votre fichier source
- Ajoutez un nouveau membre
par F6, cela lance la commande STRSEU
- une fois votre script SQL saisi (chaque ordre doit se
terminer par ";"
)
RUNSQLSTM
SRCFILE(MABIB/QSQLSRC) SRCMBR(MONSCRIPT) NAMING(*SYS)
- NAMING(*SYS)
le qualifiant est le caractère "/"
- NAMING(*SQL)
le qualifiant est le caractère "."
EN version 6, RUNSQLSTM admet un paramètre
SRCSTMF indiquant les coordonnées d'un fichier dans l'IFS plutôt
qu'un membre source.
|
3/ avec Operation
Navigator
Avec choix des bibliothèques
à afficher, choisissez les schémas
avec lesquels vous souhaitez travailler.
vous verrez apparaitre la liste
des catégories :
En mode assistance, cliquez avec le bouton
droit : "nouveau/table"
Indiquez ici le nom de la table et le texte associé (50c disponible pour CHAQUE
Objet OS/400)
Le deuxième onglet permet la définition des
colonnes de
cette table
- Survol, propose de copier la définition d'une
colonne venant d'une autre table
- Ajout, créé la colonne de toutes
pieces
notre définition de table terminée, passons aux
onglets contraintes :
- contraintes de clé = primary
key
- contraintes de clé associée = foreign
key
- contraintes de vérification = check
constraint
le bouton "affichage du code SQL" affiche le code
généré, dans le gestionnaire de
scripts (voir plus loin)
Une fois la création demandée, nous pouvons
retrouver le code à partir du catalogue SQL.
Il sera affiché dans le même produit (gestionnaire
de scripts)
• Gestion des index
Vous pouvez demander la liste des index pour un bibliothèque entière
en cliquant sur Tables, ou bien, table par table
cette liste des index affiche les informations nouvelles en V6
-> clé basée sur une expression 
-> clause WHERE de sélection de lignes 
Bien sur, la fenêtre de création d'index a été modifiée
dans ce sens
et 
pour la création de
vues, les fenêtres suivantes vous sont
affichées jusqu'en Version 7 de Client Access:
En V7R10, vous retrouverez une fenêtre plus proche du CREATE TABLE
Mais permettant de lancer l'assistant et de visualiser le résultat du SELECT
Une fois la base créée, System i Navigator permet une administration complète
Avec l'option Ouverture,
vous pourrez
éditer son contenu (Edition en V6),
- mais aussi avec le menu contextuel (clic droit)
- obtenir un aperçu (consultation uniquement)
- voir les caractéristiques techniques d'un fichier (table ou vue),
par Description.
remarquez ici, le nombre maxi d'enregistrements
- réorganiser les fichiers
-
Affichage des contraintes, comme WRKPFCST (V5R30 uniquement)
Avec activation/ désactivation possible (CHGPFCST...)
et liste des contraintes en erreur (au niveau du système)
- gérer la fonction journal
- retrouver l'ordre SQL qui permettrait d'obtenir la même chose (PF
créé par SDD, compris)
- revoir la liste des zones, des contraintes et des triggers (Définition)
• collecte de statistiques (nouveauté de
DB2/400 V5R20 orientée performances)
Affichage des index liés à une table (click droit
sur la table)
Cette dernière option vous affiche des informations nouvelles en
V5R30 concernant l'utilisation des index
En effet, la date de dernière utilisation de l'objet fichier logique,
n'est pas significative dans le cas d'une requête SQL,
où c'est l'optimiseur qui décide de l'utilisation ou non de l'index
(utilisation non explicite).
Les PTF suivantes : SI12938, SI15255, SI13432, SI13245 ET SI16620 (en
France) apportent 4 nouvelles colonnes
- Last query use
Date de dernière utilisation de cet index pour accèder à la
table
- Last query statistic use
Date de dernière utilisation de cet index pour collecter des statistiques
(voir cette nouveauté V5R20)
- Query use count
Nombre d'utilisations de cet index pour accéder à la table
- Query statistic use
Nombre d'utilisations de cet index pour collecter des statistiques
les compteurs sont mis à jour aussi bien par SQE (nouvel optimiseur
V5R20) , que par CQE (l'ancien) ,
mais ne sont à jour que depuis l'installation des PTF vues plus haut.
- Pour terminer, un accès à la
copie de données est proposé directement depuis une table
(V5R40) :
la plupart du temps cela affiche l'invite de la commande :
Sauf pour l'importation/l'exportation, où l'on vous propose un
assistant.
Indiquez des critères de sélections :
- sur une valeur de colonne
- sur le N° de rang : fonction SQL RRN()
Indiquez bien ASCII PC, sinon le fichier résultat est en EBCDIC.
La commande utilisée est CPYTOIMPF, mais à partir d'une vue
crée dans QTEMP si vous avez mis des critères.
• DataBase navigator permet d'avoir une vision
d'ensemble des liens existant entre fichiers (si
intégrité référentielle)
création d' un nouvel organigramme :
Dans la partie gauche de la fenêtre, il faut
renseigner la bibliothèque et choisir "recherche" pour
réactualiser la liste.
la liste des tables vous est affichée et pour
chaque table, les relations base de données
(liste des index, liste des vues)
L'option ajout à l'organigramme, ajoute cette
table et toutes les tables liées à
l'organigramme
 |
vous pouvez demander l'affichage ou le retrait des
éléments suivants (dans l'ordre) :
- les index
- les vues
- les journaux
- les récepteurs de journaux
- les contraintes de clé primaire
- les contraintes de vérification (CHECK)
- les contraintes de clé unique
- les alias de table
- et enfin, les alias de vue.
|
Enfin, vous pouvez lancer "à la main" le gestionnaire de
scripts ou CWBUNDBS.EXE
==> sur le nom de votre système, click droit, puis Exécution de
scripts SQL.
Vous pourrez :
- sauvegarder et relire un script SQL (stockage local ou partage réseau)
- lancer tout ou partie du script (chaque instruction doit être ternimée
par ";" )
- demander l'inclusion des messages Degug et voir l'historique du travail
sur l'AS/400
- modifier vos attributs de requête. (fichier QAQQINI)
Vous pouvez aussi insérer des commandes systèmes en commencant votre ligne par CL:
-
Fichier propose
sauvegarde ou impression de la requête
- Vue affiche l'historique du travail sur
le serveur
-
Options,
propose l'affichage dans une fenêtre séparée.
- Connexion permet de configurer les
paramètres de connexion au serveur (liste de
bibliothèques, etc...)
- Exécution , lancement de tout ou partie du script
la V5R20 apporte une aide précieuse à la saisie d'un
ordre SQL par F4.
Le gestionnaire de script subit encore de nombreux changements en version
6
1/ une option ALLOW SAVE RESULT, permet la sauvegarde des enregistrements extraits:
ensuite, avec un clic droit sur les lignes affichées :
Les formats admis, sont :
- txt
- cvs
- Lotus 123
- tableur Excel
Les paramètres de connexion (JDBC) peuvent être modifiés
temporairement ou définitivement
et proposent maintenant l'affichage des COLHDG plutôt que les noms de
zone en entête de colonne
La(les) requêtes(s) peuvent être sauvegardée(s) sur le serveur
(fichier physique ou IFS)
Ce qui accompagne très bien le nouveau paramètre SRCSTMF de
la commande RUNSQLSTM
Enfin, Vous pourrez, aussi , obtenir des informations d'optimisation sur votre
requête (VISUAL
EXPLAIN)
en V6, Visual Explain 
peut être
lancé et réactualisé,
pendant l'exécution, les informations ayant
bougé sont surlignées.
le Menu Option propose
Pour une surveillance globale des performances base de données, vous aurez
le choix entre :
1/ le moniteur de perf. Base de données
cliquez sur moniteur de Base de données / nouveau ...
remarquez :
- la notion d'utilisateur en cours
- le critère : Adresse Internet (adresse IP)
- l'élimination des requêtes liées à l'activité système
(iSeries navigator lui même, entre autre)
Choix des travaux (comme en V5R30)
- si vous lancez STRDBMON hors de l'interface iSeries Navigator, il faut
ensuite l'importer.
Quand la trace est terminée (l'arrêt est à votre charge),
choisissez une vue (les données à afficher)
- Analyse, propose une vue globale :
- avec des informations, classées par thème :
il s'agit en fait d'ordres
SQL, lancés sur les fichiers
stockés dans la bibliothèque choisie au
début du
paramétrage
.
le menu Fichier/préférences, propose l'affichage des boutons
de modification de la requête
qui vous affichera (pour modification) la requête correspondant à la
ligne active
Vous pouvez modifier cet ordre ....... avec le gestionnaire de scripts :
IBM fournit des requêtes d'exemple afin d'obtenir des
statistiques globales à l'adresse http://www-03.ibm.com/servers/eserver/iseries/db2/dbmonqrys.htm
- "Affichage des instructions", sur un moniteur, propose de nombreux critères
de sélection avant l'affichage détaillé.
2/ l'analyse du cache des plans d'accès SQL
Pour chaque instruction, vous pouvez voir le "plan d'accès"
par Visual Explain.
et enfin, nous pouvons demander à voir les recommandation faites pendant
la constitution de ce cache.
Depuis la V5R40, le système note les index qui lui paraissent
manquant dans QSYS2/SYSIXADV.
Le contenu de ce fichier est affiché par cette option "Assistant
de gestion des index" d'iSeries navigator
Avec
SF99540 (Groupe database,) niveau 4 en V5R30 ou + , le système créé lui
même sous forme d'index temporaires, les index qu'il juge nécessaires.
(fonction MTI soit Maintained Tempory
Indexes), ces index disparaissent à l'IPL.
la nouveauté se trouve tout à droite de cette fenêtre
- Affichage de l'instruction SQL est nouveau en V6
- ainsi que l'accès direct aux instructions qui ont
provoqué cette suggestion (dans le cache, voir ci-dessous)
- l'assistant affiche aussi le nombre de fois ou un index a été suggéré et,
s'il a été créé automatiquement (MTI), le nombre
de fois ou il a été utilisé
Ce compteur peut-être réinitialisé pour la table, par le
menu contextuel suivant :
Copyright
© 1995,2011 VOLUBIS
