SQL1139N The total size of the table space is too big.

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • pravin.prabhakar@gmail.com
    #1

    SQL1139N The total size of the table space is too big.

    Hello,

    Our 4K index tablespace has grown to 64GB. It has maxed out. I want to
    increase the page size from 4 to 16K. What will be the safest method?
    Any suggestions? We are in DB2 UDB 8.2 Sun Solaris 8.

    Thanks.
    Tags: None
    • Mark A
      #2
      Re: SQL1139N The total size of the table space is too big.

      <pravin.prabhak ar@gmail.comwro te in message
      news:e506af01-7b7c-4853-84b6-9233059094f5@72 g2000hsu.google groups.com...
      Hello,
      >
      Our 4K index tablespace has grown to 64GB. It has maxed out. I want to
      increase the page size from 4 to 16K. What will be the safest method?
      Any suggestions? We are in DB2 UDB 8.2 Sun Solaris 8.
      >
      Thanks.
      You would have to drop the tables and recreate them so that the indexes
      point to a different tablespace. Obviously, you would need to unload the
      data before hand, and then reload it afterwards.

      For an OLTP system, you would better off dropping just some of the larger
      tables (with large indexes) and specifying a new 4K tablespace for the
      indexes in those tables, since a 4K tablespace (and bufferpool) are probably
      advisable over a larger page size.

      If you have a data warehouse, then you may want to use a larger page size.


        Comment

        • pravin.prabhakar@gmail.com
          #3
          Re: SQL1139N The total size of the table space is too big.

          Thanks for the reply. Since the application is Siebel, I dont think we
          will have the flexibility of dropping the table...

          On Feb 24, 9:20 am, "Mark A" <nob...@nowhere .comwrote:
          <pravin.prabha. ..@gmail.comwro te in message
          >
          news:e506af01-7b7c-4853-84b6-9233059094f5@72 g2000hsu.google groups.com...
          >
          Hello,
          >
          Our 4K index tablespace has grown to 64GB. It has maxed out. I want to
          increase the page size from 4 to 16K. What will be the safest method?
          Any suggestions? We are in DB2 UDB 8.2 Sun Solaris 8.
          >
          Thanks.
          >
          You would have to drop the tables and recreate them so that the indexes
          point to a different tablespace. Obviously, you would need to unload the
          data before hand, and then reload it afterwards.
          >
          For an OLTP system, you would better off dropping just some of the larger
          tables (with large indexes) and specifying a new 4K tablespace for the
          indexes in those tables, since a 4K tablespace (and bufferpool) are probably
          advisable over a larger page size.
          >
          If you have a data warehouse, then you may want to use a larger page size.

            Comment

            • Mark A
              #4
              Re: SQL1139N The total size of the table space is too big.

              ><pravin.prabha kar@gmail.comwr ote in message
              >news:786e340 b-8218-4ac6-966f->7eea6112bc54@6 2g2000hsn.googl egroups.com...
              >Thanks for the reply. Since the application is Siebel, I dont think we
              >will have the flexibility of dropping the table...
              Read my lips. You have no choice.

              You cannot move the table or indexes to a new tablespace unless you drop and
              recreate the table. So long as you do this when the application is not
              running (and you have a competent DB2 DBA who will take into account the
              referential integrity issues) it does not matter whether the application is
              Siebel or anything else.


                Comment

                • pravin.prabhakar@gmail.com
                  #5
                  Re: SQL1139N The total size of the table space is too big.

                  On Feb 24, 10:31 am, "Mark A" <nob...@nowhere .comwrote:
                  <pravin.prabha. ..@gmail.comwro te in message
                  news:786e340b-8218-4ac6-966f->7eea6112b__BEG IN_MASK_n#9g02m G7!__...__END_M ASK_i?a63jfAD$z __@62g2000hsn.g ooglegroups.com ...
                  Thanks for the reply. Since the application is Siebel, I dont think we
                  will have the flexibility of dropping the table...
                  >
                  Read my lips. You have no choice.
                  >
                  You cannot move the table or indexes to a new tablespace unless you drop and
                  recreate the table. So long as you do this when the application is not
                  running (and you have a competent DB2 DBA who will take into account the
                  referential integrity issues) it does not matter whether the application is
                  Siebel or anything else.
                  Thanks for your reply. Greatly appreciated. I guess we dont have other
                  options.

                    Comment

                    • pravin.prabhakar@gmail.com
                      #6
                      Re: SQL1139N The total size of the table space is too big.

                      Thanks for the reply. Your response is greatly appreciated. All tables
                      have multi million rows....

                      On Feb 24, 10:31 am, "Mark A" <nob...@nowhere .comwrote:
                      <pravin.prabha. ..@gmail.comwro te in message
                      news:786e340b-8218-4ac6-966f->7eea6112b__BEG IN_MASK_n#9g02m G7!__...__END_M ASK_i?a63jfAD$z __@62g2000hsn.g ooglegroups.com ...
                      Thanks for the reply. Since the application is Siebel, I dont think we
                      will have the flexibility of dropping the table...
                      >
                      Read my lips. You have no choice.
                      >
                      You cannot move the table or indexes to a new tablespace unless you drop and
                      recreate the table. So long as you do this when the application is not
                      running (and you have a competent DB2 DBA who will take into account the
                      referential integrity issues) it does not matter whether the application is
                      Siebel or anything else.

                        Comment

                        • Lew
                          #7
                          Re: SQL1139N The total size of the table space is too big.

                          A standard practice we used to use for situations like this for moving
                          tables to a new tablespace is to create a table with the exact same
                          definition and indexes in a new tablespace with a different name.
                          Then we would do a load from cursor into the new table from the
                          existing table, rename the old table to something and then rename the
                          new table to the proper table name.

                          create table tab1_new in ts_new
                          declare cursor mycur for select * from tab1
                          load from mycur of cursor insert into tab1_new
                          rename table tab1 to tab1_old
                          rename table tab1_new to tab1
                          drop table tab1_old

                          This might make it a bit less painful

                            Comment

                            Previous template Next
                            Working...