Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

AS400 to SQL? Expand / Collapse
Author
Message
Posted Thursday, June 21, 2012 8:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:38 AM
Points: 688, Visits: 757
Hi Friends,

i am trying to transfer data from AS400 to SQL through SSIS. i googled about this and i found that i need DB2 provider or IBM iseries providers..

i downloaded Microsoft DB2 provider but when i installed in production server, it says it is supported for 2005 which is not installed. i tried on my machine which is having sql 2008 and it worked out. Production server is having sql 2008 r2 but it gives such kind of error...

so i am looking to download IBM iseries access but i am getting any link where to download it....

Could guys help me? your help would avoid me going under bad situation..


Thanks,
Charmer
Post #1319338
Posted Thursday, June 21, 2012 9:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:52 AM
Points: 89, Visits: 995
MS ole db provider for db2 requires Enterprise edition of sql.
..and I believe for you to obtain IBM IseriesClientAccess software from IBM site, you'll need IBM userid/password if you are entitled for a software support.

I use both Iseries Client access and oledb provider for db2 and either works pretty well. Our production servers use the ClientAccess on our ssis packages to ETL between SQL and as400.
My client machine has developer edition of sql, so I use MS oledb to connect to iseries through linked server.


_____________
Donn Policarpio
Post #1319391
Posted Thursday, June 21, 2012 9:29 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:38 AM
Points: 688, Visits: 757
donpolix-313947 (6/21/2012)
MS ole db provider for db2 requires Enterprise edition of sql.
..and I believe for you to obtain IBM IseriesClientAccess software from IBM site, you'll need IBM userid/password if you are entitled for a software support.

I use both Iseries Client access and oledb provider for db2 and either works pretty well. Our production servers use the ClientAccess on our ssis packages to ETL between SQL and as400.
My client machine has developer edition of sql, so I use MS oledb to connect to iseries through linked server.


could you explain how to connect through linked server?

Here i have attached the error ...Please take a look at it...


Thanks,
Charmer


  Post Attachments 
Error.PNG (16 views, 22.50 KB)
Post #1319407
Posted Thursday, June 21, 2012 1:57 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:52 AM
Points: 89, Visits: 995
What edition of sql server do you have in your production server? Verify if you have the enterprise edition.

You may download DB2OLEDB packages here (not sure if these are the latest for each sql versions though)

DB2OLEDB (sql2005)
http://www.microsoft.com/en-us/download/details.aspx?id=15748

DB2OLEDB (sql2008)
http://www.microsoft.com/en-us/download/details.aspx?id=16978


I only use linked server so I can run distributed queries against as400 from my SSMS.

Once you have the ibm client access installed, you'll have "IBMDA400" available as your provider (In SSMS, expand Server Objects->Linked Servers->Providers) and you can use it to execute your distributed query.

--add IBMDA400 linked server
sp_addlinkedserver
@server=N'MY_AS400_SRV',
@srvproduct=N'IBM AS400 OLEDB Provider',
@provider=N'IBMDA400',
@datasrc=N'MY_AS400_SRV',
@provstr=N'Initial Catalog=MY_AS400_SRV;Default Collection=MY_LIBRARY_LIBF;Force Translate=65535;Transport Product=Client Access;Convert Date Time To Char=FALSE;Block Size=1024;'
go


sp_addlinkedsrvlogin @rmtsrvname=N'MY_LIBRARY_LIBF',
@useself='false',
@rmtuser=N'MYUSERNAME',
@rmtpassword='MYPASSWORD'
go



--Here's how you can add a linked server using DB2OLEDB:

sp_addlinkedserver
@server=N'MY_AS400_SRV',
@srvproduct=N'Microsoft OLE DB Provider for DB2',
@provider=N'DB2OLEDB',
@datasrc=N'MY_AS400_SRV',
@provstr='NetLib=TCPIP;NetAddr=MY_AS400_SRV;NetPort=446;RemoteLU=GENESIS;LocalLU=LOCAL;ModeName=QPCSUPP;User ID=MYUSERNAME;Password=MYPASSWORD;InitCat=MY_AS400_SRV;Default Schema=MY_LIBRARY_LIBF;PkgCol=MY_LIBRARY_LIBF;TPName=;Commit=YES;IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;Data Source=MY_AS400_SRV',
@catalog='MY_AS400_SRV'
GO


--to drop the linked server
sp_droplinkedsrvlogin @rmtsrvname=N'MY_AS400_SRV',@locallogin=Null
go
sp_dropserver @server=N'MY_AS400_SRV'
go


--Note: You may need to enable “Allow inprocess” for IBMDA400 provider. In management studio, go to Linked Servers->Providers->IBMDA400->Right click->Properties


Once you have your linked server in place you can run your four part name queries,..
For example:
SELECT * FROM MY_AS400_SRV.MY_AS400_SRV.MY_LIBRARY_LIBF.MYAS400FILE



Or use OPENROWSET...


--Using IBMDA400 in openrowset:
SELECT * FROM OPENROWSET
('IBMDA400','Data Source=MY_AS400_SRV;Initial Catalog=MY_AS400_SRV;USER ID=MYUSERNAME;Password=MYPASSWORD;Default Collection=MY_LIBRARY_LIBF;Force Translate=65535;Transport Product=Client Access;Convert Date Time To Char=FALSE;Block Size=1024;',
'
SELECT * FROM MY_LIBRARY_LIBF.MYAS400FILE WHERE FIELD1 = ''TEST''
'
)


--Or using DB2OLEDB in openrowset:
SELECT * FROM OPENROWSET
('DB2OLEDB','User ID=MYUSERNAME;Password=MYPASSWORD;Initial Catalog=MY_AS400_SRV;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=MY_AS400_SRV;Network Port=446;Package Collection=MY_LIBRARY_LIBF;Default Schema=MY_LIBRARY_LIBF;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=MY_LIBRARY_LIBF;DBMS Platform=DB2/AS400;Defer Prepare=False;Persist Security Info=False;Connection Pooling=False;Derive Parameters=False;',
'
SELECT * FROM MY_LIBRARY_LIBF.MYAS400FILE WHERE FIELD1 = ''TEST''
'
)



When you design or execute your ssis packages, you should have either of the two providers installed on the machine. When you create a new OLEDB connection, you should see something like "IBMDA400 OLE DB Provider" or "Microsoft OLE DB Provider for DB2" depending on which package is installed.


Hope this helps.


_____________
Donn Policarpio
Post #1319623
Posted Friday, June 22, 2012 2:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:38 AM
Points: 688, Visits: 757
The client server has standard edition of sql 2008R2...

what shall i do..?


Thanks,
Charmer
Post #1319816
Posted Friday, June 22, 2012 8:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:52 AM
Points: 89, Visits: 995
Since you have AS400 systems, I'm assuming you're entitled for ibm support, so you may wanna go for ClientAccess and have it installed on your standard ed sql. If you have 64 bit system, there is a patch you need to apply before you install clientaccess.

Whether you wanna upgrade to sql enterprise and use db2oledb, or use clientaccess is up to you.
There are other software out there for sure that serves the same function so you may wanna search if you haven't done so.


_____________
Donn Policarpio
Post #1320011
Posted Friday, June 22, 2012 11:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:00 PM
Points: 4,175, Visits: 4,257
For a SQL Server 2008 R2 64 bit System I use IBM System Access for Windows V6R1 SI42423 (64-bit) on Windows 2008 R1 64-bit with SQL Server.

As was previously stated you will need an IBM Account to get the drivers.

The following are Microsoft prerequisites for the 64-bit version of IBM iSeries Client v6.1 SI42423.
1. Microsoft Visual C++ 2005 SP1 Redistributable Package (x86)
http://www.microsoft.com/download/en/details.aspx?id=5638
Note - This 32-bit fix needs to be applied even though it is a 64-bit operating system
2. Microsoft Visual C++ 2005 SP1 Redistributable Package (x64)
http://www.microsoft.com/download/en/details.aspx?id=18471
3. Both the 32-bit and 64-bit versions of Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update
http://www.microsoft.com/download/en/details.aspx?id=14431
Note - download and install both vcredist_x64.exe (64-bit) and vcredist_x86.exe (32-bit) even though it is a 64-bit operating system

When you create the ODBC Connections pay close attention to the settings.

HTH.




For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1320122
Posted Friday, June 22, 2012 11:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
Welsh Corgi (6/22/2012)


Thanks for providing some feedback. I'm sure the OP appreciates it also.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1320125
Posted Monday, June 25, 2012 6:59 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:38 AM
Points: 688, Visits: 757
Welsh Corgi (6/22/2012)
For a SQL Server 2008 R2 64 bit System I use IBM System Access for Windows V6R1 SI42423 (64-bit) on Windows 2008 R1 64-bit with SQL Server.

As was previously stated you will need an IBM Account to get the drivers.

The following are Microsoft prerequisites for the 64-bit version of IBM iSeries Client v6.1 SI42423.
1. Microsoft Visual C++ 2005 SP1 Redistributable Package (x86)
http://www.microsoft.com/download/en/details.aspx?id=5638
Note - This 32-bit fix needs to be applied even though it is a 64-bit operating system
2. Microsoft Visual C++ 2005 SP1 Redistributable Package (x64)
http://www.microsoft.com/download/en/details.aspx?id=18471
3. Both the 32-bit and 64-bit versions of Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update
http://www.microsoft.com/download/en/details.aspx?id=14431
Note - download and install both vcredist_x64.exe (64-bit) and vcredist_x86.exe (32-bit) even though it is a 64-bit operating system

When you create the ODBC Connections pay close attention to the settings.

HTH.






Hi Corgi, Thank you for the ideas...

i am having some concerns....If my client machine does not agree with upgrading from sql standard edition to enterprise edition, what should i do? And i explained to my higher authorities that "we can ask our client to get the IBM drivers since they use AS400, they must have entitled for software support with IBM...so that they can get the drivers from IBM and we can use it for our data transformation to SQL..." but my authority said that "we must not ask clients for anything unless we have no other option".....So what should i do now?
I am still unable to connect to the AS400 to SQL server....i am out of ideas....so i am totally depend on you guys..

Help me please...


Thanks,
Charmer
Post #1320607
Posted Monday, June 25, 2012 7:02 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 5:38 AM
Points: 688, Visits: 757
Lynn Pettis (6/22/2012)
Welsh Corgi (6/22/2012)


Thanks for providing some feedback. I'm sure the OP appreciates it also.



yes of course Lynn, I totally appreciate it..because feed back is not just a feed back...its a help when ever we are out of help...Thank you to Lynn and Welsh Corgi....


Thanks,
Charmer
Post #1320610
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse