Creating and Editing Collections

Go Up to Using Capacity Analyst

Typically, how you set up collections will be dictated by your own requirements. If your enterprise has several applications for example, each using multiple databases, you might be interested in viewing graphs and reports on an application-by-application basis, with each collection comprising statistics for several databases. At a lower level, you may be interested in viewing statistics on collections of table or indexes.

Similarly, how you set up collections can also be impacted by Capacity Analyst functionality. For example, a collection option lets you schedule statistics update at specified intervals. If your requirements include reports with different intervals, hourly as opposed to weekly for example, you could set up your collections accordingly. Or since the Collection Analysis Viewer calibrates a graph to the range of the highest valued statistic displayed, you might group statistics with similar expected values.

In short, you should consider all functionality available in Capacity Analyst before deciding on a collection setup. For details see Content and Options in a Collection and Viewing Charts and Reports on Collections.

The following topics provide details on creating and editing Capacity Analyst collections:

• Creating a New Collection
• Editing a Capacity Collection
• Cloning a Collection
• Deleting a Collection

Content and Options in a Collection

The reports and analysis you work with using Capacity Analyst include such statistics as space usage and access count details on database objects, databases, files and other storage mechanisms. They can also include performance-based and wait based statistics. The specific content and options available depend on the DBMS that DBArtisan is working with. The following topics provide summaries of available options and content on a product-by-product basis:

• IBM DB2 for Windows, Unix, and Linux Capacity Collection Content and Options
• Microsoft SQL Server Capacity Collection Content and Options
• Oracle Capacity Collection Content and Options
• Sybase ASE Capacity Collection Content and Options

IBM DB2 for Windows, Unix, and Linux Capacity Collection Content and Options

When creating or editing a Capacity Analyst collection, you specify the statistics you want in that collection and specify additional notification and update options. The table below summarizes the available statistics and options on the tabs/panels of collection wizards and editors for this DBMS.

Content/Option Category	Editor Tab or Wizard panel	Description or specific DBMS feature support
Identification/Notification	General	Lets you provide a name and description for the collection and optionally, provide an email address or net send name for notifications.
Types of statistics collected	Selection Types	Lets you select the types of content and options you want to include in the collection. You provide specifics in subsequent panels/tabs.
Database/storage structure space statistics	Storage Structures	A DB2 capacity collection can contain usage statistics on database (partition) space, DMS tablespace space, and SMS tablespace space.
Specific database object types for which to collect statistics	DB Objects	A DB2 capacity collection can contain usage statistics on indexes and tables
Update and rollup options	Statistics	A DB2 capacity collection offers the option to update statistics with additional specific choices for tables (distribution and column options), indexes (collection of extended and sample statistics), and access options (allowing read/write access during collection).
Automatic purging of old data	AutoPurge	Offers the option to automatically purge data older than a specified age, each time the collection is updated.

For information on how to create and edit collections, see:

• Creating a New Collection
• Editing a Capacity Collection
• Cloning a Collection

Microsoft SQL Server Capacity Collection Content and Options

When creating or editing a Capacity Analyst collection, you specify the statistics you want in that collection and specify additional notification and update options. The table below summarizes the available statistics and options on the tabs/panels of collection wizards and editors for this DBMS.

Content/Option Category	Editor Tab or Wizard panel	Description or specific DBMS feature support
Identification/Notification	General	Lets you provide a name and description for the collection and optionally, provide an email address or net send name for notifications.
Types of statistics collected	Selection Types	.Lets you select the types of content and options you want to include in the collection. You provide specifics in subsequent panels/tabs.
Database/storage structure space statistics	Storage Structures	An SQL Server capacity collection can contain size statistics on database backup space and database log backup space, and usage and size statistics on database space, filegroups, and files.
Specific database object types for which to collect statistics	DB Objects	An SQL Server capacity collection can contain usage and size statistics on indexes and tables.
Wait-event-based statistics	Wait Events	An SQL Server capacity collection can contain statistics on SQL Server Wait Types
Performance-based statistics	Performance Statistics	Provides statistics in the following performance counter categories: Access Methods, Buffer Manager, Buffer Partition, Databases, General Statistics, Latches, Locks, Memory Manager, SQL Statistics
Update and rollup options	Statistics	Provides the option to update statistics with additional options to specify a scan range, specify index or columns update, and recompute statistics
Automatic purging of old data	AutoPurge	Offers the option to automatically purge data older than a specified age, each time the collection is updated.

For information on how to create and edit collections, see:

• Creating a New Collection
• Editing a Capacity Collection
• Cloning a Collection

Oracle Capacity Collection Content and Options

When creating or editing a Capacity Analyst collection, you specify the statistics you want in that collection and specify additional notification and update options. The table below summarizes the available statistics and options on the tabs/panels of collection wizards and editors for this DBMS.

Content/Option Category	Editor Tab or Wizard panel	Description or specific DBMS feature support
Identification/Notification	General	Lets you provide a name and description for the collection and optionally, provide an email address or net send name for notifications.
Types of statistics collected	Selection Types	Lets you select the types of content and options you want to include in the collection. You provide specifics in subsequent panels/tabs.
Database/storage structure space statistics	Storage Structures	All tablespace, all non-temporary tablespaces, or select specific tablespaces
Specific database object types for which to collect statistics	DB Objects	Lets you include statistics on clusters, indexes, index partitions, rollback segments, tables, and table partitions.
Wait-event-based statistics	Wait Events	Lets you include statistics based on Oracle Wait Events.
Performance-based statistics	Global Stats	Lets you include Global Database Statistics.
Update and rollup options	Statistics	Offers the option to update statistics with additional options to compute or estimate statistics.
Automatic purging of old data	AutoPurge	Offers the option to automatically purge data older than a specified age, each time the collection is updated.

For information on how to create and edit collections, see:

• Creating a New Collection
• Editing a Capacity Collection
• Cloning a Collection

Sybase ASE Capacity Collection Content and Options

When creating or editing a Capacity Analyst collection, you specify the statistics you want in that collection and specify additional notification and update options. The table below summarizes the available statistics and options on the tabs/panels of collection wizards and editors for this DBMS.

Content/Option Category	Editor Tab or Wizard panel	Description or specific DBMS feature support
Identification/Notification	General	Lets you provide a name and description for the collection and optionally, provide an email address or net send name for notifications.
Types of statistics collected	Selection Types	Lets you select the types of content and options you want to include in the collection. You provide specifics in subsequent panels/tabs.
Database/storage structure space statistics	Storage Structures	Lets you use Global Space Summary statistics for the datasource or space and usage statistics for specific database devices and databases.
Specific database object types for which to collect statistics	DB Objects	Lets you include statistics on indexes and tables.
Wait-event-based statistics	Wait Events	Wait event statistics are only available if MDI performance views have been created.
Performance-based statistics	Performance Statistics	Lets you include Sybase ASE Performance Statistics in a collection.
Update and rollup options	Statistics	Option to update statistics with additional options to update indexes or partitions
Automatic purging of old data	AutoPurge	Offers the option to automatically purge data older than a specified age, each time the collection is updated.

For information on how to create and edit collections, see:

• Creating a New Collection
• Editing a Capacity Collection
• Cloning a Collection

Creating a New Collection

Capacity Analyst has a WIzard that walks you through specifying the statistics and options in a collection. Before creating a new collection, you should be familiar with the material in Content and Options in a Collection.

To create a new collection

1. Open the Capacity Analyst. See Opening the Capacity Analyst.
2. On the Capacity Analyst toolbar click the New Collection Wizard button.

   

   The Capacity Collection Wizard opens.

3. Provide the information required on each panel of the Wizard. For more information on these options, see the relevant DBMS-specific topic under Content and Options in a Collection.
4. When done click Finish.

   Capacity Analyst offers the option to schedule updates for the statistics in this collection.

5. If you decide to schedule statistics updates, use the Windows Task Scheduler to provide update details.

   Capacity Analyst adds the new collection to the Capacity Collection list.

See the following tasks for information on tasks you can perform after creating a collection:

• Editing a Capacity Collection
• Updating Statistics in Collections for use in Reports and Charts
• Viewing Charts and Reports on Collections

Editing a Capacity Collection

Because a collection stores rolled up statistics, all gathered and calculated over a specific interval, editing statistics in a collection could corrupt the data in a collection. Therefore, you cannot modify the statistics in a collection or the devices, databases or database objects that statistics are gathered against. You can only modify the basic identifiers for the collection and the refresh and autopurge options.

To edit an existing collection

1. Open the Capacity Analyst. See Opening the Capacity Analyst.
2. Select the collection to be edited and then on the Capacity Analyst toolbar click the Open Collection button.

   

3. On the General tab, edit the name, description, email address, or net send name.
4. On the Statistics tab, enable or disable update of statistics when you save your changes.
5. On the AutoPurge tab, enable or disable autopurge of older data when you save your changes.
6. Click OK.

Cloning a Collection

The Capacity Analyst lets you create a new collection from an existing collection. This lets you build a new collection starting from properties defined in the existing collection.

To make a copy of an existing collection

1. Open the Capacity Analyst. See Opening the Capacity Analyst.
2. Select the collection to be cloned and then on the Capacity Analyst toolbar click the Clone Collection button.

   

   Capacity Analyst opens the Capacity Analyst Collection Wizard. With the exception of the collection name, all statistics and options specified for the new collection are identical to those for the existing collection.

3. Minimally, provide a name for the new collection. Complete each panel of the wizard, changing settings as required. For more information on these options, see the relevant DBMS-specific topic under Content and Options in a Collection.
4. When done click Finish.

   Capacity Analyst offers the option to schedule updates for the statistics in this collection.

5. If you decide to schedule statistics updates, use the Windows Task Scheduler to provide update details.

   Capacity Analyst adds the new collection to the Capacity Collection list.

See the following tasks for information on tasks you can perform after cloning a collection:

• Editing a Capacity Collection
• Updating Statistics in Collections for use in Reports and Charts
• Viewing Charts and Reports on Collections

Deleting a Collection

If you no longer use the reports or analysis associated with a collection, Capacity Analyst lets you delete the collection.

To delete a collection

1. Open the Capacity Analyst. See Opening the Capacity Analyst.
2. Select a collection and then on the Capacity Analyst toolbar click the Delete button.

   

   Capacity Analyst prompts you to verify that you want to delete the collection.

3. Click Yes.