× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Matt,

I've not ever noticed a 10x slowdown when using UDFs.

Here's my version:
create function CvtNumISOtoDate(indte Numeric(8,0)) returns Date  
language SQL                                                           
not fenced                                                             
deterministic no external action                                       
returns null on null input                                             
contains SQL                                                           
set option datfmt=*ISO                                                 
begin                                                                  
  declare INVALID_DATETIME condition for '22007';                      
  declare exit handler for INVALID_DATETIME                            
    begin                                                              
      resignal sqlstate '01HD1'                                        
        set message_text = 'Invalid date/time replaced by NULL';       
      return NULL;                                                     
    end;                                                               
  return(                                                              
     case indte                                                        
      when 0 then NULL                                                 
      when 99999999 then date('9999-12-31')                            
  declare exit handler for INVALID_DATETIME                      
    begin                                                        
      resignal sqlstate '01HD1'                                  
        set message_text = 'Invalid date/time replaced by NULL'; 
      return NULL;                                               
    end;                                                         
  return(                                                        
     case indte                                                  
      when 0 then NULL                                           
      when 99999999 then date('9999-12-31')                      
      else date(substr(digits(indte),1,4) concat '-' concat      
                substr(digits(indte),5,2) concat '-' concat      
                substr(digits(indte),7,2))                       
     end                                                         
   );                                                            
end;                  

A couple of quick test does show it to be slower, but not 10x slower.

See what you find.

I'd consider opening a PMR with IBM on this issue.


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx 
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tyler, Matt
Sent: Thursday, June 29, 2006 11:04 AM
To: Midrange Systems Technical Discussion
Subject: RE: AW: SQL - Calculate the number of hours between 
2 timestamps

Just a word of warning about using SQL UDFs.  We implemented 
Birgitta's
example 
http://archive.midrange.com/rpg400-l/200606/msg00263.html and a
query, we added it to, slowed by an order of about 10 times.  Not
Birgittas fault from her example but I believe it's the way the system
implements the actual code behind SQL UDFS.   Putting the raw 
code from
her UDF into the statement (even though it made the statement longer)
was such an improvement that I doubt we will use a SQL UDF ever again.


We have not implemented RPGLE UDFs yet to see how they stack up. 

By the way, the UDF had to be called about 7 times with in this one
statement and the total number of records estimated to be 
processed was
around 40,000.  The job was stacking up the IO on 
QSYS2/QSQPTABL at well
over 10,000,00.  

Thanks, Matt



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, June 29, 2006 6:22 AM
To: Midrange Systems Technical Discussion
Subject: Re: AW: SQL - Calculate the number of hours between 2
timestamps

I suppose one could write their own bif.  Although then it would be a
UDF.
http://faq.midrange.com/data/cache/185.html

Rob Berendt
-- 

Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





vhamberg@xxxxxxxxxxx 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
06/28/2006 06:40 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: AW: SQL - Calculate the number of hours between 2 timestamps






BTW, timestampdiff is not an iSeries exclusive extension - it 
is part of

several SQL implementations. Its behavior in other scenarios 
is probably

similar, although there is the chance that one vendor does it better
than 
another. Is it part of any of the SQL standard specifications?

Vern

-------------- Original message -------------- 
From: rob@xxxxxxxxx 

I agree, it sucks. However, when you think about what you would do
with 
a 
calculating the difference between two timestamps, doesn't 
the decimal

answer work "good enough"? The OP wanted to know if the 
duration was 
greater than 8 hours. We posted something like select 
ts1-ts2 from ...

and got a number. And if that number was greater than 80000 then it
was 
greater than 8 hours. 

If is not "good enough" and you have a business reason, then see if
you 
can find a standard, preferably ISO, that says otherwise. IBM won't 
change timestampdiff because someone is probably using it and is 
counting 
on it's anomalies. But if you have a strong enough business 
case, and 
submit a DCR then perhaps they'll come out with timestampdiffiso.
Don't 
know what the odds of them ever creating one that says 
timestampdiffMS

for 
MicroSoft. Legal reasons, animosity, and possible confusion with 
milliseconds might intervene. 

Rob Berendt 
-- 
Group Dekko Services, LLC 
Dept 01.073 
PO Box 2000 
Dock 108 
6928N 400E 
Kendallville, IN 46755 
http://www.dekko.com 





Loyd Goodbar 
Sent by: midrange-l-bounces@xxxxxxxxxxxx 
06/27/2006 09:27 PM 
Please respond to 
Midrange Systems Technical Discussion 


To 
Midrange Systems Technical Discussion 
cc 

Subject 
Re: AW: SQL - Calculate the number of hours between 2 timestamps 






Yeah, I saw that too. Frankly, that sucks. 

Does anyone know if this just an iSeries DB2 thing, or does it also 
afflict the "other" DB2? I mean, ILE RPG and even SQL Server can 
calculate accurately durations between two date/time stamps. 

--lg 

On Jun 27, 2006, at 10:59 AM, rob@xxxxxxxxx wrote: 

Study that timestampdiff bif carefully. A lot of assumptions are 
made. 
Which may, or may not, matter for your situation. Straight from 
the book: 

The following assumptions may be used in estimating the 
difference: 

there are 365 days in a year 
there are 30 days in a month 
there are 24 hours in a day 
there are 60 minutes in an hour 
there are 60 seconds in a minute 
These assumptions are used when converting the information in the 
second 
argument, which is a timestamp duration, to the interval type 
specified in 
the first argument. The returned estimate may vary by a number of 
days. 
For example, if the number of days (interval 16) is 
requested for a 
difference in timestamps for '1997-03-01-00.00.00' and 
'1997-02-01-00.00.00', the result is 30. This is because the 
difference 
between the timestamps is 1 month so the assumption of 30 
days in a 
month 
applies. 


Rob Berendt 

-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing

list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l. 


-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing

list 
To post a message email: MIDRANGE-L@xxxxxxxxxxxx 
To subscribe, unsubscribe, or change list options, 
visit: http://lists.midrange.com/mailman/listinfo/midrange-l 
or email: MIDRANGE-L-request@xxxxxxxxxxxx 
Before posting, please take a moment to review the archives 
at http://archive.midrange.com/midrange-l. 

-- 

This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


-- 

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


-- 
This is the Midrange Systems Technical Discussion 
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.