Feature: Larger Record Identifiers (LRIDs)
Record Identifiers (sometimes also called Row Identifiers or
RIDs) are used within indexes to address records. You may think of
a RID as 'pointer' to the actual table data. If during query
processing an index scan occurs, the immediate result of this scan
is a set of record identifiers. By following these RIDs the DBMS
can retrieve the records very efficiently. To do this two things
must be known: in which data page is the record located and where
in the page is it. We call the place inside the page where a record
resides a 'slot'.
Table Size Limit Considerations
Up to DB2 V8 a RID consisted of 4 bytes: 3 bytes to address the
page and 1 byte to address the slot. Three bytes are 24 bits, which
means that a RID can point to 224 = 16.777.216 pages.
Consequently, in a 4K tablespace a single table can only grow up to
4 KByte * 16.277.216 = 64 GByte. In a 8K tablespace, this table
size limit doubles to 128 GByte and so on. The table below
summarizes the size restrictions.
| Tablespace page size | Max. table size in DB2 V8 | Max. table size in DB2 V9 |
| 4 KByte | 64 GByte | 2048 GByte |
| 8 KByte | 128 GByte | 4096 GByte |
| 16 KByte | 256 GByte | 8192 GByte |
| 32 KByte | 512 GByte | 16384 GByte |
In SAP systems most tablespaces have now a page size of 16K. But
occasionally there are tables which need to grow beyond 256 GByte.
To circumvent this table size limit you had to move the table to a
tablespace with a larger page size or partition the table using the
DPF (Database Partitioning Feature).
DB2 Version 9 overcomes these table size limits by extending the
size of a record identifier from 4 to 6 bytes. Now 4 bytes are used
to address the page and 2 bytes are available for the slot number
(the size of these new RIDs is also characterized by '4+2' in
comparison to the pre-V9 '3+1' schema). Let's do the calculation
again with these larger RIDs. 4 bytes are 32 bits which results in
232 = 429.49.67.296 possible pages. In a 16K tablespace
this would mean that a table can grow up to 16 KByte *
429.49.67.296 = 64 TByte. Wow!
Unfortunately another limit comes into the game here: the
tablespace size limit. Tablespaces of type 'regular' can hold only
up to 16.777.215 pages. If the tablespace is of type 'large' this
number increases 32 times to 536.870.911 pages. For a 'large' 16K
tablespace we therefore calculate 16 KByte * 536.870.911 = 8192
GByte (see table above). A single table in a 16K tablespace can
grow in V9 to nearly 8 TByte without a need for partitioning.
That's still a huge improvement!
A word about slots. The 3+1 RID schema used in V8 offers one
byte to address the slot which means that 28 = 256
records could be placed within one page. In V9 we have two bytes,
but one of the bits is not used. Therefore 215 = 32.768
records could be placed in theory in one page. Only tables with a
small record length - compared to the tablespace page size - will
benefit from this larger slot improvement.
LRIDs@SAP
We at SAP are very much in favour of larger RIDs. New SAP
installations starting with the forthcoming SAP Netweaver release will set the type of
all tablespaces to 'large' by default. Also, all tables in the
forthcoming releases will use by default large RIDs and large
slots. But what can you do if you migrate from DB2 V8 to V9 and you
would like to use this new feature? Let's see an example.
A LRID example
I'm using a Netweaver 04 SR1 SAP system (with a 6.40 Basis and a
DB2 V9) here, the SID is 'MIJ'. Let us examine the table 'BALDAT'
which holds data for the SAP application log.
db2 =>
SELECT data_object_l_size, data_object_p_size, index_object_l_size, index_object_p_size
FROM TABLE(ADMIN_GET_TAB_INFO('SAPMIJ' , 'BALDAT')) AS X
DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE
-
-
-
-
4451840 4451840 576096 576096
db2 => SELECT large_rids, large_slots FROM TABLE(ADMIN_GET_TAB_INFO('SAPMIJ', 'BALDAT')) AS X
LARGE_RIDS LARGE_SLOTS
-
-
N N
Using the new UDF ADMIN_GET_TAB_INFO (see my last New Features in DB2 UDB V9 - Part 1
for details) it is easy to find out that the table size is about 4
GByte. Large RIDs and large slots are not enabled. Let's assume
that you observe a steady growth of this table in your system. The
first thing you would do is to check if you can avoid this growth
by deleting or archiving data from this table (for the application
log check e.g. SAP note 195157). Let's say you checked that and you
decided to enable large RIDs for this table. Our (very detailed)
action plan look like this:
- Increase the tablespace size limit of the corresponding data
and index tablespace.
- Estimate how much space is needed additionally.
- Enable large RIDs and/or large slots by reorganizing only the
indexes of the table or the complete table.
- Check the result.
An easy way to find out the names of the required tablespaces isthe following statement:
db2 =>
SELECT CAST(tbspace AS VARCHAR(20)), CAST(index_tbspace AS VARCHAR(20))
FROM syscat.tables WHERE tabschema = 'SAPMIJ' AND tabname = 'BALDAT'
1 2
-
-
MIJ#BTABD MIJ#BTABI
SELECT numrids/512 FROM syscat.indexes WHERE tabschema = 'SAPMIJ' AND tabname = 'BALDAT'
1
-
17653
Note that this is just a rough estimation. The actual required diskspace depends on the space which is left on each index page andwhether the two extra bytes per RID fit into the page or not. Also,in reality your indexes might be fragmented. Doing a reorg willresult in smaller indexes in this situation, despite the fact thatlarge RIDs are enabled. For this example I made sure that theindexes on BALDAT are fine. Let's go to step three and do the actual conversion. We have twooptions: if we like to enable just large RIDs it is enough toreorganize the indexes only. This is definitely faster than acomplete table reorg and less temporary disk space is required(which is important for large tables). To enable large RIDs andlarge slots a complete table reorg is necessary. I will try thefirst option here:
db2 => REORG INDEXES ALL FOR TABLE sapmij.baldat
DB20000I The REORG command completed successfully.
Note that it is possible to add the 'ALLOW WRITE ACCESS' option to this REORG command which enables users to carry out read and write operations during the index reorg on the specified table. See the DB2 Command Reference for details and restrictions about the various REORG options. Now it's time to check the result and prove our size estimation.
db2 =>
SELECT large_rids, large_slots FROM TABLE(ADMIN_GET_TAB_INFO('SAPMIJ', 'BALDAT')) AS X
LARGE_RIDS LARGE_SLOTS
-
-
Y P
db2 => SELECT data_object_l_size, data_object_p_size, index_object_l_size, index_object_p_size
FROM TABLE(ADMIN_GET_TAB_INFO('SAPMIJ' , 'BALDAT')) AS X
DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE
-
-
-
-
4451840 4451840 594816 594816
Large RIDs are in place now, the usage of large slots is 'Pending'(which is ok). The table size is the same (as expected) and theindexes grew by 594816 - 576096 = 18720 KBytes. This comes close toour estimation above (17653), probably some page splits occurredduring the index reorg.
One more hint if you choose the option of a complete table reorg.
The reorg creates a temporary copy of the table. As we don't want
to have this copy in the tablespace of our table (which would
increase the high watermark considerably) it is a good idea to
specify a temporary tablespace for the reorganization with the USE
clause. This temporary tablespace must have the same page size as
the tablespace in which the table which is being reorganized is
located. A suitable reorg command for our example table would be
'REORG TABLE sapmij.baldat USE psaptemp16'.
Conclusion
Large row identifiers are an essential new feature in DB2 V9.
Forthcoming SAP releases will employ the usage of LRIDs by default.
If you migrate from an older version of DB2, large RIDs are not
enabled by default. With a few manual steps this can be
accomplished for single tables after the migration. A reorg of the
indexes or a complete table reorg is required. Enabling large RIDs
results in a small space penalty for all affected indexes.
