Difference between revisions of "Informix"
From kemiko
| Line 23: | Line 23: | ||
*Informix date and time fields are very different from MySQL | *Informix date and time fields are very different from MySQL | ||
| − | I have the most experience with MySQL....so, what are the differences between Informix and MySQL? | + | |
| + | I have the most experience with MySQL....so, what are the differences between Informix and MySQL? (<font color="green">positive</font>/<font color="red">negative</font>) | ||
<table> | <table> | ||
<tr> | <tr> | ||
| Line 31: | Line 32: | ||
<td> | <td> | ||
MySQL | MySQL | ||
| + | </td> | ||
| + | </tr> | ||
| + | <tr> | ||
| + | <td> | ||
| + | select ''variable/literal'' <font color="green"><b>||</b></font> ''variable/literal'' from ''table'' | ||
| + | </td> | ||
| + | <td> | ||
| + | select <font color="red">concat(</font> ''variable/literal'', ''variable/literal'' <font color="red">)</font> | ||
</td> | </td> | ||
</tr> | </tr> | ||
</table> | </table> | ||
Revision as of 08:23, 27 January 2017
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.
I am currently working with Informix 9.40.FC7 as an adminisrator not a developer
- 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
- Learn the sysmaster database...this database holds statistics and configurations
- ESQL/C is very fast for accessing the database
- dbacces (output is vertical if width if greater than 80 characters) and isql are horrible interfaces
- Every database has system tables that hold table, column, index information, etc. (select tabname from systables where tabid <= 100)
- 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
- Inserts into a 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
- The system log only stamps the log with date when the first record of the day is written...then most records only have a time stamp
- Try and size your table large enough that you don't have a lot of additional extents
- I really like some of Informix's SQL extensions...like "||" for concatenation, field[n,m] for substring, field matches( regex ), etc.
- Informix date and time fields are very different from MySQL
I have the most experience with MySQL....so, what are the differences between Informix and MySQL? (positive/negative)
Informix |
MySQL |
select variable/literal || variable/literal from table |
select concat( variable/literal, variable/literal ) |