Automatic Storage The Future of Storage Management

Document Sample
Automatic Storage The Future of Storage Management Powered By Docstoc
					                                    Session: C10

                                    DB2 Automatic Storage : The
                                    Future of Storage Management in
                                    DB2 for Linux, Unix and Windows


                                    Matt Huras, Kelly Schlamb, IBM




                                    Wed, Oct 4, 1:30 pm – 2:30 pm


                                    Platform: DB2 for Linux Unix and Windows



                                                                                 1




Automatic Storage has been recently introduced into DB2 LUW to significantly reduce the human
costs of storage administration. This presentation reviews the goals, externals and motivations
of Automatic Storage, and then dives into the details of the implementation. It then overviews
the recommended best practices for deployment, and winds up with a view of the latest
enhancements to Automatic Storage in DB2 9.




                                                                                                  1
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    2




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips. Key bullet points:
-Auto-resize tablespaces
-Automatic storage
-Montoring, Recovery, Migration
-Hints & Topics
-DB2 9 and Beyond




                                                                                                       2
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    3




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                        3
               What are Auto-Resize Tablespaces ?

                   DMS tablespaces with file containers that automatically extend

                     Introduced in DB2 V8.2.2 (V8 FP9)
                     Applies to DMS file tablespaces only; tablespaces with raw device
                     containers cannot be enabled for auto-resize



                   Instead of getting an “unable to allocate new pages” error, DB2 will
                   automatically grow the last range of containers in the tablespace

                     Auto-growth will only occur if there is space in the file system(s) for the
                     container(s) to grow.
                     An error will be returned to the caller if this cannot be done (SQL0289N –
                     unable to allocate new pages)                                                 4




< No notes for this slide >




                                                                                                       4
Auto-Resize Tablespaces
  Without Auto-Resize                                                                  User

   Tablespace A
                     1.   -289 returned from SQL                     Tablespace A
                     2.   Page out administrator
                     3.   Administrator RESIZEs container(s)
                     4.   Notifies users that space has been added
      Container
         Container
   Container         5.   Operations resume
                                                                           Container
                                                                        Container
                                                                     Container




                                                                                       Admin

  With Auto-Resize                                                                      User

   Tablespace A
                          DB2 automatically RESIZEs                  Tablespace A


                          containers (no -289 returned)

         Container
      Container
   Container

                                                                           Container
                                                                        Container
                                                                     Container




                                                                                       Admin
                                                                                               5




                                                                                                   5
               Aside : Why DMS File ?
                       Why did we base the new Storage features on DMS File?

                       At a high-level, tradeoffs involved:
                           Outstanding performance of DMS Raw                                    - vs -
                           Very good performance and flexibility of DMS File                     - vs-
                           Outstanding manageability of SMS

                       Technology Trend : significant advances are being made in file systems
                           Eg. Direct/concurrent I/O capabilities of many file systems
                           Performance gap between file systems and raw devices is narrowing
                           Over time, use of raw devices is likely to diminish in favor of file systems
                                Result: Ease of file system management but with the performance of raw

                       Idea : get best of both worlds via DMS File
                           Near-raw performance with DIO/CIO
                           Flexibility of DMS functionality
                           Manageability of file systems
                           On-demand growth as in SMS


                                                                                                          6




< No notes for this slide >




                                                                                                              6
               New CREATE/ALTER TABLESPACE Syntax

                      New syntax introduced for CREATE and ALTER TABLESPACE:

                        CREATE TABLESPACE <tsName> MANAGED BY DATABASE
                          USING (<listOfFileContainers>)
                          [AUTORESIZE {NO|YES}] [INCREASESIZE integer {PERCENT|K|M|G}]
                          [MAXSIZE {NONE | integer {K|M|G}}]


                        ALTER TABLESPACE <tsName>
                          [AUTORESIZE {NO|YES}] [INCREASESIZE integer {PERCENT|K|M|G}]
                          [MAXSIZE {NONE | integer {K|M|G}}]


                      Existing syntax is still supported
                        Default is auto-resize disabled


                      In multi-partition databases (DPF), values provided are per-partition

                                                                                              7




The AUTORESIZE option is only allowed for DMS tablespaces in which all of the containers are
files. Any attempt to specify this option for SMS tablespaces or for DMS tablespaces with raw
devices will result in an error. The INCREASESIZE and MAXSIZE options can only be specified
when AUTORESIZE is enabled.


As pointed out on the slide, if you do not explicitly choose to use the new syntax then you will be
running with the pre-V8.2.2 behavior (the tablespace will not auto-resize).




                                                                                                      7
               New CREATE/ALTER TABLESPACE Syntax (cont.)
                        INCREASESIZE specifies the amount of space to automatically
                        add to the tablespace when it becomes full
                          Specified as an explicit size (e.g. 64 M) or as a percentage of the size of
                          the tablespace when the growth occurs (e.g. 10 PERCENT)


                        MAXSIZE specifies the maximum size that the tablespace is
                        allowed to grow to
                          NONE means “unlimited growth” (practically, there are still DB2 limits on
                          tablespace size and space available to the file system(s))
                          Also limits how much space can be added via user-initiated container
                          operations

                        If AUTORESIZE is enabled (YES) but no values are specified, the
                        defaults are:
                          INCREASESIZE = 32 M , MAXSIZE = NONE (“unlimited”)

                                                                                                        8




With respect to using a percentage for increase size, this means that whenever DB2 must
automatically extend the tablespace, it will use the percentage to calculate the amount of growth
at that particular time. For example, if a tablespace is created with a size of 100 MB, auto-resize
is enabled, and the increase size is 20%, the growth will actually occur with the following
amounts: 20 MB (20% of 100 MB), 24 MB (20% of 120 MB), 28.8 MB (20% of 144 MB), etc.


Note the comment that states that MAXSIZE limits how much space can be added via user-
initiated container operations. For auto-resize disabled tablespaces, you can add as much space
as you want to the tablespace using the ADD, BEGIN NEW STRIPE SET, EXTEND, and
RESIZE options of the ALTER TABLESPACE statement (up to the DB2 tablespace size limit of
course). However, because the MAXSIZE option is a hard cap on space usage for auto-resize
enabled tablespaces, that hard cap applies to container operations as well. For example, if you
have a tablespace that is 190 MB in size with a maximum size of 200 MB, you will not be able to
add a 100 MB container to the tablespace. If you did want to add a 100 MB container to the
tablespace for some reason, you either have to turn auto-resize off or increase the maximum
size first. It is important to note that while a container operation (and subsequent rebalance) is
performed, the auto-resize capability is temporarily disabled. It will be re-enabled once the
operation and rebalance (if needed) completes.


DB2 may not use exactly the values specified by the user. When extending containers, DB2 will
extend them in units of extents which means that DB2 may extend by a slightly smaller or larger
amount than what was specified. Likewise, DB2 may not be able to reach a specified maximum
size exactly. And because the maximum size is a hard limit, it may not be possible to reach the
maximum size exactly. As a result, DB2 may return a “no available pages” error when the
maximum size hasn’t been reached exactly, but the tablespace size is close to that maximum.
                                                                                                            8
               Examples
                       CREATE TABLESPACE TS1 MANAGED BY DATABASE
                         USING (FILE ‘TS1’ 1000) AUTORESIZE YES


                       CREATE TABLESPACE TS2 MANAGED BY DATABASE
                         USING (FILE ‘/dir/TS2/C0’ 100 M, FILE ‘dir/TS2/C1’ 100 M)
                         AUTORESIZE YES    INCREASESIZE 50 M     MAXSIZE 1 G


                       CREATE TABLESPACE TS3 MANAGED BY DATABASE
                         USING (FILE ‘D:\TS3’ 2000)
                       ALTER TABLESPACE TS3
                         AUTORESIZE YES     INCREASESIZE 50 PERCENT   MAXSIZE NONE


                       CREATE TABLESPACE TS4 MANAGED BY DATABASE
                         USING (FILE ‘TS4’ 200 M)    AUTORESIZE YES
                       ALTER TABLESPACE TS4    AUTORESIZE NO


                                                                                     9




Scenario #1: Table space TS1 is being created with a single container that is 1000 pages in size
(x 4 KB (the default page size) for a total of 4000 KB). Auto-resize is being enabled with a
default increase size of 32 MB and a default maximum size of NONE (it will grow until DB2’s
tablespace size limit of 64 GB has been reached or until container ‘TS1’ is unable to grow for
whatever reason (e.g. ulimit, no space left on file system, etc.).


Scenario #2: Table space TS2 is being created with 2 containers, each 100 MB in size (for a total
of 200 MB). The increase size is 50 MB which means that when the tablespace needs to grow,
the containers will be extended by approximately 25 MB each. Auto-growth will occur until a size
of 1 GB (of course, the growth only occurs as necessary, so if objects within the tablespace don’t
consume that amount of space, the tablespace will not actually get to be that big).


Scenario #3: Table space TS3 is being created with a single container that is 2000 pages in size
(x 4 KB (the default page size) for a total of 8000 KB). Auto-resize is disabled (the default).
However, auto-resize is being subsequently enabled using the ALTER TABLESPACE statement.
In this case, the maximum size is NONE (unlimited growth) and the increase size is 50%. As
space is completely consumed in the tablespace it will grow by 4000 KB (50% of 8000 KB), then
by 6000 KB (50% of 12000 KB), then by 9000 MB (50% of 18000 KB), etc.


Scenario #4: Table space TS4 is being created with auto-resize enabled. It uses the default
values for the increase size and maximum size. However, it is then being disabled using the
AUTORESIZE NO option of ALTER TABLESPACE.



                                                                                                     9
               How Growth Occurs
                      Table space will auto-extend when it is full and more space is needed
                        Can use fast storage allocation where available (eg. AIX JFS2, Windows)


                      Only those containers that are part of the last range in the tablespace
                      map will grow (see next slide for an explanation of ranges)
                        Ensures that a rebalance will never take place as part of an auto-resize


                      Auto-growth will stop when any of the following happen:
                        The value specified for MAXSIZE is reached

                        One of the containers in the last range cannot grow any further

                          – To preserve the level of striping, DB2 will not automatically extend the
                            other containers in the last range

                          – To continue growth, can add space to file system, or a new stripe set      10




With respect to the point on not extending the other containers in the last range when one of
them is full, this is done because the user may wish to maintain a certain level of striping across
the containers and this would violate that.


It will be discussed in an upcoming slide, but there are a few ways to “kick start” the auto-growth
when a container in the last range becomes full. This includes extending the file system on
which the container resides (so it can start growing again), extending one or more of the
containers in the last range (so that a new range is created), or adding a new stripe set of
containers (which also creates a new range in the tablespace map – and these new containers
will become the ones that auto-extensions will occur on from here on).




                                                                                                            10
              A Quick Primer On Ranges

                          Every DMS tablespace has a “map” that describes the logical and physical layout of
                          the tablespace
                          A range is a region of this map where the striping involves a unique set of containers

                           CREATE TABLESPACE TS1 MANAGED BY DATABASE
                             USING (FILE 'cont0‘ 60, FILE 'cont1' 60, FILE 'cont2' 40)
                             EXTENTSIZE 10     (remember: 1 extent per container is used to hold a tag)


                                  Containers
                                                               Range     Stripe   Max       Start    End      Containers
                              0       1        2               Number    Set      Extent    Stripe   Stripe
                                                                  0         0        8         0        2     3 (0, 1, 2)
                     0        0       1        2
                                                                  1         0        12        3        4     2 (0, 1)
                     1        3       4        5   Range #0
              Stripes 2       6       7        8
                     3        9      10
                                                   Range #1
                     4       11      12
                                                                        * Using containers that are of differing sizes is not
                                                                        recommended – this is simply shown as an example

                                                                                                                                11




The map shown here (not the diagram representation) is very similar to what you see in the
output of GET SNAPSHOT FOR TABLESPACES. Certain fields (maximum page, stripe offset,
adjustment) have been removed for readability.




                                                                                                                                     11
               Example of Auto-Growth Stopping
                                                                                                  How do you kick start
                                                                                                   auto-growth again?
               Auto-resize
               tablespace                                                                         1. Make more room
               created with        C0   C1                                                           available on the file
                                                          C0        C1
               2 containers                                                           C0    C1       system holding C1
                                             Table                           Table
                                             space                           space                2. Add a new stripe set
                                             grows                           grows                   (recommended if #1
                                                                                                     not possible)
                                                                                                  3. Extend C0 by some
                                                                                                     amount (reduces
                                                                                                     striping)


               1)                       2)                     Adding the new         3)              Extending C0 results
                                                               stripe set here                        in a new range being
                                                               results in a new                       created that holds
                                                               range being created                    only that one
                    C0        C1             C0      C1        in the tables space         C0    C1   container. Hence,
                                                               map. Hence, auto-                      auto-resize will only
                                                               resize will only                       extend that one
                                                               extend these new                       container.
                                                               containers from here
                                                               on.
                                             C2      C3



                                                                                                                              12




In this slide, the dashed boxes represent file systems and the amount of space they have
available to them. As you can see, as growth occurs (and this growth occurs at the same rate for
C0 and C1, keeping them at the same size) the file system holding C1 becomes full. At this
point, the tablespace will no longer grow automatically.


Case #1: In this case, free space is being made on the file system holding C1. This can occur by
deleting non-DB2 files that may exist, or by extending the file system in size (platform
dependant). Since C0 and C1 are the containers in the last range of the tablespace map and
there is now free space on each of the file systems holding them, when an auto-resize operation
is attempted, it will be successful.


Case #2: In this case, the user is deciding that they want to maintain the same amount of striping
so they add two new containers to the tablespace using the BEGIN NEW STRIPE SET option of
ALTER TABLESPACE. The result of this is that new space is added to the tablespace and the
data that currently exists in the tablespace will *not* be rebalanced onto the new containers. And
of course, a new stripe set (which in this case is a single new range) is added to the tablespace
map. So, when an auto-resize operation is next attempted, it will try to resize those containers
that are in the last range of the map, which happens to be C2 and C3 in this case.


Case #3: In this case, container C0 is being extended by some amount of space (minimum would
need to be one extent). In doing this, a new range is added to the tablespace map and this
range will only contain container C0. Therefore, when an auto-resize operation is attempted next
by DB2, it will only attempt to grow container C0. Because there is free space available on that
file system, the container extension should succeed.
                                                                                                                                   12
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    13




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         13
               What is Automatic Storage ?
                      New storage management technique where storage for multiple
                      tablespaces is automatically managed at the database level

                              Multiple tablespaces automatically draw increments of storage from a
                              “database storage pool” on demand
                              Removes need to watch out for disk shortages in each individual
                              tablespace
                              Removes need to manually enlarge containers or add stripe sets
                              Uses DMS infrastructure internally : combines performance benefits of
                              DMS infrastructure with manageability benefits of SMS


                      Tablespaces retain their other properties which can be useful for
                      logical grouping of tables and objects, eg:

                              Combining logically related tables in the same tablespace so that they
                              can be recovered together
                              Separating logically unrelated tables in different tablespaces so they can
                              be recovered independently
                              Placing the objects of a table (data, index, long) in separate bufferpools
                                                                                                           14




< No notes for this slide >




                                                                                                                14
               Single Point of Storage Management

                                                                       Automatic Storage

                          Non-Automatic Storage
                                                                         Database “Y”

                                Database “X”
                                                             Table           Table           Table
                                                           Space “A”       Space “B”       Space “C”
                      Table        Table         Table
                    Space “A”    Space “B”     Space “C”



                                                                 Storage paths on file systems




                                                                                                       15




< No notes for this slide >




                                                                                                            15
               On the Motivation for Automatic Storage …

                 Spreading objects across multiple tablespaces can be very useful :
                   Granularity of recovery (convenient to store objects that need to be backed up / restored
                   together in the same tablespace)
                   Able to bind objects to specific buffer pools
                   Work around limitations for maximum tablespace size and maximum number of objects



                 At times, however, multiple points of storage management for tablespaces can
                 cause administration headaches :
                   Need to know before hand how to distribute the available space between tablespaces
                     Often hard to predict as data population patterns are not accurately known before hand
                   Each tablespace needs to be administered separately for space
                     Need to ADD / RESIZE containers on a per tablespace basis as each tablespace grows
                     and becomes full
                     Applications may fail until the DBA manually adds more space to the tablespace

                                                                                                               16




< No notes for this slide >




                                                                                                                    16
              Automatic Storage Functionality

                 Automatic Storage allows you to …

                   Create a database and associate a set of storage paths with it

                   Add storage paths to the database after creation

                   Create AUTOMATIC STORAGE tablespaces
                     No explicit container definitions are provided
                     Containers automatically created across the database storage paths
                     Growth of existing containers and addition of new ones managed by DB2


                   Redefine database storage paths during a database restore

                                                                                             17




Basically, a storage path can be defined as a directory on a file system (or on a drive in the
   Windows world). One may ask why there is no support for raw device storage paths. Its
   unclear if such support would be important to users. Historically, raw device containers have
   provided better performance, but they have been the hardest to manage. The performance
   between raw and file system storage has been narrowing (direct I/O) and it is expected that
   less and less installations will be using raw in the future.




                                                                                                   17
              New CREATE DATABASE Syntax
                 CREATE DATABASE database-name
                 [AT DBPARTITIONNUM | [AUTOMATIC STORAGE {NO | YES}]
                 [ON path[{,path}...][DBPATH ON path]]
                 . . .
                 [CATALOG TABLESPACE tblspace-defn] [USER TABLESPACE tblspace-defn]
                 [TEMPORARY TABLESPACE tblspace-defn] [WITH "comment-string"]]
                 [AUTOCONFIGURE [USING config-keyword value [{,config-keyword
                 value}...]]
                 [APPLY {DB ONLY | DB AND DBM | NONE}]]

                  tblspace-defn:
                     MANAGED BY { SYSTEM USING ('string' [ {,'string'} ... ] ) |
                     DATABASE USING ({FILE | DEVICE} 'string' number-of-pages
                     [ {,{FILE | DEVICE} 'string' number-of-pages} ... ]) | AUTOMATIC
                            STORAGE}
                     [EXTENTSIZE number-of-pages] [PREFETCHSIZE number-of-pages]
                     [OVERHEAD number-of-milliseconds] [TRANSFERRATE number-of-
                  milliseconds]
                     [NO FILE SYSTEM CACHING | FILE SYSTEM CACHING]
                     [AUTORESIZE {NO | YES}] [INITIALSIZE integer {K|M|G}]
                     [INCREASESIZE integer {PERCENT|K|M|G}] [MAXSIZE {NONE | integer
                            {K|M|G}}]
               (Existing (non-automatic storage) behavior is the default if the new syntax is not explicitly used)
                                                                                                                     18




On Windows, you can only specify a drive letter for the database path. However, you can specify
absolute path names (i.e. absolute directory names) for storage paths. These
drives/directories/paths must exist prior to the CREATE DATABASE command being executed.


The underlying sqlecrea( ) API has been updated with the automatic storage functionality as well.
Therefore, you can programmatically create automatic storage databases.




                                                                                                                          18
               New CREATE DATABASE Syntax (cont.)

                   Automatic storage can be enabled explicitly using the AUTOMATIC
                   STORAGE YES option:

                       CREATE DATABASE TESTDB1 AUTOMATIC STORAGE YES
                       CREATE DATABASE TESTDB2 AUTOMATIC STORAGE YES ON /db2data



                   Or it can be implicitly enabled:

                     By specifying more than one path with the ON option
                     By specifying the DBPATH ON option

                       CREATE DATABASE TESTDB3 ON C:,D:
                       CREATE DATABASE TESTDB4 /fs1,/fs2,/fs3 DBPATH ON /fs2


                                                                                     19




If the database is being implicitly enabled for automatic storage then it does not matter whether
or not AUTOMATIC STORAGE YES is also specified. However, if you specify AUTOMATIC
STORAGE NO but you do something that is supposed to implicitly enable it (e.g. multiple paths
specified or the DBPATH ON option is used) then the command will fail with an error
(SQL2032N).




                                                                                                    19
              New CREATE DATABASE Syntax (cont.)

                 The paths listed with the ON option are the database’s storage paths.
                   If ON is not specified then the database has one storage path, determined by database
                   manager configuration parameter dftdbpath


                 The database path is determined in the following order:
                   Path specified with the DBPATH ON option
                   If that is not specified, the first path listed with the ON option
                   If that is not specified, the database manager configuration parameter dftdbpath


                 By default, SYSCATSPACE, TEMPSPACE1, USERSPACE1 and any
                 subsequently created tablespaces are created as automatic storage
                 tablespaces (more on these later), in an automatic storage database
                   Note: can override this; can create any mix of automatic storage and "normal" SMS or
                   DMS
                                                                                                           20




This slide talks about the database path. The database path is the location in which the
database directory is created (e.g. <database path>/<instance>/NODE<num>/SQL<token>).
The database directory contains various DB2 control files such as SQLSPCS.1/2, SQLDBCON,
and SQLOGCTL.LFH. Its also the default location for the log files (<dbdir>/SQLOGDIR).
Therefore, its important to choose the database path wisely (and you may want to consider using
a path that is not the same as one of the storage paths).




                                                                                                                20
               CREATE DATABASE (cont.)

                  Examples:                           CREATE DATABASE TESTDB4
                                                       AUTOMATIC STORAGE YES ON /dbdir
                                                      -Automatic storage enabled: Yes
                   CREATE DATABASE TESTDB1            -Database path: /dbdir

                    -Automatic storage enabled: No    -Storage path: /dbdir

                    -Database path: dftdbpath
                                                      CREATE DATABASE TESTDB5
                   CREATE DATABASE TESTDB2             ON /db2/dir1,/db2/dir2,/db2/dir3
                    ON /testdb2                       -Automatic storage enabled: Yes

                    -Automatic storage enabled: No    -Database path: /db2/dir1

                    -Database path: /testdb2          -Storage paths: /db2/dir1, /db2/dir2, /db2/dir3


                   CREATE DATABASE TESTDB3            CREATE DATABASE TESTDB6
                    AUTOMATIC STORAGE YES              ON D:\DB2_AS1,E:\DB2_AS2
                    -Automatic storage enabled: Yes    DBPATH ON C:
                    -Database path: dftdbpath         -Automatic storage enabled: Yes
                    -Storage path: dftdbpath          -Database path: C:
                                                      -Storage paths: D:\DB2_AS1, E:\DB2_AS2

                                                                                                        21




This slide shows various CREATE DATABASE commands. As you can see, the “old” syntax
does not implicitly enable automatic storage. This gives you a choice of whether you want to use
this new functionality or not (as you will see in this presentation, there are pros and cons to using
automatic storage).




                                                                                                             21
               New ALTER DATABASE SQL Statement
                 Adds storage paths to an existing automatic storage-enabled database
                   Note that these paths may not be used until the existing paths are consumed


                 Transactional in nature
                   Log record is written and eligible for replay during a roll forward command


                 Syntax:
                   ALTER DATABASE [<dbName>] ADD STORAGE ON '<path1>' [,'<path2>',...]


                 Examples:
                   ALTER DATABASE ADD STORAGE ON '/dbpath3'
                   ALTER DATABASE ADD STORAGE ON 'D:\NewPath1', 'E:\NewPath2'

                                                                                                 22




Because this is an SQL statement, you must be connected to the database in question before
executing this command. For reasons that we won't go into here, you may choose to specify a
database name as part of the statement. However, the database name that you specify must
match the name of the database you are connected to. Because this is essentially redundant
information, it doesn't provide you any benefit to specify the database name and it is suggested
that you don't go to the trouble of adding it in.




                                                                                                      22
               Displaying the Storage Paths

                      Storage paths are displayed as part of a DATABASE snapshot
                      For databases not enabled for automatic storage:

                         Number of automatic storage paths               = 0


                      For databases enabled for automatic storage:

                         Number of automatic storage paths               = ##
                         Automatic storage path                          = <1st path>
                         Automatic storage path                          = <2nd path>
                         ...




                                                                                        23




< No notes for this slide >




                                                                                             23
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    24




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         24
               What is an Automatic Storage Tablespace ?
                  A tablespace that will automatically manage it’s storage by drawing storage from
                  the database storage paths
                    Automatically assigns containers to itself upon creation
                    Automatically grows these containers
                    Automatically adds new containers (as new stripe sets) when appropriate

                  Selected via new MANAGED BY AUTOMATIC STORAGE clause (or by not
                  specifying a MANAGED BY clause at all)
                    Not really a new tablespace type, more like a new "storage management style"
                    Still uses either the DMS infrastructure (SMS for temps – more on this later)

                  Requires no containers to be specified when the tablespace is created
                    Containers will be defined and allocated by DB2
                    Explicit container operations cannot be performed against the tablespace

                  Have all of the other tablespace attributes (extent size, prefetch size, etc.)

                  Can only be created in Automatic Storage databases (ie. databases that have
                  storage paths defined)
                                                                                                     25




< No notes for this slide >




                                                                                                          25
               New CREATE TABLESPACE Syntax
                                New syntax introduced for CREATE TABLESPACE:

                                     CREATE TABLESPACE <tsName> [MANAGED BY AUTOMATIC STORAGE]
                                       [INITIALSIZE integer {K|M|G}]
               Same as what            [AUTORESIZE {NO|YES}] [INCREASESIZE integer {PERCENT|K|M|G}]
               was shown for           [MAXSIZE {NONE | integer {K|M|G}}]
               auto-resize earlier


                                Default initial size is 32 MB and auto-resize is enabled by default
                                Examples:
                                     CREATE TABLESPACE USER1
                                     CREATE TEMPORARY TABLESPACE TEMPTS
                                     CREATE TABLESPACE MYTS INITIALSIZE 100 M MAXSIZE 1 G
                                     CREATE LARGE TABLESPACE LRGTS INITIALSIZE 512 M AUTORESIZE NO
                                     CREATE REGULAR TABLESPACE USER2 INITIALSIZE 50 M
                                     CREATE TABLESPACE USER4 MANAGED BY DATABASE USING …


                                                                                                      26




By default, if no type is specified for the tablespace then it defaults to REGULAR.




                                                                                                           26
               Container Name Format
                   <storage path>/<instance>/NODE####/<dbname>/T#######/C#######.<EXT>

                      <storage path>   A storage path associated with the database
                      <instance>       The instance under which the database was created
                      NODE####         The database partition number (always NODE0000)
                      <dbname>         The name of the database
                      T#######         The tablespace ID
                      C#######         The container ID
                      <EXT>            A three-letter extension based on the type of data being stored:
                                         CAT - System catalog tablespace
                                         TMP - System temporary tablespace
                                         UTM - User temporary tablespace
                                         USR - User or regular tablespace
                                         LRG - Large tablespace

                     Examples:

                       /db2data/path1/kschlamb/NODE0000/TESTDB/T0000002/C0000000.USR
                       E:\Storage\db2\NODE0000\PRODDB\T0000000\C0000000.CAT



                                                                                                          27




< No notes for this slide >




                                                                                                               27
Regular/Large Automatic Storage Tablespaces
     Regular and Large table spaces are created using DMS as the underlying
     tablespace type
         With file containers

     Differences between automatic storage and non-automatic storage tablespaces:

 Non-automatic storage                                     Automatic Storage
 Containers must be explicitly provided when the           Containers cannot be provided when the tablespace is
 tablespace is created.                                    created; they will be assigned and allocated automatically by
                                                           DB2.
 Automatic resizing of tablespaces is off (AUTORESIZE      Automatic resizing of tablespaces is on (AUTORESIZE YES)
 NO) by default.                                           by default.
 Container operations can be performed using the           DB2 automatically creates new stripe sets when appropriate.
 ALTER TABLESPACE statement (ADD, DROP, BEGIN              Explicit Container operations cannot be performed because
 NEW STRIPE SET, and so on).                               DB2 is in control of space management.
 The initial size for the tablespace cannot be specified   The initial size for the tablespace can be specified using the
 using the INITIALSIZE clause.                             INITIALSIZE clause.
 A redirected restore operation can be used to redefine    The storage paths for the database can be redefined via
 the containers associated with the tablespace.            database restore and/or relocate DB (more on these later),
                                                           since storage management occurs at the database level.


                                                                                                                            28




                                                                                                                                 28
               Regular/Large Tablespaces Mechanics                                                    1/4

                     How does DB2 automatically assign containers to
                     tablespaces ?
                       DB2 will choose to create 0 or 1 container per storage path

                       Attempts are made to create containers with equal sizes (where possible)

                       Storage paths with a relatively small amount of space will be avoided
                       (where possible)

                         Allows a more consistent set of striping as the tablespace grows (i.e. avoids
                         hitting disk full right away, necessitating a new stripe set)

                         E.g. Three storage paths with 5 MB, 500 MB, and 600 MB of free space
                         respectively. Table space is created with an initial size of 800 MB

                         – The tablespace will be created with one container on each of the last two paths (each 400
                           MB in size)

                                                                                                                       29




< No notes for this slide >




                                                                                                                            29
               Regular/Large Tablespaces Mechanics                                             2/4

                    Recently added storage paths are not used until a new stripe set is
                    needed

                    As described earlier in the auto-resize section, growth occurs by
                    extending all of the containers in the last range of the map

                      When one of the containers is no longer able to grow, a new stripe set of
                      containers is added

                        This is different than the basic auto-resize case discussed earlier (which
                        would fail at this point with an SQL0289N error)

                      It is at this time that recently added storage paths can be considered for new
                      containers




                                                                                                       30




< No notes for this slide >




                                                                                                            30
               Regular/Large Tablespaces Mechanics                                                                            3/4
                Two storage paths                        The third storage                           To continue growing,
               and a tablespace has                     path is not used by                          the tablespace must
                a container on each                     the tablespace yet                           add a new stripe set

                    C0 C1                                C0 C1                                        C0 C1
                                    3rd storage                                 TS grows until                                New stripe set
                                   path is added                                C0 can't grow                               added automatically




                 Only now is the                        To continue growing,                            C4 will grow as the
              recently added storage                    the tablespace must                           tablespace grows from
                   path utilized                        add a new stripe set                                 here on

                C0 C1                                    C0 C1                                           C0 C1
                                       TS grows until                            New stripe set
                      C2 C3            C0 can't grow
                                                               C2 C3
                                                                               added automatically
                                                                                                               C2 C3
                                                                                                                     C4
                                                                                                                                NOTE: For
                                                                                                                                simplicity, we're
                                                                                                                                just showing one
                                                                                                                                tablespace within
                                                                                                                                the database

                                                                                                                                                  31




This slide is intended to show how auto-resize occurs in an automatic storage tables space. Like
the auto-resize behavior we talked about earlier in this presentation, containers in the last range
of the tablespace map extend when an auto-resize operation is needed. However, once one of
those containers cannot be grown any further (usually due to a file system full error, but could
also be the result of a file system limitation (e.g. there is a maximum container size) or a low
ulimit setting) the "Storage Manager" component within DB2 is queried for a new list of
containers to create. Its at this point that all of the storage paths are taken into consideration
(even recently added ones).


The diagram shows the 3rd file system staggered compared to the other two. This is just to show
the layout of containers compared to each other. You can look at data placement as a waterfall
or trickle from one set of containers to the next.




                                                                                                                                                       31
               Regular/Large Tablespaces Mechanics                                                4/4
                    A More Typical/Recommended Scenario
                Two storage paths
               and a tablespace has
                a container on each

                    C0 C1             3rd & 4th   C0 C1    TS grows
                                                                                 New stripe set
                                      storage                until a
                                                                                    added
                                       paths              container(s)   C0 C1   automatically     C0 C1
                                       added               can't grow



                                                                                                   C2 C3




                                                                                                   NOTE: For
                                                                                                   simplicity, we're
                                                                                                   just showing one
                                                                                                   tablespace within
                                                                                                   the database

                                                                                                                   32




This slide shows the typical scenario for auto growth and stripe set addition, which maintains
striping.




                                                                                                                        32
               Temporary Automatic Storage Tablespaces
                      Table spaces are created using SMS as the underlying tablespace type
                        The auto-resize options have no meaning and cannot be specified
                             Remember that SMS is already an auto-extend type of infrastructure (where objects
                             grow by a page or extent at a time)

                      Differences between automatic storage and non-automatic storage tablespaces:


                          Non-automatic storage                         Automatic Storage

                          Containers must be explicitly provided when   Containers cannot be provided when the
                          the tablespace is created.                    tablespace is created, they will be assigned
                                                                        and allocated automatically by DB2.
                          Containers cannot be added after the          DB2 will redefine the containers across the
                          tablespace has been created.                  storage paths at database startup.

                          A redirected restore operation can be used    The storage paths for the database can be
                          to redefine the containers associated with    redefined via database restore and/or
                          the tablespace.                               relocate DB (more on these later), since
                                                                        storage management occurs at the
                                                                        database level.


                                                                                                                       33




Why do temporary automatic storage tablespaces use SMS instead of DMS? Because the
space usage of temporary tablespaces is very transient in nature, ranging anywhere from no
usage at all to filling up all of the file systems that they reside on, they don't make a good fit with
the auto-resize capabilities of DMS (since there is no built-in ability to automatically shrink back
down in size once they have grown and the objects within them no longer exist). Also, with SMS
temporary tables, they can be created completely within the buffer pool such that no I/O is ever
done (and the actual size of the underlying containers has no effect). However, with DMS
temporary tables, they can only be created if there is sufficient space backing it in the
tablespace.


And because of this transient nature of temporary tablespaces, it may make sense in some
cases to not even use temporary automatic storage tablespaces in an automatic storage
database. If a temporary table happens to temporarily consume all of the space in the file
systems it resides on, it can prevent regular/large automatic storage tablespaces from growing as
they need to. It might make more sense to use a non-automatic storage SMS tablespace
instead, that resides on its own separate file system, away from the database's storage paths.




                                                                                                                            33
               Temporary Tablespaces Mechanics                                                  1/2


                        The choice of which storage paths to create the tablespace on is
                        based on the most effective use of space

                          With SMS tablespaces, the smallest container is the limiting factor

                              E.g. two containers with 10 MB and 100 MB – tablespace will be full after
                              using 20 MB (10 MB in each)




                                                                                                          34




< No notes for this slide >




                                                                                                               34
               Temporary Tablespaces Mechanics                                                               2/2
                                                    Choice #1: Use P1, P2, P3, P4         Choice #2: Use P2, P3, P4
                    Given the following four        Result:    Effective size is 400 MB   Result:    Effective size is 600 MB
                    storage paths, what is
                    the best choice for the             C0     C1     C2     C3
                    temporary tablespace?
                                                                                                     C0     C1     C2



                    P1
                 (100 MB)

                            P2
                         (200 MB)
                                                    Choice #3: Use P3, P4                 Choice #4: Use P4
                                                    Result:    Effective size is 800 MB   Result:    Effective size is 400 MB
                                   P3       P4
                                (400 MB) (400 MB)




                                                                      C0     C1                                    C0

               NOTE: This isn't saying that the
               tablespace will use this much
               space, just that it possibly could


                                                                                                                                35




This slide shows how DB2 determines what containers to create on the database's storage paths
for an temporary automatic storage tablespace (which is always built on top of the SMS
infrastructure). This is the same logic that is used at database startup to redefine the containers
(to allow the temporary tablespace to adapt to the changes which may have occurred in the
storage paths since the tablespace was first created).




                                                                                                                                     35
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    36




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         36
               Displaying Automatic Storage Paths

                         Storage paths are displayed as part of a DATABASE snapshot
                         For databases not enabled for automatic storage:
                              Number of automatic storage paths         = 0

                         For databases enabled for automatic storage:
                              Number of automatic storage paths         = ##
                              Automatic storage path                    = <1st path>
                              Automatic storage path                    = <2nd path>
                              ...

                         Can access this information through snapshot UDFs as well:
                              select num_db_storage_paths from
                                     table(snap_get_db('DBNAME', -1)) as dbinfo

                              select db_storage_path from
                                     table(snap_get_sto_paths('DBNAME', -1)) as stgpaths


                                                                                           37




< No notes for this slide >




                                                                                                37
               Tablespace Snapshot Output
                 Tablespace name                      =   TS1
                   Tablespace ID                      =   3
                   Tablespace Type                    =   Database managed space
                   Tablespace Content Type            =   Any data
                   Tablespace Page size (bytes)       =   4096
                   Tablespace Extent size (pages)     =   32
                   Automatic Prefetch size enabled    =   Yes
                   Buffer pool ID currently in use    =   1
                   Buffer pool ID next startup        =   1                      New fields that are
                   Using automatic storage            =   Yes                    applicable to auto-resize
                   Auto-resize enabled                =   Yes
                   File system caching                =   Yes                    and/or automatic storage
                   Tablespace State                   =   0x'00000000'           tablespaces.
                   Tablespace Prefetch size (pages)   =   32
                   Total number of pages              =   4608                   Note: these are also
                   Number of usable pages             =   4576                   accessible via SQL via
                   Number of used pages               =   4512
                   Number of pending free pages       =   0
                                                                                 the snap_get_tbsp() and
                   Number of free pages               =   64                     snap_get_tbsp_part()
                   High water mark (pages)            =   4512                   UDFs.
                   Initial tablespace size (bytes)    =   1048576
                   Current tablespace size (bytes)    =   18874368
                   Maximum tablespace size (bytes)    =   104857600
                   Increase size (bytes)              =   1048576
                   Time of last successful resize     =   02/14/2005 15:32:17.355772
                   Last resize attempt failed         =   No
                   Rebalancer Mode                    =   No Rebalancing
                   Minimum Recovery Time              =   02/14/2005 15:31:57.030410
                   Number of quiescers                =   0
                   Number of containers               =   1
                   …


                                                                                                             38




This is an example of the type of output you can see from the GET SNAPSHOT FOR
TABLESPACES command. In this example, the increase size is set to an explicit size (1 MB).
However, if the user chose to go with the default value then it would show a value of
“AUTOMATIC” instead. Or, if the user specified the size as a percentage then the line would
read “Increase size (percentage)” instead of “Increase size (bytes)”. Likewise, the maximum size
in this example is set to an explicit value (100 MB) but if the default was used it would show a
value of “NONE” instead.


Note that some of these new fields are only shown if applicable. For example, you won’t see the
size fields for SMS tablespaces. And, you won’t see an initial tablespace size for anything but an
automatic storage tablespace that uses the DMS infrastructure.




                                                                                                                  38
              Tablespace Snapshot Output (cont.)

                      New elements are accessible through the snapshot UDFs as well:

                       select tbsp_name,
                              tbsp_id,
                              tbsp_using_auto_storage,
                              tbsp_auto_resize_enabled
                         from table(snap_get_tbsp('TESTDB', -1)) as tbsinfo

                       select tbsp_name,
                              tbsp_id,
                              dbpartitionnum,
                              tbsp_initial_size,
                              tbsp_current_size,
                              tbsp_max_size,
                              tbsp_increase_size,
                              tbsp_increase_size_percent,
                              tbsp_last_resize_time,
                              tbsp_last_resize_failed
                         from table(snap_get_tbsp_part('TESTDB', -1)) as tbspartinfo


                                                                                       39




There are many other elements available through these UDFs; only those new elements related
to automatic storage and auto-resize are shown here. Note that there are two UDFs involved
here. The first one (SNAP_GET_TBSP) returns tablespace information that is common to all
partitions in the database (e.g. the name, the type, whether the tablespace is enabled for
automatic storage, etc.). The second one (SNAP_GET_TBSP_PART) returns tablespace
information that may be different on each partition (e.g. current size, number of usable pages,
state, etc.).




                                                                                                  39
               New Automatic Storage Health Indicators
                   Automatic Storage
                      Database automatic storage utilization (db.db_auto_storage_util)
                        Tracks free space in the database storage path(s)
                        Calculation: (db.auto_storage_used / db.auto_storage_total) * 100



                   Auto-Resize DMS File Tablespaces
                      Table space automatic resize status (ts.ts_auto_resize_status)
                        Tracks whether or not a tablespace has failed to resize automatically
                        Values: Normal, Resize failed

                      Automatic resize tablespace utilization (ts.ts_util_auto_resize)
                        Tracks the consumption of storage and free space for each auto-resize
                        tablespace on which a maximum size has been defined

                                                                                                40




< No notes for this slide >




                                                                                                     40
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    41




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         41
              Backup & Restore

                The syntax and behavior of the BACKUP command has not changed
                The syntax of the RESTORE command has changed to allow the redefinition
                of storage paths:

               RESTORE DATABASE source-database-alias { restore-options | CONTINUE | ABORT }

               restore-options:
                 [USER username [USING password]] [TABLESPACE [ONLINE] |
                 TABLESPACE (tblspace-name [ {,tblspace-name} ... ]) [ONLINE] |
                 HISTORY FILE [ONLINE] | LOGS [ONLINE] | COMPRESSION LIBRARY [ONLINE]]
                 [INCREMENTAL [AUTOMATIC | ABORT]] [USE {TSM | XBSA} [OPEN num-sess SESSIONS]
                 [OPTIONS {options-string | options-filename}] |
                 FROM dir/dev [{,dir/dev} ... ] | LOAD shared-lib [OPEN num-sess SESSIONS]
                 [OPTIONS {options-string | options-filename}]] [TAKEN AT date-time]
                 [[TO target-directory] | [ON path[{,path}...][DBPATH ON path]]]
                 [INTO target-database-alias] [LOGTARGET directory]
                 [NEWLOGPATH directory] [WITH num-buff BUFFERS] [BUFFER buffer-size]
                 [DLREPORT file-name] [REPLACE HISTORY FILE] [REPLACE EXISTING] [REDIRECT]
                 [PARALLELISM n] [COMPRLIB lib-name] [COMPROPTS options-string]
                 [WITHOUT ROLLING FORWARD] [WITHOUT DATALINK] [WITHOUT PROMPTING]


                                                                                                42




In addition to the RESTORE command changing to support the redefinition of storage paths, the
underlying db2Restore( ) API has changed as well.




                                                                                                     42
              Restore : Rules for Determining DBPATH and
              Storage Paths

                   RESTORE will determine the database path and storage paths as follows

                     The database path (which is where DB2 stores various control files for the database)
                       If the TO clause or the DBPATH ON clause is specified: it indicates the database path
                       Else, if the ON clause is used, then the first path listed in the ON clause is used as the
                       database path
                       If none of the TO, ON, or DBPATH ON clauses are specified, the dftdbpath database
                       manager configuration parameter determines the database path
                       However, if a database with the same name exists on disk, the above is all ignored and
                       the database is restored into the same database path as the existing database.

                     The storage paths (where DB2 creates automatic storage tablespace containers)
                       If the ON clause is specified, all of the paths listed are considered storage paths, and
                       these paths are used instead of the ones stored within the backup image.
                       If the ON clause is not specified, no change is made to the storage paths (the storage
                       paths stored within the backup image are maintained).


                                                                                                                    43




This seems rather daunting but it should hopefully be clearer when you see the examples on the
next slide.




                                                                                                                         43
              Restore (cont.)
                CREATE DATABASE TESTDB ON /fs1, /fs2, /fs3 DBPATH ON /fs4
                {Database is then backed up and eventually dropped}


                RESTORE DATABASE TESTDB
                                                                               If the database wasn't
                 Database path: dftdbpath                Storage paths are     dropped then the
                 Storage paths: /fs1, /fs2, /fs3         not redefined         database path would
                                                                               remain on /fs4,
                RESTORE DATABASE TESTDB TO /newfs1                             regardless of what
                                                                               was specified on the
                 Database path: /newfs1                                        RESTORE command
                 Storage paths: /fs1, /fs2, /fs3

                RESTORE DATABASE TESTDB ON /newfs1, /newfs2
                 Database path: /newfs1                                Storage paths
                 Storage paths: /newfs1, /newfs2                       are redefined

                RESTORE DATABASE TESTDB ON /newfs1, /newfs2 DBPATH ON /newfs3
                 Database path: /newfs3
                 Storage paths: /newfs1, newfs2


                                                                                                        44




RESTORE #1: In this case, nothing is being specified on the RESTORE command. When this is
done, the database path chosen is based on the value of dftdbpath (which matches the non-
automatic storage database case). The storage paths are not being redefined as part of the
restore so the storage paths stored within the backup image (/fs1, /fs2, /fs3) are used. The fact
that the database path used to be /fs4 has no relevance here (just like in the non-automatic
storage database case).


RESTORE #2: In this case, the TO option is being used and it dictates what to use for the
database path (/newfs1). It does not have any effect on the storage paths, so like RESTORE #1
the storage paths from the backup image are used (/fs1, /fs2, /fs3).


RESTORE #3: In this case, the ON option is being specified (without the DBPATH ON option)
which affects the database path and the storage paths. The storage paths are being redefined to
/newfs1 and /newfs2. And because DBPATH ON isn't used, the first storage path listed
(/newfs1) is also used as the database path . This matches the behavior of the CREATE
DATABASE command.


RESTORE #4: In this case, the ON option and the DBPATH ON option is specified. The ON
option instructs the restore to redefine the storage paths to be /newfs1 and /newfs2. The
DBPATH ON option explicitly states the database path to use (/newfs3). This matches the
behavior of the CREATE DATABASE command.




                                                                                                             44
               Rollforward

                      If the storage paths associated with a database are changed
                      during a restore:
                        Log records associated with ADD STORAGE are not replayed during a
                        subsequent rollforward

                        (The assumption is that you are choosing a final storage configuration for the
                        database)




                      If the storage paths associated with a database are not
                      changed during a restore:

                        Log records associated with ADD STORAGE are replayed

                        (The assumption is that after you restore and rollforward you wish the
                        database to be in the same state it was in prior to the restore)
                                                                                                         45




There is no way to change the behavior described in this slide.




                                                                                                              45
               db2relocatedb
                        Can be used to change the database name, database path, and the
                        location of tablespace containers
                          Can also be done using backup/restore
                          Benefit of the tool is speed (can use OS/file system tools to do the moving)

                        Changes for automatic storage databases:
                          CONT_PATH cannot be specified for an automatic storage tablespace
                          STORAGE_PATH can be used to change a storage path


                     CREATE DATABASE TESTDB ON
                                                                        config.txt
                       /DIR1,/DIR2,/DIR3
                                                                         DB_NAME=TESTDB
                     {move /DIR1->/NEWDIR1}                              INSTANCE=db2inst
                     {move /DIR2->/NEWDIR2}                              DB_PATH=/DIR1,/NEWDIR1
                     {move /DIR3->/NEWDIR3}                              STORAGE_PATH=/DIR1,/NEWDIR1
                                                                         STORAGE_PATH=/DIR2,/NEWDIR2
                                                                         STORAGE_PATH=/DIR3,/NEWDIR3
                     db2relocatedb –f config.txt

                                                                                                         46




Note that you cannot change the number of storage paths, just the location of them.


Its also important to note for this particular example that a path (/DIR1) is being used both as the
database path and as a storage path (since a database path was not specifically given on the
CREATE DATABASE command). Therefore, since /DIR1 is being completed moved to
/NEWDIR1 it must be listed as a DB_PATH entry and as a STORAGE_PATH entry.




                                                                                                              46
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    47




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         47
Using New Functionality On Existing Databases

     Auto-resize can be enabled for existing DMS FILE tablespaces,
     regardless of when they were created
       ALTER TABLESPACE <tsName> AUTORESIZE YES
       Can only be enabled if all of the containers are file (no raw devices)
       For tablespaces with raw device containers you can do the following:
         Backup the database (or individual tablespaces)
         Do a redirected restore, specifying a list of file containers (instead of raw
         device containers)
         Once the database is online, use ALTER TABLESPACE to enable it




                                                                                         48




                                                                                              48
              Moving Back To A Pre-V8.2.2 Level of DB2
                     Automatic storage databases are not usable on pre-V8.2.2. levels of
                     DB2
                       Automatic storage databases have a significant amount of meta-data stored
                       within them that is not recognized on older levels of DB2

                       Includes moving an instance back and connecting, or trying to restore a
                       database backup (both will fail with errors)


                     Auto-resize tablespaces can be used on older levels of DB2 provided
                     that auto-resize is disabled first
                       ALTER TABLESPACE <tsName> AUTORESIZE NO

                       If not disabled then the database will be unusable




                                                                                                   49




An attempt to use an automatic storage database on a pre-V8.2.2 level of DB2 will result in an
SQL0902N error (“system error”) while trying to connect to the database. A restore of an
automatic storage database will fail with an SQL2530N error (“backup image is corrupted”).


In the case of an auto-resize tablespace (in a non-automatic storage database), if auto-resize
isn’t disabled before moving the database to a pre-V8.2.2 level then both attempts to connect or
restore will fail with an SQL0902N error.




                                                                                                        49
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    50




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         50
               Hints & Tips
                    When adding new containers to an auto-resize DMS tablespace
                      Add them via a new stripe set
                      Add containers that are the same size

                    When defining/adding database storage paths, try to ensure the file systems
                    associated with the paths are approximately equal in capacity

                    Use the DBPATH clause to keep the database directory control data on different
                    storage than the storage paths, and avoid using the file systems associated with
                    the database storage paths for anything but DB2 tablespace data

                      Depending on the non-DB2 activity that is occurring, it can cause strange growth
                      patterns for the automatic storage tablespaces

                      When determining free space on a storage path, DB2 will not use the last nn MB of
                      space if it shares a file system with the following:
                      • The database path (32 MB)
                      • The system directory (Windows only – 64 MB)

                                                                                                          51




< No notes for this slide >




                                                                                                               51
              And Finally … Sorting our your Tablespace Options
             Tablespace          Single     Auto        Auto       File         Allows          Underlying
             Option              Point of   Container   Stripe     System       Different       Infrastructure
                                 Storage    Growth ?    Set        Buffering    Tablespaces
                                 Mgt for                Addition   ?            &
                                 Multiple               ?                       Bufferpools
                                 Table-                                         for a Table’s
                                 spaces                                         Data, Index,
                                 ?                                              & Long Data
                                                                                ?
             SMS                 No         Yes         No         Selectable   No              SMS
                                                                   (DIO/CIO)

             DMS       File      No         No          No         Selectable   Yes             DMS
                                                                   (DIO/CIO)

                       RAW       No         No          No         No           Yes             DMS


                       File      No         Yes         No         Selectable   Yes             DMS
                                                                   (DIO/CIO)
                   Auto-Resize

             Automatic           Yes        Yes         Yes        Selectable   Yes             DMS (perm)
             Storage                                               (DIO/CIO)                    SMS (temp)

                                                                                                                 52




Automatic Storage is becoming the preferred and strategic choice for most purposes.




                                                                                                                      52
               Agenda
                      New Concepts
                        Auto-Resize Tablespaces
                        Automatic Storage
                           Automatic Storage Tablespaces

                      Hints, Tips, Best Practices
                        Monitoring
                        Recovery
                        Migration
                        Misc Hints & Tips


                      DB2 9
                        And beyond, time permitting

                                                                                    53




The 2 major storage features added in V8.2.2 are auto-resize DMS File Tablespaces and
automatic storage. This presentation will cover both concepts in detail,
including recovery and migration implications as well as a set of hints and tips.




                                                                                         53
               DB2 9

                      Automatic Storage on DPF

                      Automatic Storage : the new Default for
                      newly created databases




                                                                54




< No notes for this slide >




                                                                     54
        Automatic Storage on DPF
           Storage paths are, by default, homogeneous across
           database partitions :


            2 node DPF instance   CREATE DATABASE mydb
                   “inst”          ON \path1,\path2,DBPATH ON \db1

                                  -Automatic storage enabled: Yes

                                  -On Database Partition 1 :
                                             Storage paths: /path1/inst/NODE0000/mydb
                                                               /path2/inst/NODE0000/mydb
                                             Database path: /db1/inst/NODE0000/SQL00000
                     NODE0000




                                  -On Database Partition 2 :
                                             Storage paths: /path1/inst/NODE0001/mydb
                                                               /path2/inst/NODE0001/mydb
                                             Database path: /db1/inst/NODE0001/SQL00000

                     NODE0001
                                                                                           55




<storage
path>/<instance>/NODE####/<dbname>/T#######/C#######.<EXT>




                                                                                                55
        Automatic Storage on DPF
           If desired, non-homogeneous setups are possible:



            2 node DPF instance   CREATE DATABASE mydb
                   “inst”          ON \p$N,\p$N,DBPATH ON \db1

                                  -Automatic storage enabled: Yes

                                  -On Database Partition 0 :
                                             Storage paths: /p0/inst/NODE0000/mydb
                                                               /p0/inst/NODE0000/mydb
                                             Database path: /db1/inst/NODE0000/SQL00000
                     NODE0000




                                  -On Database Partition 1 :
                                             Storage paths: /p1/inst/NODE0001/mydb
                                                               /p1/inst/NODE0001/mydb
                                             Database path: /db1/inst/NODE0001/SQL00000

                     NODE0001
                                                                                          56




<storage
path>/<instance>/NODE####/<dbname>/T#######/C#######.<EXT>




                                                                                               56
Automatic Storage by Default

  Examples:


   CREATE DATABASE TESTDB1                   CREATE DATABASE TESTDB1
    -Automatic storage enabled: No YES       -Automatic storage enabled: No YES
    -Database path: dftdbpath                -Database path: dftdbpath
    -Storage path: dftdbpath                 -Storage path: dftdbpath

   CREATE DATABASE TESTDB3
    AUTOMATIC STORAGE NO                     CREATE TABLESPACE MYTS
    -Automatic storage enabled: No           -Automatic storage tablespace automatically
                                             drawing storage from dftdbpath
    -Database path: dftdbpath


   CREATE DATABASE TESTDB6                   CREATE TABLESPACE MYTS2
    ON D:\DB2_AS1,E:\DB2_AS2                 MANAGED BY DATABASE USING
    DBPATH ON C:                             (FILE ‘a’ 40000)
                                             - Of course, any scripts or procedures that
    -Automatic storage enabled: Yes          explicitly create non-AS tablespaces will still
    -Database path: C:                       work and will still create non-AS tablespaces
    -Storage paths: D:\DB2_AS1, E:\DB2_AS2
                                                                                               57




                                                                                                    57
          Obligatory Fine Print

             The information in this presentation concerns new products and/or features that IBM may or may
             not announce or deliver. Any discussion of OEM products is based upon information which has
             been publicly available and is subject to change. The specification and/or details of the content
             described in this presentation may change.

             REFERENCES IN THIS PUBLICATION TO IBM PRODUCTS, PROGRAMS, OR SERVICES DO
             NOT IMPLY THAT IBM INTENDS TO MAKE THESE AVAILABLE IN ALL COUNTRIES IN WHICH
             IBM OPERATES.

             IBM MAY HAVE PATENTS OR PENDING PATENT APPLICATIONS COVERING SUBJECT
             MATTER IN THIS DOCUMENT. THE FURNISHING OF THIS DOCUMENT DOES NOT IMPLY
             GIVING LICENSE TO THESE PATENTS.

             SOME OF THE MATERIAL MAY REPRESENT IBM CONFIDENTIAL INTELLECTUAL
             PROPERTY WHICH IS NOT TO BE DIVULGED TO ANYONE WITHOUT PRIOR CONSENT
             FROM IBM.




                                                                                                                 58




Notes :
•A ‘simple’ map is essentially one in which each stripeset consists of one range
only, with containers of equal size within that stripeset. Note that this results from
following the general recommendation of using containers of equal size for a
tablespace.




                                                                                                                      58
               Possible Future Areas of Enhancement
                      Storage Groups

                      Raw Support

                      Migration to Automatic Storage

                      Storage Path Removal

                      AS Temporary Tablespace Enhancements

                      Misc

                       Eg. Limiting Container Size

                       Eg. Rebalance capability

                      …
                                                             59




< No notes for this slide >




                                                                  59
Futures : Storage Groups

             Temporary                             Tablespace A          Tablespace B
             Tablespace




         Storage Group 1                                  Storage Group 2




  In this example…
      Storage groups are used to reserve the fastest and/or most-redundant disks for permanent
      data
      Lower cost devices used for transient data


                                                                                                 60




                                                                                                      60
Session C10
Session Title : DB2 Automatic Storage : The Future of Storage
                Management in DB2


                         Matt Huras
          IBM
          huras@ca.ibm.com




                                                                61




                                                                     61