Following are several new system procedures and functions, and new extensions to existing system procedures and functions.
Enhancements to all procedures and functions to support the DEFAULT clause For procedures and user-defined functions, the value DEFAULT may be provided as an argument if the corresponding parameter was defined with a default value. In cases where the procedure has several parameters and the ones being defaulted are not all at the end, it may be easier to specify DEFAULT in the argument list than to use named parameters. Also, named parameters are not permitted in function calls.
New system procedures The following system procedures have been added:
sa_clean_database system procedure Sets the duration of time for which the database cleaner runs. See sa_clean_database system procedure.
sa_column_stats system procedure The sa_column_stats system procedure returns string-related statistics about the specified column(s). See sa_column_stats system procedure.
sa_conn_list system procedure The sa_conn_list system procedure returns a connection ID. See sa_conn_list system procedure.
sa_conn_options system procedure The sa_conn_options system procedure returns property information for connection properties that correspond to database options. See sa_conn_options system procedure.
sa_db_list system procedure The sa_db_list system procedure returns a database ID. See sa_db_list system procedure.
sa_describe_query system procedure The sa_describe_query system procedure returns one row per column and describes the domain of the result expression and its nullability. This procedure is equivalent to performing the EXPRTYPE function on each column. See sa_describe_query system procedure.
sa_get_bits system procedure The sa_get_bits system procedure decodes a bit string, returning one row for each bit in the bit string, indicating the value of the bit. See sa_get_bits system procedure.
sa_make_object system procedure You can now specify an events as an object type for the sa_make_object system procedure. See sa_make_object system procedure.
sa_materialized_view_info system procedure The sa_materialized_view_info system procedure returns information about a specified materialized view, such as its status and the owner of the view. See sa_materialized_view_info system procedure.
sa_refresh_materialized_views system procedure The sa_refresh_materialized_views system procedure refreshes all materialized views in the database that are currently in an uninitialized state. See sa_refresh_materialized_views system procedure.
sa_remove_tracing_data system procedure This procedure permanently deletes all record of a given logging session from the diagnostic tracing tables. See sa_remove_tracing_data system procedure.
sa_save_trace_data system procedure This procedure saves data from temporary tracing tables to the base tables. See sa_save_trace_data system procedure.
sa_set_tracing_level system procedure Sets the level of tracing data to generate for the database being profiled. See sa_set_tracing_level system procedure.
sa_snapshots system procedure Returns a list of snapshots that are currently active for the database. See sa_snapshots system procedure.
sa_split_list system procedure Takes a string representing a list of values and returns a result set containing that list. See sa_split_list system procedure.
sa_table_stats system procedure Returns information about how many pages have been read from each table. See sa_table_stats system procedure.
sa_transactions Returns a list of transactions that are currently running against a database. See sa_transactions system procedure.
sa_unload_cost_model and sa_load_cost_model system procedures You can now unload the cost model from one database and load it into another database using the new system procedures sa_unload_cost_model and sa_load_cost_model, respectively. This eliminates repetitive, time-consuming recalibration activities when there is a large number of similar hardware installations. See sa_unload_cost_model system procedure and sa_load_cost_model system procedure.
New functions The following functions have been added:
BIT_LENGTH function Returns the number of bits stored in the array. See BIT_LENGTH function [Bit array].
BIT_SUBSTR function Returns a sub-array of a bit array. See BIT_SUBSTR function [Bit array].
BIT_AND function Takes two bit arrays and returns a bitwise AND-ing of its arguments using the following logic: for each bit compared, if both bits are 1, return 1; otherwise, return 0. See BIT_AND function [Aggregate].
BIT_OR function Takes two bit arrays and returns a bitwise OR-ing of its arguments using the following logic: for each bit compared, if either bit (or both) is 1, return 1; otherwise, return 0. See BIT_OR function [Aggregate]
BIT_XOR function Takes two bit arrays and returns a bitwise exclusive OR-ing of its arguments using the following logic: for each bit compared, if just one bit (but not both) is 1, return 1; otherwise, return 0. See BIT_XOR function [Aggregate].
COUNT_SET_BITS function Returns a count of the number of bits set to 1 (TRUE) in the array. See COUNT_SET_BITS function [Bit array].
GET_BIT function Returns the value (1 or 0) of a specified bit in a bit array. See GET_BIT function [Bit array].
REVERSE function This new function returns the reverse of a character expression. See REVERSE function [String].
SET_BIT function Sets the value of a specific bit in a bit array. See SET_BIT function [Bit array].
SET_BITS function Creates a bit array where specific bits, corresponding to values from a set of rows, are set to 1 (TRUE). See SET_BITS function [Aggregate].
TRACED_PLAN function Generates a graphical plan for a query using tracing data and information about optimizer conditions when the query was traced. See TRACED_PLAN function [Miscellaneous].
Enhancements to various system procedures and functions The following system procedures and functions have been enhanced as described:
Enhancements to property functions Property functions can now return LONG VARCHAR.See:
Enhancements to DB_EXTENDED_PROPERTY function You can now use the DB_EXTENDED_PROPERTY function with the NextScheduleTime database property to obtain the next scheduled execution time for an event. You can also use the function the return extended information about the CHAR character set. See DB_EXTENDED_PROPERTY function [System].
New CONNECTION_EXTENDED_PROPERTY function You can use the CONNECTION_EXTENDED_PROPERTY function to find out extended information for certain connection parameters. See CONNECTION_EXTENDED_PROPERTY function [String].
sa_procedure_profile system procedure The output from sa_procedure_profile system procedure can now be saved to a file, has new syntax, requires fewer parameters, and has new uses. See sa_procedure_profile system procedure.
sa_procedure_profile_summary system procedure The sa_procedure_profile_summary system procedure now supports saving its output to a file, has new syntax, accepts fewer parameters, and has new uses. See sa_procedure_profile_summary system procedure.
sa_server_option system procedure The sa_server_option system procedure lets you change settings for the database server while it is still running. You can now change the following settings:
CacheSizingStatistics property Display cache information in the Server Messages window whenever the cache size changes.
CollectStatistics property Collect Performance Monitor statistics for the database server.
ConsoleLogFile property Specify the name of the output file where Server Messages window information is recorded.
ConsoleLogMaxSize property Specify the maximum size of the output file used to record Server Messages window information.
DebuggingInformation property Display diagnostic communication messages and other messages for troubleshooting purposes.
IdleTimeout server option Disconnect TCP/IP or SPX connections that have not submitted a request for the specified number of minutes.
ProfileFilterConn property Capture profiling information for a specific connection ID, without preventing other connections from using the database.
RequestFilterDB property You can use the sa_server_option system procedure to filter connections to a single database for request logging.
RequestLogging property The request log can now record blocking and unblocking events, plan information, procedures, and triggers.
RequestTiming property Turning on request timing instructs the database server to maintain timing information for each request.
Enhancement to xp_startsmtp system procedure The xp_startsmtp system procedure supports three new parameters: smtp_user_name, smtp_auth_username, and smtp_auth_password. See xp_startsmtp system procedure.
Enhancement to xp_sendmail system procedure The xp_sendmail system procedure now supports attachments when sending mail using SMTP, using the new include_file parameter. In addition, xp_sendmail supports MIME content when using SMTP mail, using the new content_type parameter. See xp_sendmail system procedure.
sa_conn_info system procedure now returns several new property values The sa_conn_info system procedure now returns the following additional properties: ClientPort, ServerPort, and LockTable. The procedure no longer returns the LastIdle property, and the UncmtOps value has been renamed to UncommitOps. See sa_conn_info system procedure.
sa_performance_diagnostics returns more information The sa_performance_diagnostics system procedure now returns the LockCount and SnapshotCount when you use snapshot isolation. See sa_performance_diagnostics system procedure.
Enhancement to the HASH function The HASH function now accepts the following new algorithms: SHA256, SHA1_FIPS, and SHA256_FIPS. The FIPS related algorithms are only for use on systems that use FIPS-certified software. See HASH function [String].
COMPRESS and DECOMPRESS functions support new algorithm The gzip algorithm is now available to compress and decompress a string in a function. See COMPRESS function [String], and DECOMPRESS function [String].