You are here: Home > Knowledge Refreshers

KR editions 119 to 122

KR-119 (DB2 MIS):

Hi everyone,

(Note: you’ll understand this edition better if you refresh yourself on the index structure – regd. root pages and leaf pages; the diagram would help) - this is KR-16 reloaded.
In types of indexed access we’ve already seen the direct look-up method (this is used by our optimizer when we specify the entire index key). It will just start from the root page and go down to the leaf page, retrieve the Record ID (RID) and then retrieve the row from the tablespace. 

What happens when we don’t specify the entire index key?

  • Let’s say we have a table with a 3 column index (project code, Activity code and date). This example table is a contrived one. 

  • If we write an SQL which has only the project no. and activity no. then the optimizer cannot make use of the direct look-up method (because the full key is not present). In this case it does a matching index scan (MIS). DB2 will try to locate the index leaf page containing rows with the col.1 and column2 combination of our SQL. For this particular combination there could be many rows in the leaf page and DB2 will have to retrieve all those rows. 

  • For ex.: SELECT * from PROJ_T where project_code=’olympics’ and act_code='coding’ 

  • DB2 will start at the root index page, go down to the leaf page containing this combination and then retrieve all rows containing this combination (since this combination could have multiple dates). 

For a matching index scan, the optimizer requires a starting point in the root page (i.e. we should specify the higher order column in the index key for the optimizer to use MIS). Why? Without this (in our example the higher-order key is the project_code) the optimizer won’t have a starting point in the root page. 

What if we don’t specify the higher order column in our SQL? 
Then DB2 will go for a non-matching index scan. More on this in the next edition.

Now you know why a direct look-up gives the results instantaneously while a matching index scan might take more time.

Well, that's all for this edition and the week; havea great weekend.

KR-120 (NMIS):

Hi everyone, 

Continuing where we left off…..What if we don’t specify the higher order column of the index in our SQL predicate?

DB2 will go for a non-matching index scan (NMIS) since it doesn’t have any starting point in the index structure (i.e. it can’t go from the root page to the leaf page as in matchis index scan). 
In NMIS, the optimizer will decide to scan thro the leaf pages one by one (note: this is part of the index structure. It is not scanning thro the tablespace). 

You might wonder what’s the advantage of scanning thro index leaf pages instead of the tablespace itself. Before going into that some interesting points to note. 

There are 2 types of I/Os that are involved in DB2: 
1.) Index I/O (where DB2 has to retrieve index pages).
2.) Table I/O (where DB2 will access the table to retrieve pages from the tablespace).
I/O operations in general consume time. So, any method which can reduce the number of I/Os is always preferred. 

NMIS can be useful in the following scenario:

  • If the data we want is part of the index then DB2 doesn’t need to even access the tablespace. For ex.: consider a table that has an index on the columns first_name, middle_name and last_name. 

  • If our SQL were:SELECT first_name from CUSTOMER where middle_name="harry" and last_name="smith" 
    then DB2 would use NMIS (since we haven’t specified the high level index column "first_name" in our where clause).

  • Now DB2 will scan thro the index leaf pages. But when it finds the matching rows in the index pages, it doesn’t need to do table I/Os to retrieve the whole record (because all we’ve asked for is the first_name alone which is stored in the index itself).

  • Scanning index pages is also faster than scanning thro tablespace pages because the index pages will be smaller than the table pages (this is called index-only access).

  • That's all for this edition; suggestions and KRs welcome...

KR-121 (EXPLAIN again):

Hi everyone,

Having covered substantial theory on indexes and scans, the results of the EXPLAIN command which we obtained earlier should now be more comprehendible.
Recap of what we did: 
We had a table with the index on colA, colB and colC and we wrote an SQL:

SELECT * FROM mytab.dummy_t
WHERE colB='05'
AND colC='20'
The result in PLAN_TABLE (using EXPLAIN) said that ACCESSTYPE is I and MATCHCOLS is 0.

Observations:
'I' stands for Indexed access - i.e. DB2 will make use of the index pages to retrieve the rows (and in our query colB and colC are 2 columns of the composite index).

  • MATCHCOLS indicates the number of index columns present in the SQL condition (i.e. the predicate) which DB2 can use to select the rows. So even though we've used 2 columns which are part of the index DB2 cannot make use of them. But then how is it indexed access?
    We then changed our SQL to:

    SELECT * FROM mytab.dummy_t
    WHERE colA='2005'
    AND colB='05'

  • Now the values in PLAN_TABLE for this SQL was: ACCESSTYPE = I and MATCHCOLS = 2 

  • The reason for the difference is that in the 2nd SQL we've used the higher order index column- the composite index is on the columns colA, colB and colC.

  • When MATCHCOLS has a value it denotes a matching-indexed acess and when MATCHCOLS=0 it means DB2 will use a non-matching indexed access. 


What we learnt from the theory?

  • Since the first SQL DB2 didn't have the higher order column of the index in the predicate DB2 didn't have a starting point in the index root page (it had to scan through the index leaf pages instead).

  • But in SQL 2, though the full index key hasn't been specified, the higher order columns are present and so it could use MIS (but limited to matching using the first 2 columns alone).

That's all for this edition....we'll have a break from DB2 this week; 
Did you know that we can put mainframes to sleep! (more in the next edition),

KR-122 (MAINFRAMES ASEEP):

Hi everyone,

A short KR tip contributed by Venkat

The command “TSO OPSWAIT n” waits for n seconds. Or you can also go to the TSO command area (option 6) and type OPSWAIT 5 (which would make mainframes sleep for 5 seconds! actually it'll just hold up your session for 5 seconds).

We can also specify the command as:
OPSWAIT FOR(mm:ss)
or
OPSWAIT mm:ss
which would cause a pause of mm minutes and ss seconds.

When would it be useful to ask mainframes to sleep like this? In a batch job, in case you want a specific time delay you could make use of this. The command can be executed in a JCL, to give the needed delay.

Sample JCL giving a delay of 1 minute and 10 seconds:

//SLEEP EXEC PGM=IKJEFT01 
//SYSPRINT DD SYSOUT=* 
//SYSTSPRT DD SYSOUT=* 
//SYSTSIN DD * 
OPSWAIT FOR(01:10) 
//* 

(since this is a TSO command we execute it in batch using IKJEFT01).

That's all for this edition and the week; have a great weekend;
Tips, suggestions and contributions welcome.

Go back to the main contents page