Index Why In the DB2 : Cost Calculation 2 : index only post I noticed something upsetting, well, at least it was upsetting to me. We looked at the explain plan for looking up 1 value by an index. The index had nlevel=3. So there is a root-node, some intermediate-nodes and finally finally some leaf-nodes. …
I started a series of articles concerning the cost of queries (and as a consequence concerning the choices the optimizer makes). This post is a index of these articles. 1 – DB2 : Cost calculations 1 : Table Scan 2 – DB2 : Cost Calculations 2 : Index Only 3 – DB2 : Cost Calculations …
Index Why indexes Searching through millions of rows can be the right thing to do (TBSCAN). It might be the only option available to get the data you need.But in a lot of cases we need smarter ways to get to our data. If you need only one row,it seems silly to process thousands of …
Index Why? When you supply DB2 with a query DB2 has to make some serious decisions: – Should indexes be utilized? – Which indexes are most suitable? – Which join operation should be used? (hash join/nested loop) – Is all data needed at once or are the first 10 rows as quick as possible (the …
Just a quick reminder: If you start a new database where you want to genrate some execution-plans you might run into the following : db2 => explain plan for select * from t1,t3 where t1.n1=t3.n1; DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During …
Sometimes you need a mechanism to generate rows. For example when you need to create a demonstration table . Recursive CTE’s (Common Table Expression) might be your friend WITH engine (rownum) AS (select 1 as rownum from sysibm.sysdummy1 union all select rownum + 1 AS rownum from engine where rownum<10) SELECT rownum FROM engine; …
Starting the database [db2inst1@bloemkool ~]$ db2start 01/11/2022 14:57:48 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [db2inst1@bloemkool ~]$ Stopping the database [db2inst1@bloemkool ~]$ db2stop 01/11/2022 14:58:43 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [db2inst1@bloemkool ~]$ Stopping the database when there are connections/applications in the database.At first …
Problem Playing 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. First step is understanding how to make the recursive SQL’s The setup: drop table relations; create table relations (id int, parent …