Difference between SMS, DMS and Automatic Storage tablespaces

Let us see the differences between SMS ( System Managed Space), DMS (Database Managed Space) and Automatic Storage tablespaces.

How they are created:

• SMS: Created using MANAGED BY SYSTEM clause in the CREATE TABLESPACE command.
• DMS: Created using MANAGED BY DATABASE clause in the CREATE TABLESPACE command.
• Automatic Storage: Created using MANAGED BY AUTOMATIC STORAGE clause in the CREATE TABLESPACE command. When the MANAGED BY clause is not given in the CREATE TABLESPACE command, then DB2 will automatically treat it as automatic storage.

Container Definition:

• SMS: Containers will be defined as a directory name.
• DMS: Containers will be defined as a file name or a device name. We must specify the initial space for each container.
• Automatic Storage: We need not to define the containers, the database manager automatically creates containers on all the storage paths. Data will be striped evenly across all the containers.

Initial Space Allocation:

• SMS: The file system controls the allocation of storage.
• DMS: Space will be allocated while creating the tablespace.
• Automatic Storage: Space will be allocated while creating the tablespace, you can also specify the intial size of the tablespace.

Changes to tablespace containers: 

• SMS: We cannot do any changes the tablespace containers once created.
• DMS: We can add, extend, reduce and drop the containers.
• Automatic Storage: Containers can be reduced or dropped, if the tablespace size is reduced, tablespace can be rebalanced evenly across containers when new storage is added or dropped from the database.

Growth of the tablespace:

• SMS: Containers will grow until they reach the capacity of the file system.
• DMS: Containers can be extended beyond the allocated size manually or automatically (if auto resize is enabled) upto the file system size.
• Automatic Storage: Containers will grow until they reach the capacity of the file system. If storage paths are added to the database, containers are extended automatically.

On-going Maintenance:

• SMS: There will be no on-going maintenance for SMS tablespaces.
• DMS: We will have some maintenance activities like adding or extending containers, Lowering High Water Mark and Re-balancing.
• Automatic Storage: Lowering High Watermark and Re-balancing.

Performance:

• SMS: Generally slower than DMS and Automatically Storage.
• DMS: Good performance when compared to others.
• Automatic Storage: Same as DMS.

How to do DB2 Administration when DB2 instance id is locked.

How to do DB2 administration when the instance id is locked or sudo is revoked.

1. Login to the server with your personal id (id should be a member of db2admin group)
2. Find the instance home directory using lsuser command or finger command.
3. Switch to the instance home directory.
4. Now go to sqllib directory and execute db2profile. This will setup the environment for particular instance.
5. Now attach to the instance using the following command.
6. Now we can perform almost all the administration tasks, with out instance id. (Only thing is our id should be part of db2admin group).
7. Certain specific utility like db2pd, db2top may not work due to restrictions, but still we are good to go with other normal DBA activity.

In this way we can perform all the administration tasks, even instance id is locked or sudo is not working.

Table space states

The current status of a table space is reflected by its state. The table space states most commonly associated with recovery are:

• Backup pending. A table space is put in this state after a point-in-time rollforward operation, or after a load operation with the no copy option. The table space must be backed up before it can be used. (If it is not backed up, the table space cannot be updated, but read-only operations are allowed.)
• Restore pending. A table space is put in this state if a rollforward operation on that table space is cancelled, or if a rollforward operation on that table space encounters an unrecoverable error, in which case the table space must be restored and rolled forward again. A table space is also put in this state if, during a restore operation, the table space cannot be restored.
• Rollforward-in-progress. A table space is put in this state when a rollforward operation on that table space is in progress. Once the rollforward operation completes successfully, the table space is no longer in rollforward-in-progress state. The table space can also be taken out of this state if the rollforward operation is cancelled.
• Rollforward pending. A table space is put in this state after it is restored, or following an input/output (I/O) error. After it is restored, the table space can be rolled forward to the end of the logs or to a point in time. Following an I/O error, the table space must be rolled forward to the end of the logs.