KR editions 114 to 115

KR-114 (TABLESCAN INTRO):


Hi everyone, 
There are 2 ways in which DB2 can access data in tables: if it can use the index then it goes for some form of indexed access (index scan) otherwise it has to go through a tablescan.

There are 3 types of tablescans (depending on the type of tablespace). We have 3 types of tablespaces and so 3 types of table scans:

• Simple Tablespace Scan – scans all the pages of the tablespace because each page can have rows from multiple tables (this is one reason why if we use a simple tablespaces we wouldn’t want to have multiple tables).

• Segmented Tablespace Scan – (each segment in this tablespace will contain rows pertaining to a single table). Read only the segment pages which contain rows of that particular table.

• Partitioned Tablespace Scan – in this case all partitions store data of only one table. So in this scan DB2 will have to read through the partitions. 
  

A little SQL:

The WHERE clause and HAVING clause are functionally similar. The difference is that WHERE operates on detail rows while HAVING operates on aggregated rows. An SQL having both of these clauses would have WHERE followed by GROUP BY and finally HAVING (where will select a bunch of rows, group by will aggregate the rows and having will filter out from the aggregated rows).

Do you remember what is a DM, BM and RDS (they are things we learnt in training)?
That's all for this week...suggestions welcome,

KR-115 (RDS,DM,BM):


Hi everonye,

So, what is BM, DM and RDS?

• They are 3 components of the DBAS. What's a DBAS? DB2 comprises 3 smaller components: DBAS, SSAS and DDFS where DBAS is the database services, SSAS for system services and DDFS for distributed data facitilty. The AS stands for address space. 

• SSAS - provides the interface between DB2 and other subsytems (like CICS). 

• DBAS - this provides support to manipulate the database; execution of SQLs etc.

• The DBAS (the one which executes SQLs), has 3 parts:

                1) RDS: Relational Data System

                2)DM: Data Manager

                3) BM: Buffer Manager

• An SQL would first go to the RDS. The flow is: sql->RDS->DM->BM->DM->RDS->return result to requester 

• RDS will check the sql syntax, authorization and then optimize the SQL, decide the acccess path and then pass the optimized SQL to the DM. The DM works on a row level. It directs the BM to fetch data.

• When the BM has to retrieve data, it needs to first check the buffer; if the required data pages are already in the buffer then it needn't access the physical data sets. The pages are passed from the BM back to the DM and then to the RDS. The BM would return pages and not rows. The DM is the component which operates at the row level.

• The DM will apply some predicates (called stage 1 predicates), filter the data and pass it over to RDS. Now RDS will apply the stage 2 predicates on this filtered data.

• What are these predicates? Predicates are the filtering conditions we specify in our SQLs (the WHERE clause). Some predicates are simple while some are complex. The simple ones are Stage 1 predicates and others are Stage 2 predicates.

• In simple terms, the BM would return pages, the DM would apply simple predicates (like WHERE column = 'literal'), retrieve rows from the pages and pass the rows to the RDS. The RDS would then do the complex tasks- like applying complex predicates, sorting, ordering etc. and return the resultant rows.
  

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

Go back to the main contents page