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. […]
Continue ReadingDB2 Cost : index of articles
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 […]
Continue ReadingDB2 : Cost Calculation 2 : index only
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 […]
Continue ReadingDB 2 : Cost calculations 1 : Table Scan
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 […]
Continue ReadingDB2 Explain plan doesn’t exists
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 […]
Continue ReadingDB2 Row generator
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; […]
Continue ReadingDB2 commands
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 […]
Continue ReadingRecursive SQL in DB2
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 […]
Continue Reading