CREATE TABLE BANKROLL_SDEPO (
  ID_BANKROLL	INTEGER	NOT NULL,
  ID_PERSON_FUND	INTEGER	NOT NULL,
  ID_ALLOC_TIME	INTEGER	NOT NULL	DEFAULT 1,
  VARIANT_DATE	DATE	NOT NULL	DEFAULT '0001-01-01',
  BOOK_DATE	DATE	NOT NULL,
  QUANTITY_START	DECIMAL(31, 14)	NOT NULL,
  DELTA_IN	DECIMAL(31, 14)	NOT NULL	DEFAULT 0,
  DELTA_OUT	DECIMAL(31, 14)	NOT NULL	DEFAULT 0,
  DELTA_REV	DECIMAL(31, 14)
) in userspace1;

CREATE INDEX BANKROLL_SDEPO01
  ON BANKROLL_SDEPO
    ( ID_BANKROLL ASC, BOOK_DATE ASC, ID_PERSON_FUND ASC, ID_ALLOC_TIME ASC, VARIANT_DATE ASC );

alter table BANKROLL_SDEPO volatile;

CREATE OR REPLACE VIEW VBANKROLL_SDEPO
    ( ID_PERSON_FUND, ID_ALLOC_TIME, ID_BANKROLL, QUANTITY_START, DELTA_IN,
    DELTA_OUT, DELTA_REV, QUANTITY_END, START_DATE, END_DATE,
    VARIANT_DATE )
AS
select 
t1.ID_PERSON_FUND, t1.ID_ALLOC_TIME, t1.ID_BANKROLL, t1.QUANTITY_START, t1.DELTA_IN, t1.DELTA_OUT, t1.DELTA_REV
, t1.QUANTITY_START + t1.DELTA_IN - t1.DELTA_OUT + coalesce(t1.DELTA_REV, 0), t1.BOOK_DATE
, coalesce(max(t1.BOOK_DATE) over (partition by t1.ID_BANKROLL, t1.ID_ALLOC_TIME, t1.VARIANT_DATE, t1.ID_PERSON_FUND order by t1.BOOK_DATE ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - 1 DAY, nullif(t1.VARIANT_DATE, '0001-01-01'), '9999-01-01'), t1.VARIANT_DATE 
from BANKROLL_SDEPO t1;

explain all for 
select * from VBANKROLL_SDEPO tTotal 
where tTotal.ID_PERSON_FUND = ?
and tTotal.ID_BANKROLL = ?
  and tTotal.ID_ALLOC_TIME = ?
  and tTotal.VARIANT_DATE = ?;

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



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

DB2_VERSION:       09.07.6
SOURCE_NAME:       SQLC2H23
SOURCE_SCHEMA:     NULLID  
SOURCE_VERSION:      
EXPLAIN_TIME:      2013-04-09-09.34.08.055001
EXPLAIN_REQUESTER: MARK_B  

Database Context:
----------------
	Parallelism:          None
	CPU Speed:            1.417033e-007
	Comm Speed:           0
	Buffer Pool size:     3000
	Sort Heap size:       256
	Database Heap size:   600
	Lock List size:       4096
	Maximum Lock List:    22
	Average Applications: 1
	Locks Available:      28835

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 VBANKROLL_SDEPO tTotal 
where tTotal.ID_PERSON_FUND = ? and tTotal.ID_BANKROLL = ? and 
        tTotal.ID_ALLOC_TIME = ? and tTotal.VARIANT_DATE = ?


Optimized Statement:
-------------------
SELECT ? AS "ID_PERSON_FUND", ? AS "ID_ALLOC_TIME", ? AS "ID_BANKROLL", 
        Q3.QUANTITY_START AS "QUANTITY_START", Q3.DELTA_IN AS "DELTA_IN", 
        Q3.DELTA_OUT AS "DELTA_OUT", Q3.DELTA_REV AS "DELTA_REV", Q3.$C7 AS 
        "QUANTITY_END", Q3.START_DATE AS "START_DATE", COALESCE((Q3.$C10 - 1 
        DAYS), 
CASE 
WHEN (? = '01.01.0001') 
THEN NULL 
ELSE ? END , '01.01.9999') AS "END_DATE", ? AS "VARIANT_DATE" 
FROM 
   (SELECT Q2.ID_PERSON_FUND, Q2.ID_ALLOC_TIME, Q2.ID_BANKROLL, 
           Q2.QUANTITY_START, Q2.DELTA_IN, Q2.DELTA_OUT, Q2.DELTA_REV, Q2.$C7, 
           Q2.START_DATE, Q2.VARIANT_DATE, MAX(Q2.START_DATE) OVER (PARTITION 
           BY Q2.ID_BANKROLL, Q2.ID_ALLOC_TIME, Q2.VARIANT_DATE, 
           Q2.ID_PERSON_FUND ORDER BY Q2.START_DATE ROWS BETWEEN 1 FOLLOWING 
           AND 1 FOLLOWING) 
   FROM 
      (SELECT Q1.ID_PERSON_FUND, Q1.ID_ALLOC_TIME, Q1.ID_BANKROLL, 
              Q1.QUANTITY_START, Q1.DELTA_IN, Q1.DELTA_OUT, Q1.DELTA_REV, 
              (((Q1.QUANTITY_START + Q1.DELTA_IN) - Q1.DELTA_OUT) + 
              COALESCE(Q1.DELTA_REV, +00000000000000000.00000000000000)), 
              Q1.BOOK_DATE, Q1.VARIANT_DATE 
      FROM MARK_B.BANKROLL_SDEPO AS Q1) AS Q2) AS Q3 
WHERE (Q3.VARIANT_DATE = ?) AND (Q3.ID_ALLOC_TIME = ?) AND (Q3.ID_BANKROLL = 
        ?) AND (Q3.ID_PERSON_FUND = ?)

Access Plan:
-----------
	Total Cost: 		0.00715261
	Query Degree:		1

              Rows 
             RETURN
             (   1)
              Cost 
               I/O 
               |
                0 
             FILTER
             (   2)
           0.00708956 
                0 
               |
                0 
             TBSCAN
             (   3)
            0.0063561 
                0 
               |
                0 
             SORT  
             (   4)
           0.00617373 
                0 
               |
                0 
             FETCH 
             (   5)
           0.00579113 
                0 
          /----+----\
         0             0 
      IXSCAN    TABLE: MARK_B  
      (   6)    BANKROLL_SDEPO
    0.00566218        Q1
         0 
        |
         0 
  INDEX: MARK_B  
 BANKROLL_SDEPO01
        Q1