This document articulates the Cirro SQL language. Although Cirro is generally SQL-92 compliant, this document is the authoritative source for the Cirro SQL language and takes precedence over other, non-Cirro SQL language documentation.

SQL 92 Compliance

The Cirro SQL language is a derivation of the SQL92 standard.

Commands, syntaxes, and options not discussed in this guide are not supported.

Cirro Query Execution and Federated Systems

When executing queries on federated systems, the behaviors of the underlying database are dominant regarding how results are calculated.

Audience

Users of this information should:

  • Have a basic understanding of the SQL92 standard and relational databases.
  • Understand which of their data sources are available through Cirro.
  • Know how to use a SQL client to connect to Cirro. See the Cirro BI User Guide and the Cirro Analyst User Guide for information.

Index

Index of all SQL Guide pages

The following pages and posts are tagged with

TitleTypeExcerpt
ABS ABSVAL Numeric Function Page ABS and ABSVAL are both supported, perform the same function, and can be used interchangeably. They returns the absolute value of a number in the same data ...
CREATE ACCESS POLICY RULE Page Add an access policy rule to allow or restrict access to Cirro both before login and after.
DELETE ACCESS POLICY Page Delete access policies.
VIEW ACCESS POLICY logs Page View access policy logs.
UPDATE ACCESS POLICY RULE Page Update an access policy rule.
VIEW ACCESS POLICY rules Page View access policy rules.
ACOS Numeric Function Page Returns the arc cosine of a numeric value as a DOUBLE value. Returns NULL if numValue is NULL. Returns an error if numValue is less than 0 or greater than 1.
Activate Log Config File debugging Page Activate and deactive Cirro Log Config File debug levels to aid in error correction. Log Config files are standard log4j format.
ADD AUTHENTICATION Page ADD or ALTER AUTHENTICATION allows an admin to add external authentication providers to Cirro.
ADD AUTHORIZATION Page Associates an existing Cirro user (created with CREATE USER) with an existing account on the federated system.
ADD RESOURCE Page Link a driver file with a resource created with CREATE DRIVER.
Addition Page Adds two numeric values.
ALTER DRIVER Page Allows modification of an existing driver, which was created previously with CREATE DRIVER.
ALTER METADATA Page Overrides the internal rowcount for a table or view. This is a global setting, and will affect all users and all sessions for this system.
ALTER POLICY (Set User Security Policy) Page SQL commands to set security policies for standard passwords such as username length and password length
ALTER SYSTEM Page Allows modification of an existing system, which was created with CREATE SYSTEM.
ALTER USER Page Changes the password for an existing Cirro user.
ALTER VIEW Page Alters the SELECT query of an existing, non-materialized view.
ACOS Numeric Function Page Returns the arc sine of a numeric value as A DOUBLE value. Returns NULL if numValue is NULL. Returns 0 if numValue is 0. Returns an error if numValue is less...
ACOS Numeric Function Page Returns the arc tangent of a numeric value as DOUBLE value. Returns NULL if numValue is NULL. Returns an error if numValue is less than 0 or greater than 1.
AVG Aggregate Function Page AVG returns the aggregate average of values.
Begin End block syntax Page Begin---End is used in Data Projects for data movement and parallelization.
BETWEEN Page Determines whether a value is within a specified range defined by two boundaries.
BIGINT Data Type Page BIGINT is an Integer numeric type data type
BIGINT data type conversion function Page Use of this function converts a value to a BIGINT.
CALL Page Executes a pre-defined procedure.
CANCEL PROJECT Page The CANCEL PROJECT statement will terminate a running project, i.e. find the session and cancel its statement. This requires the CANCEL SESSION privilege.
CASE Conditonal Function Page The CASE statement is used in place of an expression to return a value based on other values in the query.
CAST data type conversion function Page CAST converts a value from one data type to another.
CHAR Data Type Page CHAR is a fixed length string type data type.
CHAR data type conversion function Page CHAR converts a value to CHAR.
CHAR FOR BIT Data Type Page CHAR FOR BIT is a fixed length Binary Data Type.
CHAR_LENGTH/LENGTH String Function Page CHAR_LENGTH/LENGTH Returns the length of a string value, including spaces and special characters.
Check Service Status Page Check Cirro services with these commands.
Cirro Roles Page Cirro has several built-in roles you can assign to users.
Cirro Version Page After installation, you can determine the Cirro version installed on your system using these methods.
cirrosys.access_counter Page cirrosys.access_counter is a connection and sql access log which applies only to HUB.
cirrosys.access_counter Page cirrosys.access_counter is a connection and sql access log which applies only to HUB.
cirrosys.cirroactionrun Page cirrosys.cirroquery lists metadata for.
cirrosys.cirroalert Page cirrosys.cirroalert lists metadata for.
cirrosys.cirroconnection Page cirrosys.cirroconnections is a connection and SQL Access log table.
cirrosys.cirroconnection Page cirrosys.cirroconnections is a connection and SQL Access log table.
cirrosys.cirroquery Page CIRROSYS.CIRROQUERY is a connection and SQL access log table.
cirrosys.cirroquery Page CIRROSYS.CIRROQUERY is a connection and SQL access log table.
cirrosys.cirroquery_archive Page cirrosys.cirroquery_archive lists metadata for.
cirrosys.cirrosession Page cirrosys.cirrosession is a Connection and SQL Access log table.
cirrosys.cirrosession Page cirrosys.cirrosession is a Connection and SQL Access log table.
cirrosys.datamovement Page cirrosys.datamovement is a connection and SQL access log table.
cirrosys.datamovement Page cirrosys.datamovement is a connection and SQL access log table.
cirrosys.datamovement_exceptions Page cirrosys.datamovement_exceptions is a connection and SQL access log table.
cirrosys.datamovement_exceptions Page cirrosys.datamovement_exceptions is a connection and SQL access log table.
cirrosys.excel_vb Page cirrosys.excel_vb lists metadata for.
cirrosys.fqoplans Page cirrosys.fqoplans is a connection and SQL access log table.
cirrosys.fqoplans Page cirrosys.fqoplans is a connection and SQL access log table.
cirrosys.fqoplans_archive Page cirrosys.fqoplans_archive lists metadata for.
cirrosys.google_analytics_dimensions_metrics Page cirrosys.google_analytics_dimensions_metrics lists metadata for.
cirrosys.jobstatus Page cirrosys.jobstatus is a scheduler table.
cirrosys.jobstatus Page cirrosys.jobstatus lists metadata for.
cirrosys.keyvaluepairs Page cirrosys.keyvaluepairs lists metadata for.
cirrosys.map_reduce_functions Page cirrosys.map_reduce_functions lists metadata for.
cirrosys.map_reduce_parameters Page cirrosys.map_reduce_parameters lists metadata for.
cirrosys.mapreducestats Page cirrosys.mapreducestats lists metadata for.
cirrosys.object_destination_lineage Page cirrosys.object_destination_lineage lists metadata for.
cirrosys.object_source_lineage Page cirrosys.object_source_lineage lists metadata for.
cirrosys.permdesc Page cirrosys.permdesc lists metadata for.
cirrosys.project_runs Page cirrosys.project_runs lists metadata for.
cirrosys.projects Page cirrosys.projects lists metadata for.
cirrosys.qrtz_blob_triggers Page cirrosys.qrtz_blob_triggers lists metadata for.
cirrosys.qrtz_calendars Page cirrosys.qrtz_calendars lists metadata for.
cirrosys.qrtz_cron_triggers Page cirrosys.qrtz_cron_triggers lists metadata for.
cirrosys.qrtz_fired_triggers Page cirrosys.qrtz_fired_triggers lists metadata for.
cirrosys.qrtz_job_details Page cirrosys.qrtz_job_details lists metadata for.
cirrosys.qrtz_locks Page cirrosys.qrtz_locks lists metadata for.
cirrosys.qrtz_paused_trigger_grps Page cirrosys.qrtz_paused_trigger_grps metadata for.
cirrosys.qrtz_scheduler_state Page Column Name Type Length Nullable Contents sched_name character varying (480)     instance_name character varying (800)     last_checkin_time bigint       checkin_interval bigint      
cirrosys.qrtz_simple_triggers Page Column Name Type Length Nullable Contents sched_name character varying (480)     trigger_name character varying (800)     trigger_group character varying (800)     repeat_count bigint       repeat_interval bigint       ...
cirrosys.qrtz_simprop_triggers Page cirrosys.qrtz_simprop_triggers metadata for.
cirrosys.qrtz_triggers Page cirrosys.qrtz_triggers metadata for.
cirrosys.rmsexecexceptions Page Column Name Type Length Nullable Contents rmsid integer      
cirrosys.rule_check_log Page cirrosys.rule_check_log is a connection and SQL access log table.
cirrosys.rule_check_log Page cirrosys.rule_check_log is a connection and SQL access log table.
cirrosys.run_detail Page cirrosys.run_detail metadata for.
cirrosys.schema_update Page cirrosys.schema_update metadata for.
cirrosys.sysremotefunctionelements Page cirrosys.sysremotefunctionelements table lists the elements (remote system functions and parameters) of a Cirro remote function definition.
cirrosys.sysremotefunctionparameters Page cirrosys.sysremotefunctionparameters defines the parameters of remote system functions.
cirrosys.sysremotefunctions Page cirrosys.sysremotefunctions lists the remote functions which have been created using the CREATE REMOTE FUNCTION command.
CLEAR CACHE Utility Function Page Empties the system cache, or the given cache ID.
Command Line - Reset Cirro Root Password Page Reset your password using these steps.
COALESCE Conditional Function Page COALESCE returns the first of multiple arguments that is not NULL.
Combining optimizer_hints within a Query Page Multiple hint types can be submitted within a single query. When including multiple hints within a query, follow the same syntactic rules for each included h...
COMPARE command Page Compare the rows of the target cursor or table to those of the source cursor or table.
Concatenation Page Concatenates two string values.
CONNECT Page The CONNECT command is required only when connecting through the Derby ij tool.
CONVERT data type conversion function Page Use this function to convert a value from one data type to another.
COUNT Aggregate Function Page COUNT Returns a count of values in the data set.
Create/Alter Alert Page Alerts can be created to notify you of user logins to the system. Alerts are connected to Rules which when met trigger the alert.
CREATE/ALTER Encryption Key Page CREATE ENCRYPTION KEY creates an encryption key within the system, saving it to a specified location. Use ALTER ENCRYPTION KEY using the same syntax.
CREATE/ALTER PROJECT Page Create or Alter a Data project with this syntax. All options are optional
CREATE BACKUP Page Run a backup of all Cirro settings, drivers and users.
CREATE DRIVER Page Creating a Cirro driver allows you to connect to a data source. After the driver has been created, add resources to it with the ADD RESOURCE command.
CREATE JOB Page Creates a job to refresh one or more materialized views views on a set schedule.
CREATE REMOTE DATABASE Page Creates a local synonym for a remote database. This functionality is used to create mappings to the four-part object names required by Cirro.
CREATE REMOTE FUNCTION Utility Function Page Creates a Cirro function reference which maps to an existing function or functions on remote systems.
CREATE ROLE Page Adds a new Cirro role. This can also be used to associate Cirro roles to active_directory/LDAP roles but requires Cirro and active_directory/LDAP integration
CREATE SYSTEM Page These commands link Cirro to the target database. Each distinct Database type should be added with its own CREATE SYSTEM command.
CREATE TABLE Page Creates a new table on a federated system.
CREATE TABLE as SELECT Page Alters the SELECT query of an existing, non-materialized view.
CREATE USER Page Cirro users created with the CREATE USER command have access permissions to connect to the Cirro Data Hub. Additional actions are not permitted until additio...
CREATE VIEW Page Creates a materialized or non-materialized view from existing tables or views.
CURRENT DATE Date Time Function Page Returns the current date on the Cirro Data Hub.
CURRENT TIME Date Time Function Page Returns the current time on the Cirro Data Hub.
CURRENT TIMESTAMP Date Time Function Page Returns a timestamp the current day and time on the Cirro Data Hub.
USER / CURRENT_USER Utility Function Page Returns the Cirro username used when connecting to Cirro.
CURRENT_UTC_DATE Date Time Function Page Returns the system date in UTC (Coordinated Universal Time).
CURRENT_UTC_TIME Date Time Function Page Returns the system time in UTC (Coordinated Universal Time).
CURRENT_UTC_TIMESTAMP Date Time Function Page Returns the system timestamp in UTC (Coordinated Universal Time).
Database Driver Setup with SQL Page Setup your database drivers using SQL commands.
Database Setup with SQL Page Setup your database using SQL commands.
DATACOPY statement Page SQL to copy data from source to target.
DATE Data Type Page The DATE Data Type stores date values without time components.
DATE data type conversion function Page Use this function to convert a non-date value to a DATE type
DATE_TRUNC Date Time Function Page DATE_TRUNC truncates a TIMESTAMP to the specified precision.
DATEADD Date Time Function Page DATEADD adds a specified interval to a date or time value.
DATEDIFF Date Time Function Page DATEDIFF adds a specified interval to a date or time value.
DATENAME Date Time Function Page DATENAME returns the name or numeric value for the specified date component.
DATEPART Date Time Function Page DATEPART returns the numeric portion of a date.
DAY Date Time Function Page DAY returns the day portion of a value.
DAYOFWEEK Date Time Function Page DAYOFWEEK returns the day of the week, as an integer from 1 to 7, where 1 is Sunday.
DAYOFYEAR Date Time Function Page DAYOFYEAR returns the day of the year, as an integer from 1 to 366, where 1 is January 1.
DECIMAL/NUMERIC Data Type Page The DECIMAL/NUMERIC data type is an Exact numeric type datatype
DENY privilege Page Explicitly denies a permission to a user or role. When a DENY is in place, it generally overrides an explicit or inherited GRANT at the same level.
DISABLE USER Page SQL command disables a user account and prevents login. Password reset enables the account once more, once user has chosen a new password.
Division Page Divides one numeric value into another numeric value.
DOUBLE Data Type Page The DECIMAL/NUMERIC data type is a Floating Point numeric type datatype
DOUBLE data type conversion function Page Converts a value to a DOUBLE type.
job_table_rows Page job_table_rows is a data projects table.
project_runs Page project_runs is a Data Projects table.
projects Page cirrosys.projects lists metadata for.
run_detail Page run_detail is a data projects table.
DROP Alert Page Use the DROP command to delete an alert.
DROP Authentication Provider Page Drops authentication provider from Cirro.
DROP AUTHORIZATION Page Drops existing authorization for a user.
DROP DEFAULT AUTHENTICATION PROVIDER Page Drop a default authentication provider from the system
DROP DRIVER Page Removes an existing Cirro driver, which was created using the CREATE DRIVER command.
Drop Encryption Key Page DROP ENCRYPTION KEY deletes a named encryption key from Cirro.
DROP JOB Page Drops an existing scheduled job.
DROP PROJECT Page Drop an existing Data project with this syntax. The system checks the current session has edit permission on the project.
DROP REMOTE DATABASE Page Drops an existing remote database reference.
DROP_REMOTE_FUNCTION Utility Functions Page Shows the expected data movements and supporting queries for a user-specified query, in the expected execution order.
DROP RESOURCE Page Removes an existing Cirro resource, which was added with the ADD RESOURCE command.
DROP ROLE Page Drops an existing Cirro role.
DROP SYSTEM Page Removes an existing Cirro system, which was created using CREATE SYSTEM.
DROP TABLE Page Drops an existing table which was created using the Cirro CREATE TABLE command.
DROP USER Page Drops an existing Cirro user.
DROP VIEW Page Drops an existing view.
DUPLICATES command Page The DUPLICATES command identifies all rows in the target table or cursor whose key also appears in other rows.
Enable User Account Page There is no specific SQL command to enable a user. Instead, the simplest approach is to force a password reset. When the user resets their password correctly...
Equals Page Compares two expressions for equivalence, and returns a true value if they are found to be equal. Syntax
EXCEPT and INTERSECT Page Compares two queries and returns distinct rows only based on the keyword provided.
EXECUTE PROJECT Page Execute/run the specified data project. EXECUTE PROJECT executes in the current session synchronously.
EXPAND statement Page Use the statement to EXPAND any SELECT statement into block form.
EXPLAIN Utility Function Page Shows the expected data movements and supporting queries for a user-specified SELECT query, in the expected execution order.
FLOAT Data Type Page The FLOAT data type is a Floating Point numeric type datatype.
Force Column Data Type Hint Page Forces the data type during data movement for a particular column to be one of the Cirro data_types or a data type native to a specific database in the feder...
Force Rowcount Hint Page Allows the user to override the rowcount used by the query optimizer, with the goal of altering data movement behaviors. The force rowcount hint can be appli...
Force Runmode Hint Page Forces the optimizer to move query data to and then execute the query on a user-specified system rather than a system selected by the optimizer.
Force Split Column Hint Page Forces the optimizer to split data retrieval using a specified column. This parallelizes the query and data movement.
GENERATE_PASSWORD Page Generates a system-valid, temporary password when specifying passwords with CREATE USER and ALTER USER.
GRANT privilege Page Grants privileges on a specified securable to a user or role.
GRANT role Page Adds a role to a Cirro user or role. The user must be previously granted 'GRANT ROLE' permission.
Greater Than Page Compares two expressions, and returns true if the first value is greater than the second value.
Greater Than or Equal To Page Compares two expressions, and returns true if the first value is greater than or equal to the second value.
Accessing Hadoop and Running Cirro Functions through SQL Page Most of the Cirro functions available through Cirro Analyst for Excel (and described in the Cirro Functions Guide) are also available through Cirro SQL.
HASHROW Utility Function Page Returns the hexadecimal row hash value of one or more expressions.
HOUR Date Time Function Page HOUR returns the hour portion of a value.
Identifiers and Reserved words Page These words are reserved in either Cirro or the SQL-92 standard and should not be used as identifier names.
IN Page Determines whether a value is within a specified range, defined by a subquery, expression, or list.
INCLUDE or EXCLUDE Schemas Page You can choose to include or exclude schemas from any database connected to Cirro.
INSERT Page Adds data into an existing table on the federated system.
INTEGER Data Type Page The INTEGER data type is a Floating Point numeric type datatype.
INTEGER data type conversion functions Page Use this function to convert a value to an INTEGER.
IS [ NOT ] NULL Page Compares an expression or value and determines whether it is NULL.
Joins Page Joins combine data from multiple, related data sources is a single result set. Join expressions appear in the WHERE clause.
LAST_DAY Date Time Function Page LAST_DAY returns the last day of the month.
LCASE / LOWER String Function Page Converts a string value to lowercase characters.
LEFT String Function Page Returns the leftmost characters in a string value, based on a specified number of characters.
Less Than Page Compares two expressions, and returns true if the first value is less than the second value.
Less Than or Equal To Page Compares two expressions, and returns true if the first value is less than or equal to the second value.
LIKE Page Compares a character value to a specified character pattern.
LOCATE String Function Page If a specified substring is found within a specified search string, LOCATE returns the index at which the substring is found within the search string. If the...
LONG VARCHAR Data Type Page LONG VARCHAR is a Variable Length string type data type.
LONG VARCHAR FOR BIT DATA Data Type Page LONG VARCHAR FOR BIT DATA is a variable length Binary Data Type.
LTRIM String Function Page Trims spaces from the start of a string value.
MAX Aggregate Function Page MAX returns the maximum value in a data set.
MIN Aggregate Function Page Min returns the minimum value in a data set.
MINUTE Date Time Function Page MINUTE returns the minute portion of a value.
MONTH Date Time Function Page MONTH returns the month portion of a value.
Multiplication Page Multiplies two numeric values.
Not Equal Page Compares two expressions for equivalence, and returns a true value if they are found to be unequal.
Null data_types Page All of the supported data_types can be NULL. Although NULL is not a value, but the absence of a value, it is sometimes referred to as a “NULL value”.
NULLIF Conditional Function Page NULLIF returns NULL when the two arguments are equivalent.
Numeric data_types Page This section discusses the Cirro-provided numeric data_types.
Optimizer Hints Page Queries submitted through the Cirro Data Hub can include optimizer_hints to control some aspects of query execution which would otherwise be handled automati...
Privilege Mappings Page Cirro has built-in privileges which can be granted, revoked, or denied.
public.user_validation Page public_user_validation is a security model table.
public.user_validation Page public_user_validation is a security model table.
QUARTER Date Time Function Page QUARTER returns the quart of the year in which the day is found, as an integer from 1 to 4, and where the first quarter of the year is “1”.
Query Operators Page The following commands can be used to combine multiple queries.
REAL Data Type Page REAL is a Floating Point numeric type data type.
REFRESH MATERIALIZED VIEW Page Refreshes the data in a materialized view.
REPLACE String Function Page Replaces one or more occurrences of a specified string value with another specified value.
Restart Services Page Follow these instructions to restart services required by Cirro.
RESTRICT USER Page Sets user account in state where user must specify a new password on next login. No other actions are allowed until the password is changed.
REVOKE privilege Page Removes a previously granted or denied privilege.
REVOKE role Page Removes a role from a Cirro user or role. It must be performed prior to a DROP .
RIGHT String Function Page Replaces one or more occurrences of a specified string value with another specified value.
rms.sysviews Page rms.sysviews This table lists metadata for views on systems configured as remote databases.
rms.sysviews Page rms.sysviews This table lists metadata for views on systems configured as remote databases.
RTRIM String Function Page Trims spaces from the end of a string value.
SECOND Date Time Function Page SECOND returns the seconds portion of a value.
security.authorization_providers Page security.authorization_providers is a third-party authorization proivider table that contains authorization providers set-up in the system.
Security Model System Tables Page Use these system tables to determine the permissions already set in the system.
security_password_hashes Page security.password_hashes is a security model table.
security_password_hashes Page security.password_hashes is a security model table.
security.password_requirements Page security.password_requirements is a Login & Password Policy table.
security.rule_alerts Page security.rule_alerts is a security model table.
security.rule_alerts Page security.rule_alerts is a security model table.
security.rules Page security.rules is a security model table.
security.rules Page security.rules is a security model table.
security.syspermissions Page security.syspermissions is a security model table that tracks explicit GRANT and DENY permissions against Cirro users.
security.syspermissions Page security.syspermissions is a security model table that tracks explicit GRANT and DENY permissions against Cirro users.
security.sysroles Page security.sysroles is a security model table that contains Cirro roles.
security.sysroles Page security.sysroles is a security model table that contains Cirro roles.
security.tag_values Page security.tag_values is a security model table.
security.tag_values Page security.tag_values is a security model table.
security.tags Page security.tags is a security model table.
security.tags Page security.tags is a security model table.
security.user_auth_providers Page security.user_auth_providers is a third-party authorization providers table that contains authorization providers set-up in the system.
security.userid_requirements Page security.userid_requirements is a Login & Password Policy table.
security.users Page security.users is a security model table that contains information on Cirro users.
security.users Page security.users is a security model table that contains information on Cirro users.
SELECT Statement Page Retrieves data from a structured data source. The SELECT statement can be used individually, or with one or more of clauses or joins.
SET CACHEPOLICY Page Sets a cache policy for a specified federated system. This is a global setting, and will affect all users and all sessions for this system.
SET DEFAULT AUTHENTICATION PROVIDER Page Optionally set a default provider to enable authentication for users not explicitly managed by Cirro. Typically this will be an Active Directory or LDAP prov...
SET DESCRIBE Page Enables return of query metadata, including column names and data_types.
SET NUMBEROFRDBMSTRANSFERTHREADS Page Specifies the session-specific number of RDBMS transfer threads available to the query optimizer.
SET PASSTHROUGH Page Enables execution of SQL commands directly on remote systems.
Set server time and timezone Page Use the command line to set the Cirro server time and timezone.
SET SQUOOP OPTIONS Page Allows user to specify Sqoop options to override the Cirro default values. These values will be used in Sqoop for data movement between all relational databa...
SHOW PROJECT Page Display any data project using these commands.
SMALLINT Data Type Page SMALLINT is a numeric data type.
SMALLINT data type conversion function Page Use this function to convert a value to a SMALLINT.
Add SSL Security via Command Line Page SSL (Secure Sockets Layer) establishes an encrypted network link between webserver and browser.
SUBSTR String Function Page Returns a substring of a source string, based on starting position and substring length.
Subtraction Page Subtracts two numeric values.
SUM Aggregate Function Page SUM returns the aggregate sum of values in a data set..
Supported data type conversion functions Page The following source data type conversion functions are supported for CAST and CONVERT.
Cirro Privileges - Supported Securables Page These statements control which privileges are available to which users and roles.
SYNC statement Page SQL to sync data between source and target.
SYS.CIRROCOLUMNS Page SYS.CIRROCOLUMNS lists metadata for all columns on Cirro systems.
SYS.CIRROJOBS Page sys.cirrojob is a scheduler table that returns information on scheduled jobs.
SYS.CIRROJOB Page SYS.CIRROJOB returns information on scheduled jobs.
SYS.CIRROREMOTEDATABASES Page SYS.CIRROREMOTEDATABASES provides information on configured remote databases.
SYS.CIRROSCHEMAS Page SYS.CIRROSCHEMAS lists metadata for all schemas on Cirro Systems.
SYS.CIRROTABLES Page SYS.CIRROTABLES lists metadata for all tables on Cirro systems.
SYS.CIRROVIEWS Page This table lists metadata for all views on Cirro systems.
SYS.DRIVERS Page The sys.drivers view shows the configured drivers.
SYS.DRIVERSRESOURCE Page The sys.driversresource view shows configured resources.
sys.privatekeys Page Table used to store system Private Keys.
SYS.RMISERVICE Page SYS.SYSRMISERVICE shows agent systems, RDMSRMS or data movement agents together with Sqoop and Hadoop systems since these are also technically agents.
SYS.SYSCACHEDRESULTSET Page This table provides information on Cirro system cached data.
SYS.SYSCOLUMNS Page This table lists metadata for all columns on systems configured with 3-part names.
SYS.SYSEXCLUDEDSCHEMAS Page This table provides a list of all excluded schemas from remote data sources.
SYS.SYSFILESTRUCTURES Page This table table provides information on remote system file structures.
SYS.SYSPERMISSIONS Page This table tracks explicit GRANT and DENY permissions against Cirro users.
SYS.SYSPOLICIES Page This table tracks Cirro user security policies set with ALTER POLICY command. These values are specified through the Admin Console or using UPDATE commands.
SYS.SYSREMOTEDATABASES Page This table lists the local synonyms which have been mapped to remote databases.
SYS.SYSREMOTESYSTEMS Page SYS.SYSREMOTESYSTEMS lists all the RDBMS systems, or the systems that can be viewed that way. This table provides information on configured remote systems.
SYS.SYSREMOTESYSTEMTYPES Page This table provides a list of remote system types, referenced in SYS.SYSREMOTESYSTEMS.
SYS.SYSROLES Page This table provides information on Cirro roles.
SYS.SYSSCHEMAS Page This table lists metadata for schemas on systems configured as remote databases.
SYS.SYSTABLES Page This table lists metadata for tables on systems configured as remote databases.
SYS.SYSVIEWS Page This table lists metadata for views on systems configured as remote databases.
SYS.USERS Page This table provides information on Cirro users.
SYSCS_UTIL.SYSCS_FREE_CACHE() Utility Function Page Empties the system cache.
SYSTEM_USER Utility Function Page Returns the username of the user which was used to run the query on the data source.
TIME Data Type Page The TIME Data Type Stores time values without date components.
TIME Date Time Function Page TIME returns the time portion of a value.
TIMESTAMP Data Type Page The TIMESTAMP Data Type stores a date and time.
TIMESTAMP Date Time Function Page TIMESTAMP returns the timestamp portion of a value.
TIMESTAMPDIFF Date Time Function Page TIMESTAMPDIFF returns the difference between two timestamp values, based on a specified interval type.
TRIM String Function Page Trims spaces from the start and end of a string value.
UCASE / UPPER String Function Page Converts a string value to uppercase characters.
USE Page Changes the database context to another database or to a specific schema, table, or view.
USE Page Changes the database context to another database or to a specific schema, table, or view.
VARCHAR Data Type Page VARCHAR is a Variable Length string data type.
VARCHAR FOR BIT DATA Data Type Page VARCHAR FOR BIT DATA is a variable length Binary Data Type.
WEEK Date Time Function Page WEEK returns the week of the year in which the day is found, as an integer from 1 to 52, and where the first week of the year is 1.
YEAR Date Time Function Page YEAR returns the year portion of a value.