DB2 DMS Tablespaces are 99% full but Autoresize=Yes. What action required??

[Former Member]

Dear Friends,

DB2 DMS Tablespaces are 99% full but Autoresize=Yes. What actions are required or should leave as it  is ?? Kindly suggest solution.

Sample Scenario :

Tablespace Name	TS Type	KB Total	Percent Used	KB Free	Page Size (KB)	High-Water Mark (KB)
SID#BTABD	DMS	6422528	99.94	3680	16	6418720
No. Containers	Contents	TS State	AUTORESIZE	Pending Free Pages	FS_FREE_SIZE_MB
4	Large objects	Normal	YES	0	0

Regards

Dharmendra

[Former Member · Accepted solution]

Hi Dharmendra,

If autostorage and auto extend enabed ,then the db will always show the tablepsaces as around 99% full as it only extends them as needed.

You should ensure there is plenty of free space at filesystem level (sapdata) for the storage paths to grow as needed.

Br Vaibhav

[Former Member · Accepted solution]

Dear Mr. Vaibhav,

Thanks for helpful reply. It seems open ended solution regarding free space at filesystem level i.e. plenty of free space at filesystem level

How can I ensure minimum/exactly free space required on filesystem for Auto-extend ?

Rgds

Dharmendra

[Former Member · Accepted solution]

Hi Dharmendra,

You can put manual monitoring/ccms alert for filesystem space used.

Keep threshold for 80% used.

As sapdata filesystem reach to threshold value, go for space addtion externally through volume group to make it below 80% through the help from Unix/OS team.

Hope it helps

Br Vaibhav

[Former Member · Accepted solution]

Dear Mr. Vaibhav,

My question is " How much a tablespace size will be extended during autoresize/autoextend trigger ?" 1GB, 2GB, 5GB, 10GB.

Regards

Dharmendra

[Former Member · Accepted solution]

Hi Dharmendra;

from the IBM Knowledge Center:

Increase size (INCREASESIZE)

The INCREASESIZE clause of the CREATE TABLESPACE statement defines the amount of space used to increase the table space when there are no free extents within the table space but a request for one or more extents was made. You can specify the value as an explicit size or as a percentage, as shown in the following examples:

  CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES INCREASESIZE 5 M CREATE TABLESPACE DMS1 MANAGED BY DATABASE USING (FILE '/db2files/DMS1' 10 M) AUTORESIZE YES INCREASESIZE 50 PERCENT

A percentage value means that the amount by which to increase is calculated every time that the table space needs to grow; that is, growth is based on a percentage of the table space size at that point in time. For example, if the table space is 20 MB in size and the INCREASESIZE value is 50% , the table space grows by 10 MB the first time (to a size of 30 MB) and by 15 MB the next time.

If you do not specify the INCREASESIZE clause when you enable the auto-resize feature, the database manager determines an appropriate value to use, which might change over the life of the table space. As with AUTORESIZE and MAXSIZE, you can change the value of INCREASESIZE using the ALTER TABLESPACE statement.

If you specify a size increase, the actual value that the database manager will use might be slightly different than the value that you provide. This adjustment in the value used is done to keep growth consistent across the containers in the table space.

Best regards

Joachim

[Former Member · Accepted solution]

Hi Dharmendra,

Answer to your question:

"My question is " How much a tablespace size will be extended during autoresize/autoextend trigger ?" 1GB, 2GB, 5GB, 10GB."

It all depends on your settings during tablespace creation, there is no such default value.

There are two parameeter for each tablespaces MAXSIZE and INCREASESIZE.

You can check settings from DBACOCKPIT --> SPACE -->Tablespaces ( under Storage tab)

The MAXSIZE clause of the CREATE TABLESPACE statement defines the maximum size for the table space , The MAXSIZE NONE clause specifies that there is no maximum limit for the table space.

While INCREASESIZE clause of the CREATE TABLESPACE statement defines the amount of space used to increase the table space.

If you do not specify the INCREASESIZE clause when you enable the auto-resize feature, the database manager determines an appropriate value to use, which might change over the life of the tablespace. As with AUTORESIZE and MAXSIZE,you can change the value of INCREASESIZE using the ALTER TABLESPACE statement.

Refer below link

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0012...

Hope it helps

Br Vaibhav

[Former Member · Accepted solution]

Dear Mr. Joachim,

Thank you for helpful answer. Can explain this scenario or parameters (AUTORESIZE=YES, INCREASESIZE "XM/Y%", MAXSIZEetc) in SAP environment where tablespaces are also created automatically during SAP Installation with predefined parameters?

Regards

Dharmendra

[maria_shen]

Hello Dharmendra,

Please check follows:

1. Check the database snapshot (db2 get snapshot for database on <dbsid>)

-  Number of automatic storage paths  =      (Should be a value larger than 0)

2. Check tablespace snapshot (db2 get snapshot for tablespaces on <dbsid>))

  Using automatic storage                  =    (should be YES)

  Auto-resize enabled                      =      (should be YES)

3. Whether there are enough free space for the storage paths on the file system level.

Depending on the database version, there is "hard" limit on one tablespace size. But the size shown here seems not to be the issue.

Kind regards
Maria

[Former Member]

Dear

as you said your Autoresize=Yes then it will autoextended automatically specified space to that table space

for that there should be sufficient free size in file system so no issue will come or you can add datafiles to that tablespace

Regards

[Former Member]

Dear All,

I got confused by reading comment from Mr. Rajendra "that there should be sufficient free size in file system so no issue will come or you can add datafiles to that tablespace.

What should I do exactly? should add data file or not ?

If No and It will be automatically extended, then when will it be triggered and what will be size of next auto-extension ?

Regards

Dharmendra

[Former Member]

Dear

dont get confused if you don't have space in file system them how  r u  going extend table space go and read some notes on google for further clarification

if still not solved then send me the screen shot

REgards

[Former Member]

Dear All,

There is no space constraint on filesystem neither mentioned anywhere in above discussion. My query is related to Database ->Tablespace only. Kindly suugest.

Rgds

DK

[Former Member]

Dear

you can add data files to table spaces that solves your issue 

Regards

[Former Member]

Dear Rajendra,

What is the purpose of Autoresize=Yes in tablespace snapshots ?

Why should I add datafile? What is logic ?

Kindly clarify.

Rgds

DK