| 
SSSSSSSSS QQQQQQQQQ LL 44 000000 000000 SS QQ QQ LL 44 44 00 00 00 00 SSSSSSSSS QQ QQ LL === 44444444 00 00 00 00 SS QQ QQ LL 44 00 00 00 00 SSSSSSSSS O QQQQQQQQQ O LLLLLLL 44 000000 000000 Q -------------------------------------------------------------------- STRUCTURED QUERY LANGUAGE |
|
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) |
|
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 (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 |
|
Il est possible d'ajouter/retirer une contrainte par l'ordre ALTER TABLE : |--ADD---> ALTER TABLE nom --- |--DROP--> |--contrainte d'unicité (même syntaxe que CREATE TABLE)---| >---ADD-- | |--contrainte référentielle(même syntaxe que CREATE TABLE-| >---DROP-----PRIMARY KEY------------------------------------------- | | |--FOREIGN KEY-| | |--UNIQUE------|-- nom de la contrainte--| |--CONSTRAINT--| (Vous pouvez aussi utiliser la commande Système WRKPFCST) |
|
Il est possible d'ajouter (ADD), retirer (DROP) ou de modifier (ALTER) une colonne, modifiant ainsi la structure même de la table : ADD ALTER TABLE nom-table-----< ALTER >-COLUMN ---------------> DROP ADD COLUMN = même syntaxe que CREATE TABLE ADD COLUMN-nom-FOR COLUMN-nom/400---Définition-------> >---------------------------------------------------> !--NOT NULL-------------! !--clause WITH DEFAULT--! >-------------------------------------------------------------------- ! ! ! ! !--CONSTRAINT--nom---UNIQUE------------! !-BEFORE--nom-colonne(V7)! !-PRIMARY KEY----! !-REFERENCES ...-! |
|
!--CASCADE---! DROP COLUMN-----nom de zone----- !------ !--RESTRICT--! CASCADE indique que tous les logiques, toutes les vues toutes les contraintes qui s'appuient sur cette colonne sont supprimées. RESTRICT indique que la colonne n'est pas retirée si un des éléments ci-dessus s'appuie sur cette colonne. ALTER COLUMN -------------------------------------------------> !-SET ---DATA TYPE- type de zone---! ! ! !--NOT NULL---------------! >----DROP ---DEFAULT-----------------------------> ! ! !-NOT NULL--! >----(clause WITH DEFAUT)------------------. |
|
La TR2 de la version 7.2 apporte une option à CREATE TABLE CREATE OR REPLACE TABLE offrant les mêmes services que ALTER TABLE ON REPLACE PRESERVE ALL ROWS conservation de toutes les lignes, y compris sur une table partitionnée PRESERVE ROWS (dft) si une plage (pour un table partitionnée) est enlevée, les lignes correspondantes disparaissent DELETE ROWS aucune ligne n'est conservée. |
|
en V4R20 :
On peut indiquer des contraintes de domaine :
il s'agit de définir les valeurs pouvant être placées dans une colonne
par exemple :
CONSTRAINT service_valide CHECK (service IN (10, 20, 30, 40))
ce qui peut être indiqué par l'ordre SQL :
ALTER TABLE personnel ADD CONSTRAINT salairecst
CHECK (salaire < 1000000 AND prime <= salaire)
ou par la commande OS/400 :
ADDPFCST FILE(personnel) TYPE(*CHKCST) CST(salairecst)
CHKCST('salaire < 1000000 AND prime <= salaire')
|
|
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 |
|
Si vous souhaitez ignorer la casse (différence minuscules/majuscules) lors de vos recherches : 1/ utiliser UCASE, par exemple : Select * from VINS where UCASE(cepage) like 'CABERNET%' ->le problème est que SQL ne peut réutiliser alors, aucun index existant. 2/ Utiliser un critère de tri particulier (paramètre SRTSEQ) : *LANGIDSHR ->si vous avez créé un index avec les mêmes attributs, il est utilisé ! avec SDD, vous utilisez STRSEQ( ) et LANGID( ) sur la commande CRTLF avec SQL, indiquez cette propriété pour la session SQL : STRSQL STRSEQ(*LANGIDSHR) LANGID(FRA) sous ODBC/JDBC, onglet "Langue" - Tri par ID Langue - Poids partagé |
|
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. |
|
c) VIEW (Fichier logique format différent) (Projection , Sélection, jonction) CREATE--VIEW -nomvue---------------------------------- I-nom-colonne1, nom-colonne2,-I >--------------(AS SELECT ...FROM ... WHERE ....[cf SELECT]- >----------------------------------------------------------- | | | |-CASCADED-| | |-WITH ----------- CHECK OPTION--| | |-LOCAL----| | La déclaration des noms de colonnes est obligatoire si une colonne est définie 2 fois (jonction) ou n'est pas définie (résultat d'un calcul ou d'une expression). |
|
WITH CHECK OPTION : la clause WHERE doit être vérifiée aussi pour les mises à jour et les insertions. Si vous faites une restriction sur le code société = 01 l'utilisateur ne peut pas insérer(par exemple) des lignes d'une autre société. SQL autorise des vues s'appuyant sur des vues CREATE TABLE t1 .... CREATE VIEW v1 (AS SELECT ... FROM t1 ....) CREATE VIEW v2 (AS SELECT ... FROM v1 ....) avec CASCADED le contrôle est effectué AUSSI par rapport aux sélections de TOUTES les vues sous-jacentes (utilisées dans FROM). avec LOCAL le contrôle n'est effectué que pour les vues sous-jacentes ayant elles même la clause WITH CHECK OPTION. |
|
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 |
|
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. |
|
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 |
|
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. |
|
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-IF EXISTS-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. |
|
7/ Création d'un script (source SQL contenant tous les ordres de création de la base) : CREATE SCHEMA nom -----------------------------------------------> -----------------------------------------------------------------> !--IN ASP x--! !--WITH DATA DICTIONNARY--! --> -----------------------------------------------------------------> ! ! ! ! ! ! ! ! ! TABLE ! ! ! ! ! ! !--CREATE INDEX----! !-COMMENT ON--! !-LABEL ON--! ! VIEW ! ! ----------------------------------------------------------. ! ! ! ! !--GRANT -----! !-- Cet ordre doit être traité par RUNSQLSTM qui exécute un ordre SQL contenu dans un fichier source. (SBMJOB admis et même recommandé). |
|
8/ Renommer --- TABLE (nom de tabvle ou de vue)-- RENAME -- ----> --- INDEX (nom d'index)-------------- >-- TO --- (nouveau-nom)------------------------------- | | | | | |--FOR SYSTEM NAME (nom OS/400)-| | | | |--SYSTEM NAME (nouveau-nom-OS/400)---------- |
|
SQL/400 et OS/400 --------------------- OBJET SQL ! OBJET OS/400 --------- ! ------------- COLLECTION ! *LIB + *JRN + *JRNRCV [ + *DTADCT] ! TABLE ! *FILE/PF (sans clé si créé par SQL) ! toutefois un fichier physique avec clé ! (créé par CRTPF) peut être traité comme ! une table SQL. ! VIEW ! *FILE/LF (fmt différent, sans clé) ! INDEX ! *FILE/LF (même fmt, définition de clé) |
|
+ Les ordres de créations SQL sont maintenant valides dans toute bibli. OS/400 (A l'origine, seules les COLLECTIONS SQL étaient admises) Il est possible de créer dans une collection SQL des index de des vues pointant sur des fichier NON SQL d'une bibliothèque OS/400. + Il est possible de créer (CRTPF,CRTDUPOBJ) de restaurer(RSTOBJ) de déplacer(MOVOBJ) un fichier physique(NON SQL) dans une collection SQL Les catalogues SQL SONT MIS A JOUR La commande CHGOBJOWN qui change le propriétaire d'un objet modifie les catalogues SQL |
|
+ L'ordre SQL LABEL ON modifie le texte de l'objet les COLHDG des zones La commande CHGOBJD modifiant le texte de l'objet modifie le cataloque SQL + L'opération inverse(CRTDUPOBJ,RSTOBJ,MOVOBJ de COLLECTION vers bibliothèque OS/400) est possible. Les catalogues SQL SONT AUSSI MIS A JOUR (le catalogue ne réference plus cette table) + Le fichier RESTE JOURNALISE, reste un objet SQL. il est POSSIBLE de placer dans une collection SQL un fichier logique qui n'a pas été créé par SQL, à l'inverse un index SQL fera une très bonne spécif F en RPG. |
|
CATALOGUE SQL (dans QSYS2) SYSTABLES ! une ligne par table/PF SYSCOLUMNS ! une ligne par colonne de fichier SYSCST ! contraintes SYSKEYCST ! clés de contraintes (UNIQUE et Primary KEY) SYSREFCST ! contraintes de type RI SYSCSTCOL ! colonnes de contraintes RI SYSCSTDEP ! dépendance parent/enfant (RI) SYSCHKCST ! contraintes de type CHECK SYSINDEXES ! une ligne par index SYSKEYS ! une ligne par clé d'index SYSVIEWS ! une ligne par vue SYSVIEWDEP ! dépendance table/PF -> vue SYSLANGS ! compatibilité avec norme ISO (niveau supporté) SYSPACKAGE ! package SQL (DRDA) |
|
SYSPROCS ! une ligne par procédure cataloguée. SYSPARMS ! une ligne par paramètre de procédure SYSFUNCS ! une ligne par fonction (V4R40) SYSROUTINE ! contient l'ensemble des routines SQL (proc+fonctions) SYSTYPES ! une ligne par type créé par l'utilisateur SYSTRIGGER ! une ligne par Trigger SYSSEQ ! une ligne par SEQUENCE (V5R30) -------------------------------------------------------------------------- les fichiers suivants du catalogues SQL sont nouveaux en V6R10 statistiques sur l'activité des tables SYSCOLUMNSTAT SYSPACKAGESTAT SYSINDEXSTAT SYSPROGRAMSTAT SYSMQTSTAT SYSTABLEINDEXSTAT SYSTABLESTAT sur les partitions (membre en langage SQL) SYSPARTITIONSTAT SYSPARTITIONINDEXES SYSPARTITIONMQT SYSPARTITIONINDEXSTAT enfin une ligne par ibliothèque SYSSCHEMAS |
|
IMPORTATION / EXPORTATION deux nouvelles commandes disponibles en V4R30, stables en V4R40 : 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 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
Vous reconnaissez ici tout ce qui permet de constituer un fichier CSV.
EN V4,cette commande génère un fichier en page de code 297 (EBCDIC france)
pour en faire un fichier au format ANSI (Windows LATIN-1) lancez :
CPY OBJ('fichier1') TOOBJ('fichier2') TOCODEPAGE(1252) DTAFMT(*TEXT)
|
|
> En V5R10, les commandes CPYFRMIMPF et CPYTOIMPF possédent de nouveaux paramètres réglant (en autre) le problème ci dessus : STMFCODPAG = pour le choix du code page lors des exportations. *PCASCII = 1252 => l'ANSI de MS-Windows *STDASCII = 850 => l'ASCII DOS. FLDDFNFILE = fichier de définition de zones, pour l'importation. ERRRCDFILE = fichier contenant les enregistrements en erreur. (fichier source dont la longueur est au moins celle du fichier base de données plus 183 c.) RPLNULVAL = remplace les valeurs nulles par les valeurs par défaut. V7 + SF99701 level 14 ou V6 + SF99601 level 25 ORDERBY = ajout d'un critère de tri pour la copie (nous pouvions déjà indiquer une vue en tant que source) |
