Converting from unix timestamp format into db2 timestamp format

[Alex]

Hi all,

Got a small problem here.

I have a number of web caches here that generate loads and loads of
log files and instead of keeping them for analysis I want to write
them to a db/2 database.
The problem I've got is that the log files have a timestamp field that
is the number of seconds since 1/1/1970 which I need to convert into a
db/2 timestamp format.

I've found a UDF called unixtimestamp which accepts a big intetger and
returns a db/2 timestamp but I'm not sure how to incorporate it into a
prepare statement.

The following is an excerpt from the perl file which executes the
prepare statement.

$sth=$dbh->prepare("INSERT INTO
squid.logger(cacheip,msec,elapsed,bill,code,\
status,bytes,url,userident,host)\
VALUES(?,?,?,?,?,?,?,?,?,?)");
msec is defined as being of data type timestamp.
A perl variable called $cts is a string representation of the web
cache log field in milliseconds.

How would I modify the above prepare statement to incorporate the UDF

would it be ?
"insert into squid.logger(cacheip,unixtimestamp(bigint($cts)),. .....

TIA
Alex

[Knut Stolze]

Alex <A.******@hull.ac.uk> wrote:

Hi all,

Got a small problem here.

I have a number of web caches here that generate loads and loads of
log files and instead of keeping them for analysis I want to write
them to a db/2 database.
It's named "DB2"
The problem I've got is that the log files have a timestamp field that
is the number of seconds since 1/1/1970 which I need to convert into a
db/2 timestamp format.

I've found a UDF called unixtimestamp which accepts a big intetger and
returns a db/2 timestamp but I'm not sure how to incorporate it into a
prepare statement.
The following is an excerpt from the perl file which executes the
prepare statement.

$sth=$dbh->prepare("INSERT INTO
squid.logger(cacheip,msec,elapsed,bill,code,\
status,bytes,url,userident,host)\
VALUES(?,?,?,?,?,?,?,?,?,?)");

msec is defined as being of data type timestamp.
A perl variable called $cts is a string representation of the web
cache log field in milliseconds.

How would I modify the above prepare statement to incorporate the UDF

would it be ?
"insert into squid.logger(cacheip,unixtimestamp(bigint($cts)),. .....

No. After the name of the table you want to insert into follow the column
names. But you want to change the (data types of the) values that are to
be inserted. So you do this:

INSERT
INTO squid.logger(cacheip, msec, elapsed, bill, code,
status, bytes, url, userident, host)
VALUES (?, UnixTimestamp(CAST(? AS BIGINT)), ?, ?, ?, ?, ?, ?, ?, ?)

Note that you need the CAST to BIGINT here (instead of the function call) so
that DB2 can correctly resolve and find the function UNIXTIMESTAMP.
Without the cast, DB2 does not know anything about the data type for the
parameter marker '?' and would not know which function is to be called.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Ian]

Alex wrote:

Hi all,

Got a small problem here.

I have a number of web caches here that generate loads and loads of
log files and instead of keeping them for analysis I want to write
them to a db/2 database.
The problem I've got is that the log files have a timestamp field that
is the number of seconds since 1/1/1970 which I need to convert into a
db/2 timestamp format.

You might also consider doing this in perl, it's pretty simple and may
be a little faster (i.e. you avoid the overhead of calling the UDF for
each row).

@t = localtime($unix_ts);
$db2_ts = sprintf("%4d-%2d-%2d-%2d.%2d.%2d.000000",
$t[5]+1900, $t[4]+1, $t[3], $t[2], $t[1], $t[0]);
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----