August 30, 2006 at 12:40 pm
I need to retrieve data from Informix tables and make this information available through a mssql database. This can probably be done by linking both database servers using a stored procedure similar to:
EXEC sp_informixserver
@server='db_server',
@provider='MSDASQL',
@datasrc='db_odbc',
@srvproduct='',
@provstr='DSN=db_odbc;Database=db_name;UID=db_usr;PWD=db_pwd'
Is this the best way to accessing the tables on the Informix server?
August 31, 2006 at 8:45 am
Just an idea:
I have to retrieve data from tables on an AS400, and use a "Linked Server" to do it. In our case, the AS400 is set up as a Linked Server [AS400_Linked]. The Linked Server can be address as follows: [LinkedServerName].[Database].[Library].[TableOrView].
I don't, allow applications to address the linked server directly. Instead, there is an abstraction layer so our applications actually address a SQL view instead of the Linked Server. We have a database on the SQL Server [dbAS400], and within that database, views like:
CREATE VIEW dbo.vwEmpInfo
AS
SELECT A_CONO
, A_LNAME AS LastName
, A_SSNO AS SSNo
FROM [AS400_Linked].[S101422A].[QTEMP].[EMPMST]
Using OLE or ODBC drivers you should be able to setup your Informix server as a Linked Server. Then either address it directly or through views or stored procedures.
September 1, 2006 at 8:46 am
It's hard to say what is the "best" way, but the typical way to access tables on another server from MSSQL is to define a linked server and use four-part names as shown above. This works very well in most cases, but four-part names are not allowed everywhere. You can't do "SELECT ... INTO Link.Db.User.Table" for instance (not that you'd want to). After you have defined a linked server there are some other issues to consider.
The performance varies depending on what kind of queries you are running. A select query with that returns a few rows will perform very well. Some queries that join a remote table to a local table may copy the entire remote table over the network every time the query runs. If the Informix tables are large you should examine the query plans you get to make sure they are optimal. You may want to think about copying some of the Informix tables to the SQL Server. Replication might be an answer, or customize your own cacheing strategy. If your servers are configured to allow RPC, you can use "EXEC Link.Db..sproc <args>" to run remote stored procedures, which may be preferable to direct table access from a security standpoint.
SQL extensions and non-standard language features may not work with a linked server, but you can pass-thru a query with OPENQUERY instead. For instance if I want to use table locking hints to do a non-blocking read of a remote table, it is illegal to use "SELECT COUNT(*) FROM Link.Db..Table WITH(NOLOCK)" but you can use "SELECT * FROM OPENQUERY(Link, "SELECT COUNT(*) FROM Db..Table WITH(NOLOCK)"). Even though the remote server is a SQL Server that understands the locking hint, it can't be used with a four-part name. I don't know what nonstandard features you might use in Informix, but you need OPENQUERY to use them.
If you are doing inserts, updates, or deletes you have to make sure Distributed Transaction Controller will cooperate.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply