sap ase data store access management users guide en

PUBLIC

SAP Adaptive Server Enterprise 16.0 SP02Document Version: 1.0 – 2015-09-03

Data Store Access Management Users Guide

Content

1 Data Store Access Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

2 Access Tiers, Access Groups, and Policies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

3 Relationship Workflow Between DSAM Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

4 Requirements and DSAM Database Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84.1 Determining the Size of sybdsamdb. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Estimating the sybdsamdb Size Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10Example of Counting User Partitions in All Trackable Databases. . . . . . . . . . . . . . . . . . . . . . . . .12

4.2 Installing the sybdsamdb Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

5 Default Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155.1 Default Device Classes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155.2 Default Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165.3 Default Access Group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165.4 Default Policies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

DSAMPartitionEvaluationData. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185.5 DSAM Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

monDeviceSegmentUsage Monitoring Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20monDeviceSegmentIO Monitoring Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

6 Access Counts Collection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23

7 Data Access Patterns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

8 DSAM and Non-Volatile Cache. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

9 Viewing DSAM in GUI Mode. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

10 DSAM API. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2710.1 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

DSAMAccessByPartition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27DSAMAccessBySegment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28DSAMAccessGroups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29DSAMAccessTiers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29DSAMCompressionLevels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30DSAMCustomizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30DSAMDeviceClasses. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32DSAMPolicies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

2P U B L I C© 2015 SAP SE or an SAP affiliate company. All rights reserved.

Data Store Access Management Users GuideContent

DSAMPolicyBindings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3310.2 Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

dam_add_access_group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33dsam_add_access_tier. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34dsam_add_attribute. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35dsam_add_device_class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37dsam_add_policy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38dsam_aggregation_timestamp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39dsam_check_access_auth. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40dsam_check_access_time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41dsam_check_count_range. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42dsam_check_group_rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43dsam_check_period_range. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44dsam_check_tier_overlap. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45dsam_class_id. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46dsam_data_cleanup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46dsam_data_collector. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47dsam_delete_access_group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47dsam_delete_access_tier. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48dsam_delete_attribute. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49dsam_delete_db_binding. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50dsam_delete_device_class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50dsam_delete_policy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51dsam_device_io_counts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51dsam_get_message. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52dsam_get_period_seconds. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53dsam_get_table_info. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54dsam_get_time_value. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55dsam_help_access_group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56dsam_ptn_eval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56dsam_set_aggregation_frequency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .57dsam_set_collection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58dsam_set_collection_frequency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59dsam_set_compression_period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60dsam_set_device_class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61dsam_set_policy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62dsam_set_purge_period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63dsam_show_period. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64dsam_track_object. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65dsam_update_access_group. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67dsam_update_access_tier. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67


P U B L I C© 2015 SAP SE or an SAP affiliate company. All rights reserved. 3

dsam_update_device_class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69dsam_update_policy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69dsam_update_table_binding. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71



1 Data Store Access Management

Data store access management tracks access patterns for devices, segments, and data partitions, providing detailed insights on how data is accessed in an SAP ASE database. DSAM allows you to define data storage policies that compress and move data to appropriate storage based on the activity level of data.

Benefits for using DSAM include:

● Being able to monitor data usage● Moving data to faster devices or to archive devices based on data access activity.● Enhancing performance by keeping active data on high performance storage.● Enabling a more cost-effective use of storage assets.

Data Store Access Management Users GuideData Store Access Management


2 Access Tiers, Access Groups, and Policies

Data store access management (DSAM) reports and manages data by analyzing the frequency in which data partitions are accessed and applies user-supplied rules to the data access patterns.

These rules are grouped into data life cycle descriptions, called access groups. Each access group contains a set of access frequency descriptions, called access tiers. These access tiers describe the data access patterns for reporting purposes. In addition, each access tier may have a policy that controls what to do with data that fits that access pattern.

Term Description

Access tiers

A characteristic of a partition that specifies how many times data in that partition has been accessed via physical I/O within what period of time, such as, "100,000 or more times within 24 hours" or "less than 1,000 times within 30 days."

Access groups

A collection of access tiers that, taken together, describe the expected set of access characteristics of one or more partitions. Use this collection to report on and/or manage storage for those partitions. An access group may manage many partitions, but each individual partition may only be managed by a single access group.

For example, an access group might describe these access patterns:

● Extremely hot – a partition accessed a million or more times in the past 24 hours. Store this data on solid-state disks, and do not compress it.

● Hot – a partition accessed a million or more times in the past seven days. Store this data on solid-state disks, but it may be compressed.

● Normal – a partition accessed fewer than a million times in the past seven days. Compress and store this data on cheaper disks.

● Cold – a partition accessed fewer than a thousand times in the past 60 days. Archive this data by compressing and storing it on network-mounted files.

Each of these is one access tier, with its own policy describing what to do with data that has an access pattern that matches that tier.

Policy Actions to be taken when a partition’s access characteristics match a given access tier within the access group that manages that data. These actions may include changing the data's compression level and/or restricting its storage to a particular device class.

DSAM associates partitions with storage devices by creating segments containing only devices of a certain class. Then, when a partition's access pattern matches a given access tier and that tier has a policy that places data on a given device class, DSAM reassigns that partition to the segment naming that device class. This moves that partition's data onto the devices appropriate to it.


Data Store Access Management Users GuideAccess Tiers, Access Groups, and Policies

3 Relationship Workflow Between DSAM Elements

This illustration describes the DSAM elements.

Element Description

Object (Tables or Databases) Associated with access groups for the purpose of display and control.

Access Group A collection of access tiers, any of which may have a policy associated with it.

Access Tier Describes the data’s access patterns, for reporting purposes.

Policy May describe a compression level and a device class to be applied to data with an access pattern that matches the associated access tier.

Device Class Identifies a segment which in turn names a set of devices in the database that are appropriate to hold the affected data.

Segment Partition assigned to a region of storage, which controls the data’s physical placement on its storage devices.

Device Each device may permit storage for several segments, or for only one.

Compression Level Row- or page-level compression.

Data Store Access Management Users GuideRelationship Workflow Between DSAM Elements


4 Requirements and DSAM Database Installation

There are minimum requirements for installing and using DSAM.

To use DSAM, you need sybdsamdb, a database in which DSAM stores its information. Although you may run the installsybdsamdb script to install the default sybdsamdb database, this database may be too small for your needs, in which case you should plan for and create one instead. By default, the sybdsamdb database is built on the sybdsamdbdev device, which needs at least 10,240 pages. If the sybdsamdbdev device is unavailable, the script uses the default device.

Activity Requirement

Compressing partitions Enable configuration parameter enable compression. Using this feature requires the license ASE_COMPRESSION.

Using operations that call DSAM procedures

When granular permissions are:

● Enabled – the user needs manage any database permission.

● Not enabled – the user needs sa_role.

Scheduling ● SAP ASE Job Scheduler must be running to schedule the background access collection job, policy evaluation job, and partitions’ movement job.

● To schedule background access collection for the monitored databases users need these roles, in the following order:

1. sa_role2. mon_role3. js_admin_role or js_user_role

● When granular permissions are enabled, users must have sa_role or manage server permissions, and Job Scheduler must be installed, enabled, and running.


Data Store Access Management Users GuideRequirements and DSAM Database Installation

4.1 Determining the Size of sybdsamdb

DSAM stores information in the sybdsamdb historical database. Plan for your needs and create the sybdsamdb database before using the installsybdsamdb installation script. The script creates the sybdsamdb database if it does not exist, but the default size may be too small for your needs.

Context

The sybdsamdb space requirement is principally defined by the DSAMAccessData table, which holds historical data access information. New rows are added to table DSAMAccessData on a user-defined schedule (the default is every six hours) for every user partition in every tracked database. Those records are thinned out after a user-defined period (the default is after six months) and eventually discarded (the default is after one year). This uses the most space in the database, so this is the basis used to determine the database size. Knowing the maximum size of this table lets you determine the maximum required size of sybdsamdb.

Procedure

1. Determine which database or databases to track through DSAM. You cannot use master, model, temporary, or in-memory databases.

2. Count the number of user partitions in those databases. A user partition has a partition ID greater than 255.

3. Determine how many rows each partition should store in DSAMAccessData. Each partition stores one row per aggregation interval up to the "compression period", and one row per compression interval through the "purge period". Using the system-supplied defaults:

○ Aggregation frequency is six hours (that is, four collections per day).○ Compression period is six months, or 183 days.○ Compression interval is seven days.○ Purge period is one year, or 365 days.

Using these defaults, each partition stores 4 rows/day * 183 days = 732 rows, plus 1 row/week * 182 days = 26 rows, for a total of 758 rows per partition.

4. Multiply rows per partition times the number of partitions to get total rows. For example, if you have 10,000 partitions, you will store 758 * 10,000 = 7,580,000 rows.

5. Determine how many pages that data occupies. The number of rows per page depends on your installation’s logical page size:

Option Description

2 KB page 34 rows/page (there are 512 pages per megabyte)





Option Description


In the example below, if you use a 16 KB page, you need 7,580,000 / 281 = 26,975.09 pages. SAP ASE cannot allocate a fraction of a page, so you round up to 26,976 pages.

6. Determine how many megabytes that data will occupies: Divide the total pages by the number of pages/MB given above. In our example, this is 26,976 / 64 = 421.5 MB.

7. Add approximately 25 percent for the table’s index and 25 percent for estimated log space. So: 421.5 * 1.25 = 526.875 MB for data, and 421.5 * 0.25 = 105.375 MB for log, for a total of 632.25 MB.

8. Minor additional data space may be required for smaller tables. This will not be larger than the size of the model database:

select sum(size) / {pages per megabyte} from master.dbo.sysusageswhere dbid = db_id('model')

Using a default installation with a 16 KB logical page, that size is 24 MB. 526.875 + 24 = 550.875 MB for data.

9. SAP ASE can only create databases in even numbers of megabytes, and its “block” size varies by logical page size. For 2 KB and 4 KB pages, the block is 1 MB; for 8 KB pages, 2 MB; and for 16 KB pages, 4 MB. Round the sizes up to a multiple of your block size. In the example below, with a 16 KB page, 550.875 rounds to 552 MB and 105.375 MB rounds to 108 MB; or, if creating a mixed-use database, (550.875 + 105.375) = 656.25 rounds to 660 Mb.

NoteCreate a database with separate log and data sections.

ExampleThe size of the database you should create is:

create database sybdsamdb on data_device = '552M' -- recommended: create separate datalog on log_device = '108M' -- and log sections.

or

create database sybdsamdb on mixed_device = '660M' -- not recommended: mix log and data.

4.1.1 Estimating the sybdsamdb Size Example

An example procedure that estimates the required sybdsamdb database size.

The input parameter is the number of partitions you expect to collect data for. It represents the total number of user data partitions in all databases for which DSAM is enabled. You may count this or estimate it. To count it, see the example for counting user partitions in all trackable databases.



For each database to be managed through DSAM, use the following:

select count(1) from (this database).dbo.syspartitions where id > 255

The total of those counts is the input @ptn_total:

create or replace procedure dsam_database_size @ptn_total int = 10000 asdeclare @agg_freq numeric(2,0) , @agg_days numeric(4,0) , @data_mb numeric(12,2) , @index_mb numeric(12,2) , @log_mb numeric(12,2) , @mb_data int , @mb_log int , @page_k int , @pg_per_mb numeric(3,0) , @rows numeric(15,0) , @rows_page numeric(5,0) , @store_freq numeric(2,0) , @store_days numeric(4,0)set nocount on

These numbers are the default DSAM configuration:

● Aggregate every six hours (four times per day)● Store all data for sx months● Thin out data to one row per seven days● Store thinned-out data for six months

select @agg_freq = 4.0 -- Aggregate 4 times per day. , @agg_days = 183.0 -- Aggregate for 6 months (1 month = 30.5 days) , @store_freq = 7.0 -- Compression period stores 1 record per 7 days , @store_days = 183.0 -- Compression period lasts 6 months

This is the total number of rows to store in DSAMAccessData:

select @rows = ceiling( @ptn_total * (@agg_days * @agg_freq) + @ptn_total * (@store_days / @store_freq

Discover how many rows fit on one page at the current page size:

select @page_k = @@maxpagesize / 1024 select @pg_per_mb = 1024 / @page_kselect @rows_page = case when @page_k = 2 then 34 when @page_k = 4 then 69 when @page_k = 8 then 140 else 281 end

Divide rows by rows/page to get pages. Divide pages by pages/MB to get MB. From that number, estimate index space and log space:

select @data_mb = ceiling( @rows / @rows_page / @pg_per_mb ) select @index_mb = ceiling( @data_mb / 4.0 )select @log_mb = ceiling( (@data_mb + @index_mb) / 4.0 )

Display the calculations results:

print 'Number of rows by default for %1! partitions: %2!, @ %3! rows/page', @ptn_total, @rows, @rows_page



print 'Estimated size:'select @data_mb as 'Data size in Mb' , @index_mb as 'Index size in Mb' , @data_mb + @index_mb as 'Total non-log Mb' , @log_mb as 'Log Mb'

Additionally, estimate the system table size plus additional spare space. The following estimate includes the total size of the model database:

select @data_mb = @data_mb + (sum(size) / @pg_per_mb) from master.dbo.sysusageswhere dbid = db_id('model')

From those results, develop a suggested create database statement:

select @mb_data = convert(int, @data_mb + @index_mb) select @mb_log = convert(int, @log_mb)if @page_k > 2begin

Round the suggested sizes to sizes that can succeed create database with this page size:

select @page_k = @page_k / 2 if (@mb_data & (@page_k - 1)) != 0 select @mb_data = @mb_data + @page_k - (@mb_data & (@page_k - 1)) if (@mb_log & (@page_k - 1)) != 0 select @mb_log = @mb_log + @page_k - (@mb_log & (@page_k - 1))endprint ''print 'Suggested sizes for sybdsamdb:'print 'create database sybdsamdb on [data device] = ''%1!M'' log on [log device] = ''%2!M''', @mb_data, @mb_log

4.1.2 Example of Counting User Partitions in All Trackable Databases

This example demonstrates how to obtain the total partition count of user tables in all databases that are eligible to have DSAM statistics collected.

Any database is permitted except for master, model, temporary, and in-memory databases.

This example includes databases you typically need not monitor, such as sybsystemprocs and sybsystemdb, which contain very few user tables.

set nocount on declare @ptn_ct int , @ptn_now int , @db_name varchar(30) , @cmd varchar(300)select @ptn_ct = 0select @db_name = min(name) from master.dbo.sysdatabaseswhere dbid > 3 and status3 & -536870656 = 0 and status4 & 4096 = 0while @db_name is not nullbegin select @cmd = 'select @ptn_now = count(1) from ' + @db_name + '.dbo.syspartitions where id > 255'



exec (@cmd) select @ptn_ct = @ptn_ct + @ptn_now if @ptn_now != 0 print '%1! partitions in database %2!', @ptn_now, @db_name select @db_name = min(name) from master.dbo.sysdatabases where dbid > 3 and status3 & -536870656 = 0 and status4 & 4096 = 0 and name > @db_nameendprint ''select @ptn_ct as 'Partition count'

4.2 Installing the sybdsamdb Database

Run the installsybdsamdb script to install the sybdsamdb database.

Prerequisites

To run installsybdsamdb:

● Enable the enable monitoring configuration parameter.● Enable the per object statistics active configuration parameter.● Have sa_role or mon_role, or the manage server granular permission.

Procedure

1. Start SAP ASE.2. Go to the scripts directory at:

Option Description

UNIX $SYBASE/$SYBASE_ASE/scriptsWindows %SYBASE%\%SYBASE_ASE%\scripts

3. Use isql to log in to the SAP ASE server and run the following script, where <server_name> is the destination server for the database:

Option Description

UNIX isql -Usa -Ppassword -S<server_name> -i$SYBASE/$SYBASE_ASE/scripts/installsybdsamdb



Option Description

Windows isql -Usa -P***** -S<server_name> -i %SYBASE%\%SYBASE_ASE%\scripts\installsybdsamdb



5 Default Configuration

When you install DSAM, the process creates a default DSAM configuration.

DSAM uses predefined configurations for the following:

● Devices classes● Custom parameters● One predefined access group

Use the DSAM Guidance Wizard from SAP ASE cockpit to customize the configuration.

5.1 Default Device Classes

DSAM has three predefined device classes.

● High Performance (HI) – Fast-access data storage● General Purpose (GP) – General purpose data storage● Online Archive (OA) – Rarely accessed data storage

You can associate devices with the device classes according to the device’s speed, cost, and usage. The two-character designator ("HI", "GP", "OA") shown with each class is the class tag, and is stored as part of the device description and used to associate a device with a class.

The class tags shown are the defaults created when DSAM is installed. You can create others, and need not keep these. No class tag is associated with any device until you make that association. Use the following procedures to perform class-related actions:

Procedure Action

dsam_set_device_class Associates a device with a class.

dsam_add_device_class Creates a new class.

dsam_delete_device_class Removes an existing class.

Related Information

dsam_add_device_class [page 37]dsam_delete_device_class [page 50]dsam_set_device_class [page 61]dsam_update_device_class [page 69]DSAMDeviceClasses [page 32]

Data Store Access Management Users GuideDefault Configuration


5.2 Default Parameters

The default DSAM configuration includes a set of predefined custom parameters, which you can modify.

Parameter Description

Aggregation Frequency Defines how often a DSAM record is added to DSAM access table. One new row per monitored partition is added per aggregation interval. You can modify its frequency to control how fast the DSAM access table grows. Default is six hours, or four rows per day per partition. The highest permitted aggregation frequency is 24 hours, or at least one row per day.

Collection Frequency Defines how often data collection occurs; it will update the latest aggregation row for the partition in DSAM access table. The default is one hour.

Compression Period Definesthe time after it starts compress rows to larger aggregation interval in order to reduce the DSAM access table size. The default period is six months or 183 days, and aggregation frequency is seven days. This means for rows with a timestamp older than 183 days, only the latest available row will be retained for each partition within each seven-day period.

Purge Period Definess the time after which DSAM data is discarded. The default is one year.

5.3 Default Access Group

The default DSAM configuration predefines one access group called Default.

Default access group has three access tiers:

Access Tier Description

Active Data set accessed 500 times per hour or more over the past seven days.

Less Active Data set accessed less than 500 times per hour over the past seven days.

Historical Data set accessed 100 times or less within 30 days.

An access tier is a description of one access pattern over a period of time, such as "500 times or more per hour over the past seven days." An access group is a collection of access tiers which, taken together, describe the ways in which data access patterns may be categorized: for example, hot (very frequent access), normal, and cold (infrequent access). As noted, DSAM predefines one access group, Default, which has three access tiers.



You can modify the Default access group, or create other access groups that describe other data access patterns. However, to be useful, an access group's member tiers must be complete; that is, it should always be possible to pick one access tier from that group that best describes the current access pattern of a given data partition.

To be complete, an access group needs to fulfill two conditions:

● Access tiers do not overlap, at any time period. For example, one access tier is fewer than 60 access counts and another access tier with 50 to 70 access counts within the same time period would violate the rule.

● At least one set of access tiers within an access group must cover all possible counts within a single time period. This ensures that a partition qualifies at least one tier. For example, a group having only access tier of 500 KB to 700 KB access counts within seven days will not satisfy this rule, as access counts 0 to 499,999 and 700,001 to infinity are not covered.

5.4 Default Policies

The default DSAM configuration includes predefined policies.

A policy is a description of what should happen to a data set. This can include storing a data set on devices that have a given storage class, and changing the way the data is compressed. For example, frequently accessed data might best be stored on a fast-access device and kept uncompressed.

Default Policy Description

HI, no compression Stores data on devices of class HI without compression.

GP, compression as-is Stores data on devices of class GP and keeps whatever compression is currently defined for the data.

GP, page compression Stores data on devices of class GP and uses page compression.

GP, row compression Stores data on devices of class GP and uses both page and row compression.

A tier is optionally associated with a policy. Examples include the following:

● Policy DevHistorical_CompressPage to tier Historical



● Policy DevLowCost_CompressRow to tier Less Active● Policy DevHighPerformance_CompressNone to tier Active

NotePolicies are independent of tiers. No tier must have a policy associated with it.

5.4.1 DSAMPartitionEvaluationData

The DSAMPartitionEvaluationData table the results of evaluating DSAM access rules.

Columns

Name Datatype Attribute Description

<group_id> int Not null The ID of the access group to which the table has been assigned..

<table_id> int Null The ID of the partition's owning table.

<owner_id> <owner_id> Null The ID of the table owner.

<dbid> int Not null The ID of the table's database

<dbname> varchar(30) Not null The table's database name.

<object> varchar(300) Null The string of the form "<owner.table>".

<group_name> <group_name> Not null The name of the table's access group.

<tier_name> varchar(255) Not null The access tier’s name to which the table has been assigned.

<tier_id> int Not null The access tier’s ID to which the table has been assigned.

<policy> varchar(255) Not null The policy name of the access tier.



Name Datatype Attribute Description

<policy_id> int Not null The ID of the policy.

<ptn_name> varchar(255) Not null The name of partition that does not match the policy proposed device class or/and compression type.

<ptn_id> int Not null The ID of the partition.

<ptn_segment_name> varchar(255) Not null The segment in which the partition resides.

<ptn_type> varchar(255) Not null The partition type. Valid values are:

● Range● Hash● List● Round-robin

<ptn_size> unsigned int Not null The size of the partition.

<ptn_compression_name>

varchar(255) Not null The compressed type of the partition.

<policy_compression_name>

varchar(255) Not null The proposed compression type according to the policy.

<ptn_device_class_name>

varchar(255) Not null The device class of the device to which the segment resides.

<policy_device_class_name>

varchar(255) The proposed device class to which the segment should move according to the policy

<exec_time> bigdatetime Not null The execution time stamp of the evaluation.

<scheduled_job_name> varchar(255) Not null The name of the scheduled job, if any.

<scheduled_job_owner> varchar(30) Null The owner of the scheduled job, if any.

<occurrences> int Null The number of times that this recommendation has been issued.



Indexes

● Unique clustered index on <dbid>, <table_id>, <ptn_id>

5.5 DSAM Monitoring

Use procedure dsam_set_collection to identify databases to be monitored.

No databases are monitored by default. You can monitor any database except:

● master● model● Temporary databases● In-memory databases

Monitoring other system databases is permitted, although probably unnecessary.

DSAM monitoring stores information about data access, which can become large. To minimize the required storage, turn on DSAM monitoring only for the databases that are necessary to control storage.

5.5.1 monDeviceSegmentUsage Monitoring Table

The monDeviceSegmentUsage monitoring tables displays pages used in a database by device and segment.

Queries on monDeviceSegmentUsage can take a very long time to run, because the table's data is generated by reading system catalogs and scanning disks at the time the query is issued. To minimize the time spent getting results, specify as many of the table’s keys (DBID, DeviceNumber, SegmentNumber) as possible, to limit the amount of work SAP ASE performs to obtain results. The bigger the database or the more databases that you scan, the longer the query takes to produce results.

Columns

Name Datatype Attributes Description

DBID int Not null The database ID from sysdatabases. Unique identifier for the database.

DeviceNumber int Not null The device ID from sysdevices. Unique identifier for the device.

SegmentNumber int Not null The segment ID from the local database’s segments. Unique identifier for the segment.




PagesUsed bigint Not null How many logical pages are in use on this DBID, device, or segment.

Pages are allocated in groups of 8, even though some of the allocated pages might not be used. Thus, subtracting this number from the total size of the device for that database in sysusages will say how much space is available for new objects. It does not say whether or not space is available for rows added to objects already stored in that place.

Stranded int Not null How many logical pages should not be in this segment.

Not required by DSAM. It is an indication of whether or not objects should be where they are and the column is usually zero. When it is not zero, the device’s segment map, as stored in sysusages does not permit storage of that object on this device. This situation is rare, but can occur after the customer does sp_placeobject to assign a partition to a different segment: the existing data for that partition does not automatically move, so if the new segment is not permitted where that data currently resides then the data is ‘stranded’.

This example displays space used on the master device.

1> select * from monDeviceSegmentUsage 2> where DeviceNumber = 03> order by DBID, SegmentNumber4> compute sum(PagesUsed) by DBID5> DBID DeviceNumber SegmentNumber PagesUsed Stranded ----------- ----------- ----------- -------------------- ----------- 1 0 0 2336 0 1 0 1 552 0 1 0 2 16 0 Compute Result:-------------------- 2904 DBID DeviceNumber SegmentNumber PagesUsed Stranded ----------- ----------- ----------- -------------------- ----------- 2 0 0 744 0 2 0 1 104 0 2 0 2 8 0 Compute Result:-------------------- 856



DBID DeviceNumber SegmentNumber PagesUsed Stranded ----------- ----------- ----------- -------------------- ----------- 3 0 0 744 0 3 0 1 104 0 3 0 2 8 0 Compute Result:-------------------- 856 DBID DeviceNumber SegmentNumber PagesUsed Stranded ----------- ----------- ----------- -------------------- ----------- 31513 0 0 768 0 31513 0 1 104 0 31513 0 2 16 0 Compute Result:-------------------- 888

This table only shows databases where the information is cached in memory. If a display of a database not currently cached is requested, no rows will return. Specify use <database>, then query the table again. The act of using a database caches its information.

5.5.2 monDeviceSegmentIO Monitoring Table

The monDeviceSegmentIO monitoring table displays the DSAM collection information by device and segment.

Columns


DBID int Not null The database ID associated with this record. Unique identifier for the database.

DeviceNumber int Not null The device ID from sysdevices. Unique identifier for the device.

SegmentNumber int Not null The segment ID from the local database’s segments. Unique identifier for the segment.

PhysicalReads64 unsigned bigint Not null The number of physical reads recorded for this DBID, device, or segment.

PhysicalWrites64 unsigned bigint Not null The number of writes recorded for this DBID, device, or segment.



6 Access Counts Collection

DSAM tables are located in the sybdsamdb database. DSAM collects two kinds of access counts: access by partition, and access by device and segment combination.

Both sets are stored in the DSAMAccessData table:

Table 1: DSAMAccessData Information


dbid smallint Not null The database ID associated with this record.

time_stamp bigdatetime Not null The aggregation interval timestamp for this record.

physical_read_count unsigned bigint

Not null The physical read count for monitoring information.

write_count unsigned bigint

Not null The physical write count from monitoring information.

stat_id tinyint Not null A code describing the kind of count.

index_id tinyint Not null The index ID associated with the count.

segment_id smallint Not null The segment ID associated with the count.

vdevno int The device number associated with the count.

table_id intl Not null The table ID associated with the count.

partition_id int Not null The partition ID associated with the count.

Not all columns are relevant for each kind of count. Counts for partitions do not use vdevno or segment_id. Counts for device and segment combinations do not use index_id, table_id, or partition_id. These columns are all collected into a single row because it makes the total storage requirement smaller. Collections are done on the interval described by a collection frequency, but records are stored with a time_stamp controlled by an aggregation frequency.

During each collection, if the collector finds that a record already exists for the count it is collecting, it updates that record’s read_count and write_count but does not change the time_stamp. If no such record exists, the collector adds a new record, applying the timeStamp of the current collection interval.

Data Store Access Management Users GuideAccess Counts Collection


7 Data Access Patterns

DSAM tracks data access patterns with the monOpenPartitionActivity monitoring table, which tracks activity for all partitions currently in use, and monDeviceSegmentIO, which tracks I/O per database by which segment and which device it was performed on.

DSAM queries the monOpenPartitionActivity monitoring table on a set schedule to obtain physical read and write counts for the currently active set of partitions in databases managed by DSAM. These counts are recorded in a statistics history table, which DSAM then uses to compute the I/O rate for managed partitions.

Enable both enable monitoring and per object statistics active configuration parameters to use monOpenPartitionActivity.

DSAM controls data placement on storage devices according to user-supplied rules regarding its access patterns. DSAM tracks only physical I/O and how many times it is accessed on the disk, and not how many times the data be read. If an access requires that data is read from disk or written to disk, it is counted.

Data in the DSAM history table is aggregated according to an aggregation interval. No matter how many times data collection occurs during an aggregation interval, only one record is written to the history table for each partition. This helps keep the table to a manageable size. History records are retained only until a purge period, at which point they are discarded. Further, after a compression period, history records are thinned out, so that the table retains only a fraction of the previously collected history records for each partition. The default aggregation interval is six hours. The default compression period is six months. The default purge period is one year.

Use the following procedures to modify these intervals:

● dsam_set_aggregation_frequency● dsam_set_compression_period● dsam_set_purge_period

Related Information

dsam_set_aggregation_frequency [page 57]dsam_set_compression_period [page 60]dsam_set_purge_period [page 63]


Data Store Access Management Users GuideData Access Patterns

8 DSAM and Non-Volatile Cache

Solid state drive (SSD) storage devices are much faster than with a hard disk because they use non-volatile flash memory. Using DSAM with such SSD non-volatile cache changes the rules for read/write from/to the hard disk device, and affects the counters that count the physical and logical I/O in DSAM.

Action Description

Physical writes When you use non-volatile cache, all writes from the buffer manager are written to the non-volatile cache first. These dirty pages in the non-volatile cache are written to the disk in a delayed fashion by the background process. In DSAM, the counter for physical I/O increments only when the page is written from the non-volatile cache to the disk.

Physical reads When you use non-volatile cache, frequently read pages are transferred to the non-volatile cache when evicted from the main memory named cache. That means that when a page is needed, DSAM performs its search in the non-volatile cache first before reading from the disk. The disk is accessed only if the page is not found in the non-volatile cache. In DSAM, the read from SSD cache is treated as a logical read. The counter for physical read increments only when the read is from disk.

Data Store Access Management Users GuideDSAM and Non-Volatile Cache


9 Viewing DSAM in GUI Mode

Manage and view DSAM data from SAP ASE cockpit, a Web-based tool for monitoring the status and availability of SAP ASE servers.

For more information, see SAP Adaptive Server Enterprise Cockpit documentation.


Data Store Access Management Users GuideViewing DSAM in GUI Mode

10 DSAM API

DSAM API is a shared API that can be utilized by a third party for partitions access information.

The installer script installsybdsamdb provided with DSAM creates the sybdsamdb database and populates it with a sample DSAM installation. Users must have sa_role to run installsybdsamdb. To use DSAM, monitoring must be enabled and job scheduler must be installed.

The DSAM API is a collection of views and stored procedures that are installed by the DSAM install script.

Related Information

Views [page 27]Procedures [page 33]

10.1 Views

Views organize configuration data and present it with descriptive column names, replacing the column names in sysattributes where the configuration is stored.

Views present data from the access history table organized according to what type of statistic it represents.

10.1.1 DSAMAccessByPartition

Selects stored access counts from DSAMAccessData for partitions.

Columns

Table 2: DSAMAccessByPartition Columns

Name Datatype Description

dbid smallint The partition’s database ID.

table_id int The ID of the partition’s owning table. indexID is the ID of the partition’s owning index. This is always zero, indicating a data partition.

Data Store Access Management Users GuideDSAM API



index_id int The ID of the index.

partition_id int The ID of the partition.

time_stamp bigdatetime The aggregation period for which this statistic was collected.

physical_read_ount unsigned bigint The number of physical reads recorded for this partition as of the last time data aggregation occurred.

write_count unsigned bigint The number of physical writes recorded for this partition as of the last time data aggregation occurred.

10.1.2 DSAMAccessBySegment

Selects stored access counts from DSAMAccessData for segments on devices.

Columns

Table 3: DSAMAccessBySegment Columns


dbid smallint The segment's database ID.

segment_id smallint The ID of the segment for which this statistic was recorded.

vdevno int The virtual number of the device.

time_stamp bigdatetime The aggregation period for which this statistic was collected.

physical_read_count unsigned bigint The number of physical reads recorded for this partition as of the last time data aggregation occurred.

write_count unsigned bigint The number of physical writes recorded for this segment or device as of the last time data aggregation occurred.



10.1.3 DSAMAccessGroups

Lists the names and IDs of currently defined access groups.

Columns

Table 4: DSAMAccessGroups Columns


group_id int The ID of the access group.

name varchar(255) The access group's name.

comment varchar(255) A descriptive comment for the access group, or NULL if none was provided.

10.1.4 DSAMAccessTiers

Lists currently defined access tiers.

Columns

Table 5: DSAMAccessTiers Columns


group_id int The ID of the access group to which this access tier belongs.

member_id int The ID of this access tier within its access group.

name varchar(255) The access tier’s name.

comment varchar(255) A descriptive comment for the access tier, or NULL if none was provided.

frequency int The time period represented by this access tier, given as a number of seconds.

lower int The lower bound of the count range represented by this access tier, or NULL is the same as zero.

upper int The upper bound of the count range represented by this access tier. NULL means no upper limit.

color int An integer representing the RGB code color to be used when displaying this access tier, or NULL if none was specified.



10.1.5 DSAMCompressionLevels

Lists the compression levels currently available for defining policies.

There are four defined compression levels:

Level Description

Default Uses the compression configuration from the table

None Data in this partition should not be compressed.

Row Data in this partition should be row-compressed

Page Data in this partition should be both row- and page-compressed

Columns


id int The ID representing the compression level.

name varchar(255) The compression level’s name.

comment varchar(255) A descriptive comment for the compression level, or NULL if none was provided.

10.1.6 DSAMCustomizations

Lists the customization options currently defined for DSAM.

Columns

Table 6: DSAMCustomizations Columns


id int The database or object ID.




tag char(2) A two-character identifier for the customization. The valid values are:

● AF – Aggregation Frequency, the period of time controlling how often new statistics records are to be created. IntValue shows this period in seconds.

● CF – Collection Frequency, the period of time showing how often statistics are to be collected. IntValue shows this period in seconds.

● CP – Compression Period, the time after which data is to be retained less often. IntValue shows this period in seconds. Info1 shows the length of time in seconds that should be used as the aggregation frequency for this period.

● PP – Purge Period, the time after which collected data should be deleted. IntValue shows this period in seconds.

● DB – database for which data is being collected.ID shows the database ID. Name shows the database name.

● OB – an object (such as a partition) for which data is being collected. ID shows the object’s database ID. IntValue shows the object’s ID. Name shows the table name.

name varchar(255) The customization’s name.

comment varchar(255) A descriptive comment for the compression level, or NULL if none was provided.

int_value int The purge period, aggregation frequency, or collection frequency in seconds. Also the object's ID when used with Tag OB.

info1 int Any additional descriptive information required to identify the objec.t

info2 int Any additional descriptive information required to identify the object.

info3 int Any additional descriptive information required to identify the object.



10.1.7 DSAMDeviceClasses

Lists the device classes currently available for defining policies and for applying to storage devices.

Columns

Table 7: DSAMDeviceClasses columns


id int An ID representing the device class.

tag char(2) A two-character identifier for the device class. This is the value that will identify the device class in sysdevices.

name varchar(255) The device class name.

comment varchar(255) A descriptive comment for the device class, or NULL if none was provided.

10.1.8 DSAMPolicies

Shows the complete list of policies currently defined. Policy definitions use the name of the associated device storage class and compression level.

Columns

Table 8: DSAMPolicies Columns


id int The ID assigned to the policy.

name varchar(255) The policy's name.

device_class char(2) The two-character identifier of the device class used by this policy, or blank if no device class is specified.

device_class_name varchar(255) The name of the device class used by this policy.

dompression int The integer ID for the compression used by this policy.

compression_name varchar(255) The name of the compression used by this policy.

comment varchar(255) A description of the policy.



10.1.9 DSAMPolicyBindings

Shows the list of policy bindings currently defined.

Columns

Table 9: DSAMPolicyBindings Columns


tier_id int The ID of the access tier.

policy_id int The ID of the access tier.

tier_name varchar(255) The name of the access tier

policy_name varchar(255) The name of the policy.

10.2 Procedures

Procedures provide controlled access to set or change configurations in sysattributes and to do chores such as collecting data or purging old data.

10.2.1 dam_add_access_group

Determines the class ID to be used by DSAM operations when working with the sysattributes table, and creates a new access group.

Syntax

dsam_add_access_group <@grpname> [, <@comment>]

Parameters

<@grpname>



Is the name of the group to add. Access group names must be unique. The datatype is varchar(255).

<@comment>(Optional) Is the comment describing the access group. The datatype is varchar(255).

Examples

dsam_add_access_group Example

Creates a new default access group:

exec dsam_add_access_group 'Default', 'The default DSAM access group'

Related Information

DSAMPartitionEvaluationData [page 18]

10.2.2 dsam_add_access_tier

Adds an access tier to an access group.

Syntax

dsam_add_access_tier <@grpname>, <@mbrname>, <@time>, <@ct_lo>, <@ct_hi>[, <color>][, <@comment>]

Parameters

<@grpname>Is the name of the access group that will contain the new tier. The datatype is varchar(255).

<@mbrname>Is the name of the tier to be added. The datatype is varchar(255).

<@time>



Is the time interval to be used by this tier, given as a number of seconds. The datatype is int.

<@ct_lo>Is the lower bound of the count range to be used by this tier. If specified as NULL, it indicates a lower limit of 0. The datatype is int.

<@ct_hi>Is the upper bound of the count range to be used by this tier. If specified as NULL, it indicates infinity (i.e., no upper limit). The datatype is int.

<@color>(Optional) is the color used when displaying data accesses matching this tier. It is an integer RGB value. If not specified, the default is 0 (black). The datatype is int.

<@comment>(Optional) is a comment describing this access tier. The datatype is varchar(255).

Examples

Example 1

Adds an access tier named 'Historical' to the default access group:

exec dsam_add_access_tier 'Default', 'Historical', 2592000, 0, 100, 255, 'Data not accessed within 30 days'

Example 2

Adds an access tier named 'Less Active' to the default access group:

exec dsam_add_access_tier 'Default', 'Less Active', 604800, 0, 83999, 16776960, 'Normal data access pattern'

Example 3

Adds an access tier named 'Active' to the default access group:

exec dsam_add_access_tier 'Default', 'Active', 604800, 84000, NULL, 16711680, 'Data accessed 500 times per hour or more this week'

10.2.3 dsam_add_attribute

Adds data to the DSAM configuration. It is used by other DSAM procedures for inserting configuration data.

Syntax

dsam_add_attribute <@attr >, <@objno >, <@cinfo>, <@comment>,



<@ival>,<@oi1>, <@oi2>, <@oi3>

Parameters

<@attr>Is the attribute value to insert in the attribute column in sysattributes. The values are:

● 0 – DSAM attribute types● 1 – DSAM storage classes● 2 – DSAM access tiers● 3 – DSAM compression levels● 4 – DSAM policies● 5 – DSAM customizations● 6 – DSAM tier policy definition

<@objno>Is the value to insert in the object column in sysattributes. The datatype is int.

<@cinfo>Is the value to insert in the object_cinfo column in sysattributes. The datatype is varchar(255).

<@comment>Is the value to insert in the comments column in sysattributes. The datatype is varchar(255).

<@type>Is the value to insert in the object_type column in sysattributes. The datatype is char(2).

<@ival>Is the value to insert in the int_value column in sysattributes. The datatype is int.

<@oi1>Is the value to insert in the object_info1 column in sysattributes. The datatype is int.





Examples

dsam_add_attribute Example

exec dsam_add_attribute 5, 0, 'Aggregation Frequency', 'How often DSAM adds a new record for collected data', 'AF', 21600

10.2.4 dsam_add_device_class

Adds a device class.

Syntax

dsam_add_device_class <@tag>, <@name>[, <@comment>]

Parameters

<@tag>Is a two-character code for the device class to be added. The datatype is char(2). Once created, you cannot alter the value of <@tag> for a device class. To change the tag, delete the class then add a new class with the correct tag.

<@name>Is the name of the device class. The datatype is varchar(255).

<@comment>(Optional) Is the comment that describes this device class. The datatype is varchar(255).

Examples

dsam_add_device_class Example

Adds a high-performance device class:

exec dsam_add_device_class 'HI', 'High Performance', 'Fast access data storage'



10.2.5 dsam_add_policy

Adds a policy description.

Syntax

dsam_add_policy <@name>, <@class>, <@compress> [, <@comment>]

Parameters

<@name>Is the policy’s name. Policy names must be unique. The datatype is varchar(255).

<@class>Is the device class to which objects matching this policy should belong. This may be the name of the class or its two-character tag. NULL indicates that the policy does not specify what class objects should belong to. The datatype is varchar(255).

<@compress>Is the compression level to apply to objects matching this policy. NULL indicates that objects matching this policy should keep the compression level they currently have. The datatype is varchar(255). Valid compression levels are:

● 'None' – does not compress data.● 'Default' – compression data that is currently set to remain unchanged.● 'Page' – compress data at the page level.● 'Row' – in addition to page-level compression, compress individual rows.

<@comment>(Optional) Describes this policy. The datatype is varchar(255).

Examples

dsam_add_policy Example

Adds a 'GP, compression as-is' policy description:

dsam_add_policy 'GP, compression as-is', 'GP', 'Default', 'Normal storage, no change to compression'



Usage

Restrictions:

● If <@class> is NULL, then <@compress> cannot be NULL or 'Default'.● If <@compress> is NULL or 'Default', <@class>cannot be NULL.● The combination of <@class> and <@compression> must be unique.

10.2.6 dsam_aggregation_timestamp

Determines a timestamp to be applied to an interval.

Syntax

dsam_aggregation_timestamp <@ts> [, <@tag>]

Parameters

<@ts>Is the requested timestamp. It is set by the procedure. The datatype is bigdatetime output.

<@tag>(Optional) Is the key for which timestamp to return. The datatype is varchar(2). Valid values are:

● AF – (default) returns the timestamp for the current aggregation interval.● CP – returns the timestamp of the current compression period.● PP – returns the timestamp of the current purge period.

Examples

dsam_aggregation_timestamp Example

declare @agg_now bigdatetime exec dsam_aggregation_timestamp @agg_now output, 'AF'



10.2.7 dsam_check_access_auth

Determines whether a user fulfills the requirements to run a DSAM procedure.dsam_check_access_auth is called by other DSAM procedures to verify user authorization. It returns zero if the user is authorized, or an error message ID if the user is not authorized.

Syntax

dsam_check_access_auth(<@options>, <@proc>)

Parameters

<@options>Is a string requesting optional checks. The datatype is varchar(200). The string may contain any or all of:

● mon – does not permit this procedure to run unless SAP ASE options enable monitoring and per object statistics active are enabled.

● sybdsamdb – requires that the procedure is called while the user is in sysdsamdb.● tran – does not permit this procedure to run if an ongoing transaction is present.

<@proc>Is the procedure name that called this procedure. It is used as a parameter for error messages. The datatype is varchar(200).

Examples

dsam_check_access_auth Example

Determines whether a user fulfills the requirements to run a procedure named sample_procedure:

dsam_check_access_auth 'mon,tran', 'sample_procedure'



10.2.8 dsam_check_access_time

Checks to make sure that the time is not less than the minimum acceptable time range, nor greater than the compression period..

Syntax

Code Syntax

dsam_check_access_time <@me>[, <@time>, <@mintime>, <@maxtime>]

Parameters

<@me>Is the name of the calling function. The datatype is varchar(30).

<@time>Is the time expressed in seconds. If <@time> is null, this procedure simply returns output parameters. The datatype is int.

<@mintime>Is the minimum acceptable time. The datatype is int output.

<@maxtime>Is the maximum acceptable time. The datatype is int output.

Examples

Example

Checks to make sure that the time of 86400 seconds for the function named sample_procedure is within the aggregation frequency:

exec @error = dsam_check_access_time 'sample_procedure', 86400, @mintime output, @maxtime output

Usage

Receive a time expressed in seconds. Check to assure that the time is not less than our aggregation frequency, which is the minimum acceptable time range, nor greater than the compression period, the maximum



acceptable time. Return 0 if the check succeeds, an error code otherwise. Return frequency frequency and compression period as output parameters.

10.2.9 dsam_check_count_range

Receives low and high values for an I/O count range, checking the range for validity.

Syntax

Code Syntax

dsam_check_count_range <@ct_lo>, <@ct_hi>

Parameters

<@ct_lo>Is the low value for an I/O count. The datatype is int.

<@ct_hi>Is the high value for an I/O count. The datatype is int.

Examples

dsam_check_count_range Example

Receives an I/O count range with low value of 0 and high value of 1075000:

exec @error = dsam_check_count_range 0, 1075000

Usage

Returns 0 if the range is valid, an error number otherwise. Counts are valid if <@ct_lo> is less than or equal to <@ct_hi> and both are greater than or equal to zero.



10.2.10 dsam_check_group_rules

Determines whether a database meets all the criteria necessary to be managed by an access group.

Syntax

dsam_check_group_rules (<@grpid>, <@dbid>, <@class>)

Parameters

<@grpid>Is the ID of the access group to be checked. The datatype is int.

<@dbid>Is the database ID of a database to be managed by the access group. The datatype is int.

<@class>Is the name or tag of a device class proposed for addition to the access group’s policies. The datatype is varchar(255). If the <@class> parameter is not null, the procedure determines whether all the databases managed by that access group have devices of the specified class, so that it may safely use a policy associated with the access group. If checks pass, it returns zero. If it detects an error, it returns an error message ID.

Examples

Example 1

Determines whether database 4 meets all the criteria necessary to be managed by access group 1.

dsam_check_group_rules 1, @dbid=4

Example 2

Determines whether all the databases managed by access group 1 have devices predefined as high-performance:

dsam_check_group_rules 1, @class='HI'



10.2.11 dsam_check_period_range

Checks a time against low and high bounds.

Syntax

Code Syntax

dsam_check_period_range <@me>, <@tag>, <@sec_this>, <@sec_lo>, <@sec_hi>

Parameters

<@me>Is the name of the calling procedure. The datatype is varchar(255).

<@tag>Is the key for which timestamp to return. The datatype is varchar(2).

<@sec_this>Is the input value to be checked. The datatype is int.

<@sec_lo>Is the low bound. The datatype is int.

<@sec_hi>Is the high bound. The datatype is int.

Examples

dsam_check_period_range Example

Checks a time for sample_procedure for a collection frequency of 32767 seconds against low and high bounds:

exec @error = dsam_check_period_range 'sample_procedure', 'CF', 32767, @coll_min, @coll_max



10.2.12 dsam_check_tier_overlap

Determines whether the combination of a given frequency with low count and high count overlaps an existing member tier of a specified access group.

Syntax

Code Syntax

dsam_check_tier_overlap <@groupid>, <@time>, <@ct_lo>, <@ct_hi>[, <@mbr_id>]

Parameters

<@groupid>Is the ID of the access group. The datatype is int.

<@time>Is the frequency to be checked. The datatype is int.

<@ct_lo>Is the low count. The datatype is int.

<@ct_hi>Is the high count. The datatype is int.

<@mbr_id>(Optional) Iis the ID of a member tier that should not be checked. The datatype is int.

Examples

dsam_check_tier_overlap Example

Determines whether the combination of a given frequency of 86400 seconds overlaps with an existing member tier in access group 1:

exec @error = dsam_check_tier_overlap 1, 86400, @ct_lo, @ct_hi



10.2.13 dsam_class_id

Determines the class ID to be used by DSAM operations when working with table sysattributes, and is used by other DSAM procedures and operations for constructing SQL statements.

Syntax

dsam_class_id <@dsamclass>

Parameters

<@dsamclass>Is the class ID used by DSAM that is set by the procedure. The datatype is int output.

10.2.14 dsam_data_cleanup

Removes records from DSAMAccessData that are older than the currently configured purge period, and consolidates records that are older than the currently configured compression period.

Syntax

dsam_data_cleanup

Parameters

None.



10.2.15 dsam_data_collector

The main DSAM data collection procedure, dsam_data_collector collects per-partition access counts and per-segment monitoring information for all currently monitored databases.When dsam_data_collector runs during a collection period, it inserts new entries into DSAMAccessData for counts that do not exist, and updates counts for entries that do exist.

Syntax

dsam_data_collector

Parameters

None.

10.2.16 dsam_delete_access_group

Removes an indicated access group and any access tiers it contains, as well as remove object bindings and evaluation data.

Syntax

dsam_delete_access_group <@grpname>[,<@force>]

Parameters

<@grpname>Is the name of the group to be removed. The datatype is varchar(255).

<@force>Is a flag, 0 (the default) or 1. If 1, the procedure also removes associated entries in DSAMPartitionEvaluationData and associated object bindings. If not 1, the procedure refuses to run if such entries exist.



Examples

dsam_delete_access_group Example

Deletes the default access group:

dsam_delete_access_group 'Default'

In the example, quotes are required around 'Default'.

10.2.17 dsam_delete_access_tier

Removes an indicated access tier from its access group, and can also remove any policy binding for the access tier.

Syntax

dsam_delete_access_tier <@grpname>, <@mbrname> [,<@force>]

Parameters

<@grpname>Is the name of the access group containing the tier to be removed. The datatype is varchar(255).

<@mbrname>Is the name of the tier to be removed. The datatype is varchar(255).

<@force>Is a flag, 0 (the default) or 1. If the value is 1, the procedure also removes any policy bound to the indicated access tier. If not, the procedure refuses to run if a binding exists.

Examples

dsam_delete_access_tier Example

Deletes the default access tier from the Historical tier:

dsam_delete_access_tier 'Default', 'Historical'



10.2.18 dsam_delete_attribute

Used by other DSAM procedures, dsam_delete_attribute removes data from the DSAM configuration.

Syntax

dsam_delete_attribute <@attr>[, <@type>, <@objno>, <@cinfo>,<@intv>, <@info1>, <@info2>, <@info3>]

Parameters

<@attr>Is the ID of the attribute to be removed. The datatype is int.

<@type>Is the tag of the attribute to be removed. The datatype is char(2).

<@objno>Is an object specifier for the attribute to be removed. The datatype is int.

<@cinfo>Is a string describing the attribute to be removed. The datatype is varchar(255).

<@intv>Is an integer value describing the attribute to be removed. The datatype is int.

<@info1>, <@info2 int>, <@info3>Are additional information describing the value to be removed. The datatype is int.

Examples

dsam_delete_attribute Example

Deletes device class "HI":

dsam_delete_attribute 1, @type='HI'

Usage

The parameter interpretation varies according to the encoding scheme of the attribute to be removed.



10.2.19 dsam_delete_db_binding

An internal procedure that is called by SAP ASE when a database is dropped or loaded, dsam_delete_db_binding removes DSAM information from DSAMAccessData for a specified database, along with any access group bindings.

Syntax

dsam_delete_db_binding <@dbid>

Parameters

<@dbid>Is the ID of the database to be removed. The datatype is int.

10.2.20 dsam_delete_device_class

Removes an indicated device class.

Syntax

dsam_delete_device_class <@name>

Parameters

<@name>Is the name or tag of the device class to be removed. The indicated class cannot be in use by any device, and cannot be named by any policies. The datatype is varchar(255).



10.2.21 dsam_delete_policy

Removes the indicated policy, along with any tier bindings it may have.

Syntax

dsam_delete_policy <@name>

Parameters

<@name>Is the name of the policy to be removed. The datatype is varchar(255).

Related Information

dsam_add_policy [page 38]dsam_set_policy [page 62]dsam_update_policy [page 69]

10.2.22 dsam_device_io_counts

Shows the current device and segment I/O counts from the monDeviceSegmentIO monitoring table.

Syntax

dsam_device_io_counts [<@dbname>]

Parameters

<@dbname>(Optional) Displays counts for the named database. If you do not specify this parameter, counts are displayed for all of the databases in the table. The datatype is varchar(30).



Examples

Example

Shows the current device and segment I/O counts from the sybdsamdb database:

1> dsam_device_io_counts sybdsamdb 2> goDatabase------------------------------sybdsamdbDevice Segment Reads Writes------- ---------- -------------------- --------------------dsamdev logsegment 2693 1395dsamdev system 38804 654 dsamdev default 896327 971

Usage

See Reference Manual: Tables for details about monDeviceSegmentIO.

10.2.23 dsam_get_message

Obtains the message ID and text of a DSAM message by its short identifier.

Syntax

dsam_get_message (<@msgid>, <@msg_text>)

Parameters

<@msgid>Is an input and output parameter. On:

● Input – <@msgid> holds the short identifier of the message of interest.● Output – <@msgid> holds that message’s ID from sysusermessages.

The datatype is int output.

<@msg_text>Is an output parameter to receive the message text. The datatype is varchar(200) output.



Examples

dsam_get_message Example

This example returns the true message ID and the message text of DSAM message 2:

declare @error int, @message varchar(200) select @error = 2 exec dsam_get_message @error output, @message output

This operation requires sa_role.

Usage

If message text is available in the user’s current configured language, the procedure returns the language text. Otherwise it returns the US English.

10.2.24 dsam_get_period_seconds

A helper procedure used by other DSAM procedures, dsam_get_period_seconds retrieves the time period expressed by a configurable time period.

Syntax

dsam_get_period_seconds <@me>, <@tag>, <@seconds>

Parameters

<@me>Is the name of the calling procedure. It is used when this procedure needs to print an error message. The datatype is varchar(255).

<@tag>Identifies the period of interest. The datatype is varchar(2). Valid values are:

● AF – Aggregation Frequency, the period of time controlling how often new statistics records are to be created.

● CF – Collection Frequency, the period of time showing how often statistics are to be collected.

● CP – Compression Period, the time after which data is to be retained less often.



● PP – Purge Period, the time after which collected data should be deleted.

<@seconds>Receives the time period identified by the input tag. The datatype is int output.

Examples

dsam_get_period_seconds Example

declare @mintime int exec dsam_get_period_seconds 'sample_procedure', 'AF', @mintime output

10.2.25 dsam_get_table_info

Receives a table name, then returns the table’s ID and owner name.

Syntax

dsam_get_table_info <@object>, <@owner>, <@tabid>, <@dbname>

Parameters

<@object>Is an input and output parameter. On:

● Input – <@object> holds the table name of interest, which may be a simple name or a string of the form owner.table.

● Output – <@object> receives the table name.

The datatype is varchar(300).

<@owner>Is an output parameter that receives the owner name of the table. The datatype is varchar(30).

<@tabid>Is an output parameter that receives the table’s ID. The datatype is int.

<@dbname>Holds the name of the database containing the table of interest. The datatype is varchar(30).



Examples

Example

declare @owner varchar(30), @table varchar(300), @tabid int select @table = 'my_table' dsam_get_table_info @table output, @owner output, @tabid output, 'my_DB'

10.2.26 dsam_get_time_value

Converts a character-coded time specifier into an integer time range.

Syntax

dsam_get_time_value <@time_string>, <@time_seconds>[, <@silent>]

Parameters

<@time_string>Is the time specifier to be decoded. It holds a string-encoded number and an optional units specifier. The number may be an integer such as ‘123’ or fractional number such as ‘1.5’. The specifier is a single letter: ‘S’ (seconds, the default), ‘N’ (minutes), ‘H’ (hours), ‘D’ (days), ‘W’ (weeks), ‘M’ (months), or ‘Y’ (years). For this parameter, a “month” is 30.5 days and a “year” is 365.25 days. The datatype is varchar(20).

<@time_seconds>Is an output parameter that receives the integer time period expressed by <@time_string>. The datatype is int.

<@silent>(Optional) Determines whether the procedure prints an error for an invalid input string. If this parameter is zero (the default), a message is printed; otherwise errors are silent. The datatype is int.

Examples

dsam_get_time_value Example

This example obtains the number of seconds in 30 minutes ('30n'):

declare @30_min int



dsam_get_time_value '30n', @30_min output

10.2.27 dsam_help_access_group

Displays information about an indicated access group, or about all access groups.

Syntax

dsam_help_access_group <@grpname>

Parameters

<@grpname>(Optional) Is the name of the group to display. If this parameter is null (the default), the procedure prints the names, IDs, and descriptive comments for all currently defined access groups. If it is not null, the procedure prints detailed information for the indicated access group. The datatype is varchar(255).

10.2.28 dsam_ptn_eval

Evaluates the current set of monitored partitions against their controlling policies.dsam_ptn_eval inserts or updates information in table DSAMPartitionEvaluationData to show the current recommendation for partitions needing action.

Syntax

dsam_ptn_eval

Parameters

None.



Related Information

DSAMPartitionEvaluationData [page 18]

10.2.29 dsam_set_aggregation_frequency

Determines how often DSAM creates new records for I/O counts.

Syntax

dsam_set_aggregation_frequency <@time>, <@auto_cf>

Parameters

<@time>Is the time specifier to be decoded, and holds a string-encoded number and an optional units specifier. The number may be an integer such as ‘123’ or fractional number such as ‘1.5’. The specifier is a single letter: ‘S’ (seconds, the default), ‘N’ (minutes), ‘H’ (hours), ‘D’ (days), ‘W’ (weeks), ‘M’ (months), or ‘Y’ (years). For this parameter, a “month” is 30.5 days and a “year” is 365.25 days. This period may not be less than the currently configured collection frequency, nor more than the lesser of 1 day or the shortest frequency currently configured for any access tier. As created by the installer procedure, this is preset to 6 hours (‘6H’). The datatype is varchar(20).

<@auto_cf>Determines whether dsam_set_aggregation_frequency should automatically set the collection frequency to a fraction of the requested aggregation frequency. The default is 0, "do not set collection frequency". When this parameter is set to 1, the collection frequency is set as follows:

● If aggregation frequency is less than six hours, then a half hour.● If aggregation frequency is between six and 12 hours, then one hour.● Otherwise, two hours.



Examples

Example 1

This sets the aggregation frequency to six hours:

dsam_set_aggregation_frequency '6h'

Example 2

This sets the aggregation frequency to four hours, and instructs the procedure to also configure the collection frequency:

dsam_set_aggregation_frequency '4h', 1

Because the aggregation frequency is less than six hours, the collection frequency is set to 30 minutes (a half hour).

10.2.30 dsam_set_collection

Turns data collection on or off for a database.

Syntax

dsam_set_collection <@dbname>, <@set>

Parameters

<@dbname>Is the name of the database using data collection. <@dbname> cannot be the name of a system database, temporary database, or in-memory database. The datatype is varchar(30).

<@set>Is a string describing the requested action:

● ON causes data collection to be started for the indicated database.● OFF causes collection to be stopped. Turning collection OFF does not remove any

previously stored collection data for the indicated database. It simply stops DSAM from collecting any data for that database in future.




Examples

Example 1

This sample informs DSAM that it should collect data for database my_db:

dsam_set_collection 'my_db', 'ON'

Example 2

This example informs DSAM that it should stop collecting data for database my_db:

dsam_set_collection 'my_db', 'OFF'

Turning collection OFF does not remove any previously stored collection data for my_db. It simply stops DSAM from collecting any data for that database in future.

10.2.31 dsam_set_collection_frequency

Sets how often DSAM collects I/O counts (aggregation frequency).

Syntax

dsam_set_collection_frequency <@time>

Parameters

<@time>Is the time specifier to be decoded. It holds a string-encoded number and an optional units specifier. The number may be an integer such as '123' or fractional number such as '1.5'. The specifier is a single letter: 'S' (seconds, the default), 'N' (minutes), 'H' (hours), 'D' (days), 'W' (weeks), 'M' (months), or 'Y' (years). For this parameter, a "month" is 30.5 days and a "year" is 365.25 days. This period may not be less than 30 minutes nor more than the currently configured aggregation frequency. The maximum collection frequency is the value set for dsam_set_collection_frequency. As created by the installer procedure, this is preset to 1 hour ('1H'). The datatype is varchar(20).



Examples

dsam_set_collection_frequency Example

This example sets the collection frequency to one hour:

dsam_set_collection_frequency '1H'

10.2.32 dsam_set_compression_period

Sets the time period after which DSAM begins consolidating data to save space.

Syntax

dsam_set_compression_period <@time>

Parameters

<@time>Specifies the desired compression period. It holds a string-encoded number and an optional units specifier. The number may be an integer such as '123' or fractional number such as '1.5'. The specifier is a single letter: 'S' (seconds, the default), 'N' (minutes), 'H' (hours), 'D' (days), 'W' (weeks), 'M' (months), or 'Y' (years). For this parameter, a "month" is 30.5 days and a "year" is 365.25 days. As created by the installer procedure, this is preset to 6 months ('6M'), 183 days. The datatype is varchar(20). This period:

● Cannot be less than the greater of 1 month or the longest frequency currently configured for any access tier

● Cannot be longer than the currently configured compression period

The minimum compression period is the longer of 1 month or the longest current access tier frequency. The maximum compression period is the value set for dsam_set_compression_period.



Examples

dsam_set_compression_period Example

This example sets the compression period to one year:

exec @error = dsam_set_compression_period '1Y'

Related Information

dsam_set_purge_period [page 63]

10.2.33 dsam_set_device_class

Applies a supplied class to an indicated device. If the supplied class is null, the device’s class is set to null.

Syntax

dsam_set_device_class <@device>, <@class>

Parameters

<@device>Is the device that is having its class changed. The datatype is varchar(30). The device:

● Is the name of a disk device in sysdevices.● Cannot already have a class that is used by a database, and the database has a

segment that names that device’s current class, and at least one partition is assigned to that segment.

<@class>Is the name or two-character tag of a device class defined in view DSAMDeviceClasses, or NULL if the device’s class is set to null. The datatype is varchar(255).



Examples

Example 1

This example sets the class of device device_A to GP (general purpose):

dsam_set_device_class 'device_A', 'GP'

Example 2

This example sets the class of device device_A to General Purpose, instead of using the device class tag as seen in the previous example:

dsam_set_device_class 'device_A', 'General Purpose'

10.2.34 dsam_set_policy

Attaches a policy to or removes a policy from an access tier.

Syntax

dsam_set_policy <@policy>, <@set>[, <@tier_id>][, <@group>, <@tier>]

Parameters

<@policy>Is the policy to be added to or removed from the tier. If the policy is being removed, this parameter may have special values ‘*’ or ‘%’ to mean “any policy that is bound to the access tier”. The datatype is varchar(255).

<@set>Is an indicator showing whether the policy is being added or removed. The datatype is varchar(3). Valid values are:

● ON means the policy is being added.● OFF means it is being removed.

<@tier_id>(Optional) Is the ID of the access tier to be affected. If this parameter is not null, you need not specify <@group> and <@tier>. The datatype is int.

<@group>(Optional) Is the name of the access group to be affected. If this parameter and <@tier> are not null, you need not specify <@tier_id>. The datatype is varchar(255).



<@tier>(Optional) Is the name of the access tier to be affected. If this parameter and <@group> are not null, you need not specify <@tier_id>. The datatype is varchar(255).

Examples

dsam_set_policy Example

This example attaches policy "Hi, no compression" to tier "Active" of access group "Default":

dsam_set_policy 'HI, no compression', 'ON', NULL, 'Default', 'Active'

Usage

● Either <@tier_id> alone or <@group> with <@tier> is sufficient to identify the access tier that will be affected.

● If all of <@tier>, <@group>, and <@tier_id> are null, the policy is removed from all access tiers it is bound to. If <@policy> has the special value ‘*’ or ‘%’, this removes all policies from all access tiers.

● This procedure does not delete the policy itself. To do that, use procedure dsam_delete_policy.

Related Information

dsam_delete_policy [page 51]dsam_add_policy [page 38]dsam_update_policy [page 69]

10.2.35 dsam_set_purge_period

Sets the DSAM purge period to the specified <@time>, a string that may indicate a time such as '6M' or a character-coded integer number of seconds. The purge period may not be shorter than the compression period.

Syntax

dsam_set_purge_period <@time>



Parameters

<@time>Is the string-coded time describing the desired purge period. It holds a string-encoded number and an optional units specifier. The number may be an integer such as '123' or fractional number such as '1.5'. The specifier is a single letter: 'S' (seconds, the default), 'N' (minutes), 'H' (hours), 'D' (days), 'W' (weeks), 'M' (months), or 'Y' (years). For this parameter, a "month" is 30.5 days and a "year" is 365.25 days. As created by the installer procedure, this is preset to one year (‘1Y’), 365.25 days. The datatype is varchar(20). A purge period (when records should be thrown away) can be any length of time, but cannot be less than the compression period (when data should be kept less frequently).

Examples

dsam_set_purge_period Example

Sets the purge period to one year:

exec @error = dsam_set_purge_period '1Y'

Related Information

dsam_set_compression_period [page 60]

10.2.36 dsam_show_period

Displays the time period associated with a given input.

Syntax

dsam_show_period <@name>[, <@group_name>]

Parameters

<@name>



Is the string-encoded time period to display. The datatype is varchar(255). This may be:

● One of the tags shown for procedure dsam_get_period_seconds, or its associated name

● An access tier name, in which case <@group_name> cannot be null● A time string as shown for procedure dsam_get_time_value.

<@group_name>(Optional) Is the name of an access group in which <@name> is an access tier.The datatype is varchar(255).

Examples

dsam_show_period Example

This example displays the time period for the "Historical" tier of the "Default" access group:

dsam_show_period 'Historical', 'Default'

10.2.37 dsam_track_object

Binds an object to an access group, or removes an existing binding. The object may be an entire database or a table within a database.

Syntax

dsam_track_object (<@grpname>, <@set>, <@dbname>[, <@table>)]

Parameters

<@grpname>Is the name of the access group affected by dsam_track_object. If binding is being removed, <@grpname> can be one of the special values '*' or '%' to mean "any group". The datatype is varchar(255).

<@set>Is an indicator for the action being performed. The datatype is varchar(3). The valid values are:

● ON means that the object is being bound to the access group.



● OFF means a binding is being removed.

<@dbname>Is the name of the affected database. The datatype is varchar(30).

<@table>(Optional) Is the name of a table within <@dbname> to bind to the indicated group. The datatype is varchar(255).

Examples

dsam_track_object Example

This example turns tracking on for database "my_DB" using access group "Default":

dsam_track_object 'Default', 'ON', 'my_DB'

Usage

● If the <@table> parameter is○ Null, the command works on a database-level binding.○ Not null, the command works on a table-level binding.

● Databases may be bound to one access group, with individual tables in that database bound to a different access group. This permits tables to have special rules that are different from those of the general database.

● If a database-level binding:○ Already exists in the indicated access group, adding a table-level binding to that group has no effect.○ Is added to an access group, any existing table-level bindings for that database are removed from the

group.○ Is being removed from an access group but the group has only table-level bindings, all the database's

table-level bindings are removed from that group.● If a binding is being removed and <@grpname> has the special value '*' or '%', the indicated binding is

removed from all access groups in which it appears. If <@table> is null, this removes both database- and table-level bindings for the indicated database.

● For error recovery, the indicated database may be specified as a string-encoded database ID. (For example, the 'master' database could be specified as '1'.) This allows removing bindings for databases that were previously dropped but whose bindings were not removable at that time.



10.2.38 dsam_update_access_group

Modifies the name or comment associated with an existing access group.

Syntax

dsam_update_access_group <@grpid>[, <@grpname>,<@comment>]

Parameters

<@grpid>Is the ID of the access group to be updated, and is available via procedure dsam_help_access_group. The datatype is int.

<@grpname>(Optional) Is the name for the indicated access group. The datatype is varchar(255).

<@comment>(Optional) Is the comment for the indicated access group. The datatype is varchar(255).

Examples

dsam_update_access_tier Example

This example changes the name of access group 1 to "My access group":

dsam_update_access_group 1, @grpname='My access group'

10.2.39 dsam_update_access_tier

Modifies the information associated with an existing access tier.

Syntax

dsam_update_access_tier <@mbr_id>[, <@name>, <@time>, <@ct_lo>, <@ct_hi>, <@color>, <@comment>]



Parameters

<@mbr_id>Is the ID of the access tier to be modified, and is available via procedure dsam_help_access_group. The datatype is int.

<@name>(Optional) Is the name for the indicated access tier that is unique within its access group. The datatype is varchar(255).

<@time>(Optional) Is the time interval for the tier, in number of seconds. The datatype is int.

<@ct_lo>(Optional) Is the lower bound of the count range of the tier. The datatype is int.

<@ct_hi>(Optional) Is the upper bound of the count range of the tier. The datatype is int.

<@color>(Optional) Is the color used when displaying data accesses matching the tier. It is an integer RGB value.

<@comment>(Optional) Describes the access tier. The datatype is varchar(255).

Examples

dsam_update_access_tier Example

This example changes the name of access tier 10002 to "Sample access tier" (10002 is tier 2 of access group 1.):

dsam_update_access_tier 10001, @name='Sample access tier'

Usage

If any of <@time>, <@ct_hi>, or <@ct_hi> are not null, the tier’s access characteristics are revalidated. The tier’s low bound and high bound cannot overlap with any other access tier in this group that has the same time interval.



10.2.40 dsam_update_device_class

Modifies the name or comment associated with an existing device class.

Syntax

dsam_update_device_class <@tag> [, <@name>,<@comment>]

Parameters

<@tag>Is the two-character device class tag to be updated. The datatype is char(2).

<@name>(Optional) Is the unique name for this device class. The datatype is varchar(255).

<@comment>(Optional) Is the comment for this device class. The datatype is varchar(255).

Examples

dsam_update_device_class Example

This example changes the comment for device class "HI" to "High-speed access":

dsam_update_device_class 'HI', @comment='High-speed access'

10.2.41 dsam_update_policy

Modifies the information associated with an existing policy.

Syntax

dsam_update_policy <@policy_id>[, <@name>, <@class>, <@compress>, <@comment>]



Parameters

<@policy_id>Is the ID of the policy to be updated.

<@name>(Optional) Is the unique name for the policy.The datatype is varchar(255).

<@class>(Optional) Is the device class that the policy should use. This parameter may have the special value ‘ ’ (a blank) to indicate that the policy does not specify a device class. The datatype is varchar(255).

<@compress>(Optional) is the compression level that the policy should use. Valid values are:

● ‘None’ – do not compress data.● ‘Default’ – compression data which is currently set to remain unchanged.● ‘Page’ – compress data at the page level.● ‘Row’ – in addition to page level compression, compress individual rows.


<@comment>(Optional) Is the comment describing this policy. The datatype is varchar(255).

Examples

dsam_update_policy Example

Changes the name of policy 1 to "New policy name":

dsam_update_policy 1, @name='New policy name'

Usage

Restrictions:

● If <@class> is NULL, then <@compress> cannot be NULL or ‘Default’.● If <@compress> is NULL or ‘Default’, then <@class> cannot be NULL.● The combination of <@class> and <@compress> must be unique.

Related Information

dsam_add_policy [page 38]



dsam_delete_policy [page 51]dsam_set_policy [page 62]

10.2.42 dsam_update_table_binding

Updates or removes historical entries and attribute bindings for a given object. This procedure is called internally by SAP ASE when a database is dropped or loaded, or when a table gains or loses a clustered index.

NoteDo not run this procedure. SAP ASE calls it internally to maintain DSAM historical information and makes recommendations for managed objects.

Syntax

dsam_update_table_binding <@dbid int>, <@objid int>, <@indid int>, <@partitionid int>, <@toggle int>

Parameters

<@dbid int>Is the database ID of the affected database.

<@objid int>Is the ID of a table within that database, or 0 for “any”.

<@indid int>Is the ID of an index on the supplied table.

<@partitionid int>Is the ID of a partition within the indicated index.

<@toggle int>Is a switch saying whether index IDs 0 and 1 are to be swapped: 1 if yes, 0 if no.



Examples

dsam_update_table_binding Example

This example removes any existing history entries and object bindings for partition 1030141 of index 0 of table 1030141 in database 4:

dsam_update_table_binding 4, 1030141, 0, 1030141, 0



Important Disclaimers and Legal Information

Coding SamplesAny software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, unless damages were caused by SAP intentionally or by SAP's gross negligence.

AccessibilityThe information contained in the SAP documentation represents SAP's current view of accessibility criteria as of the date of publication; it is in no way intended to be a binding guideline on how to ensure accessibility of software products. SAP in particular disclaims any liability in relation to this document. This disclaimer, however, does not apply in cases of wilful misconduct or gross negligence of SAP. Furthermore, this document does not result in any direct or indirect contractual obligations of SAP.

Gender-Neutral LanguageAs far as possible, SAP documentation is gender neutral. Depending on the context, the reader is addressed directly with "you", or a gender-neutral noun (such as "sales person" or "working days") is used. If when referring to members of both sexes, however, the third-person singular cannot be avoided or a gender-neutral noun does not exist, SAP reserves the right to use the masculine form of the noun and pronoun. This is to ensure that the documentation remains comprehensible.

Internet HyperlinksThe SAP documentation may contain hyperlinks to the Internet. These hyperlinks are intended to serve as a hint about where to find related information. SAP does not warrant the availability and correctness of this related information or the ability of this information to serve a particular purpose. SAP shall not be liable for any damages caused by the use of related information unless damages have been caused by SAP's gross negligence or willful misconduct. All links are categorized for transparency (see: http://help.sap.com/disclaimer).

Data Store Access Management Users GuideImportant Disclaimers and Legal Information


http://help.sap.com/disclaimer/

www.sap.com/contactsap

© 2015 SAP SE or an SAP affiliate company. All rights reserved.No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an SAP affiliate company. The information contained herein may be changed without prior notice.Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors. National product specifications may vary.These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP or its affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP or SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. All other product and service names mentioned are the trademarks of their respective companies.Please see http://www.sap.com/corporate-en/legal/copyright/index.epx for additional trademark information and notices.

http://www.sap.com/contactsap

http://www.sap.com/corporate-en/legal/copyright/index.epx

http://www.sap.com/corporate-en/legal/copyright/index.epx

sap ase data store access management users guide en

Documents

dsam elements

viewing dsam

dsam api

data access patterns248

dsam database installation

access tiers

access groups

default access group