[DB2-L] V7 for z/OS, UDF-Question

Ruediger Kurtz

[DB2-L] V7 for z/OS, UDF-Question
Walter,

thanks for the reply, and yes, I suppose that UDF would be stage-2.
As to V8 ... alsas, we're not yet on V8, but I like the idea of a reference-table.

Thanks

Ruediger

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Walter Janißen
Gesendet: Donnerstag, 12. Januar 2006 08:56
An: [login to unmask email]
Betreff: Re: [DB2-L] V7 for z/OS, UDF-Question

Hi Rüdiger

I am not familiar with UDF, but I am afraid, that UDF would be a solution
for you, because the predicate would become stage2, wouldn't it?

May be, you could use a reference table, maintained by a trigger, where you
have 2 columns: the timestamp and the inverted timestamp and in all queries
you join to that table.

If you are in V8, there are a lot of options, e.g. to avoid the NPI, but
nevertheless partition by the inverted timestamp.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Vaughan

Re: AW: [DB2-L] V7 for z/OS, UDF-Question
(in response to Ruediger Kurtz)
Probably depends on how you do it. If you wrote a UDF to convert the inverted timestamp back to a normal timestamp (where UDF1(INVERTED_TS_COL) = ?) then I agree that this would end up being stage-2, but if you took the other approach and wrote a query to convert the timestamp variable to match the inverted timestamp ("where INVERTED_TS_COL = UDF1(?)"), then I think this would end up being stage-1 as a non-column expression.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Kurtz, Rüdiger
Sent: Thursday, January 12, 2006 4:15 AM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] V7 for z/OS, UDF-Question

Walter,

thanks for the reply, and yes, I suppose that UDF would be stage-2.
As to V8 ... alsas, we're not yet on V8, but I like the idea of a reference-table.

Thanks

Ruediger

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Walter Janißen
Gesendet: Donnerstag, 12. Januar 2006 08:56
An: [login to unmask email]
Betreff: Re: [DB2-L] V7 for z/OS, UDF-Question

Hi Rüdiger

I am not familiar with UDF, but I am afraid, that UDF would be a solution
for you, because the predicate would become stage2, wouldn't it?

May be, you could use a reference table, maintained by a trigger, where you
have 2 columns: the timestamp and the inverted timestamp and in all queries
you join to that table.

If you are in V8, there are a lot of options, e.g. to avoid the NPI, but
nevertheless partition by the inverted timestamp.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: AW: [DB2-L] V7 for z/OS, UDF-Question
(in response to Mike Vaughan)
Hi Ruediger,

Here's the UDF you may use:

CREATE FUNCTION INV26
(INC CHAR(26))
RETURNS CHAR(26)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
CAST( TRANSLATE(‘ABCDEFGHIJKLMNOPQRSTUVWXYZ' , INC,
'ZYXWVUTSRQPONMLKJIHGFEDCBA')
AS CHAR(26) );

As it's SQL function there's no need for WLM SP and it runs fast.
Use it on the :HV - (as suggested before)

SELECT ...
FROM ....
WHERE INVERTED_TS = INV26(:hv)

PS - if you need strings > 26 you'll have to revert to X'...'

Isaac Yassin

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Kurtz, R?diger
Sent: Thursday, January 12, 2006 12:15 PM
To: [login to unmask email]
Subject: [DB2-L] AW: [DB2-L] V7 for z/OS, UDF-Question

Walter,

thanks for the reply, and yes, I suppose that UDF would be stage-2.
As to V8 ... alsas, we're not yet on V8, but I like the idea of a reference-table.

Thanks

Ruediger

-----Ursprüngliche Nachricht-----
Von: DB2 Data Base Discussion List [mailto:[login to unmask email] Im Auftrag von Walter Janißen
Gesendet: Donnerstag, 12. Januar 2006 08:56
An: [login to unmask email]
Betreff: Re: [DB2-L] V7 for z/OS, UDF-Question

Hi Rüdiger

I am not familiar with UDF, but I am afraid, that UDF would be a solution for you, because the predicate would become stage2,
wouldn't it?

May be, you could use a reference table, maintained by a trigger, where you have 2 columns: the timestamp and the inverted timestamp
and in all queries you join to that table.

If you are in V8, there are a lot of options, e.g. to avoid the NPI, but nevertheless partition by the inverted timestamp.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be
reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be
reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm