The way to do this is to create a copy of the table using In this example I will assume I have a table called apple in database plum, and the column you want to make unique is called fruit select * into tempdb..apple from plum..apple next create an identity column on this new table: alter table tempdb..apple add pip numeric(5,0) identity The following query will now eliminate the duplicate entries of fruit; delete from tempdb..apple where pip in (select a.pip from tempdb..apple a, tempdb..apple b where a.fruit = b.fruit and a.pip > b.pip) You will now have the table unique on the column fruit and you can put the data back into the original table (minus the identity column created with the following: First delete or truncate the original plum..apple then Select fruit,… , <columns in plum..apple> Into plum..apple From tempdb..apple

Setting up a historical server recording

As a rough guide just to see how it all works after you have the monitor server and historical server running execute the following on the historical server (log in using isql);

Example

Suppose you want to record, once every second, the reads, writes, and total I/O per device on an Adaptive Server that is monitored by the SMS1100 Monitor Server. You can use the following steps to record the data, create a table to accept the data, and then use the bcp utility to import the data into the table. In this example, the view data file that is created by Historical Server for the view is d1_1_device_io.

1. Use isql to log in to Historical Server.
2. Record the data by using the following commands:

hs_create_recording_session capella_mon, 30

go

hs_create_view device_io,

    "device name", "value for sample",

    "device reads", "value for sample",

    "device writes", "value for sample",

    "device i/o", "value for sample"

go

hs_initiate_recording

go

1. Use isql to log in to Adaptive Server. Because the recording session contains only one view (“device_io”), create just one table in the pubs2 database to store data for that view by using the following transact sql commands:

use history

go

create table device_io

(

sample_time          datetime         not null,

device_name          varchar (255)    null,

device_reads_val     int              null,

device_writes_val    int              null,

device_io_val        int              null,

)

go

1. After the recording session is complete, you can use bcp to import data from the view’s data file into the database table. Use the following commands:

bcp history..device_io in d1_1_device_io -c 
-e d1_1_device_io.err -U username 
-P password -S server –t\,

Appendix

Table of data items and definitions

Table A-1 describes the data items available for inclusion in recording session views. The table lists data items in alphabetical order and provides the following information about each one:

• A definition
• An Adaptive Server release dependency designation
• A result or key designation

Table A-1: Data items and definitions Data item	Description
Application Execution Class Version: 11.5 and later	Configured execution class, if any, for a given application name. Because of override features, the configured execution class does not necessarily reflect the priority and engine group at runtime. The following notations are used in the monitor: Blank – no execution class is configured for this application. Execution class name – the execution class configured for the application in general, without consideration for specific logins. (That is, an execution class is configured for the application with null scope.) Execution class name followed by asterisk (*) – in addition to the execution class configured for the application in general, additional execution classes are configured for specific logins. (That is, an execution class is configured for the application with null scope and at least one additional execution class is configured for the application with a specific scope.) An asterisk (*) without execution class – no execution class is configured for the application in general, but execution classes are configured for specific logins using this application. (That is, at least one execution class is configured for the application with a specific scope.) Type: Result
Application Name Version: 11.0 and later	Name of application for which other statistics are being accumulated. Views that contain Application Name report only on processes that are active as of the end of the sample period. Application name is mutually exclusive with Process ID in a view. Type: Key
Blocking Process ID Version: 11.0 and later	ID of the process that holds a lock that another process is waiting for, if any. A returned value of zero means that the other process is not blocked. Type: Result
Cache Efficiency Version: 11.0 and later	The number of cache hits per second per megabyte of a particular data cache. Type: Result
Cache Hit Pct Version: 11.0 and later	The fraction of the page reads for objects bound to a particular data cache that are satisfied from the cache computed from the following formula: cache_hits / (cache_hits + cache_misses) * 100 Type: Result
Cache Hits Version: 11.0 and later	The number of times a page read was satisfied from a particular data cache. Type: Result
Cache ID Version: 11.0 and later	The ID of a data cache in Adaptive Server version 11.0 or later. Particular database tables and indexes may be bound to a specific data cache, or all objects in a database may be bound to the same data cache. No object may be bound to more than one data cache. Type: Key
Cache Misses Version: 11.0 and later	Number of times a page read was satisfied from disk rather than from a particular data cache. This data item includes failed attempts to locate pages in the data caches during page allocation. Therefore, the number of physical page reads reported may be overstated. If this occurs, the percentage of data cache misses reported by Cache Hit Pct is understated. Type: Result
Cache Name Version: 11.0 and later	The name of a data cache. Particular database tables and indexes may be bound to a specific data cache, or all objects in a database may be bound to the same data cache. No object may be bound to more than one cache. Type: Key
Cache Prefetch Efficiency Version: 11.0 and later	A percentage comparing the count of pages being reused in large I/O buffers (the denominator) to the number of those pages that were ever referenced by Adaptive Server. When a buffer is reused, all of the pages in it are counted as reused. Buffers are reused when there are no free buffers in the pool to accept a new physical read from a database device. The number of reused pages referenced by Adaptive Server divided by the result of the number of pages per buffer multiplied by the number of reused buffers indicates the effectiveness of large I/O fetches. Regardless of how many buffer pools are configured in a named data cache, Adaptive Server only uses two of them. It uses the 2K buffer pool and the pool configured with the largest-sized buffers. Prefetch effectiveness does not apply to the 2K buffer pool, since 2K grabs are not considered large I/O. Therefore, prefetch effectiveness applies to the largest buffer pool in the cache. For example, if a data cache has pools of buffers sized 2K, 8K, and 16K, the 8K pool is not used, and this metric reflects the effectiveness of large I/O in the 16K buffer pool. If the ratio is large, then prefetching is effective; otherwise, prefetching is not providing much benefit. This may suggest that a buffer pool should be eliminated (or it may imply that a clustered index on some table is fragmented, and that the index should be dropped and re-created). Type: Result
Cache Refer and Reuse Version: 11.0 and later	The number of pages in buffers that were both referenced and reused. This count is employed when determining the efficiency of prefetching buffers (see Cache Prefetch Efficiency). This data item, unlike Cache Prefetch Efficiency, includes activity in the default 2K buffer pool. See Cache Prefetch Efficiency for a definition of buffer reuse. Type: Result
Cache Reuse Version: 11.0 and later	The number of pages in buffers that were reused. A large value indicates a high rate of turnover (of buffers in this memory pool), and suggests that the pool may be too small. A zero value suggests that this memory pool may be too large. This data item, unlike Cache Prefetch Efficiency, includes activity in the default 2K buffer pool. See Cache Prefetch Efficiency for a definition of buffer reuse. Type: Result
Cache Reuse Dirty Version: 11.0 and later	The number of times that a buffer that was reused had changes that needed to be written. A non-zero value indicates that the wash size is too small. See Cache Prefetch Efficiency for a definition of buffer reuse. Type: Result
Cache Size Version: 11.0 and later	The size of a data cache, in megabytes. Type: Result
Cache Spinlock Contention Version: 11.0 and later	The fraction of the requests for a data cache’s spinlock that were forced to wait. spinlock_waits / spinlock_requests Type: Result
Code Memory Size Version: 11.0 and later	Amount of memory in bytes allocated to Adaptive Server. Type: Result
Connect Time Version: 11.0 and later	Number of seconds elapsed since the process was started or since the session was started, whichever is smaller. Type: Result
CPU Busy Percent Version: 11.0 and later	Percentage of total server CPU ticks that the Adaptive Server CPU was busy. Type: Result
CPU Percent Version: 11.0 and later	If used in a view with Process ID, this is the percentage of time a single process was in the Running state over the time all processes were in the Running state. If used in a view with Application Name, this is the percentage of time the set of processes running a given application were in the Running state over the time all processes were in the Running state. Type: Result
CPU Time Version: 11.0 and later	If used in a view with no keys, this data item is the total CPU busy time, in seconds, on the server. If used with keys, this data item is the busy time, in seconds, that was used by each process, application, or engine. Process ID and Application Name are mutually exclusive. Type: Result
CPU Yields Version: 11.0 or later	Number of times that the Adaptive Server yielded to the operating system. Type: Result
Current Application Name Version: 11.0 and later	The name of the application that is currently executing on a particular process. Type: Result
Current Engine Version: 11.0 and later	Number of the Adaptive Server engine on which the process was running most recently. Type: Result
Current Execution Class Version: 11.5 and later	The name of the execution class under which a process is currently running. Type: Result
Current Process State Version: 11.0 and later	Current state of a process. See Process State for definitions of the possible states. Type: Result
Current Stmt Batch ID Version: 11.5 and later	The ID of a particular query batch being executed on a particular process. Type: Result
Current Stmt Batch Text Version: 11.5 and later	The text of a particular query batch being executed for a particular process. This text may be only an initial substring of the complete text in a query batch. The amount of text stored in this field is determined by the Adaptive Server max SQL text monitored configuration parameter. Type: Result
Current Stmt Batch Text Byte Offset Version: 11.5 and later	The byte offset to the beginning of a statement within the query batch or stored procedure being executed for a particular process. If both: Current Stmt Procedure Database ID is equal to 0 and Current Stmt Procedure ID is equal to 0 then the statement is the currently executing SQL statement in the query batch. Otherwise, the statement is the currently executing SQL statement in the stored procedure uniquely identified by these two IDs. Type: Result
Current Stmt Batch Text Enabled Version: 11.5 and later	Reports whether Adaptive Server (version 11.5 and later) is saving the SQL text of the currently executing query batches and, if so, how much. Value of 0 = saving SQL text disabled Value of 1 or more = maximum number of bytes of batch text per server process that can be saved. Type: Result
Current Stmt Context ID Version: 11.5 and later	The ID that uniquely identifies a stored procedure invocation within a particular query batch being executed for a particular process. Type: Result
Current Stmt CPU Time Version: 11.5 and later	The amount of time (in seconds) that the currently executing SQL statement has spent in the running state. Type: Result
Current Stmt Elapsed Time Version: 11.5 and later	The amount of time (in seconds) that the currently executing SQL statement has been running. Type: Result
Current Stmt Line Number Version: 11.5 and later	The number of the line (within a query batch or stored procedure) that contains the beginning of the currently executing SQL statement for a particular process. The currently executing SQL statement is in the query batch if both Current Stmt Procedure Database ID is equal to 0 and Current Stmt Procedure ID is equal to 0. Otherwise, the currently executing SQL statement is in the stored procedure uniquely identified by these two IDs. Type: Result
Current Stmt Locks Granted After Wait Version: 11.5 and later	Number of lock requests by the currently executing SQL statement that were granted after waiting. Type: Result
Current Stmt Locks Granted Immediately Version: 11.5 and later	Number of lock requests by the currently executing SQL statement that were granted immediately or were not needed (because sufficient locking was already held by the requestor). Type: Result
Current Stmt Locks Not Granted Version: 11.5 and later	Number of lock requests by the currently executing SQL statement that were denied. Type: Result
Current Stmt Logical Reads Version: 11.5 and later	Number of data page reads satisfied from cache or from device reads by the currently executing SQL statement. Type: Result
Current Stmt Number Version: 11.5 and later	The number of the statement (appearing in a query batch or stored procedure) that is the currently executing SQL statement for a particular process. The currently executing SQL statement is in the query batch if both Current Stmt Procedure Database ID is equal to 0 and Current Stmt Procedure ID is equal 0. Otherwise, the currently executing SQL statement is in the stored procedure uniquely identified by these two IDs. A value of 0 indicates partial result data for the currently executing SQL statement. In other words, this SQL statement began executing before monitoring began. Performance metrics are available but numbers reflect only the time period since the start of monitoring. Type: Result
Current Stmt Page I/O Version: 11.5 and later	Number of combined logical page reads and page writes by the currently executing SQL statement. Type: Result
Current Stmt Physical Reads Version: 11.5 and later	Number of data page reads by the currently executing SQL statement that could not be satisfied from the data cache. Type: Result
Current Stmt Procedure Database ID Version: 11.5 and later	The database ID of the stored procedure (including triggers, a special kind of stored procedure) that contains the currently executing SQL statement for a particular process. If the currently executing SQL statement is not contained in a stored procedure, this ID is 0. Type: Result
Current Stmt Procedure Database Name Version: 11.5 and later	The database name of the stored procedure (including triggers, a special kind of stored procedure) that contains the currently executing SQL statement for a particular process. If the currently executing SQL statement is not contained in a stored procedure, this name is “**NoDatabase**”. Type: Result
Current Stmt Procedure ID Version: 11.5 and later	The ID of the stored procedure (including triggers, a special kind of stored procedure) that contains the currently executing SQL statement for a particular process. If the currently executing SQL statement is not contained in a stored procedure, this ID is 0. Type: Result
Current Stmt Procedure Name Version: 11.5 and later	The name of the stored procedure (including triggers, a special kind of stored procedure) that contains the currently executing SQL statement for a particular process. If the currently executing SQL statement is not contained in a stored procedure, this name is “**NoObject**”. Type: Result
Current Stmt Procedure Owner Name Version: 11.5 and later	The owner name of the stored procedure (including triggers, a special kind of stored procedure) that contains the currently executing SQL statement for a particular process. If the currently executing SQL statement is not contained in a stored procedure, this name is “**NoOwner**”. Type: Result
Current Stmt Procedure Text Version: 11.5 and later	The text of a particular stored procedure (including triggers, a special kind of stored procedure) being executed for a particular process. If both Current Stmt Procedure Database ID is equal to 0 and Current Stmt Procedure ID is equal to 0 then a stored procedure is not currently executing and this text is a null-terminated empty string (“”). If the text is not available (because this stored procedure was compiled and its text was discarded, or because the text is stored in an encrypted format), then this text is a null-terminated empty string (“”). Type: Result
Current Stmt Query Plan Text Version: 11.5 and later	The text of the query plan for a particular query being executed for a particular process. If the text is not available (because the Adaptive Server has removed this plan from its catalog of query plans), then this text is a null-terminated empty string (“”). Type: Result
Current Stmt Start Time Version: 11.5 and later	The date and time, in the time zone of Adaptive Server, when the currently executing SQL statement began running. If this SQL statement began running before monitoring began, then this result is the date and time that activity was first encountered for this statement. Type: Result
Current Stmt Text Byte Offset Version: 11.5 and later	The byte offset to the beginning of a statement within the query batch or stored procedure being executed for a particular process. If both Current Stmt Procedure Database ID is equal to 0 and Current Stmt Procedure ID is equal to 0, then the statement is the currently executing SQL statement in the query batch. Otherwise, the statement is the currently executing SQL statement in the stored procedure uniquely identified by those two IDs. Type: Result
Database ID Version: 11.0 and later	Unique identification of a database. Type: Key
Database Name Version: 11.0 and later	Name of the database. Type: Result
Deadlock Count Release: 11.0 and later	Number of deadlocks. Type: Result
Demand Lock Version: 11.0 and later	A character string (Y or N) that indicates whether or not a lock that has been upgraded to demand lock status. Type: Result
Device Hit Percent Version: 11.0 and later	The fraction of device requests is computed by multiplying the quotient of device hits divided by device misses plus device misses by 100. Type: Result
Device Hits Version: 11.0 and later	Number of times access to a device was granted. Type: Result
Device I/O Version: 11.0 and later	Combination of device reads and device writes. Type: Result
Device Misses Version: 11.0 and later	Number of times access to a device had to wait. Type: Result
Device Name Version: 11.0 and later	Name of a database device defined in Adaptive Server. Type: Key
Device Reads Version: 11.0 and later	Number of reads made from a device. Type: Result
Device Writes Version: 11.0 and later	Number of writes made to a device. Type: Result
Elapsed Time Version: 11.0 and later	The time increment, in seconds, either from one data refresh to the next (sample) or from the creation of the view to the present (session). Type: Result
Engine Number Version: 11.0 and later	Number of the Adaptive Server engine. Type: Key
Host Name Version: 11.0 and later	The name of the host computer that established a particular connection. Type: Result
Index Logical Reads Release: 11.0 and later	Number of index page reads satisfied from cache and from database devices. Type: Result
Index Physical Reads Version: 11.0 and later	Number of index page reads that could not be satisfied from the data cache. Type: Result
Kernel Process ID Version: 11.0 and later	An Adaptive Server process identifier that remains unique over long periods of time. Type: Key
Kernel Structures Memory Size Version: 11.0 and later	Amount of memory in bytes allocated to the kernel structures. Type: Result
Large I/O Denied Version: 11.0 and later	The number of times the buffer manager did not satisfy requests (of the optimizer) to load data into a buffer in this data cache by fetching more than one contiguous page from disk at a time. Type: Result
Large I/O Performed Version: 11.0 and later	The number of times the buffer manager satisfied requests (of the optimizer) to load data into a buffer in this data cache by fetching more than one contiguous page from disk at a time. Type: Result
Large I/O Requested Version: 11.0 and later	The number of times the optimizer made requests (of the buffer manager) to load data into a buffer in this data cache by fetching more than one contiguous page from disk at a time. Type: Result
Lock Count Version: 11.0 and later	Number of locks. Type: Result
Lock Hit Percent Version: 11.0 and later	Percentage of successful requests for locks. Type: Result
Lock Result Version: 11.0 and later	Result of a logical lock request. Lock result values are: 1 – granted immediately. 2 – not needed; requestor already held a sufficient lock. 3 – waited; requestor waited. 4 – did not wait; lock was not available immediately and the requestor did not want the lock request to be queued. 5 – deadlock; requestor selected as deadlock victim. 6 – interrupted; the lock request was interrupted by attention condition. Type: Key
Lock Results Summarized Version: 11.0 and later	Lock results summarized at a granted or not granted level. 1 – the lock result summary granted is composed of the lock results: granted, not needed, and waited. 2 – the lock result summary not granted is composed of the lock results: did not wait, deadlock, and interrupted. Type: Key
Lock Status Version: 11.0 and later	Current status of a lock which includes lock status values: 1- held and blocking. 2 – held and not blocking. 3 – requested and blocked. 4 – requested and not blocked. Type: Key
Lock Status Count Version: 11.0 and later	Number of locks in each lock status. This is a snapshot value. Type: Result
Lock Type Version: 11.0 and later	Adaptive Server protects tables or data pages currently used by active transactions by locking them. Adaptive Server employs the following lock types: 1 – exclusive table lock. 2 – shared tablelock. 3 – exclusive intent lock. 4 – shared intent lock. 5 – exclusive page lock. 6 – shared page lock. 7 – update page lock. 8 – exclusive row lock. 9 – shared row lock. 10 – update row lock. Type: Key
Locks Being Blocked Count Version: 11.0 and later	Number of locks being blocked by this process that holds this “held_and_blocking” lock. Type: Result
Locks Granted Immediately Version: 11.5 and later	Number of locks that were granted immediately, without having to wait for another lock to be released. Type: Result
Locks Granted After Wait Version: 11.5 and later	Number of locks that were granted after waiting for another lock to be released. Type: Result
Locks Not Granted Version: 11.5 and later	Number of locks that were requested but not granted. Type: Result
Log Contention Percent Version: 11.0 and later	The percentage of times, of the total times when a user log cache was flushed into the transaction log, that it had to wait for the log semphore. A high percentage may indicate that the user log cache size should be increased. Type: Result
Logical Page Reads Version: 11.0 and later	Number of data page reads per unit of time, whether satisfied from cache or from a database device. Type: Result
Login Name Version: 11.0 and later	Login name associated with Adaptive Server processes. Type: Result
Most Active Device I/O Version: 11.0 and later	Number of combined reads and writes against the device with the most activity during a given time interval. Type: Result
Most Active Device Name Version: 11.0 and later	Name of the device with the largest number of combined reads and writes during a given time interval. Type: Result
Net Bytes Received Version: 11.0 and later	Number of network bytes received. Type: Result
Net Bytes Sent Version: 11.0 and later	Number of network bytes sent. Type: Result
Net Default Packet Size Version: 11.0 and later	Default network packet size. Type: Result
Net I/O Bytes Version: 11.0 and later	Total number of network bytes sent and received. Type: Result
Net Max Packet Size Version: 11.0 and later	Configured maximum size for a network packet. Type: Result
Net Packet Size Received Version: 11.0 and later	Average size of network packets received. Type: Result
Net Packet Size Sent Version: 11.0 and later	Average size of network packets sent. Type: Result
Net Packets Received Version: 11.0 and later	Number of network packets received. Type: Result
Net Packets Sent Version: 11.0 and later	Number of network packets sent. Type: Result
Number of Engines Version: 11.0 and later	Number of engines configured for Adaptive Server. Type: Result
Number of Processes Version: 11.0 and later	Number of processes currently running on Adaptive Server, or, if used with the key Application Name, the number of processes currently running a given application. Type: Result
Object ID Version: 11.0 and later	ID of a database object. The object returned is a database table, a stored procedure, or a temporary table. Object IDs might be negative numbers. The object IDs that Adaptive Server assigns to temporary tables can be positive or negative. Type: Key
Object Name Version: 11.0 and later	Database object name. The string **TempObject** is reported for temporary tables. Type: Result
Object Type Version: 11.0 and later	Type of database object: 0 – none. 1 – stored procedure (including triggers). 2 – table. Type: Result
Owner Name Version: 11.0 and later	Name of an objects’s owner. Type: Result
Page Cache Size Version: 11.0 and later	Amount of memory in bytes allocated for the page cache. Type: Result
Page Hit Percent Version: 11.0 and later	Percentage of times that a data page read could be satisfied from cache without requiring a physical page read. Type: Result
Page I/O Version: 11.0 and later	Combined total of logical page reads and page writes. Type: Result
Page Number Version: 11.0 and later	Data page number for a particular lock or lock request. Type: Key
Page Writes Version: 11.0 and later	Number of pages written to a database device. Type: Result
Physical Page Reads Version: 11.0 and later	Number of data page reads that could not be satisfied from the data cache. Type: Result
Procedure Buffer Size Version: 11.0 and later	Amount of memory in bytes allocated for the procedure buffer. Type: Result
Procedure CPU Time Version: 11.0 and later	Number of seconds of CPU time spent executing a stored procedure. Type: Result
Procedure Database ID Version: 11.0 and later	Database ID of the active stored procedure. Type: Key
Procedure Database Name Version: 11.0 and later	Database name of the active stored procedure. Type: Key
Procedure Elapsed Time Version: 11.0 and later	Number of seconds elapsed during a stored procedure execution. All statistic types valid with this data item report time in units of seconds. For example, “Procedure Elapsed Time”, “Average for Session” reports the average number of elapsed seconds per procedure execution. Type: Result
Procedure Execution Class Version: 11.5 and later	Configured execution class, if any, for a given stored procedure. Type: Result
Procedure Execution Count Version: 11.0 and later	Number of times a stored procedure, or a line in a stored procedure, was executed. Type: Result
Procedure Header Size Version: 11.0 and later	Amount of memory in bytes allocated for the procedure header. Type: Result
Procedure Hit Percent Version: 11.0 and later	Percentage of times that a procedure execution found the procedure’s query plan in procedure cache and available for use. Type: Result
Procedure ID Version: 11.0 and later	Active stored procedure. Active indicates the top-level stored procedure was called. Type: Key
Procedure Line Number Version: 11.0 and later	Stored procedure line number. Type: Key
Procedure Line Text Version: 11.0 and later	Entire text of the stored procedure. Type: Result
Procedure Logical Reads Version: 11.0 and later	Number of requests to execute a stored procedure, whether satisfied from procedure cache or with a read from sysprocedures. Type: Result
Procedure Name Version: 11.0 and later	Name of the active stored procedure. Type: Result.
Procedure Owner Name Version: 11.0 and later	Name of the owner of the active stored procedure. Type: Result
Procedure Physical Reads Version: 11.0 and later	Number of requests to execute a stored procedure, for which a read from sysprocedures was necessary. Type: Result
Procedure Statement Number Version: 11.0 and later	Statement number within a stored procedure. A single stored procedure line can contain one or more statements. Type: Key
Process ID Version: 11.0 and later	Adaptive Server process identification number. Views that contain Process ID only report on processes that are active as of the end of the sample period. Process ID is mutually exclusive with Application Name in a view. Type: Key
Process State Version: 11.0 and later	Process state: 0 – None. 1 – alarm sleep.Waiting on an alarm. 2 – background. Adaptive Server process executing. 3 – bad status. Undetermined error condition. 4 – infected. Tagged by Adaptive Server as unprocessable. 5 – lock sleep. Waiting on a lock acquisition. 6 – received sleep. Waiting on a network read. 7 – runnable. Waiting to run according to priority and availability of CPU. 8 – running. Executing. 9 – send sleep. Waiting on a network send. 10 – sleeping. Paused for any other reason not listed here, such as: waiting on device I/O (physical reads) or waiting for client activity. 11 – stopped. Process terminated. 12 – terminating. Process terminating. 13 – unknown. Process state undeterminable. 14 – remote I/O. Waiting on a remote (OMNI) server to complete an operation. 15 – synch sleep. Waiting to synchronize with some other server process(es) that are working in parallel to execute a given query. Type: Key
Process State Count Version: 11.0 and later	Number of processes in a particular state. Type: Result
Rows Deleted Version: 11.0 and later	Number of rows deleted from a database table. Type: Result
Rows Deleted Deferred Version: 11.0 and later	Number of rows deleted from a database table in deferred mode. Type: Result
Rows Deleted Direct Version: 11.0 and later	Number of rows deleted from a database table in direct mode. Type: Result
Rows Inserted Version: 11.0 and later	Insertions into a database table. Type: Result
Rows Inserted Clustered Version: 11.0 and later	Insertions into database tables with clustered indexes. Type: Result
Rows Inserted Heap Version: 11.0 and later	Insertions into database tables without clustered indexes. Type: Result
Rows Updated Version: 11.0 and later	Updates to a database table. Type: Result
Rows Updated Deferred Version: 11.0 and later	Updates that require two steps to complete. First, records for deleting the existing entry and inserting the new entry are written to the log, but only the deletes are actually performed on the data pages. In the second step, the log is rescanned and the insert operations performed on the data pages. Type: Result
Rows Updated Direct Version: 11.0 and later	The sum of expensive, in-place, and not-in-place updates (everything except updates deferred). Type: Result
Rows Updated Expensive Version: 11.0 and later	A type of direct update that deletes a row from its original location and inserts it in a new location.
Rows Updated In Place Version: 11.0 and later	A type of direct update that does not require rows to move on a data page. Type: Result
Rows Updated Not In Place Version: 11.0 and later	A type of direct update that does not require the updated row to move, but because the length of the updated row changes, other rows on the data page are moved. Also known as cheap updates. Type: Result
Select Statements Version: 11.0 and later	Number of SELECT or OPEN CURSOR statements. Type: Result
Server Structures Size Version: 11.0 and later	Amount of memory in bytes allocated for the Adaptive Server structures. Type: Result
SQL Server Name Version: 11.0 and later	Name of the Adaptive Server that is being monitored as specified by the -S parameter in the start-up command for the Monitor Server being used. Type: Result
SQL Server Version Version: 11.0 and later	Version of the Adaptive Server that is being monitored. For more information, see the global @@version variable in the Transact-SQL User’s Guide. Type: Result
Thread Exceeded Max Version: 11.5 and later	The number of times a query plan was adjusted at runtime because of attempting to exceed the configured limit of threads in the server-wide worker thread pool in Adaptive Server version 11.5 and later. Type: Result
Thread Exceeded Max Percent Version: 11.5 and later	The percentage of time a query plan was adjusted at runtime because of attempting to exceed the configured limit of threads in the server-wide worker thread pool in Adaptive Server version 11.5 and later. Type: Result
Thread Max Used Version: 11.5 and later	The maximum number of threads from the server-wide worker thread pool that were concurrently in use on the server. Type: Result
Time Waited on Lock Version: 11.0 and later	Amount of time (in seconds) that a lock request has been waiting. Type: Result
Timestamp Version: 11.0 and later	In recording session views and in playback views when summarization_level is raw, the date and time on the Adaptive Server when the recording session data was gathered. In playback views, when summarization_level is actual, entire, or a user-defined interval, the time is converted to the time zone of Historical Server. For more information, see the getdate() function in the Transact-SQL User’s Guide. Type: Result
Timestamp Datim Version: 11.0 and later	In recording session views and in playback views when summarization_level is raw, the date and time on the Adaptive Server when the recording session data was gathered, returned in a CS_DATETIME structure. For more information, see the getdate() function in the Transact-SQL User’s Guide. In playback views, when summarization_level is actual, entire, or a user-defined interval, the time is converted to the time zone on Historical Server. Type: Result
Transactions Version: 11.0 and later	Total number of committed Transact-SQL statement blocks delimited by a begin transaction and commit transaction statement. Type: Result

Sybase Auditing

1. Create the auditing devices and auditing database with the Transact-SQL disk init and create database commands. For example:

USE master go DISK INIT NAME=’sybsecurity_data1′, PHYSNAME=’/sybdevices/cids/sybsecurity_data1.dat’, VDEVNO=60, SIZE=5120, VSTART=0, CNTRLTYPE=0, DSYNC=true go EXEC sp_diskdefault ‘sybsecurity_data1′,defaultoff go USE master go DISK INIT NAME=’sybsecurity_data2′, PHYSNAME=’/sybdevices/cids/sybsecurity_data2.dat’, VDEVNO=61, SIZE=5120, VSTART=0, CNTRLTYPE=0, DSYNC=true go EXEC sp_diskdefault ‘sybsecurity_data2′,defaultoff go USE master go DISK INIT NAME=’sybsecurity_data3′, PHYSNAME=’/sybdevices/cids/sybsecurity_data3.dat’, VDEVNO=62, SIZE=5120, VSTART=0, CNTRLTYPE=0, DSYNC=true go EXEC sp_diskdefault ‘sybsecurity_data3′,defaultoff go USE master go DISK INIT NAME=’sybsecurity_log1′, PHYSNAME=’/sybdevices/cids/sybsecurity_log1.dat’, VDEVNO=63, SIZE=2560, VSTART=0, CNTRLTYPE=0, DSYNC=true go EXEC sp_diskdefault ‘sybsecurity_log1’,defaultoff USE master go CREATE DATABASE sybsecurity ON sybsecurity_data1=10 LOG ON sybsecurity_log1=5 go USE master go EXEC sp_dboption ‘sybsecurity’,’trunc log on chkpt’,true go EXEC sp_dboption ‘sybsecurity’,’abort tran on log full’,true go use sybsecurity go EXEC sp_changedbowner ‘sa’ Go checkpoint go –          Use isql to execute the installsecurity script: o   cd $SYBASE/ASE-12_5/scripts setenv DSQUERY server_name isql -Usa –Ppassword –Sserver_name < installsecurity –          Shut down and restart Adaptive Server to initialise the auditing subsystem –          When you have completed these steps, the sybsecurity database has one audit table (sysaudits_01) created on its own segment. You can enable auditing at this time, but should add more auditing tables with sp_addaudittable. –          Now Create the extra sysaudits tables in sybsecurity: Extend the sybsecurity database to the device you initialized in step 1. use master go alter database sybsecurity on sybsecurity_data2= 10 go use sybsecurity go sp_addaudittable ‘sybsecurity_data2’ go use master go alter database sybsecurity on sybsecurity_data3= 10 go use sybsecurity go sp_addaudittable ‘sybsecurity_data3’ go –          Create the repository database sybaudit and the repository table audit_data USE master go CREATE DATABASE sybaudit ON DATA1=500 LOG ON LOG1=50 go USE master go EXEC sp_dboption ‘sybaudit’,’select into/bulkcopy/pllsort’,true go EXEC sp_dboption ‘sybaudit’,’trunc log on chkpt’,true go USE sybaudit go CHECKPOINT go USE sybaudit go EXEC sp_changedbowner ‘sa’ Go –          Create the audit_data table in sybaudit use sybaudit go CREATE TABLE dbo.audit_data ( event     smallint     NOT NULL, eventmod  smallint     NOT NULL, spid      smallint     NOT NULL, eventtime datetime     NOT NULL, sequence  smallint     NOT NULL, suid      int     NOT NULL, dbid      smallint     NULL, objid     int          NULL, xactid    binary(6)    NULL, loginname varchar(30)  NULL, dbname    varchar(30)  NULL, objname   varchar(255)  NULL, objowner  varchar(30)  NULL, extrainfo varchar(255) NULL, nodeid    tinyint      NULL ) LOCK ALLPAGES Go –          Add a sp_thresholdaction procedure to each of the segments created above, use the example below; Use sybsecurity go create procedure sp_audit_thresholdaction as declare @audit_table_number int select @audit_table_number = value from master.dbo.sysconfigures where name = “current audit table” exec sp_configure “current audit table”, 0, “with truncate” if @audit_table_number = 1 insert sybaudit.dbo.audit_data select * from sysaudits_01 truncate table sysaudits_01 if @audit_table_number = 2 insert sybaudit.dbo.audit_data select * from sysaudits_02 truncate table sysaudits_02 if @audit_table_number = 3 insert sybaudit.dbo.audit_data select * from sysaudits_03 truncate table sysaudits_03 return(0) –          To apply this action to the three segments issue the following command; Use sybsecurity go EXEC sp_addthreshold ‘sybsecurity’,’aud_seg_01′,200,’sp_audit_thresholdaction’ go EXEC sp_addthreshold ‘sybsecurity’,’aud_seg_02′,200,’sp_audit_thresholdaction’ go EXEC sp_addthreshold ‘sybsecurity’,’aud_seg_03′,200,’sp_audit_thresholdaction’ go –          Next create a procedure to flush all the data from sybsecurity into the table on sybaudit; Use sybaudit Go IF OBJECT_ID(‘dbo.sp_aud_flush_display’) IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_aud_flush_display IF OBJECT_ID(‘dbo.sp_aud_flush_display’) IS NOT NULL PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.sp_aud_flush_display >>>’ ELSE PRINT ‘<<< DROPPED PROCEDURE dbo.sp_aud_flush_display >>>’ END go CREATE PROCEDURE dbo.sp_aud_flush_display AS BEGIN exec sybsecurity..sp_audit_thresholdaction exec sybsecurity..sp_audit_thresholdaction exec sybsecurity..sp_audit_thresholdaction select distinct loginname from audit_data END go EXEC sp_procxmode ‘dbo.sp_aud_flush_display’,’unchained’ go –          Set the following parameters in the Sybase server; sp_configure “auditing”, 1 go sp_configure “suspend audit when device full”, 0 go –this enables auditing sp_configure “audit queue size”, 100 go –this sets up the queue size for handling auditing events –Now To audit the sa_role logins issue the following command; sp_audit “all”, “sa_role”, “all”, “on” go

This process sets up a proxy table so that you can select and insert into a remote table on another server from your local server. It’s more elegant than doing regular bcp’s The following example defines the remote Adaptive Server table authors, starting with the server definition: 1.         Define a server named SYBASE. Its server class is ASEnterprise, and its name in the interfaces file is SYBASE: exec sp_addserver SYBASE, ASEnterprise, SYBASE 2.         Define a remote login alias. This step is optional. User “sa” is known to remote server SYBASE as user “sa,” password “timothy”: exec sp_addexternlogin SYBASE, sa, sa, timothy 3.         Define the remote authors table: create existing table authors ( au_id      id              not null, au_lname   varchar(40)     not null, au_fname   varchar(20)     not null, phone      char(12)        not null, address    varchar(40)     null, city       varchar(20)     null, state      char(2)         null, country    varchar(12)     null, postalcode char(10)        null ) EXTERNAL TABLE at “SYBASE.pubs2.dbo.authors” 4.         Update statistics in tables to ensure reasonable choices by the query optimizer: update statistics authors 5.         Execute a query to test the configuration: select * from authors where au_lname = ‘Carson’

You can fix the rows containing the large numbers to correct gaps in the identity column. Perform the following steps:

set identity_insert table_name on

bcp out the data from the table.

set identity_insert table_name off

Drop and recreate the table with possibly a smaller numeric field for the identity column e.g numeric (5,0) instead of numeric (18,0)

bcp the table back in

From scratch

1.               run sp_plan_dbccdb from the master database to get an estimate of the sizes needed 2.               create a dedicated data and log device for the dbccdb database and create the database onto these, or just create it on existing devices 3.               run isql –U –P –S –i$SYBASE/$SYBASE_ASE/scripts/installdbccdb to install all the necessary tables and stored procs in the the dbccdb database 4.               Add segments into the dbccdb database; use dbccdb go sp_addsegment scanseg, dbccdb, dbccdb_data1 go sp_addsegment textseg, dbccdb, dbccdb_data1 Where dbccdb_data1 is the data device dbccdb was created on 5.         Add a 16K pool if it doesn’t already exist;

               sp_poolconfig "default data cache", "150M", "16K"

go 5.               Create workspaces;

     use dbccdb

go sp_dbcc_createws dbccdb, scanseg, scan_pubs2, “scan”, “10M” go sp_dbcc_createws dbccdb, textseg, text_pubs2, “text”, “10M” go The values in MB are found from the highest values of the output from sp_plan_dbccdb earlier 6.   Configure databases into dbccdb; derive the required values from the sp_plan_dbccdb earlier

use dbccdb

go sp_dbcc_updateconfig pubs2,”max worker processes”, “4” go sp_dbcc_updateconfig pubs2, “dbcc named cache”, “default data cache”, “10K” go sp_dbcc_updateconfig pubs2, “scan workspace”, scan_pubs2 go sp_dbcc_updateconfig pubs2, “text workspace”, text_pubs2 go sp_dbcc_updateconfig pubs2, “OAM count threshold”, “5” go sp_dbcc_updateconfig pubs2, “IO error abort”, “3” go sp_dbcc_updateconfig pubs2,”linkage error abort”, “8” go You will need to increase the number of worker processes parameter to 100 and bounce the Sybase server, after it comes up set max parallel degree to 10 and max scan parallel degree to 3

In case all the device files vanish and you have to start from scratch these are the steps to take; 1)         Run buildmaster –d <path to master device> -s <size in pages> (or dataserver in ASE12.5) to create a new master device 2)         Start the dataserver in single user mode 3)         log in using sa with null password and do the following a)         update sysservers set srvnetname=”<Old backup server name>” b)         alter database master on master =46MB (or whatever the master db size was before) c)         shutdown and restart the server  and backupserver d)         load the master database from a recent dump 4)          The server will shut down after the load, start it up again in single user mode, this time though log in with your ususal sa account and execute the following; a)         In the sysusages table remove all but the first 2MB fragment for tempdb (dbid =2) and also change the segmap for this first fragment to 7 I)          update sysusages set segmap=7 where lstart=0 and dbid=2 go II)         delete from sysusages wheredbid=2 and lstart >1000 go b)         update sysdatabases set status=320 where name not in (“master”,”model”,”tempdb”,”sybsystemdb”) c)         execute dbcc dbrepair (sybsystemprocs, dropdb) d)         delete from sysdevices where name=”sysprocsdev” e)         shut down the dataserver 5)        Edit the runserver file to start the dataserver up in normal mode and start it up again 6)        Log in normally and create a sysprocsdev device, then create the sybsystemprocs database on it (200MB) and load this database from a recent dump. 7)        Now you just need to extend tempdb and create the other databases.

Building new installation This is quite straight forward, simply put the CD into the drive and run the installation script, select to create the demo database, it’s always quite usefull.

Starting the server

The following command can be used; start_asiq @/tore/mjolner_iq/ASIQ-12_6/demo/asiqdemo.cfg the contents of the .cfg file could be something like; >>>>>>>>>>>>>>>>>>>>>>>> # asiqdemo.cfg # ———————————————————— # Default startup parameters for the ASIQ demo database # ———————————————————— -n  mjolner_asiqdemo /tore/mjolner_iq/ASIQ-12_6/demo/asiqdemo.db -x  tcpip{port=2638} # The following parameters are also found in the configuration file # $ASDIR/scripts/default.cfg.  Any parameters not specified below # and not in the start up parameter list, will be added by start_asiq # using default.cfg as a guide. -c  48m -gc 20 -gd all -gl all -gm 10 -gp 4096 -ti 4400 >>>>>>>>>>>>>>>>>>>>>>> note that the –n parameter gives the name of the iq server followed by the path to the database file.

Connecting to database/server

from unix type ; dbisql -nogui -host mjolner -port 2639 -jConnect -c “uid=DBA;pwd=SQL” or if you want the gui version omit the –nogui option, you need to set the display first though. From Windows use Interactive SQL or Sybase central Useful commands sp_iqstatus Display the current status of the IQ server/database incl used space etc. sp_iqdbsize Displays the size of the current database select * from sysiqfile Display information on the dbspaces

Creating a new database

The easiest place to do this from is using Interactive SQL logged into asiqdemo and then type the following; create database ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.db’ IQ PATH ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.iq’ IQ SIZE 12000 message path ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.iqmsg’ temporary path ‘/tore/mjolner_iq/ASIQ-12_6/iq1/iq1.iqtmp’ temporary size 6000 page size 4096;  

Importing data

To import a table from ASE into IQ use the following sequence; 1)         Extract the ddl for the table you want to transfer and create the table in the IQ database 2)         bcp out the data with a command like; bcp murex2000db.MUREXDB.MPY_GLOB_DBF out MPY_GLOB_DBF.out -c -Usybufji –P<password> -SCIDS_DEV_SQL1 -t \| 3)         From a dbisql or interactive SQL session execute a command like; load TABLE MPY_GLOB_DBF ( TIMESTAMP    ‘|’, M_IDENTITY   ‘|’, M_VTYP       ‘|’, M_VINT       ‘|’, M_MLAB0      ‘|’, M_SLAB0      ‘|’, M_MLAB1      ‘|’, M_SLAB1      ‘|’, M_VDAT       ‘|’, M_VREF       ‘|’, M_VVAL       ‘|’, M__INDEX_    ‘|’, M__REPLICAT_ ‘\n’ ) FROM ‘/tore/MPY_GLOB_DBF.out’ ESCAPES OFF QUOTES OFF; Note that there may be some problems if the original table has timestamp or identity columns, but these can be worked around.

!! Ignore any errors occurring on object syslogs they are more than likely transient !! The following error codes need the following actions; 100031 run dbcc tablealloc (table_name) 100035 run dbcc checktable (table_name, fix_spacebits)

Stored proc to hammer tempdb, useful for tests. IF OBJECT_ID(‘dbo.heater’) IS NOT NULL BEGIN DROP PROCEDURE dbo.heater IF OBJECT_ID(‘dbo.heater’) IS NOT NULL PRINT ‘<<< FAILED DROPPING PROCEDURE dbo.heater >>>’ ELSE PRINT ‘<<< DROPPED PROCEDURE dbo.heater >>>’ END go create procedure heater @limit int as begin set nocount on declare @error                    int, @procname                 varchar(35), @tc                       int, @rc                       int, @rs                       int, /* Timing the process */ @elapsed_time1            datetime, @elapsed_time2            datetime, @snapdate                 datetime, @upd_stats_diff           int, @seconds                  numeric (10,4), /* Counting rows. Declaring variables */ @total_deleted            int, @total_inserted           int, @total_updated            int, @inf_initial              int, @inf_final                int select @elapsed_time1=getdate() print “” print “Starting time: %1!. “, @elapsed_time1 print “” create table #heater (col1 int, col2 char(255), col3 char(255), col4 char(255)) declare @counter int select @counter =1 while (@counter < @limit ) begin insert into  #heater values (@counter, “AAAAAAAAAAAAAAAAA”, “BBBBBBBBBBBBBBBBBBBBBBB”, “CCCCCCCCCCCCCCCCCCCCCCCCCC”) select @counter = @counter + 1 if (@counter = @limit/2) begin select getdate() exec sp_helpsegment “logsegment” exec sp_helpsegment “default” exec sp_helpsegment “system” end end /* Timing the process */ select @elapsed_time2=getdate() select @seconds=datediff(ss, @elapsed_time1, @elapsed_time2) print “” print “Ending time: %1!. Elapsed time: %2! seconds”, @elapsed_time2, @seconds end go IF OBJECT_ID(‘dbo.heater’) IS NOT NULL PRINT ‘<<< CREATED PROCEDURE dbo.heater >>>’ ELSE PRINT ‘<<< FAILED CREATING PROCEDURE dbo.heater >>>’ go EXEC sp_procxmode ‘dbo.heater’,’unchained’ go