CODEWITHSUNDEEP
sqlsql-serverdb2ibm-midrangedata-integration
ghoston3rd
ghoston3rd

Reputation: 149

Data integration between IBM AS400 to SQL Server database

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?

Upvotes: 2

Views: 13840

Answers (3)

Richard Evans
Richard Evans

Reputation: 384

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.

Upvotes: 1

bdongus
bdongus

Reputation: 668

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.

Upvotes: 1

Fuzzy
Fuzzy

Reputation: 3810

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

Upvotes: 1

Related Questions