Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Jan 20, 2004

A Fresh Look at Data Striping in DB2 Universal Database Version 8.1 - Page 2

By DatabaseJournal.com Staff

By Paul C. Zikopoulos and Roman B. Melnyk

The BEGIN NEW STRIPE SET Option

DB2 UDB Version 8.1 introduces the BEGIN NEW STRIPE SET option on the ALTER TABLESPACE statement (Figure 4).

Figure 4. The BEGIN NEW STRIPE SET option on the ALTER TABLESPACE statement allows you to add new containers to a DMS table space without the need for rebalancing the containers.

The easiest way to do this is through the Control Center, which gives you access to database objects, including table spaces. Figure 5 shows a DMS table space called TS1 that we created in the SAMPLE database. All of the table spaces in the SAMPLE database appear in the contents pane with Table Spaces selected in the object tree.

Figure 5. The table spaces that are associated with a particular database appear in the contents pane of the Control Center with Table Spaces selected in the object tree.

Clicking Alter in the pop-up menu launches the Alter Table Space window (Figure 6).

Figure 6. The Containers tab of the Alter Table Space window. Selecting the Manage stripe sets check box adds a Stripe Set column to the window.

Clicking the Add button launches the Define Container window (Figure 7). If the Manage stripe sets check box in the Alter Table Space window was selected, you will see a Stripe set drop-down list in the Define Container window, from which you can select a new (or existing) stripe set for the new container.

Figure 7. The Define Container window lets you specify a new container for a DMS table space. You can associate this new container with an existing stripe set, or with a new one, which is equivalent to specifying the BEGIN NEW STRIPE SET clause on the ALTER TABLESPACE statement.

The following code shows the underlying SQL statements that defined the new container TSC2 and stripe set 1:

db2 connect to sample
db2 alter tablespace ts1 begin new stripe set (file 'D:\WorkDir\tsc2' 5120)
db2 connect reset

If you were to add another container (TSC3, for example) to the table space without specifying the BEGIN NEW STRIPE SET option, TSC3 would become part of the new stripe set (1). In addition, if you subsequently added container TSC4, this time specifying the BEGIN NEW STRIPE SET option, a new stripe set (2) would be created, and TSC4 would be part of stripe set 2.

You can add a container to an existing stripe set by specifying the ADD option, along with the TO STRIPE SET clause, as follows:

db2 connect to sample
db2 alter tablespace ts1 add to stripe set 0 (file 'D:\WorkDir\tsc5' 512)
db2 connect reset

If you do not specify the TO STRIPE SET clause, the current (that is, the most recently created) stripe set is assumed.

Conclusion

DB2 UDB Version 8.1 introduces the BEGIN NEW STRIPE SET option on the ALTER TABLESPACE statement. This option allows you to add more storage to DMS table spaces without the need for rebalancing containers.

About the Authors

Paul C. Zikopoulos, BA, MBA, is with IBM Canada Ltd.  Paul has written numerous magazine
articles and books about DB2. Paul has co-authored the books: DB2 Version 8:
The Official Guide, DB2: The Complete Reference, DB2 Fundamentals
Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases
on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and
Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence
and Database Administration). You can reach him at: paulz_ibm at msn.com.

Roman B. Melnyk, PhD, is with IBM Canada Ltd., specializing in database administration, DB2
utilities, and SQL. Roman has written numerous DB2 books, articles, and
other related materials. Roman co-authored DB2 Version 8: The Official
Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for
Dummies, and DB2 for Dummies. You can reach him at roman_b_melnyk at
hotmail.com.



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.