### Create Optimization Job Examples

Source: https://docs.datavirtuality.com/v25/optimization-jobs

Examples demonstrating the creation of optimization jobs with different sets of parameters. These examples show how to specify required and optional arguments.

```SQL
CALL SYSADMIN.createOptimizationJob(recOptId => 5)
```

```SQL
CALL SYSADMIN.createOptimizationJob(recOptId => 5, allowIndexCreation => 'JOIN, MANUAL')
```

```SQL
CALL SYSADMIN.createOptimizationJob(recOptId => 5, allowIndexCreation => 'NONE', gatherNativeStats => FALSE)
```

--------------------------------

### Import Index Examples

Source: https://docs.datavirtuality.com/v25/index-management

Examples demonstrating the usage of SYSADMIN.importIndex for different index types and configurations.

```SQL
CALL SYSADMIN.importIndex(2, 'test_tables_or.test_a.a,test_tables_or.test_a.b', 'MANUAL', null, 'MULTIPLE') ;

CALL SYSADMIN.importIndex(1, 'FLOOR(test_tables_pg.test_a.a)', 'MANUAL', null, 'EXPRESSION') ;
```

--------------------------------

### Unzip Example with Output Folder

Source: https://docs.datavirtuality.com/v25/unzip

This example demonstrates calling the UNZIP procedure when a specific output folder is provided.

```SQL
CALL "UTILS.unzip"(
    "file_datasource" => 'file',
    "file_name" => 'jboss-as-7.1.1.Final.zip',
    "output_folder" => '1'
);;
```

--------------------------------

### Example of includeTables and excludeTables combined

Source: https://docs.datavirtuality.com/v25/salesforce-connector

This example demonstrates how to use both `includeTables` and `excludeTables` properties. It includes tables starting with 'Account' but excludes 'AccountPartner'.

```text
importer.includeTables=^Account.*$,importer.excludeTables=^AccountPartner$
```

--------------------------------

### Create Index Example (Basic)

Source: https://docs.datavirtuality.com/v25/index-management

A basic example demonstrating the CALL syntax for the createIndex procedure with essential parameters.

```SQL
CALL SYSADMIN.createIndex(1, 'test_tables_pg.test_a.a', 'MANUAL', NULL, 'SINGLE') ;
```

--------------------------------

### Example Call to dGetPiece

Source: https://docs.datavirtuality.com/v25/public-api

This example demonstrates how to call the dGetPiece procedure with sample parameters for retrieving piece details.

```SQL
CALL dGetPiece('john.doe','fxeiu483','23454664553','piece-code','2012-01-01','2012-05-01')
```

--------------------------------

### SELECT INTO Example 1: Copy all columns

Source: https://docs.datavirtuality.com/v25/create-table

This example creates 'dwh.table1' with the same structure and data as 'source.table2' by selecting all columns.

```SQL
SELECT * INTO dwh.table1 FROM source.table2;
```

--------------------------------

### Example: Set User Query Limits

Source: https://docs.datavirtuality.com/v25/user-and-role-management-procedures

An example demonstrating how to call the setUserLimits procedure to configure query restrictions for a user.

```SQL
CALL "SYSADMIN.setUserLimits"(
    "name" => 'user1',
    "concurrentQueries" => 10,
    "maxQueryTime" => 5,
    "maxRows" => 100
);;
```

--------------------------------

### Example JAVA_TOOL_OPTIONS for Kerberos

Source: https://docs.datavirtuality.com/v25/tableau-desktop-as-jdbc-client

An example of how to set the JAVA_TOOL_OPTIONS environment variable with specific Kerberos realm and KDC values.

```text
-Djavax.security.auth.useSubjectCredsOnly=false 
-Dsun.security.jgss.native=false 
-Djava.security.krb5.realm=KRBTEST.DV
-Djava.security.krb5.kdc=DC01.KRBTEST.DV
```

--------------------------------

### $NOOPT Option Example

Source: https://docs.datavirtuality.com/v25/option-clause

Example of setting the $NOOPT option to TRUE to disable query optimizations.

```SQL
SET $NOOPT TRUE;;
```

--------------------------------

### Start a Job Immediately

Source: https://docs.datavirtuality.com/v25/managing-jobs

Starts a specified job immediately by creating a background schedule. The job ID or UUID must be provided.

```SQL
SYSADMIN.startJob(IN id biginteger, IN startDelay integer, IN queueHandler string, IN uuid string)
```

--------------------------------

### CREATE TABLE Example

Source: https://docs.datavirtuality.com/v25/create-table

This example demonstrates creating a table named 'table1' with an integer column 'col1' in the 'dwh' schema.

```SQL
CREATE TABLE dwh.table1 (col1 integer);
```

--------------------------------

### Create Leaky Bucket Example

Source: https://docs.datavirtuality.com/v25/rate-limit-procedures

Example of creating a new leaky bucket with a specified capacity and refill rate.

```SQL
CALL "SYSADMIN.leakBucketTryConsume"(
    "name" => 'bucket1',
    "capacity" => 2,
    "refill" => 10
);;
```

--------------------------------

### SELECT INTO Example 2: Copy specific columns

Source: https://docs.datavirtuality.com/v25/create-table

This example creates 'dwh.table1' with specific columns 'a' and 'b' from a join of 'source.table2'.

```SQL
SELECT t1.a, t2.b INTO dwh.table1 FROM source.table2 t1 JOIN source.table2 t2 ON t1.c = t2.c;
```

--------------------------------

### Create Installation Directory and Set Ownership

Source: https://docs.datavirtuality.com/v25/cdata-virtuality-server-as-a-service

Creates the installation directory for CData Virtuality Server and assigns ownership to the dedicated user.

```bash
mkdir /opt/datavirtuality
chown -R datavirtuality:datavirtuality /opt/datavirtuality
```

--------------------------------

### Get Installed License Information

Source: https://docs.datavirtuality.com/v25/system-data-procedures

Retrieves detailed information about the installed license, including expiration date, trial status, holder, start date, consumer type and amount, and additional info.

```SQL
CALL getInstalledLicense(OUT expDate timestamp NOT NULL RESULT, OUT trial boolean NOT NULL, OUT holder string NOT NULL, OUT startDate timestamp NOT NULL, OUT consumerType string NOT NULL, OUT consumerAmount integer NOT NULL, OUT info string NOT NULL, OUT limitInfo string )
```

```SQL
SELECT * FROM (CALL SYSADMIN.getInstalledLicense()) a;
```

--------------------------------

### Get Start Point of LineString - ST_StartPoint

Source: https://docs.datavirtuality.com/v25/spatial-functions

Extracts the starting point of a LineString geometry. Returns null if the input geometry is not a LineString.

```SQL
ST_StartPoint(geom)
```

--------------------------------

### Create Index Examples (Named Parameters)

Source: https://docs.datavirtuality.com/v25/index-management

Illustrates creating indexes using named parameters for single, multiple, and expression kinds, including table creation.

```SQL
CREATE TABLE dwh.testIndex (a integer, b integer);;

--Single kind:
CALL "SYSADMIN.createIndex"(
    "columnName" => 'a',
    "tableName" => 'dwh.testIndex'
);;

--Multiple kind:
CALL "SYSADMIN.createIndex"(
    "columnName" => 'a,b',
    "tableName" => 'dwh.testIndex',
    "indexKind"=> 'MULTIPLE'
);;

--Expression kind:
CALL "SYSADMIN.createIndex"(
    "columnName" => 'dwh.testIndex.a + dwh.testIndex.b',
    "tableName" => 'dwh.testIndex',
    "indexKind"=> 'EXPRESSION',
    "uuid"=> 'f0cd2e3c-8267-41cc-9fed-4de73cd1bcb2'
);;
```

--------------------------------

### OData Query Example

Source: https://docs.datavirtuality.com/v25/odata

Example of accessing a table via an HTTP GET request using OData. The table name is case-sensitive.

```text
GET http://localhost:8080/odata4/datavirtuality/SYSADMIN/Roles
```

--------------------------------

### Sample boot.log Entries

Source: https://docs.datavirtuality.com/v25/logs

Provides sample lines from the boot.log file, illustrating configuration details, Java environment information, hostnames, and memory settings.

```bash
... 

dv.dvconfig.ds = dvconfigs_pg
dv.dvconfig.schema = dvconfig
dv.dvconfig.type = psql
dv.dvlogs.ds = dvconfigs_pg
dv.dvlogs.schema = dvlogs
dv.dvlogs.type = psql
...
java.home = C:\Program Files\Java\jdk1.7.0_60\jre
...
java.runtime.name = Java(TM) SE Runtime Environment
java.runtime.version = 1.7.0_60-b19
java.specification.name = Java Platform API Specification
java.specification.vendor = Oracle Corporation
java.specification.version = 1.7
...
jboss.host.name = dv-pc
...
jboss.node.name = dv-pc
jboss.qualified.host.name = dv-pc
...
jboss.server.log.dir = C:\Program Files(x86)\datavirtuality\dvserver\standalone\log
...
VM Arguments: -XX:+TieredCompilation -Dprogram.name=standalone.bat -Xrs -Xms8192M -Xmx8192M -XX:MaxPermSize=512M -XX:MaxDirectMemorySize=6g ...
```

--------------------------------

### Invoke Web Service (HTTP GET)

Source: https://docs.datavirtuality.com/v25/web-services-connector

A basic example of invoking a web service using the invoke procedure with the HTTP binding and GET action.

```SQL
CALL ws.invoke(binding => 'HTTP', action => 'GET');;
```

--------------------------------

### Example with MySQL and NOFAIL Option

Source: https://docs.datavirtuality.com/v25/connecting-to-analytical-storage

This example demonstrates setting up a MySQL datasource and then using `setCurrentDWH()` with the `OPTION $NOFAIL` to set it as the current DWH, followed by checking the settings.

```SQL
-- MySQL data source
CALL SYSADMIN.createConnection('ms','mysql','host=localhost,db=adventureworks,user-name=root,password=root');;
CALL SYSADMIN.createDatasource('ms','mysql5','importer.defaultSchema=adventureworks,importer.useFullSchemaName=False,importer.tableTypes="TABLE,VIEW",importer.importIndexes=false','');;
 
--set MySQL data source as DWH
CALL SYSADMIN.setCurrentDWH(nameInDv => 'ms', nameInSource => 'ms');;
CALL SYSADMIN.setCurrentDWH(nameInDv => 'salesforce2', nameInSource => 'salesforce2') OPTION $NOFAIL;; 
SELECT * FROM SYSADMIN.getCurrentDWH();;
```

--------------------------------

### Example of includeTables property

Source: https://docs.datavirtuality.com/v25/salesforce-connector

The `includeTables` property uses a case-insensitive regular expression to specify which tables should be included during import. This example includes tables whose names start with 'Account'.

```text
importer.includeTables=^Account.*$
```

--------------------------------

### Example of excludeTables property

Source: https://docs.datavirtuality.com/v25/salesforce-connector

The `excludeTables` property uses a case-insensitive regular expression to prevent specific tables from being imported. This example excludes tables whose names start with 'Account'.

```text
importer.excludeTables=^Account.*$
```

--------------------------------

### Windows: Run Simple Query

Source: https://docs.datavirtuality.com/v25/cdata-virtuality-command-line-client-dsql

Example of running a simple SQL query on Windows using default host and port. Ensure you provide valid username and password.

```bash
dsql.bat "username=user1" "password=password1" "query=select 1 ;;"
```

--------------------------------

### Kill Session Example (Versions up to 25.2)

Source: https://docs.datavirtuality.com/v25/query-management

Example of how to kill a session using SYSADMIN.killSession in system versions up to 25.2. First, select from SYSLOG.QueryLogs to get the session ID, then call the procedure.

```SQL
SELECT * FROM SYSLOG.QueryLogs ;;
-- use the "sessionId" from the result
CALL SYSADMIN.killSession('njWUGSusn8rz') ;;
```

--------------------------------

### JOIN Syntax Examples

Source: https://docs.datavirtuality.com/v25/from-clause

Demonstrates different types of joins between tables.

```SQL
FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria
```

```SQL
FROM table1 CROSS JOIN table2
```

--------------------------------

### Kill Query Example (Versions up to 25.2)

Source: https://docs.datavirtuality.com/v25/query-management

Example of how to kill a query using SYSADMIN.killQuery in system versions up to 25.2. First, select from SYSLOG.QueryLogs to get the query ID, then call the procedure.

```SQL
SELECT * FROM SYSLOG.QueryLogs ;;
-- use the "id" from the result
CALL SYSADMIN.killQuery(1) ;;
```

--------------------------------

### Example of Adding a System Property

Source: https://docs.datavirtuality.com/v25/user-defined-system-properties-management

Demonstrates how to call the addSystemProperty procedure with sample string values for type, key, and properties.

```SQL
CALL "SYSADMIN.addSystemProperty"(
    "propType" => 'string_propType',
    "propKey" => 'string_propKey',
    "properties" => 'string_properties'
);;
```

--------------------------------

### Kill Session Example (Version 25.2 and higher)

Source: https://docs.datavirtuality.com/v25/query-management

Example of how to kill a session using SYSADMIN.killSession in system versions 25.2 and higher. First, select from SYSLOG.ActiveQueries to get the session ID, then call the procedure.

```SQL
SELECT * FROM SYSLOG.ActiveQueries ;;
-- use the "sessionId" from the result
CALL SYSADMIN.killSession('njWUGSusn8rz') ;;
```

--------------------------------

### Kill Query Example (Version 25.2 and higher)

Source: https://docs.datavirtuality.com/v25/query-management

Example of how to kill a query using SYSADMIN.killQuery in system versions 25.2 and higher. First, select from SYSLOG.ActiveQueries to get the query ID, then call the procedure.

```SQL
SELECT * FROM SYSLOG.ActiveQueries ;;
-- use the "id" from the result
CALL SYSADMIN.killQuery(1) ;;
```

--------------------------------

### Examples for Setting Global Index Creation Policy

Source: https://docs.datavirtuality.com/v25/index-management-and-recommended-indexes

Demonstrates setting the global index creation policy to 'NEW_ACCEPTED', 'ACCEPTED', or 'NONE'.

```SQL
CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'NEW_ACCEPTED');;
```

```SQL
CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'ACCEPTED');;
```

```SQL
CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'NONE');;
```

--------------------------------

### Load Source System Functions Example

Source: https://docs.datavirtuality.com/v25/vertica-connector

Enables loading of source system functions, supported for Microsoft SQL Server and Azure.

```text
importer.loadSourceSystemFunctions=TRUE
```

--------------------------------

### SYSADMIN.getInstalledLicense

Source: https://docs.datavirtuality.com/v25/system-data-procedures

Retrieves detailed information about the installed license, including expiration date, trial status, holder, start date, consumer type and amount, and additional info.

```APIDOC
## CALL SYSADMIN.getInstalledLicense

### Description
Provides license information for the CData Virtuality Server instance.

### Method
CALL

### Parameters
#### Output Parameters
- **expDate** (timestamp) - NOT NULL - The expiration date of the license.
- **trial** (boolean) - NOT NULL - Indicates if the license is a trial version.
- **holder** (string) - NOT NULL - The name of the license holder.
- **startDate** (timestamp) - NOT NULL - The start date of the license.
- **consumerType** (string) - NOT NULL - The type of consumer the license is for.
- **consumerAmount** (integer) - NOT NULL - The amount of consumers the license allows.
- **info** (string) - NOT NULL - Additional information about the license.
- **limitInfo** (string) - NOT NULL - Information about license limits.

### Example
```sql
SELECT * FROM (CALL SYSADMIN.getInstalledLicense()) a;
```
```

--------------------------------

### Get API Status (On-premise)

Source: https://docs.datavirtuality.com/v25/rest-api

This endpoint checks the status of the CData Virtuality REST API for on-premise installations. It's a good way to verify authentication and connectivity.

```APIDOC
## GET /rest/api/status/

### Description
Retrieves the current status of the CData Virtuality REST API.

### Method
GET

### Endpoint
http://<datavirtuality-server-address>:8080/rest/api/status/

### Parameters
None

### Request Example
None

### Response
#### Success Response (200)
- **status** (string) - Indicates the API status, typically "OK".

#### Response Example
{
  "status": "OK"
}
```

--------------------------------

### Create View and Convert to XML

Source: https://docs.datavirtuality.com/v25/tabletoxml

This example demonstrates creating a sample view and then using the tableToXml procedure to convert it into an XML format. It includes the SQL to create the view and the procedure call.

```sql
CREATE VIEW views.testView
AS
SELECT 1 AS "a", 2 AS "b", 9 AS "c"
UNION ALL
SELECT 5 AS "a", 7 AS "b", 10 AS "c"
UNION ALL
SELECT 9 AS "a", 8 AS "b", 20 AS "c";;

CALL "UTILS.tableToXml"(
    "tableName" => 'views.testView'
);;
```

--------------------------------

### Example Java Security Configuration for Login

Source: https://docs.datavirtuality.com/v25/tableau-desktop-as-jdbc-client

An example of the java.security file configuration, pointing to a specific dvclient.conf file path on the C: drive.

```text
#
# Default login configuration file
#
#login.config.url.1=file:${user.home}/.java.login.config
login.config.url.1=file:C:/datavirtuality/dvclient.conf
```

--------------------------------

### Get Count of Tables (No Incremental Field)

Source: https://docs.datavirtuality.com/v25/returnextremevalue

This example demonstrates calling returnExtremeValue without specifying an incremental field. In such cases, the procedure returns no data, resulting in a null value assignment.

```SQL
SELECT count(*) FROM UTILS.returnExtremeValue (
    tableName => 'SYS.Tables'
);;
```

--------------------------------

### Example of Creating a Date Axis Virtual Procedure

Source: https://docs.datavirtuality.com/v25/create-procedure

This example demonstrates creating a virtual procedure named `dateaxis` that generates a series of dates between a start and end date. It utilizes a local temporary table and a WHILE loop. Note the use of double semicolons (`;;`) as the statement separator, which is required to distinguish statements inside and outside the procedure block.

```SQL
CREATE VIRTUAL PROCEDURE views.dateaxis(IN startdate date,IN enddate date) RETURNS (xdate date) OWNER 'admin' EXECUTE AS OWNER
AS 
BEGIN
DECLARE date idate;
idate=startdate;
CREATE LOCAL TEMPORARY TABLE #x(xdate date);
  WHILE (idate<=enddate)
  BEGIN
	INSERT INTO #x(xdate) VALUES (idate);
	idate=timestampadd(SQL_TSI_DAY,1,idate);
  END
SELECT * from #x;
END;;
```

--------------------------------

### SQL Create and Insert Data

Source: https://docs.datavirtuality.com/v25/update-table

Demonstrates how to create a sample table and insert initial data for testing update operations. Ensure the table and columns match your intended use case.

```SQL
CREATE TABLE dwh.test_table (a integer, b integer) ;;
INSERT INTO dwh.test_table VALUES(1, 2);;
INSERT INTO dwh.test_table VALUES(3, 4);;
```

--------------------------------

### Start DS command

Source: https://docs.datavirtuality.com/v25/connection-level-interface-commands

The START DS command writes a DS command start to the buffer.

```cli
start ds;
```

--------------------------------

### Create Basic Salesforce Connection and Data Source

Source: https://docs.datavirtuality.com/v25/salesforce-connector

Establishes a basic connection to Salesforce and creates a corresponding data source. Use this for initial setup.

```sql
CALL SYSADMIN.createConnection( 'salesforcealias', 'salesforce', 'user-name=user1,password=pass1' );
CALL SYSADMIN.createDatasource( 'salesforcealias', 'salesforce', '<model properties>', '<translator properties>');
```

--------------------------------

### Start Embedded PostgreSQL

Source: https://docs.datavirtuality.com/v25/embedded-postgresql-as-configuration-database

Starts the embedded PostgreSQL service. This is done automatically when the CData Virtuality Server starts.

```bash
dvserver/pgsql/embeddedpg_start.bat/.sh
```

--------------------------------

### Windows: Run Query with Host and Port

Source: https://docs.datavirtuality.com/v25/cdata-virtuality-command-line-client-dsql

Example of running a SQL query on Windows while specifying the host and port. This is useful when connecting to a non-default server instance.

```bash
dsql.bat "username=user1" "password=password1" "host=localhost" "port=31000" "query=select 1 ;;"
```

--------------------------------

### Start RA command

Source: https://docs.datavirtuality.com/v25/connection-level-interface-commands

The START RA command adds a start line for a resource adapter to the output script.

```cli
start ra "resource-adapter.rar" "class.name" 
```

--------------------------------

### Install XULRunner on OpenSUSE 13.1 (64-bit)

Source: https://docs.datavirtuality.com/v25/installing-cdata-virtuality-studio-linux

Downloads and installs XULRunner v.24 for 64-bit architecture on OpenSUSE 13.1. Ensure you have wget and tar installed.

```bash
--on 64bit architecture
wget http://ftp.mozilla.org/pub/mozilla.org/xulrunner/releases/24.0/runtimes/xulrunner-24.0.en-US.linux-x86_64.tar.bz2
sudo tar xvjf xulrunner-24.0.en-US.linux-x86_64.tar.bz2 -C /opt/
```

--------------------------------

### Linux: Run Simple Query

Source: https://docs.datavirtuality.com/v25/cdata-virtuality-command-line-client-dsql

Example of running a simple SQL query on Linux using default host and port. Use the long-form arguments for username and password.

```bash
./dsql.sh --username user1 --password password1 "select 1 ;;"
```

--------------------------------

### Install XULRunner on OpenSUSE 13.1 (32-bit)

Source: https://docs.datavirtuality.com/v25/installing-cdata-virtuality-studio-linux

Downloads and installs XULRunner v.24 for 32-bit architecture on OpenSUSE 13.1. Ensure you have wget and tar installed.

```bash
--on 32bit architecture
wget http://ftp.mozilla.org/pub/mozilla.org/xulrunner/releases/24.0/runtimes/xulrunner-24.0.en-US.linux-i686.tar.bz2
sudo tar xvjf xulrunner-24.0.en-US.linux-i686.tar.bz2 -C /opt/
```

--------------------------------

### Make AI Backend Executable and Run (Linux)

Source: https://docs.datavirtuality.com/v25/talk-to-your-data-installation

On Linux, first grant execute permissions to the main AI backend executable, then run it. Ensure the vars.env file is in the correct directory.

```bash
chmod +x main
./main
```

--------------------------------

### GET /rest/api/query

Source: https://docs.datavirtuality.com/v25/sql-queries

Executes a SQL query using the GET method. While POST is recommended for security, GET can be used for demo purposes or special cases.

```APIDOC
## GET /rest/api/query

### Description
Executes a SQL query using the GET method. This method is suitable for demo purposes and specific use cases, although the POST method is recommended for enhanced security.

### Method
GET

### Endpoint
/rest/api/query

### Parameters
#### Query Parameters
- **sql** (string) - Required - Encoded SQL statement
- **array** (boolean) - Optional - Default value: TRUE - If TRUE, returns a 2D array; otherwise, returns an array of objects.
- **headers** (boolean) - Optional - Default value: FALSE - Works only with `array=TRUE` and controls whether column headers are included in the response.
- **pagination** (boolean) - Optional - Default value: FALSE - Activates the pagination mode.
- **requestid** (string) - Optional - Default value: "" - Allows using a cursor with the provided identifier buffered in a previous request.
- **limit** (long) - Optional - Default value: -1 - Restricts the number of results returned from the SQL statement.
- **offset** (long) - Optional - Default value: -1 - Excludes the number of results returned from the SQL statement.
- **timeout** (long) - Optional - Default value: 600000 - Timeout in milliseconds before closing the connection to the CData Virtuality Server. This is counted after the last use of the connection. To change the default value, set the desired one via the `rest.timeout` system property in the **standalone.conf.props(.bat)** file.

### Request Example
```
GET http://<cdatavirtuality-server-address>:8080/rest/api/query?sql=select%201&array=false&timeout=1200000
```

### Response
#### Success Response (200)
- Response format depends on the `array` and `headers` parameters. Example for `array=false`:
```json
[{"expr1":1}]
```

```

--------------------------------

### Create View and Convert to JSON

Source: https://docs.datavirtuality.com/v25/tabletojson

This example demonstrates creating a sample view and then using the tableToJson procedure to convert it into a JSON representation. It highlights the full workflow from data definition to JSON output.

```sql
CREATE VIEW views.testView
AS
SELECT 1 AS "a", 2 AS "b", 9 AS "c"
UNION ALL
SELECT 5 AS "a", 7 AS "b", 10 AS "c"
UNION ALL
SELECT 9 AS "a", 8 AS "b", 20 AS "c";;

CALL "UTILS.tableToJson"(
    "tableName" => 'views.testView'
);;
```

--------------------------------

### Example of Creating a Custom Backup Job

Source: https://docs.datavirtuality.com/v25/backup-jobs

This example demonstrates how to call the SYSADMIN.createBackupJob procedure with specific parameters to set up a custom backup job. Note that the jarFileLocation parameter is deprecated since v4.10.

```SQL
CALL "SYSADMIN.createBackupJob"(
    "description" => 'A custom backup job',
    "parallelRunsAllowed" => 1,
    "retryCounter" => 2,
    "retryDelay" => 3,
    "runTimeout" => 4,
    "jobName" => 'csmt-bckp-jb',
    "jarFileLocation" => '../bin/cli-export-1.0/cli-export.jar',
    "exportMattables" => true,
    "backupFolder" => '../custom_backup',
    "fileNamePattern" => '$(uuid)_custom_backup.sql',
    "additionalProperties" =>('--export-jboss-settings', 'true',),
	"uuid" => '7925969c-63cb-11ee-8c99-0242ac120002'
);;
```

--------------------------------

### Install MPP with Default Settings

Source: https://docs.datavirtuality.com/v25/massively-parallel-processing-installation-and-con

Installs MPP using the Helm chart with default configurations. Customizations can be applied using a values.yaml file.

```bash
helm install my-mpp mpp/mpp
```

--------------------------------

### PostgreSQL OrderByStringConversion Examples

Source: https://docs.datavirtuality.com/v25/postgresql

Examples of how to configure the 'OrderByStringConversion' property for ORDER BY clauses in PostgreSQL.

```SQL
OrderByStringConversion=%s
-- no conversion will be applied
```

```SQL
OrderByStringConversion=(binary %s)
-- ORDER BY (binary someStringExpression)
```

```SQL
OrderByStringConversion=cast((%s) as varchar(2000) ccsid 1208)
-- ORDER BY cast((someStringExpression) as varchar(2000) ccsid 1208)
```

```SQL
OrderByStringConversion=(%s COLLATE "C")
-- PostgreSQL specific conversion
```

--------------------------------

### Example: Configure SMTP with UTILS

Source: https://docs.datavirtuality.com/v25/setsmtpconfiguration

An example demonstrating how to set SMTP configuration parameters using the UTILS procedure. Note that 'maxRetries' and 'responseTimeout' are available from v25.3.

```SQL
CALL "UTILS.setSmtpConfiguration"(
    "hostname" => 'example_host',
    "port" => 12345,
    "ssl" => true,
	"starttls" => false,
    "username" => 'smtp-user',
    "password" => 'secret',
    "maxRetries" => 2,
    "responseTimeout" => 30
) ;;
```

--------------------------------

### NOCACHE Option Example

Source: https://docs.datavirtuality.com/v25/option-clause

Example of using the NOCACHE option to disable caching for schema.table1.

```SQL
SELECT t1.id FROM postgres.table1 OPTION NOCACHE schema.table1;;
```

--------------------------------

### Check if unixODBC is installed

Source: https://docs.datavirtuality.com/v25/installing-cdata-virtuality-odbc-linux

Verify if the unixODBC package is installed on your system by checking for the 'isql' utility.

```bash
$ which isql
```

--------------------------------

### Example: Create SQL Server Data Source using Azure Key Vault

Source: https://docs.datavirtuality.com/v25/azure-key-vault

This example demonstrates how to create an SQL Server data source, referencing Azure Key Vault for the password using the credential store and alias.

```SQL
CALL "SYSADMIN.createOrReplaceDatasource"(
    "name" => 'test',
    "translator" => 'sqlserver',
    "modelProperties" => 'importer.useFullSchemaName=False,importer.TableTypes="TABLE,VIEW",importer.schemaPattern=dbo,importer.defaultSchema=dbo',
    "connectionTemplateName" => 'mssql',
    "connectionProperties" => 'user-name=USER_NAME,port=SERVER_PORT,host=HOST,db=DATABASE,password="{credentialStoreName=azure-key-vault-store,credentialStoreAlias=SECRET_KEY_NAME}"',
    "enableReplace" => TRUE
);;
```

--------------------------------

### Download and Unpack Virtuality Server

Source: https://docs.datavirtuality.com/v25/cdata-virtuality-server-as-a-service

Navigates to the installation directory, downloads the CData Virtuality Server, unpacks it, and sets ownership.

```bash
cd /opt/datavirtuality
wget "<insert provided download URL>"
unzip <name of downloaded file>
chown -R datavirtuality:datavirtuality dvserver
```

--------------------------------

### Create FTP Connection and Data Source

Source: https://docs.datavirtuality.com/v25/ftp-sftp-scp-connector

Example of creating an FTP connection and a corresponding data source using SQL. Ensure correct host, port, user, password, and security settings are provided.

```SQL
CALL SYSADMIN.createConnection(name => 'ftp', jbossCLITemplateName => 'ftp', connectionOrResourceAdapterProperties => 'host=myserver,poer=21,user=JohnDoe,password=mySecretPassword,secure=false,passive=true') ;;
CALL SYSADMIN.createDataSource(name => 'ftp', translator => 'ufile', modelProperties => '', translatorProperties => '') ;;
```

--------------------------------

### Greenplum comparisonStringConversion Example: No Conversion

Source: https://docs.datavirtuality.com/v25/greenplum-database

Example of setting comparisonStringConversion to apply no conversion to WHERE clauses on strings.

```SQL
comparisonStringConversion=%s
-- no conversion will be applied
```

--------------------------------

### Example: Drop a Standard Procedure

Source: https://docs.datavirtuality.com/v25/drop-procedure

This is a concrete example of dropping the 'dateaxis' procedure from the 'views' schema.

```SQL
DROP PROCEDURE views.dateaxis
```

--------------------------------

### Example: Configure SMTP with SYSADMIN

Source: https://docs.datavirtuality.com/v25/setsmtpconfiguration

An example demonstrating how to set SMTP configuration parameters using the SYSADMIN procedure. Note that 'maxRetries' and 'responseTimeout' are available from v25.3.

```SQL
CALL "SYSADMIN.setSmtpConfiguration"(
    "hostname" => 'example_host',
    "port" => 12345,
    "ssl" => true,
	"starttls" => false,
    "username" => 'smtp-user',
    "password" => 'secret',
    "maxRetries" => 2,
    "responseTimeout" => 30
) ;;
```

--------------------------------

### Install DEB Package

Source: https://docs.datavirtuality.com/v25/cdata-virtuality-server-as-a-service

Installs the CData Virtuality Server DEB package on Debian-based Linux distributions.

```bash
sudo dpkg -i dvserver-RELEASE_<version>.deb
```

--------------------------------

### Example Usage of sendCsvExport

Source: https://docs.datavirtuality.com/v25/sendcsvexport

This example shows a practical call to the sendCsvExport procedure with specific values for its parameters.

```SQL
 CALL "views.sendCsvExport"(
     sourceSchema => 'dwh'
    ,sourceTable => 'Fetch_list_of_notes'
    ,Recipients => 'user@example.com'
    ,Subject => 'test send csv'
    ,Body => 'test body'
    ,fileName => 'test_send_file.csv'
    ,delimiter => ','
    ,quote => '"'
    ,header => null
    ,orderBy => null
    ,encoding => null
);;
```

--------------------------------

### Create File Connector Connection and Datasource

Source: https://docs.datavirtuality.com/v25/file-connector

Demonstrates how to create a connection and a datasource for the file connector using SQL. Ensure the 'ParentDirectory' is correctly set for your local filesystem path.

```SQL
CALL SYSADMIN.createConnection('filealias', 'ufile', 'ParentDirectory=c:/parentDataDir');;
CALL SYSADMIN.createDatasource('filealias', 'ufile', 'importer.useFullSchemaName=false', null);;
```

--------------------------------

### Reset Leaky Bucket Example

Source: https://docs.datavirtuality.com/v25/rate-limit-procedures

Example of calling the SYSADMIN.leakyBucketReset procedure to destroy a specified leaky bucket.

```SQL
CALL SYSADMIN.leakyBucketReset("name" => 'bucket1');;
```