IBM DB2 for i: Code example

This document contains information about using the User Defined SQL function DELTA.

This function calculates the difference between numeric, date or time values from the current row and the prior row.

>> DELTA ( expression,  |
                                                     | character-expression                              | )><
                                                     | expression, character-expression | 

The DELTA function returns the change in a value from the previous row. Up to three arguments can be provided depending on the characteristics of the first argument.

If only the first argument is specified then it must be an integer or decimal value.

If the first argument is a numeric value then a second argument may be specified representing a control group. This control group must be a character string. When the value of the character string changes from the previous row then the return value is set to 0.

If the first argument is a date, time or timestamp (or a valid string representation of a date, time or timestamp) value then the rules for the second argument are as follows:

• If the first argument is a date then the second argument must be a 'Y' (Years), 'M' (Months) or 'D' (Days)
• If the first argument is a time value then the second argument must be an 'HH' (hours), 'MM' (minutes) or 'SS' (seconds).
• If the first argument is a timestamp value then the second argument must be one of the prior values or 'MS' (milliseconds) or 'US' (microseconds).

For datetime values a third argument may be specified representing a control group. This control group must be a character string. When the value of the character string changes from the previous row then the return value is set to 0.

If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The data type of the result depends on the data type of the first argument. If the first argument is an integer or decimal value then the result is a decimal value with no precision. If the first argument is a datetime value then the result is an integer.

Multiple DELTA functions specified for a single row will be executed in separate threads. The first row will always return 0.

Examples

Integer and decimal deltas

Assume that table QAZPDQJOBS contains three rows. Each row represents data collected during subsequent executions of a job (INVOICING). The following statement will return the actual values for columns QRNEVT (integer value) and QRNDSZ (packed decimal value) and the differences between the current values and the value in the prior row:

 SELECT QTSJNM, QRNTSR, QRNEVT, QRNDSZ,
                            delta(QRNEVT), delta(QRNDSZ)
 FROM qazpdqjobs ORDER BY QRNTSR         

The DELTA function returns 0 for the first row. The second row shows a reduction (negative value) between the QRNEVT value from row 1 and the QRNEVT value in row 2. An increase (positive value) is returned for the QRNDSZ DELTA function. The third row returns decreases in both values from the previous row values.

Date

Assume that table EVENTDATA contains historical records about certain events. Each record contains the date the event was created and the date and time the event was changed. The following SQL statement calculates the delta between the date and times an event was changed:

 SELECT evtchgdt,  
                           delta(evtchgdt,'D'), evtchgtm,  
                           delta(evtchgtm,'SS')
 FROM EVENTDATA
 WHERE evtdate = '1960-01-01'
 ORDER BY evtchgdt, evtchgtm
 OPTIMIZE FOR 1 ROW         

The above result returns the delta, in days ('D'), for column evtchgdt and the delta, in seconds ('SS') for evtchtm. The data is ordered by date and time.

Because the date and time are contained in two separate columns the time goes negative when the date changes. The negative value can be eliminated by adding the control group parameter as follows:

 SELECT  evtchgdt, delta(evtchgdt,'D'), evtchgtm, delta(evtchgtm,'SS',char(evtchgdt))
 FROM  EVENTDATA
 WHERE evtdate = '1960-01-01'
 ORDER BY evtchgdt, evtchgtm
 OPTIMIZE FOR 1 ROW

Since the control group parameter expects a character string the evtchgdt column had to be converted from a date type value to a character string (CHAR built in function).

Notice that the DELTA function allows expressions as parameters. When the date value changes the time delta is set to 0. Since the control group was not specified for the date delta column then the change in days is returned.

Time

Assume that table DSPFDMBR contains the periodic output of the DSPFD TYPE(*MBR) command. The following SQL statement uses the DELTA function to determine the elapsed time (in minutes) and the total number of open and close operations occurring at different collection intervals:

 SELECT  MBFILE,
 time( substr(MBRTIM,1,2) || '.' ||
 substr(mbrtim,3,2) || '.' ||
 substr(mbrtim,5,2)),
 DELTA(time(substr(MBRTIM,1,2) || '.' ||
 substr(mbrtim,3,2) || '.' ||
 substr(mbrtim,5,2)),'MM'),
 MBOPOP, MBCLOP, DELTA(MBOPOP + MBCLOP)
 FROM dspfdmbr ORDER BY MBFILE

The first row returns 0 for the columns using the delta functions. The result of the query shows that every 3 or 4 minutes 200 opens and closes are occurring against files CUST_DIM and DBMONDATA.

The first row for DBMONDATA results in negative values for the delta functions, due to the data being in MBFILE sequence. The following SQL statements shows the use of the control group parameter of the DELTA function:

 SELECT  MBFILE,
 time( substr(MBRTIM,1,2) || '.' ||
 substr(mbrtim,3,2) || '.' ||
 substr(mbrtim,5,2)),
 delta(time(substr(MBRTIM,1,2) || '.' ||
 substr(mbrtim,3,2) || '.' ||
 substr(mbrtim,5,2)),
 'MM',MBFILE), MBOPOP, MBCLOP, delta((MBOPOP + MBCLOP),MBFILE)
 FROM dspfdmbr ORDER BY 1

In the above result the first row of the control group (MBFILE) returns zeros for the delta columns. If the next row contains the same control group value then the difference is calculated.

Timestamp

The following SQL statement will return the difference in milliseconds between timestamps contained in some Database monitor data:

 SELECT qqtime, DELTA(qqtime,'MS'), QQRID
 FROM starlib.dbmondata
 WHERE qqjnum = '441674' and qqucnt = 253
 ORDER BY qqtime;

The time duration is returned as a whole number without rounding. If the time took less than a millisecond then the time is returned as zero. This may not be granular enough. The next SQL statement is identical to the previous statement, however the duration type is changed to microseconds ('US'):

 SELECT qqtime, DELTA(qqtime,'US'), QQRID
 FROM starlib.dbmondata
 WHERE qqjnum = '441674' and qqucnt = 253
 ORDER BY qqtime 

The last example shows the elapsed time between synchronous Read Start and Read Complete IO events from the outfile (trcbytime) of a PRTPEXRPT command. The IO sector field is used as a control group:

 SELECT QTRDUN, QTRDSE, QTRETS, delta(QTRETS,'MS',QTRDSE), QTREID
 FROM trcbytime
 WHERE qtrdun = 27 ORDER BY qtrdun, qtrdse, qtrets

Usability notes

If the DELTA function is dependent on the order of a column, or columns, within the query then these columns should be specified on an ORDER BY clause. SQL does not guarantee order.

If the optimizer chooses a parallel enabled method then the results of the DELTA function may be unpredictable. Use the CHGQRYA command or the QAQQINI file to disable parallelism for the query.

Use ALWCPYDTA(*NO) to prevent the optimizer from using a sort to order the data. The DELTA function appears to be called prior to the sort resulting in erroneous results.