create table part_t1 (id int not null, v varchar( 10 ), d date not null, constraint t1_c check(d<'2010-11-01')) in userspace1;
create table part_t2 (id int not null, v varchar( 10 ), d date not null, constraint t2_c check(d between '2010-11-01' and '2010-11-30')) in userspace1;
create table part_t3 (id int not null, v varchar( 10 ), d date not null, constraint t3_c check(d>'2010-11-30')) in userspace1;
create view  part_v as 
select id, v, d from part_t1
  union all 
select id, v, d from part_t2
  union all 
select id, v, d from part_t3
with row movement;
create sequence part_s;

insert into part_v (id, v, d)
select nextval for part_s, v, date(d)
from table(values
  ('row1', '2010-01-01')
, ('row2', '2010-11-05')
, ('row3', '2010-12-05')) t(v, d);

select * from part_v;

ID          V          D         
----------- ---------- ----------
           1  row1        01 . 01 . 2010 
           2  row2        05 . 11 . 2010 
           3  row3        05 . 12 . 2010 

select * from part_t1;

ID          V          D         
----------- ---------- ----------
           1  row1        01 . 01 . 2010 

select * from part_t2;

ID          V          D         
----------- ---------- ----------
           2  row2        05 . 11 . 2010 

select * from part_t3;

ID          V          D         
----------- ---------- ----------
           3  row3        05 . 12 . 2010 

update part_v set d='2010-11-04' where id= 1 ;

select * from part_t1;

ID          V          D         
----------- ---------- ----------

select * from part_t2;

ID          V          D         
----------- ---------- ----------
           2  row2        05 . 11 . 2010 
           1  row1        04 . 11 . 2010 

DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 		09.05.5
SOURCE_NAME: 		SQLC2G15
SOURCE_SCHEMA: 		NULLID  
SOURCE_VERSION: 	
EXPLAIN_TIME: 		2010-11-06-17.04.23.984000 
EXPLAIN_REQUESTER: 	BARIN_M 

Database Context:
----------------
	Parallelism: 		None
	CPU Speed: 		1,495757e-007
	Comm Speed: 		100
	Buffer Pool size: 	1250
	Sort Heap size: 	256
	Database Heap size: 	600
	Lock List size: 	50
	Maximum Lock List: 	22
	Average Applications: 	1
	Locks Available: 	935

Package Context:
---------------
	SQL Type: 		Dynamic
	Optimization Level: 	5
	Blocking: 		Block All Cursors
	Isolation Level: 	Cursor Stability



---------------- STATEMENT 1  SECTION 203 ----------------
	QUERYNO: 		1
	QUERYTAG: 		                    
	Statement Type: 	Select
	Updatable: 		No
	Deletable: 		No
	Query Degree: 		1

Original Statement:
------------------
select * 
from part_v 
where d between '2010-01-01' and '2010-04-04'


Optimized Statement:
-------------------
SELECT Q1.ID AS "ID", Q1.V AS "V", Q1.D AS "D" 
FROM BARIN_M.PART_T1 AS Q1 
WHERE ('01.01.2010' <= Q1.D) AND (Q1.D <= '04.04.2010')

Access Plan:
-----------
	Total Cost: 		7,56822
	Query Degree:		1

      Rows 
     RETURN
     (   1)
      Cost 
       I/O 
       |
       0,1 
     TBSCAN
     (   2)
     7,56822 
        1 
       |
        1 
 TABLE: BARIN_M 
     PART_T1
       Q1



Extended Diagnostic Information:
--------------------------------

Diagnostic Identifier: 	1
Diagnostic Details: 	EXP0020W  Table has no statistics.  The table
			"BARIN_M "."PART_T1" has not had runstats run on it.
			 This may result in a sub-optimal access plan and
			poor performance.

Plan Details:
-------------


	1) RETURN: (Return Result)
		Cumulative Total Cost: 		7,56822
		Cumulative CPU Cost: 		54980,3
		Cumulative I/O Cost: 		1
		Cumulative Re-Total Cost: 	0,000656238
		Cumulative Re-CPU Cost: 	4387,33
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	7,56797
		Estimated Bufferpool Buffers: 	1

		Arguments:
		---------
		BLDLEVEL: (Build level)
			DB2 v9.5.500.784 : s091123
		HEAPUSE : (Maximum Statement Heap Usage)
			80 Pages
		PREPTIME: (Statement prepare time)
			        58 milliseconds
		STMTHEAP: (Statement heap size)
			2048

		Input Streams:
		-------------
			2) From Operator #2

				Estimated number of rows: 	0,1
				Number of columns: 		3
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.D+Q2.V+Q2.ID


	2) TBSCAN: (Table Scan)
		Cumulative Total Cost: 		7,56822
		Cumulative CPU Cost: 		54980,3
		Cumulative I/O Cost: 		1
		Cumulative Re-Total Cost: 	0,000656238
		Cumulative Re-CPU Cost: 	4387,33
		Cumulative Re-I/O Cost: 	0
		Cumulative First Row Cost: 	7,56797
		Estimated Bufferpool Buffers: 	1

		Arguments:
		---------
		MAXPAGES: (Maximum pages for prefetch)
			ALL
		PREFETCH: (Type of Prefetch)
			NONE
		ROWLOCK : (Row Lock intent)
			NEXT KEY SHARE
		SCANDIR : (Scan Direction)
			FORWARD
		SKIP_DEL: (Skip Deleted Rows)
			TRUE
		SKIP_INS: (Skip Inserted Rows)
			TRUE
		TABLOCK : (Table Lock intent)
			INTENT SHARE
		TBISOLVL: (Table access Isolation Level)
			CURSOR STABILITY

		Predicates:
		----------
		2) Sargable Predicate
			Comparison Operator: 		Less Than or Equal (<=)
			Subquery Input Required: 	No
			Filter Factor: 			0,333333

			Predicate Text:
			--------------
			('01.01.2010' <= Q1.D)

		3) Sargable Predicate
			Comparison Operator: 		Less Than or Equal (<=)
			Subquery Input Required: 	No
			Filter Factor: 			0,333333

			Predicate Text:
			--------------
			(Q1.D <= '04.04.2010')


		Input Streams:
		-------------
			1) From Object BARIN_M.PART_T1

				Estimated number of rows: 	1
				Number of columns: 		4
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q1.$RID$+Q1.D+Q1.V+Q1.ID


		Output Streams:
		--------------
			2) To Operator #1

				Estimated number of rows: 	0,1
				Number of columns: 		3
				Subquery predicate ID: 		Not Applicable

				Column Names:
				------------
				+Q2.D+Q2.V+Q2.ID


Objects Used in Access Plan:
---------------------------

	Schema: BARIN_M 
	Name: 	PART_T2
	Type: 	Table (reference only)

	Schema: BARIN_M 
	Name: 	PART_T3
	Type: 	Table (reference only)

	Schema: BARIN_M 
	Name: 	PART_V
	Type: 	View (reference only)

	Schema: BARIN_M 
	Name: 	PART_T1
	Type: 	Table
			Time of creation: 		2010-11-06-10.22.52.390001
			Last statistics update:
			Number of columns: 		3
			Number of rows: 		1
			Width of rows: 			24
			Number of buffer pool pages: 	1
			Number of data partitions: 	1
			Distinct row values: 		No
			Tablespace name: 		USERSPACE1        
			Tablespace overhead: 		7,500000
			Tablespace transfer rate: 	0,060000
			Source for statistics: 		Single Node
			Prefetch page count: 		32
			Container extent page count: 	32
			Table overflow record count: 	0
			Table Active Blocks: 		-1
			Average Row Compression Ratio: 	-1
			Percentage Rows Compressed: 	-1
			Average Compressed Row Size: 	-1