2

I'm a web developer that has been tasked with creating some sort of mechanism for moving data from an IBM AS400 to a SQL server. Unfortunately, linked servers are out of the question in this case as the SQL Server is just Standard Edition (db2 providers not available in this version) and the AS400 server is on a separate server. I've researched adding some sort of trigger on the AS400 table that calls a web service that would insert data into the SQL server, but that doesn't seem like the best method. Does anyone have any suggestions on the process to get the data from the AS400 to the SQL Server when it is committed to the AS400?

ghoston3rd
  • 129
  • 2
  • 5
  • 14
  • SSIS would be your best bet. – Fuzzy Feb 23 '16 at 15:41
  • Do you have access to IBM's iSeries Access tools? Your AS/400 admin team may be able to help you install it on to your SQL server. This will give you some OLE DB providers that will connect to the AS/400 as a linked server. – Tracy Probst Feb 23 '16 at 23:14
  • Correct me if I'm wrong, but the OLE db2 providers are not accessible from the standard version of SQL server, only Enterprise and Development editions can use the db2 providers to add a linked server. – ghoston3rd Feb 24 '16 at 12:57
  • That's why using the IBM provider instead of Microsoft's *might* be a solution. I don't really know for sure without testing and I don't have that version installed anywhere handy. – Tracy Probst Feb 24 '16 at 16:56
  • In general, "AS400" is an obsolete term and refers to effectively obsolete hardware. It's kind of like saying "Windows" when you mean "Windows 8" rather than "Windows 2000". Asking for solutions gets tricky if you actually mean "iSeries" or some later version of the platform. What is the version of the "AS400" OS? That can help in knowing what you might use. – user2338816 Feb 24 '16 at 22:37
  • i5 Version 7 Release 1. Does that help? – ghoston3rd Mar 01 '16 at 17:55

3 Answers3

1

This solution assumes you are familiar with SQL Server Integration Services (SSIS):

Connection to AS400

  1. Create a new ADO.Net connection Manager
  2. Set the Provider to .Net Provider --> ODBC Data Provider
  3. Create a DSN (Control Panel -->Administrative Tools-->Data Sources ODBC -->System DSN)
  4. In the connection manager for Data source specification select the DSN created. Provide the login information.
  5. Test the connection.

Data flow source:

  1. Use the DataReader source
  2. In Advanced Editor select the Ado.Net connection manager just created.
  3. In Component Properties tab --> Custom properties, in SQLCommand specify the required query string (select * from DatabaseName.TableName)
  4. Check the column mappings for accuracy
  5. Go to Input and Output properties -->Data reader output -->External columns (Select the columns which were of type varchar in the table, they will now be of the datatype UnicodeString (DT_WSTR). This is because by default DataReader reads strings as unicode strings. This implies that in the destination table in SQL these columns must be of type unicode i.e NVARCHAR instead of VARCHAR)

Answer sourced from www.sqlservercentral.com/Forums

Fuzzy
  • 3,810
  • 2
  • 15
  • 33
  • I am not familiar with SSIS. – ghoston3rd Feb 23 '16 at 15:52
  • @ghoston3rd chances are you have it already installed if you have SQL server installed. you can try and play around until you get your head around it but I understand that it may be a tall order. if you are interested here is a link. https://msdn.microsoft.com/en-us/library/ms169917.aspx – Fuzzy Feb 23 '16 at 15:55
  • Thank you, I will give that a shot. – ghoston3rd Feb 23 '16 at 15:58
  • As it turns out the client does not have SSIS installed. Any other suggestion for me to pursue? – ghoston3rd Feb 23 '16 at 19:12
  • @ghoston3rd I suppose you could go the .NET route. But my expertise in that is limited, I'd be googling it :) – Fuzzy Feb 24 '16 at 13:12
1

I synchronize my web applications with an IBM i. But I have my own database design and wrote a sync program on the Windows side.

Having the same database design I wonder why I would need a copy on SQL server. I would access the IBM directly. Install the drivers as @Kamran Farzami suggested and use them. That way there would be no lag between writes on the mainframe and your queries.

If a lag is acceptable for you and you can't access the IBM i directly, I see three main options:

  1. Pull the data from your Windows system with the OLE DB driver. Using the .NET driver you can use the relative record number (RRN) to remember where you stopped synchronizing.
  2. Read the journal files and make them available by creating a webservice on the IBM i.
  3. Read the journal files in a scheduled job and push the changes from the journal to a webservice which updates the SQL server.

Option 1 only works if the files you sync are not reorganized. The RGZPFM command changes the record numbers. If that's okay, you can get the RRN in your SELECT statement: select *, RRN(MYTABLE) as RRNMYTABLE from MYTABLE

The web service server is included in OS400 since V5R4. So you should be able to use option 2.

bdongus
  • 658
  • 4
  • 20
  • Regarding option 0, accessing the IBM directly with the DB2 OLEDB provider, it depends on what you intend to do with the data. If it is going into an application's objects this works well. If you intend to do SQL JOINS between it and the SQL Server data, you are not going to be able to do this across two different OLEDB providers. – Mike May 10 '18 at 14:56
1

I've done something similar where the SQL server was in a remote (Honduras) location where the internet connection was unreliable. It was a short VB program, using the OLE DB driver, running on the server that connected to the AS400 when it was available (or "slept" when the connection was down). When available the program would update/synchronize a uniquely keyed mirror file. Another program uploaded individual transaction records to a separate table (file).

We'd also periodically update SQL Server master tables (i.e. item master) from the AS400. That also utilized a VB program (could be any language using the driver) initiated on the server. It isn't exactly elegant, but more practical than an AS/400 trigger to a web service, I believe.

Richard Evans
  • 384
  • 2
  • 10