MySQL Bugs: #46730: Tables created witn SE IBMDB2I show incorr update and create timestamps

Bug #46730	Tables created witn SE IBMDB2I show incorr update and create timestamps
Submitted:	14 Aug 2009 16:17	Modified:	21 Jul 2010 16:05
Reporter:	George Keenan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DB2SE for IBM i	Severity:	S3 (Non-critical)
Version:	5.1.37	OS:	IBM i (Release 6.1)
Assigned to:	Tim Clark	CPU Architecture:	Any
Tags:	ibmdb2i

Description:
The create_time and update_time for tables created with the IBMDB2I storage engine shoe these times as 1969-12-31 17:59:59. This is, I believe, is the start of the UNIX/AIX epoch corrected for the time zone, CDT (-6 hours).

How to repeat:
Create any table using the IBMDB2I storage engine.

Suggested fix:
Put a note in the manual that this is as expected, put the correct time stamp or replace time stamp with NULL.

Extract from information schema showing create and update time stamps

Attachment: INFORMATION_SCHEMA.html (text/html), 1012 bytes.

Further information about the MySql install:
+-------------------------+------------------------------+ 
| Variable_name           | Value                        | 
+-------------------------+------------------------------+ 
| protocol_version        | 10                           | 
| version                 | 5.1.37                       | 
| version_comment         | MySQL Community Server (GPL) | 
| version_compile_machine | power                        | 
| version_compile_os      | ibm-i5os                     | 
+-------------------------+------------------------------+ 
On the i5os:
The Cum package is 9111.
The PTF group SF99601 is at level 10.
The PTF's listed in informational APAR have bee installed.
The machine type is 9506 525.

The informational APAR in the previous comment is II14442.

Many thanks for writing a bug report.

My information is that timestamp in DB2 starts with 1970-01-01 0:00 UTC ....

But anyway, what do you mean here exactly. When did it happen?

Please see the file I attached to this bug report (INFORMATION_SCHEMA.html (text/html), 1012 bytes).  You will see that the table created with the INNODB data engine show a correct create_time and a NULL for the update_time.  
On the other hand, tables created with IBMDB2I show an obviously incorrect create_time and update_time.  The update_time does not ever change even with an insert of new rows into the table.  
By the way the minimum value for a date in DB2 is '0001-01-01'. 
Thanks for your attention and help.

Verified as described.  

mysql> select table_name,engine, create_time, Update_time from information_schema.tables where engine='ibmdb2i';
+------------+---------+---------------------+---------------------+
| table_name | engine  | create_time         | Update_time         |
+------------+---------+---------------------+---------------------+
| t1         | IBMDB2I | 1969-12-31 15:59:59 | 1969-12-31 15:59:59 | 
| tm_test    | IBMDB2I | 1969-12-31 15:59:59 | 1969-12-31 15:59:59 | 
+------------+---------+---------------------+---------------------+
2 rows in set (3.41 sec)

Table t1 was created 6 months ago and table tm_test create 8/24/09.  System i does not report the creation and update information back from DB2 so the information schema assumes 0 (unix epoch).

This bug originated in IBM i code and is fixed by applying two operating system PTFs.

For IBM i 5.4, the PTFs are:
SI35959
SI35963

For IBM i 6.1, the PTFs are:
SI35964
SI35965