Optimization Guidelines - The Easy way

To use this, there is one very important step, which is:

 ===> set the registry DB2_OPTPROFILE to YES.

Interestingly, this registry is not visible in the 'db2set -lr' output.

Once this is done, you are good to go. All the available 'guidelines' you have are available in a section of the online documentation called 'Current Optimization Schema', which is in XML format. For the example here, I'm going to use the guideline the 'easy way'.

Let's say we have a table M1 and M2, both with columns C1 and C2. We want to join them M1.C1 = M2.C1 using NL join. Let's jump straight into the usage:


SELECT <something>
from
M2 M2 JOIN M1 M1
ON ( M1.C1 = M2.C1)
/*<OPTGUIDELINES>
<NLJOIN>
<IXSCAN TABLE="M1"/>
<TBSCAN TABLE="M2"/>
</NLJOIN>
</OPTGUIDELINES>*/


Note that you start the guideline at the *end* of your statement with /*<OPTGUIDELINES>, and end the guidelines with </OPTGUIDELINES>*/. Anything in between these tags are your guidelins to the database. In this example, I instructed the database to use NL join for the join of table M1 and M2, using index scan (IXSCAN) for table M1, and table scan (TBSCAN) for table M2.

Personally, I use it to understand the WHAT-IF scenario if the execution plan changes in certain manner. This short-cut way to specify the guidelines makes testing very straightforward.

Datatype Space Usage: SMALLINT, BIGINT, etc

When a datatype (such as BIGINT) is chosen for a column, is space being allocated based on the 'value' , or is it a fixed allocation regardless of the value?

This is what I mean:

If a value of '5' being insert into the table, does it consume the same amount of space compared to value '9876543' ? Or does a '5' occupy less space than '9876543'? 

Let's find out. First, we create a single column table with type BIGINT. Then we insert roughly 150000 rows of value '1' into it.

This is the space used:

TAB             NPAGES

--------------- --------------------

BIGINT                           771

Let's repeat this test using value '99999999' instead. This is the space used:

TAB             NPAGES

--------------- --------------------

BIGINT                           771

That suggests there is no difference. 

That also means, if you know the range of the values ( number type, that is) for the column, you should pick the smallest possible datatype that would fit the range.

Below is a table showing the range for each numerical type:

	Limit
Smallest SMALLINT value	-32 768
Largest SMALLINT value	+32 767
Smallest INTEGER value	-2 147 483 648
Largest INTEGER value	+2 147 483 647
Smallest BIGINT value	-9 223 372 036 854 775 808
Largest BIGINT value	+9 223 372 036 854 775 807
Largest decimal precision	31
Maximum exponent (Emax) for REAL values	38
Smallest REAL value	-3.402E+38
Largest REAL value	+3.402E+38
Minimum exponent (Emin) for REAL values	-37
Smallest positive REAL value	+1.175E-37
Largest negative REAL value	-1.175E-37
Maximum exponent (Emax) for DOUBLE values	308
Smallest DOUBLE value	-1.79769E+308
Largest DOUBLE value	+1.79769E+308
Minimum exponent (Emin) for DOUBLE values	-307
Smallest positive DOUBLE value	+2.225E-307
Largest negative DOUBLE value	-2.225E-307
Maximum exponent (Emax) for DECFLOAT(16) values	384
Smallest DECFLOAT(16) value1	-9.999999999999999E+384
Largest DECFLOAT(16) value	9.999999999999999E+384
Minimum exponent (Emin) for DECFLOAT(16) values	-383
Smallest positive DECFLOAT(16) value	1.000000000000000E-383
Largest negative DECFLOAT(16) value	-1.000000000000000E-383
Maximum exponent (Emax) for DECFLOAT(34) values	6144
Smallest DECFLOAT(34) value1	-9.999999999999999999999999999999999E+6144
Largest DECFLOAT(34) value	9.999999999999999999999999999999999E+6144
Minimum exponent (Emin) for DECFLOAT(34) values	-6143
Smallest positive DECFLOAT(34) value	1.000000000000000000000000000000000E-6143
Largest negative DECFLOAT(34) value	-1.000000000000000000000000000000000E-6143