Here’s one way to obtain the Unix time value (the number of seconds since midnight on January 1, 1970) from a DB2 timestamp:
values( (days(current_timestamp-current_timezone) - days('1970-01-01') )*86400 + midnight_seconds(current_timestamp - current_timezone) )
This can be used in a query directly or wrapped into a simple SQL user-defined function.
NOTE: this does not work correctly if your DB2 9.7 database is in the Oracle compatibility mode. Date arithmetic are implemented differently to be compatible with Oracle. I’m going to post a new solution for the Oracle compatibility mode shortly.
Here’s the version for an Oracle-compatible DB2 database: