Le web de Dominique Guebey – IBM AS/400 iSeries

Page : http://www.dg77.net/tekno/as400/as400clsql2.htm


   D o m i n i q u e   G u e b e y    J u n g l e     IBM AS/400 iSeries

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);
}

}