Langage de définition de données SQL400
Introduction
Structured Query Language, dans sa version DB2 pour OS/400, ou SQL/400. SQL comprend plusieurs types d'objets : collections, tables, vues et index, implémentés comme bibliothèques et fichiers AS400. On a un language de définition de données (DDL) et un language de manipulation de données (DML). Il existe de nombreuses façons d'entrer des commandes SQL, par exemple en mode interactif (ISQL) ou avec RUNSQLSTM. Ou encore avec Operation Navigator
Collection
Une collection équivaut à une bibliothèque sur l'AS400. La commande ci-dessous crée une bibliotèque Dg77Dta, crée un jeu de Vues et un journal QSQJRN et QSQJRN0001.
Create Collection Dg77Dta
Table
On spécifie la collection qui contient la table, le nom de la table, une ou plusieurs colonnes, une contrainte de clef primaire (optionnel), une ou plusieurs contraintes de clefs secondaires (optionnel). Le fichier AS400 créé est marqué comme fichier SQL, est limité a un seul membre, n'a pas de taille maximale. Les enregistrements supprimés sont réutilisés, les images "avant" et "après" des enregistrements sont journalisées. Dans l'exemple qui suit, le fichier est indexé par un index interne sur la zone NumCli.
Create Table AppDta/Customer
( NumCli Dec( 7, 0 ) Not Null,
NomCli Char( 30 ) Not Null,
ShpLine1 Char( 100 ) Not Null,
ShpLine2 Char( 100 ) Not Null,
ShpCity Char( 30 ) Not Null,
ShpState Char( 2 ) Not Null,
ShpPsCd1 Char( 10 ) Not Null,
ShpPsCd2 Char( 10 ) Not Null,
ShpCntry Char( 30 ) Not Null,
PhnVoice Char( 15 ) Not Null,
PhnFax Char( 15 ) Not Null,
Status Char( 1 ) Not Null
With Default ' ',
CrdLimit Dec( 7, 0 ) With Default Null,
EntDate Date Not Null,
Primary Key( NumCli ) )
On utilise la dénomination système (*SYS) ex: Dg77Dta/Clients ou SQL (*SQL) Dg77Dta.Clients
Contraintes.
Modification de contraintes de clefs (utilisation de Alter Table).
Alter Table AppDta/Sale
Drop Primary Key
Alter Table AppDta/Sale
Drop Constraint SaleCustFK
Alter Table AppDta/Sale
Add Constraint SaleCustFK Foreign Key( NumCli )
References Customer ( NumCli )
On Delete Cascade
On Update Restrict
Vues
Clients dont la limite de crédit ne dépasse pas 5000
Create View AppDta/CustCrd As
Select *
From AppDta/Customer
Where CrdLimit >= 5000
La vue peut ensuite être utilisée comme toute autre Table.
Update CustCrd Set Status = 'B' Where ShpCity = 'Seattle' And Status = 'X'
Une vue peut ne comprendre qu'une partie des colonnes (=zones)
Create View AppDta/CustShip As
Select NumCli,
ShpLine1,
ShpLine2,
ShpCity,
ShpState,
ShpPsCd1,
ShpPsCd1,
ShpCntry
From AppDta/Customer
La clause From peut traîter plusieurs Tables et Vues. On joint infra les Tables Customer et Sale avec un NumCli similaire.
Create View AppDta/CustSale As
Select Customer.NumCli,
Customer.NomCli,
Sale.OrderID,
Sale.SaleDate,
Sale.SaleTot
From AppDta/Customer,
AppDta/Sale
Where Customer.NumCli = Sale.NumCli
Sortir une ligne pour chaque City ayant au moins un client. Une telle vue ne peut être mise à jour.
Create View AppDta/CustDscAvg
( ShpCity,
AvgDsc )
As Select ShpCity,
Avg( Remise )
From AppDta/Customer
Group By ShpCity
Comme pour les tables, on peut spécifier un nom long de colonne à côté d'un nom système court.
Create View AppDta/CustDscAvg
( CustShipCity For ShpCity,
CustAverageRemise For AvgDsc )
As Select ShpCity,
Avg( Remise )
From AppDta/Customer
Group By ShpCity
Option With Check, qui interdira insertion ou mise-à-jour sur des enregistrements sélectionnés.
Create View AppDta/CustCrd As
Select *
From AppDta/Customer
Where CrdLimit >= 5000
With Check Option
Combinaison d'expressions conditionnelles
Create View AppDta/CustCrd As
Select *
From AppDta/Customer
Where ShpCity <> 'Richmond'
And CrdLimit Between 1000 And 9999
And Status In ( 'A', 'B', 'C' )
Colonnes réordonnnées et renommées (rem: les noms de 6 car. vont permettrent d'utiliser un pgme RPG sans souci).
Create View AppDta/CustRpg
( CsStrt,
CsCity,
CsSt,
CsZip,
CsCrRt,
CsAttn,
CsCnry,
NumCli )
As Select ShpLine2,
ShpCity,
ShpState,
ShpPsCd1,
ShpPsCd2,
ShpLine1,
ShpCntry,
NumCli
From AppDta/Customer
Concaténation de colomnes
Create View AppDta/EmpNamPhn
( EmpID,
PfxVoice,
FullName )
As Select EmpID,
SubStr( PhnVoice, 5, 3 ),
FstNam ConCat MdlInl ConCat LstNam
From AppDta/Employee
Insertion de caractères Espaces.
Strip( FstNam ) ConCat ' '
ConCat Strip( MdlInl ) ConCat ' '
ConCat Strip( LstNam )
Jointure d'une table à elle-même
Create View AppDta/EmpMgr
( EmpID,
LstNam,
MgrEmpID,
MgrLstNm )
As Select Emp.EmpID,
Emp.LstNam,
Emp.MgrEmpID,
Mgr.LstNam
From Employee Emp,
Employee Mgr
Where Emp.MgrEmpID = Mgr.EmpID
Index interne
On peut ajouter des indexes pour un accès plus efficace aux données
Create Index AppDta/CustCtyX01
On AppDta/Customer
( ShpCity,
CrdLimit Desc )
Suppression
On utilise Drop
- Drop Collection collection-name
- Drop Table table-name
- Drop View view-name
- Drop Index index-name
Privilèges
Utilisation de Grant (lecture seule)
Grant Select
On Customer
To SmithJH
Insertion autorisée
Grant Select,
Insert
On Customer,
CustCrd
To SmithJH,
JonesRK
Révocation des droits :
Revoke Select,
Insert
On Customer,
CustCrd
From SmithJH,
JonesRK
Catalogue
Informations sur les bases. Sur AS400, se trouvent dans QSYS2
Select Sys_DName,
Name,
Label
From QSys2/SysTables
Where Type = 'T'
OrderBy Sys_DName,
Name
Tables commençant par "SYS"
Select *
From QSys2/SysColumns
Where DbName = 'QSYS2'
And TbName Like 'SYS%'
Order By TbName,