PostgreSQL PL/pgSQL to Informix SPL migration

PL/pgSQL coding can be quite a different experience coding with Informix SPL. However almost all the procedural functionality is converted in SPL.

1. Variables: In SPL use DEFINE instead of plpgsqls' DECLARE
Most in-build PL/pgSQL variables are supported.
For %ROWTYPE however one has to create a row type datatype
In pgplsql for: DECLARE var tbl.colname%TYPE
in SPL use: DEFINE var like tbl.colname
Assigning variables SPL requires the LET keyword
LET var1 = var2+5 ;

2. User defined functions returning values
SPL uses RETURNING keyword whereas in plpgsql use RETURN

3. Date/Time
I couldn't find a with or without timezone clause in SPL. However if TZ environment variable is set,
then one can call select DBINFO('get_tz') from tablename
plpgsql TIMESTAMP is equivalent to SPL datetime
The data types Interval, Date and Time are the same in both language
To get epoch time in SPL:
select DBINFO('utc_to_datetime',colname) from tablename;
select DBINFO('utc_current') returns the current date as epoch
To convert datetime to epoch is more interesting:
Some developers have blogged to_epoch user defined functions that manually converted datetime to an integer.


4. EXCEPTION Handling
SPL provides the SQLCODE=100 for a successful query but with no rows returned.
This is similar to plpgsqls' IF NOT FOUND clause
I found I had to use a cursor to retrieve the SQLCODE
create function fn()
returning integer;
DEFINE qry varchar(100) ;
LET qry= "select col from table where col='somevalue'" ;
PREPARE stmt from qry ;
DECLARE cursor1 cursor for stmt ;
OPEN cursor1 ;
FETCH cursor1 into found ;
if (SQLCODE = 100) then
raise exception -746,0,"no matching row found";
end if
return SQLCODE ;
end function ;
The -746 is a custom code provided by Informix allowing a developer to return their own created exception

If someone doesn't want to use cursors, another option is after running the select statement, use:
select DBINFO(sqlca.sqlerrd2') into rows_affected from systables where tabid=1 ;
or
LET retcode = DBINFO('sqlca.sqlerrd2') ;
If no rows were returned this will return 0 into rows_affected

6. SEQUENCES
ANSI SQL does not support sequences as DEFAULT col values in CREATE TABLE DDL, although PL/pgSQL allows it.
Once you create a sequence, the next value can be retrieved into an SPL variable and then used in an insert stmt.

7. SQL/RETURNING statement
PL/pgSQL provides a RETURNING clause after an SQL select, insert or update stmt (not ANSI compliant).
In SPL I wrote an SQL select stmt after insert/update SQL to get the value.

8. Testing an SPL routine
One can use dbaccess to create a routine and save it
From query-language->New option then
execute function function_name() ;

The returned values/error messages are displayed

Informix Cheat Sheet

Shared Memory stats
$ipcs -m
$onstat -g seg

To quickly get Informix version number:
$onstat -s

Use oninit with -s option before using the onmode -m
This will allow you to check log files and other config params before users can logon onstat -lRdt

Use onmode with -yuk option. This terminates user sessions, rolls back transaction and then goes offline allowing for a quicker startup
$onmode -yuk

The default page size with onspaces is 2K. You can use onmonitor or -k option to change the page size.
$onstat -bB
$oncheck -pr\grep -i 'page size'
$getconf PAGESIZE

# view the number of daemon servers and Flush page processes
$onstat -u
$ps -efgrep oninit

rollbacks, commits, cache, reads/writes etc.
$oncheck -cc
$oncheck -pc
$oncheck -pr

After going fully online, you can run the following to check VPs, Users and Sessions:
$onstat -g glo
$onstat -ug ses session_id

Importing/Exporting database:
Note qhen using dbimport Informix Engine will drop the database and create it new with the output of dbexport.

Other useful dbaccess SQL commands
info tables
info columns for table
info status for table
info access for table

PostgreSQL Cheat Sheet

Starting/Stopping Server:
$set PGDATA=/postgres/data
$pg_ctl -w start/restart -D /var/lib/pgsql/data -l log -o --port=5432
$pg_ctl -D /var/lib/pgsql/data stop -m immediate/smart/fast
$pg_ctl -s
$pg_ctl reload

Server Administration (psql)
Some useful commands :
=>select VERSION()
=>select CURRENT_DATABASE()
=>select CURRENT_SCHEMA()
=>select USER
=>select INET_SERVER_PORT()
=>select INET_SERVER_ADDR()
=>vacum analyze verbose tablename
=>explain sql

#TCP/IP settings in var/lib/pgsql/data/pg_hba.conf
host all 127.0.0.1 255.255.255.0 password
To allow remote connections: modify /var/lib/pgsql/data/postgresql.conf
#change listen_addresses = 'localhost'
# to listen_addresses = '*'

Forgot root/postgres password?
#Modify pg_hba.conf to all trust connections
$pg_ctl reload
$psql -U postgres
=>alter user postgres with password 'newpassword' ;
=>\q
#Set pg_hba.conf back to orginal settings
$pg_ctl reload

$pg_dump -U user db > tmp/db_dump
Create a database:
$createdb test
or
$psql -U postgres -c "CREATE DATABASE test" -d template1
$psql -U postgres -d test
=>\r ; clear buffer
=>\e ; edit buffer
=>\w ; write buffer
=> \! ; run sub-shell
=>\g or ; ; rerun contents of buffer
=>copy table to file
=>copy table from file
=>set ; to view DBNAME, user, host and port
=>\connect or \c dbname ; no arguments gives current database name
=>\i file.sql ; execute script
=>\e ; to change encoding
=>\o filename ;
send query results to a file
=>\o
=>\d table or \d for all tables
=>\dt or \di or \dv or \dv or \dC
=>\dg ; list of groups
=>\dn ; schemas
=>\dn+ schema
=>\c db username

To view a stored procedure in psql:
=>select prosrc from pg_proc where procname='procedure_name' ;
Some other useful system tables
pg_trigger (triggers)
pg_class (tables)
=>\d tablename ;describes the table
=>select relname from pg_class ; lists all the user tables

To view the a schema with all its tables, try the following:
\o dbschema.txt
=>echo \d `select relname from pg_class`
\o

error messages
undefined symbol: pg_valid_server_encoding_id is often the result of incompatible libraries (usally when upgrading PostgreSQL) usually the libpq.so
$ldd -d path to psql
$export LD_LIBRARY_PATH=path to correct libpq.so
$sudo ldconfig

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