Hi,
I have a question on index pages split in db2.
For example,lets say a 4k index page has following entries in it,
1
2
3
8
9
10

and then i insert values 4,5,6 into the table for this key.
Would index page splits happen in DB2(LUW), so after insertion physical allocation looks like this:
page1
------
1
2
3

page2
------
4
5
6
8
9
10

or db2 re-adjusts the index free space in the page during insertion and inserts these records in the same page.
Please help me understand this concept.
I am using db2 v9.5, v9.7 LUW.
How does db2 manage this scenario in db2 z/OS.

Thanks.

New index keys are placed on the same page if there is still enough space. Splits only occur if you run out of space. So in your example, no index leaf page split is done because there are just 10 index entries.

Pretty much all database systems handle things that way. The reason is quite simple: adding another page to the index introduces more overhead because additional I/O may be needed. Furthermore, space utilization would degrade. Doing an in-place reorg and shuffling the index entries around is a lot cheaper in the long run.

Thanks Stolze.

Also, see PAGE SPLIT .... options on the create index statement (LUW)