page d'accueil
Boite à outils

Welcome page
Tools box

Cet outil permet d'exécuter une pile de commandes SQL, comme le fait déja STRQMQRY (avec un ; entre chaque commande SQL).
Il permet aussi

• de gérer les erreurs entre chaque commandes, un peu comme MONMSG en CL
• de gérer le paramétrage de manière beaucoup plus fine
• ne nécessite pas de compilation
• d'exécuter des commandes AS400
• de produire un spoule par groupe de commande SQL (un spoule par RUNSQL)
• de garder ou purger ces spoules en fin d'exécution correcte
• de créer ou supprimer des fichiers physiques (=des tables) et des fichiers logiques (=des index + des vues)
• de marquer ces objets créés : références du fichier source et marquage utilisateur (la fameuse zone disponible de 20 caractères que possède chaque objet AS400).
• de manipuler les données par SELECT, UPDATE, DELETE, INSERT, ... avec ou sans phase de COMMIT

Cet outil ne permet pas

• d'extraire des données vers un fichier en créant le fichier à la volée. Pas de soucis, STRQMQRY fait ça très bien. Ou alors faire un INSERT INTO ... SELECT ...

cet outil requiert (voir page de sommaire) 

• FINDMBR 
• CHGOBJATR 
• CHKLNK - check if an IFS file or directory exists

Syntaxe utilisée

• commande SQL : syntaxe SQL
• exécuter un groupe de commandes : runsql [titre]
• changer les options d'exécution : sqlopt [COMMIT(...)] [NAMING(...)]  [ERRLVL(...)]  [DATFMT(...)]  [DATSEP(...)]  [TIMFMT(...)]  [TIMSEP(...)]  [SAAFLAG(...)]  [FLAGSTD(...)]  [DECMPT(...)]  [SRTSEQ(...)]  [LANGID(...)]  [PRTFILE(...)] 
• commentaire : * en premier caractère
• variables : &nom, nom est une chaîne de caractères de 1 à 10 caractères de long, nom est un nom AS400 valide
• marquage d'objets : ONCREATE-USERATTR = marque
• libellé d'objet à créer : ONCREATE-TEXT = libellé

Mode d'emploi

Cet outil propose plusieurs modes d'action :

• par utilisation de la copmmande RUNSQLSTM
• par utilisation de Execute Immediate (Embbeded SQL)
• par utilisation des API CLI

Le mode  RUNSQLSTM a des contraintes. La première étant qu'une commande SQL peut s'étaler sur une ou plusieurs lignes de 80 Caractères MAXIMUM

Le texte des commandes se saisit dans un fichier source "normal" ... à savoir un fichier de largeur 80 à 100 caractères. 

separer chaque commande SQL avec une ligne RUNSQL , pour provoquer la prise en compte de la commande SQL par QSYS/RUNSQLSTM ... COMMIT(*NONE) NAMING(*SYS) 

Ou avec une ligne EXECUTESQL pour provoquer la prise en compte de la commande SQL par Execute Immediat, controlé par les SET OPTION codés en dur dans EXECSQLR (par défaut, aucun)

Substitution de parametres : exemple
1) la sequence sql : 

• to manage the mistakes between every orders, a few as MONMSG in CL.
• to manage the variables in an a lot finer manner 
• don't require compilation 
• to execute some AS400S orders 
• to produce a spool by group of SQL order (a spool by RUNSQL) 
• to keep or to purge these spools at the end of correct execution 
• to create or to suppress physical files (=tables) and logical files (= index + views) 
• to mark these created objects: references of the source file and user mark (the famous 20 characters available field that possesses every AS400 object)
• to manipulate the data by SELECT, UPDATE, DELETE, INSERT,... with or without COMMIT phase
  

This tool doesn't permit 

• to extract some data toward a file while creating the file to the flight. No worries, STRQMQRY makes that very well. Or then to make an INSERT INTO... SELECT... 

this tool requires (see summary) 

• FINDMBR
• CHGOBJATR 
• CHKLNK - check if an IFS file or directory exists

Syntax used 

• SQL order: SQL syntax 
• to execute a group of orders: runsql [title] 
• to change the options of execution: SQLOPT [COMMITTED (...)] [NAMING (...)] [ERRLVL (...)] [DATFMT (...)] [DATSEP (...)] [TIMFMT (...)] [TIMSEP (...)] [SAAFLAG (...)] [FLAGSTD (...)] [DECMPT (...)] [SRTSEQ (...)] [LANGID (...)] [PRTFILE (...)] 
• comments: * in first character 
• variable: &name, name is a 10 characters string, name is a valid AS400 name 
• marking of objects: ONCREATE-USERATTR = mark 
• description of object to create: ONCREATE-TEXT = description

Instructions for use 

This tool offers some different action modes  :

• using the RUNSQLSTM command
• using the Execute Immediate (Embbeded SQL)
• using the CLI API

The RUNSQLSTM mode hac its constraints. The first is that an SQL order can spread on one or several lines of 80 MAXIMUM Characters 

The text of the orders are in a "normal" source file... ie a file with record size 80 to 100 characters. 

Separate every SQL order with a RUNSQL line, to provoke the running of the SQL by the command QSYS/RUNSQLSTM... COMMIT(*NONE) NAMING(*SYS) 

Or with a EXECUTESQL to provoke the running of the SQL order by Immediate Execute ... controled by hardcoded SET OPTION in EXECSQLR (initialy, none)

Substitution of parameters : sample
1) the sequence : sql

* detruire les données de la société &csoc
delete from &target/custom where company = '&cmp'
runsql titre

excsql ... ((target LaBib) (cmp 6)) 

excsql respecte les minuscules : dans le texte, dans le nom des variables (attention, l'as400 met par défaut en majuscule la saisie) et dans le texte de remplacement

remarque : la ligne RUNSQL ou EXECUTESQL n'est pas sensible à la majuscule mais le mot 'RUNSQL'/'EXECUTESQL' doit être en position 1 à 6, 1 à 10 de la ligne 

si le titre est indiqué, il est utilisé comme référence utilisateur du spoule QTMPSQL, sinon référence = ligne du source

options pour RUNSQLSTM
si vous désirez changer les options initiales : COMMIT(*NONE) NAMING(*SYS),ajoutez une ligne SQLOPT (ou sqlopt) suivie des options désirées
Options de la commande : COMMIT NAMING ERRLVL DATFMT DATSEP TIMFMT TIMSEP SAAFLAG FLAGSTD DECMPT SRTSEQ LANGID PRTFILE 
chaque ligne SQLOPT annule et remplace la précédente 

gestion des messages. en fait, SQL9010 La commande RUNSQLSTM a échoué. 
par défaut : sortie brutale en *ESCAPE 
modification : ajouter une ligne MONMSG  
si cette ligne contient IGNORE, les messages sont captures 
sinon, retour en mode ESCAPE. 

commentaires
mettre une étoile en position 1

Imbrication de commande as400 : basé sur create procedure jpl/syscmd(UDF - user define function - source = jpl/qtxtsrc(sql)

remarque: the RUNSQL ou EXECUTESQL line is not sensitive to the case but the word ' RUNSQL'/'EXECUTESQL' must be in position 1 to 6, 1 to 10 of the line

if title is indicated, it is used as user reference of the QTMPSQL spoule, otherwise reference = source line number

options for RUNSQLSTM 
if you want to change the initial options: COMMIT(*NONE) NAMING(*SYS), add a SQLOPT line (or sqlopt) consistent of the wanted options 
Options of the order: COMMITS NAMING ERRLVL DATFMT DATSEP TIMFMT TIMSEP SAAFLAG FLAGSTD DECMPT SRTSEQ LANGID PRTFILE  
every SQLOPT line deletes and replaces the previous

messages handling. in fact, SQL9010 : RUNSQLSTM has failed.  

by default: brutal exit with *ESCAPE  
modification: add a MONMSG line 
if this line contains IGNORE, the messages are captured  
otherwise, return in ESCAPE. 

commentaires 
put a star in position 1

Imbrication of as400 order: based on create procedure jpl/syscmd(UDF - to use define function - source = jpl/qtxtsrc(sql)

call syscmd ('clrpfm &bib/&fic')

Variables. Les variables "internes" complètent les variables transmises par l'appelant
setvar permet de créer ou corriger une variable. Un setvar peut citer une variable existante

Une variable est détectée dans le code source par le &. Dès qu'il trouve un & l'interpréteur est potentiellement face à 10 variables, selon que l'analyse se fait sur les 1 à 10 caractères suivant le &. Une au moins doit exister. L'interpréteur cherche parmi les variables disponibles la première qui correspond au texte. C'est la longueur de chaque variable qui détermine la longueur du texte source analysé. exemple :

DELETE FROM &WORKDATA/MYFILE
appelé par
     excsql ... ((WORK MyLib))
donne
     DELETE FROM MyLib/MYFILE

appelé par
     excsql ... ((WORK MyLib) (WORKDATA MyData))
donne
     DELETE FROM MyLib/MYFILE
&WORK est interprété avant &WORKDATA

appelé par
     excsql ... ((WORKDATA MyData) (WORK MyLib))
donne
     DELETE FROM MyData/MYFILE
&WORKDATA est interprété avant &WORK

DELETE FROM &WORKDATA/MYFILE
appelé par
     excsql ... ((WORK '&COMPANYlib') (COMPANY my)
donne
     DELETE FROM mylib/MYFILE
une variable peut faire appel à une autre variable

Test des variables internes :

A variable is detected in the code source by it &. as soon as it finds a & the interpreter is potentially working with 10 variables, depending on analysing 1 to 10 chars after the &. At least, one must exists. The interpreter  looks among the available variables for the first that corresponds to the text. It is the length of each variable that determines the length of the text source analyzed. 
example:

DELETE FROM &WORKDATA/MYFILE
called by
     excsql ... ((WORK MyLib))
returns 
     DELETE FROM MyLib/MYFILE

called by
     excsql ... ((WORK MyLib) (WORKDATA MyData))
returns 
     DELETE FROM MyLib/MYFILE
&WORK is interpreted before &WORKDATA

called by
     excsql ... ((WORKDATA MyData) (WORK MyLib))
returns 
     DELETE FROM MyData/MYFILE
&WORKDATA is interpreted before &WORK

DELETE FROM &WORKDATA/MYFILE
called by
     excsql ... ((WORK '&COMPANYlib') (COMPANY my)
returns
     DELETE FROM mylib/MYFILE
one variable can call an other variable

test of internal variables :

 ABCDEF = &ABCDEF
 ABCDE = &ABCDE
 IMBRICATION = &TARGET
 ERREUR = &ERREUR POUR TESTER CPD0172 PARAMETRE NON TRANSMIS
 ERREUR = &ERREUR for testing CPD0172 untransmitted parameter
*
MonMsg Ignore
RuNsQl

 EXECSQL LIBRARY(ECOLIB) MEMBER(EXECSQLTST)
 SETVAR((ABCDEF PREMIER) (ABCDE DEUXIEME)
 (TARGET 'VARIABLE &ABCDE IMBRIQUEE'))

6100 - CALL PGM(EXECSQLR) /* LA COMMANDE CALL CONTIENT DES PARAMÈTRES *
ABCDEF = PREMIER
ABCDE = DEUXIEME
IMBRICATION = VARIABLE DEUXIEME IMBRIQUEE

la suite importe peu. Ce test se termine par un plantage. tu t'en doutais pas un peu ?

OnCreate Ces deux options permettent de valoriser correctement les paramètres de CHGOBJATR, outil utilisé par EXECSQL chaque fois qu'il crée un objet SQL

ONCREATE-USERATTR = le marqueur personnel. Cette option garde sa valeur en permanence 

ONCREATE-TEXT = la description de l'objet. Cette option est effacée après chaque utilisation pour éviter de répéter abusivement un nom d'objet (dans le cas de deux CREATE de suite, en en absence de ONCREATE-TEXT après le premier CREATE)

Il y a un bug quand on crée une table SQL dans QTEMP quand le nom de la table fait plus de 10 caractères (dans ce cas le nom AS400 est différent du nom SQL) :

OnCreate These two options permit to valorize the parameters of CHGOBJATR correctly, tool used by EXECSQL each time it creates a SQL object 

ONCREATE-USERATTR = the personal mark. This option keeps its value in permanence, such as a variable.

ONCREATE-TEXT = the object description. This option is erased after each use to avoid to repeat an object name abusively (in the case of two successive CREATE, without ONCREATE-TEXT after the first CREATE) 

There is a bug when it creates a SQL table in QTEMP when the table name is  more than 10 characters long (in this case the AS400 name is different from the SQL name)

setvar toto = VA.savoir
setvar toto = bof
oncreate-userattr = Perso
oncreate-text = nom d''un petit bonhomme
monmsg ignore

drop TABLE qtemp/TESTTESTTEST

runsql
monmsg

CREATE TABLE qtemp/TeSTtEsTTEST
(UNE CHAR (10 ) NOT NULL WITH DEFAULT)
runsql cretest

Ca plante : je ne sais pas où est l'équivalent de qsys.qadbxref pour qtemp.
donc pas de nom long(>10 cars) dans qtemp sous execsql