Question on space for a reorg

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

    Question on space for a reorg

    Running db2 8.1.1 on aix 5.1.

    Tried to do an offline reorg on the system tables and could not reorg
    SYSIBM.SYSCOLDI ST at about 3.5 million rows.

    Apparently there was not enough space in the default SYSCATSPACE
    tablespace in which DB2 could create the 'shadow' table.

    We are trying to determine the space we would need in a temp tablespace
    that would be specified in the reorg command. I know I've seen the
    formula before but can't seem to locate it.

    Is it a simple matter of computing number of rows times avg row length?

    Thanks in advance.
    Gerry

    Tags: None
    • Phil Sherman
      #2
      Re: Question on space for a reorg

      "Classic" reorg requires as much work space as the table occupies. If
      the reorged table is larger than the original; then the work space is
      the (new) larger size.

      I'd always add 10-20% extra to the workspace required.

      The newer "in-place" reorg allows access during the reorg and doesn't
      use very much work space. Unfortunately, it dows log all changes and can
      create a large number of log records.

      I'd opt for the classic version and add additional work space.

      Phil Sherman


      datapro01@yahoo .com wrote:[color=blue]
      > Running db2 8.1.1 on aix 5.1.
      >
      > Tried to do an offline reorg on the system tables and could not reorg
      > SYSIBM.SYSCOLDI ST at about 3.5 million rows.
      >
      > Apparently there was not enough space in the default SYSCATSPACE
      > tablespace in which DB2 could create the 'shadow' table.
      >
      > We are trying to determine the space we would need in a temp tablespace
      > that would be specified in the reorg command. I know I've seen the
      > formula before but can't seem to locate it.
      >
      > Is it a simple matter of computing number of rows times avg row length?
      >
      > Thanks in advance.
      > Gerry
      >[/color]

        Comment

        • Pierre Saint-Jacques
          #3
          Re: Question on space for a reorg

          If you have run stats on the table, the stat FPAGES gives you the number of
          pages (4k,8k,...) required to hold the table and therefore the minimum size
          of tempspace required.
          HTH, Pierre.

          --
          Pierre Saint-Jacques
          SES Consultants Inc.
          514-737-4515
          <datapro01@yaho o.com> a écrit dans le message de news:
          1126546188.9114 16.53210@f14g20 00...legro ups.com...[color=blue]
          > Running db2 8.1.1 on aix 5.1.
          >
          > Tried to do an offline reorg on the system tables and could not reorg
          > SYSIBM.SYSCOLDI ST at about 3.5 million rows.
          >
          > Apparently there was not enough space in the default SYSCATSPACE
          > tablespace in which DB2 could create the 'shadow' table.
          >
          > We are trying to determine the space we would need in a temp tablespace
          > that would be specified in the reorg command. I know I've seen the
          > formula before but can't seem to locate it.
          >
          > Is it a simple matter of computing number of rows times avg row length?
          >
          > Thanks in advance.
          > Gerry
          >[/color]

            Comment

            • datapro01@yahoo.com
              #4
              Re: Question on space for a reorg

              Many thanks

                Comment

                Previous template Next
                Working...