Topic
  • 16 replies
  • Latest Post - ‏2013-11-05T06:43:19Z by 9RYQ_Kiran_mk
Michael_Moloney
Michael_Moloney
13 Posts

Pinned topic DB2 Increasing tablespace size.

‏2013-10-18T08:12:54Z |

Hi,

I need to increase the size of my tablespaces (and all containers therein) by 50% in my DB2 database using the alter tablespace command .

I have run the following command but I get an error :-

[db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend all increasesize integer 50

and get the error:-

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0104N An unexpected token "all increasesize integer 50" was found

following "e SYSCATSPACE extend". Expected tokens may include: "<space>".

Please help !!

Regards,

Michael.

  • Amyris
    Amyris
    12 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-18T12:29:01Z  

    Hi there,

    If you look at the syntax for alter table space, you can use the extend clause or the increasesize but not both:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000890.html

     

    You will find examples of how to use extend all or increasesize in the same topic.

    Hope this helps,

    Amyris

  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-21T12:36:49Z  
    • Amyris
    • ‏2013-10-18T12:29:01Z

    Hi there,

    If you look at the syntax for alter table space, you can use the extend clause or the increasesize but not both:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000890.html

     

    You will find examples of how to use extend all or increasesize in the same topic.

    Hope this helps,

    Amyris

    Hi Amyris,

    Thanks for you reply.

    However I cannot work out the correct syntax for the alter tablespace commmand.

    I am new to DB2 and learning as I go along.

    I would appreciate if you were to write out the command for me to extend all the containers in the following table space by 50%

    Table space is :-

    SYSCATSPACE

    and the database is :-

    DATAWHSD

    See the attachment for full details.

    Thanks,

    Michael.

     

  • Amyris
    Amyris
    12 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-21T14:08:23Z  

    The following examples from the topic shows exactly how to do it just change the tb space name:

    • Example 4: Extend all of the containers in the DATA_TS table space by 1000 pages.
      
       
      ALTER TABLESPACE DATA_TS     
      EXTEND (ALL 1000
      )
      
    • Example 10: Change the growth rate for an auto-resizable table space named MY_TS to 512 kilobytes, and set its maximum size to be as large as possible.
      
       
      ALTER TABLESPACE MY_TS     
      INCREASESIZE 512 
      K MAXSIZE NONE
      

    Hope this helps,

    Amyris.

    Updated on 2013-10-21T14:09:13Z at 2013-10-21T14:09:13Z by Amyris
  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-28T13:36:51Z  

    Hi Amyris,

    I ran the command as you advised and it completed successfully.

    I added 50 M to tablespace 'SYSCATSPACE' on database 'DATAWHSD'

    The command completed successfully however the tablespace size remains unchanged.

    Command run:-

    db2 alter tablespace SYSCATSPACE increasesize 50 M maxsize none

    See attachment for db2cc Table space output.

    Regards,

    Michael.

    Attachments

  • Amyris
    Amyris
    12 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-28T19:58:47Z  

    Hi Amyris,

    I ran the command as you advised and it completed successfully.

    I added 50 M to tablespace 'SYSCATSPACE' on database 'DATAWHSD'

    The command completed successfully however the tablespace size remains unchanged.

    Command run:-

    db2 alter tablespace SYSCATSPACE increasesize 50 M maxsize none

    See attachment for db2cc Table space output.

    Regards,

    Michael.

    Michael,

    I specified both options because you have them together in your posting and I was not sure what you want to do. The increasesize is for tbs that have been enable for auto-resize and it will only happen when more space is needed.

    If you want to increase the size use the example #4. Sorry for the confusion.

    Hope this helps,

    Amyris.

    Updated on 2013-10-28T20:00:21Z at 2013-10-28T20:00:21Z by Amyris
  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-29T07:46:40Z  
    • Amyris
    • ‏2013-10-28T19:58:47Z

    Michael,

    I specified both options because you have them together in your posting and I was not sure what you want to do. The increasesize is for tbs that have been enable for auto-resize and it will only happen when more space is needed.

    If you want to increase the size use the example #4. Sorry for the confusion.

    Hope this helps,

    Amyris.

    As per my understanding and knowledge so far, SYSCATSPACE is an SMS Tablespace. SMS Tablespace automatically increases and shrinks the tablespace size as the name itself suggests (System Managed Space).  So no point in managing manually.  

    The only way to make sure of the Tablespace Size availability is to have more space on the filesystem where the SYSCATSPACE tablespace resides.

    If the SYSCATSPACE  tablespace becomes full you cannot add one more container you can only increase the file system space.

    Best Regards,

    Kiran.M.K.

  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-29T12:49:18Z  

    Hi Kiran and Amyris,

    SYSCATSPACE is a Database managed space

     Tablespace ID                        = 0
     Name                                       = SYSCATSPACE
     Type                                          = Database managed space
     Contents                                  = All permanent data. Regular table space.
     State                                         = 0x0000
     

    When I run the alter tablespace command with the increasesize option it runs ok but no size change is shown in the DB2 Control Centre.

    Is this how it should be ??

    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE increasesize 40 M maxsize none
    DB20000I  The SQL command completed successfully.

    When I run the command with the extend option I get :-

    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend (ALL 1000)
    -bash: syntax error near unexpected token `('
    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend ALL 1000
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "ALL" was found following "e SYSCATSPACE
    extend".  Expected tokens may include:  "(".  SQLSTATE=42601
    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend 1000
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "1000" was found following "e SYSCATSPACE
    extend".  Expected tokens may include:  "<alter_container_defn>".
     

    Have I got the syntax correct ??

    Regards,

    Michael.

     

  • Amyris
    Amyris
    12 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-29T20:38:35Z  

    Hi Kiran and Amyris,

    SYSCATSPACE is a Database managed space

     Tablespace ID                        = 0
     Name                                       = SYSCATSPACE
     Type                                          = Database managed space
     Contents                                  = All permanent data. Regular table space.
     State                                         = 0x0000
     

    When I run the alter tablespace command with the increasesize option it runs ok but no size change is shown in the DB2 Control Centre.

    Is this how it should be ??

    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE increasesize 40 M maxsize none
    DB20000I  The SQL command completed successfully.

    When I run the command with the extend option I get :-

    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend (ALL 1000)
    -bash: syntax error near unexpected token `('
    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend ALL 1000
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "ALL" was found following "e SYSCATSPACE
    extend".  Expected tokens may include:  "(".  SQLSTATE=42601
    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend 1000
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "1000" was found following "e SYSCATSPACE
    extend".  Expected tokens may include:  "<alter_container_defn>".
     

    Have I got the syntax correct ??

    Regards,

    Michael.

     

    hi there,

    What system are you using?

    I would use quotes to make sure that ( is not being expanded:

    db2 "alter tablespace SYSCATSPACE extend (ALL 1000)"

    Hope this helps,

    Amyris

  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-30T06:00:32Z  

    Hi Kiran and Amyris,

    SYSCATSPACE is a Database managed space

     Tablespace ID                        = 0
     Name                                       = SYSCATSPACE
     Type                                          = Database managed space
     Contents                                  = All permanent data. Regular table space.
     State                                         = 0x0000
     

    When I run the alter tablespace command with the increasesize option it runs ok but no size change is shown in the DB2 Control Centre.

    Is this how it should be ??

    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE increasesize 40 M maxsize none
    DB20000I  The SQL command completed successfully.

    When I run the command with the extend option I get :-

    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend (ALL 1000)
    -bash: syntax error near unexpected token `('
    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend ALL 1000
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "ALL" was found following "e SYSCATSPACE
    extend".  Expected tokens may include:  "(".  SQLSTATE=42601
    [db2inst1@forktail1 ~]$ db2 alter tablespace SYSCATSPACE extend 1000
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "1000" was found following "e SYSCATSPACE
    extend".  Expected tokens may include:  "<alter_container_defn>".
     

    Have I got the syntax correct ??

    Regards,

    Michael.

     

    Hi Michael,

    Can you please provide the output of db2pd -db <dbname> -tablespaces

  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-30T11:05:52Z  
    • Amyris
    • ‏2013-10-29T20:38:35Z

    hi there,

    What system are you using?

    I would use quotes to make sure that ( is not being expanded:

    db2 "alter tablespace SYSCATSPACE extend (ALL 1000)"

    Hope this helps,

    Amyris

    Hi Amiris,

    I am using DB2 9.7 on 64 bit Linux.

    I ran the command as you suggested and got :-

    [db2inst1@forktail1 ~]$ db2 "alter tablespace SYSCATSPACE extend (ALL 1000)"
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL20318N  Table space "SYSCATSPACE" of type "AUTOMATIC STORAGE" cannot be
    altered using the "EXTEND" operation.  SQLSTATE=42858
     

    So the command works but as the table spaces are of type "AUTOMATIC STORAGE" the extend option is not permitted.

    In a previous post you advised :-

    The increasesize is for tbs that have been enable for auto-resize and it will only happen when more space is needed.

    Is there any way I can see how much ithe tablespace can grow to ??

    Regards,

    Michael.

     

     

  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-30T11:49:11Z  

    Hi Michael,

    Can you please provide the output of db2pd -db <dbname> -tablespaces

    Hi Kiran,

    Please find the output of db2pd below.

    [db2inst1@forktail1 ~]$ db2 list active databases

                               Active Databases

    Database name                              = C10CONTD
    Applications connected currently           = 32
    Database path                              = /db2data/C10CONTD/db2inst1/NODE0000/SQL00001/

    Database name                              = DATAWHST
    Applications connected currently           = 0
    Database path                              = /db2data/db2inst1/NODE0000/SQL00005/

    Database name                              = COGAUDD
    Applications connected currently           = 1
    Database path                              = /db2data/db2inst1/NODE0000/SQL00003/

    Database name                              = DATAWHSD
    Applications connected currently           = 7
    Database path                              = /db2data/db2inst1/NODE0000/SQL00002/

    [db2inst1@forktail1 ~]$ db2 activate database DATAWHSD
    SQL1494W  Activate database is successful, however, there is already a
    connection to the database.
    [db2inst1@forktail1 ~]$ db2 activate database DATAWHST
    SQL1490W  Activate database is successful, however, the database has already
    been activated on one or more nodes.
    [db2inst1@forktail1 ~]$
    [db2inst1@forktail1 ~]$ db2pd -db WHSD -tablespaces

    Database WHSD not activated on database partition 0.
    Another possibility of this failure is the Virtual Address Space Randomization is currently enabled on this system.

    Option -tablespaces requires -db <database> or -alldbs option and active database.

    Regards,

    Michael.

     

  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-31T07:07:18Z  

    Hi Kiran,

    Please find the output of db2pd below.

    [db2inst1@forktail1 ~]$ db2 list active databases

                               Active Databases

    Database name                              = C10CONTD
    Applications connected currently           = 32
    Database path                              = /db2data/C10CONTD/db2inst1/NODE0000/SQL00001/

    Database name                              = DATAWHST
    Applications connected currently           = 0
    Database path                              = /db2data/db2inst1/NODE0000/SQL00005/

    Database name                              = COGAUDD
    Applications connected currently           = 1
    Database path                              = /db2data/db2inst1/NODE0000/SQL00003/

    Database name                              = DATAWHSD
    Applications connected currently           = 7
    Database path                              = /db2data/db2inst1/NODE0000/SQL00002/

    [db2inst1@forktail1 ~]$ db2 activate database DATAWHSD
    SQL1494W  Activate database is successful, however, there is already a
    connection to the database.
    [db2inst1@forktail1 ~]$ db2 activate database DATAWHST
    SQL1490W  Activate database is successful, however, the database has already
    been activated on one or more nodes.
    [db2inst1@forktail1 ~]$
    [db2inst1@forktail1 ~]$ db2pd -db WHSD -tablespaces

    Database WHSD not activated on database partition 0.
    Another possibility of this failure is the Virtual Address Space Randomization is currently enabled on this system.

    Option -tablespaces requires -db <database> or -alldbs option and active database.

    Regards,

    Michael.

     

    I think you are giving the wrong db name for db2pd command

     

    You have activated DATAWHSD Database and taking the db2pd out for different db (WHSD).

    I think u r looking for DATAWHSD database right.

    db2pd -db DATAWHSD -tablespaces.

    Best Regards,

    Kiran.M.K.

  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-10-31T10:18:59Z  

    I think you are giving the wrong db name for db2pd command

     

    You have activated DATAWHSD Database and taking the db2pd out for different db (WHSD).

    I think u r looking for DATAWHSD database right.

    db2pd -db DATAWHSD -tablespaces.

    Best Regards,

    Kiran.M.K.

    Hi Kiran,

    Please find the output attachec.

    Regards,

    Michael.

     

    Attachments

  • Amyris
    Amyris
    12 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-11-04T13:26:48Z  

    Hi Amiris,

    I am using DB2 9.7 on 64 bit Linux.

    I ran the command as you suggested and got :-

    [db2inst1@forktail1 ~]$ db2 "alter tablespace SYSCATSPACE extend (ALL 1000)"
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL20318N  Table space "SYSCATSPACE" of type "AUTOMATIC STORAGE" cannot be
    altered using the "EXTEND" operation.  SQLSTATE=42858
     

    So the command works but as the table spaces are of type "AUTOMATIC STORAGE" the extend option is not permitted.

    In a previous post you advised :-

    The increasesize is for tbs that have been enable for auto-resize and it will only happen when more space is needed.

    Is there any way I can see how much ithe tablespace can grow to ??

    Regards,

    Michael.

     

     

    Hi there,

    If you are using automatic storage, you just need to make sure you have space on your database storage paths, or add a storage path so that there is more space available. You do not have to manually do it, that is the idea of automatic storage.

    Here is a link to an scenario for adding storage paths:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054955.html

    Here is a link to a topic that explains how automatic storage table spaces work:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0054960.html

     

    Hope this helps,

    Amyris

     

     

     

  • Michael_Moloney
    Michael_Moloney
    13 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-11-04T15:06:37Z  
    • Amyris
    • ‏2013-11-04T13:26:48Z

    Hi there,

    If you are using automatic storage, you just need to make sure you have space on your database storage paths, or add a storage path so that there is more space available. You do not have to manually do it, that is the idea of automatic storage.

    Here is a link to an scenario for adding storage paths:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054955.html

    Here is a link to a topic that explains how automatic storage table spaces work:

    http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0054960.html

     

    Hope this helps,

    Amyris

     

     

     

    Hi Amyris,

    Thanks for your reply. It was most helpful.

    Regards,

    Michael.

     

  • 9RYQ_Kiran_mk
    9RYQ_Kiran_mk
    78 Posts

    Re: DB2 Increasing tablespace size.

    ‏2013-11-05T06:43:19Z  

    Hi Kiran,

    Please find the output attachec.

    Regards,

    Michael.

     

    Hi Michael,

    As per the attachment for the database tablespaces, it clearly shows that the tablespaces are Automatic Storage Enabled.  So no need to worry for increasing the size of the tablespaces, as in Automatic Storage Tablespaces the space is managed by its own and it uses the space available in the mount point or file system of the DB Storage Path.

    So here it will not allow you to extend the Tablespace Size but you can set the "increase size"  which will be invoked when the Last extent of the Tablespace container is reached and it automatically extends based on the INCREASE SIZE Specified.  MAXSIZE NONE specifies that tablespace can grow upto the file system space left.

    In the below output AS refers to Automatic Storage and AR refers to Automatic Resize which will be resized based on INCREASE SIZE specified or altered. 

    Tablespace Autoresize Statistics:

    Address                              Id    AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF

    0x00002ADDAC3E35A0 0     Yes Yes 33554432    52428800    No  None        None                       No

    0x00002ADDAC3E4CA0 1     Yes No  0           0           No  0                    None                       No

    0x00002ADDAC3E85C0 2     Yes Yes 33554432    -503316480  No  None        None                       No

    0x00002ADED675C0A0 3     Yes Yes 33554432    -1          No  None        None                       No

    0x00002ADED675D560 4     Yes No  0           0           No  0                    None                       No

    0x00002ADED6760E80 5     Yes No  0           0           No  0                    None                       No

    0x00002ADED67647A0 6     Yes Yes 33554432    -1          No  None        None                       No

    0x00002ADED67660A0 7     Yes No  0           0           No  0                    None                       No

     

    Please let me know in case of any doubt.

     

    Thanks for the understanding.

     

    Best Regards,

    Kiran.M.K.