Skip to Content

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 is
the 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 disk
space depends on the space which is left on each index page and
whether the two extra bytes per RID fit into the page or not. Also,
in reality your indexes might be fragmented. Doing a reorg will
result in smaller indexes in this situation, despite the fact that
large RIDs are enabled. For this example I made sure that the
indexes on BALDAT are fine.

Let’s go to step three and do the actual conversion. We have two
options: if we like to enable just large RIDs it is enough to
reorganize the indexes only. This is definitely faster than a
complete table reorg and less temporary disk space is required
(which is important for large tables). To enable large RIDs and
large slots a complete table reorg is necessary. I will try the
first 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 the
indexes grew by 594816 – 576096 = 18720 KBytes. This comes close to
our estimation above (17653), probably some page splits occurred
during 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.

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply