V7 for z/OS, UDF-Question

Ruediger Kurtz

V7 for z/OS, UDF-Question

Hello everyone out there,

we have several tables that are partitioned by an inverted timestamp, while the original timestamp still is a column of the respective table.
Now we have several ad-hoc-queries that access those tables via 'where timestamp = ...', but there is no supporting index for those queries. Since the tables are quite large those queries take quite some time. We are very reluctant to add an NPI to those tables, so the question came up whether one could use a User Defined Function that sort of re-inverts the timestamp_inverted-colum to its original value.
We haven't used UDFs in our shop so far, so if any of you are willing to share their comments, ideas, suggestions as to what to do and what to look out for we'd be grateful.

Thanks a lot in advance

Ruediger Kurtz

---------------------------------------------------------------------------------
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

Walter Janißen

Re: V7 for z/OS, UDF-Question
(in response to Ruediger Kurtz)
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