Thanks for all the responses. I have a good idea what to do here and will probably start with the SSMS process as it sounds like the best path. No coding needed, just configuration. I would prefer to have control on the IBM side of the process just because that is where 99% of our inhouse expertise is and we already have things in place on the IBM side to monitor automated tasks like this. Wish DB2 had a built in process like this :)
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Craig Pelkie
Sent: Tuesday, November 21, 2017 1:56 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Subject: RE: replicate DB2 table to MS SQL
On SQL Server, configure a linked server to your IBM i
1. In SSMS, expand Server Objects, expand Linked Servers.
2. Right-click Providers, add a new provider. Use the IBMDA400 (OLE DB) provider. On the Properties page for the provider, be sure to check the "Allow inprocess" provider option. (This implies that you have installed the IBM i Access for Windows database providers on the MSSQL server machine, include the "OLE DB" option in addition to ODBC.) BE SURE YOU ALSO INSTALL THE CURRENT SERVICE PACK for the version of IBM i Access for Windows on the MSSQL machine.
3. After creating the Provider, right-click Linked Servers, add a new linked server. Enter a name for the linked server (suggestion: the IBM i host name), select "Other data source". Select IBMDA400 as the provider. Enter a product name (can be anything, I always enter IBMDA400). For Data source, specify TCP/IP host name or address of IBM i. For Provider string, enter a value like this:
User ID=user_name;Password=password;Catalog Library List=lib1,lib2
For "user_name", put in the name of IBM i user profile with authorization to the library/dbfile you will be using For "password", put in the password for the IBM i user profile For "lib1,lib2" put in the name(s) of the library(ies) that you want access to. Suggestion: just use one library, the one that has the dbfile you are working with. Don't use QTEMP.
Suggestions: create a "SQL Server xfer library", just copy the dbfiles that you need to transfer into that library. Create a user profile/password that is only authorized to that library/dbfiles. You can configure a linked server against a production library with a production user profile is you want to, but it may be better to keep these things somewhat separated.
4. Once you have the linked server created, you should be able to expand it and see the tables that are in the library that you specified. You can right-click on those tables and run SQL commands.
5. Once you have the linked server created and tested, you can enter a SELECT INTO statement in SSMS like this:
SELECT *
INTO sqldb.dbo.sqltable
FROM ibmi.rdbdire.library.table
Where "sqldb.dbo.sqltable" is the fully qualified name of the SQL Server table to write data to, "ibmi.rdbdire.library.table" is the linked server name (ibmi), the relational database directory entry (from WRKRDBDIRE), the library name and the table name you want to work with.
The SQL Server table is created if it does not exist, with the appropriate column names/attributes.
You can also use the (preferred) list of columns instead of the *. Example
SELECT CUSNUM, LSTNAM, STREET
INTO SQL400.dbo.QCUST
FROM M270.S105HMNM.QIWS.QCUSTCDT
You can go the other way (SQL Server -> IBM i) with an INSERT INTO
statement:
INSERT INTO M270.S105HMNM.QIWS.QCUSTCDT
(CUSNUM, LSTNAM, STREET)
SELECT CUSNUM, LSTNAM, STREET
FROM SQL400.dbo.QCUST
You can create scripts in SSMS (.sql files) and manually run the scripts, invoke them through SQL Server Agent, or run them from a Windows command prompt using the (SQL Server provided) SQLCMD command.
Craig Pelkie
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Mike Cunningham
Sent: Monday, November 20, 2017 5:46 PM
To: Midrange Systems Technical Discussion
Subject: replicate DB2 table to MS SQL
I have a need to replicate a DB2 table to MS SQL. I know I could manually create the MS SQL table with the same column names and attributes as the DB2 table and then coy the data between the two systems but the DB2 tables in question change on a fairly frequent basis. A column is added or dropped on average about once a week. (in reality its more like 4 changes made one time a month but average is one a week). I would prefer to just make the change to the DB2 table and have that change automatically replicated to the MS SQL table. In my case I can drop and recreate the MS SQL tables on every sync process so there would be no need to do an ALTER TABLE on the MS SQL side.
Does anyone know of any utilities that might do this already so I don't need to grow my own utility?
Thanks
Mike Cunningham
________________________________
This email may contain confidential information about a Pennsylvania College of Technology student. It is intended solely for the use of the recipient.
This email may contain information that is considered an "educational record" subject to the protections of the Family Educational Rights and Privacy Act Regulations. The regulations may be found at 34 C.F.R. Part 99 for your reference. The recipient may only use or disclose the information in accordance with the requirements of the Federal Educational Rights and Privacy Act Regulations. If you have received this transmission in error, please notify the sender immediately and permanently delete the email.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
________________________________
This email may contain confidential information about a Pennsylvania College of Technology student. It is intended solely for the use of the recipient. This email may contain information that is considered an “educational record” subject to the protections of the Family Educational Rights and Privacy Act Regulations. The regulations may be found at 34 C.F.R. Part 99 for your reference. The recipient may only use or disclose the information in accordance with the requirements of the Federal Educational Rights and Privacy Act Regulations. If you have received this transmission in error, please notify the sender immediately and permanently delete the email.
midrange.com
