Stored procedures

Apart from emulating SQL Server’s stored procedures, Querona introduces some of its own. All of them start with qua_ prefix to be easily distinguished from regular SQL Server stored procedures.

Administrative

Administrative stored procedures should be used by power users and administrators. Note that most of the tasks can also be achieved conveniently using Querona Administration Portal user interface.

Stored procedure name Description
qua_flush_plan_cache Flushes the whole execution plan cache.
qua_run_garbage_collection Attempts garbage collection to reclaim unused memory.
qua_set_default_federator Configures federator for an agent. Given connection will be the new default federator for the designated agent.

Cache management

Stored procedure name Description
qua_create_view_copy Creates a primary/secondary view copy for the materialized view.
qua_evict_spark_inmem_cached_table Removes data from Spark in-memory cache for given table.
qua_full_load_cached_view_copy Loads cache for a given view.
qua_full_load_view_copy Fills a primary/secondary view copy.
qua_incremental_insert Inserts new rows into an incrementally cached view.
qua_incremental_load_cached_view_copy Conducts the whole process of an incremental load of new rows.
qua_invalidate_spark_cached_table Invalidates and then loads Spark in-memory cache for given table.
qua_populate_cached_table Materializes a cached view.
qua_refresh_view Refreshes the materialized view by reloading the cache.
qua_set_reload_mode Sets reload mode for a given view.
qua_set_view_cache_mode Sets the cache mode for a given view.
qua_set_view_copy_status Allows setting the view cache internal status flag. Useful for manual cache management.
qua_set_incremental_marker Sets new incremental marker value for given table or view.
qua_set_incremental_marker_column Sets incremental marker column for given table or view. The column will be later used for incremental load process.
qua_switch_view_copy Switches view copy between the primary and secondary table for cached views with two rotated tables.
qua_trim_view_copy Drops persistent cache and recreates it.

CData

Stored procedure name Description
qua_get_oauth_access_token_d365 Gets the authentication token used to authenticate to the service.
qua_get_oauth_authorization_url_d365 Gets an authorization URL from the data source.

Connection management

Stored procedure name Description
sp_addlinkedserver Creates a connection to a data source. A connection allows for access to a single data source (endpoint).
sp_addlinkedsrvlogin Creates or updates a mapping between a connection on the local instance of Querona and a security account on a remote data source.
sp_dropserver Removes a connection from the list of known connections on the instance of Querona.
sp_testlinkedserver Tests the connection to a data source.
sp_updatelinkedserver Updates a connection to a data source.

Diagnostics

Stored procedure name Description
qua_set_logging_level Dynamically changes Querona logging level.
qua_help_jobs Fetches a data set with details of all jobs that the schedule is aware of.

Job management

Stored procedure name Description
sp_add_job Adds a job executed by the job scheduler.
sp_add_jobstep Adds a step (operation` to a job.
sp_add_schedule Creates a schedule that can be used by any number of jobs.
sp_attach_schedule Sets a schedule for a job.
sp_detach_schedule Removes an association between a schedule and a job.
sp_delete_job Deletes a job.
sp_help_job Returns information about jobs.
sp_help_jobstep Returns information for the steps in a job.
sp_start_job Instructs the scheduler to execute a job immediately.
sp_update_job Changes the attributes of a job.
sp_update_jobstep Changes the setting for a step in a job that is used to perform automated activities.
sp_update_schedule Changes the settings for a schedule.
sp_delete_jobstep Deletes a step from the job.
sp_delete_schedule Deletes a schedule.

Utility

Stored procedure name Description
qua_analyze_table Orders the underlying system to update its object-level, or column-level statistics, or both.
qua_execute_provider_sql_on_connection Executes provider-specific SQL query or command on a given connection.
qua_update_object_statistics Updates statistics (row count and distinct count` for a given table.
qua_update_row_count_object_statistics Updates row count statistics for a given table.

Metadata management

Stored procedure name Description
qua_get_partitioning_schema Retrieves partitioning schema for a given view.
qua_set_partitioning_schema Sets partitioning mode and schema for a given view.
sp_generate_views Generates views on target database based on objects (tables or views` from a source database).
sp_import_metadata Generates views on target database based on objects (tables or views` from a source database).

Spark management

Stored procedure name Description
qua_spark_force_become_active Transitions the node to Active, regardless of Zookeeper state.
qua_spark_force_become_standby Transitions the node to StandBy, regardless of Zookeeper state.
qua_spark_instance_kill Forces an OS-level kill on the given Spark process.
qua_spark_instance_start Tries to find a running Spark process in the OS. If not found, a new process is started.
qua_spark_instance_stop Sends a stop-server signal to the remote driver.
qua_spark_release Releases Spark context and transitions driver to non-initialized state.
qua_spark_stop_driver Sends a stop-server signal to the remote driver.

SQL Server Legacy

The following internal stored procedures are internally supported by Querona engine for compatibility with TDS protocol and technologies like OLE DB:

Procedure name Description
sp_prepare Prepares a parameterized Transact-SQL statement and returns a statement handle for execution.
sp_prepexec Prepares and executes a parameterized Transact-SQL statement.
sp_execute Executes a prepared Transact-SQL statement using a specified handle and optional parameter value.
sp_unprepare Discards the execution plan created by the sp_prepare stored procedure.
sp_executesql Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically.
sp_columns Returns column information for the specified objects that can be queried in the current environment.
sp_special_columns Returns the optimal set of columns that uniquely identify a row in the table.
sp_tables Returns a list of objects that can be queried in the current environment.
sp_statistics Returns a list of all indexes and statistics on a specified table or indexed view.
sp_fkeys Returns logical foreign key information for the current environment.
sp_pkeys Returns primary key information for a single table.
sp_describe_undeclared_parameters Returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.
sp_changeobjectowner Changes the owner of an object in the current database.
xp_msver Returns version information compatible with Microsoft SQL Server.

Honorable mentions

The following stored procedures are internally supported by Querona engine, but are not directly usable from queries:

  • sp_catalogs_rowset
  • sp_catalogs_rowset
  • sp_catalogs_rowset
  • sp_catalogs_rowset2
  • sp_columns_100
  • sp_columns_100_rowset
  • sp_columns_100_rowset2
  • sp_columns_rowset
  • sp_columns_rowset
  • sp_columns_rowset
  • sp_columns_rowset2
  • sp_columns_managed
  • sp_indexes_rowset
  • sp_indexes_rowset
  • sp_indexes_rowset
  • sp_indexes_rowset2
  • sp_indexcolumns_managed
  • sp_tableswc
  • sp_tables_info_rowset
  • sp_tables_info_rowset
  • sp_tables_info_rowset2
  • sp_statistics_100
  • sp_statistics_rowset
  • sp_statistics_rowset
  • sp_statistics_rowset2
  • sp_oledb_database
  • sp_oledb_ro_usrname
  • sp_oledb_defdb
  • sp_oledb_deflang
  • sp_oledb_language
  • sp_views_rowset
  • sp_views_rowset2
  • sp_schemata_rowset
  • sp_schemata_rowset
  • sp_tables_rowset
  • sp_tables_rowset
  • sp_tables_rowset
  • sp_tables_rowset2
  • sp_primary_keys_rowset
  • sp_primary_keys_rowset
  • sp_primary_keys_rowset
  • sp_primary_keys_rowset
  • sp_primary_keys_rowset2
  • sp_provider_types_100_rowset
  • sp_provider_types_90_rowset
  • sp_provider_types_rowset
  • sp_reset_connection
  • sp_foreign_keys_rowset
  • sp_foreign_keys_rowset
  • sp_foreign_keys_rowset
  • sp_foreign_keys_rowset
  • sp_foreign_keys_rowset2
  • sp_foreign_keys_rowset3
  • sp_procedures_rowset
  • sp_procedures_rowset
  • sp_procedures_rowset2
  • xp_qv
  • xp_instance_regread
  • xp_regread