SQL in Action the Problem Solver

(1)

SQL in Action – the Problem Solver

Michael Tiefenbacher ids-System GmbH

Session Code: C7

Tuesday, 15th November 2016, 15:10 - 16:10 | Platform: DB2 LUW

(2)

Objectives – as Submitted to IDUG

• Extracting relevant information from string data types. Regular expressions are a nice thing for that - can we use them in DB2?

• Using OLAP functions to provide aggregations and much more.

Let us analyze the db2diag.log with SQL!

• Lots of problems around date and time arithmetic exist out

there. This session will show you some new functions - let us do it the easy way.

• Challenge: Working with multiple prioritized actions and durations and mapping them to single time line.

• If LOBs come into the game it gets more complcated. Why?

(3)

Agenda

• Introduction and Motivation

• Regular Expression in SQL

• OLAP Functions

• Date- & Time-Arithmetic

• Mapping multiple prioritized Action to a Timeline

• Optional: SQL Functions and LOBs

(4)

Introduction & Motivation

• SQL is extremely powerful

• Although often underestimated

• It can be used to build solutions not only for programmers

• DBA tools

• Data Warehouse solutions

• Analytical challenges

• Often asked in

• Customer projects

• Forums i.e. stackoverflow

SQL

(5)

Agenda

• OLAP Functions

(6)

Regular Expressions

• Powerful technique to filter data on certain criteria

• Known from many programming languages

• Could be very useful within SQL

• i.e. filtering or checking data in ETL processes

• Requirement for a very long time

• Bringing the Power of Regular Expression Matching to SQL (Knut Stolze)

• from 2003

• Implemented as UDF

• http://www.ibm.com/developerworks/data/library/techarticle/0301stolze/

0301stolze.html

(7)

Regular Expressions in DB2

• Before DB2 11.1 regular expressions where delivered as part of XML functions

• 'fn:matches'

• 'fn:replace'

• 'fn:tokenize'

• “DB2® XQuery regular expression support is based on the XML schema regular expression support as defined in the W3C Recommendation XML Schema Part 2: Datatypes Second Edition with extensions as defined by W3C Recommendation XQuery 1.0 and XPath 2.0 Functions and

Operators.” (IBM KC)

SELECT id, comment

(8)

Regular Expessions with DB2 11.1

• SQL functions have been added to support REGEXP

Function Description regexp_count

regexp_match_count how often is the expression found in a string regexp_like used in WHERE condition

regexp_instr start or end position of the matched substring regexp_substr

regexp_extract extracts the specified substring regexp_replace replaces the substring

(9)

Regular Expession – Function Parameters

• REGEXP functions have a set of common parameters

• FLAG:

Flag Description

c case insensitive (default) i case sensitive

m input can span several lines n ‘.’ matches a line terminator s same as n

x white space is ignored, unless escaped

(10)

Regular Expessions – Problem Description

• How often is a certain tag part of a XML?

• Seems to be a XML question with a XQuery answer but XML data is stored in CLOB column

• Casting it as XML took too much CPU for user limits

• So we are back to a string – a long one

• How can we count the occurrence?

• No SQL function for this is delivered with DB2 … up to now

• Workarounds with replace and transform

• With DB2 11.1: regexp_match_count or regexp_count

(11)

Example – Number of Occurrences

'How much wood would a woodchuck chuck if a woodchuck could chuck wood

he would chuck as much wood as a woodchuck could chuck if a woodchuck would chuck wood.'

select length(

translate(

vargraphic(

replace(replace(text, '!', ''), 'wood', '!') ),

'', ' abcdefghijklmnopqrstuvwxyzH.','') )

from teststring

How often can we find

“wood“ in this string?

Ensure the replacement string is not in the string and replace the search word with this character

Translate all characters to an empty string

fill character necessary for

double byte chars

(12)

REGEXP Example

select id, text, regexp_match_count(text, '\d') from test;

select id, text, regexp_extract(text, '\b\d{12,15}\D') from test;

digits

12 to 15 digits after a word boundary followed by non-digits

(13)

REGEXP Example – Check Constraint

create table checktelno (id int, telno varchar(50)

check (regexp_like(telno, '^(\+[0-9]{2,3})[\d\s\/\(\)-]+')) );

insert into checktelno values (1, '+49 (0)176/10180242');

insert into checktelno values (2, '+49 (0)176101802 ext 42');

insert into checktelno values (3, '+49 0176 101802-42');

insert into checktelno values (4, '0049 0176 1018 02/42');

How many rows will be inserted and which one(s)?

• ID 1 will be inserted

• ID 2 will be inserted – Why? => because of the regexp_LIKE – can be avoided

Regular expression for a telephone number

(14)

Agenda

• OLAP Functions

(15)

OLAP Functions – Overview

• OLAP functions are part of SQL standard 2003

• Are also called window functions, because the relate to a group (window) of rows

• Keyword: OVER()

• Functions:

• ROW_NUMBER

• RANK and DENSE_RANK

• Other column functions

• LEAD and LAG

(16)

OLAP Functions – Special Considerations

• A column or OLAP function can only be used in

• the SELECT list of a fullselect

• a HAVING clause

• limited situations in WHERE or GROUP BY

• Only possible in a WHERE clause if used in a HAVING clause and the argument of the column function has a correlation to the group

• Otherwise error message

• SQL0120N Invalid use of an aggregate function or OLAP function.

• Tip

• Using a CTE (Common Table Expression) can be a workaround

• Can make your life easier and your queries faster

(17)

OLAP Function – Example

• List all employees of the department with their salary and the percentage of the salary compared to the overall salary of the department

(18)

Example – Classic Solution

• Approach

• Overall salary

• Combined with

sub-query

SELECT workdept, firstnme, lastname, salary FROM employee

SELECT workdept, sum(salary) FROM employee

GROUP BY workdept

WITH dept AS (

SELECT workdept, sum(salary) as deptsalary FROM employee

GROUP BY workdept)

SELECT workdept, firstnme, lastname, salary, salary/deptsalary * 100 as percent FROM employee e

INNER JOIN dept d

ON e.workdept = d.workdept

(19)

Example – OLAP Solution

• Solution

• GROUP BY is replaced with OVER

and PARTITION BY

• No subquery necessary

SELECT workdept, firstnme, lastname, salary,

(salary/sum(salary) OVER(PARTITION BY workdept))*100 as percent

FROM employee

(20)

OLAP Functions – ROW_NUMBER and ORDER BY

• Numbering the result rows

• OVER()

• OVER without further arguments will use the whole resultset as

“window“

• To get a dedicated sort order the OVER() clause can be extended with an ORDER BY

SELECT firstnme, lastname, edlevel, row_number() OVER()

FROM employee

SELECT firstnme, lastname, edlevel,

row_number() OVER(ORDER BY LASTNAME) FROM employee

(21)

PARTITION BY

• ROW_NUMBER() functionality can be used to explain the PARTITION BY

• The GROUP BY of a SELECT can be compared to the PARTITION BY of the OVER()

• Numbering by department and membership

SELECT workdept, firstnme, lastname, hiredate, row_number() OVER(PARTITION BY workdept

ORDER BY hiredate) as num FROM employee

ORDER BY workdept

(22)

Result Window and ROW or RANGE

• OVER()

• Without argument does not limit the result set so it refers to the whole table

• With ROWS the window can be limited to certain rows (relative to the current position)

• With RANGE the window can be limited to certain values

• Following option can be used

• UNBOUNDED PRECEDING

• <Integer> PRECEDING

• CURRENT ROW

• <Integer> FOLLOWING

• BETWEEN

• Hint: for ROWS and RANGE a sort order has to be specified

(23)

ROWS vs. RANGE

• This example shows the difference between both options

sum(value) OVER (ORDER BY value

BETWEEN 1 PRECEDING AND 1 FOLLOWING)

sum is

calculated

ROWS RANGE

sum is calculated from three values (if they exist)

2

2 4

5 7

9 17

6 6 9 9

11 7

ROWS RANGE

VALUE

(24)

OLAP Function: LEAD and LAG

• Can be used to compare or combine values from another row with the current one

• LAG (or LEAD)

• Options

• Column: column which is referenced in the other row

• offset: default is 1 – thismeans 1 row before the current (LAG) or 1 row after the current row (LEAD)

• default_value: defaults to NULL – can be used to set a value if the row with the specified offset does not exist

• 'RESPECT NULLS' or 'IGNORE NULLS': determines how NULL values will be handled

(25)

OLAP Function: LEAD and LAG – Example

• Determine the availability of a database

• No monitoring element for this

• db2diag.log lists start and stop points – so this is all we have

• db2diag.log file can be queried with pd_get_diag_hist table function

• Idea:

• Filter relevant events

• Calculate times between starts and stops

(26)

OLAP Function: LAG Example – Situation

SELECT dbname, timestamp,

eventtype

FROM TABLE(PD_GET_DIAG_HIST('MAIN', 'E', '', NULL, NULL)) as t WHERE DBNAME is not null

AND EVENTTYPE in ('START', 'STOP') ORDER BY timestamp

Filter entries with no database relation CHANGE and NULL entries

will be filtered

Problem:

Difference of two timestamps of following rows need to be calculated lists DB2 diag.log entries

(27)

db2diag.log Analysis

SELECT dbname, eventtype,

timestamp as Event_timestamp, lag(timestamp)

over (partition by dbname order by timestamp) as reference_time,

case when eventtype = 'STOP'

then timestampdiff(2, timestamp - lag(Timestamp) over (partition by dbname order by timestamp))

end as Active_Time,

case when eventtype = 'START'

then timestampdiff(2, timestamp - lag(Timestamp) over (partition by dbname order by timestamp))

end as Inactive_Time

FROM TABLE(PD_GET_DIAG_HIST('MAIN', 'E', '', NULL, NULL)) as t WHERE DBNAME = 'SAMPLE'

LAG to compare times of different rows

Difference of two timestamps in seconds (2)

Filtering SAMPLE database Case to

set activity

time or inactivity

time

(28)

RATIO_TO_REPORT

• Returns the ratio of a value to the sum of all values in the OLAP window

• Example

• Identical results besides the data type

SELECT firstnme, lastname, salary,

salary/sum(salary) OVER(PARTITION BY workdept)

* 100 as percent,

ratio_to_report(salary) OVER(PARTITION BY workdept)

* 100 as percent2 FROM employee

DECFLOAT DECIMAL(31,22)

(29)

OLAP Functions – Summary

• OLAP functions

• use the OVER() clause to set the OLAP window and avoids the need of a sub-select

• allow calculations within the OLAP window

OLAP window Current

row

LEAD LAG FIRST_VALUE

PRECEDING

FOLLOWING

(30)

Agenda

• OLAP Functions

(31)

Date- and Time-Arithmetic

• Lots of new functions have been implemented with DB2 11.1

• Really useful for analytical applications and Data Warehouses

• Time is always a dimension

• Date- and time information is stored in numerous ways on operational systems

• Often not in appropriate ways to process them (VARCHAR,…)

(32)

Function Examples

select this_year('15.11.2016') as this_year,

this_quarter('15.11.2016') as this_quarter, this_month('15.11.2016') as this_month,

next_month('15.11.2016') as next_month,

next_quarter('15.11.2016') as next_quarter, next_year('15.11.2016') as next_year

from sysibm.sysdummy1

• THIS_* and NEXT_* but no LAST_*

• Could be done via: this_month('31.03.2016') - 1 month

(33)

DATE_TRUNC I

SELECT

date_trunc ('millennium', timestamp('2016-11-15 00:00:00.000000')) as date_trunc_millennium

,date_trunc ('century', timestamp('15.11.2016')) as date_trunc_century ,date_trunc ('decade', date('2016-11-15')) as date_trunc_decade

,date_trunc ('year', timestamp('15.11.2016')) as date_trunc_year

,date_trunc ('quarter', timestamp('15.11.2016')) as date_trunc_quarter ,date_trunc ('month', timestamp('15.11.2016')) as date_trunc_months ,date_trunc ('days', timestamp('15.11.2016')) as date_trunc_days FROM sysibm.sysdummy1;

• Truncate parts of a date or timestamp

(34)

DATE_TRUNC II

select

date_trunc ('hours', current timestamp) as hours,

date_trunc ('minutes', current timestamp) as minutes, date_trunc ('seconds', current timestamp) as seconds,

date_trunc ('milliseconds', current timestamp) as milliseconds, date_trunc ('microseconds', current timestamp) as microseconds from sysibm.sysdummy1

Only make sense with timestamp(7) to

timestamp(12)

(35)

DATE_PART I

select

date_part('millennium', timestamp('2016-11-15 00:00:00.000000')) as date_part_millennium

,date_part('century', '15.11.2016') as date_part_century

,date_part('decade', date('15.11.2016')) as date_part_decade ,date_part('year', '15.11.2016') as date_part_year

,date_part('quarter', '15.11.2016') as date_part_quarter ,date_part('month', '15.11.2016') as date_part_months ,date_part('days', '15.11.2016') as date_part_days from sysibm.sysdummy1

• Extracts parts of a date or timestamp

(36)

DATE_PART II

select

date_part('hours', current timestamp) as date_part_hours,

date_part('minutes', current timestamp) as date_part_minutes, date_part('seconds', current timestamp) as date_part_seconds,

date_part('milliseconds', current timestamp) as date_part_milliseconds, date_part('microseconds', current timestamp) as date_part_microseconds from sysibm.sysdummy1

(37)

DATE_PART III

select

date_part('epoch', current timestamp) as date_part_dayssince1970, date_part('doy', current timestamp) as date_part_dayofyear,

date_part('week', current timestamp) as date_part_week, date_part('dow', current timestamp) as date_part_dayofweek from sysibm.sysdummy1

• Some “unusual” parts (formats) have been added

• These function and formats have been added for Netezza and PostgreSQL compatibility

(38)

Week

select week(date('01.03.2016')) as week, week('2016-03-01') as week,

week_iso(date('01.03.2016')) as week_iso,

date_part('week', '01.03.2016') as date_part_week from sysibm.sysdummy1

Attention:

select week('01.03.2016') as week from sysibm.sysdummy1

⇒ Error -443

⇒ These are also “old” SYSFUN functions

(39)

DAY Functions

select day('01.03.2016') as day,

dayofweek('01.03.2016') as dayofweek, dayofmonth('01.03.2016') as dayofmonth,

dayofyear('2016-03-01') as dayofyear,

dayofweek_iso('2016-03-01') as dayofweek_iso,

date_part('dow', '01.03.2016') as date_part_dayofweek from sysibm.sysdummy1

Attention:

values dayofweek_iso('01.03.2016') values dayofyear('01.03.2016')

(40)

Duration Functions & Predicates

• DAYS_TO_END_OF_MONTH

• OVERLAPS

• predicate

SELECT 'Overlap found' as result FROM sysibm.sysdummy1

WHERE (date('01.03.2015'), date('01.08.2015')) overlaps (date('01.06.2015'), current date) SELECT days_to_end_of_month('24.12.2015')

as days_to_end_of_month FROM sysibm.sysdummy1

(41)

BETWEEN Functions

• Functions

• YEARS_BETWEEN

• Counts full years

• MONTH_BETWEEN

• result decimal(31,15)

• „returns an estimate of the number of months” Knowledge Center

• WEEKS_BETWEEN

• Counts full weeks (7 days)

• DAYS_BETWEEN

• Counts full days

• YMD_BETWEEN

• Years, months and days between two datetime information

(42)

Calculating Durations

• One option: timestampdiff

• Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.(IBM KC)

• Different granularities

SELECT timestampdiff(8,

CHAR(TIMESTAMP('2016-02-03 14:57:57.368992') – TIMESTAMP('2015-12-17 12:35:24.383976'))) FROM SYSIBM.SYSDUMMY1

SELECT (JULIAN_DAY('2016-02-03 14:57:57.368992') -

JULIAN_DAY('2015-12-17 12:35:24.383976') ) * 24 +(MIDNIGHT_SECONDS('2016-02-03 14:57:57.368992') –

MIDNIGHT_SECONDS('2015-12-17 12:35:24.383976')) /(60 * 60)

FROM SYSIBM.SYSDUMMY1

SELECT hours_between('2016-02-03 14:57:57.368992', '2015-12-17 12:35:24.383976') FROM SYSIBM.SYSDUMMY1

1130

1154

(43)

More Duration: BETWEEN Functions

SELECT

years_between('01.03.2016', '01.01.1970') as years_between, months_between('01.03.2016', '01.01.1970') as months_between, weeks_between('01.03.2016', '01.01.1970') as weeks_between, days_between('01.03.2016', '01.01.1970') as days_between, ymd_between('01.03.2016', '01.01.1970') as ymd_between,

hours_between('01.03.2016', '01.01.1970') as hours_between,

minutes_between('01.03.2016', '01.01.1970') as minutes_between, seconds_between('01.03.2016', '01.01.1970') as seconds_between FROM sysibm.sysdummy1

(44)

MONTH_BETWEEN

• Result shows

• Whole month before the separator

• Month fractions after the separator

• Month is ALWAYS calculated with 31 days

• Check out the IBM KC for details

SELECT

months_between('01.04.2016', '01.04.2015') as same_day1, months_between('01.04.2016', '01.03.2016') as same_day2, months_between('02.04.2016', '01.04.2016') as other_day FROM sysibm.sysdummy1

(45)

UTC Timestamp

• Convert timestamps to or from UTC

• For any timestamp / date – not just the current one

• Timezones

SELECT

to_utc_timestamp('2016-03-01-14.00.00','Europe/Brussels') as utc_mar, to_utc_timestamp('2016-07-01-14.00.00','Europe/Brussels') as utc_jul, from_utc_timestamp('2016-03-01-14.00.00','Europe/Brussels')

as from_utc

FROM sysibm.sysdummy1 Take care – mixed

case letters necessary!

(46)

Agenda

• OLAP Functions

(47)

Timeline – Problem Description

• Business problem for an analytical solution

• Different tasks with different priorities can overlap

• There are no overlaps of tasks with the same priority

• Need to build time slices

time Prio 1

Prio 2

Task red None Task red - green

(48)

Timeline – Problem Examples

• Example 1

• Example 2

• Example 3

time

Telephone - Prio 1 Mail - Prio 2

Tel 1 Tel 2

Mail 1 Mail 2

time

Tel 1

Mail 1

Tel 2 Tel 3

time

Tel 1

Mail 1

(49)

Timeline – Problem Example Data

(50)

Time Slices – Solution Ideas

• Goal: Solution in a single SQL statement – without cursor

• Idea 1:

• Prio 1 tasks are easy to select

• Possibility to UNION them with priority 2 tasks?

• PROBLEM

• In example 1 (Mail 1) two slices need to be created from a single task

• This has to by dynamic as example 3 already shows four slices

• Idea 2:

• Usage of OLAP functions

• Reference previous task with LAG functionality

• PROBLEM

• In example 3 the previous task is not enough – again a problem cause by the dynamic number of slices

(51)

Time Slices – Solution Ideas

• So how can we get the necessary flexibility / dynamic?

• Recursion is a way in SQL to generate a unknown number of levels

• But how should it look like in this scenario?

• Final solution idea:

• We have to give up the idea of overlapping durations

• Focusing on the points of change (Start and End)

Tel 1 Tel 2

Mail 1 Mail 2

(52)

Time Slices – Solution – Points of Change

• Generate the points of change

• Start point and end point are unioned by two statements

• Timestamp gets combined into a single field

• Information of origin will be saved in ts_type field

select userid,

startts as ts, 'S' as ts_type,

substr(text,1,1) as op, text

from temptest union all

select userid,

endts as ts, 'E' as ts_type,

substr(text,1,1) as op, text

from temptest

Start timestamp

Add operation

End timestamp

(53)

Time Slices – Solution – Prioritization

• Further challenges

• Implementation of prioritization

• How can we keep track of which activity / task is still active?

• Each end timestamp is the start of a new slice

• Could be a slice with no activity

• Or a slice of a task with a lesser priority

• Solution

• All tasks need to be recorded

• Remember: there are no parallel activities with the same priority

• Assign a weight for each event

• A positive weight at start time

• A negative weight (same absolute value) at its end

(54)

Time Slices – Solution

select userid,

startts as ts, 'S' as ts_type,

substr(Text,1,1) as op,

case

when substr(Text,1,1) = 'M' then 1 when substr(Text,1,1) = 'T' then 2 end as opvalue,

text from temptest union all

select userid,

endts as ts, 'E' as ts_type,

substr(Text,1,1) as op,

case

when substr(Text,1,1) = 'M' then -1 when substr(Text,1,1) = 'T' then -2 end as opvalue,

text from temptest

Negative weights for end events Positive weights

for start events

T has a higher priority than M and gets the higher weight

(55)

Time Slices – Solution

• Further enhancements are necessary

• With CTE (Common Table Expression) and OLAP functions

with t_startend as ( select userid,

…

from temptest union all

select userid,

…

from temptest )

select userid, ts, ts_type, op, opvalue, text,

sum(opvalue) over (partition by userid order by ts) as sum_opvalue, lead(ts) over (partition by userid order by ts) as lead_ts,

lag(ts_type) over (partition by userid order by ts) as lag_ts_type,

Sum of weights (operation value)

(56)

with t_startend as ( select userid,

…

from temptest) , t_enrich as (

select userid, ts, ts_type, op, opvalue, text,

sum(opvalue) over (partition by userid order by ts) as sum_opvalue, lead(ts) over (partition by userid order by ts) as lead_ts,

lag(ts_type) over (partition by userid order by ts) as lagts_type, lag(op) over (partition by userid order by ts) as lag_op,

lag(text) over (partition by userid order by ts) as lag_text from t_startend

order by userid, ts)

Time Slices – Complete Solution 1/2

(57)

select e.userid, e.ts as startts, e.lead_ts as endts, e.ts_type, e.op, e.text, e.sum_opvalue,

case

when e.tsart = 'S' and e.op = 'M' and e.lagts_type = 'S' and e.lag_op = 'T‚ then e.lag_op

when e.tsart = 'S' then e.op

when e.tsart = 'E' and e.op = 'T' and e.sum_opvalue = 1 then 'M' when e.tsart = 'E' and e.op = 'M' and e.sum_opvalue = 2 then 'T' else '-'

end as op_new,

t.text as text_stillactive, case

when e.ts_type = 'S' and e.op = 'M' and e.lag_ts_type = 'S' and e.lag_op = 'T' then e.lag_text

when e.ts_type = 'S' then e.text

when e.ts_type = 'E' and e.op = 'T' and e.sum_opvalue = 1 then t.text

when e.ts_type = 'E' and e.op = 'M' and e.sum_opvalue = 2 then e.lag_text

else '-'

Time Slices – Complete Solution 2/2

(58)

Agenda

• OLAP Functions

• Mapping multiple prioritized Actions to a Timeline

(59)

LOB Columns

• Large Objects complicate things a lot

• Not all functions support LOB data type

• Length / size of the objects

(60)

SQL with LOB Columns – Situation

• Situation

• Max. of three lines per ID

• Only single value per column and ID

• Result should look like this

ID MSG1(LOB) MSG2(LOB) MSG3(LOB)

1 Text1 NULL NULL

1 NULL Text2 NULL

2 Text3 Text4 NULL

3 Text5 NULL NULL

3 NULL NULL Text6

3 NULL Text7 NULL

ID MSG1(LOB) MSG2(LOB) MSG3(LOB)

1 Text1 Text2 NULL

2 Text3 Text4 NULL

3 Text5 Text7 Text6

(61)

SQL with LOB Columns – Solution

• Solution Idea

• Problem:

• Max is not supported for LOBs select id,

max(msg1) as msg1, max(msg2) as msg2, max(Msg3) as msg3 from t3

group by id

(62)

SQL with LOB Columns – Solution

• Solution

with temp as (

select id, msg1, msg2, msg3,

row_number() over (partition by id) as rownum from t3

)

select t1.id,

coalesce(t1.msg1, t2.msg1, t3.msg1) as msg1, coalesce(t1.msg2, t2.msg2, t3.msg2) as msg2, coalesce(t1.msg3, t2.msg3, t3.msg3) as msg3 from temp t1

left join temp t2 on t1.id = t2.id and t2.rownum=2 left join temp t3 on t1.id = t3.id and t3.rownum=3 where t1.rownum = 1

(63)

Further Information

• Regular Expression Tester

• https://regex101.com/

• Using DB2 UDB OLAP functions

• http://www.ibm.com/developerworks/data/library/techarticle/dm- 0401kuznetsov/

• Exploring the rich world of SQL OLAP functions - Moving Averages by George Baklarz

• http://www.idug.org/p/bl/et/blogaid=427

• Stackoverflow

• http://stackoverflow.com

(64)

Michael Tiefenbacher

ids-System GmbH

[email protected]

C7: SQL in Action – the Problem Solver

Please fill out your session evaluation before leaving!