Introduction

A record identifier (RID) is a reference to the location of a row in a table. You can use RIDs to:

• Describe on which data page records are located
• Describe where in a data page records are located

RIDs are divided into a page number followed by a slot number. Once the index is used to identify a RID, the RID is used to get to the correct data page and slot number within that page. Once a record is assigned a RID, it does not change until the table is reorganized.

When a table page is reorganized, embedded free space left on that page once a record is physically deleted is converted to usable free space. RIDs are redefined based on the movement of the records on a data page in order to take advantage of the usable free space.

RIDs do not apply to system-managed space (SMS) tablespaces.

Larger RIDs

What are larger RIDs?

Prior to DB2® for Linux, UNIX ®, and Windows® Version 8, the RID size was limited to four bytes in order to uniquely identify rows within the database: three for page numbers, and one for a slot number. The tablespace default data type was set to "REGULAR". DB2 9 supports larger RIDs, extending the previous tablespace size limit. The data object pool page was increased from three bytes to four bytes, and the slot identifier was increased from one byte to two bytes in DB2 9. The new tablespace default data type is set to "LARGE".

Extending the length of a RID, and thus the page number, allows a table to consist of more data pages. Also, a page can contain more records because the slot number can be r. Creating a tablespace as LARGE (or converting it to LARGE) will only enable the new feature, and no significant increase in storage occurs. In order for a table inside that tablespace to use this new space, you must perform a REORG operation on the indexes of the table. To use the new slots within a page, you must perform a REORG on the table.

Note: When you convert a SAP tablespace to LARGE, it is good practice to convert both data and index tablespaces to LARGE.

When a table is placed in a LARGE tablespace:

• The table can support more than 255 rows per data page, which improves space utilization on data pages.
• Indexes that are defined on the table require an additional two bytes per row entry, compared to indexes defined on tables residing in a regular tablespace.

The RID type (REGULAR or LARGE) stored in the index is determined by the type of tablespace that houses the base table (the table where the data is stored) RID type; it has nothing to do with the type of tablespace where the index resides. Both system temporary and user temporary tablespaces support large RIDs. For new systems, they are created as LARGE by default.

With the new change, each RID requires 8 bytes of memory in a single-partition environment and 16 bytes of memory in a partitioned database environment; the extra memory is needed for performance reasons.

Advantages of using larger RIDs

Creating a large tablespace (or converting a tablespace to LARGE) significantly increases its maximum size compared to that of previous releases.

Can my table benefit from large slots?

Run the following statement to see whether your table will benefit from this new feature:

SELECT TABSCHEMA, TABNAME, AVGROWSIZE FROM SYSCAT.TABLES

If the average row size (-2) for a table is smaller than the minimum record length for the page size used, there may be storage benefits to converting the tablespace to large and reorganizing the table to enable large slots.

Note: The value returned for AVGROWSIZE includes two bytes for the slot entry, but the minimum record length value in the above table doesn’t.

ADMINTABINFO and ADMIN_GET_TAB_INFO

You can use the ADMINTABINFO administrative view and the ADMIN_GET_TAB_INFO table function to query how a table is using the large RIDs feature. They contain information about the tables with the identifiers LARGE_RIDS and LARGE_SLOTS:

• LARGE_RIDS(CHAR(1): Indicates whether the table is using large row IDs (RIDs)
  
  • A "Y" value indicates that the table is using large RIDs.
  • An "N" value indicates that the table is not using large RIDs.
  • A "P" (pending) value indicates that the table supports large RIDs -- that is, the table is in a large tablespace -- but at least one of the table's indexes has not been reorganized or rebuilt yet. This means that the table is still using four-byte RIDs. You must either convert the table or index(es).
• LARGE_SLOTS(CHAR(1)): Indicates whether the table is using large slots (allowing for more than 255 rows per page)
  
  • A "Y'" value indicates that the table is using large slots.
  • A value of "N" indicates that the table is not using large slots.
  • A "P" (pending) value indicates that the table supports large slots -- that is, the table is in a large tablespace -- but no off-line table reorganization or table truncation operation has been performed on the table yet. It is still using a maximum of 255 rows per page.

The ADMIN_GET_TAB_INFO table function also contains the following identifiers:

• DATA_OBJECT_L_SIZE: Data object logical size, the amount of disk space logically allocated for the table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated for the table (for example, in the case of a logical table truncation).
• DATA_OBJECT_P_SIZE: Data object physical size, amount of disk space physically allocated for the table, reported in kilobytes.
• INDEX_OBJECT_L_SIZE: Index object logical size, amount of disk space logically allocated for the indexes defined on the table, reported in kilobytes. The logical size is the amount of space that the table knows about. It might be less than the amount of space physically allocated to hold index data for the table (for example, in the case of a logical table truncation).
• INDEX_OBJECT_P_SIZE: Index object physical size, amount of disk space physically allocated for the indexes defined on the table, reported in kilobytes.

Example 1: You can identify tables that are eligible to use large RIDs, but are not currently enabled by issuing the following statement:

SELECT TABNAME FROM SYSIBMADM.ADMINTABINFO 
	WHERE LARGE_RIDS=’P'

Example 2: After running the conversion and the REORG operation on a table, issue the following statement to ensure the table is using the new feature:

SELECT large_rids, large_slots FROM TABLE 
	(ADMIN_GET_TAB_INFO('<schema>', '<Table>')) AS X

The value should be "Y" for both if a full table REORG was performed.

Example 3: To monitor the increase in the size of the tables and indexes, issue the following statement before and after the conversion:

  SELECT TABNAME, TABSCHEMA, DATA_OBJECT__P_SIZE, INDEX_OBJECT_P_SIZE 
    FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO(“,”)) 
    AS T WHERE TABNAME=’<tablename>’

Implement larger RIDs

Requirements

To implement this new feature, the following conditions must be met:

• The database where the conversion will be implemented must be at DB2 9 or higher.
• The tablespace must be database managed (DMS) tablespaces. To check whether the tablespace is DMS, run the DB2 LIST TABLESPACES command. The output should look like this:

Tablespace ID = 10

Name = SAMPLE

Type = Database managed space

Contents = All permanent data. Regular tablespace

State = 0x0000

Detailed explanation: Normal
	

• It is recommended that you set the tablespace to auto-resize with an unlimited size value. To check whether the tablespace is auto-resizing, run the DB2 GET SNAPSHOT FOR TABLESPACES ON <SID> command. The output should look like this:

Tablespace name = SAMPLE

Tablespace type = Database managed space
  
Tablespace content type = All permanent data. Large tablespace
  
Tablespace page (bytes) = 4096
	
Tablespace extent size (pages) = 2
	
Automatic prefetch size enabled = Yes

Buffer pool ID currently in use = 1

Buffer pool ID next startup = 1
  
Using automatic storage = Yes

Auto-resize enabled = Yes
  
File system caching = Yes
  
Tablespace state = 0x'00000000'
	
Detailed explanation: Normal
	

Space considerations

The size of a RID is increased by two bytes in DB2 9, which in turn increases the size of the indexes. To get an estimation of the index growth for all indexes, issue the following as user db2<sid>:

SELECT SUM(numrids)/512 FROM syscat.indexes
WHERE tabschema = '<schema>' and tabname = '<LargeTable>'

Where <schema> is SAPR3 or SAP<SID> depending on the level of your system. Please note that during conversion some page splits might occur, which will lead to additional growth.

Using larger RIDs will increase the row size of your result sets from queries or positioned updates. If the row size nears the maximum row length limit for your system temporary tablespace with the largest page size, you may need to one with a larger page size. If your system temporary tablespace page size is 32 KB, you need to reduce the length of the information retrieved by your query or split your query. For more information on this, please refer to "Ensuring system temporary tablespaces page sizes meet requirements".

Convert tablespaces to LARGE

To enable this new feature, a tablespace must first be declared as LARGE. After this declaration, all tables that are created in this tablespace will automatically take advantage of this feature. For existing tables, please review the following sections on reorganizing indexes and reorganizing tables. It is considered a good practice to convert all tablespaces to LARGE after migrating to DB2 9. There is no additional space needed and minimal time is required for conversion. By default, all newly created tablespaces are created as LARGE. So, for new SAP installations on DB2 9, this step is not necessary.

Use the ALTER TABLESPACE command to run the conversion. You can use the command as follows:

% db2 ”ALTER TABLESPACE <tbsname> CONVERT TO LARGE”

To convert all existing tablespaces to LARGE, please use the following steps:

1. Log in as the instance owner (db2<sid>) and connect to the database.
2. Run the following command and pipe it to a file:

   db2 "SELECT 'ALTER TABLESPACE' || tbspace || ‘ CONVERT TO LARGE;’ AS TABSPACE FROM syscat.tablespaces WHERE tbspacetype = 'D' AND datatype = ‘A’ AND tbspace NOT LIKE ‘SYS%’” > convert.sql

   
   
3. The file convert.sql will look like this:
   

   … alter tablespace PSAPSOURCED convert to large; alter tablespace PSAPSOURCEI convert to large; alter tablespace PSAPSTABD convert to large; alter tablespace PSAPSTABI convert to large; alter tablespace PSAPUSER1D convert to large; alter tablespace PSAPUSER1I convert to large;

   
   
4. Execute the file convert.sql:
   

   %db2 –tvf convert.sql

   
   

Note:

• This new option cannot be combined with any other alter tablespace options.
• You can ignore the >SQL1237W and ADM6104W warnings.
• If the tablespace contains a partition for a range partitioned table, all partitions for the table must be in the same tablespace.
• The conversion usually runs for a few seconds, during which the tablespace and all its contents are offline (not available to users).
• The conversion also updates the SYSCAT.TABLESPACES, SYSCAT.TABLES catalog tables, scans SYSCAT.SATAPARTITIONS for data and long data in the tablespace, and scans SYSCAT.INDEXES for indexes in the tablespace.
• This operation cannot be reversed once committed.

Enable large page identifiers: Reorganize indexes only

As explained earlier, by reorganizing the indexes only, tables can take advantage of the larger RIDs feature and benefit from the page identifier increase from three bytes to four bytes. The advantage of this operation is that it can be done offline or online (without having to stop the SAP system), and it takes less time than full table reorganization. Both the data and index tablespaces (where the table and indexes reside) should be converted to LARGE before performing the REORG operation. Use the REORG command as follows:

% db2 "REORG INDEXES ALL FOR TABLE <schema>.<tabname>”

To convert indexes for all tables in a specific tablespace, you can do the following:

1. Log on as the instance owner (db2<sid>) and connect to the database.
2. Run the following command and pipe it to a file:
   

   db2 "SELECT 'REORG INDEXES ALL FOR TABLE ' || CHR(34) || RTRIM(tabschema) || CHR(34) || '.' || CHR(34) || tabname || CHR(34) || ' ;' FROM syscat.tables WHERE type = 'T' AND tbspace = '<tbsname> " | grep REORG > index_reorg.sql

   
   
3. The file index_reorg.sql looks like this:
   

   … REORG INDEXES ALL FOR TABLE "SAPR3"."J_3RFANKA" ; REORG INDEXES ALL FOR TABLE "SAPR3"."/SAPHT/MEST9LG" ; REORG INDEXES ALL FOR TABLE "SAPR3"."/SAPHT/MESZPARM" ; REORG INDEXES ALL FOR TABLE "SAPR3"."DB6IDENTIFIER" ; REORG INDEXES ALL FOR TABLE "SAPR3"."T5BPBS01" ;

   
   
4. Execute the file index_reorg.sql:
   

   %db2 –tvf index_reorg.sql

   
   

Note:

• The REORG command should be used without the CLEANUP ONLY clause.
• For partitioned tables, you must use the clause ALLOW NO ACCESS.
• To run the REORG command in online mode, add the clause ALLOW WRITE ACCESS.

Enable large page and large slot identifiers: Reorganize tables

To take full advantage of the larger RIDs feature (larger page ID and larger slot ID), you must perform a full table reorganization. After the reorganization, every data page can hold more than 255 records (provided they fit on one page). The number of records that fit on one data page depends on the size of a single record and the page size.

The REORG command is used as follows:

% db2 "REORG TABLE <schema>.<tabname>”

To convert all tables in a tablespace, you can do the following:

1. Make sure the SAP system is down.
2. Log on as the instance owner (db2<sid>) and connect to the database.
3. Run the following command and pipe it to a file:
   

   db2 "SELECT 'REORG TABLE ' || CHR(34) || RTRIM(tabschema) || CHR(34) || '.' || CHR(34) || tabname || CHR(34) || ' ;' FROM syscat.tables WHERE type = 'T' AND tbspace = '<tbsname> " | grep REORG > table_reorg.sql

   
   
4. The file table_reorg.sql looks like this:
   

   … REORG TABLE "SAPR3"."J_3RFANKA" ; REORG TABLE "SAPR3"."/SAPHT/MEST9LG" ; REORG TABLE "SAPR3"."/SAPHT/MESZPARM" ; REORG TABLE "SAPR3"."DB6IDENTIFIER" ; REORG TABLE "SAPR3"."T5BPBS01" ;

   
   
5. Execute the file table_reorg.sql:
   

   %db2 –tvf table_reorg.sql

   
   

Note:

• The REORG command should be used without the CLEANUP ONLY clause.
• For partitioned tables the clause ALLOW NO ACCESS must be used.

Back to top

FAQ

Q. Does SAP support large RIDs?

A. Yes, all newly installed SAP systems with DB2 9 will have that option enabled by default.

Q. What exactly does the ALTER TABLESPACE command do?

A. This command does three things:

1. It locks all tables which are defined to be used by the in Z mode.
2. It updates the row in SYSTABLESPACES to indicate the tablespace is LARGE, not REGULAR.
3. It updates the SPCS file, in memory tablespace definition, and all in memory TCB's to indicate the table is LARGE. This step is performed is at COMMIT time.

Q. Do I need to run the REORG INDEXES first and then REORG TABLE?

A. A table REORG performs a reorganization of the data and the indexes on the table. An index REORG is not required after this action. Bear in mind that a table REORG takes more time and must be performed offline. An index REORG might be sufficient depending on the expected growth of the table.

Q. What happens if the REORG TABLE operation is interrupted?

A. During an index REORG, a rollback is performed. During a table REORG the indexes are rebuilt to support large RIDs. Depending on when the REORG operation is interrupted, the indexes are either rebuilt or marked bad to be rebuilt at next access.

How long does the CONVERT TABLESPACE operation take? And what is the expected increase in tablespace size?

A. Based on some lab testing, this operation takes a few seconds. There is no increase in the tablespace size after a conversion to LARGE.

Q. How long does the REORG operation take? And what is the expected increase in tablespace size?

A. Based on some lab testing, the REORG TABLE operation can take from 10 minutes to 1 hour depending on how large and fragmented the table is and how many indexes belong to that table. REORG INDEXES takes approximately half of this time. A REORG TABLE operation performed on a group of 4044 tables took around 4 hours and 53 minutes. In testing, there was an increase of about 1% in tablespace size and a decrease of about 1.2% in the number of free pages in the tablespace after the reorganization.

Back to top

Appendix

SAMPLE program provided by IBM to enable large RIDs

-----------------------------------------------------------------------------
-- Licensed Materials - Property of IBM
--
-- Governed under the terms of the International
-- License Agreement for Non-Warranted Sample Code.
--
-- (C) COPYRIGHT International Business Machines Corp. 2006 - 2006
-- All Rights Reserved.
--
-- US Government Users Restricted Rights - Use, duplication or
-- disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
-----------------------------------------------------------------------------
--
-- SOURCE FILE NAME: largerid.db2
--
-- SAMPLE: How to enable Large RIDs support on both new tables/tablespaces and 
--         existing tables/tablespaces.
--
--         This sample shows:
--         1. Converting a regular DMS tablespaces to a large DMS tablespaces.
--         2. Table level migration - from earlier versions to new version to 
--            support larger table sizes.
--         3. Reorganizing indexes to support Large RIDs.
--
-- SQL STATEMENTS USED:
  --       ALTER TABLESPACE 
  --       CREATE TABLE
  --       CREATE TABLESPACE
  --	   REORG
  --	   SELECT
  --       SET INTEGRITY
--         TERMINATE
--
-- OUTPUT FILE: largerid.out (available in the online documentation)
-----------------------------------------------------------------------------
--
-- For more information about the command line processor (CLP) scripts,
-- see the README file.
--
-- For information on using SQL statements, see the SQL Reference.
--
-- For the latest information on programming, building, and running DB2
-- applications, visit the DB2 Information Center:
--     http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp
-----------------------------------------------------------------------------

-- Connect to database.
CONNECT TO sample;

-- The following scenario shows how a table which resides in a regular DMS 
-- tablespaces can be converted to reside into large DMS tablespaces to 
-- support large RIDs. 

-- Create a regular DMS tablespace.
CREATE REGULAR TABLESPACE largetbsp MANAGED BY DATABASE USING (FILE 'cont1' 1000);

-- Create a table in 'largetbsp' DMS tablespace.
CREATE TABLE large (max INT, min INT) IN largetbsp;

-- Create an index on 'large' table.
CREATE INDEX large_ind ON large (max);

-- Alter tablespace from a regular DMS tablespace to large DMS tablespace to 
-- support large RIDs.
ALTER TABLESPACE largetbsp CONVERT TO LARGE;

-- Rebuild/Reogranize indexes on table to support large RIDs.
-- Reorg reorganizes all indexes defined on a table by rebuilding the 
-- index data into unfragmented, physically contiguous pages.
-- This will permit the table use four-byte page numbers but not enable
-- the table to use more than 255 slots on a page. 

-- To use more than 255 slots on a page:
-- a) The table definition and the tablespace page size must allow it.
-- b) the table must be reorganized using classic, off-line reorg.

REORG INDEXES ALL FOR TABLE LARGE;

-- Drop index, table and tablespace.
DROP INDEX large_ind;
DROP TABLE large;
DROP TABLESPACE largetbsp;

-- The following scenario shows how a partitioned table which resides in a 
-- regular DMS tablespaces  can be converted to reside into large DMS 
-- tablespaces to support large RIDs. 

-- Create regular DMS tablespaces.
CREATE REGULAR TABLESPACE tbsp1 MANAGED BY DATABASE USING (FILE 'cont1' 1000);
CREATE REGULAR TABLESPACE tbsp2 MANAGED BY DATABASE USING (FILE 'cont2' 1000);
CREATE REGULAR TABLESPACE tbsp3 MANAGED BY DATABASE USING (FILE 'CONT3' 1000);

-- Create a partitioned table.
CREATE TABLE LARGE (max SMALLINT NOT NULL, CONSTRAINT CC CHECK (max>0)) 
  PARTITION BY RANGE (max) 
    (PART  part1 STARTING FROM (1) ENDING (3) IN tbsp1, 
    PART part2 STARTING FROM (4) ENDING (6) IN tbsp2, 
    PART part3 STARTING FROM (7) ENDING (9) IN tbsp3);

-- Insert data into the table.
INSERT INTO LARGE VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- Display records from the table.
SELECT * FROM LARGE;

-- If a partitioned table has data partitions in different regular DMS 
-- tablespaces, then the tablespaces cannot be converted to large
-- with the current definition.
-- To do this, first detach all the partitions of the table, later
-- convert all the tablespaces to large, reorg all the detached 
-- partitions to support large RID. Finally, reattach the partitions.
-- Now the entire table supports large RIDs.

ALTER TABLE large DETACH PARTITION PART3 INTO TABLE detach_part3;
ALTER TABLE large DETACH PARTITION PART2 INTO TABLE detach_part2;

-- Display records contained in each table.
SELECT * FROM large;
SELECT * FROM detach_part2;
SELECT * FROM detach_part3;

-- Convert all tablespaces from regular DMS tablespace to large DMS tablespaces.
ALTER TABLESPACE tbsp3 CONVERT TO LARGE;
ALTER TABLESPACE tbsp2 CONVERT TO LARGE;
ALTER TABLESPACE tbsp1 CONVERT TO LARGE;

-- Reorganize the detached partitions in order to support large RIDs. 
-- REORG reorganizes a table by reconstructing the rows to eliminate 
-- fragmented data, and by compacting information.

REORG TABLE detach_part3;
REORG TABLE detach_part2;
REORG TABLE large;

-- Reattach the reorganized detached partitions for table to support 
-- large RIDs. 
ALTER TABLE large ATTACH PARTITION part2 STARTING FROM (4) ENDING (6) 
  FROM TABLE detach_part2;
ALTER TABLE large ATTACH PARTITION part3 STARTING FROM (7) ENDING (9) 
  FROM TABLE detach_part3;

-- After performing above ALTER statements, table is put into 
-- set integrity peniding  state.
-- Before performing SELECT, table must be brought out from pending state.
SET INTEGRITY FOR large IMMEDIATE CHECKED;

-- Display records from the table.
SELECT * FROM LARGE;

-- Drop tables and tablespaces.
DROP TABLE LARGE;
DROP TABLESPACE tbsp1;
DROP TABLESPACE tbsp2;
DROP TABLESPACE tbsp3;

-- Disconnect from a database.
CONNECT RESET;

TERMINATE;

Resources

Learn

• Learn more about larger RIDs from the DB2 for Linux, UNIX, and Windows Information Center.
  
  
• Visit the developerWorks resource page for DB2 for Linux, UNIX, and Windows to read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Download a free trial version of DB2 Enterprise 9.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Marianne Nesiem is a member of the IBM SAP Integration and Support Centre at the IBM Toronto Lab. Her current activities include testing of SAP R/3 with DB2 UDB and helping customers with problem analysis and troubleshooting. She is also a customer advocate, providing custom-tailored support for large customer accounts running SAP and DB2. She is a DB2 Certified Solutions Expert and a UNIX systems administrator, and has experience in application development on Java for databases and other applications.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Larger RIDs
• Implement larger RIDs
• FAQ
• Appendix
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers