Installing Informix 11.5 on Ubuntu

I recommend referencing the IBM Informix website which has a detailed description of the requirements and install process.



Get the pre-requisitie libraries on ubuntu. On a 32-bit server:
$sudo apt-get install libaio1
$sudo apt-get install bc
$sudo apt-get install pdksh



Create group and username
$more /etc/group grep 200
$more /etc/passwd grep 200
$sudo groupadd informix –g 200
$sudo useradd informix –m informix –d /home/informix –g informix –u 200
$sudo passwd informix
$su Informix

#give sudo access to Informix
$sudo adduser Informix admin
$sudo groups Informix
$ cd /home/Informix
$mkdir Informix_install
$cd informix_install



#download the Informix files
#and chown and chgrp to Informix
#untar the file
$sudo ./ids_install
default dir is /opt/IBM/informix
$sudo cp /opt/IBM/Informix/etc/sqlhosts /opt/IBM/Informix/etc/sqlhosts.server
edit sqlhosts.server to create
If you installed demo then use profile settings to set:
INFORMIXDIR=/opt/IBM/informix
SQLHOSTS=sqlhosts.server
INFORMIXSERVER=server


Informix provides a tool 'onmonitor' to manage dbspace

To create a 1GB dbspace 1,024,000KB
$onmonitor

$dbaccess
Use dbaccess to create a database


To access the database other than a username 'informix'
Add the username to the Informix group
Be careful to make sure the other groups the username belongs to are retained
Add the Informix bin (/opt/IBM/informix/bin) to the $PATH

Setup a profilesettings file that gets automatically executed or add to the startup shell script file such as .bashrc
INFORMIXDIR
SQLHOSTS
INFORMIXSERVER
PATH=$PATH:/opt/IBM/informix/bin


$dbaccess
Use the tool to create the database

SQL-92 compatible scripts

For installing and running SQL-92 scripts that can run on both Informix and PostgreSQL:

Two sources for SQL-92 syntax are:
HOWTO - SQL92 website
http://www.ocelot.ca/commands.htm
I found an excellent reference from Apress for PostgreSQL:
Beginning PHP and PostgreSQL 8 by Gilmore and Treat


IF EXISTS in statement
remove any "if exists" statements from the sql 'drop' command
ex. drop view if exists view1 should be changed to:
drop view view1
drop index if exists indx1 should be changed to:
drop index indx1
drop sequence if exists seq1 should be changed to:
drop sequence seq1
drop table if exists tab1 should be changed to:
drop table indx1


PostgreSQL scripts allow:
alter table.. owner ..

The owner clause in 'alter table' is not SQL-92 compatible.

When using and SQL DDL command such as create table..
note that setting a DEFAULT value that references a sequence is not allowed (see Sequences below)


BOOLEAN DATA
boolean data should be 't' for true and 'f' for false

SEQUENCES
Sequences are not defined in SQL-92 and are database specific.

IN PostgreSQL, to create a sequence:
create sequence seq1
start with 1
increment by 1
NO MAXVALUE
NO MINVALUE
cache cache_size

is rewritten for Informix as:
create sequence 'seq1'
start with value
increment by 1
nominvalue
nomaxvalue
cache cache_size

Informix does not allow use of NEXTVAL or CURVAL except in insert, select, delete and update statements;
NEXTVAL cannot be used in DDL.
In PostgreSQL, the setval function is used for setting a sequence value :
select pg_data.setval('seq1', value, true) = select pg_data.setval('seq',value)
sets the sequence to the next value when using NEXTVAL
This is PostgreSQL specific code.

VIEWS
PostgreSQL 8.3 easily integrates 'order by' into views.
In PostgreSQL
create view view1 as
select ..
from..
order by..

For Informix rewrite
create view view1 as
select (..)
from
(select ..
order by.. )

According to sql.org website 'or replace' option with 'create view' command is not sql-92 and Informix does not accept the clause. So instead use:
drop view view ;
CREATE VIEW view [ column [, ...] ] AS SELECT expression [ AS colname ] [, ...]
FROM table [ WHERE condition ]
[ WITH [ CASCADE LOCAL ] CHECK OPTION ]


CONVERTING EPOCH TIME:
To convert a unix timestamp to informix:
select DBINFO('utc_to_datetime', unix_epoch_time)
from..

CONSTRAINTS
The syntax for adding contraints is:
ALTER TABLE tbl ADD CONSTRAINT
PRIMARY/FOREIGN KEY (col1,col2..)
CONSTRAINT constraint_name ;

PostgreSQL OID::bpchar ::regclass
This specification is not required for informix and not SQL-92

Connecting Cognos 8.3 to Informix 11.5 on Linux via ODBC

Download the Informix ClientSDK from IBM's website

On the server side (ubuntu) make sure that a service is created with the IP/port/protocol in the file is specified under SQLHOSTS environment variable
Add the service to /etc/services specifying port/protocol
Start informix
$oninit
from the logon specified in the ODBC connection. No startup error messages should be displayed
Adding the ubuntu logon to the informix group (created at install time) solves quite a few problems.

Installing on Windows Installation of the CSDK is straightforward :
Extract the contents of the downloaded zip file
The installer creates an entry IBM Informix Client-SDK 3.50 under 'All Programs'
From there, it is easy to access Setnet32 to create the ODBC connection

Run setnet32:

Select the Host Information tab and under
Server Information enter the connection parameters :
Database : database
Host name: Host IP
Server: INFORMIXSERVER that is set on the linux server
Protocolname: onsoctcp (or whatever protocol specified for the service in sqlhosts)
Service: port (service specified in /etc/services file or the port number)

Under the Host Information tab:
Current Host: Host IP
Username: ubuntu logon
Password Option: Specify if a password is required
Password: Enter the ubuntu userid password

On the server side:
Update the sqlhosts file with the service name
Edit /etc/services to include the service IP/protocol and port numbers

Test the connection and then Save the connection under a name such as informix115
You can now use the saved connection wherever an ODBC connection is allowed