UGISS User Group Italiano SQL Server
L’iSeries o AS/400 che dir si voglia, è uno dei sistemi più diffusi in Italia, capita quindi spesso di dover effettuare estrazioni o stabilire canali di comunicazione tra il mondo SQL Server ed il mondo iSeries.
Se si tratta di un ETL (SSIS) per costruire il Datawarehouse la cosa è abbastanza semplice: si setta un DSN utilizzando il driver nativo di IBM per gli iseries ed il gioco è (quasi) fatto. Diversa cosa è settare correttamente un linked server verso AS/400 utilizzando SQL Server 2008 o 2008 R2 su architettura a 64 bit.
Non abbiamo mai registrato problemi utilizzando anche versioni vecchie di Client Access o Personal Communicator (i vecchi nomi di iAccess) su architetture a 32 bit sia SQL server 2005 che SQL Server 2008, i problemi di comunicazione si presentano invece con vari errori sui 64 bit e su SQL Server 2008 R2. Partendo dal presupposto che non possiamo creare un linked server basato su provider ODBC 32, se sto operando su un SQL Server in architettura 64, dobbiamo procurarci un driver per iseries a 64 bit, presente soltanto dalla versione V6R3M0 di iAccess. Se proprio non è possibile ottenere una versione aggiornata di iAccess, allora esiste un altro “workaround” con però alcune limitazioni di cui parlerò al termine del post.
Prima di tutto controllate di avere installato sul server le ultime versioni di service pack di SQL Server 2008:
Configurate l’ODBC 64 scegliendo dalla lista il driver iSeries Access ODBC Driver:
Si inserisce l’indirizzo ip dell’iSeries dando un nome appropriato all’origine dati:
In elenco librerie si inseriscono le librerie di iSeries in cui sono inserite le tabelle che ci interessano (in AS/400 il concetto di tabella è un po’ diverso, nel mondo AS le tabelle dati sono definite come file fisici e le viste come file logici e quelli che sono schema o catalog in SQL Server in iSeries si definiscono librerie, anche se il concetto è più vicino a quello delle cartelle di file):
Tutti gli altri parametri possono essere lasciati di default, tranne sulla tab Prestazioni dove deve essere tolto il flag su Abilita pre-richiamo dei dati per le query:
A questo punto l’origine dati ODBC è settata correttamente e si può procedere a creare il linked server su SQL Server, settando prima di tutto il provider utilizzato dal Linked Server per connessioni ODBC, MSDASQL:
Settando l’opzione Allow in process a enabled:
Si può ora procedere nella configurazione del linked server scegliendo come provider Microsoft OLE DB Provider for ODBC Driver, inserendo nel Product name e data source il nome dato alla nostra origine dati ed infine dando nella connection string nome utente e password per accedere all’AS/400 (normalmente l’utente valido è QPGMR):
Passare alla pagina delle Server Options e impostare a True le opzioni RPC e RPC out, ed a False l’opzione Use Remote Collation:
Di seguito lo script completo dell’azione che è sicuramente più veloce da lanciare:
Infine il “workaround” se non abbiamo a disposizione un ODBC iSeries a 64 bit: in questo caso possiamo utilizzare per la creazione di linked server il driver OLE DB fornito anch’esso da IBM con le varie versioni di Client Access/Personal Communicator/iAccess (mantenessero sempre lo stesso nome!!). Si possono usare indifferentemente sia il provider “IBM DB2 for i5/OS IBMDA400 OLE DB Provider” che “IBM DB2 for i5/OS IBMDASQL OLE DB Provider” come prima va però settato qualche parametro del provider, Allow inprocess, Dynamic parameter (necessario se vogliamo utilizzare query con variabili e parametri dinamici) e Support ‘Like’ operator devono essere esplicitamente abilitati:
A questo punto passiamo alla creazione del linked server inserendo nel campo Product name un nome identificativo a piacere mentre nel campo Data Source va inserito l’indirizzo IP del nostro iSeries, infine l’importante è la stringa di connessione che deve essere inserita come segue:
User Id=uuuu;Password=pppp;Default Collection=LibraryName;
E qui si intuisce già il principale “drawback” di questo metodo: posso specificare una sola libreria dati di AS/400 per il collegamento, quindi se devo estrarre dati da più di una libreria devo: o creare due linked server distinti oppure creare delle viste logiche su AS/400 sotto la libreria collegata che puntino ai file fisici contenuti nelle altre librerie. Ed in questo caso devo avere un minimo di supporto da chi segue l’AS in azienda.
Infine vado a settare come prima i parametri della pagina Server Options:
Anche in questo caso, di seguito lo script di creazione del linked server a cui andranno sostituiti i parametri di connessione con quelli appropriati del vostro iSeries:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver@server = N’AS_400′, @srvproduct=N’as400′, @provider=N’IBMDA400′, @datasrc=N’10.0.1.x’, @provstr=N’User Id=qpgmr;Password=pppp;Default Collection=acg_datv3;’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’use remote collation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N’AS_400′, @locallogin = NULL , @useself = N’False’
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver@server = N’AS_400′, @srvproduct=N’as400′, @provider=N’MSDASQL’, @datasrc=N’as400′, @provstr=N’Uid=qpgmr;Password=XXX’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’use remote collation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption@server=N’AS_400′, @optname=N’remote proc transaction promotion’, @optvalue=N’true’
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin@rmtsrvname = N’AS_400′, @locallogin = NULL , @useself = N’False’
GO
