I don’t think triggers would do what I want. It could be used to do an immediate data copy to the MS SQL server but that would not help with the task of keeping the column definitions in sync.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Tuesday, November 21, 2017 8:23 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: replicate DB2 table to MS SQL
You mean like triggers and stuff?
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Mike Cunningham <mike.cunningham@xxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 11/21/2017 08:14 AM
Subject: RE: replicate DB2 table to MS SQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
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.
--
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
