I linked a IBM Informix Dynamic Server Version 9.40.UC4 on SuSE SLES 8 server – with Microsoft SQL Server Standard (64-bit) on Windows Server 2008 R2 Standard:

1.  Get the latest IBM Informix Client SDK  from here (I used clientsdk.3.70.FC7DE.WIN) and install on your Microsoft Server.

If you are installing the 32 bit version onto a 64 bit machine, you might need to change the SysWOW64 path:   open a comand prompt from the folder where the Informix SDK install is and enter:

set PATH=C:\WINDOWS\SysWOW64\;%PATH%

2.  Add your host in C:/Windows/System32/drivers/etc/hosts (at the very bottom), e.g.  <your server IP>  <your server name>

3.  Similarly, add “sqlexec  1526/tcp#” at the very bottom of C:/Windows/System32/drivers/etc/services.  Note the # at the end.

Now simply call your remote db – from the MS SQL side – as follows:

SELECT * FROM [LinkedServer].[RemoteDatabase].[User/Owner].[Table]

I’ve come to know these table names by heart (by using them over the last 13 years) –  and (still) frequently use them in sentences..

This is more for me to remember.

Informix > Sage Line 500 name:

bmhstm > BOM History
bmwodm > BOM Detail
bmwohm > BOM Header
cbbankm > Cash Bank file
cbbatchm > Cash Batches file
cbtranm > Cash Transactions file
ceaddrm > Customer (shipper_code1) Addresses
cecaldrm > (Financial) Calendar
cenatypem > Narrative Types
cevatregm > VAT Register
nlaccm > Nominal Account File
nlmastm > Nominal Master File
nltranm > Nominal (GL) Transactions
opaudm > Sales Order Audit File
opcosm > Cost Of Sales Matrix
opdetm > Sales Order Detail
opheadm > Sales Order Header
oplastprm > Sales Order Last Price
oplistm > (Sales Order) Price Lists
opsahistm > Sales History Transactions
opservm > Sales Order Services
plitemm > Purchase Ledger Items
plpaym > AP Payments
plsuppm > AP Suppliers (Creditors)
podetm > Purchase Order Detail
poheadm > Purchase Order Header
porecp2m > Purchase Order Receipts
porecpm > Purchase Order Receipts
slbankm > Customers (Debtors) Bank
slcashm > Customers (Debtors) Cash
slcustm > Customers (Debtors) Master File
slitemm > Customers (Debtors) Transactions
stallocm > Stock Allocation (to Sales Orders)
stkhstm > Stock Transactions
stkwhm > Stock Warehouse Master File
stockm > Stock Master File
stquem > Stock Batches
sysdirm > System Keys

Let me know if you need to write a report on these.

Make a note of the output of onstat -d to monitor the effect of your actions.

Ensure exclusive access – preferably with Informix transaction logging off.

Informix transaction logging off:  ondblog nolog <database name>            then      onbar -b -F

As always, make sure you’ve got a decent backup before you start.

Log into Sage – into the Company you need to reduce the space of.  Exit to shell.  You should now be in the company directory by default (check System Manager > System Administration > Company Management > Working Directories).

Test a small table first, e.g.:

unload st_warehouse > stkwhm.unl

Now make sure the unload file (in this case stkwhm.unl) contains the field names at the top and data below it.  Then do

load -cD -x 10000 st_warehouse < stkwhm.unl

The ‘10000’ can be adjusted according the the number of lines loaded at a time – increase for larger tables.

Be sure to update statistics once done!  E.g:

update statistics for table stkwhm;

And lastly, remember to switch Informix transaction logging back on.

Informix transaction logging on:  ondblog buf <database name>            then      onbar -b -F

For years I’ve had trouble finding the correct Informix table (name) used by Sage Line 500’s Data Dictionary – until I discovered this nifty trick:

Note:  Requires one to have Demo/Test company installed – accessing a database differently named to the live/production db.  I work in a SuSE Linux Enterpise Server (SLES) environment.

1.  Access the desired Sage screen/window.  (e.g.   if you’d like to know which table stores Sales Order Processing order detail – then go into any example order’s detail screen/window). 

2.  Open another session, log in as Infomix and run ‘onstat  -g sql’.  This will show all current database(s) threads.  Most of these will (might) relate to the production db’s processes – so look for the process with the Demo/Test db’s name in it.  Make a note of the process number (integer number on the left).

3.  As Informix, run ‘onstat -g sql <process number>’  (e.g.  ‘onstat -g sql 16342’) and look for the table name in the output (usually after ‘from’). 

Let me know if problems!

It was a struggle. Here’s the shortcut:

1. Download the IBM Informix Client SDK3.50 – I used version 3.50.FC8 – for operating system Windows (64bit).
I found that the Infomix Connect 2.90 (choose the 64 bit option) also works – but you’ll have to run it as Administrator on Windows 7.

2. Edit c:/Windows/System32/drivers/etc/services and add

sqlexec 1526/tcp #

on the last line. Note the hash(#) at the end of the line.

3. Edit c:/Windows/System32/drivers/etc/hosts and add

(your Informix server IP) (your Informix server name)  e.g

12.345.67.89      informixserver.com     #

on the last line.  Again, note the hash(#) at the end of the line.

NB:  Make sure you define your host (e.g informixserver.com) in SetNet32!  (part of IBM SDK install)

Note:  If Windows 7 complain that you you’re not allowed to save or make changes to the file ‘services’ or ‘hosts’ – try opening Notepad as Administartor (Swift + Right-click) – or even copy the contents of the (say) hosts, rename the old file (hosts_old) and past into a new file (hosts).

4. Create a file (say) your_data_source.dsn in C:/Users/YourMachine/AppData/Roaming/Microsoft/Queries with contents

[ODBC]
DRIVER={IBM INFORMIX ODBC DRIVER}
DATABASE=your_informix_database
HOST=informixserver.com
SRVR=your_server
SERV=sqlexec
PRO=olsoctcp
CLOC=en.US.CP1252
DLOC=en_US.819

Note the ‘informixserver.com’ host as specified in point 3 – and Setnet32 – above!

OR

Go Start > Control Panel > Adminstrative Tools > Data Sources (ODBC)  to add a User DSN or System DSN  (both work).  Or access via C:\Windows\SysWOW64\odbcad32.exe

5. Now the datasource should appear in MSQuery!