Informix - Revision history

Kemiko at 16:26, 6 March 2018

2018-03-06T16:26:35Z

Kemiko at 21:39, 27 October 2017

2017-10-27T21:39:29Z

Kemiko at 18:22, 22 October 2017

2017-10-22T18:22:00Z

Kemiko at 19:58, 21 October 2017

2017-10-21T19:58:26Z

Kemiko at 18:51, 21 October 2017

2017-10-21T18:51:03Z

Kemiko at 18:39, 21 October 2017

2017-10-21T18:39:35Z

Kemiko at 18:33, 21 October 2017

2017-10-21T18:33:56Z

Kemiko at 18:10, 21 October 2017

2017-10-21T18:10:44Z

Kemiko at 16:37, 21 October 2017

2017-10-21T16:37:12Z

Kemiko at 15:45, 20 October 2017

2017-10-20T15:45:26Z

← Older revision		Revision as of 16:26, 6 March 2018
Line 150:		Line 150:

	[http://www.iiug.org/en/2017/07/29/compare-informix/ Compare the Informix Version 12 editions]		[http://www.iiug.org/en/2017/07/29/compare-informix/ Compare the Informix Version 12 editions]
		+
		+
		+	<b>Informix NOTs</b>
		+
		+	-case sensitive
		+
		+	-can't have spaces in AS

← Older revision		Revision as of 21:39, 27 October 2017
Line 35:		Line 35:
	*Informix "CURRENT" operator vs MySQL NOW() function.		*Informix "CURRENT" operator vs MySQL NOW() function.
	*Informix <database><font color="red"><b>:</b></font><table>...MySQL <database><font color="red"><b>.</b></font><table>		*Informix <database><font color="red"><b>:</b></font><table>...MySQL <database><font color="red"><b>.</b></font><table>
		+	*Informix 9.40 did not have a "truncate" command!

← Older revision		Revision as of 18:22, 22 October 2017
Line 7:		Line 7:


−	I am currently working with <font color="red"><b>Informix 9.40.FC7</b></font> on HP-UX 11.11 as mostly an adminisrator not a developer. I recently added an <font color="red"><b>Informix 12.10.FC7WE</b></font> replicated server on RHEL 7.3. I started with <font color="red"><b>MySQL 3.23</b></font> on Linux and <font color="red"><b>Informix C-ISAM</b></font> on DEC Ultrix.	+	I am currently working with <font color="red"><b>Informix 9.40.FC7</b></font> on HP-UX 11.11 as mostly an adminisrator not a developer. I recently added an <font color="red"><b>Informix 12.10.FC7WE</b></font> replicated server on RHEL 7.3. I started with <font color="red"><b>MySQL 3.23</b></font> on Linux and <font color="red"><b>Informix C-ISAM</b></font> 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]])		*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]])

@@ Line 73: / Line 73: @@
   <tr>
    <td>
-    SELECT <i><b>'select_expr'</b></i> <font color="green"><b>MATCHES</b></font> <i><b>'regular_expr'</b></i> FROM <i><b>table</b></i>
+    SELECT <i><b>'select_expr'</b></i> <font color="green"><b>MATCHES</b></font> <i><b>'regular_expr'</b></i> FROM <i><b>table</b></i>;
    </td>
    <td>
-    SELECT <i><b>'select_expr'</b></i> <font color="red"><b>REGEXP</b></font> <i><b>'regular_expr'</b></i>
+    SELECT <i><b>'select_expr'</b></i> <font color="red"><b>REGEXP</b></font> <i><b>'regular_expr'</b></i>;
    </td>
   </tr>
   <tr>
    <td>
-    SELECT <i><b>'select_expr'</b></i> <font color="green"><b>||</b></font> <i><b>'select_expr'</b></i> FROM <i><b>table</b></i>
+    SELECT <i><b>'select_expr'</b></i> <font color="green"><b>||</b></font> <i><b>'select_expr'</b></i> FROM <i><b>table</b></i>;
    </td>
    <td>
-    SELECT <font color="red">CONCAT(</font> <i><b>'select_expr'</b></i>, <i><b>'select_expr'</b></i> <font color="red">)</font>
+    SELECT <font color="red">CONCAT(</font> <i><b>'select_expr'</b></i>, <i><b>'select_expr'</b></i> <font color="red">)</font>;
    </td>
   </tr>
   <tr>
    <td>
-    SELECT <font color="red">FIRST i</font> <i><b>'select_expr'</b></i> FROM <i><b>table</b></i>
+    SELECT <font color="red">FIRST i</font> <i><b>'select_expr'</b></i> FROM <i><b>table</b></i>;
-    <font color="red"><b>OR</b></font>
+    <font color="blue"><b>--OR</b></font>
     SELECT <i><b>'select_expr'</b></i> FROM <i><b>table</b></i> <font color="green">LIMIT i</font>;  {Informix version 12}
    </td>
@@ Line 99: / Line 99: @@
   <tr>
    <td>
-    SELECT <i><b>'select_expr'</b></i> FROM <font color="red"><i><b>table</b></i> WHERE 1 = 1</font>
+    SELECT <i><b>'select_expr'</b></i> FROM <font color="red"><i><b>table</b></i> WHERE 1 = 1</font>;
    </td>
    <td>
-    SELECT <i><b>'select_expr'</b></i> <font color="green">WHERE 1</font>
+    SELECT <i><b>'select_expr'</b></i> <font color="green">WHERE 1</font>;
    </td>
   </tr>
   <tr>
    <td>
-    SELECT <i><b>'select_expr<font color="green">[i,j]</font>'</b></i> FROM <i><b>table</b></i> 1 = 1
+    SELECT <i><b>'select_expr'<font color="green">[i,j]</font></b></i> FROM <i><b>table</b></i> 1 = 1;
    </td>
    <td>
-    SELECT <font color="red">SUBSTR(</font> <i><b>'select_expr', <font color="red">i, j )</font> </b></i>WHERE 1
+    SELECT <font color="red">SUBSTR(</font> <i><b>'select_expr', <font color="red">i, j )</font> </b></i>WHERE 1;
    </td>
   </tr>
   <tr>
    <td>
-    SELECT <font color="red">CURRENT</font> FROM <i><b>systables</b></i> WHERE <i><b>tabid = 1</b></i>
+    SELECT <font color="red">CURRENT</font> FROM <i><b>systables</b></i> WHERE <i><b>tabid = 1</b></i>;
    </td>
    <td>
-    SELECT <font color="green">NOW( )</font>
+    SELECT <font color="green">NOW( )</font>;
    </td>
   </tr>
 <tr>
    <td>
-    SELECT * FROM <i><b>database</b></i><font color="orange"><b>:</b></font><i><b>table</b></i>
+    SELECT * FROM <i><b>database</b></i><font color="orange"><b>:</b></font><i><b>table</b></i>;
    </td>
    <td>
-    SELECT * FROM <i><b>database</b></i><font color="orange"><b>.</b></font><i><b>table</b></i>
+    SELECT * FROM <i><b>database</b></i><font color="orange"><b>.</b></font><i><b>table</b></i>;
    </td>
   </tr>
@@ Line 138: / Line 138: @@
    </td>
    <td>
-    SELECT * FROM information_schema.columns WHERE table_schema = '<i><b>database</b></i>' AND table_name = '<i><b>table</b></i>';
+    SELECT *
-    <font color="red"><b>OR</b></font>
+   FROM information_schema.columns
     <font color="green">DESCRIBE <i><b>database</b></i>.<i><b>table</b></i>;</font>
    </td>

@@ Line 90: / Line 90: @@
    <td>
     SELECT <font color="red">FIRST i</font> <i><b>'select_expr'</b></i> FROM <i><b>table</b></i>
    </td>
    <td>
-    SELECT <i><b>'select_expr'</b></i> <font color="green">LIMIT i</font>
+    SELECT <i><b>'select_expr'</b></i> <font color="green">LIMIT i</font>;
    </td>
   </tr>
@@ Line 138: / Line 140: @@
     SELECT * FROM information_schema.columns WHERE table_schema = '<i><b>database</b></i>' AND table_name = '<i><b>table</b></i>';
     <font color="red"><b>OR</b></font>
-    DESCRIBE <i><b>database</b></i>.<i><b>table</b></i>;
+    <font color="green">DESCRIBE <i><b>database</b></i>.<i><b>table</b></i>;</font>
    </td>
   </tr>

← Older revision		Revision as of 18:39, 21 October 2017
Line 144:		Line 144:


−	[~~https~~://www.~~ibm~~.~~com~~/~~developerworks~~/~~data~~/~~library~~/~~techarticle~~/dm-~~0801doe~~/ Compare the Informix Version 12 editions]	+	[http://www.iiug.org/en/2017/07/29/compare-informix/ Compare the Informix Version 12 editions]

@@ Line 49: / Line 49: @@
 <tr>
    <td>
-    database <database to use>;
+    <font color="orange"><b>database</b></font> database;
    </td>
    <td>
-    use <database to use>;
+    <font color="orange"><b>use</b></font> database;
    </td>
   </tr>
@@ Line 113: / Line 113: @@
   <tr>
    <td>
-    SELECT <font color="red">CURRENT</font> FROM <i><b>systables</b></i> WHERE tabid = 1
+    SELECT <font color="red">CURRENT</font> FROM <i><b>systables</b></i> WHERE <i><b>tabid = 1</b></i>
    </td>
    <td>
@@ Line 121: / Line 121: @@
 <tr>
    <td>
-    SELECT * FROM &lt;database&gt;<font color="orange"><b>:</b></font>&lt;table&gt;
+    SELECT * FROM <i><b>database</b></i><font color="orange"><b>:</b></font><i><b>table</b></i>
    </td>
    <td>
-    SELECT * FROM &lt;database&gt;<font color="orange"><b>.</b></font>&lt;table&gt;
+    SELECT * FROM <i><b>database</b></i><font color="orange"><b>.</b></font><i><b>table</b></i>
    </td>
   </tr>
   <tr>
    <td>
-    SELECT systables.tabname, syscolumns.*
+    SELECT <i><b>database</b></i>:systables.tabname, <i><b>database</b></i>:syscolumns.*
-    FROM systables
+    FROM <i><b>database</b></i>:systables
-    JOIN syscolumns
+    JOIN <i><b>database</b></i>:syscolumns
-    ON systables.tabid = syscolumns.tabid
+    ON <i><b>database</b></i>:systables.tabid = <i><b>database</b></i>:syscolumns.tabid
-    WHERE tabname = 'table';
+    WHERE <i><b>database</b></i>:systables.tabname = '<i><b>table</b></i>';
    </td>
    <td>
-    SELECT * FROM information_schema.columns WHERE table_schema = 'database' and table_name = 'table';
+    SELECT * FROM information_schema.columns WHERE table_schema = '<i><b>database</b></i>' AND table_name = '<i><b>table</b></i>';
     <font color="red"><b>OR</b></font>
-    DESCRIBE table;
+    DESCRIBE <i><b>database</b></i>.<i><b>table</b></i>;
    </td>
   </tr>

@@ Line 15: / Line 15: @@
 *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
+*Raw devices are faster which Informix uses very well
-*Optimize your statistics plan
+*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 stop running if these logs can't be backed up
+*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
+*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 through a pipe.  12.10 does not have this issue.
+*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.&nbsp;&nbsp;In 12.10 set "MSG_DATE 1" in the onconfig file.
+*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.&nbsp;&nbsp;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 table spaces do not have a lot of additional extents (a physical unit of storage)
+*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 ), etc.
+*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.&nbsp;&nbsp;Use lowercase strings to configure DBSERVERNAME and DBSERVERALIASES
+*Informix can do queries between 2 servers.&nbsp;&nbsp;Use lowercase strings for DBSERVERNAME and DBSERVERALIASES values
-*Do not "nice" dbaccess, etc.  The engine handles the priority.  Set MAX_PDQPRIORITY (Parallel Database Query) to process queries faster.
+*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!  Also, Informix can list "all" grants in one statement...MySQL can not!
+*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!
 *<font color="red">Informix 12.10 has a single user mode...does MySQL? (read_only=1)</font>
 *Informix has had descending btree indexes since 9.40, but MySQL will not have them until 8.0.
@@ Line 121: / Line 121: @@
 <tr>
    <td>
-    SELECT <database>:systables.tabname, <database>:syscolumns.*
+    SELECT * FROM &lt;database&gt;<font color="orange"><b>:</b></font>&lt;table&gt;
    </td>
    <td>
-    SELECT * FROM information_schema.columns WHERE table_schema = '<database>'; or DESCRIBE table
+   SELECT * FROM &lt;database&gt;<font color="orange"><b>.</b></font>&lt;table&gt;
    </td>
   </tr>

@@ Line 4: / Line 4: @@
-I have worked with Informix in both manufacturing and reservations and find this to be true.
+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 <font color="red"><b>Informix 9.40.FC7 on HP-UX 11.11</b></font> as an adminisrator not a developer.  We finally installed a new version of Informix...<font color="red"><b>12.10.FC7WE on RHEL 7.3</b></font>.  However, I started with <font color="red"><b>MySQL and Informix C-ISAM on Linux</b></font>.
+I am currently working with <font color="red"><b>Informix 9.40.FC7</b></font> on HP-UX 11.11 as mostly an adminisrator not a developer.  I recently added an <font color="red"><b>Informix 12.10.FC7WE</b></font> replicated server on RHEL 7.3.  I started with <font color="red"><b>MySQL 3.23</b></font> on Linux and <font color="red"><b>Informix C-ISAM</b></font> on DEC Ultrix.
 *onstat commands are predefined queries that pull from the shared memory, sysmaster database, system files, etc.&nbsp;&nbsp;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&nbsp;&nbsp;([[Informix Monitoring]])
 *Learn the sysmaster database...this database holds configurations and statistics
-*ESQL/C is very fast for accessing the database (itop)
+*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 [http://squirrel-sql.sourceforge.net/ SQuirreL]
+*dbacces (output is vertical if width if greater than 80 characters) and isql are horrible SQL clients...use something like open source [http://squirrel-sql.sourceforge.net/ 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';
+*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
 *Optimize your statistics plan