Using dbaccess remotely on Windows Server

We are currently in the process of upgrading one of our core applications which also involves moving from DB2 to Informix 11.5. There is a requirement that we extract data from this Informix database once nightly to a staging area on a MS SQL Server which is used to produce various management reports and data warehouse extracts. I've previously used the DB2 IBM iAccess File Transfer Utility for this which extracts the contents of a table into a delimited file (and various other methods) which is then imported into our MS SQL Server via a single Integration Services package. My understanding is that the dbaccess Informix tool will allow me to unload table contents into a delimited file by issuing the appropriate SQL statements. We have the 64-bit Informix Client SDK installed on our Windows server where the MS SQL Server instance is hosted. However I am unclear on what I should be configuring on the Windows server and also the Informix server so I can use the dbaccess tool remotely to unload a series of tables into delimited files onto the Windows server. We already have linked server connections created in MS SQL Server which could be used for extracting the data but my concern is that this will not scale and SQL Server Integration will easily out perform the linked servers method. I have very limited knowledge of Informix along with its setup and configuration. Do you have any suggestions on what details I need to acquire or whether this is even possible to do? Any help is greatly appreciated.

Welcome to the Informix family and congratulations on moving your application to the best RDBMS available! You are in for a great ride! So, to make the UNIX server visible to windows apps, run the setnet32 utility to define the server's connection parameters. That's about it. Does the CSDK you have installed include dbaccess? Normally it resides on the server. I is included in the latest releases of the CSDK for Unix/Linux, but I don't know if it is included in the Windows release. At any rate, you could extract the data on the server side to a disk that is visible to your Windows SQL Server machine and import the file(s) from there. Question: Why not just execute the analytical queries directly against the Informix database itself? Finally, note that Informix v11.50 is two major releases behind the latest, so it is time to start asking your software vendor when they will support v11.70 or v12.10 since 11.50 will likely go out-of-support in a couple of years. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 1:21 PM, MARK HEPTINSTALL <djphatic@gmail.com>wrote: > We are currently in the process of upgrading one of our core applications > which also involves moving from DB2 to Informix 11.5. > There is a requirement that we extract data from this Informix database > once > nightly to a staging area on a MS SQL Server which is used to produce > various > management reports and data warehouse extracts. > > I've previously used the DB2 IBM iAccess File Transfer Utility for this > which > extracts the contents of a table into a delimited file (and various other > methods) which is then imported into our MS SQL Server via a single > Integration Services package. My understanding is that the dbaccess > Informix > tool will allow me to unload table contents into a delimited file by > issuing > the appropriate SQL statements. > > We have the 64-bit Informix Client SDK installed on our Windows server > where > the MS SQL Server instance is hosted. However I am unclear on what I > should be > configuring on the Windows server and also the Informix server so I can use > the dbaccess tool remotely to unload a series of tables into delimited > files > onto the Windows server. > > We already have linked server connections created in MS SQL Server which > could > be used for extracting the data but my concern is that this will not scale > and > SQL Server Integration will easily out perform the linked servers method. > > I have very limited knowledge of Informix along with its setup and > configuration. Do you have any suggestions on what details I need to > acquire > or whether this is even possible to do? > > Any help is greatly appreciated. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b3a82367de98804dd660fd9

Is there anything in particular in setnet32 we would need to do on the UNIX server and/or the Windows server? We do already have ODBC connections setup on the Windows server. There is a dbaccess executable in the installed folder of the CSDK on the Windows Server though when it is run the following error is displayed in the DOS prompt. ERROR: Could not initialize the security subsystem. Please ensure that this account has the necessary privileges and ensure INFORMIXSERVER value exists in the registry and environment. The supplier does not want us executing ad-hoc queries against the database. Using this method we can bring a copy of the data into a different environment which should have minimal impact on the server/database performance. We can live with a lag of one day in data for the majority of reports. I'm not sure whether our I.T. department know what version of Informix we will be using, I believe this has been left to the supplier of the application.

Hello. One option would be using a shared filesystem, and run a cron job in linux server, using external table feature. That should be great for bigger tables, but it is not so easy as a simple "unload to /dir/file select * from table", that can also be run into a cron job, on linux side. If you don´t want to share a filesystem, there could be used an ftp transfer, or some other way (it mostly depends on your operational systems and frontends, from Informix there is no much to do). Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: djphatic@gmail.com > Subject: Using dbaccess remotely on Windows Server [30325] > Date: Thu, 23 May 2013 13:21:34 -0400 > > We are currently in the process of upgrading one of our core applications > which also involves moving from DB2 to Informix 11.5. > There is a requirement that we extract data from this Informix database once > nightly to a staging area on a MS SQL Server which is used to produce various > management reports and data warehouse extracts. > > I've previously used the DB2 IBM iAccess File Transfer Utility for this which > extracts the contents of a table into a delimited file (and various other > methods) which is then imported into our MS SQL Server via a single > Integration Services package. My understanding is that the dbaccess Informix > tool will allow me to unload table contents into a delimited file by issuing > the appropriate SQL statements. > > We have the 64-bit Informix Client SDK installed on our Windows server where > the MS SQL Server instance is hosted. However I am unclear on what I should be > configuring on the Windows server and also the Informix server so I can use > the dbaccess tool remotely to unload a series of tables into delimited files > onto the Windows server. > > We already have linked server connections created in MS SQL Server which could > be used for extracting the data but my concern is that this will not scale and > SQL Server Integration will easily out perform the linked servers method. > > I have very limited knowledge of Informix along with its setup and > configuration. Do you have any suggestions on what details I need to acquire > or whether this is even possible to do? > > Any help is greatly appreciated. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

ODBC connections don't require setnet32 to be configured but native Informix (SQLI) connections do, so you have to go into setnet32 and define the server's host address, the servername, port, and protocol (onsoctcp or drsoctcp) as appropriate to the server. You have already put these values into the DSN setup for ODBC, but you still have to do it again for SQLI. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 23, 2013 at 1:46 PM, MARK HEPTINSTALL <djphatic@gmail.com>wrote: > Is there anything in particular in setnet32 we would need to do on the UNIX > server and/or the Windows server? We do already have ODBC connections > setup on > the Windows server. > > There is a dbaccess executable in the installed folder of the CSDK on the > Windows Server though when it is run the following error is displayed in > the > DOS prompt. > > ERROR: Could not initialize the security subsystem. Please ensure that this > account has the necessary privileges and ensure INFORMIXSERVER value > exists in > the registry and environment. > > The supplier does not want us executing ad-hoc queries against the > database. > Using this method we can bring a copy of the data into a different > environment > which should have minimal impact on the server/database performance. We can > live with a lag of one day in data for the majority of reports. > > I'm not sure whether our I.T. department know what version of Informix we > will > be using, I believe this has been left to the supplier of the application. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c37a842eded004dd674da4