Accueil du site > Les articles > Créer une UDTF.
Version à imprimer Enregistrer au format PDF

Créer une UDTF.

les user defined function ou UDF

jeudi 6 septembre 2007, par David Malle Visites  2671 |Popularité : 41%  Première mise en ligne le lundi 3 septembre 2007.

Mettre en oeuvre des user defined function ou UDTF permet de considérer autrement les développements, de mutualiser des fonctionnalités au niveau de la base de données elle même, de faciliter la maintenance dans les cas où plusieurs programmes effectuent une même sélection de données.

Les UDTF permettent d’aller encore plus loin, en récupérant des données non stockées dans la base de données, sous forme de table, via une requête SQL.


Introduction

Depuis la V5R2, il est possible de définir des "User-Defined Table Functions" ou UDTF qui permettent de définir des routines chargées de renvoyer une table entière :

- Il existe des UDTF SQL (écrites en SPL, SQL procedure language) et des UDTF externes (écites en HLL).
- Il est possible via les UDTF externes, de récupérer des données à l’exterieur de la base de données, et de les ramener sous forme d’une table.
- Les UDTF peuvent êtres utilisées dans la clause FROM d’une instruction SELECT, comme une table ou une vue normale.

Exemple simple d’une UDTF SQL

But

obtenir la liste des articles (code + libellé long + libellé court) pour une condition signifiant que les articles sont utilisables (ici cond1, cond2 er cond3 à ’O’).

Création de la fonction

create function Articles_Actifs()
Returns Table (CodeArt char(7) , LibLong char(35) , LibCourt char(12))
Language SQL
Disallow Parallel
Begin
Return select CODART , LIBLART , LIBCART from FICART          
 where COND1 = 'O' and COND2 = 'O' and COND3 = 'O';
End

Utilisation

select * from table(Articles_Actifs()) as ArticlesActifs

Explications

- SQL extrait l’information de l’UDTF via l’instruction SELECT avec l’UDTF incluse dans le mot-clé TABLE. N’oubliez pas de fournir un nom de corrélation (ArticlesActifs, par exemple) quand vous référencez une UDTF dans une requête.
- Dans notre exemple, il n ’y a pas de paramètres en entrée. Mais il est bien sur possible d’en inclure.

Avantages

- L’interêt de cette solution est bien sur de faciliter la maintenance. En imaginant qu’une dizaine de programmes récupèrent la liste des articles actifs, il est plus rapide (développement et tests) de ne modifier qu’une fonction SQL, que 10 programmes.

UDTF externes en HLL

  • Les UDTF externes peuvent êtres écrites en langage évolué (HLL) comme RPG, C ou COBOL.
  • Les UDTF SQL permettent à SQL d’avoir un accès en lecture à des sources de données non relationnelles (données non stockées dans des tables de base de données par exemple, tout type de données accessible à un programme HLL) :
    • Contenu de fichier spoule
    • Contenu d’espace utilisateur
    • Contenu de fichier IFS
    • Liste de fichiers spoules
    • Liste de fichiers IFS
    • Données renvoyées par une API système

Pourquoi faire ?

Bonne question. Puisqu’on peut déjà faire des programmes pour accéder aux données, pourquoi mettre en oeuvre des fonctions sql pour faire la même chose ? Et bien :

- SQL est plus simple à utiliser, sa lisibilité est supérieure.
- La complexité d’un programme en HLL est masquée par la simplicité d’une requête SQL, avec en plus les possibilités de tris, de filtres et de jointures apportées par SQL.

Exemple

- Le propos est juste de vous fournir une base de départ pour les UDTFS. Le site d’IBM par exemple, dispose d’une ressource documentaire conséquente sur le sujet.
- Cet article propose juste un exemple simple (mais pas trop), pour obtenir des données non relationelles : la liste (des fichiers d’une directory de l’IFS passée en paramètre.
- Elle devrait vous permettre d’aller un peu plus loin par vos propres moyens.

Création du programme de service

- La fonction utilise un programme écrit en RPGLE : LSTIFSDIR (code source joint à cet article).
- Créez le programme de service via les deux commandes suivantes :

1) CRTRPGMOD MODULE(BIB/LSTIFSDIR)
            SRCFILE(BIB/QRPGLESRC)
2) CRTSRVPGM SRVPGM(BIB/LSTIFSDIR)
            EXPORT(*ALL)          

Comment enregistrer votre UDTF externe

- Etablir les paramètres en entrée de la fonction (ici, le répertoire de l’IFS).
- Etablir les éléments renvoyés par la fonction (ici le nom des fichiers trouvés dans le répertoire de l’IFS ainsi que son type).
- Construire l’instruction "Create function" pour enregistrer votre fonction sql. Il est possible d’enregistrer la fonction avant la création du programme en HLL. Mais pour des raisons de sauvegarde / restauration, il vaut mieux le faire après.
- Le code ci-dessous vous permet d’enregistrer la fonction.

  CREATE FUNCTION LSTIFSDIR(VARCHAR(100))    
  RETURNS TABLE(FIC_NAME   VARCHAR(100),        
                FIC_TYPE   VARCHAR(12))          
  EXTERNAL NAME 'BIB/LSTIFSDIR(LSTIFSDIR)'
  LANGUAGE RPGLE                            
  DISALLOW PARALLEL                          
  NO SQL                                    
  RETURNS NULL ON NULL INPUT                
  PARAMETER STYLE DB2SQL                    
  NOT DETERMINISTIC                          

- Cette fonction reçoit un paramètre de longueur variable qui contient le chemin vers un dossier IFS.
- Le mot-clé RETURNS TABLE contient les colonnes qui sont renvoyées à partir de la fonction table (Nom du fichier trouvé, type du fichier trouvé).
- EXTERNAL indique que la fonction est écrite en HLL. Si aucun nom n’est précisé, SQL suppose que la fonction et le programme ont le même nom.
- LANGUAGE RPGLE spécifie que le programme externe est écrit en ILE RPG. Par défaut, SQL considère le langage C, alors n’oubliez pas ce mot clé (la spécification du langage a des ramifications en matière de transmission de paramètres).
- DISALLOW PARALLEL indique qu’une fonction ne peut pas être exécutée en parallèle. Elle est nécessaire lorsqu’on crée des fonctions table.
- RETURNS NULL ON NULL INPUT ordonne à SQL de renvoyer automatiquement une table vide si l’un quelconque des paramètres transmis à la fonction est NULL. Dans ce cas, le code SPL ou HLL qui implémente la fonction n’est pas exécuté.
- PARAMETER STYLE DB2SQL contrôle la manière dont la liste des paramètres est construite pour le programme appelant.

Le programme LSTIFSDIR écrit ILE RPG

Le programme LSTIFSDIR est un exemple écrit "rapidement", en fonctions de diverses lectures du net. Il est perfectible sous bien des aspects.

Il faut respecter 3 points pour coder une UDTF externe :

- construire la liste des paramètres. Le type de données SQL doit être compatible avec le type de données HLL.
- répondre au paramètre CALL TYPE,
- indiquer quand il n’y a plus de données à renvoyer.

Le programme utilise les API suivantes :

- opendir pour ouvrir un dossier (celui passé en paramètre en entrée).
- readdir pour lire son contenu.
- closedir pour le fermer.
- lstat pour récupérer des informations sur un fichier.

Principes :

- Initialisation de la fonction table (indiqué par le type d’appel de -1), l’API OPENDIR est appelée pour ouvrir le dossier demandé. Les paramètres d’entrée sont disponibles mais des données ne devraient pas être renvoyées à ce stade.
- Après l’initialisation, le gestionnaire de base de données transmettra une requête « fetch » comme indiqué par le type d’appel de 0. A ce momentlà, le programme appelle l’API READDIR pour atteindre le nom d’un fichier provenant du dossier IFS spécifié.
- Après avoir lu un nom de fichier, le programme appelle l’API LSTAT pour extraire les attributs du fichier le programme définit les paramètres de sortie (c’est-à-dire, les données colonne pour la table) et ajoute une ligne à la table UDTF.
- SQL continue à appeler l’UDTF avec un type d’appel de of 0 (fetch) jusqu’à ce que le programme signale la fin de table (EOT, end of table).
- Quand READDIR signale cela, il n’y a plus d’entrées à lire, le programme définit le paramètre d’état SQL à ’02000’ pour indiquer qu’il n’y a plus de données (End of Table).
- Après avoir reçu le signal EOT, SQL appelle le programme une fois de plus avec le type d’appel de 1 (cleanup) pour indiquer au programme qu’il doit maintenant procéder aux activités de nettoyage.
- Puis SQL appelle l’API CLOSEDIR, met *INLR à « on » pour préparer le programme à se terminer en bon ordre.
- Chaque programme UDTF externe suit la même structure de base d’initialisation (open), d’extractions de lignes répétées (fetchs) et de nettoyage (close), comme indiqué par le paramètre type d’appel.

Traitement des erreurs UDTF :

Si le programme UDTF rencontre une erreur, il doit se fermer de manière ordonnée. L’UDTF indique au gestionnaire de base de données, via les paramètres de SQL State et de Message Text, qu’il a rencontré un problème. SQL State est CHAR(5) et les deux premiers caractères identifient les « classes » listées ci-après :

- ’00’, Success (Réussite)
- ’01’, Warning (Avertissement)
- ’02’, No Data (Pas de données)
- Other exceptions

- Pour les besoins des procédures stockées et fonctions externes, le programme devrait retourner un SQL State qui commence par ’US’ pour signaler l’occurrence d’une exception définie par l’utilisateur. Les trois caractères restants sont quelque peu arbitraires pour les exceptions définies par l’utilisateur.
- IFSDir transmet ’US001’ comme code d’erreur pour toutes les conditions d’erreur et remplit la paramètre Message Text avec un texte décrivant le problème.
- Si une exception est signalée pendant l’appel « open », le gestionnaire de base de données n’appellera pas l’UDTF à nouveau avec un type d’appel « close ». Il faut donc palier à la défaillance d’un open par un travail de nettoyage éventuel.
- Si une erreur est signalé pendant l’appel « fetch », le gestionnaire de base de données émettra encore un appel « close ».

Utilisation :

SELECT fic_name, fic_type FROM table(LSTIFSDIR('/home')) as listeifs

Timeout

Si la fonction prend un temps non négligeable en récolte d’informations : la liste des objets sur une liste de bibliothèque via l’API QUSLOBJ peut être gourmande, il est possible que SQL renvoi un timeout.

Et vous avez un message d’erreur de ce style dans la log :

3 — The database timed out waiting for the program to return. The timeout value used by the database was 0 minutes and 30 seconds.

Pour éviter ce timeout, vous pouvez dupliquer le fichier QAQQINI et modifier via SQL la valeur par défaut (30 secondes) de UDF_TIME_OUT :

CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE)       TOLIB(QUSRSYS) DATA(*YES)
UPDATE QUSRSYS/QAQQINI set QQVAL = '60'
     WHERE QQPARM = 'UDF_TIME_OUT'

A noter que UDF_TIME_OUT peut prendre la valeur *MAX.

DB2 SQL, Performances, Optimisations, personnalisations

Contraintes de nommage

Attention en nommant les colonnes retournées par la fonction. Certains mots sont réservés. Ils sont renommés automatiquement lors de la création de la fonction.

Si vous nommez une colonne USER par exemple, le système prendre en compte "USER", mais ne provoquera pas d’avertissement :

- Quand vous ferez un SELECT user FROM table(LSTUSER(’votre parametre’)) as listeuser. Cela fonctionnera.
- Par contre les tris et filtres ne fonctionneront pas.

Donc, attention au nom des colonnes retournées.

P.-S.

Voir aussi l’article obtenir une liste d’objets AS400 via SQL

Documents joints