Friday, September 20, 2013

nested SDD

SDD (Statement driven development) is a powerfull productivity weapon.

Until now minuteproject SDD configuration was limited to a single statement.
This is enough when you want to query to display a list or graph or even call a store proc.

But often your business need is make of nested action. The output of some query serves as the input for other actions.
This is this capacity to handle multiple successive actions based on statement that is called nested SDD.

Something concrete


Imagine the following scenario
I have DB connections that are stucked and I want to kill them. How I want to do that is via a web application.
And I want to have this application without writing a single LOC and within 1 minute...
Here is the kind of productivity challenge Minuteproject is bound to solve with nested SDD.

Decomposition of the scenario

Infrastructure

Oracle DB.

Queries

I have DB connections that are stucked and I want to kill them.
http://appsdbanew.wordpress.com/2007/11/05/how-to-find-blocking-session-and-kill-the-session-from-database/
It can be reduced in writing 2 queries:

Retrieve the connections (and stucked ones)
select process,sid, serial#, blocking_session from v$session where blocking_session is not null

Kill collections
alter system kill session '#SID,#SERIAL' immediate

Nesting

The output of the first query (sid and serial#) serves as input of the second.


Minuteproject

Those two queries show be enough to get a application in Primefaces for quite handy for the help desked.