Problem<\/b>
\nPlaying around in DB2 I wanted to know how to create a hiearchical query. This because I wanted to create view for explain plans, a bit resembling the explain plans which oracle provides.<\/p>\n
First step is understanding how to make the recursive SQL’s<\/p>\n
The setup:<\/b><\/p>\n Just to be sure,it looks like:<\/p>\n What I would like to see is something resembling following:<\/p>\n First try: (Recursive) Common Table Expressions (r)CTE<\/b> This gives me the following result:<\/p>\n This is somewhat resembling what I want. However, the query shows the rows all per level. After some searchinf I came to know that this is calles \"Search Breadth First\", what I need is \"Search Dept First\".<\/p>\n Tried a lot of things but to no avail. But ...NO....No luck there.<\/p>\n Second try: Hiearchical SQL<\/b> This didn't work at first. We first need to tell DB2 that it can use also these type of features:<\/p>\n Since I'm on my development environment, I can stop and start the database whenever it suits me \ud83d\ude42<\/p>\n Following output was generated by running this query:<\/p>\n Exactly what I want. So at least we have a solution.<\/p>\n\r\ndrop table relations;\r\n\r\ncreate table relations (id int, parent int);\r\n\r\ninsert into relations values(0,NULL);\r\ninsert into relations values(1,0);\r\ninsert into relations values(2,1);\r\ninsert into relations values(3,1);\r\ninsert into relations values(4,3);\r\ninsert into relations values(5,0);\r\ninsert into relations values(6,5);\r\ninsert into relations values(7,5);\r\ninsert into relations values(8,6);\r\ninsert into relations values(9,7);\r\ninsert into relations values(10,0);\r\ninsert into relations values(11,1);\r\n\r\ncommit;\r\n<\/pre>\n
\r\nID PARENT \r\n----------- -----------\r\n 0 -\r\n 1 0\r\n 2 1\r\n 3 1\r\n 4 3\r\n 5 0\r\n 6 5\r\n 7 5\r\n 8 6\r\n 9 7\r\n 10 0\r\n 11 1\r\n<\/pre>\n
\r\nID Parent\r\n0 -\r\n1 0\r\n2 1\r\n3 1\r\n4 3\r\n11 1\r\n5 0\r\n6 5\r\n8 6\r\n7 5\r\n9 7\r\n10 0 \r\n<\/pre>\n
\nDB2 has a nice way to have recursion in r(CTE) let’s see if I can get that working:<\/p>\n\r\nwith recur(id, parent, level) as\r\n (\r\n select rel.id id, rel.parent parent, 1 level \r\n from relations rel\r\n where rel.id=0\r\n union all\r\n select rel.id, rel.parent, rec.level+1 \r\n from recur rec,\r\n relations rel\r\n where rec.id=rel.parent\r\n and rec.level<10\r\n )\r\nselect id, \r\n cast(lpad(parent, level*2, ' ') as varchar(20)) parent,\r\n level \r\nfrom recur;\r\n<\/pre>\n
\r\nID PARENT LEVEL \r\n----------- -------------------- -----------\r\n 0 - 1\r\n 1 0 2\r\n 5 0 2\r\n 10 0 2\r\n 2 1 3\r\n 3 1 3\r\n 11 1 3\r\n 6 5 3\r\n 7 5 3\r\n 4 3 4\r\n 8 6 4\r\n 9 7 4\r\n<\/pre>\n
\nOn the net I came across the \"Search Dept First\" clause. This should then look like:<\/p>\n\r\nwith recur(id, parent, level) as\r\n (\r\n select rel.id id, rel.parent parent, 1 level \r\n from relations rel\r\n where rel.id=0\r\n union all\r\n select rel.id, rel.parent, rec.level+1 \r\n from recur rec,\r\n relations rel\r\n where rec.id=rel.parent\r\n and rec.level<10\r\n ) search depth first by parent set ordering\r\nselect id, \r\n cast(lpad(parent, level*2, ' ') as varchar(20)) parent,\r\n level \r\nfrom recur\r\norder by ordering;\r\n<\/pre>\n
\nAnother approach is using tha naticve hiearchical capabilities from the SQL implemantation:<\/p>\n\r\nselect id, lpad(parent, level*2, ' ') parent, level\r\nfrom relations\r\nstart with id=0\r\nconnect by prior id=parent;\r\n<\/pre>\n
\r\ndb2set DB2_COMPATIBILITY_VECTOR=08\r\ndb2stop\r\ndb2start\r\n<\/pre>\n
\r\nID PARENT LEVEL \r\n----------- ---------------- -----------\r\n 0 - 1\r\n 1 0 2\r\n 2 1 3\r\n 3 1 3\r\n 4 3 4\r\n 11 1 3\r\n 5 0 2\r\n 6 5 3\r\n 8 6 4\r\n 7 5 3\r\n 9 7 4\r\n 10 0 2\r\n<\/pre>\n