Archives de catégorie : SQL

Cette catégorie regroupe les articles traitant du langage SQL

Coder les règles métier dans DB2 SQL

Je vais vous faire un aveu, j’ai pas mal hésité sur le titre de cet article. J’avais par exemple envisagé de le titrer ainsi : « You don’t know DB2 SQL ». Comme une sorte de clin d’oeil à l’un des mes auteurs préférés, Kyle Simpson, qui a écrit une série de bouquins fantastiques consacrés au langage Javascript. Mais je m’égare déjà, alors que le sujet du jour, c’est DB2 SQL, et c’est en particulier la publication de 2 nouveaux articles, que j’ai déposés ces jours ci sur XDocs400.com, et qui sont les suivants :

Ces 2 articles traitent de sujets très différents, mais ils ont en commun deux points essentiels :

  • il s’agit de requêtes SQL qui embarquent du code « métier »
  • dans les 2 cas, il s’agit, à partir d’informations transmises par la base de données et/ou un utilisateur, de générer des données qui n’existent pas telles quelles dans la base

En effet, la génération d’un tableau d’amortissement d’immobilisation est un processus très orienté « métier ». A partir de quelques données fournies en entrée telles que la valeur initiale du bien, le taux d’amortissement et la durée de l’amortissement, on va générer un tableau qui a des caractéristiques très précises (imposées par la législation), avec la contrainte supplémentaire que le jeu de données produit contient des lignes qui n’appartiennent  pas à la base de données, du fait qu’elles sont générées dynamiquement via une RCTE (Recursive Common Table Expression). Autre subtilité spécifique aux tableaux d’amortissements, et qui apporte tout son piment à l’exercice, il y a une donnée qui est  « glissante » d’une ligne sur la ligne suivante : vous l’aurez sans doute deviné, je fais référence à la VNC, ou « Valeur Nette Comptable », qui de « fin d’exercice » sur une ligne N du tableau, devient VNC de « début d’exercice » sur la ligne N+1.

Dans le cas de la détermination des périodes de vacance de biens en location, on n’a pas besoin de la récursivité, mais on devra quand même reconstituer des données ne figurant pas dans la base de données, puisque l’on souhaite identifier les périodes d’inoccupation de logements (ou d’autres biens) à partir des périodes d’occupation. Il faut donc faire « matcher » les lignes correspondant aux périodes d’occupation spécifiques à un logement. On veut en effet comparer la date de fin d’occupation d’un occupant N, avec la date de début d’occupation de l’occupant N+1. Cela nécessite de créer un lien « virtuel » entre les différentes périodes d’occupation d’un même logement, et ce lien virtuel va être généré par la clause « PARTITION BY », qui va nous servir à déclencher une rupture à chaque changement de logement. Cette notion de rupture que les développeurs RPG connaissent existe aussi en SQL DB2, mais elle est rarement employée par les développeurs SQL et c’est bien dommage.

Les techniques SQL que je présente dans ces deux articles sont quelque peu inhabituelles, elle provoquent souvent des réactions de surprise du genre :

  • « ah bon, on peut faire ça en SQL ? »

ou encore

  • « dis donc, ça donne un peu mal à la tête ton truc »

Il faut en effet un peu de temps et de pratique pour s’approprier ces techniques, mais une fois que vous les maîtrisez, c’est tout un univers de possibilités qui s’ouvre à vous. Et quand on a la chance de travailler avec une base de données aussi puissante que DB2 – base de données multi-plateformes, je crois utile de le rappeler – ce serait vraiment dommage de passer à côté de telles possibilités.

Deux nouveaux dossiers sur XDocs400.com

En ce 1er septembre 2014, je viens de publier deux nouveaux articles sur le site XDocs400.com.

Dans le premier dossier, j’explique comment contrôler, via SQL, l’existence d’un objet IBMi. La technique est simple et consiste à écrire un petit programme CL (qui fait un CHKOBJ et renvoie en sortie un flag indiquant si l’objet existe ou pas). Ce programme CL est encapsulé dans une fonction SQL de type externe, et pour une plus grande souplesse d’utilisation, j’ai également écrit une petite fonction « full SQL » qui appelle la fonction externe. La fonction « full SQL accepte des paramètres de type VARCHAR, ce qui offre une plus grande souplesse d’utilisation (la fonction de type externe n’acceptant que des paramètres de taille fixe équivalents à ceux du programme CLP, son utilisation, notamment lors des tests, est plus compliquée).

Dans le second dossier, j’explique comment contrôler la validité des Jobd d’un serveur IBMi. Pour ce faire, je m’appuie sur un programme RPG encapsulant l’API QWDRJOBD. Ce programme avait été publié en 2006 par Robert Cozzi, et c’est une version partiellement réécrite en RPG Free que je propose dans ce dossier. En plus du programme RPG, il y a quelques fonctions SQL et programmes CL, mais je n’entre pas dans le détail ici, je vous laisse le soin de les découvrir en lisant le dossier sur XDocs400.com. A noter que dans ce second dossier, je fais appel à la fonction de contrôle présentée dans le premier dossier. Il est donc préférable d’avoir lu le premier dossier avant de lire le second.

Ces deux dossiers sont extraits d’un cours « SQL avancé pour développeurs IBMi » que je dispense dans le cadre de mon activité de consultant. Pour tout renseignement concernant ce cours, vous pouvez prendre contact avec moi via l’adresse mèl suivante : contact@six-axe.fr

Bonne lecture.
Grégory

Le Merge sous SQL DB2

Je viens de publier sur le site XDocs400.com un nouvel article que j’ai intitulé « Le MERGE sous SQL DB2« .

Cela faisait un moment que je voulais écrire un article concernant cette instruction SQL, apparue sur la V7R1 de DB2 for i, mais comme j’étais sur plusieurs dossiers « chauds », je ne trouvais tout simplement pas le temps de le faire. Enfin, je n’ai pas beaucoup de mérite à publier cet article maintenant, vu que ce n’est finalement qu’un extrait du cours « DB2 SQL avancé » sur lequel je planchais en fin d’année dernière.

Je tenais beaucoup à publier cet article, car le MERGE SQL est certainement une des instructions les plus intéressantes pour les développeurs d’applications de gestion, qui ont souvent à écrire des choses du genre : « si cette donnée existe dans la base alors je la mets à jour, sinon je l’ajoute ». Le MERGE SQL permet de faire cela très simplement, et bien plus encore, comme vous le pourrez le constater en lisant ce nouveau dossier. Et c’est tellement pratique à utiliser, que je m’en sers maintenant dans tous mes développements, que ce soit en PHP, en RPG ou en PL/SQL (procédures stockées DB2). Il est vrai que j’ai la chance de travailler en ce moment pour des clients dont les serveurs sont en V7R1, ce qui n’est pas le cas de tous mes confrères, malheureusement.

Le seul inconvénient que je verrais dans l’utilisation du MERGE, c’est l’impossibilité de récupérer un « result set » des données impactées par l’instruction. En effet, vous savez peut être (ou peut être pas) que vous pouvez obtenir un « result set » des données modifiées par un DELETE, un INSERT ou un UPDATE, en utilisant la syntaxe SQL DB2 suivante : SELECT * FROM FINAL TABLE (INSERT …).

Cette technique utilisant la clause « FINAL TABLE » peut être très utile pour récupérer la liste des identifiants créés par un INSERT SQL, ou tout simplement le dernier identifiant généré par une série d’INSERT, vous pouvez dans ce cas écrire quelque chose dans ce genre : SELECT MAX(ID) FROM FINAL TABLE (INSERT …).

Le MERGE SQL ne peut pas être combiné avec la clause « FINAL TABLE », donc vous ne pourrez pas récupérer le result set résultant d’un MERGE. C’est à mon avis un inconvénient mineur au vu des possibilités qu’apporte le MERGE.

J’ai d’autres sujets tout aussi intéressants en préparation sur ma « boilerplate » (comme disent les anglais), que je publierai prochainement sur XDocs400.com, comme par exemple :

  • comment contrôler que toutes les jobd pointent bien sur des bibliothèques existantes
  • comment récupérer l’intégralité des postes du scheduler IBMi dans une table (réactualisation en RPG Free d’un vieil article publié sur XDocs400.com)

Ca c’est pour les sujets chauds à paraître dans les semaines à venir. J’en ai aussi d’autres en préparation, mais qui ont besoin de mûrir encore un peu, alors je ne m’étendrai pas davantage, je vous en reparlerai au fil de l’eau.

Sur ce blog, vous trouverez aussi prochainement quelques articles consacrés au langage Javascript (dans le contexte du HTML5), et à différentes techniques assez géniales que j’ai découvertes au cours de mes recherches sur ce langage (notamment sur les polyfills, sortes de librairies destinés à pallier les carences des vieux navigateurs).

Restez à l’écoute.

A très bientôt

Grégory

Générer des jeux de données à partir de rien avec SQL DB2 et la récursivité

Je viens de publier sur le site Xdocs400.com, un nouvel article que j’ai intitulé:

« Générer des jeux de données à partir de rien avec SQL DB2 et la récursivité »

Les techniques que je présente dans cet article sont relativement simples, mais elles apportent beaucoup de souplesse au développeur SQL.

Je pense que j’y consacrerai un autre article prochainement, car il y a encore beaucoup à dire sur ce sujet.