Archive pour la catégorie : SQL_DB2

Vous êtes ici : / SQL_DB2
, , Informations sur les SAVF

Les groupes DB2 pour la TR2 de la V7R5 et de la TR8 de la V7R4 sont disponibles, une des nouveautés c’est les vues sur les fichiers de sauvegarde

La première vue sur les SAVF SAVE_FILE_INFO permet d’avoir des informations sur le SAVF

par exemple
vous voulez connaitre les SAVF qui date de plus de 6 mois

select * from QSYS2.SAVE_FILE_INFO
where SAVE_TIMESTAMP < current date – 6 month
order by SAVE_TIMESTAMP desc

Vous pouvez par exemple utiliser la fonction SQL QCMDEXC pour faire le ménage plus d’informations ici
https://www.gaia.fr/qcmdexc-en-fonction-sql/

La deuxième vue sur les objets sauvegardés dans les SAVF, QSYS2.SAVE_FILE_OBJECT permet d’avoir des informations sur les ojets contenus dans le SAVF

Par exemple vous voulez savoir, si un objet est dans une sauvegarde et sa date de sauvegarde

select * from QSYS2.SAVE_FILE_OBJECTS
where OBJECT_NAME = ‘votre objet’ and OBJECT_TYPE = ‘votre type’
order by SAVE_TIMESTAMP desc

Attention
Ces fonctions sont à lancer en batch, c’est des informations qui mettent du temps à être extraites

/par
, Migration en V7R4 et IFS

Il est possible que vous ayez un effet de bord sur les fichiers téléchargés par FTP ou SFTP par exemple dans vos cpyfrmstmf ou cpyfrmimpf vous avez un plantage.

Les fichiers étaient jusqu’ici encodés en CCSID = 819 et maintenant par défaut, ils sont encodés en CCSID = 1208

Le problème est référencé ici

https://www.ibm.com/support/pages/after-v7r4-upgrade-encoding-scheme-not-compatible-utilizing-gifsgatecpyfrmstmf-gentran-server-iseries

En résumé, vous devez rajouter une variable d’environnement et redémarrer vos services

Voici comment ?

// Ajout d’une variable d’environnement

ADDENVVAR ENVVAR(PASE_DEFAULT_UTF8) VALUE(N) LEVEL(*SYS)

// Redémarrage du FTP

STRTCPSVR SERVER(*FTP)

ENDTCPSVR SERVER(*FTP)

// Redémarrage du SFTP

ENDTCPSVR SERVER(*SSHD)

STRTCPSVR SERVER(*SSHD)

Conclusion :

Ca vous permet de contourner le problème, mais le mieux est de passer en unicode donc CCSID = 1208

/par
Les fonctions géospatiales

Premier test sur les fonctions géospatiales, elles sont désormais intégrées à DB2

Vous pouvez indiquer les coordonnées GPS d’un lieu dans une zone, et vous pourrez ensuite faire des calculs,
de distance , de superficie, etc …

C’est des zones de type QSYS2.ST_POINT, par exemple pour indiquer des coordonnées GPS, mais vous pouvez également indiquer des formes comme des lignes ou des polygones ..

Voici un premier exemple

— Création table des salariés avec leur lieu de travail

create table GDATA.SALARIE
(ID int,
NOM varchar(30),
PRENOM varchar(30),
MAISON QSYS2.ST_POINT,
TRAVAIL QSYS2.ST_POINT);

— Insertion dans la table des informations
Vous pouvez les trouver ici les coordonnées GPS:
https://www.coordonnees-gps.fr/

le gouvernement mais également à disposition un site

https://adresse.data.gouv.fr/api-doc/adresse

Vous pouvez interroger par CURL ou API bien sur par API en SQL

Exemple pour Gaia:

sous qsh

curl « https://api-adresse.data.gouv.fr/search/?q=41+rue+diebold&postcode=69009 »

résultat

Dans un browser

https://api-adresse.data.gouv.fr/search/?q=41+rue+Diebold&postcode=69009

INSERT INTO GDATA.SALARIE VALUES(1, ‘Géronimo’, ‘Cohen’,
QSYS2.ST_POINT(‘point (45.7542616 4.9152559)’),
QSYS2.ST_POINT(‘point (45.7726 4.8033)’)
) ;

Par Api

VALUES QSYS2.HTTP_GET(
‘https://api-adresse.data.gouv.fr/search/?q=41+rue+Diebold&postcode=69009’);

— Calculé la distance entre 2 points en mètres
— Représentation en coordonnées des zones

SELECT
ST_ASTEXT(maison) as coord_maison,
ST_ASTEXT(travail) as coord_travail,
st_distance(maison , travail) as distance from GDATA.SALARIE ;

Résultat :

.

Vous pouvez par exemple, facilement calculer les salariés qui habitent à plus de 100 km de leur lieu de travail.

SELECT « ID », NOM, PRENOM
from GDATA.SALARIE
where st_distance(maison , travail) > 100000 ;

On complète

— Création table des docteurs
create table GDATA.DOCTEUR
(ID int,
NOMDOC varchar(30),
VILLE varchar(30),
CABINET QSYS2.ST_POINT);

— insertion d’une liste de docteurs

INSERT INTO GDATA.DOCTEUR VALUES(1, ‘Docteur1’, ‘Lyon’,
QSYS2.ST_POINT(‘point (45.7679223 4.8445736)’)
) ;

INSERT INTO GDATA.DOCTEUR VALUES(2, ‘Docteur2’, ‘Marseille’,
QSYS2.ST_POINT(‘point (43.2978337 5.3693324)’)
) ;

INSERT INTO GDATA.DOCTEUR VALUES(3, ‘Docteur3’, ‘Paris’,
QSYS2.ST_POINT(‘point (48.8532238 2.3678865)’)

) ;

— Je veux les docteurs à moins de 10 km du domicile du salarié

Select b.nomdoc, b.ville, st_distance(a.maison , b.cabinet) as distance
from gdata.salarie a , gdata.docteur b
where st_distance(a.maison , b.cabinet) < 10000 ;

Le résultat

Conclusions :

Il y a des fonctions beaucoup plus poussées, et on imagine bien le type d’application qu’on pourra faire, donc on peut prévoir dès à présent une zone de géolocalisation dans des fichiers clients qu’on va créer, voir modifier .

Plus d’informations ici

https://www.ibm.com/docs/en/i/7.5?topic=analytics-geospatial-functions

/par
, Comparer 2 monitors de base de données

Vous avez collecté 2 monitors de base de données, du genre avant après une mise en prod et vous voulez les comparer

La première solution est d’utiliser ACS
SQL Performance Center
Moniteur de performances

Mais vous pouvez faire des opérations assez similaire en utilisant SQL

https://www.ibm.com/docs/en/i/7.4?topic=formats-sql-table

Voici 2 exemples de requêtes

En nombre en ID record 

WITH dbmon1 (QQRID, total1) AS (
         SELECT QQRID,
                COUNT(*)
             FROM votrebib.QZGxxxxxx     << monitor 1
             GROUP BY QQRID
     ),
     dbmon2 (QQRID, total2) AS (
         SELECT QQRID,
                COUNT(*)
             FROM votrebib.QZGyyyyyyy     << monitor 2
             GROUP BY QQRID
     )
    SELECT dbmon1.QQRID,
           dbmon1.total1,
           dbmon2.total2
        FROM dbmon1
             JOIN dbmon2
                 ON dbmon1.QQRID = dbmon2.QQRID

En temps d’exécution

WITH dbmon1 (QQRID, total1) AS (
         SELECT QQRID,
                sum(QQETIM - QQsTIM )
             FROM votrebib.QZGxxxxxxx    << monitor 1
             GROUP BY QQRID
     ),
     dbmon2 (QQRID, total2) AS (
         SELECT QQRID,
                 sum(QQETIM - QQsTIM )
             FROM votrebib.QZGyyyyyyyy   << monitor 2 
             GROUP BY QQRID
     )
    SELECT dbmon1.QQRID,
           dbmon1.total1,
           dbmon2.total2
        FROM dbmon1
             JOIN dbmon2
                 ON dbmon1.QQRID = dbmon2.QQRID

Après il vous faudra enquêter sur les différences que vous avez constaté

Exemple :


Avec l’ID , 3002 vous aurez les indexs à créer

/par
, Les instructions SQL d’un profil

Vous voulez récupérer les requêtes SQL exécutées sous une session interactive

Si vous pouvez vous connecter sous le profil c’est relativement simple

Connectez vous sous le profil et sous STRSQL faites <F13>

Vous pouvez indiquer un fichier avec différentes options, le fichier par défaut s’appelle QSQLSESS de QGPL

La difficulté existe, si vous ne pouvez pas vous connecter sous le profil en effet ces informations sont stockées dans le profil.

Pour les voir vous devrez donc utiliser la commande DMPSYSOBJ

Comme ceci

DMPSYSOBJ OBJ(‘ISQLSTvotreuser*’) +
CONTEXT(QRECOVERY) TYPE(19) SUBTYPE(EE)

Vous obtenez un spool QPSRVDMP que vous pourrez analyser

Bien sur vous devez avoir le droit de dumper et le droit sur le profil

Pour vous aider nous avons fait un outil DMPSQLUSR que vous pouvez trouvez ici https://github.com/Plberthoin/PLB/tree/master/GTOOLS

Il n’est pas parfait, mais il produit un fichier SQLLISTE dans QTEMP qui contiendra toutes instructions exécutées

Rappel :

Vous pouvez également retrouver des informations sur l’exécution des requêtes dans des moniteurs DB ou dans le cache SQL.

/par
, TR2 pour la V7R5

Les annonces de la TR2 pour la V7R5 sont sorties

Voici quelques infos sur les nouveauté de la TR

des nouvelles vues SQL
par exemple pour manipuler les fichiers SAVF
QSYS2.SAVE_FILE_INFO
QSYS2.SAVE_FILE_OBJECTS

sur les audits

Nouvelles fonctions table AUDIT_JOURNAL_XX pour analyser les types suivants
AP, AX, OR, PA, PF, PU, RA, RO, and RZ

ZLIB supporté par SQE

Sur DB2 , nouvelles fonctions SQL pour manipuler les dates et les heures

ADD_DAYS scalar function
ADD_HOURS scalar function
ADD_MINUTES scalar function
ADD_SECONDS scalar function
ADD_YEARS scalar function

Mais la principale nouveauté c’est l’arrivée de

Geospatial Analytics dans DB2 , c’est celle de Watson

https://www.ibm.com/docs/en/i/7.5?topic=analytics-best-practices-considerations

Vous voulez une définition regardez ici

https://fr.wikipedia.org/wiki/Analyse_spatiale

Ca préfigure de grosses nouveautés en matière d’analyse de données

RDV pour les premiers tests

plus d’informations ici

https://www.ibm.com/support/pages/ibm-i-75-tr2-enhancements

/par
, , Database Information Finder

Si vous n’administré pas au quotidien votre base de données, mais que vous devez intervenir ponctuellement,
Vous avez un lien qui référence les principales opérations à faire et qui peut vous aider

https://www.ibm.com/docs/en/i/7.5?topic=database-information-finder

Vous avez 5 manières de chercher

DB2 tasks
SQL quick reference
DB2 and SQL examples
SQL statements
DB2 topics

Sélectionner l’information dans la liste et faite GO

Nous on utilise souvent la première

DB2 tasks

Vous voulez ajouter une zone, Appuyer vers Go, ca vous emmènera vers des liens qui référencera les opérations à effectuées.

Rappel :

Pour les exemples, vous avez aussi ACS qui en propose une liste

dans exécution de scripts SQL

/par
, Comparer 2 fichiers

Vous devez comparer le contenu de 2 fichiers sur votre IBMi

Voici 3 Solutions, il en existe sans doute d’autres …

Première solution

En utilisant SQL et la fonction Table COMPARE_FILE

dans notre on a limité aux données

https://www.ibm.com/docs/en/i/7.4?topic=services-compare-file-table-function

Deuxième solution

En utilisant ACS

Elle se base également sur la fonction table COMPARE_FILE

Sélectionner le premier fichier à comparer

sélectionner le deuxième fichier à comparer

Choisissez l’option uniquement les datas

Vous pouvez exporter votre résultat au format csv par exemple

Troisième solution


Un petit script maison

select ‘a except b’ , a.* from fady/clients a
except
select ‘a except b’ , b.* from fady/clientssv b
union all
select ‘c except d’ , c.* from fady/clientssv c
except
select ‘c except d’ , d.* from fady/clients d

Conclusion :

A vous de choisir la solution qui vous convient

Attention cependant au COMPARE_FILE qui se base sur le RRN et un enregistrement recrée à l’identique avec un rrn différent sera considéré comme nouveau !

Merci a ceux qui m’ont aidé pour cette publication

/par
, , , Les zones HIDDEN en SQL

Il existe un comcept dans SQL sur les tables qui s’appelle les zones cachées.
Je vais essayer de vous expliquer ce que c’est.

Exemple :

CREATE TABLE SALAR (
NUMERO CHAR(6) CCSID 1147 NOT NULL DEFAULT  » ,
NOM CHAR(20) CCSID 1147 NOT NULL DEFAULT  » ,
PRENOM CHAR(30) CCSID 1147 NOT NULL DEFAULT  » ,
SALAIRE DECIMAL(5, 0) NOT NULL DEFAULT 0 IMPLICITLY HIDDEN )

Pour faire simple ces des zones qui n’apparaîtront pas si vous faites un select *


Il y plusieurs buts à cette démarche , caché sommairement des informations ou simplifier des requêtes en cachant des informations utiles et enfin les zones complétables automatiquement les bien connues date, heure et utilisateur de modification.
Maintenant que vous savez ce que c’est je vais vous expliquer l’impact sur vos développements existants.
D’abord bien sûr si vous avez des select * dans vos développements ça produira une erreur si vous respectez les règles de développement vous ne devriez pas en avoir.
Ensuite sur les insert , par défaut il ne connait que les zones non cachées vous devrez indiquer explicitement les zones cachées que vous voulez alimenter.

Conclusion
Ça peut être intéressant dans certains cas pour éviter une vue qui aurait juste pour fonction de limiter les zones.
Attention toutefois, si voulez utiliser cette possibilité toutes les zones sont visibles dans les invites Sql …

Et enfin une zone ajoutée même en hidden change le niveau de format puisqu’il est calculé sur l’ensemble des zones.

/par
, , , Contrainte d’intégrité référentielle

Egalement appelée clés étrangères, c’est une approche data centrique pour gérer les dépendances des données entre les tables de votre base de données.

Prenons un exemple :

Une commande ne peut pas avoir un client qui n’existe pas et à l’inverse, vous ne pouvez pas supprimer un client qui a encore des commandes

Jusqu’à maintenant, on avait tendance à laisser gérer cette dépendance à l’application, ce qui immanquablement créait des orphelins, qu’on devait corriger par des programmes de contrôle

Il existe donc une alternative c’est de demander à SQL de gérer cette dépendance, c’est l’approche data centrique, voyons comment

Dans la bibliothèque PLB nous allons créer 2 tables

tclients pour les clients

CREATE TABLE PLB.TCLIENTS (
NUMERO CHAR(6) CCSID 1147 NOT NULL DEFAULT  » ,
NOM CHAR(30) CCSID 1147 NOT NULL DEFAULT  » )

ALTER TABLE PLB.TCLIENTS
ADD CONSTRAINT PLB.Q_PLB_TCLIENTS_NUMERO_00001 PRIMARY KEY( NUMERO )

Cette table doit impérativement avoir une clé primaire sur la clé que vous voulez contrôler ici NUMERO

tcommande pour les commandes

CREATE TABLE PLB.TCOMMANDE (
NUMERO CHAR(6) CCSID 1147 NOT NULL DEFAULT  » ,
NUMEROCDE CHAR(6) CCSID 1147 NOT NULL DEFAULT  » ,
DESCRCDE CHAR(30) CCSID 1147 NOT NULL DEFAULT  » )

ALTER TABLE PLB.TCOMMANDE
ADD CONSTRAINT PLB.Q_PLB_TCOMMANDE_NUMEROCDE_00001
UNIQUE( NUMEROCDE ) ;

On ajoute une clé sur le numéro de commande qui ne sert pas pour la contrainte, mais qui logiquement serait présente pour identifier votre commande

Mise en Œuvre

Pour ajouter votre contrainte vous avez 2 solutions

Par les commandes IBM i natives

ADDPFCST FILE(PLB/TCOMMANDE)
TYPE(REFCST) KEY(NUMERO) PRNFILE(PLB/TCLIENTS) DLTRULE(RESTRICT)
UPDRULE(*RESTRICT)

Par SQL

ALTER TABLE PLB.TCOMMANDE
ADD CONSTRAINT PLB.Q_PLB_TCOMMANDE_NUMERO_00001
FOREIGN KEY( NUMERO )
REFERENCES PLB.TCLIENTS ( NUMERO )
ON DELETE RESTRICT
ON UPDATE RESTRICT ;

Vous fixez une action sur le fichier parent, en cas de non respect de la règle posée, le plus souvent on met RESTRICT qui interdira l’opération.
Vous pouvez regarder les autres actions pour voir , attention à *CASCADE qui peut être très brutal …

En ajoutant votre contrainte, vous pouvez avoir ce message qui indique que des valeurs ne respectent pas la régle de contrôle énoncée

ID message . . . . . . : CPD32C5
Date d’envoi . . . . . : 11/02/23 Heure d’envoi . . . . : 07:51:54

Message . . . . : Les valeurs de clé de la contrainte référentielle sont
incorrectes.

Cause . . . . . : La contrainte référentielle Q_PLB_TCOMMANDE_NUMERO_00001 du
fichier dépendant TCOMMANDE, bibliothèque PLB, est en instance de
vérification. Le fichier parent TCLIENTS, bibliothèque PLB, possède une
règle de suppression de *RESTRICT et une règle de mise à jour de *RESTRICT.
La contrainte est en instance de vérification car l’enregistrement 2 du
fichier dépendant comporte une valeur de clé étrangère qui ne correspond pas
à celle du fichier parent pour l’enregistrement 0.
Si le numéro d’enregistrement du fichier parent ou du fichier dépendant
est 0, l’enregistrement ne peut pas être identifié ou ne satisfait pas à
l’état vérification en instance.

A ce moment la contrainte est active mais vous avez des enregistrements non conformes
vous pouvez les voir par WRKPFCST


l’option 6 permet de voir les enregistrements en attente de validation et en erreur

Testons, si vous essayez de créer une commande avec un client qui n’existe pas vous aurez un message de ce type par DFU ou dans un programme RPGLE

ID message . . . . . . : CPF502D
Date d’envoi . . . . . : 09/02/23 Heure d’envoi . . . . : 16:17:38

Message . . . . : Violation de contrainte référentielle sur le membre
TCOMMANDE.

Cause . . . . . : L’opération en cours sur le membre TCOMMANDE, fichier
TCOMMANDE, bibliothèque PLB a échoué. La contrainte
Q_PLB_TCOMMANDE_NUMERO_00001 empêche l’insertion ou la mise à jour du numéro
d’enregistrement 0 dans le membre TCOMMANDE du fichier dépendant TCOMMANDE
dans la bibliothèque PLB : aucune valeur de clé correspondante n’a été
trouvée dans le membre TCLIENTS du fichier parent TCLIENTS de la
bibliothèque PLB. Si le numéro d’enregistrement est zéro, l’erreur s’est
produite lors d’une opération d’insertion. La règle de contrainte est 2. Les
règles de contrainte sont les suivantes :
1 — *RESTRICT

dans vos programmes RPG vous pourrez par exemple utiliser les fonctions %error()

Maintenant essayons de voir ce qui ce passe dans un programme SQLRPGLE, ce qui est la norme de développement à ce jour

**FREE
// création d'une commande avec un client qui n'existe pas
exec sql
INSERT INTO PLB/TCOMMANDE VALUES('000004', '000007',
'Lunettes bleaues') ;
dsply ('Insert : ' + %char(sqlcode)) ;
// modification d'une commande avec un client qui n'existe pas
exec sql
UPDATE PLB/TCOMMANDE SET NUMERO = '000007' ;
dsply ('Update : ' + %char(sqlcode)) ;
// supression d'un client qui a des commandes
exec sql
DELETE FROM PLB/TCLIENTS WHERE NUMERO = '000001' ;
dsply ('delete : ' + %char(sqlcode)) ;
*inlr = *on ;

Vous obtenez les SQLCODEs suivants

DSPLY Insert : -530
DSPLY Update : -530
DSPLY Delete : -532

Voir les contraintes existantes

pour voir les contraintes existantes

Vous pouvez faire un DSPFD
exemple :
DSPFD FILE(PLB/TCOMMANDE)
TYPE(*CST)


Par les vues SQL
exemple

SELECT * FROM qsys2.SYSCST WHERE TDBNAME = ‘PLB’ and TBNAME =
‘TCOMMANDE’ and CONSTRAINT_TYPE = ‘FOREIGN KEY’ ;

Vous pouvez les administrer par la commande WRKPFCST
exemple :
QSYS/WRKPFCST FILE(PLB/TCOMMANDE)
TYPE(*REFCST)

Avec l’option 6 vous pourrez par exemple voir les enregistrements en instance de vérification, c’est la commande DSPCPCST, pas de sortie fichier !

Conseil :


C’est une très bonne solution sur vos nouvelles bases de données, mais attention l’ajouter sur des bases de données existantes peut être risqué en effet certain traitements pouvant essayer de bypasser ce contrôle, ou avoir des erreurs présentes sur votre base …

Astuces

Vous pouvez utiliser une contrainte temporaire pour vérifier les orphelins de votre base :

Ajout de la contrainte

DSPCPCST pour voir les erreurs

Retrait de la contrainte

Cette opération doit se faire hors activité utilisateur !

Quelques liens :


https://www.ibm.com/docs/en/i/7.5?topic=objects-constraints
https://www.ibm.com/docs/en/i/7.5?topic=constraints-adding-using-check
https://fr.wikipedia.org/wiki/Cl%C3%A9_%C3%A9trang%C3%A8re

Les vues SQL sur les contraintes


SYSCST La vue SYSCST contient une ligne pour chaque contrainte du schéma SQL.
SYSREFCST La vue SYSREFCST contient une ligne pour chaque clé étrangère du schéma SQL.
SYSKEYCST La vue SYSKEYCST contient une ou plusieurs lignes pour chaque UNIQUE KEY, PRIMARY KEY ou FOREIGN KEY dans le schéma SQL. Il existe une ligne pour chaque colonne dans chaque contrainte de clé unique ou primaire et les colonnes de référence d’une contrainte référentielle.
SYSCHKCST La vue SYSCHKCST contient une ligne pour chaque contrainte de vérification dans le schéma SQL. Le tableau suivant décrit les colonnes de la vue SYSCHKCST.
SYSCSTCOL La vue SYSCSTCOL enregistre les colonnes sur lesquelles les contraintes sont définies. Il existe une ligne pour chaque colonne dans une clé primaire unique et une contrainte de vérification et les colonnes de référence d’une contrainte référentielle.
SYSCSTDEP La vue SYSCSTDEP enregistre les tables sur lesquelles les contraintes sont définies.

/par