You are here: Home > Knowledge Refreshers

KR editions 116 to 118

KR-116 (SEQ. PREFETCH):

Hi everyone,

Since KR was on leave for a couple of editions we're having a special edition today. 
In this edition we’ll take a look at indexed-access (but before that a little about seqential prefetch).
What is sequential pre-fetch?

  • It’s the process by which DB2 fills up the buffer by predicting the future! 

  • The DM will ask the BM to do a sequential prefetch. This is done when the optimizer decides that it needs to do sequential processing. 

  • For example: in a tablescan, the optimizer knows that it’ll need to read all the pages and so it’ll request the BM for a sequential prefetch. 

  • The advantage: in a single I/O operation the BM will pull out many pages into the buffer (instead of doing repeated I/Os to get those pages). This is generally used in table scans and also when scanning through index pages.

  • How many pages can it retrieve in one shot? (it depends on the page size and also the size of the buffer).

We know there are 2 ways DB2 can access data: using the index (index scan) and not using the index (tablescan).

Indexed-Access Methods:

The SQL we use should satisfy 2 conditions:
1.) One of the columns mentioned in the predicate should be listed in an available index.
2.) At least one of the predicates should be indexable (slightly different from the first point). Let’s say we have a column for curr_date which is indexed in the table. If we write an SQL as:
select * from table1 where curr_date = :ref-date + 10
DB2 might not use the index (predicate is the expression that you use within the where clause and if we use arithmetic operations within this then the index cannot be used – there are some other conditions also which prevent DB2 from using the index).
That's all for this edition and this week; have a great weekend.



KR-117 (INDEX ACCESS & COBOL CODING):

Hi everyone,

Continuing with indexed access...
The simplest form of indexed-access is the direct index lookup access. In this case if our table has an index with 3 columns then our SQL should also use the 3 columns in the predicate. DB2 would then perform the following steps:

  1. Compare the value in the predicate to the values in the root (of the index page). 

  2. Jump to the non-leaf pages (if any) 

  3. Next locate the leaf pages containing the required index value. 

  4. Jump to the leaf page and obtain the record ID. 

  5. With the record ID get the row from the table. 

(you can easily understand the process if you take a look at the index structure diagram which was sent in an earlier KR).

What if we use only the 2 columns of an index in the predicate while our index has 3 columns? DB2 cannot go for a direct lookup. It would have to choose between 2 other types of index scans (matching index scan or non-matching index scan). We'll take a look at this in our forthcoming editions.

To wrap up this edition, some tips in case you code COBOL in textpad and FTP the code to mainframes:

  • Ensure that no line exceeds 80 characters (because the PDS where you store this member would be declared with an LRECL of 80).

  • If you copy something from a Word document and paste in textpad then some special characters (like quotes), would appear in a different format (as magic characters - i.e. characters appearing to be blank but which are not actually blank - they will cause compile errors) in mainframes (this is because the quotes in MS Word is different from the normal quotes used in mainframes). So beware while copy pasting from other apps into Textpad. 

  • Convert all tabs to spaces (there is an option in textpad to do this) - a tab if not converted to spaces will also appear as a magic character in mainframes. 

  • After FTPing the code to mainframes, try the command F P'.' (this command will find all magic characters - i.e.. characters which mainframes cannot display - your code shouldn't have any magic characters). 

Well, that's all for this edition; suggestions and KRs welcome.



KR-118 (POINTS ABOUT SQLS):

Hi everyone,

Before continuing with the discussion on indexes, it would be good to know some interesting points about SQLs (this is KR-9 reloaded):

  • In an SQL ensure that you use equivalent data types (do not compare a char(6) with a char(5) column). Do not use a host variable whose precision is different from the precision of the column you are comparing with. Whenever DB2 has to convert data it may not use the index.

  • Do not assume the order of the output records returned by an SQL. Depending on the access path chosen by the optimizer the order could vary. Use ORDER BY clause if you want the output ordered. 
    BETWEEN is more efficient than using the combination of >= and <= since the optimizer chooses an efficient path when it encounters a BETWEEN. 

  • If possible use IN instead of LIKE.

  • If a % or _ appear at the beginning avoid using LIKE (because in this case DB2 will not use the index). For ex: avoid this statement - SELECT * from CUSTOMER where cust_name like ‘%on’.

  • Avoid using the <> (NOT operator) if you can make use of a >= or a <= operator. 
    For ex: Instead of SELECT * from STUDENTS where average <> 0
    Use SELECT * from STUDENTS where average > 0
    Of course this can be used if you are really sure that no student will have a negative average. 

  • It is important to know about the data in your tables to improve your SQLs.

 

Go back to the main contents page