System procedures and functions

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   Runs the database cleaner for the time specified. 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 database 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 database server messages window information is recorded.

      • ConsoleLogMaxSize property   Specify the maximum size of the output file used to record database 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.

      For more information, see sa_server_option system procedure.

    • 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].

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1