Built-in System Procedures and Functions

This section contains the reference documentation for the Splice Machine Built-in SQL System Procedures and Functions, in the following subsections:

Database Admin Procedures and Functions

These are the system procedures and functions for administering your database:

Procedure / Function Name Description
SYSCS_UTIL.SYSCS_BACKUP_DATABASE

Backs up the database to a specified backup directory.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.SYSCS_CANCEL_BACKUP

Cancels a backup.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.SYSCS_CANCEL_DAILY_BACKUP

Cancels a scheduled daily backup.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.CHECK_TABLE Reports on inconsistencies between a table and its indexes.
SYSCS_UTIL.COMPACT_REGION Performs a minor compaction on a table or index region.
SYSCS_UTIL.SYSCS_CREATE_USER Adds a new user account to a database.
SYSCS_UTIL.SYSCS_DELETE_BACKUP

Delete a specific backup.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.SYSCS_DELETE_OLD_BACKUPS

Deletes all backups that were created more than a certain number of days ago.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.SYSCS_DROP_USER Removes a user account from a database.
SYSCS_UTIL.GET_ENCODED_REGION_NAME Returns the encoded name of the HBase region that contains the specified, unencoded Splice Machine table primary key or index values.
SYSCS_UTIL.GET_REGIONS Retrieves the list of regions containing a range of key values.
SYSCS_UTIL.GET_RUNNING_OPERATIONS Displays information about each Splice Machine operations running on a server.
SYSCS_UTIL.GET_START_KEY Retrieves the unencoded start key for a specified HBase table or index region.
SYSCS_UTIL.INVALIDATE_DICTIONARY_CACHE Invalidates the dictionary cache on the connection's region server.
SYSCS_UTIL.INVALIDATE_GLOBAL_DICTIONARY_CACHE Invalidates the dictionary cache on all region servers.
SYSCS_UTIL.KILL_OPERATION Terminates a Splice Machine operation running on the server to which you are connected.
SYSCS_UTIL.MAJOR_COMPACT_REGION Performs a major compaction on a table or index region.
SYSCS_UTIL.MERGE_REGIONS Merges two adjacent table or index regions.
SYSCS_UTIL.SYSCS_MODIFY_PASSWORD Called by a user to change that user's own password.
SYSCS_UTIL.SYSCS_PERFORM_MAJOR_COMPACTION_ON_SCHEMA Performs a major compaction on a schema
SYSCS_UTIL.SYSCS_PERFORM_MAJOR_COMPACTION_ON_TABLE Performs a major compaction on a table.
SYSCS_UTIL.SYSCS_REFRESH_EXTERNAL_TABLE Refreshes the schema of an external table in Splice Machine; use this when the schema of the table's source file has been modified outside of Splice Machine.
SYSCS_UTIL.SYSCS_RESET_PASSWORD Resets a password that has expired or has been forgotten.
SYSCS_UTIL.SYSCS_RESTORE_DATABASE

Restores a database from a previous backup.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.SYSCS_SCHEDULE_DAILY_BACKUP

Schedules a full or incremental database backup to run at a specified time daily.

This procedure is only available in our On-Premise Database product.

SYSCS_UTIL.SYSCS_UPDATE_SCHEMA_OWNER Changes the owner of a schema.
SYSCS_UTIL.VACUUM Performs clean-up operations on the system.

Database Properties Procedures and Functions

These are the system procedures and functions for working with your database properties:

Procedure / Function Name Description
SYSCS_UTIL.SYSCS_GET_ALL_PROPERTIES Displays all of the Splice Machine Derby properties.
SYSCS_UTIL.SYSCS_GET_GLOBAL_DATABASE_PROPERTY function Fetches the value of the specified property of the database.
SYSCS_UTIL.SYSCS_GET_SCHEMA_INFO Displays table information for all user schemas, including the HBase regions occupied and their store file size.
SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE function

Allows users to observe the instantaneous current value of a sequence generator without having to query the SYSSEQUENCES system table.

SYSCS_UTIL.SYSCS_SET_GLOBAL_DATABASE_PROPERTY Sets or deletes the value of a property of the database.

Importing Data Procedures and Functions

These are the system procedures and functions for importing data into your database:

Procedure / Function Name Description
SYSCS_UTIL.BULK_IMPORT_HFILE Imports data from an HFile.
SYSCS_UTIL.DELETE_SNAPSHOT Deletes a stored snapshot.
SYSCS_UTIL.IMPORT_DATA Imports data to a subset of columns in a table.
SYSCS_UTIL.SYSCS_MERGE_DATA_FROM_FILE Imports data from external files, inserting new records and updating existing records.
SYSCS_UTIL.SET_PURGE_DELETED_ROWS Enables (or disables) physical deletion of logically deleted rows from a specific table.
SYSCS_UTIL.RESTORE_SNAPSHOT Restores a table or schema from a stored snapshot.
SYSCS_UTIL.SNAPSHOT_SCHEMA Creates a Splice Machine snapshot of a schema.
SYSCS_UTIL.SNAPSHOT_TABLE Creates a Splice Machine snapshot of a specific table.
SYSCS_UTIL.SPLIT_TABLE_OR_INDEX Computes split keys for a table or index and then sets up the table or index.
SYSCS_UTIL.SYSCS_UPSERT_DATA_FROM_FILE Imports data from external files, inserting new records and updating existing records.

Jar File Procedures and Functions

These are the system procedures and functions for working with JAR files:

Procedure / Function Name Description
SQLJ.INSTALL_JAR Stores a jar file in a database.
SQLJ.REMOVE_JAR Removes a jar file from a database.
SQLJ.REPLACE_JAR Replaces a jar file in a database.

Logging Procedures and Functions

These are the system procedures and functions for working with system logs:

Procedure / Function Name Description
SYSCS_UTIL.SYSCS_GET_LOGGER_LEVEL Displays the log level of the specified logger.
SYSCS_UTIL.SYSCS_GET_LOGGERS Displays the names of all Splice Machine loggers in the system.
SYSCS_UTIL.SYSCS_SET_LOGGER_LEVEL Changes the log level of the specified logger.

Statement and Stored Procedures System Procedures

These are the system procedures and functions for working with executing statements and stored procedures:

Procedure / Function Name Description
SYSCS_UTIL.SYSCS_EMPTY_GLOBAL_STATEMENT_CACHE Removes as many compiled statements (plans) as possible from the database-wide statement cache (across all region servers).
SYSCS_UTIL.SYSCS_EMPTY_STATEMENT_CACHE Removes as many compiled statements (plans) as possible from the database statement cache on your current region server.
SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS Invalidates all system prepared statements and forces the query optimizer to create new execution plans.
SYSCS_UTIL.SYSCS_UPDATE_METADATA_STORED_STATEMENTS Updates the execution plan for stored procedures in your database.
SYSCS_UTIL.SYSCS_UPDATE_ALL_SYSTEM_PROCEDURES Updates the signatures of all of the system procedures in a database.
SYSCS_UTIL.SYSCS_UPDATE_SYSTEM_PROCEDURE Updates the stored declaration of a specific system procedure in the data dictionary.

Statistics Procedures and Functions

These are the system procedures and functions for managing database statistics:

Procedure / Function Name Description
SYSCS_UTIL.COLLECT_SCHEMA_STATISTICS Collects statistics on a specific schema in your database.
SYSCS_UTIL.DISABLE_COLUMN_STATISTICS Disables collection of statistics on a specific column in a table.
SYSCS_UTIL.DROP_SCHEMA_STATISTICS Drops statistics for a specific schema in your database.
SYSCS_UTIL.ENABLE_COLUMN_STATISTICS Enables collection of statistics on a specific column in a table.

System Status Procedures and Functions

These are the system procedures and functions for monitoring and adjusting system status:

Procedure / Function Name Description
SYSCS_UTIL.SYSCS_GET_ACTIVE_SERVERS Displays the number of active servers in the Splice cluster.
SYSCS_UTIL.SYSCS_GET_REGION_SERVER_STATS_INFO Displays input and output statistics about the cluster.
SYSCS_UTIL.SYSCS_GET_REQUESTS Displays information about the number of RPC requests that are coming into Splice Machine.
SYSCS_UTIL.SYSCS_GET_RUNNING_OPERATIONS Displays information about all Splice Machine operations running on the server to which you are connected.
SYSCS_UTIL.SYSCS_GET_SESSION_INFO Displays session information, including the hostname and session IDs.
SYSCS_UTIL.SYSCS_GET_VERSION_INFO Displays the version of Splice Machine installed on each server in your cluster.
SYSCS_UTIL.SYSCS_GET_WRITE_INTAKE_INFO Displays information about the number of writes coming into Splice Machine.

Transaction Procedures and Functions

These are the system procedures and functions for working with transactions in your database

Procedure / Function Name Description
SYSCS_UTIL.SYSCS_GET_CURRENT_TRANSACTION Displays summary information about the current transaction.



For access to the source code for the Community Edition of Splice Machine, visit our open source GitHub repository.