473,703 Members | 2,976 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Creating Tablespaces : Best Practices

Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

This is one i generated through Control centre....

CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/db2home/db2inst1/dnci1d/user_tblspace')
EXTENTSIZE 8 PREFETCHSIZE 16 BUFFERPOOL GBP01 OVERHEAD 12.670000
TRANSFERRATE 0.180000;

Now, to create the remaining using command prompt how should i proceed
in specyfying the directories ...USING
('/db2home/db2inst1/dnci1d/user_tblspace') ??

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??

2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2

What is the best practice ??

3.) I have a table with 3 fields having Varchar(4000) and am getting
sqlcode -286
A default tablespace could not be found with a page size atleast 16384.
How should I size the Tablespace and bufferpool for this purticular
table ??

Sep 1 '06 #1
10 13410
While stranded on information super highway rAinDeEr wrote:
Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

This is one i generated through Control centre....

CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/db2home/db2inst1/dnci1d/user_tblspace')
EXTENTSIZE 8 PREFETCHSIZE 16 BUFFERPOOL GBP01 OVERHEAD 12.670000
TRANSFERRATE 0.180000;

Now, to create the remaining using command prompt how should i proceed
in specyfying the directories ...USING
('/db2home/db2inst1/dnci1d/user_tblspace') ??

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??
Yes.
>
2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2
I don't think so.
>
What is the best practice ??
It depends on how you are using the database. In high volume environment
the best practice would be to use DMS table spave and have one table
per disk (unless you are using SAN) and have one ioserver per disk in
use. In low volume you can probably get by using SMS table space.

>
3.) I have a table with 3 fields having Varchar(4000) and am getting
sqlcode -286
A default tablespace could not be found with a page size atleast 16384.
How should I size the Tablespace and bufferpool for this purticular
table ??
The table have 3 columns of VARCHAR 4000 which is greater than 4K (page
size of the table space), one size up for page size is 16K, you will have
to create table space with page size of 16K, which will also require to
create a buffer pool with page size 16K.

>
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@ xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Sep 1 '06 #2
Hi..

How do I start creating Tablespaces in Linux in the command prompt... ?
Control centre is really easy.....

Do i have to use MKDIR then change the ownership ?

Sep 1 '06 #3
rAinDeEr wrote:
Hi..

How do I start creating Tablespaces in Linux in the command prompt... ?
Control centre is really easy.....

Do i have to use MKDIR then change the ownership ?
Have you considered using automatic storage? It would make your life so
much easier.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 1 '06 #4
rAinDeEr wrote:
1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??
Correct. Best might be to create the directories as DB2 instance owner.
Then you don't have to worry about the access.
2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2
No. SMS containers require a directory. Thus, you can't place different
containers in the same directory. But you can use common parent
directories:

/containers/container1/
/containers/container2/

DMS containers are different. Since those are files, you can place those
files in the same directory.
3.) I have a table with 3 fields having Varchar(4000) and am getting
sqlcode -286
A default tablespace could not be found with a page size atleast 16384.
How should I size the Tablespace and bufferpool for this purticular
table ??
3 * 4000 = 12000. So you need a tablespace with 16K or 32K page size... and
a buffer pool that goes along with it. As for the size of the
tablespace/bufferpool, it depends on the amount of data. The usual TS/BP
tuning comes into play.

p.s: Is there a particular reason why you want to place all the tables in
dedicated tablespaces? You could just dump them into a few TS and let DB2
take care of the rest. Multiple TS and containers are usually only helpful
if you want to physically separate the data and/or if you have multiple
discs and want to take advantage of parallel I/O. (Or if you need
different page sizes...)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 1 '06 #5
Am trying to partition the tables in such a way that the tables with
huge data belong to separate tablespaces and bufferpools and tables
with just a few records I have clubbed into a single tablespace for
performance.

Am i proceeding in the right way ?

Also, I have to remotely connect to the Database. I dont have a client
installed nor have i cataloged any where..Have to TELNET and do the
operations.

I am working in a m/c with no partitions..So how do I go about
Automatic Storage ?

Also deciding on the bUfferpool size is another issue..

Around 100 tables...40 of them with very high records...remai ning just
a few 100 records..

Sep 1 '06 #6
rAinDeEr wrote:
Am trying to partition the tables in such a way that the tables with
huge data belong to separate tablespaces and bufferpools and tables
with just a few records I have clubbed into a single tablespace for
performance.
That makes sense. ;-)
Also, I have to remotely connect to the Database. I dont have a client
installed nor have i cataloged any where..Have to TELNET and do the
operations.
You may want to consider using a DB2 client on your own machine. Then you
can handle things a bit easier if you are not very familiar with the CLP
yet.
Also deciding on the bUfferpool size is another issue..
There I would recommend to run the Configuration Advisor (from the control
center) since it makes some suggestions regarding buffer pool sizes.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 1 '06 #7
"rAinDeEr" <ta**********@g mail.comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .
Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...
While it is best practice in DB2 for z/OS to have a single tablespace for
each table (with some exceptions), it is bad practice to that in DB2 for
(LUW) Linux, UNIX, and Windows.

With DB2 for LUW, you want one tablespace for each page size that is needed.
If you want multiple bufferpools, then you may need multiple tablespaces
since tablespaces are assigned to bufferpools (not tables).

You might want additional tablespaces if you have some extremely large
tables (in addition to smaller tables) that will be have a lot of full table
scans (queries that read every single row of the table), and you want to use
a different prefetch or extent size than the other tablespaces.

There is no way you should have 70 tablespaces in DB2 for LUW.

Sep 1 '06 #8
Ian
rAinDeEr wrote:
Hi,

I am trying to create around 70 tablespaces for around 100 tables..
Am using DB2 UDB 8.2 in Linux environment...

This is one i generated through Control centre....

CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP
IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/db2home/db2inst1/dnci1d/user_tblspace')
EXTENTSIZE 8 PREFETCHSIZE 16 BUFFERPOOL GBP01 OVERHEAD 12.670000
TRANSFERRATE 0.180000;

Now, to create the remaining using command prompt how should i proceed
in specyfying the directories ...USING
('/db2home/db2inst1/dnci1d/user_tblspace') ??

1.) Do I have to create around 70 directories for each container using
MKDIR first and then proceed with the command ...??
No. I'm not sure why other people think that the directory has to exist
first. DB2 will create the directory if it is able to. This applies to
both SMS and DMS (i.e. DB2 does the equivalent of 'mkdir -p').

In fact, if DB2 does create the directory, it creates a file in the
directory named .SQLCRT.FLG that signifies that DB2 created the
directory. When you drop your tablespace, DB2 will remove the
directory if the .SQLCRT.FLG exists.
2.) Can I nest the 70 containers under one directory ? Am getting
SQL0298N Bad container path. SQLSTATE=428B2
The containers can all exist in a single path. Example:

/db2home/db2inst1/dnci1d/user_tblspace/snci001 (for tbspace SNCI001)
/db2home/db2inst1/dnci1d/user_tblspace/snci002 (for tbspaceSNCI002)
Note, DB2 does not append the name of the tablespace to the path you
specify for SMS (which it looks like you were assuming that it did,
based on your container path).

What is the best practice ??
This depends completely on your server and the physical disks you've got
installed in the server.

Sep 1 '06 #9
Ian
Mark A wrote:
While it is best practice in DB2 for z/OS to have a single tablespace for
each table (with some exceptions), it is bad practice to that in DB2 for
(LUW) Linux, UNIX, and Windows.
I don't think it's necessarily a bad practice on LUW. But it certainly
shouldn't be done by default.

It's just that the reasons why people do it on z/OS don't apply on
LUW, right? (I'm not a z/OS guy).

Sep 1 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1967
by: byrocat | last post by:
I'm chasing after a documetn that was available on one of the Microsoft websites that was titled somethign like "MS SQL Server Best Practices" and detailed a nyumber of best practices about securing the server. Included in this was revoking public access to the system table objects. Can someone post the URL where I can pick this up, or drop me a note on contacting them for a copy of the document?
1
2234
by: nathan_kent_bullock | last post by:
I have a python program that I am trying to generate documentation for. But in one of my files I have a class called "Data", when pydoc gets to this class it just barfs. Well more specifically it generates documentation for only that one class in the file, it ignores the rest of the classes, plus it doesn't create the standard header, Package Contents section, Classes section, or anything else. Just wondering if this is a known bug, I can...
136
9397
by: Matt Kruse | last post by:
http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their code was littered with document.all and eval, for example, and I wanted to create a practical list of best practices that they could easily put to use. The above URL is version 1.0 (draft) that resulted. IMO, it is not a replacement for the FAQ,...
13
2277
by: john doe | last post by:
A quick question, about so-called 'best practices', I'm interested in which of A/B of the two examples people would choose, and why. public enum MyEnum { Option1 = 0, Option2 = 1, Option3 = 2, Option4 = 3
0
4234
by: Anonieko Ramos | last post by:
ASP.NET Forms Authentication Best Practices Dr. Dobb's Journal February 2004 Protecting user information is critical By Douglas Reilly Douglas is the author of Designing Microsoft ASP.NET Applications and owner of Access Microsystems. Doug can be reached at doug@accessmicrosystems.com. --------------------------------------------------------------------------------
2
1822
by: Amelyan | last post by:
Could anyone recommend a book (or a web site) that defines best practices in ASP.NET application development? E.g. 1) Precede your control id's with type of control btnSubmit, txtName, etc. 2) Group relevant .aspx files into subfolders within your project etc.
10
3454
by: jojobar | last post by:
Hello, I am trying to use vs.net 2005 to migrate a project originally in vs.net 2003. I started with creation of a "web site", and then created folders for each component of the site. I read somewhere that each folder under the "web site" is compiled in separate assembly. I however, did not find that the "web site" creation in vs.net 2005 created any AssemblyInfo.cs file.
8
1566
by: SStory | last post by:
When I right a class, I am wondering what are the best practices for error handling? Do I try..catch and trap the error and if so what do I do with it? Because most likely the class user will want to know the information in the exception.... That being the case do I just not catch it and let the user of the class catch it and get all the information? I know I could catch it and throw my own, but I'd have to tell them the same
9
1792
by: Phlip | last post by:
Newsgroupies: Good guidelines keep source code within a "comfort zone". Programming languages provide extraordinarily wide design spaces, much wider than hardware designs enjoy, with many tricks and backdoors that could provide hours of pleasant diversion writing obfuscated code. Don't write like that on the job. Always write similar, obvious statements to do similar, simple things. Engineers learn their working vocabulary from...
0
8662
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9243
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9109
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8956
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7853
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4420
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4677
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2434
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.