Revision as of 21:14, 22 June 2017

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 both manufacturing and reservations and find this to be true.

I am currently working with Informix 9.40.FC7 as an adminisrator not a developer. We finally installed 12.10.FC7WE. However, I grew up with MySQL.

• 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
• dbacces (output is vertical if width if greater than 80 characters) and isql are horrible SQL clients...use something like open source SQuirreL
• 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';
• Raw devices are faster
• Optimize your statistics plan
• HPL (High Performance Loader) is a very awkward 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 stop running if these logs can't be backed up
• Large tables can really slow down OLTP databases
• A "dummy" archive, ontape -s -L 0...w/TAPEDEV set to /dev/null, 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't archive a large database to disk without going through a pipe. Our 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
• Try and size your initial extents large enough that table spaces don't have a lot of additional extents
• 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 ), etc.
• Don't hit ctrl-c while starting the database (oninit)...this will leave share memory in limbo
• With Informix it is really easy to setup doing queries with two separate servers!

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

  Informix

  MySQL

  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

  SELECT 'select_expr' LIMIT i

  SELECT 'select_expr' FROM table 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 table WHERE 1 = 1

  SELECT NOW( )

  SELECT <database>:systables.tabname, <database>:syscolumns.*
  FROM <database>:systables, <database>:syscolumns
  WHERE <database>:systables.tabid = <database>:syscolumns.tabid and <database>:systables.tabid > 100

  SELECT * FROM information_schema.columns WHERE table_schema = '<database>'; or DESCRIBE table

Compare the Informix Version 12 editions