On Sun, 22 Jul 2012 15:05:10 -0700, Fin wrote:

Temporary tablespaces (user or system) by default use SMS tablespaces, which use paths (not files).
Since temporary tablespaces only stick around during the short time they are needed and then get
deleted right away, they may be hard to track. Also, it may be that SMS temporary tablespaces are not
actually stripped (even with auto storage), but are created on the different paths in a round-robin
method. Try it for a data tablespace created with automatic storage (which will create the containers
as a DMS tablespace).

If you want to see the containers (or container paths for SMS) of a tablespace, then use this command:
db2 list tablespace containers for n show detail (where n is the tablespace number from the command
"db2 list tablespaces").

Additionally, SELECT * FROM TABLE(MON_GET_CONTAINER('',-2)) AS t only shows 1 container for the newly added automatic storage tablespace.

And, SELECT * FROM TABLE(ADMIN_GET_STORAGE_PATHS('',-1)) AS T shows
IBMSTOGROUP 0 0 C:\DB2\CONTAINERS\TABLESPACES IN_USE 0 3124103852 499273166848 118449278976 380622561280
IBMSTOGROUP 0 0 D:\DB2\CONTAINERS\TABLESPACES NOT_IN_USE 1 3893546797 250057060352 114136899584 135920160768

IE: Not in Use.

That seems to work fine on a data tablespace, so it's only user temporary tablespaces I need to watch out for then, which effectively rules out using an automatic storage dms user temporary tablespace for any stored procedure.

Me thinks the online documentation should say so in large bold letters .. heh.
Thanks so much for your patience and help, appreciate it.

Will post again perhaps when I gather the courage to make the changes to my 4 data tablespaces .. lol.

On Sun, 22 Jul 2012 15:25:48 -0700, Fin wrote:

Not sure whether you know this, but you should use SMS for all temporary tablespaces. That it is why it
is the default with automatic storage.

uhhhh why would that be when all documentation points to DMS being better for performance ?

I've been using DMS tablespaces for ALL temporary tablespaces (TEMPSPACE1 /USERSPACE1 included) ever since I started reading and self-learning about db2 without issue so far to date. I have literally hundreds of SP that makeuse of such every day and they all perform very well from what I can tell (with the limited hardware I have).

SYSCATSPACE & SYSTOOLSPACE are the only SMS tablespaces I have.

Fin <tdavidge (AT) hotmail (DOT) com> writes:

Did your database already have 1 storage path on the C: drive before you
added the second storage path?

If so, you need to issue the ALTER TABLESPACE ... REBALANCE statement to
tell DB2 to spread existing data tablespaces out across both containers.
DB2 won't do this automatically just because you added a new storage
path.

For temporary tablespaces, you actually need to deactivate and
reactivate the database after adding storage paths to the database
before DB2 will start to use the new storage path. DB2 looks at what
storage paths are available when the database starts up (is activated)
to determine which paths to use for temporary tablespaces.



--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---

On Sun, 22 Jul 2012 16:41:26 -0700, Fin wrote:

I will admit that the documentation can be confusing in that regard. In theory, DMS has better
performance. One reason for that is that when a table is created in a DMS tablespace container, DB2
spends extra time looking for the optimum place to put the table within the tablespace. When you a
have a SP or query running that needs a temporary table, the slightly better performance of DMS is
more than offset by the extra time it takes to create the table in DMS, since a temporary table needs to
be created as quickly as possible and is only used for a very short amount of time. If you are creating a
table that is not a temporary table, that extra time when using DMS is not an issue.

I would suggest you create a brand new database and specify two paths for automatic storage (since
you have two spindles), just to look at all the defaults that DB2 uses for tablespaces. One other thing
you might notice (besides that all temporary tablespaces are SMS, and all others are DMS) is that all
SMS tablespaces by default use FILE SYSTEM CACHING and all DMS by default are NO FILE SYSTEM
CACHING.

I understand your frustration with the doc, but actually if you use the default automatic storage, and let
it take all the defaults on tablespaces, you don't need to know all these details like was necessary in
V8. The only thing you have to do is to set up your automatic storage paths ahead of time and define
them in the create database command (although you can add or modify them later if needed).

Mark,

I have made changes to two of my tablespaces (rawdata & static) both of which are now managed by automatic storage and point to containers on both drives.

Before I make the changes to the two remaining tablespaces which are very large by comparison, I have a question about file fragmentation under the windows 7 pro operating system.

With the existing DMS tablespace containers I can specify the container file size and then do a defrag to get the actual file into one contiguous fragment and move it to the fast (beginning part of the physical desc). If however I enable automatic storage I am assuming I cannot set the size manuallyand the container size will continue to grow automatically as required as data is added to either tablespace. My problem with this is that windows isnotorious for poorly handling file increases like this and it will no doubt create several fragments in the process. As the files will be quite largethe time taken to defrag such as they increase in size is going to be painful to say the least.

With a normal DMS container, one can set the size, defrag it and not have to worry about it fragmenting again unless you have to manually resize it.

Can I at least set a min size for the storage-group containers ?

Well, I went ahead and made the changes anyway to all 4 tablespaces, changes went successfully, no issues.

My observations, first the BAD.

Base table data Insert performance has taken a hit of approx 15-20% for theworse. As I feared, it seems that if a table has indexes then there is definately a performance hit in this regard. However, it is not all bad news as the majority of inserting data is done at the very beginning of end of day processing and only makes up around 10 % of the overall processing that must be done.

Good news: The vast majority of my processing is done in SQL Stored Procedures using global temp tables that first insert, then create any required indexes, process, rinse repeat etc. This means that very little inserting into tables where indexes already exist. The performance GAINED in this regardis difficult to measure as it depends entirely on the day in question, however I can definately see a performance benefit in the data read/write rates to these GTT tables.

Further, the large reporting requirements of some 800k + rows etc in some reports certainly see improvement in performance. Runstats times have also improved.

A final question to anyone that can help,
SELECT * FROM TABLE(MON_GET_TABLESPACE('',-2))
tells me that TBSP_AUTO_RESIZE_ENABLED = 1 for the changed tablespaces, however:

SELECT SNAPSHOT_TIMESTAMP, TBSP_ID, TBSP_NAME, TBSP_STATE, TBSP_UTILIZATION_PERCENT
FROM SYSIBMADM.TBSP_UTILIZATION
WHERE TBSP_TYPE = 'DMS'
ORDER BY 5 DESC;

reports that 2 of my tablespaces are at 90% TBSP_UTILIZATION_PERCENT. If auto_resizing is enabled, at what point will it kick in, and when it does, what exactly does it do ? Does it increase the size of existing containers inthe storage-group, add more containers, what exactly happens here ?

I have checked the documentation and cannot find what it does when it decides it must increase size, or what determines it must do so. Does it get to a % of utilization and then increase extents ? I don't know and would like to be certain I am not going to get into trouble with this.

Many thanks to all, Fin.