Le web de Dominique Guebey – IBM AS/400 iSeries

Page : http://www.dg77.net/tekno/as400/db2sqlddl.htm


   D o m i n i q u e   G u e b e y    J u n g l e     IBM AS/400 iSeries

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

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,