How add a container to tablespace in db2?

[Former Member]

Hi,

System Details:SUSE Linux 11 ,DB2 9.1 fp12,ECC6.0 SR3,kernel 720, 70SWPM

I found that one of the table space fill 100%. So i am trying to add a new container to table space. In the process i tried through both application level and os level but i am unable to add a container to table space.

1. table space 100% Full .For reference see table space_full.jpg  in attachment

2. In application level i went to DBACOCKPIT-->SPACE-->CONTAINER--> add container. Here create option was disabled for reference see create container disable.jpg in attachment

3.AUTO RESIZE was enabled. For reference see table space_full.jpg  in attachment.

At OS level : connected to db2<SID> user then execute the command DB2 CONNECT TO <SID> then executing the following command

db2 alter table space <table space name>  add (FILE '<PATH OF CONTAINER>'  <SIZE>)

it throws the following error

            DB21034E  The command was processed as an SQL statement because it was not avalid Command Line Processor command.  During SQL processing it returned:SQL20318N  Table space "PR3#BTABD" of type "AUTOMATIC STORAGE" cannot bealtered using the "ADD" operation.  SQLSTATE=42858

Please find the below attachment for reference.

[Former Member · Accepted solution]

Hi all,

Thanks  for  giving reply...

table size was increased  By using below command

db2 alter tablespace my_tbspce increasesize 30 percent;

Thanks & regards

yashwanth

[manumohandas82 · Accepted solution]

Hi Yashwanth ,

This would have already been done by your DB2 as you have already made your TB as automatic storage .

Believe you do not need to worry when the tablespace shows 100% in DBACOCKPIT and your tablespace is Automatic storage enabled  ( and you have space in your Filesystem )

The fact that db2 automatically  didnt increase is because there was no need to . When the next requirement comes to write something into that tablespace , DB2 would have already increased it by the extend size

Frank  ,

Kindly correct me if the above is wrong ?

This is just to make sure that nobody following this thread does something which is not required to do  ( if in case it is wrong )

Thanks ,

Manu

[martin_mikala]

Hello,

By my opinion , 100% usage isn't problem or  it is normal for Automatic storage TS.

If there is any problem, in db2diag will be "Severe" error message.

db2diag -t 2015-02-26 -l severe

B.R.

Martin

[former_member182657]

Hi,

Could you please check with  SAP Note  1895425 - DB6: Using Automatic Storage

and can refer solution from it.

Regards,

[Former Member]

Hi all,

thnx for giving reply....

yes in my file system lot of space is their then way it is showing 100% full in table space

....

i tried all the ways but storage is didn't extended way????,it shows same ....

please give  me suggestion ,what can i do?

thnx....

reg..

yashwanth

[former_member182657]

Addition to it i would suggest you to refer one wonderful SCN document at http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/70da5b8c-d651-2c10-37ac-9eb76853d...

Hope this will help you.

Regards,

Gaurav

[former_member182657]

As per the results,there is no need to extend the file systems, as sufficient space is available for the further actions.

Here you could use ALTER DATABASE STORAGE command to add new paths to the database’s automatic storage collection instead of ALTER TABLESPACE statement as automatic storage is enabled at your end.

Thanks,

Gaurav

[manumohandas82]

Hi Yeshwanth ,

Apologies .. not good at Linux commands

Can you try df -h

The core is if your filesystems ( /db2/PR3/sapdata[1-4] ) has enough space  you dont need to worry [ believe you are not getting any errors in SM21 /db2diag ]  , DB2 will automatically extend  .

If  all the File systems are getting filled up   extend your FS  ( this is the easiest option as far i know )

You may also extend  as suggested by siddesh/Frank

Thanks ,

Manu

[Former Member]

Hi manu

The following was the df -h result

sandbox:/ # df -h

Filesystem      Size  Used Avail Use% Mounted on

/dev/sda3       255G   61G  181G  26% /

devtmpfs         24G  192K   24G   1% /dev

tmpfs            50G   88K   50G   1% /dev/shm

/dev/sda1       486M   41M  421M   9% /boot

/dev/sdb1       826G  414G  370G  53% /db2

One more thing in when ever i hit SM21

it will prompt a dialog box withe folloeing matter with continue option

         central system log cannot be read

         /usr/sap/PR3/SYS/global/SLOGJ

[manumohandas82]

Hi Yashwanth ,

Please traverse to /db2/PR3/sapdata1 and issue the command df -h . ( dot )

As for the system log can you paste the screen

Thanks ,

Manu

[Former Member]

Hi manu

The following  are the details for df -h

sandbox:/db2/PR3/sapdata1 # df -h.

df: invalid option -- '.'

Try `df --help' for more information.

sandbox:/db2/PR3/sapdata1 # df -h

Filesystem      Size  Used Avail Use% Mounted on

/dev/sda3       255G   61G  181G  26% /

devtmpfs         24G  192K   24G   1% /dev

tmpfs            50G   88K   50G   1% /dev/shm

/dev/sda1       486M   41M  421M   9% /boot

/dev/sdb1       826G  414G  370G  53% /db2

[manumohandas82]

Hi Yeshwaraj ,

The command was

db2 get snapshot for tablespaces on PR3 | grep -p  BTABD   ( small p , i wanted the entire paragraph )

Please tell me whether all you sapdata file systems is filled  ie 100% in the file system level  ( for that  tablespace )

put a df -g in your os  and send us the output

Thanks ,

Manu

[Former Member]

Hi manu

The output came for given query with p(small) was below

sandbox:db2pr3 59> db2 get snapshot for tablespaces on PR3 | grep -p  BTABD

                                      grep: invalid option -- 'p'

                               Usage: grep [OPTION]... PATTERN [FILE]...

                                Try `grep --help' for more information.

When i hit df -g it shows below

sandbox:/ # df -g

df: invalid option -- 'g'

Try `df --help' for more information.

when i went for --help, -g option was not there it shows below options

-a, --all             include dummy file systems

  -B, --block-size=SIZE  scale sizes by SIZE before printing them.  E.g.,

                           `-BM' prints sizes in units of 1,048,576 bytes.

                           See SIZE format below.

      --total           produce a grand total

  -h, --human-readable  print sizes in human readable format (e.g., 1K 234M 2G)

  -H, --si              likewise, but use powers of 1000 not 1024

  -i, --inodes          list inode information instead of block usage

  -k                    like --block-size=1K

  -l, --local           limit listing to local file systems

      --no-sync         do not invoke sync before getting usage info (default)

  -P, --portability     use the POSIX output format

      --sync            invoke sync before getting usage info

  -t, --type=TYPE       limit listing to file systems of type TYPE

  -T, --print-type      print file system type

  -x, --exclude-type=TYPE   limit listing to file systems not of type TYPE

  -v                    (ignored)

      --help     display this help and exit

      --version  output version information and exit

[former_member182657]

Hi,

db2 alter table space <table space name>  add (FILE '<PATH OF CONTAINER>'  <SIZE>)

Instead of above could you try below

db2 "alter table space <table space name>  add (FILE '<PATH OF CONTAINER>'  <SIZE>)"

Regards,

[manumohandas82]

Why would you extend if Automatic storage is already enabled ?

Thanks ,

Manu

[former_member182657]

Table space "PR3#BTABD" of type "AUTOMATIC STORAGE" cannot bealtered using the "ADD" operation.  SQLSTATE=42858

Yes he need not to use db2 alter table space <table space name>  add (FILE '<PATH OF CONTAINER>'  <SIZE>)

as automatic storage is already enabled her for the table space.

Thanks,

[Frank-Martin]

Hi,

if you are using automatic storage you need to extend your storage paths in your storage group or add new paths to your storage group.

Regards

                Frank

[former_member185954]

Correct, the error message clearly states that, the link I provided earlier describes the error and solution.

Yashwant,

you can find the syntax to execute the appropriate command on google.

[Former Member]

Hi Manu sir,

Thanks for your reply ASAP.

    Here the tablespace was complety (100%) filled that's why we are going to add another container to tablespace.

Is there any problem occurs with this 100% filled tablespace. Let me know.

[former_member182657]

Hi Frank,

Thanks for your valuable suggestion to us.

need to extend your storage paths in your storage group or add new paths to your storage group.

So here we can add new storage path to the storage group by using the ALTER STOGROUP statement with the ADD clause ? Kindly correct/suggest me for the statement .

Regards,

Gaurav

[manumohandas82]

Hi Siddesh ,

Your Link points to the a valid  resolution . But are you sure  that any issue exists as per his screenshot .

A tablespace  has reached 100% , Automatic storage is enabled

Is the Concerned file system full ? We dont know yet  .. right

Without knowing that why are we suggesting to extend / add new storage paths ?

Please correct me if i am wrong

Thanks ,

Manu

[former_member185954]

Hello Yashwanth,

At OS level try using a different command, check the link below:

IBM Knowledge Center

Regards,

Siddhesh

[manumohandas82]

Hi

Can you provide the following details

db2 get snapshot for tablespaces on <SID > | grep -p BTABD

Thanks ,

Manu

[Former Member]

Hi manu

Thanks for your reply ASAP.

The following was the output for the query you gave.

sandbox:db2pr3 58> db2 get snapshot for tablespaces on PR3 | grep -P BTABD

                              Tablespace name                            = PR3#BTABD