Latest revision as of 09:26, 6 March 2018

IBM Informix

This wiki article says, "Informix is generally considered to be optimized for environments with very low or no database administration, including use as an embedded database. It has a long track record of supporting very high transaction rates and providing uptime characteristics needed for mission critical applications such as manufacturing lines and reservation systems. Informix has been widely deployed in the retail sector, where the low administration overhead makes it useful for in-store deployments."

I have worked with Informix in a very large reservation system, insurance and manufacturing. I find the above statement very true.

I am currently working with Informix 9.40.FC7 on HP-UX 11.11 as mostly an adminisrator not a developer. I recently added an Informix 12.10.FC7WE replicated server on RHEL 7.3. I started with MySQL 3.23 on Linux and Informix C-ISAM on DEC Ultrix (pre IBM).

• onstat commands are predefined queries that pull from the shared memory, sysmaster database, system files, etc.  They are not output in the best format for reading or joining with other data...so use the sysmaster database to create your own reports with SQL joins  (Informix Monitoring)
• Learn the sysmaster database...this database holds configurations and statistics
• ESQL/C is very fast for accessing the database. I used it to create session monitoring tool (itop) which is like *nix "top" or MySQL "mytop"
• dbacces (output is vertical if width if greater than 80 characters) and isql are horrible SQL clients...use something like open source SQuirreL for most queries. But, if you want to query table between Informix servers you have to use Informix tools like dbacces and isql.
• Every database has catalog tables that hold table, column, index information, etc. (select tabname from systables where tabid < 100;)
• Find columns with SQL...SELECT tabname, syscolumns.* FROM systables, syscolumns WHERE systables.tabid = syscolumns.tabid AND tabname = 'table'; or with ANSI join SELECT t.tabname, c.* FROM systables t JOIN syscolumns c ON t.tabid = c.tabid;
• Raw devices are faster which Informix uses very well
• Optimize your statistics plan for better performance
• HPL (High Performance Loader) is a very awkward GUI tool, but can unload and reload tables quicker using a raw data format
• Create enough space for logical logging to prevent downtime if the tape fails. The engine will block transaction if these logs can't be backed up
• Large tables can really slow down OLTP databases. So, archive tables that are OLTP.
• A "dummy" archive, ontape -s -L 0...w/TAPEDEV set to /dev/null (in onconfig), must be done after some configuration changes
• Our Informix 9.40.FC7, which is 64 bit, has a 2GiB file limitation writing to HP-UX 11.11's file system...not the actual database data files going to a tape or pipe. So 9.40 can not archive a large database to disk without going to tape or through a pipe. 12.10 does not have this issue.
• The system log only stamps the log with the date when the first record of the day is written...then only a time stamp is on the records.  In 12.10 set "MSG_DATE 1" in the onconfig file to get the date stamp on each record.
• Try and size your initial extents large enough that the engine does not have to keep creating more extents. Multiple extents fragment your table spaces.
• Informix date and time fields/functions are very different from MySQL
• I really like some of Informix's SQL extensions...like "||" for concatenation, field[n,m] for substring, field matches( regexp ), first, etc.
• Don't hit ctrl-c while starting the database (command oninit)...this will leave share memory in limbo
• Informix can do queries between 2 servers.  Use lowercase strings for DBSERVERNAME and DBSERVERALIASES values
• Do not OS "nice" dbaccess, etc. The engine handles the priority. Set MAX_PDQPRIORITY (Parallel Database Query) to process queries faster.
• Informix is closely tied to the OS. Users and passwords are created and maintained by the OS.
• Informix privileges are definitely different from MySQL. Informix has a "connect" grant that I like because it is the only privilges you need to revoke to deny a user access make other grants void! Also, Informix can list "all" grants in one statement...MySQL can not!
• Informix 12.10 has a single user mode...does MySQL? (read_only=1)
• Informix has had descending btree indexes since 9.40, but MySQL will not have them until 8.0.
• Informix "CURRENT" operator vs MySQL NOW() function.
• Informix <database>:<table>...MySQL <database>.<table>
• Informix 9.40 did not have a "truncate" command!

What are the differences between SQL statements with Informix and MySQL?  (positive/negative/neutral)

  Informix

  MySQL

  database database;

  use database;

  comments are: "{" comment(s) "}" or "--"

  coments are: "/*" comment(s) "*/" or "--"

  A table NEEDS to be included in ALL select statements

  A table DOES NOT NEED to be included in select statements

  SELECT 'select_expr' MATCHES 'regular_expr' FROM table;

  SELECT 'select_expr' REGEXP 'regular_expr';

  SELECT 'select_expr' || 'select_expr' FROM table;

  SELECT CONCAT( 'select_expr', 'select_expr' );

  SELECT FIRST i 'select_expr' FROM table;
  --OR
  SELECT 'select_expr' FROM table LIMIT i;  {Informix version 12}

  SELECT 'select_expr' LIMIT i;

  SELECT 'select_expr' FROM table WHERE 1 = 1;

  SELECT 'select_expr' WHERE 1;

  SELECT 'select_expr'[i,j] FROM table 1 = 1;

  SELECT SUBSTR( 'select_expr', i, j ) WHERE 1;

  SELECT CURRENT FROM systables WHERE tabid = 1;

  SELECT NOW( );

  SELECT * FROM database:table;

  SELECT * FROM database.table;

  SELECT database:systables.tabname, database:syscolumns.*
  FROM database:systables
  JOIN database:syscolumns
  ON database:systables.tabid = database:syscolumns.tabid
  WHERE database:systables.tabname = 'table';

  SELECT * 
  FROM information_schema.columns
  WHERE table_schema = 'database' AND table_name = 'table';
  --OR
  DESCRIBE database.table;

Compare the Informix Version 12 editions

Informix NOTs

-case sensitive

-can't have spaces in AS