Skip to Content
author's profile photo Former Member
Former Member

A simple question about SMS tablespace

In book TADM56, following sentences describe feature about SMS, "When an object (such as a table or an index) is created, a file is created in the directory. If an extent has been filled up, a file will be created in the next directory. This is performed in a circular mode."

I do following test:

CREATE TABLESPACE TESTSPACE

MANAGED BY SYSTEM

USING ('C:\DB2TEST\TESTSPACE1', 'C:\DB2TEST\TESTSPACE2', 'C:\DB2TEST\TESTSPACE3')

EXTENTSIZE 64

PREFETCHSIZE 32

CREATE TABLE TABLE1 (

ID INT,

NAME CHAR(10)

) IN TESTSPACE INDEX IN TESTSPACE

After that, a file SQL00002.DAT is created in directory "C:\DB2TEST\TESTSPACE3"

I don't understand this sentence "If an extent has been filled up, a file will be created in the next directory. This is performed in a circular mode". Based on the test I did above, how to understand the circular mode? Please advise.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 12:39 PM

    Hi,

    the meaning is that when you insert data into the table the allocation will be done in round robin fashion on all directories. For your example table the database will allocate extents (packages) of 64 pages (usually 4k if no other default was chosen at db install). When this first extent is filled by inserts, the next 64 pages will allocated in a new created file in the next directory. When this second extent is filled a third extent will be allocated in the third directory. In the example with three directories the forth extent will be appended to the first file and so on. This behavior is meant by cyclical.

    Regards

    Ralph

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 07:22 AM

    Hi,

    you have specified 3 directories for your SMS tablespace. So this sentence means, that when the table gets filled and new extents are required to be allocated, the next time DB2 will take C:\DB2TEST\TESTSPACE1 and then C:\DB2TEST\TESTSPACE2 and then C:\DB2TEST\TESTSPACE3 again to allocate new space and so on. Have you checked if some files have been created in C:\DB2TEST\TESTSPACE1 and C:\DB2TEST\TESTSPACE2 ( perhaps a SQL00000.DAT and SQL00001.DAT) ?

    kind regards

    Thomas

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2008 at 07:29 AM

    Hi

    Circular mode means that the data is filled upto certain level in the table and when that limit reaches, it is automatically flushed to the log files. Like in Oracle the archive logs are created.

    If either LOGRETAIN or USEREXIT is enabled, the log files will be retained and become online archive log file for use in Roll-forward Recovery. This is called log retention or archive logging. Unlike circular logging, the logfiles are not normally reused. The numbering scheme for the archival logging is Sxxxxxxx.LOG, where xxxxxxx ranges from 0000000 to 9999999. The log files are stored in the directory specified in the database configuration and may be defined when the database is created or modified at a later date.

    In Oracle, when you use the archivelog mode, the log files are always used in circular mode, but when a log files becomes inactive, it is stored in the log_archive_dest subdirectory. DB2 does not work in this fashion. As mentioned above, the logfiles are not reused when archival logging is enabled. It is up to

    the user to back up these log files. To assist in this, there is a user exit capability that may be invoked whenever a logfile is closed.

    The SMS tablespace is a generalization of the storage model found in DB2 Version 1. Version 1 database files are stored under a configured number of subdirectories. All the data files are located under the database subdirectory. The database or system administrator can tell DB2 where to create the database

    subdirectory. The name and location of all the segment subdirectories is automatic. The number of subdirectories created can be specified at database creation time and cannot be changed afterward. In DB2 Version 2, SMS allows the database administrator to specify any directory that is accessible by the

    system, as a location for storing database files. These directories are called containers. In an SMS tablespace, one container maps to a single directory. Given this, the maximum size of an SMS tablespace would be the number of containers multiplied by the maximum file system size supported by the

    operating system.

    The following files are found within an SMS tablespace directory:

    SQLxxxxx.DAT Table file. All rows of a table are stored here, with the exception of LONG VARCHAR, LONG VARGRAPHIC, CLOB, BLOB, and DBLOB data. SQLxxxxx.LF Files containing LONG VARCHAR or LONG VARGRAPHIC data. This file is only created if LONG VARCHAR or LONG VARGRAPHIC columns exist in the table.

    SQLxxxxx.LB Files containing CLOB, BLOB, or DBBLOB data. This file

    is only created if CLOB, BLOB, or DBBLOB columns exist

    in the table.

    SQLxxxxx.LBA Files containing allocation and free space information

    about the SQLxxxxx.LB file.

    SQLxxxxx.INX Index files for a table. All indexes for the corresponding

    table are stored in this file. It is only created if indexes

    have been defined. When an index is dropped, the space is

    not physically freed from the index file until the index file is

    deleted. This occurs when all indexes for the table have

    been deleted.

    SQLxxxxx.EIX Damaged INX file for a table.

    SQLxxxxx.DTR Temporary data files for a REORG of a DAT file.

    SQLxxxxx.LFR Temporary data files for a REORG of an LF file.

    SQLxxxxx.RLB Temporary data files for a REORG of an LB file.

    SQLxxxxx.RBA Temporary data files for a REORG of an LBA file.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.