CL AS400 utilisant SQL, JDBC et l'IFS
Le programme Java permet une sortie sur fichier, contrairement à la commande CL RUNSQLSTM
Commande
0001.00 CMD PROMPT('Statistiques POIDS')
0002.00
0003.00 PARM KWD(DAT6D) TYPE(*CHAR) LEN(6) MIN(1) +
0004.00 PROMPT('Depuis (JJMMAA)')
0005.00 PARM KWD(DAT6F) TYPE(*CHAR) LEN(6) +
0006.00 PROMPT('Jusqu''A (JJMMAA)')
0007.00
0008.00 PARM DTA *CNAME 10 CONSTANT(DGBIBLIO) DTAARA(*YES)
0009.00 PARM WKSTN *PNAME 10 CONSTANT(' ') DTAARA(*YES)
Création de la commande
0168.00 /** STATISTIQUES POIDS AUTOMATIQUES
0169.00 CRTCMD &LIB/STATPOIDS PGM(&LIB/STATPOIDCL) SRCFILE(&LIB/QCMDSRC) +
0170.00 SRCMBR(STATPOIDS ) TEXT('STATISTIQUES POIDS ')
Programme CL
0001.00 /* STATISTIQUES POIDS */
0002.00 /* Ce CL lance un query puis deux commandes SQL */
0003.00 /* Le query sort le fichier des donnees detaillees, le premier SQL */
0004.00 /* inverse le montants CR, le second genere le fichier des cumuls. */
0005.00
0006.00 /* Ce CL se lance lui-meme en batch s'il le faut. */
0007.00
0008.00 /* T04 : OPNQRYF SUR FAKPFCCN % PLAGE DE DATE */
0009.00 /* T08 : CPYFRMQRYF DANS QTEMP */
0010.00 /* T12 : RUNQRY UTILISANT QTEMP/FAKPFCCN CREANT GUEBEY/STAT_POIDS */
0011.00 /* T16 : RUNSQLSTM POUR MAJ MONTANT CREDITS */
0012.00 /* T20 : ENVOI DANS IFS */
0013.00 /* T24 : CPYF STAT_POIDS DANS STAT_P */
0014.00 /* T28 : TRAITEMENT SQL GENERANT CUMUL ET LE METTANT DANS IFS */
0015.00
0016.00 PGM PARM(&DAT6D &DAT6F &DTA &WKSTN)
0017.00 DCL &DAT6D *CHAR 6
0018.00 DCL &DAT6F *CHAR 6
0019.00 DCL &DTA *CHAR 10 /* Nom dtaara contenant le nom de la biblio */
0020.00 DCL &WKSTN *CHAR 10
0021.00 DCL &TEXTE *CHAR 14
0022.00 DCL &TYPE *CHAR 1
0023.00 DCL &LIB *CHAR 10
0024.00 DCL &PREFIX *CHAR 10
0025.00 DCL &PATH *CHAR 256 VALUE(*BLANK)
0026.00 DCL &NOMF *CHAR 256
0027.00 DCL &NOMFEXT *CHAR 256
0028.00 DCL &CLASSES *CHAR 256
0029.00 DCL &SYSTEM *CHAR 10
0030.00
0031.00 ADDLIBLE GUEBEY POSITION(*LAST)
0032.00 MONMSG CPF0000
0033.00
0034.00 CALL SBRBIBCL PARM(&DTA &LIB) /* Nom de la bibliotheque de travail */
0035.00 MONMSG CPF0000
0036.00
0037.00 /* INTERACTIF SEULEMENT POUR TESTS */
0038.00 /* GOTO OK */
0039.00
0040.00 /* TESTE SI BATCH : --------------------------------- */
0041.00 RTVJOBA TYPE(&TYPE)
0042.00 /* SI OUI : TRAITEMENT */
0043.00 IF COND(&TYPE *EQ '0') THEN(GOTO CMDLBL(OK))
0044.00 /* SI NON, ENVOI EN BATCH */
0045.00 RTVJOBA JOB(&WKSTN)
0046.00 SBMJOB CMD(CALL PGM( &LIB/STATPOIDCL) +
0047.00 PARM(&DAT6D &DAT6F &DTA &WKSTN)) JOB(STATPOID )
0048.00 RETURN
0049.00
0050.00 /* --------------------------------------------- */
0051.00 OK:
0052.00 CALL &LIB/SBRDATCL PARM(&DAT6D) /* inverse jj et aa */
0053.00 MONMSG CPF0000
0054.00 CALL &LIB/SBRDATCL PARM(&DAT6F)
0055.00 MONMSG CPF0000
0056.00
0057.00 ADDLIBLE FR_SPPDTA
0058.00 MONMSG CPF0000
0059.00
0060.00 CLOF OPNID(FAKPFCCN)
0061.00 MONMSG CPF0000
0062.00 DLTOVR FAKPFCCN
0063.00 MONMSG CPF0000
0064.00
0065.00 T04: OPNQRYF FILE((FAKPFCCN)) OPTION(*INP) +
0066.00 QRYSLT(' +
0067.00 DTFACN *GE D6D +
0068.00 *AND DTFACN *LE D6F +
0069.00 ') +
0070.00 MAPFLD((D6D &DAT6D *DEC 6 0) +
0071.00 (D6F &DAT6F *DEC 6 0))
0072.00 MONMSG CPF0000
0073.00 /* CREATION D'UN FICHIER DE TRAVAIL */
0074.00 DLTF QTEMP/FAKPFCCN
0075.00 MONMSG CPF0000
0076.00 T08: CPYFRMQRYF FROMOPNID(FAKPFCCN) TOFILE(QTEMP/FAKPFCCN) +
0077.00 CRTFILE(*YES)
0078.00 MONMSG CPF0000
0079.00 OVRDBF FAKPFCCN QTEMP/FAKPFCCN SHARE(*YES)
0080.00
0081.00 CLOF OPNID(FAKPFCCN)
0082.00 MONMSG CPF0000
0083.00
0084.00 /* Statistiques poids */
0085.00 T12: RUNQRY GUEBEY/STATSPOIDS
0086.00 MONMSG CPF0000
0087.00 CHGVAR &TEXTE VALUE(&dat6f *cat 'STATPOID')
0088.00
0089.00 /* MAJ DES MONTANTS CREDITEURS */
0090.00 /* Utilisant une commande SQL dans un fichier source */
0091.00 T16: RUNSQLSTM SRCFILE(GUEBEY/QSQLSRC) SRCMBR(STATPOIDS) +
0092.00 COMMIT(*NONE)
0093.00 MONMSG CPF0000
0094.00
0095.00 /* M.AJ. TEXTE FICHIER + SORTIE DANS IFS */
0096.00 T20: CALL &LIB/SBRSORTIE PARM(&LIB &LIB STAT_POIDS &TEXTE &PREFIX)
0097.00 MONMSG CPF0000
0098.00
0099.00 T24: CPYF GUEBEY/STAT_POIDS GUEBEY/STAT_P MBROPT(*REPLACE) +
0100.00 CRTFILE(*YES) FMTOPT(*NOCHK)
0101.00 MONMSG CPF0000
0102.00 /* Probleme : on ne peut pas sortir un fichier avec RUNSQLSTM... */
0103.00 /*DLTF GUEBEY/STATPP */
0104.00 /*MONMSG CPF0000 */
0105.00 /*RUNSQLSTM SRCFILE(GUEBEY/QSQLSRC) SRCMBR(TOTPOIDS) COMMIT(*NONE)*/
0106.00 /*MONMSG CPF0000 */
0107.00
0108.00 T28:
0109.00 IF COND(&PATH *EQ *BLANK) THEN(CHGVAR &PATH VALUE('/home/guebey/transferts'))
0110.00 RTVNETA SYSNAME(&SYSTEM)
0111.00 CHGVAR &NOMF VALUE(&PREFIX *TCAT '_' *TCAT STAT_P)
0112.00 RTVDTAARA &LIB/DGCLASSES &CLASSES
0113.00 MONMSG CPF0000 EXEC(DO)
0114.00 CHGVAR &CLASSES '/home/guebey/java'
0115.00 ENDDO
0116.00 CHGCURDIR &CLASSES
0117.00 RUNJVA CLASS(JDBCStatPoids) PARM(&SYSTEM &LIB +
0118.00 STAT_P &PATH &NOMF) +
0119.00 CLASSPATH('.:/QIBM/PRODDATA/HTTP/PUBLIC/JT400/LIB/JT400.JAR') +
0120.00 OUTPUT(*PRINT)
0121.00 MONMSG JVA0000
0122.00
0123.00 /* LE PROGRAMME JAVA RAJOUTE L'EXTENSION AU NOM DU FICHIER */
0124.00 CHGVAR &NOMFEXT VALUE(&PATH *TCAT '/' *TCAT STAT_P *TCAT '.TXT')
0125.00 CHGAUT OBJ(&NOMFEXT ) USER(*PUBLIC) DTAAUT(*R) OBJAUT(*ALL)
0126.00 MONMSG CPF0000
0127.00
0128.00 IF COND(&TYPE *EQ '0') THEN(CALL +
0129.00 PGM(&LIB/SBRMSGCL) PARM('STAT_POIDS' &LIB &WKSTN))
0130.00
0131.00 CLOF OPNID(FAKPFCCN)
0132.00 MONMSG CPF0000
0133.00 DLTOVR FAKPFCCN
0134.00 MONMSG CPF0000
0135.00
0136.00 GOTO FIN /* TEST SEULEMENT */
0137.00 DLTF QTEMP/FAKPFCCN
0138.00 MONMSG CPF0000
0139.00
0140.00 FIN: ENDPGM
Fichier de commande SQL (membre STATPOIDS dans le fichier source QSQLSRC)
0001.00 UPDATE GUEBEY/STAT_POIDS SET MONTANT = MONTANT * -1 WHERE 0002.00 CRED_DEBIT = '6'
Programme Java
/**
Lecture d'un fichier AS400/iSeries par SQL/Jdbc, <br />
sort dans l'IFS un fichier CSV (separateurs ";"). <br />
Au debut met un entete suivi d'une ligne de pointilles.<br />
Exemple : java JDBCQfic nomsysteme nombiblio nomfichier cheminIFS nomfichIFS<br />
@author Dominique Guebey d.guebey@abxlogistics.fr
@param nom de l'as400, nom de la bibliotheque, nom du fichier as400, chemin_ifs, nom_fich_ifs.
@version 21 juin 2004
*/
// Copie a partir de JDBCQuery et IFSCopyFile
// Inspires au depart du manuel IBM Toolbox for Java version 6.
// Utilisation de com.ibm.as400.access de jt400.jar
// cf dans /QIBM/ProdData/HTTP/Public/jt400/lib/
// Modifications :
// TODO :
// - AS400Text est "deprecated"
// - optimisation : supprimer les blancs a droite
// - mettre le nom d'user dans le nom du fichier si nomfichIFS=null
// - pour Excel : remplacer le '.' decimal par ','
// - csv : remplacer les ";" dans les string par autre chose
import java.sql.*;
import java.io.*;
import java.util.*;
import com.ibm.as400.access.*;
public class JDBCStatPoids extends Object
{
// Formate une chaine s avec la bonne longueur
private static String format (String s, int width)
{
String formattedString;
// The string is shorter than specified width,
// so we need to pad with blanks.
if (s.length() < width) {
StringBuffer buffer = new StringBuffer (s);
for (int i = s.length(); i < width; ++i)
buffer.append (" ");
formattedString = buffer.toString();
}
// Otherwise, we need to truncate the string.
else
formattedString = s.substring (0, width);
return formattedString;
}
public static void main (String[] parameters)
{
// Verifie les parametres (2 derniers facultatifs)
if (parameters.length < 3) { return; }
if (parameters.length > 5) { return; }
String chemin = null;
String nomfic = null;
String system = parameters[0];
String collectionName = parameters[1];
String tableName = parameters[2];
if (parameters.length > 3) { chemin = parameters[3]; }
if (parameters.length > 4) { nomfic = parameters[4]; }
Connection connection = null;
IFSTextFileOutputStream target = null;
int textLength = 0;
byte[] data = null;
String zone = null;
String javaText = null;
AS400Text textConverter = null;
AS400Text textConvert = null;
try {
if (chemin == null) { chemin = "/home/guebey/transferts"; }
if (nomfic == null) { nomfic = tableName; }
// Definir et ouvrir le fichier en sortie et obtenir la connexion...
String targetName = chemin + "/" + nomfic + ".txt";
AS400 as400 = new AS400(system);
target = new IFSTextFileOutputStream(as400,targetName);
// Load the IBM Toolbox for Java JDBC driver.
DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
// Get a connection to the database. Since we do not
// provide a user id or password, a prompt will appear.
connection = DriverManager.getConnection ("jdbc:as400://" + system);
DatabaseMetaData dmd = connection.getMetaData ();
// Query sur le fichier
Statement select = connection.createStatement ();
ResultSet rs = select.executeQuery (
"SELECT SITE, FLUX, PRODUIT, PAYS_EXP, PAYS_DEST, sum(NB_ENVOI) as NB_ENVOI, sum(POIDS_BRUT) as POIDS_BRUT, sum(POIDS_TAX) as POIDS_TAX, sum(MONTANT) as MONTANT FROM " + collectionName + dmd.getCatalogSeparator() + tableName + " GROUP BY SITE, FLUX, PRODUIT, PAYS_EXP, PAYS_DEST ORDER BY SITE, FLUX, PRODUIT, PAYS_EXP, PAYS_DEST");
// Get information about the result set. Set the column
// width to whichever is longer: the length of the label
// or the length of the data.
ResultSetMetaData rsmd = rs.getMetaData ();
int columnCount = rsmd.getColumnCount ();
String[] columnLabels = new String[columnCount];
int[] columnWidths = new int[columnCount];
for (int i = 1; i <= columnCount; ++i) {
columnLabels[i-1] = rsmd.getColumnLabel (i);
columnWidths[i-1] = Math.max (columnLabels[i-1].length(),
rsmd.getColumnDisplaySize (i));
}
// SORTIE DE L ENTETE
for (int i = 1; i <= columnCount; ++i) {
zone = (format (rsmd.getColumnLabel(i), columnWidths[i-1]));
textLength = zone.length();
// page de code 850 sinon recupere de l'UNICODE
textConverter = new AS400Text(textLength,850);
data = textConverter.toBytes(zone);
target.write(data);
textConvert = new AS400Text(1,850);
data = textConvert.toBytes(";");
target.write(data);
}
data = textConvert.toBytes("\n");
target.write(data);
// LIGNE DE POINTILLES
StringBuffer dashedLine; // ????????
for (int i = 1; i <= columnCount; ++i) {
for (int j = 1; j <= columnWidths[i-1]; ++j) {
data = textConvert.toBytes("-");
target.write(data);
}
data = textConvert.toBytes(";");
target.write(data);
}
data = textConvert.toBytes("\n");
target.write(data);
// Boucle de lecture du fichier
// colonne par colonne
while (rs.next ()) {
String ligne = "";
for (int i = 1; i <= columnCount; ++i) {
String value = rs.getString (i);
if (rs.wasNull ()) value = "<null>";
// format en attendant resoudre pb sortie EBCDIC
zone = (format (value, columnWidths[i-1]));
ligne = ligne + zone + ";";
}
textLength = ligne.length();
textConverter = new AS400Text(textLength,850);
data = textConverter.toBytes(ligne);
target.write(data);
data = textConvert.toBytes("\n");
target.write(data);
}
target.close();
}
catch (Exception e) {
// System.out.println ();
// System.out.println ("ERROR: " + e.getMessage());
}
finally {
// Clean up.
try {
if (connection != null)
connection.close ();
}
catch (SQLException e) {
// Ignore.
}
}
System.exit (0);
}
}