DB2 for
i , Administration
I/ Syntaxe SQL DDL
| 
SQL en tant que 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 Access Client Solution)
1/ CREATION d'UNE COLLECTION ou SCHEMA
CREATE COLLECTION/SCHEMA -nom-collection---------------
(exemple SQLDB), Sont alors créés :
Objet créé ! NOM ! Type OS/400
------------------------------------------------------
Bibliothèque ! 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 une nouveauté de la V6)
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 IBM i, 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) |
|
V7.3
- Clause GENERATED ALWAYS améliorée, vous pouvez assigner des valeurs pour faciliter l'Audit
- Generated Always AS
- DATA CHANGE OPERATION (dernière opération sur la ligne, pour les tables temporelles)
- U -> Update
- I -> Insert
- D -> Delete
- « Registre »
- CURRENT CLIENT_ACCTNG
- CURRENT CLIENT_APPLNAME
- CURRENT CLIENT_PROGRAMID
- CURRENT CLIENT_USERID
- CURRENT CLIENT_WRKSTNNAME
- CURRENT SERVER
- SESSION_USER
- USER
- « variable globale »
- QSYS2.JOB_NAME
- QSYS2.SERVER_MODE_JOB_NAME
- SYSIBM.CLIENT_HOST
- SYSIBM.CLIENT_IPADDR
- SYSIBM.CLIENT_PORT
- SYSIBM.PACKAGE_NAME
- SYSIBM.PACKAGE_SCHEMA
- SYSIBM.PACKAGE_VERSION
- SYSIBM.ROUTINE_SCHEMA
- SYSIBM.ROUTINE_SPECIFIC_NAME
- SYSIBM.ROUTINE_TYPE
create table clients
(nocli int as identity,
nomcli char(50),
depcli dec(2 , 0) ,
datcrt date ,
usrcrt varchar(128) generated always as (session_user),
IP varchar(128) generated always as (sysibm.Client_ipaddr),
primary key (nocli)
)
|
|
|
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 |
|
|
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 (ACS) ,
paramétrez comme ceci
- 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 et ACS
|
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 !--IF EXISTS--!
I---PACKAGE---I
I----ALIAS----I (la clause IF EXISTS est apparue fin 2020)
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)----------
TRANSFERT OWNERSHIP
Équivalent à CHGOBJOWN
.-REVOKE PRIVILEGES---.
-TRANSFER OWNERSHIP OF--| object |--TO--| new-owner |--+---------------------+->
'-PRESERVE PRIVILEGES-'
object
|--+-INDEX--index-name-+----------------------------------------|
+-TABLE--table-name-+
+-VIEW--view-name---+
new-owner
|--+-USER--authorization-name-+---------------------------------|
| +-+-CURRENT USER-+---------+
| +-+-SESSION_USER-+---------+
| -USER-------- +
| +--SYSTEM_USER-------------+ |
|
Avancées des versions et intégrité des données
FieldProc
La version 7 de DB2 propose une nouvelle fonctionnalité permettant de crypter le contenu d'une colonne par le nouveau mot-clé FIELDPROC ajouté aux ordres SQL CREATE TABLE et ALTER TABLE
Create table fieldtable
(cle integer ,
zone char(200) FIELDPROC fieldproc1) |
sous ACS
La zone cryptée ne peut pas être :
- une zone de type ROWID
- une zone numérique avec l'attribut AS IDENTITY
- une zone de type TIMESTAMP avec AS ROW CHANGE TIMESTAMP
- un DATALINK
- une zone avec comme valeur par défaut CURRENT DATE/TIME/TIMESTAMP, USER
Programmation :
- la procédure doit être de type PGM/ILE (pas de GAPIII, de java, de programme de service)
- l'algorithme doit être réversible
(si la chaîne 'ABCDEF' est transformée en '123456', '123456' doit produire 'ABCDEF')
- on peut définir des paramètres à envoyer à la procédure, ils sont transmis à chaque appel
- la procédure est appelée lors de la création, pour :
- valider le type de zone (elle peut refuser de travailler avec des zones numériques, par ex.)
- indiquer le type de zone à stocker :
- l'utilisateur saisi du caractère, on stocke du binaire
- lors de la lecture le binaire est transformé à nouveau en caractère
- la procédure est appelée ensuite lors des affectations afin de crypter la donnée, lors des lectures afin de la décrypter
(Ordres SQL ou Entrées/sorties natives)
- Elle peut décrypter la donnée suivant des conditions, et c'est là que c'est intéressant.(l'utilisateur appartient à la DRH ou pas, par ex.)
RCAC option 47 de 5770SS1 (non facturable)
Row and Column Access Control
Il s'agit de pouvoir indiquer des « droits » à la colonne ou à la ligne qui s'appliquent y compris aux personnes ayant les droits d'administrateur
•CREATE MASK indique si une colonne est retournée tel que ou totalement/partiellement masquée ('xxx-xxx-xxx-1234' pour un n° de CB)
CREATE [or REPLACE] MASK tel_MASK ON bdvin1/producteurs
FOR COLUMN pr_tel RETURN
CASE
WHEN SESSION_USER = 'QSECOFR'
THEN PR_TEL
WHEN SESSION_USER = 'CM'
THEN left(pr_tel , 3) concat 'XXXXXXXXXXXXX'
ELSE NULL
END
ENABLE |
Puis
ALTER TABLE bdvin1/producteurs
ACTIVATE COLUMN ACCESS CONTROL |
Attention vous devez avoir les droits QIBM_DB_SECADM (même QSECOFR !)
sinon vous recevrez SQL0552 
Pour donner ces droits WRKFCNUSG
Option 2 pour modifier
Indiquez
- *USED face à Droits spécial *ALLOBJ pour que QSECOFR puisse manipuler ces notions
- *ALLOWED face à Droits par défaut pour que TOUT LE MONDE puisse manipuler ces notions (déconseillé !)
- sinon indiquez un profil à ajouter (Utilisateur) et *ALLOWED (Utilisation) pour autoriser un profil ou un groupe
Les restrictions RCAC s'appliquent dans tous les contextes :
DSPPFM du fichier sous QSECOFR -> 
DSPPFM sous le profil CM -> 
Bien sûr la valeur retournée doit être compatible avec le type de la colonne (sinon vous recevez SQL0678)
ICI avec la colonne CAV_PRIX qui est numérique
•1er test refusé : la valeur de remplacement est caractère (xxxxxxxxxx)
•2eme test accepté : la valeur de remplacement est 0
Un MASK n'empêche pas les insertions (par contre vous ne retrouvez pas forcement la donnée telle que vous l'avez insérée, mais masquée)
Le problème se pose éventuellement lors des mises à jour :
Exemple , le pgm suivant lit et modifie le producteur 1 en RPG ( fichier qui possède un MASK sur PR_TEL)
lors de l'Update RPG, il met à jour la ligne suivant les données qu'il a lui-même reçu.
Suite à un CALL par CM (c'est QSECOFR qui regarde le contenu de la table) :
Y compris un Update RPG avec la fonction %fields
résultat identique
La documentation conseille dans ce cas :
- de faire un trigger, qui rétablisse l'ancienne valeur
- de faire une contrainte qui refuse la valeur produite par le MASK
- Ou mieux, faites une contrainte qui utilise la nouvelle clause (7.2)
ON UPDATE VIOLATION
-
CHECK substr(PR_TEL, 4, 13) <> 'XXXXXXXXXXXXX'
|
L'ancienne valeur sera alors replacée automatiquement, sans message d'erreur
|
Seul un UPDATE SQL (toujours réalisé par CM) , ne met à jour que certains champs (donc ne touche pas aux autres)
et permet d'éviter la création d'une contrainte.
Résultat :
Enfin, les données sont masquées, juste avant l'affichage, c'est à dire après jointure et GROUP BY
par exemple, le SELECT suivant, qui donne le nombre de clients par indicatif téléphonique
SELECT LEFT(PR_TEL, 5) , count(*) as nombre
FROM producteurs
GROUP BY LEFT(PR_TEL, 5) |
Affiche :
| si la colonne n'est pas masquée |
si la colonne est masquée |
|
|
•CREATE PERMISSION indique la(les) règles(s) qui font qu'une ligne peut être vue
Toute ligne ne correspondant pas à la règle n'est pas retournée :
Exemple CM ne doit pas voir l'appellation 13 :
CREATE [or REPLACE] PERMISSION VINS_ROW_ACCESS ON bdvin1/vins
FOR ROWS
WHERE
SESSION_USER <> 'CM'
OR (SESSION_USER = 'CM'
and (appel_code <> 13 or appel_code IS NULL)
)
ENFORCED FOR ALL ACCESS
ENABLE |
rappelez vous, on indique ce qui peut être vu (une affirmation, donc)
Puis
ALTER TABLE bdvin1/vins
ACTIVATE ROW ACCESS CONTROL |
Le système ajoute alors une permission implicite QIBM_DEFAULT_nomdetable_schema où la permission est si 0=1, donc toujours fausse.
seules les permissions explicites autorisent des lignes à être vues (en bref tout ce qui n'est pas autorisé est interdit)
Donc, Attention, si vous enlever la permission sans désactiver ROW ACCESS CONTROL
plus aucune ligne ne peut être extraite (le fichier apparaît toujours comme vide !) |
Avec notre PERMISSION "VINS_ROW_ACCESS":
SELECT COUNT(*) from VINS , sous QSECOFR affiche 25.221
SELECT Count(*) from VINS WHERE appel_code = 13 indique un nombre de 811 vins pour cette appellation
SELECT COUNT(*) from VINS , sous CM affiche 24.410
Une PERMISSION, peut empécher une insertion ou une mise à jour, qui ne respecte pas la règle
Exemple avec APPEL_CODE à 13 (vous recevez SQ20471)
Administration :
pour modifier : ALTER MASK | PERMISSION
pour retirer : DROP MASK | PERMISSION
Les droits RCAC sont visibles (mais non modifiables) par EDTOBJAUT/DSPOBJAUT
L'API Retrieve Database File Description (QDBRTVFD) a été modifiée
(nouveau format FILD0500 pour retrouver les informations RCAC)
Sinon, pour voir la liste des droits RCAC, regarder SYSCONTROLS et SYSCONTROLSDEP
• SysControls de QSYS2
RCAC_SCHEMA
RCAC_NAME
RCAC_OWNER
TABLE_SCHEMA
TABLE_NAME
TBCORRELATION
COLUMN_NAME
SYSTEM_COLUMN_NAME
SYSTEM_TABLE_NAME
SYSTEM_TABLE_SCHEMA
CONTROL_TYPE
ENFORCED
IMPLICIT
ENABLE
CREATE_TIME
LAST_ALTERED
IASP_NUMBER
LABEL
LONG_COMMENT
RULETEXT. |
VARCHAR(128)
VARCHAR(128)
VARCHAR(128)
VARCHAR(128)
VARCHAR(128)
VARCHAR(128)
VARCHAR(128)
CHAR(10)
CHAR(10)
CHAR(10)
CHAR(1) M=Mask | R=Row permission
CHAR(1)
CHAR(1)
CHAR(1)
TIMESTAMP
TIMESTAMP
SMALLINT
VARCHAR(50)
VARCHAR(2000)
DBCLOB -> c'est ici qu'est la règle, en clair. |
Par exemple :
SELECT RCAC_NAME , cast(RULETEXT as char(2000) ) FROM QSYS2.SYSCONTROLS
WHERE RCAC_SCHEMA = 'BDVIN1'
• SYSCONTROLSDEP affiche les éléments dépendants, par exemple :
CREATE TABLE USEROK
(username char(10),
appel_code int
) ;
---------------------------------------------------------------------------
-- l'utilisateur CM ne doit voir que les appellations 13 et 144
INSERT INTO USEROK VALUES('CM', 13);
INSERT INTO USEROK VALUES('CM', 144); |
CREATE PERMISSION PROD_ROW_ACCESS1 ON producteurs
FOR ROWS
WHERE
appel_code in (select appel_code from USEROK
where username = SESSION_USER)
ENFORCED FOR ALL ACCESS
ENABLE ;
---------------------------------------------------------------------------
ALTER TABLE producteurs
ACTIVATE ROW ACCESS CONTROL
ENFORCED FOR ALL ACCESS
ENABLE ; |
SyscontrolsDep contient :
Commandes système :
- les droits RCAC sont stockés dans la table elle-même, ils sont donc :
- sauvegardés par SAVLIB, SAVOBJ
- déplacés par MOVOBJ
- CRTDUPOBJ et CPYLIB
Quand les Data sont dupliquées, elles le sont à l'identiques (en clair) , les droits RCAC étant aussi dupliqués
Mais, il y a un nouveau paramètre ACCCTL
- *ALL, les droits RCAC sont dupliqués
- *ROW, les permissions sont dupliqués
- *COL , les masques sont dupliqués
- *NONE, rien n'est dupliqué
- CPYF
Seules les données étant copiées (pas les droits RCAC), ne sont dupliqués que les lignes autorisées, et éventuellement masquées.
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 IBMi Access Client Solution
Include permet de choisir les bibliothèques à afficher
Toutes les listes peuvent être sauvegardées
Toutes les listes peuvent être paramétrées
(dans l'ordre des trois boutons)
- rafraichissement
- sélection des colonnes
- sélection des lignes (filtre)
Pour créer, en mode assistance, cliquez avec le bouton droit : "nouveau/table"
Passons aux contraintes
- contraintes de clé = primary
key
- contraintes de clé associée = foreign
key
- contraintes de vérification = check
constraint
Primary Key
Foreign Key
Check Constraint
Retrouvons l'ordre SQL qui va être lancé (Show SQL)
• Création d'un Index
• Création d'une vue
• Création d'une fonction (ici Externe, c'est à dire existant déjà en tant qu'objet *PGM ou *SRVPGM))
Dont vous pourrez modifier les caractèristiques ensuite
• Création d'une procédure (toujours externe)
• Création d'un Trigger (SQL cette fois)
Choix des événements déclencheurs
Corps du Trigger
• Création d'une permission (qui a le droit à la ligne ? dans le cadre de RCAC)
• Création d'un masque (qui a le droit de voir la colonne ? toujours RCAC)
• Création d'un index Omnifind
• Création d'un Alias
• Création d'une Séquence
• Création d'une Variable globale
Une fois la base créée, Schémas permet une administration complète
Vous trouverez une gestion des droits partout (Schéma, Table, etc...)
Sur une table
View (affichage des données)
Description (des détails comme DSPFD)
Nous pourrons retrouver l'ordre SQL généré (ici sur un trigger)
Remarquez le choix de la sortie (fichier PC ou fichier source sur le i)
• Pareil sur les fonctions
• Vous pouvez activer/désactiver les triggers
•
• Vous pouvez activer/désactiver les contraintes
• Enfin, un accès aux :
- journaux et récepteurs de journaux
Saisie des critères
:
Quand il y a des données sur les lignes (code R = Update,Insert,Delete) -> view details
- Réorganisation (RGZPFM) des tables
-
Statistiques
d'utilisation des index
ACS, montre les index liés à une
table (clic droit sur la table)
Ce dernier vous affiche des informations, nouvelles en
V5R40 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).
Vous avez ici, 4 colonnes remarquables
Enfin, vous pouvez lancer le gestionnaire de
scripts 
le gestionnaire de scripts
SQL
Vous pouvez :
-
sauvegarder
et relire un script SQL
-
lancer
tout ou partie du script
-
demander
l'inclusion des messages Debug et voir l'historique du travail sur
le serveur
- modifier vos attributs de
requête. (fichier QAQQINI)
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
Plusieurs paramètrages de la connexion (JDBC) peuvent
être mémorisés
Vous choisissez ensuite comme ceci :
Vous avez accès aux travaux et à la dernière requête SQL pour chacun d'eux (Affichage/SQL details for a Job)
Enfin, la(les) requêtes(s) peuvent être
sauvegardée(s) (uniquement en local)
Enfin, Vous pourrez, aussi , obtenir des informations d'optimisation sur votre
requête (VISUAL
EXPLAIN)
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.
Depuis performance center, choisissez l'option 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
et récapitulatif final.
Quand la trace est terminée (l'arrêt est
à votre charge), choisissez une vue (les données
à afficher)
- Analyse, propose une
vue globale :
"Affichage des instructions", sur un moniteur, propose de
nombreux critères de sélection avant l'affichage détaillé.
2/ Cache des Plans d'accès
Le jeu d'instructions actuellement en cache pouvant être
sauvegardé sous forme d'image (snapshot).
(sinon, il y a mise à blanc à l'IPL)
•Depuis Performance Center
- Quand le cache est plein il est automatiquement épuré,
il est possible de placer un moniteur sur cet événement
afin de le sauvegarder en fichier avant (Event Monitor)
Tout en gardant la possibilité de choisir les instructions sauvegardées
(comme un moniteur)
3/ Analyse des index suggérés
-
lors de la création des plans d'accès,
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'ACS
Depuis la V5R4, le système créé lui
même sous forme d'index temporaires,
les index qu'il juge nécessaires . (fonction MTI soit Maintained
Temporary Indexes), ces index disparaissent à l'IPL.
-
vous pourrez le constater, toujours en demandant l'assistant
de gestion d'index
(sur la machine , sur un nom de schéma, sur un nom de table).
la nouveauté se trouve tout à droite de
cette fenêtre
- Affichage de l'instruction SQL(Show SQL) montre l'ordre CREATE INDEX
- ainsi que l'accès direct aux instructions qui
ont provoqué cette suggestion (dans le cache, voir ci-dessus)
- 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 :
Reset Usage Counts
