DB2 equivalent INFORMIX.EXTEND

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rick - 20 Mar 2007 04:09 GMT

hi

Is there a DB2 equivalent of Informix.Extend function?

or please help me the best way to create one

Thanks
rick

Reply to this Message

Ian - 20 Mar 2007 07:16 GMT

> hi
>
> Is there a DB2 equivalent of Informix.Extend function?

Although there may be a few Informix people here, it would
be helpful for the rest of us if you tell what this function
does.

Or at least provide a pointer to the reference material
for the function.

Reply to this Message

rick - 20 Mar 2007 14:05 GMT

Hi IAN

The EXTEND function adjusts the precision of a DATETIME or DATE value.
The expression cannot be a quoted string representation of a DATE
value.

If you do not specify first and last qualifiers, the default
qualifiers are YEAR TO FRACTION(3).

If the expression contains fields that are not specified by the
qualifiers, the unwanted fields are discarded.

If the first qualifier specifies a larger (that is, more significant)
field than what exists in the expression, the new fields are filled in
with values returned by the CURRENT function. If the last qualifier
specifies a smaller field (that is, less significant) than what exists
in the expression, the new fields are filled in with constant values.
A missing MONTH or DAY field is filled in with 1, and the missing HOUR
to FRACTION fields are filled in with 0

and below is the code I have.

CREATE FUNCTION "INFX "."EXTEND" ( DATE TIMESTAMP, UPPER1
INTEGER, LOWER1 INTEGER, UPPER2 INTEGER,LOWER2 INTEGER)

        RETURNS TIMESTAMP
        SPECIFIC SQL050909191710600
        EXTERNAL NAME 'com.ibm.db2.tools.mtk.mtkinfxudf.infxUDFs! extend()'
        LANGUAGE JAVA
        PARAMETER STYLE JAVA
        NOT VARIANT
        FENCED THREADSAFE
        NOT NULL CALL
        NO SQL
        NO EXTERNAL ACTION
        NO SCRATCHPAD
        NO FINAL CALL
        ALLOW PARALLEL
        NO DBINFO;

thanks
Rick

> > hi
>
[quoted text clipped - 6 lines]
> Or at least provide a pointer to the reference material
> for the function.

Reply to this Message

Ian - 20 Mar 2007 20:51 GMT

> The EXTEND function adjusts the precision of a DATETIME or DATE value.
> The expression cannot be a quoted string representation of a DATE
> value.
>
> If you do not specify first and last qualifiers, the default
> qualifiers are YEAR TO FRACTION(3).

Well, DB2 doesn't support varying precision. There are 3 date/time
datatypes:

date (date only)
time (time only)
timestamp (equivalent to YEAR TO FRACTION(6), if it existed)

Because there's no concept of different precisions for a timestamp,
I'm not really sure if this function actually applies in DB2.

However, I am confused by the function definition you show from
the IBM Migration Toolkit (MTK). My guess is that it simply
returns a timestamp with all of the "insignificant" values zeroed
out, i.e.

2007-03-20-12.00.00.000000

(i.e. that's extending a date to include the current hour). But
I wouldn't consider this to be the same as a DATETIME YEAR TO HOUR.

Does this MTK function not provide what you're looking for?

Ian

Reply to this Message

lakiprabha@gmail.com - 23 Mar 2007 20:28 GMT

> hi
>
[quoted text clipped - 4 lines]
> Thanks
> rick

DB2 doesn't have equivalent function hence MTK providing as JAVA UDF,
if you further question please contact to mtk@us.ibm.com

Reply to this Message