Re: RE: Informix and SQL Server

RTFMM is in order. But I hace something pending: That IDS could behave as an OLE DB provider. Anyone knows how to do it?... I believe that someone refere to this topic previously stating that some querys must be run first in order to IDS to work as a OLE DB provider. Another question is OLE DB better that ODBC? J. -----Original Message----- From: "Savio Pinto (s )" <spinto@cap.org> To: "Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com>, "William Weaver" <wweaver@amicus.com> Date: Tue, 15 Feb 2005 16:53:54 -0600 Subject: RE: Informix and SQL Server the books online(help) of sqlserver has pretty good information on how to create link servers, they have examples on how to setup a link server and how to run distributed queries. i beleive you can also use linked servers in DTS packages (including oracle), Here are few tips, i got it from the help section of sqlserver "DTS driver support for heterogeneous data types" The Merant Informix OLE DB provider is supported for DTS imports from Informix, but not DTS exports to Informix. This driver also cannot be used to import meta data. The Intersolv Informix ODBC driver is supported, but with the following restrictions: BLOBs cannot be exported to Informix. When creating new tables on Informix, the DTS Import/Export Wizard will incorrectly map the SQL Server 2000 datetime columns to the Informix 'Datetime year to fraction' data type. Manually change this to the Informix Date type. The DTS meta data import will not import Informix catalog or table information. -----Original Message----- From: Sebastian, Norma J. [mailto:NormaJean.Sebastian@tellabs.com] Sent: Tuesday, February 15, 2005 4:24 PM To: Savio Pinto (s); William Weaver Cc: informix-list@iiug.org; owner-informix-list@iiug.org Subject: RE: Informix and SQL Server Tell me more about this 'linked server' stuff. We have oracle, informix, and sqlserver. I am trying to set up a scenario to help Bill without him having to buy another tool. I created a Sqlserver DTS to dump some oracle data from a unix oracle db to my PC. I was thinking Bill could create a job on the informix side to dump whatever data is needed to a file, then use DTS to ftp the file somewhere in the sqlserver domain, and have sqlserver DTS the dump file in and manipulate it accordingly. But if linked servers could do the trick... Please explain a bit further and I will test here. Thanks, Norma Jean -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Savio Pinto (s) Sent: Tuesday, February 15, 2005 10:02 AM To: William Weaver Cc: informix-list@iiug.org; owner-informix-list@iiug.org Subject: RE: Informix and SQL Server sqlserver has something called as "linked servers" where you can connect to other database servers using odbc data sources, you can then run distributed queries from sqlserver. -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of Martin Fuerderer Sent: Tuesday, February 15, 2005 2:57 AM To: William Weaver Cc: informix-list@iiug.org; owner-informix-list@iiug.org Subject: RE: Informix and SQL Server Hi, there's "Informix Enterprise Gateway Manager" (as others have already suggested), but as far as I remember, it provides to Informix (server or applications) access to other databases, but not the other way round. I.e. it cannot let SQL Server make a connection to IDS. At least my understanding of it. For more info check out http://www.ibm.com/software/data/informix/tools/egm/ Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management owner-informix-list@iiug.org wrote on 14.02.2005 22:41:43: > Nobody seems to want this one! Please, if anyone can help I would really appreciate it! > > Thanks! > > Bill Weaver > Director of Engineering > Amicus, Inc. > 512-531-3463 (office) > 512-531-3401 (fax) > -----Original Message----- > From: William Weaver [mailto:wweaver@amicus.com] > Sent: Thursday, February 10, 2005 4:03 PM > To: 'informix-list@iiug.org' > Subject: Informix and SQL Server > > > It?s been a while since I?ve been in the Informix world (sigh) as I?ve been working lately > with a Sybase database (yuck!) so I need some help getting back into things with Informix. > > I have a client who needs to connect a Microsoft SQL Server database > to an Informix database. > What is the best driver for this in terms of both speed and data type conversion? > Of special concern is Informix date variables to SQL Server native datetime. > > Thanks! > > Bill Weaver > Director of Engineering > Amicus, Inc. > 512-531-3463 (office) > 512-531-3401 (fax) sending to informix-list sending to informix-list ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================ sending to informix-list Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list

Based on your comments, doing a direct-connect appears to be fraught with enough risk that maybe you should just unload then load data between servers. A bit cumbersome, but perhaps more reliable than depending on gateway products that may or may not work. I don't know much about OLEDB or ODBC other than to say that they will give you a performance hit on load/unload to/from your database, and DTS packages are notoriously slow. You also have to consider __where__ the DTS package will run, as it uses local resources--this means be careful not to run it on a low-end workstation if you're interested in speed. Also keep in mind SQL-Server is going to stay logged, and you cannot turn off logging, so you have to deal with that. The fastest/bestest way if I were doing it is to unload to disk, then bcp to SQL-Server especially if your data volumes are large. If time or performance are not an issue, then ODBC might be what you want, using commercial ODBC drivers on both servers. Alternatively I'd even be tempted to write perl scripts to do the loading into SQL-Server, just to do the data conversions accurately. If you go ODBC, then I'd assume you're running Informix on a UNIX or Linux box and you need to buy an ODBC driver like Merant to connect to the SQL-Server machine. There are several, follow the money at www.unixODBC.org and you'll find out who is in control of ODBC drivers for UNIX and Linux. But you could also do the unload-from-Informix and BCP to SQL-Server using FreeTDS libs if Informix is on a UNIX or Linux box if you want to save money. ( www.freetds.org ) The FreeTDS is FREE ( hence the name ) and takes about 10 minutes to compile and install on your linux machine. Once you install FreeTDS, you set up an INI file to connect to SQL-Server. You can now BCP using ( guess the name? ) freebcp to the SQL-Server machine. If FreeTDS doesn't thrill you, you can create a Samba share on the Linux machine where Informix is living, unload the data with Informix, then use DTS on the Windows machine using SQL-Server as the client and pull it over, locating the data files on a Samba share. This is workable but slow. Keep in mind DTS does not have the capability to use extended ( AWE ) memory on the Windows side so it will be sluggish on big data. BCP will not be much faster, SQL-Server is no where near Informix with a high-speed loader, try not to laugh too loud. :o) DTS is a 3rd-party product that does not take advantage of more than 2GB per process. You can also use SQSH on your Linux box once you install FreeTDS, which has a BCP option in it that is somewhat more compatible with Informix people, allowing delimiter options etc etc, and probably the preferred, command-line route. SQSH has been acquired by the borg at Source Forge, start at www.sqsh.org and follow the links. ODBC will always be slower as it tries to level the playing field between server and server or server and client. Library-based connections like FreeTDS are typically faster, compared with ODBC, yet proprietary. So...as has been suggested, use a gateway product that isn't interested in being portable if your company can buy it, but you might find FreeTDS a nice alternative that doesn't cost you anything but the time to make it work which is about 10-15 minutes. By the way, if you really want to go crazy, just install Sybase ASE 12.5.2 developer's edition on your Linux box and you get a few more options with the real BCP etc etc. Takes about 15 minutes and you get SQL-Server on Linux. Best of luck to you! :-) Jean Sagi wrote: > RTFMM is in order. > > But I hace something pending: That IDS could behave as an OLE DB provider. > > Anyone knows how to do it?... I believe that someone refere to this topic previously stating that some querys must be run first in order to IDS to work as a OLE DB provider. > > Another question is OLE DB better that ODBC? > > J. > > -----Original Message----- > From: "Savio Pinto (s )" <spinto@cap.org> > To: "Sebastian, Norma J." <NormaJean.Sebastian@tellabs.com>, "William Weaver" <wweaver@amicus.com> > Date: Tue, 15 Feb 2005 16:53:54 -0600 > Subject: RE: Informix and SQL Server > > the books online(help) of sqlserver has pretty good information on how to create link servers, they have examples on how to setup a link server and how to run distributed queries. i beleive you can also use linked servers in DTS packages (including oracle), > > Here are few tips, i got it from the help section of sqlserver "DTS driver support for heterogeneous data types" > > The Merant Informix OLE DB provider is supported for DTS imports from Informix, but not DTS exports to Informix. This driver also cannot be used to import meta data. > > The Intersolv Informix ODBC driver is supported, but with the following restrictions: > BLOBs cannot be exported to Informix. > > When creating new tables on Informix, the DTS Import/Export Wizard will incorrectly map the SQL Server 2000 datetime columns to the Informix 'Datetime year to fraction' data type. Manually change this to the Informix Date type. > > The DTS meta data import will not import Informix catalog or table information. > > > > -----Original Message----- > From: Sebastian, Norma J. [mailto:NormaJean.Sebastian@tellabs.com] > Sent: Tuesday, February 15, 2005 4:24 PM > To: Savio Pinto (s); William Weaver > Cc: informix-list@iiug.org; owner-informix-list@iiug.org > Subject: RE: Informix and SQL Server > > > Tell me more about this 'linked server' stuff. > We have oracle, informix, and sqlserver. I am trying to set up a > scenario to help Bill without him having to buy another tool. > > I created a Sqlserver DTS to dump some oracle data from a unix oracle db > to my PC. > > I was thinking Bill could create a job on the informix side to dump > whatever data is needed to a file, then use DTS to ftp the file > somewhere in the sqlserver domain, and have sqlserver DTS the dump file > in and manipulate it accordingly. > > But if linked servers could do the trick... Please explain a bit further > and I will test here. > Thanks, > Norma Jean > > > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Savio Pinto (s) > Sent: Tuesday, February 15, 2005 10:02 AM > To: William Weaver > Cc: informix-list@iiug.org; owner-informix-list@iiug.org > Subject: RE: Informix and SQL Server > > sqlserver has something called as "linked servers" where you can connect > to other database servers using odbc data sources, you can then run > distributed queries from sqlserver. > > > > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Martin Fuerderer > Sent: Tuesday, February 15, 2005 2:57 AM > To: William Weaver > Cc: informix-list@iiug.org; owner-informix-list@iiug.org > Subject: RE: Informix and SQL Server > > > Hi, > > there's "Informix Enterprise Gateway Manager" (as others have already > suggested), but as far as I remember, it provides to Informix (server or > applications) access to