Informix Link 1

[Phil99]

Hi,

I'm attempting to configure a ODBC connection for connect to an Informix database, but i'm getting a driver error.

Do I need to install the Informix client software on the sql server to make this work?

Or is there an easier way to do this with SQL Server 2000?

Thanks

Phil

 

[thendrickson]

I have just went through this if you are trying to create a linked server through sql Server 2000.

I did use OLEDB not ODBC though. I did experiment with ODBC but was not able to make ODBC work reliably

This is how I made OLEDB work:


Procedure for creating an OlE DB linked server to Informix.

Prior to creating the connection, be certain that Informix Client SDK 2.90 or newer is installed on the server.
Be certain to uninstall any earlier versions first, then reboot, install the newest SDK and reboot again.

IBM provides 2 scripts that need to be run against the Informix database:
execute "doledbp.sql" first then execute
"coledbp.sql"
from the sysmaster database as user informix .

These scripts are included with the installation. The current path on the dev server is:
C:\Program Files\IBM\Informix\Client-SDK\etc

The scripts are backward compatable, but must be run for any upgrades.

Should the SDK be updated in the future, this procedure must be followed as well

1. Use Enterprise Manager.

It is possible to use sp_addlinked server, but I had difficulties with the Informix connection string
using sp_addlinkedserver and have not fully researched the proper connection string for Informix.

2. Select the SQL Server instance you wish to link the Linker server to.


3. Select "Security" from the tree then right click "Linked Servers"

4.Click New Linked server
a. You should be on the general tab of the properties form
1.Name the Linked Server. For this document, I am naming it IFXexample.
2.Select "Other Data Source"
3.Select IBM Informix OLE DB Provider from the drop down box.
4. Click Provider options.
Note: This button will be grayed out except while creating a new linked server
The options selected apply to all linked servers that use the Informix OLE DB provider.
Should you need to change this options later you must create a new linked server, change these options
click OK and return to the Properties form, then cancel the new server. (Do not blame me!!!!!)
a. Select the proper options:
I have Dynamic Parameters and Allow In Process checked.
DO NOT CHECK "Nested Queries". Informix does not support nested queries in the FROM clause and SQL Server will send a nested query to Informix when there are 2 or more joins to a table residing on the linked server This was a very difficult bug to find.
b. Select OK and return to the property form.
5.Property name = ifcoledb
6. Data Source = dbname@tservernameThis
7. Provider String = decasr8=R8;DB_LOCALE=en_US.819
The decasr8=R8 is required. I do not believe the other variable actually effects anything since I believe it is the Informix default, but I do not want to risk breaking anything I know works right now.
a. Go to Security tab
1. Set Security options
2 Our current security requires selecting "Be made using this security context" and entering the Informix db login and password.


b. Server options tab
1. Check "Collation Compatable" or performance will be horrible.
2 Check Data Access (one of the defaults) and Use Remote Collation (another default)
c. Click OK and close properties
5. Refresh and you should be good to go.


Programming Notes:

1. Access the linked server only using stored procedures. Otherwise, the performance will be very poor.
2. Use the 4 part naming convention. ie: IFXexample.dbname.schema.tablename.
3. database schema is case sensitive. This seems to be something to do with the way SQL sennds the schema to Informix contained within
double quotes. It might be due to the Informix Client SDK as well, but regardless, I can find no way to remove the schema case requirement.
NOTE: Data is not case sensitive, only the schema
4. Create Stored procedures from Query Analyzer only.
Use this example:
set Ansi_Nulls On
set Ansi_Warnings ON
GO

CREATE PROCEDURE [dbo].[aaa_BBBBB]

a. Once created you can still edit the sp as normal, but you must set Ansi Nulls and Ansi Warnings on explicity for each sp

There is still a bug from IBM for certain datatypes.
SQL will not accept a stored procedure for example where the Informix DB has an Informix time datatype.
Work Around
Create a view in Informix either leaving out that column or casting it to a different data type.
For a time data type in Informix you must use the EXTEND Keyword to change a time to a datetime.

Example: EXTEND (x0.timefld ,year to minute) where x0 is the table alias and timefld is a time value.

Do not forget to parse out the time in the SQL stored procedure since the extend seems to give the current date!

I know of no other datatype bugs and of course, if the field is not needed, do not include it in the view

 

[Phil99]

Thanks for you reply i found this very useful. I now have a connection via DTS but also need to create a link so i can update the Informix server via SQL Server.

So far i haven't been able to get the connection to work.

I'm not sure in your example what you have used for the parameter values.

Property name = ifcoledb
Data Source = dbname@tservernameThis
Provider String = decasr8=R8;DB_LOCALE=en_US.819

My enviroment is below.
Server: aesoc
Hostname: 158.122.12.82
Servicename: 22001
Protocolname: onsoctcp
Stores Database: configdata
table: configdatatest

I guess the Property Name = Database (configdata)
but i'm not sure what you have used as the Data Source.

Also in the programming notes you state, use the 4 part naming convention.
IFXexample.dbname.schema.tablename.
I assume this would be (Link server Name.aesoc.configdata.configdatatest.

Please can you confirm the above is correct.

Many Thanks

Phil

 

[thendrickson]

The property name is ifcoledb. That is the name of the Informix Driver

datasource is your database name in the format dbname@servername

in your case it looks like it should be configdata@aesoc

You are correct on the 4 part name.

Also I may have made an error re the data not being case sensitive. I am not certain yet, but I think the data is case sensitive as well.

 

[Phil99]

Thanks i've now set up the connection now and from the link server in Enterprise manager i can see the tables and views.

Using the following in a query it returns an error as below:

Linked Server = Ferret
DB Name= aesoc
Schema = Configdata
tablename = Configdatatest

Query
select count(*)
from FERRET.aesoc.configdata.configdatatest

Error Message
Server: Msg 7311, Level 16, State 2, Line 1
Could not obtain the schema rowset for OLE DB provider 'Ifxoledbc'. The provider supports the interface, but returns a failure code when it is used.
OLE DB error trace [OLE/DB Provider 'Ifxoledbc' IDBSchemaRowset::GetRowset returned 0x80040e37: ].

Is this something you have seen before?

Cheers

Phil

 

[thendrickson]

The problem is most likely with the 4 part naming.

See my first reply to your post

You must update the SDK on the server where SQL Server is installed as well as running the scripts against the informix server.

Be certain that the older versions are completely uninstalled.

I had a terrible time with this, but found the problem was a registry entry for the default path was still looking at the old driver.

Follow the steps I laid out exactly and things should work.

I have now set this up twice on test beds and both times the procedure I laid out has worked.

Keep me posted please and good luck

 

[nikolap]

Ok, I kind of have a nub question, how do u execute the script on AIX?
I have connected to the informix server with dbaccess but there is no sysmaster db, only the db that we use.
I get this when i try to execute the script as user informix.

% dbaccess sysmaster doledbp.sql

329: Database not found or no system permission.

2: No such file or directory

 

[thendrickson]

You do not have permissions I would guess.

My experience has been with SQL server. This is my first attempt to deal with Informix.

I had our DBA run the scripts per the instructions.

 

[nikolap]

Same here,
I was trying to link the servers so I can access the server directly.
I run the command as root and as Informix as well, it did not work, i connected to the Informix server via dbaccess utility and no symaster db there... beats me,
the db is made by third party and it might have a custom installation that will only support their database.
I can use odbc to access the db, but I wanted to do this through our SQL server as it would have been more flexible like that.
Thanks
Nik

 

[thendrickson]

That was why we went with the linked server.

My app works great using the linked server

I do see one possible problem or typo.

it is sysmaster not symaster.

if I understand Informix correctly (and I do not understand much), you must have the proper permissions to do anything in sysmaster. I believe you must have 'dba' permissions

In my mind, I equate that to 'sa' permissions for SQL Server.

I also suspect (do not know) that you must do this from the console, not through a terminal emulation program.

Anybody who can prove me right or wrong on this, please jump in!!


Also what version of Informix are you using? I have 7.3.
I was told the procedure was exactly the same

 

[thendrickson]

Oh there is a version of Informix other than "dynamic server" which appears to fill a similar niche as does MSDE.

No idea what would happen if you are using that version

 

[nikolap]

The Informix version is SE 7.1 and it is running on AIX 4.1

 

[thendrickson]

Is it Dynamic Server?

this link might prove useful

http://www-306.ibm.com/software/data/informix/pubs/library/

 

[Phil99]

Hi

I finally got this connection to work. The issue was with the 4 part name.

I was initially attempting to use
select *
from FERRET.aesoc.configdata.configdatatest

(Server: aesoc)

but I got the connection to work with

select *
from FERRET.configdata.informix.configdatatest

(Linked Server: FERRET
Database: configdata
Table Owner: informix
Table: configdatatest)

Many thanks for your advice.

Phil