Un AS400 per domare tutti i database
Proprio così, an AS400 to rule them all, dove "them" sono tutti i database relazionali che hanno un driver JDBC che, come spiegherò in seguito, possiamo utilizzare persino con il linguaggio RPG.
Alzi la mano chi lavora su IBM i / AS400 e ha avuto la necessità, almeno una volta, di utilizzare un database relazionale esterno (MySQL, PostgreSQL, Oracle, Microsoft SQL Server, etc). A me è capitato moltissime volte e immagino sia lo stesso per altri.
Le necessità sono varie:
- migrazione da o verso un altro ERP
- aggiornare un database esterno ad esempio quello di una soluzione di Business Intelligence esterna all'AS400
- leggere informazioni dal database di un'applicazione esterna (esempio una webapp) da elaborare nel proprio ERP su AS400
- etc
Anche le modalità per farlo sono varie:
- web services
- soluzioni ETL (Extract Tansform Load), come ad esempio Pentaho Data Integration, sviluppando e schedulando i job che leggono le informazioni dai database esterni e le scrivono in tabelle su AS400 o viceversa
- accesso diretto dall'AS400!!!
Quest'ultima è la modalità di cui voglio scrivere in quest'articolo, ovvero la possibilità di accedere a database esterni all'AS400 tramite driver JDBC usando SQL e, udite udite, il vecchio "obsoleto" e tanto caro RPG.
Per far questo ci viene in aiuto il tool AppServer4RPG di Dieter Bender, che ci permette di utilizzare direttamente dall'AS400 qualunque database abbia un driver JDBC, ovvero quelli già citati prima: MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, DB2 e tanti altri.
Nell'esempio che descrivo di seguito accedo da un AS400 in Italia al database PostgreSQL presente su un server Linux in Francia.
Configurazione AppServer4rpg
Innanzitutto bisogna scaricare e decomprimere appserver4rpg.
Spostare sull'AS400 via FTP il file jvagate.savf e ripristinare la libreria JVAGATE dal SAVF col comando RSTLIB.
Rinominare la cartella precedentemente decompressa con il nome JVAGATE quindi spostarla nella root dell'IFS dell'AS400: /JVAGATE (ATTENZIONE: se avete la necessità di darle un altro nome basta che lo indichiate nell'area dati AS4RPGPATH della libreria precedentemente ripristinata).
Copiate in /JVAGATE/lib/ tutti i driver JDBC che vi servono (se non li avete cercateli e scaricateli da internet). Nel mio caso ho scaricato e copiato il driver JDBC di PostgreSQL: postgresql-42.1.1.jar
A questo punto bisogna modificare il file di configurazione /JVAGATE/conf/global.properties per indicare i riferimenti di ogni database esterno da raggiungere.
Nel mio caso ho aggiunto i riferimenti del mio database PostgreSQL:
ard.url.PGDWS=jdbc:postgresql://xxx.xxx.xxx.xxx:5432/dws
ard.driver.PGDWS=org.postgresql.Driver
dove
- PGDWS: è il nome di fantasia che utilizzerò successivamente nel comando
ADDRDBDIRE - url: è la stringa di connessione al database in cui "xxx.xxx.xxx.xxx" va sostituito con l'IP o l'hostname del database server e dws è il nome del database a cui voglio connettermi
- driver: è l'indicazione di quale driver utilizzare per la connessione (quello riferito al JDBC driver precedentemente copiato nel percorso
/JVAGATE/lib/)
I parametri sopra descritti sono quelli essenziali per definire la connessione, ma, oltre ad essi, si possono aggiungere altri parametri facoltativi. Tra questi ne segnalo due:
ard.properties.PGDWS.user=mIo-UtentE
ard.properties.PGDWS.password=Mia-PassWord
Questi due parametri ci permettono di definire l'utente e la password con cui viene fatta la connessione al database. Indicandoli nel file global.properties si evita di indicarli ad ogni connessione fatta da STRSQL o da RPG, ma soprattutto permettono di aggirare il problema delle connessioni con utente minuscolo. In pratica, quando viene fatta la CONNECT al database, l'AS invia sempre l'utente in MAIUSCOLO anche se lo indicate minuscolo (compreso indicarlo tra apici). In questo caso l'unico modo che ho trovato per aggirare il problema è indicare l'utente all'interno del file di configurazione global.properties.
Avviare il servizio su AS:
SBMJOB CMD(CALL PGM(JVAGATE/STRJVAGATE)) JOB(JVAGATE) JOBQ(QCTL)
ATTENZIONE: se si aggiunge un nuovo database al file global.properties o un nuovo JDBC driver è necessario riavviare il servizio su AS400.
Infine si aggiunge una voce all'indirizzario RDB che si riferisca a quanto configurato nel precedente file:
ADDRDBDIRE RDB(PGDWS) RMTLOCNAME(*ARDPGM) ARDPGM(JVAGATE/JDBCGATE)
Finalmente si può accedere al database esterno
Test con STRSQL
Accesso diretto tramite il tool STRSQL dell'AS400.
Connessione al database:
CONNECT TO PGDWS USER mio-utente USING 'Mia-PassWord'
ATTENZIONE: come descritto in precedenza, se l'utente del database è minuscolo, vi darà errore di connessione perchè l'AS lo passa sempre in maiuscolo. In questo caso dovete indicare utente e password all'interno del file di configurazione global.properties e di conseguenza non è necessario specificarli nell'istruzione CONNECT.
Esecuzione di qualunque istruzione SQL, ad esempio:
select id_zona3, cod_zona3, desc_zona3
from zone
where cod_zona3 like 'IT%'
limit 100;
risultato:
Disconnessione dal Database
rollback
DISCONNECT CURRENT
Test con RPG
Ora vediamo come accedere al database esterno direttamente da un programma RPG.
Il sorgente dev'essere ovviamente di tipo SQLRPGLE.
Di seguito il sorgente di un semplice esempio in cui ci si connette al database e si legge una tabella con SQL:
H DECEDIT('0,') DATEDIT(*DMY.)
*--------------------------------------------------------------*
D sql_stm S 32700
*
D S_ID S 9 0 INZ
D S_COD S 6 INZ
D S_DESC S 40 INZ
*
D USR S 10 inz('mio-utente')
D PWD S 10 inz('Mia-PassWord')
*--------------------------------------------------------------*
/free
Exec Sql CONNECT TO PGDWS USER :USR USING :PWD;
sql_stm = 'select +
id_zona3, cod_zona3, desc_zona3 +
from zone +
where cod_zona3 like ''IT%'' +
limit 20';
Exec Sql Prepare SPG from :sql_stm;
Exec Sql Declare CPG cursor for SPG;
Exec Sql Open CPG;
/end-free
*
C DO *HIVAL
C/EXEC SQL
C+ FETCH CPG INTO :S_ID, :S_COD, :S_DESC
C/END-EXEC
C if SqlCod<>0
C leave
C endif
*
/free
// mostro le info lette dal DB esterno
dsply ('ID: ' + %char(S_ID));
dsply ('Codice: ' + %char(S_COD));
dsply ('Desc.: ' + %char(S_DESC));
dsply ('---------------------------------------');
/end-free
*
C ENDDO
*
/free
Exec Sql Close CPG;
Exec Sql ROLLBACK;
Exec Sql DISCONNECT CURRENT;
/end-free
C SETON LR
*--------------------------------------------------------------*
ATTENZIONE: compilare il sorgente con le seguenti opzioni:
- COMMIT(*CS)
- OPTION(*SQL)
Risultato dell'esecuzione:
Altri esempi li ha forniti il buon Dieter Bender nella cartella JVATEST.LIB che trovate nel file decompresso all'inizio.
Note particolari
Logging
Questo tool genera log molto verbosi in /JVAGATE/logs/. Di default genera un file log per ogni giorno e NON elimina i file dei giorni precedenti. Se lo utilizzate in modo massiccio vi troverete presto con GigaByte di log.
Per evitare questo problema si possono introdurre delle politiche di Log Rotation un po' più stringenti modificando il file di configurazione /JVAGATE/conf/log4j.properties.
Commentare le due istruzioni indicate sotto e aggiungere le nuove istruzioni con le vostre preferenze:
...
# VERSIONE ORIGINALE (genera un file di log per ogni giorno ... SENZA LIMITI)
#log4j.appender.MeinDaRoFiAppender=org.apache.log4j.DailyRollingFileAppender
#log4j.appender.MeinDaRoFiAppender.datePattern='.'yyyy-MM-dd
# Versione con Log Rotation basato sulla dimensione e con numero di file limitato
log4j.appender.MeinDaRoFiAppender=org.apache.log4j.RollingFileAppender
log4j.appender.MeinDaRoFiAppender.MaxBackupIndex=20
log4j.appender.MeinDaRoFiAppender.MaxFileSize=10MB
...
dove:
- MaxBackupIndex indica il numero massimo di file di log da tenere
- MaxFileSize indica la dimensione massima di ogni file
quindi, con i parametri sopra indicati, i log occuperanno al massimo 10MB per 20 file, ovvero 200MB.
Proprietà STRSQL
Quando ci si connette ad un database esterno usando STRSQL viene automaticamente modificato un attributo della sessione, Convenzione di denominazione, che da *SYS diventa *SQL. Questo fa si che quando vi disconnettete e provate ad usare l'SQL con le altre tabelle dell'AS400 si comporterà in modo diverso da come siete abituati, ad esempio, non vedrà più la lista librerie, quindi se fate una SELECT senza specificare la libreria cercherà la tabella in una libreria con nome uguale al nome dell'utente (ulteriori dettagli sulle differenze tra SYS e SQL li potete trovare spiegati bene qui: https://blog.faq400.com/it/database-db2-for-i/naming-sys-vs-sql/).
Per ripristinare l'impostazione standard è sufficiente premete F13, scegliete l'opzione 1. Modifica attributi sessione e ripristinare la convenzione *SYS come da immagine seguente:
Conclusione
Come avrete intuito leggendo l'articolo, l'accesso ad un database relazionale esterno tramite driver JDBC è semplicissimo anche dall'AS400. Non resta che sfruttarlo 😉
Un ringraziamento particolare va a Dieter Bender che ha sviluppato questo comodo tool e l'ha rilasciato sotto licenza open-source GPLv2. Thanks Dieter.
Potrebbero interessarti anche i seguenti articoli:
by 
Ottimo e interessantissimo articolo !
(come sempre direi…..)
🙂
ciao
Grazie Mario, troppo gentile 😉
Una bomba 🙂
Soprattutto la parte che consente l’utilizzo dell’utente minuscolo!
Grazie Avo, si fa quel che si può 😉
Buongiorno, vorrei utilizzare questa soluzione per accedere a un database MySql versione 8 con utente “produzione”, ma l’istruzione CONNECT TO non funziona perchè il primo carattere è minuscolo. Ho fatto e rifatto tutti i passaggi dell’articolo, ma senza risultati. Qualche suggerimento? Grazie.
Ciao Renato,
hai provato ad indicare le seguenti proprietà nel file /JVAGATE/lib/global.properties?
ard.properties.XXXXX.user=mIo-UtentE
ard.properties.XXXXX.password=Mia-PassWord
dove “XXXXX” è il nome che hai dato al tuo database nel comando ADDRDBDIRE.
Come spiegato sopra, servono proprio quando l’utente contiene delle minuscole.
Indicando le credenziali nel file delle proprietà, NON serve indicarlo quando fail la CONNECT. Quindi la tua CONNECT diventa semplicemente:
CONNECT TO XXXXX
ciao
L’installazione mette il file global.properties nella cartella /JVAGATE/CONF; bisogna copiarlo in /JVAGATE/LIB. Grazie per la risposta.
Buongiorno. Da STRSQL funziona tutto, sia select, che insert into, che update che delete. Da RPG non riesco a fare nulla, non ricevo nessun errore, nulla. Ho provato a vedere il log ArdGateLog.log ma non riesco a vederci nulla.
Potete aiutarmi? Grazie
Ciao Renato,
hai compilato il tuo RPG con i parametri COMMIT(*CS) OPTION(*SQL) ?
Controlla anche il JOBLOG della sessione da cui richiami il programma perchè dovrebbe comunque darti degli errori SQL se non riesce ad eseguire le istruzioni che tu gli hai dato
Buogiorno, complimenti per l’articolo davvero molto interessante.
Devo leggere tabelle Microsoft SQL Server da AS400.
Sul sitema AS400 ho già trovato installata la procedura.
Ho trovato il file global.properties nella cartella /JVAGATE/CONF,
Devo copiarlo e/o spostarlo in /JVAGATE/LIB?
Grazie
Ciao Franco,
grazie per i complimenti.
No. E’ corretto il percorso “/JVAGATE/conf”. Ho sbagliato io nell’articolo e quindi l’ho corretto ora. Grazie per la segnalazione.
Salve,
nel file global. properties ho inserito le seguenti righe:
ard.url.PGDWS=jdbc:sqlserver://XXX.XXX.XXX.XXX/Testate
ard.driver.PGDWS=com.microsoft.sqlserver.jdbc.SQLServerDriver
ard.url.PGDWS=jdbc:sqlserver://XXX.XXX.XXX.XXX/Righe
ard.driver.PGDWS=com.microsoft.sqlserver.jdbc.SQLServerDriver
sono corrette?
Dal sito Microsoft ho scaricato i seguenti driver:
mssql-jdbc-8.4.1.jre8.jar
mssql-jdbc-8.4.1.jre11.jar
mssql-jdbc-8.4.1.jre14.jar
Per fermare il servizio è sufficiente terminare il lavoro nel sottosistema?
La stringa di connessione da riga comando?
CONNECT TO PGDWS USER mio-utente USING ‘Mia-PassWord’
Grazie infinite.
Le stringhe relative all’URL non mi sembrano corrette per MS Sql Server.
Dovrebbero essere qualcosa di simile a:
jdbc:jtds:sqlserver://xxx.xxx.xxx.xxx:pppp/tuo-dbname
dove:
xxx.xxx.xxx.xxx è l’IP del tuo database server (puoi indicare anche l’hostname)
pppp è la posta su cui è esposto il servizio (solitamente la 1433 per Sql Server)
ma soprattutto devi indicare il dbname corretto a cui vuoi collegarti. Ho la sensazione che quello che hai indicato tu (Testate e Righe) siano i possibili nomi delle tabelle.
Per quanto riguarda il comando di connessione è corretto ma fai attenzione nel caso in cui l’utente di connessione fosse “minuscolo” perchè l’AS lo invia sempre in MAISCOLO. In quel caso puoi mettere i parametri di connessione direttamente nel `global.properties` evitando di specificarli nel comando di connessione. Esempio:
ard.properties.PGDWS.user=mIo-UtentE
ard.properties.PGDWS.password=Mia-PassWord
PS: forse dall’articolo non è chiaro ma “PGDWS” è un nome di fantasia. Puoi dargli il nome che vuoi, l’importante che poi usi lo stesso nome nel comando “ADDRDBDIRE”
Premesso che sono digiuno di collegamenti tra db esterni e AS, devo collegare un db Firebird presente sul mio pc e leggerlo tramite SQL sull’AS
ho seguito passo passo le indicazioni, ma sql mi da sempre un’errore imprevisto in collegamento:
JDBCGATE: SQLCODE: -913 SQLSTATE 57033 FBB ActivationGroup: 2
JDBCGATE: timeout ocurred – check Server
Dati restituiti per il formato ARCN0100 non validi.
Come utente e pwd uso i canonici SYSDBA e mastekey. Non sono sicurissimo dei driver, perchè ne ho trovati diversi e non so quale sia quello giusto.
Il file global.properties l’ho modificato così:
### this must be the first line!!!
### installation parameters
###———————————————-
### DataQ section
########## configure these properties
# fill in the ip adresse or ip name of your as400
as400.system=LOCALHOST
# this user is used for the DataQ connect
### as400.user=*CURRENT
as400.user=UTENTE1
# password for user connecting to dataQ
### as400.password=*CURRENT
as400.password=utente1
#
########## caution! the LIB has to point to your install library
as400.dataqPath=/QSYS.LIB/JVAGATE.LIB/JAVAQ.DTAQ
as400.responseqLib=/QSYS.LIB/JVAGATE.LIB/
as400.timeout=60
########## you must not change the following properties
as400.ccsid=500
handler.ARDPGM=de.bender_dv.ardgate.application.ArdHandler
### example using Firebird
ard.url.FBB=jdbc:firebirdsql://xxx.xxx.xxx.xxx/c:/salam/DATABASE.FDB
ard.driver.FBB=org.firebirdsql.jdbc.FBDriver
ard.properties.FBB.user=SYSDBA
ard.properties.FBB.password=masterkey
handler.RUNJAVARUN=de.bender_dv.commandgate.CommandHandler
Help me!!!
Grazie
Ciao Roberto,
perdona il ritardo nella risposta ma in settimana ho veramente poco tempo da dedicare al blog (anche nel we 😉 ).
Firebird non l’ho mai usato e per indagare il problema bisognerebbe vedere i log, anche quelli di Firebird, dove, magari, trovi maggiori indicazioni.
Posso solo consigliarti i seguenti controlli:
– assicurati che la stringa di connessione sia corretta
– visto che Firebird è sul tuo PC, se sul PC (come mi auguro) hai un firewall, assicurati che non blocchi i tentativi di connessione da parte dell’AS400
– assicurati di aver messo il corretto driver JDBC di Firebird (il .jar) nella directory `/JVAGATE/lib`
– assicurati che utente e password rispettino la MAIUSCOLE e le minuscole
– se riesci a fare il collegamento ma poi ti da errore sulle SQL, prova ad indicare le colonne (i campi) nella SELECT tipizzandole. Mi è capitato con PostgreSQL che certi tipi di colonne non li gestisce bene e da errore, quindi facendo il cast con un tipo “standard” si risolve il problema
Spero di esserti stato di aiuto
ciao
Buonasera,
Vorrei collegarmi a un db Firebird su server aziendale ma ho un problema di questo genere alla connessione:
JDBCGATE: SQLCODE: -30061 SQLSTATE 08003 FB1
ActivationGroup: 2
JDBCGATE: configuration Error: missing Property ard.url.FB1 in
global.properties
JDBCGATE: SQLCODE: -596 SQLSTATE 01002 FB1 ActivationGroup:
2
JDBCGATE: Connection does not exist
qui di seguito il file global.properties:
### this must be the first line!!!
### installation parameters
###———————————————-
### DataQ section
########## configure these properties
# fill in the ip adresse or ip name of your as400
as400.system=LOCALHOST
# this user is used for the DataQ connect
as400.user=*CURRENT
# password for user connecting to dataQ
as400.password=*CURRENT
#
########## caution! the LIB has to point to your install library
as400.dataqPath=/QSYS.LIB/JVAGATE.LIB/JAVAQ.DTAQ
as400.responseqLib=/QSYS.LIB/JVAGATE.LIB/
as400.timeout=60
########## you must not change the following properties
as400.ccsid=500
handler.ARDPGM=de.bender_dv.ardgate.application.ArdHandler
########## for every entry in your RDBDIR you need corresponding configuration
### connsessione per gestione ordini using Firebird – effettivo
ard.url.FB1=jdbc:firebirdsql://xxx.xxx.xxx.xxx:3050/C:/home/datifirebird/csy_ordini.fdb
ard.driver.FB1=org.firebirdsql.jdbc.FBDriver
ard.properties.FB1.user=SYSDBA
ard.properties.FB1.password=xxxxx
### connsessione per gestione ordini using Firebird – TEST
ard.url.FB2=jdbc:firebirdsql://xxx.xxx.xxx.xxx:3050/C:/var/datifirebird/csy_ordini.fdb
ard.driver.FB2=org.firebirdsql.jdbc.FBDriver
ard.properties.FB2.user=SYSDBA
ard.properties.FB2.password=xxxxx
### CommandGate Section
handler.RUNJAVARUN=de.bender_dv.commandgate.CommandHandler
Ho più volte collocato il nella directory /JVAGATE/conf ma niente.
Grazie
Ho risolto il collegamento a Firebird, il db era montato su una macchina linux e non windows, quindi non occorreva C: nella stringa di connessione. Verificare anche che il driver JBDC sia adeguato alla versione del db in uso
Ciao Marco, ho provato a seguire la tua configurazione ma nin mi funziona. Potrebbe essere un problema di driver? tu quale hai usato. In caso è possibile contattarti?
Grazie Roberto
Buongiorno Marco, ho visto che dovresti aver chiaro/risolto la connettività tra AS400 e DB Firebird. Nel mio caso è montato su AS400. Riusciresti a dirmi quali files dei driver JDBC vanno caricati in /JVAGATE/BIN e quale stringa di connessione dovrei configurare (il mio DB Firebird risponde ad es. via windows al seguente url: jdbc:firebirdsql://192.168.100.160:5030/c:\dbtest\dbtest.fdb ) Grazie in anticipo per la risposta
Davvero tanto di cappello a Dieter Bender !
Sicuramente un tool molto utile che hai spiegato in modo chiaro.
come diceva qualcuno “….per me numero 1”
Grazie Paolo.
Davide Chiesa
Intanto complimenti per il tool. Premetto che sono un sistemista AS400 ma non mi occupo di Java e JDBC. La mia esigenza è di accedere ad un DB Firebird ad un indirizzo della lan da AS400 (da client SQL windows es. Dbeaver ho seguente stringa di connessione funzionante: jdbc:firebirdsql://192.168.100.160:5030/c:\dbtest\dbtest.fdb)
Cosa devo configurare/installare (a partire dai files dei driver e configurazione stringa connessione in /JVAGATE)?
Ciao,
ho installato con successo 2 anni fa il tool e ringrazio.
Ora però non mi serve più per cambio modalità operativa.
Mi puoi dare le istruzioni per disinstallare il tutto in particolare il servizio dall’As400?
Grazie mille
Ciao,
complimenti per la guida.
Ho collegato un DB postgres e la connect funziona regolarmente.
La select di un singolo campo per un singolo record funziona, ma appena provo a mettere un campo diverso oppure select * from mi esce un errore: MCH1210 Il valore del ricevente è troppo piccolo per contenere il risultato.
È stato riscontrato un errore interno durante l’elaborazione di una richiesta DRDA.
Stato dell’unità di lavoro logica impostato su ROLLBACK RICHIESTO. Codice causa 5.
Errore di protocollo DRDA (Distributed Relational Database Architecture).
Il comando RUN QUERY non è riuscito con SQLCODE -30020.
È richiesto il ROLLBACK.
JDBCGATE: CCEXIT initialized
JDBCGATE: CCEXIT fired████2
Cosa potrebbe essere. Grazie in anticipo.
Buongiorno,
stiamo facendo alcuni test di connessione ad un DB MSSQL tramite jvagate.
Riusciamo ad accedere alle tabelle ma non alle viste presenti sul server Microsoft, che voi sappiate c’è qualche limitazione sulla tipologia di oggetti a cui accedere?
grazie
Flavio
Buongiorno e grazie per la condivisione, mi è capitato che dopo l’esecuzione del programma RPG in cui gestivo il database esterno, dalla stessa sessione avevo problemi ad utilizzare un programma SQLRPGLE perchè non si connetteva più al database locale in modo corretto….ho risolto inserendo una CONNECT RESET prima della chiusura(LR) del programma che gestiva il database esterno…..non so se può essere utile a qualcuno….