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