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
midrange.com
