Numbers

Wolframalpha

sys_connect_by_path in sql server

Oracle sys_connect_by_path

create table v(o int, k varchar(20));

insert into v values(1,'Barbados');
insert into v values(1,'Rio De Janeiro');
insert into v values(1,'Kapkaupunki');
insert into v values(2,'Hanko');
insert into v values(2,'Helsinki');


select o, max(sys_connect_by_path(k, '/')) as scbp
from (
select o
, k
, row_number() over (partition by o order by k) as rn
from v
)
start with rn = 1
connect by prior rn = rn - 1 and prior o = o
group by o
;

1 /Barbados/Kapkaupunki/Rio De Janeiro
2 /Hanko/Helsinki


SQL Server:

create table dbo.v(o int, k varchar(20))

insert into dbo.v values(1,'Barbados')
insert into dbo.v values(1,'Rio De Janeiro')
insert into dbo.v values(1,'Kapkaupunki')
insert into dbo.v values(2,'Hanko')
insert into dbo.v values(2,'Helsinki')

with x as (
select o
, k
, 1 as level
, row_number() over (partition by o order by k) as rn
, k as scbp
from dbo.v
), cte as (
select o
, k
, level
, rn
, '/' + cast(k as varchar(max)) as scbp
from x
where rn = 1
union all
select x.o
, x.k
, cte.level + 1 as level
, x.rn
, cte.scbp + '/' + cast(x.scbp as varchar(max)) as scbp
from x inner join cte on x.rn - 1 = cte.rn and x.o = cte.o
)
select o, max(scbp)
from cte
group by o
;

1 /Barbados/Kapkaupunki/Rio De Janeiro
2 /Hanko/Helsinki

SUM over time

Time is continuous and time periods are modelled in SQL tables with validFrom validTill columns. I like to see continous pair stored as open ended, validFrom moment is included in the period, but validTill moment is excluded. This way it is possible to store the whole timeline.

Lets have a relation having such a pair

9.4.2009 10.4.2009 1

10.4.2009 12.4.2009 2

11.4.2009 14.4.2009 3

As one can see two last rows overlaps and for 11.4. there is all together 5 as a sum. Here is a SQL how to generate a sum for each moment of time.

with td as (
 select trunc(sysdate) + mod(level, 4) validFrom
      , trunc(sysdate) + mod(level, 8) * 2 validTill
      , level a
   from dual
connect by level < 4
)
select validFrom
     , nvl(lead(validFrom) over (order by validFrom)
         , to_date('22000101','yyyymmdd')) validTill
     , sumovertime
  from (
 select validFrom, max(sumover) sumovertime
   from (
  select a
   , case r when 1 then validFrom else validTill end validFrom
   , sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
   from td, (
  select rownum r from dual connect by level < 3
  )
 )
 group by validFrom
)
order by validFrom
;

9.4.2009 10.4.2009 1

10.4.2009 11.4.2009 2

11.4.2009 12.4.2009 5

12.4.2009 14.4.2009 3

14.4.2009 1.1.2200 0

Vacation

Just starting my winter holiday. Next month there are also days free from work. Easter is coming. The date of easter is moving around. Found an algorithm from wikipedia for counting the day of easter sunday. Here is an implementation of "Meeus/Jones/Butcher" algorithm in SQL:

select to_date((p+1)||'-'||n||'-'||vu,'dd-mm-yyyy')
from (
select vu, a, b, c, d, e, f, g, h, i, k, l, m, trunc((h+l-7*m+114)/31) as n, mod(h+l-7*m+114,31) as p
from (
select vu, a, b, c, d, e, f, g, h, i, k, l, trunc((a+11*h+22*l)/451) as m
from (
select vu, a, b, c, d, e, f, g, h, i, k, mod(32+2*e+2*i-h-k,7) as l
from (
select vu, a, b, c, d, e, f, g, mod(19*a+b-d-g+15,30) as h, trunc(c/4) as i, mod(c,4) as k
from (
select vu, a, b, c, d, e, f, trunc((b-f+1)/3) as g
from (
select vu, a, b, c, trunc(b/4) as d, mod(b,4) as e, trunc((b+8)/25) as f
from (
select vu, mod(vu,19) as a, trunc(vu/100) as b, mod(vu,100) as c
from (
select level+1999 vu
from dual connect by level <= 20 

) ) ) ) ) ) ) )

;

Idiocy

People are idiots, some more than others. At least people are idiots in different aspects of life and issues. A person is an idiot differently each day. At least I feel like that. Other days it is quite obvious to see things exists and other days you cannot figure out a simplest case. Well maybe it is not idiotism to be clueless about a thing that one has never figured out before. Most often it is really nice to be working with persons that are less idiots than me. At least in a issue that one tried to figure out and the other has a ready answer to.

I have set up a 10g data quard manually and tried to register the databases to Grid Control. 

Stand by instance seemed to be quite hard to get registered to the agent. I did not figure out that the standby node is in mount state and dbsnmp monitoring user is trying to reach the database as a normal user. Just change the monitoring user to connect to the database as sysdba and voila grid control is showing roles of the instances correctly. Creating a stand by instance with Grid Control handles that ok. 

This week I have had a pleasure to work with a person who could figure out thigs while problems arise and a oraganization that has scheduled HA evironment testing well before going to production. After setting up Data Guard environment we had plenty of time to try out different kinds of switchower and failover situations and there is still time in the project plan to try out even more complex situations. This possibility and capability should exist in every project having a HA environment. 

A week on the other side (part3 DB2)

Missing Oracle function based indexes. Got involved in a project having a DB2 database environment. There is a need for case insensitive predicate in a where clause. In Oracle it is possible to create function based index:

create table t(n number(8) primary key, last_name varchar(100));

insert into t (n,last_name)

select rownum, object_name

from all_objects;

select * from t where upper(last_name) = :ln;

Performs a full table scan to table t.

create index t_last_name_upper on t(upper(last_name));

select * from t where upper(last_name) = :ln;

And the index is used if the table is big enough.

In DB2 there is no such thing as a function based index. But in a article on IBM site there is described alternatives to do this. Generated columns and index extensions. Generated columns seems like a valid approach. Even though data is duplicated on a row. No need for triggers seems like a good thing and the original query do not need any modifications.

This was about the first time that I am using db2cc Control Center to do things in DB2 environment. Execution plans were found easily from Access Plan page. At least with a small table implementation and tests went ok. Allthough adding a column in a table was not so straight forward because the column was a GENERATED ALLWAYS AS column.

SET INTEGRITY FOR t OFF;

alter table t
add column last_name_u varchar(100)
GENERATED ALWAYS AS ( UPPER(last_name));

SET INTEGRITY FOR t IMMEDIATE CHECKED FORCE GENERATED;

create index t_lastname_upper_idx on t(last_name_u);


How much easier would that have been in Oracle 11g. Just add a virtual column...

alter table t add last_name_u as (upper(last_name));

create index t_last_name_upper on t(last_name_u);

select * from t where upper(last_name) = :ln;


Similarily no need to change the query. Index is used. And the column is virtual, no need for storage. The index extensions approach might be the way to avoid unneeded storing of the data in DB2.

Needed also reduce a size of column and got surpriced how often REORG TABLE is needed in DB2. Or did I miss something in the documentation.

A week on the other side (part2 SQLServer)

I had a pleasure to spend more than a hour installing SQLServer for evaluation purposes. Installed SQLServer 2008 for the first time. I have now 180 days to do test with AdventureWorks databases.

A couple weeks ago I went to see a presentation about SQLServer 2008 to Oracle specialists mainly presented by Marko Hotti. Nice overview about the product. There was a question from the audience about isolation levels in SQLServer. There is still a common understanding among Oracle specialist that readers block writers. There exists snapshot isolation level in SQLServer. It was presented allready in version 2005.