New and changed system procedures

sp_monitorconfig

Adaptive Server 12.5.0.3 adds the all parameter, which displays descriptor help information for all 34 indexes, objects, databases, and auxiliary scan descriptors in the server.

sp_dboption

• Adds the async log service option, which enables, disables, or configures the asynchronous log service feature.

• Can prevent user-created temporary databases from being set to single-user mode.

sp_helpdb

• Adds a column to display asynchronous log service information.

• Reports whether or not a database is a user-created temporary database in the status column.

sp_help

Adds a column that displays optimistic index lock information.

sp_chgattribute

Adds the optimistic_index_lock option, which acquires an optimistic index lock on a table.

sp_bindcache

Can precent bvinding of individual tables to a named cache in user-created temporary databases.

sp_dbcc_summary_report

Adds the display_recommendations parameter, which enables reporting the recommendations generated by sp_dbcc_recommandations.

sp_dbcc_faultreport

Displays faults in ordered list by table and index. Adds these new parameters:

• exclusion_mode – ignores or applies exclusion lists.

• exclusion_faults – list of fault types to be excluded from reporting.

• exclusion_tables – list of tables to be excluded from reporting.

• exclusion_combo – list of fault/table combinations to be excluded from reporting.

• display_recommendations – enables reporting the recommendations generated by sp_dbcc_recommendations.

sp_dbcc_checkverify

Adds these new parameters:

• ignore_exclusions – enables or disables the exclusion list.

• tblename – limits checkverify processing to a specific .object.

sp_dbcc_updateconfig

Provides default configuration values for dbcc configuration values. Accepts delete as a value for the str1 parameter. Disables enable automatic workspace expansion for databases that are enabled by default.

sp_dbcc_configreport

Adds the defaults parameter, which enables only the display of configured default values.

sp_helptext

Displays the source text of a compiled object.

sp_tempdb

Creates the default temporary database group and binds temporary databases to the default temporary database group.

sp_dbcc_recommendations

Analyzes faults reported by the checkstorage operation and summarizes them.

sp_dbcc_help_fault

Provides a description of the specified fault type and the recommended fix.

sp_dbcc_exclusions

Allows the user to create and manage persistent exclusion lists for use by checkverify and sp_dbcc_faultreport.

sybmigrate

In Adaptive Server 12.5.0.3, sybmigrate selects dependent objects for migration when you use the auto-select feature.

These changes have been made to the command line configuration:

• -rn diff – a difference report feature that compares source and target database objects.

• -rn password – a new report feature to create a file for the changed passwords.

@@datefirst

Set using set datefirst n where n is a value between 1 and 7. Returns the current value of @@datefirst, indicating the specified first day of each week (1 for Sunday, 2 for Monday, and so on), expressed as tinyint.

The default value in Adaptive Server is 1, Sunday (based on the us_language default).

@@lock_timeout

Set using set lock wait n. Returns the current lock_timeout setting, in milliseconds. @@lock_timeout returns the value of n. The default value is no timeout. If no set lock wait n is executed at the beginning of the session, @@lock_timeout returns -1.

@@max_precision

Returns the precision level used by decimal and numeric datatypes set by the server. This value is a fixed constant of 38.

@@tempdbid

Returns a valid temporary database ID of the session’s assigned temporary database.

year

Returns an integer that represents the year in the datepart of a specified date.

month

Returns an integer that represents the month in the datepart of a specified date.

day

Returns an integer that represents the day in the datepart of a specified date.

str_replace

Replaces any instances of the second string expression (string_expression2) that occur within the first string expression (string_expression1) with a third expression (string_expression3.

square

Returns the square of a specified value expressed as a float.

left

Returns a specified number of characters on the left end of a character string.

len

Returns the number of characters, not the number of bytes, of a specified string expression, excluding trailing blanks.

newid

Generates human-readable, globally unique IDs (GUIDs) in two different formats, based on arguments you provide. The length of the human-readable format of the GUID value is either 32 bytes (with no dashes) or 36 bytes (with dashes).

tempdb_id

Reports the temporary database to which a given session is assigned.

next_identity

Retrieves the next identity value that is available for the next insert.

identity_burn_max

Tracks the identity burn max value for a given table.

monTables

Provides a description of all monitoring tables. No configuration options are required.

monTableParameters

Provides a description of all the optional parameters for each monitoring table. No configuration options are required.

monTableColumns

Describes all the columns for each monitoring table. No configuration options are required.

monState

Provides information regarding the overall state of Adaptive Server.

monEngine

Provides statistics regarding Adaptive Server engines. Requires the enable monitoring configuration parameter to be enabled.

monDataCache

Returns statistics relating to Adaptive Server data caches. Requires the enable monitoring configuration parameter to be enabled.

monProcedureCache

Returns statistics relating to Adaptive Server procedure cache. Requires the enable monitoring configuration parameter to be enabled.

monOpenDatabases

Provides state and statistical information pertaining to davases that are currently in use.

monSysWorkerThread

Returns server-wide statistics related to worker threads. Requires the enable monitoring configuration parameter to be enabled.

monNetworkIO

Returns network I/O statistics. Requires the enable monitoring configuration parameter to be enabled.

monErrorLog

Returns the most recent error messages from the Adaptive Server error log.

monLocks

Returns a list of all locks that are being held, and those that have been requested, by any process, for every object.

monDeadLock

Provides information pertaining to the most recent deadlocks that have occurred in Adaptive Server.

monWaitClassInfo

Provides a textual description for all of the wait classes. All events have been grouped into wait classes that classify the type of event that a process is waiting for.

monWaitEventInfo

Provides a textual description for every possible situation where a process is forced to wait within Adaptive Server.

monCachedObject

Returns statistics for all objects and indexes with pages currently in a data cache.

monCachePool

Provides statistics for all pools allocated for all caches.

monOpenObjectActivity

Provides statistics for all open objects.

monIOQueue

Provides device I/O statistics broken down into data and log I/O for normal and temporary databases on each device.

monDeviceIO

Returns statistical information relating to devices.

monSysWaits

Provides a server-wide view of where processes are waiting for an event.

monProcess

Provides detailed statistics about processes that are currently executing or waiting.

monProcessLookup

Provides information enabling processes to be tracked to an application, user, client machine, and so on.

monProcessActivity

Provides detailed statistics about process activity.

monProcessNetIO

Provides the network I/O activity for each process.

monProcessObject

Provides statistical information regarding objects that have been accessed by processes.

monProcessWaits

Provides a server-wide view of where processes are waiting for an event.

monProcessStatement

Provides information for currently executing statements.

monProcessSQLText

Provides the SQL text that is currently being executed.

monSysPlanText

Provides the most recent generated text plan.

monSysStatment

Provides statistics pertaining to the most recently executed statements.

monCachedProcedures

Provides statistics for all procedures currently stored in the procedure cache.

monSysSQLText

Provides the most recent SQL text that has been executed, or is currently being executed.

monProcessProcedures

Returns a list of procedures that are being executed by processes.

sysattributes

Displays information about the representation of temporary database groups and bindings.

sysdatabases

Adds a new bit which indicates the temporary status of a database by the value of 0x00000100 (256 decimal) in the status3 field.