V7R30

Nouveautés SQL de la version 7.3

Bien sur toutes les nouveautés des technology refresh de la verison 7.2 sont intégrées

Le plus gros des nouveautés 7.3 est lié à SQL

Les maximas évoluent
- Objets dans une bibliothèque : 1.000.000 (avant 360.000)
- Paramètres d'une procédure SQL : 2000 (avant 2014)
- Paramétres d'une fonction : 2000 (avant 90)
- Membres (ou tables) référencés dans une vue : 1000 (avant 256)

DROP SCHEMA .. CASCADE

le message CPA7025, n'est plus envoyé sur les récepteur de journaux non sauvegardés
Ces derniers sont supprimés automatiquement (les pgm sur le point d'exit QIBM_QJO_DLT_JRNRCV ne sont pas appelés)

Clause GENERATED ALWAYS améliorée, pour chaque champ vous pouvez assigner des valeurs pour faciliter l'Audit (qui a fait quoi ?)
Generated Always AS
- DATA CHANGE OPERATION (dernière opération sur la ligne)
  - 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) ,
    action char(1) generated always as (data change operation)
   primary key (nocli)
 )

Grosse avancée : Tables temporelles
- permet d'avoir une trace de ce qui c'est passé
- mais surtout permet d'interroger la base comme si on était à une date antérieure
- exemple
  - le client UN habite Paris
  - le 15 Avril, il déménage à Nantes, le chanceux ;-)
  - select * from clients, vous le montre à Nantes
  - select * from clients for system_time as of '2016-04-01-00.00.00.00000000000'
    
    Vous l'affiche Parisien !

create table clients 
 (nocli int as identity, 
   nomcli char(50), 
   depcli dec(2 , 0) , 
   datcrt date , 
   usrcrt varchar(128)  generated always as (session_user) ,
   action char(1) generated always as   (data change operation), 
  
   debut timestamp(12) not null  generated always as row begin, 
   fin   timestamp(12) not null  generated always as row end, 
   tsid  timestamp(12)   generated always as transaction start id, 
 period system_time (debut , fin),  
 primary key (nocli)
)

Create table clients_histo like clients

--liaison des deux tables
Alter table clients Add Versioning use history table clients_histo 
 (ON DELETE ADD EXTRA ROW)

remarques

les deux tables doivent être dans la même bibliothèque
ces nouveaux champs peuvent être cachés (implicitly hidden)
Start ID, représente le début de la transaction
(5 lignes impactées par un Update ont le même "start id")
lors de l'association des deux tables ON DELETE ADD EXTRA ROW
demande à ce que soit placé dans la table historique, lors d'un Delete, une ligne matérialisant la suppression.

Sous Navigator for i

Définition de CLIENTS

Définition de CLIENTS_HISTO

La version Windows (System i navigator) ne montre pas cette nouveauté.

La génération d'instructions SQL retrouve bien le lien entre les deux tables

->

Pas la version Windows, du moins pas avant SI60523

Après avoir ajouté Quatre clients, puis modifié l'un d'entre eux

select nocli, action, debut, fin from clients

NOCLI ACTION  DEBUT                              FIN 
 1      U    2016-02-11-14.18.48.915480000244  9999-12-30-00.00.00.000000000000 
 2      I    2016-02-08-14.30.33.608585000244  9999-12-30-00.00.00.000000000000 
 3      I    2016-02-08-14.30.58.621274000244  9999-12-30-00.00.00.000000000000 
 4      I    2016-02-08-14.31.14.640404000244  9999-12-30-00.00.00.000000000000

select nocli, action, debut, fin from clients_histo

NOCLI ACTION  DEBUT                              FIN 
 1      I    2016-02-08-14.29.58.915480000244  2016-02-11-14.18.48.915480000244

Mais surtout

Select * From clients

Vous affiche l'état actuel des clients (seule la table CLIENTS est lue), situation "normale".
Select * From clients
for system_time as of '2016-02-10-12.00.00.00000000000'

Vous affiche les clients comme si on était le 10 Février
Select * From clients
for system_time from '2016-02-01-00.00.00.00000000000'
to '2016-02-10-23.59.59.00000000000'

Vous affiche les différents états des clients existant complétement entre le 1er Février et le 10 Février
- un enregistrement vous est affiché si la date de début est > au 1er Février et la date de fin < au 10
- le même client peut vous être affiché plusieurs fois (s'il a été modifié plusieurs fois)
Select * From clients
for system_time between '2016-02-01-00.00.00.00000000000'
and '2016-02-10-23.59.59.00000000000'

Vous affiche les différents états des clients existant entre le 1er Février et le 10 Février
- un enregistrement vous est affiché si la date de début est > ou = au 1er Février et la date de fin < au 10)
- à l'identique, le même client peut vous être affiché plusieurs fois
Vous pouvez aussi renseigner le registre CURRENT TEMPORAL SYSTEM_TIME, par

SET CURRENT TEMPORAL SYSTEM_TIME = CURRENT TIMESTAMP - 1 MONTH (par ex.)
- SELECT * from clients
  ou
- SELECT * from clients FOR SYSTEM_TIME AS OF CURRENT TEMPORAL SYSTEM_TIME
  
  Vous affiche les clients, comme ils étaient il y a un mois
- Par contre, toute requête contenant for system_time alors que le registre CURRENT TEMPORAL SYSTEM_TIME n'est pas nul
  provoque SQ20524 'Period Spécification not Valid'.

Quelques compléments

ALTER TABLE CLIENTS
ADD COLUMN CACLI DEC (11 , 2) NOT NULL WITH DEFAULT

Ajoute aussi CACLI à la table historique (clients_histo)
Catalogue système (dans QSYS2)
- SYSTABLES possède une nouvelle zone TEMPORAL_TYPE
  
  Select * from systables where temporal_type = S
  (donne la liste des tables ayant une table temporelle ==> clients)
- SYSHISTORYTABLES contient la liste des tables temporelles
  
  select * from syshistorytables
  (donne la liste des tables temporelles ==> clients_histo)
- SYSPERIODS contient le détail (champs de début et de fin)
  
  Select * from sysperiods
  (donne la liste des périodes c.a.d les caractéristiques de la clause period )

Statistiques

fonctions d'agrégation

Moyenne, pas très significative (la tête dans le four, les pieds au congélateur, vous êtes à température ambiante)
VARIANCE , sur une population
→ la moyenne des carrés des écarts par rapport à la moyenne
plus la valeur est petite, plus la population est homogène (varie peu)
Ecart-type (STDDEV en SQL)
Racine carrée de la variance

Mesure un écart moyen par rapport à la moyenne générale

Exemple de deux échantillons ayant la même moyenne mais des écarts types différents

Nouvelles fonctions

COVARIANCE(x ,y) (on parle de covariance empirique)

Si la variance permet d’étudier les variations d’une variable par rapport à elle-même, la covariance va permettre d’étudier les variations simultanées de deux variables par rapport à leur moyenne respective.
Du résultat obtenu par cette mesure on en déduit que
- plus la covariance est faible et plus les séries sont indépendantes
- inversement plus elle est élevée et plus les séries sont liées.
COVARIANCE_SAM
Comme la COVARIANCE, mais non biaisée (sur une population de n-1)
- COVARIANCE(X,Y)= SUM( (X - AVG(X)) * (Y – ACG(Y)) ) / COUNT(X)
- COVARIANCE_SAM(X,Y)= SUM( (X - AVG(X)) * (Y – ACG(Y)) ) / COUNT(X-1)
CORRELATION(x , y)

COVARIANCE divisée par le produit des écart-type (compris entre -1 et 1)
- Un signe négatif indique que les deux arguments évoluent en sens contraire (l'un monte, l'autre descend)
- Proche de 0, ils évoluent de manière indépendante (non liée)
- Proche de 1, ils évoluent de manière liée
  ex : température extérieure et consommation de crèmes glacées
- Ou de -1, ils évoluent de manière liée, mais inverse
  ex : température extérieure et consommation de chauffage.
  
  Exemple, avec le nombre de "hits" sur notre site Volubis.fr, qui croît depuis quelques années
  
  La Covariance est peu lisible (ici, exprimée en nombre de "hits")
  
  La correlation avec l'année est avérée et plus simple à lire (proche de 1)
MEDIAN(x)
Valeur médiane, valeur du milieu.

rappel (clic pour agrandir)
PERCENTILE_CONT (continue)
PERCENTILE_DISC (discret = échantillon)
- Ces deux fonctions calculent le « pourcentille », dont on donne la valeur en argument
- Ex : percentile_cont(0,5) donne la valeur médiane : identique à MEDIAN()
- Select percentile_cont(0,33) WITHIN GROUP (order by salary) from employee
  - Tri les lignes par salaire et donne le salaire qui ayant 33 % des lignes au dessus et 66 % en dessous
    
    liste des années qui sont dans le premier tiers
    
    rappel
- Si le nombre de lignes est pair
  - PERCENTILE_CONT Fait la moyenne entre les deux lignes du milieu
  - PERCENTILE_DISC Retourne la première
Régression & Régression linéaire
- Définition
- Il s'agit de voir s'il y a une relation entre deux valeurs
  - la température baisse-t-elle quand la l'altitude augmente, dans une région donnée ?(C'est probable)
  - Le taux d'émission de gaz à effet de serre d'un pays est -il lié à son PIB ?
- Voyons si nous pouvons tracer une courbe(régression) ou une droite (régression linéaire) ayant
  - sur l'axe des X l'altitude ou le PIB
  - et sur l'axe des Y la température ou le taux d'émission des G.E.S
- La droite de régression
  
  Avec les données collectées, nous observons un nuage de points de forme plus ou moins rectiligne.
  
  Comment trouver l'équation de la droite qui le résume au mieux ?
  En minimisant les distances qui la séparent des points. Quelles distances ? Généralement les carrés des distances euclidiennes
  D'où l'expression droite des moindres carrés.
  
  Graphiquement, il s’agit des distances VERTICALES, parallèles à l’axe y.
  Ci-dessus, la flèche noire indique, pour l'observation n° 7, la distance entre le modèle théorique (droite rouge) et la réalité (point bleu).
- Nouvelles fonctions de régression en 7.3 : REGR_nnnn(x, y)
  
  X étant l'ordonnée (le PIB du Pays)
  Y étant l'abscisse (le taux d'émission des G.E.S)
  - REGR_COUNT() -> Nbr de paires non nul
  - REGR_INTERCEPT /REGR_ICPT (ordonnée à l'origine (valeur de x quand y=0), ou constante de régression)
  - REGR_R2 (coefficient de détermination, proche de 1, les points sont tous près de la droite)
  - REGR_SLOPE (la pente)
  - REGR_AVGX (moyenne de x, après élimination des valeurs nulles)
  - REGR_AVGY (moyenne de y, après élimination des valeurs nulles)
  - REGR_SXX ( REGR_COUNT(X, Y) * VARIANCE(Y) )
  - REGR_SXY (REGR_COUNT(X, Y) * COVARIANCE(X, Y) )
  - REGR_SYY ( REGR_COUNT(X, Y) * VARIANCE(X) )
    
    Toujours avec notre historique sur le site Volubis.fr
    
    La pente est montante, plus le n° de l'année augmente, plus le nombre hits augmente.
    
    La pente avec l'ancienneté est descendante, plus l'ancienneté est petite, plus le nombre de "hits" augmente (même valeur, mais négative)
    
    Ordonnées à l'origine, ou, si la pente est toujours vraie, année à laquelle nous étions à 0 hits (ce n'est pas le cas)
- Toutes ces fonctions servent dans des calcul usuels en statistique :
  - Coefficient de détermination ajusté (tiens compte du nombre de valeurs)
    1 - ( (1 - REGR_R2) * ((REGR_COUNT - 1) / (REGR_COUNT - 2)) )
  - Erreur type
    SQRT( (REGR_SYY-(POWER(REGR_SXY,2)/REGR_SXX))/(REGR_COUNT-2) )
  - Somme des carrés totaux (TSS)
    REGR_SYY
  - Somme des carrés de régression (RSS)
    POWER(REGR_SXY,2) / REGR_SXX
  - Somme des carrés des résidus (SSE)
    (TSS)-(RSS)
  - Test t (de student) de la pente
    REGR_SLOPE * SQRT(REGR_SXX) / (Erreur Type)
  - Test t (de student) de l'ordonnée de l 'origine
    REGR_INTERCEPT/((Erreur Type) * SQRT((1/REGR_COUNT)+(POWER(REGR_AVGX,2)/REGR_SXX)))
Fonctions OLAP
- Après
  - ROW_NUMBER() over (order by xxx)
  - RANK() over (order by xxx)
  - DENSE_RANK() over (order by xxx)
- Rappel
  - numérote les lignes
    select ROW_NUMBER() over (), codart, libart from articles order by codart
  - numérote les lignes sur le prix (1 = le prix le plus bas)
    select ROW_NUMBER() over (order by prix), codart, libart from articles order by codart
  - numérote les lignes sur le prix (1 = le prix le plus haut)
    select ROW_NUMBER() over (order by prix DESC), codart, libart from articles order by codart
  - numérote les lignes sur le prix (A l'intérieur d'une FAMILLE)
    select ROW_NUMBER() over (Partition BY FAM Order by prix), codart, libart from articles order by codart
- LAG() over (order by xxx) retourne la valeur de la ligne du dessus, suivant le tri
- LEAD() over (order by xxx) retourne la valeur de la ligne de dessous, suivant le tri
  - Génial pour calculer un % d'évolution (par rapport à l'année précédente, par ex.)
  - On peut préciser un offset (Combien de lignes au dessus/au dessous)

Toutes les fonctions d'agrégation acceptent désormais OVER (Partition BY xx Order By yy)

SELECT cacli, SUM(cacli) over (order by nocli ) as sum
FROM clients

 CACLI              SUM 
    493.00         493.00 
    987.00       1,480.00 
  1,481.00       2,961.00 
  1,974.00       4,935.00 
 10,368.00      15,303.00 
 10,861.00      26,164.00 
 11,355.00      37,519.00 
 11,849.00      49,368.00

SELECT depcli, Sum(cacli) over (partition by depcli order by nocli ) as sum, cacli, nomcli FROM clients order by depcli

DEPCLI    SUM          CACLI     NOMCLI 
 31    1,481.00      1,481.00   Client TROIS
 31   12,836.00     11,355.00   Client 20 et TROIS
 44      493.00        493.00   Client UNO
 44   10,861.00     10,368.00   Client 20 et UNO
 69    1,974.00      1,974.00   Client QUATRE
 69   13,823.00     11,849.00   Client 20 et QUATRE
 75      987.00        987.00   Client DEUX
 75   11,848.00     10,861.00   Client 20 et DEUX

Nouvelles fonctions OLAP (PARTITION BY / ORDER BY ) d'agrégation

NTILE() permet de calculer le quantile

- NTILE(4) la quartile
- NTILE(10) le décile

SELECT NTILE(3) over (order by cacli ) ,
CACLI ,NOMCLI FROM clients

NTILE        CACLI   NOMCLI 
 1           493.00  Client UNO 
 1           987.00  Client DEUX 
 1         1,481.00  Client TROIS 
 2         1,974.00  Client QUATRE 
 2        10,368.00  Client 20 et UNO 
 2        10,861.00  Client 20 et DEUX 
 3        11,355.00  Client 20 et TROIS 
 3        11,849.00  Client 20 et QUATRE

CUME_DIST() Permet de calculer la distribution cumulée (sur le rang), le dernier valant 1

SELECT cume_dist() over (order by cacli ) ,
CACLI ,NOMCLI FROM clients

 CUME_DIST   CACLI    NOMCLI         
  0.125      493.00   Client UNO     
  0.25       987.00   Client DEUX    
  0.375    1,481.00   Client TROIS   
  0.5      1,974.00   Client QUATRE  
  0.625   10,368.00   Client 20 et UNO     
  0.75    10,861.00   Client 20 et DEUX    
  0.875   11,355.00   Client 20 et TROIS   
  1       11,849.00   Client 20 et QUATRE

FIRST_VALUE, la première valeur
LAST_VALUE, la dernière valeur

NTH_VALUE, la énième valeur

SELECT first_value(cacli) over (order by cacli ) , CACLI ,
cacli / first_value(cacli) over (order by cacli) AS NBRDEFOISPLUS
FROM clients

FIRST_VALUE    CACLI      NBRDEFOISPLUS                      
    493.00      493.00   1.00000000000000000000 
    493.00      987.00   2.00202839756592292089 
    493.00    1,481.00   3.00405679513184584178 
    493.00    1,974.00   4.00405679513184584178 
    493.00   10,368.00  21.03042596348884381338 
    493.00   10,861.00  22.03042596348884381338 
    493.00   11,355.00  23.03245436105476673427 
    493.00   11,849.00  24.03448275862068965517

FIRST_VALUE, LAST_VALUE
- Implique un argument
  - la zone
- Possèdent une option
  - RESPECT NULLS | IGNORE NULLS
NTH_VALUE
- Implique deux arguments
  - La zone
  - La position (valeur de n)
- Possède deux options
  - FROM FIRST | FROM LAST
  - RESPECT NULLS | IGNORE NULLS

RATIO_TO_REPORT (% de la somme cumulée, suivant les paramètres de OVER)

SELECT SUM(cacli) over (order by cacli ) , CACLI ,
ratio_to_report(cacli) over (order by cacli) AS RATIO
FROM clients

      SUM       CACLI     RATIO                               
    493.00      493.00    1                       
  1.480.00      987.00    0,666891891891 (1)
  2.961.00    1,481.00    0,500186861187 
  4.935.00    1,974.00    0,4
 15.303.00   10,368.00    0,677514212899 
 26.164.00   10,861.00    0,415112368139
 37.519.00   11,355.00    0.302645659026 
 49.368.00   11,849.00    0,240013774104 (2)

(1) 987 = 66% de 1480
(2) 11849 = 24% de 49368

Exemple avec la fréquentation de Volubis.FR

Effectivement la dernière ligne représente un tiers de la somme cumulée, c.a.d, puisque c'est la dernière, du total.

Ratio sur un classement OLD ou NEW

Ratio sur l'ensemble

Fenêtrage : Pour ces fonctions d'agrégation
- Fonctions d'agréation « traditionnelles » utilisée avec OVER (SUM, AVG, etc...)
- Fonction d'agrégation OLAP (First_value, Last_value, Nth_value, Ratio_to_report)
Il est possible d'indiquer une « fenêtre » de travail

ROWS → fenêtre basée sur les x lignes précédentes et suivantes
RANGE → fenêtre basée sur les x valeurs de clé précédentes et suivantes
Vous pouvez indiquer
- Une position de départ (sous-entendu jusqu'à la ligne en cours)
- Une position d'arrivée (sous-entendu à partir de la ligne en cours)
- Une plage avec BETWEEN début AND fin
- Début (position de départ ou BETWEEN)
  - UNBOUNDED PRECEDING
  - n PRECEDING (n lignes ou clés précédentes)
  - CURRENT ROW
- Fin (position d'arrivée ou BETWEEN)
  - UNBOUNDED FOLLOWING
  - n FOLLOWING (n lignes ou clés suivantes)
  - CURRENT ROW

Exemple

SELECT nocli, avg(cacli) over (order by nocli rows between 2 preceding and current row) as moy, cacli,NOMCLI FROM clients order by nocli

ou bien

SELECT nocli, avg(cacli) over (order by nocli rows 2 preceding) as moy, cacli,NOMCLI FROM clients order by nocli

NOCLI            MOY                        CACLI     NOMCLI             
  1        493.0000000000000000000000       493.00   Client UNO
  2 (1)    740.0000000000000000000000       987.00   Client DEUX
  3        987.0000000000000000000000     1,481.00   Client TROIS
  4      1,480.6666666666666666666666     1,974.00   Client QUATRE
 21 (2)  4,607.6666666666666666666666    10,368.00   Client 20 et UNO
 22      7,734.3333333333333333333333    10,861.00   Client 20 et DEUX
 23     10,861.3333333333333333333333    11,355.00   Client 20 et TROIS
 24 (3) 11,355.0000000000000000000000    11,849.00   Client 20 et QUATRE

Vérifions :

(493+740)/2=740
(1481+1974+10368)/3=4607
(11849+11355+10861)/3=11355

Exemple

Qui aurait pu s'écrire

TR1, nouvelle fonction OLAP
- PERCENT_RANK
  
  % du rang (compris entre 0 et 1)
Rappel sur les tables partitionnées

Historiquement les tables sont partitionnées sur différentes machines (car une seule n'y suffit pas)
Aujourd'hui les tables sont partitionnées sur différents membres pour améliorer les performances

nécessite l'option 27 de 5770SS1

CREATE TABLE Personnel
(matricule INT as identity
..........
DateDepart date)

PARTITION BY RANGE ( datedepart )

(PARTITION p2013 STARTING (‘01/01/2013’) INCLUSIVE ENDING (‘12/31/2013’ ) EXCLUSIVE,

PARTITION p2014 STARTING (‘01/01/2014’) INCLUSIVE ENDING (‘12/31/2014’ ) EXCLUSIVE,

PARTITION p2015 STARTING (‘01/01/2015’) INCLUSIVE ENDING (‘12/31/2015’ ) EXCLUSIVE )

Nouveautés

Il existait déjà un ordre ALTER TABLE DROP PARTITION, qui détruisait le membre
ALTER TABLE DETACH PARTITION

-> le membre concerné, devient une table indépendante
ALTER TABLE ATTACH PARTITION

-> la table indépendante, devient un membre d'une table paritionnée

Sous Navigator for i

Enfin, les différentes Technology Refresh apportent un support de plus en plus complet de JSON