### Example: Get View and Procedure Creation Order

Source: https://docs.datavirtuality.com/v4/dependency-management

Example of how to call the SYSADMIN.getViewAndProcCreationOrder system procedure.

```SQL
SELECT * FROM (CALL SYSADMIN.getViewAndProcCreationOrder()) AS p
```

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

### Create and Run an Interval Schedule

Source: https://docs.datavirtuality.com/v4/creating-schedules

This example demonstrates creating a schedule with a specified interval and start delay, and then immediately running it. Ensure the interval and startDelay values are appropriate for your needs.

```SQL
CALL SYSADMIN.CreateSchedule(1, 'interval', 1, 0, null, true, null, null, null)
```

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

### Exasol SQL Example

Source: https://docs.datavirtuality.com/v4/amazon-aurora-mysql-edition

Example of how to specify the Exasol database for the `importer.catalog` property.

```sql
EXA_DB
```

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

### Install CData Virtuality on RPM-based Distributions

Source: https://docs.datavirtuality.com/v4/data-virtuality-server-as-a-service

Use these commands to install the CData Virtuality RPM package, enable the service to start on boot, and start the service.

```bash
rpm -i <release archive>.rpm 
sudo systemctl enable datavirtuality.service
sudo systemctl start datavirtuality.service
```

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

### Example: Set SMTP Configuration for UTILS

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

An example demonstrating how to set SMTP configuration using the UTILS procedure. Note that 'fromAddr' is mandatory in practice but omitted here for brevity in the example.

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

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

### Example: Unzip to file directory

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

This example shows how to unzip an archive into the same directory as the archive file by providing an empty string for the output_folder.

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

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

### Create Optimization Job Examples

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

Examples demonstrating how to call the `createOptimizationJob` procedure with different sets of parameters, including optional ones.

```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)
```

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

### Setup CData Virtuality Service

Source: https://docs.datavirtuality.com/v4/data-virtuality-server-as-a-service

Reload the systemd daemon, enable the CData Virtuality service to start automatically on boot, and then start the service. This assumes the unit file has been saved to /etc/systemd/system/datavirtuality.service.

```bash
sudo systemctl daemon-reload
sudo systemctl enable datavirtuality.service
sudo systemctl start datavirtuality.service
```

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

### Unzip Procedure Examples

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

Provides practical examples of using the UNZIP procedure with and without specifying an output folder.

```APIDOC
## Unzip Procedure Examples

### Example 1: With indicated output folder
```sql
CALL "UTILS.unzip"(
    "file_datasource" => 'file',
    "file_name" => 'jboss-as-7.1.1.Final.zip',
    "output_folder" => '1'
);;
```

### Example 2: Without indicated output folder
```sql
CALL "UTILS.unzip"(
    "file_datasource" => 'file',
    "file_name" => 'jboss-as-7.1.1.Final.zip',
    "output_folder" => ''
);;
```
```

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

### Example: Set SMTP Configuration for SYSADMIN

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

An example demonstrating how to set SMTP configuration using the SYSADMIN procedure. Note that 'fromAddr' is mandatory in practice but omitted here for brevity in the example.

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

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

### GET SQL Query Example

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

Example of a GET request to execute a simple SQL query. The `sql` parameter is URL-encoded.

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

```json
[{"expr1":1}]
```

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

### Example: Unzip with specified output folder

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

This example demonstrates calling the unzip procedure when a specific output folder ('1') is provided.

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

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

### Query Endpoint with Pagination

Source: https://docs.datavirtuality.com/v4/pagination

This example demonstrates how to initiate pagination for a query. The `pagination=true` parameter activates this mode, and `limit` and `offset` control the batch size and starting point. The response includes headers for navigating through pages.

```APIDOC
## POST /rest/api/query?pagination=true

### Description
Initiates a paginated query. The first request requires `pagination=true`. Subsequent requests use `requestId`.

### Method
POST

### Endpoint
`http://<cdatavirtuality-server-address>:8080/rest/api/query?pagination=true&limit=3&offset=3`

### Request Body
- **sql** (string) - Required - The SQL query to execute.

### Request Example
```json
{
  "sql": "SELECT * FROM SYS.Tables"
}
```

### Response
#### Success Response (200)
- **Data**: Array of rows returned for the current page.
- **Headers**: 
  - `limit` (integer) - The number of rows per page.
  - `offset` (integer) - The starting offset for the current page.
  - `nextPage` (string) - URL to fetch the next page of results.
  - `prevPage` (string) - URL to fetch the previous page of results.
  - `requestId` (string) - Unique identifier for the pagination session.

### Response Example
```json
[
    ["datavirtuality","SYS","Keys","Table",null,true,false,"tid:2cb59cfd55db-0023a214-00000040",-1,null,true,false,4],
    ["datavirtuality","SYS","ProcedureParams","Table",null,true,false,"tid:2cb59cfd55db-ab347619-0000004c",-1,null,true,false,5],
    ["datavirtuality","SYS","Procedures","Table",null,true,false,"tid:2cb59cfd55db-8f29b420-0000005d",-1,null,true,false,6]
]
```

### Headers in response:
` limit = 3 `
` offset = 3 `
` nextPage = http://localhost:8080/rest/api/query?requestId=767913&limit=3&offset=6  `
` prevPage = http://localhost:8080/rest/api/query?requestId=767913&limit=3&offset=0  `
` requestId = 767913 `
```

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

### Starting a Job

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

The `startJob` procedure starts a job immediately by creating a schedule to run it.

```APIDOC
## Starting a Job

The `startJob` procedure is the quickest way to start a job. In the background, it creates a schedule to run the specified job immediately:

### `SYSADMIN.startJob`

#### Description
Starts a job immediately.

#### Parameters
* **id** (biginteger) - The ID of the job.
* **startDelay** (integer) - Delay in seconds before starting the job.
* **queueHandler** (string) - The queue handler to use.
* **uuid** (string) - The UUID of the job. Available since v4.1.
```

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

### Example Cache Hint with Memory Preference and TTL

Source: https://docs.datavirtuality.com/v4/cache-hint

This example demonstrates how to preferably store cached results in memory for 1 minute.

```sql
/*+ cache(pref_mem ttl:60000) */ SELECT COUNT(*) FROM (
    CALL "s3_no_path1.listFiles"(
        "pathAndPattern" => '/xxx/*'
    )
) AS a ;;
```

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

### Example: Set and Get Connector Schema Temporary Value (SQL)

Source: https://docs.datavirtuality.com/v4/connector-management

Demonstrates setting a temporary schema value using SYSADMIN.setSchemaTmpValue and then retrieving it with SYSADMIN.getSchemaTmpValue. Ensure the 'name' parameter matches between calls.

```SQL
CALL "SYSADMIN.setSchemaTmpValue"(
    "name" => 'asdas',
    "val" => CAST( {ts'1970-01-01 00:00:00.000'} AS timestamp )
);

SELECT * FROM ( CALL SYSADMIN.getSchemaTmpValue( 'asdas') )a ;
```

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

### Example FTP Connection

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

An example of creating an FTP connection with specific server details. Ensure to replace placeholders with your actual server information.

```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 => '') ;;
```

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

### Example SFTP Connection

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

An example of creating an SFTP connection with specific server details. Ensure to replace placeholders with your actual server information.

```SQL
CALL SYSADMIN.createConnection(name => 'sftp', jbossCLITemplateName => 'sftp', connectionOrResourceAdapterProperties => 'host=myserver,port=22, user=JohnDoe,password=JohnsSecretPassword') ;;
CALL SYSADMIN.createDataSource(name => 'sftp', translator => 'ufile', modelProperties => '', translatorProperties => '') ;;
```

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

### SQL: Pushdown Supported Example

Source: https://docs.datavirtuality.com/v4/function-pushdown

This example shows a query where the MD5 function can be pushed down to the data source.

```sql
SELECT
    md5("Name")
FROM
    "mssql_advworks_2019.AdventureWorks2019.HumanResources.Shift"
;;
```

```sql
SELECT md5(g_0.Name) FROM mssql_advworks_2019.AdventureWorks2019.HumanResources.Shift AS g_0
```

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

### Example: Create and Drop a View in SQL

Source: https://docs.datavirtuality.com/v4/drop-view

This example demonstrates creating a view and then dropping it. The `DROP VIEW` statement is used to remove the view.

```sql
CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;
DROP VIEW views.countryregion ;;
```

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

### SendMail Procedure Example with Attachments

Source: https://docs.datavirtuality.com/v4/sendmail

An example demonstrating how to send an email with multiple attachments using the sendMail procedure. It retrieves files using file.getFiles.

```SQL
CALL "UTILS.sendMail"(
    "Recipients" => 'example@example.org',
    "Subject" => 'Testmail',
    "Body" => 'Hello world.',
    "Attachments" => array(
        (SELECT file FROM (CALL "file.getFiles"() ) a WHERE a.filePath like 'currencyconvertor.asmx'),
        (SELECT file FROM (CALL "file.getFiles"() ) a WHERE a.filePath like 'currencyconvertor.wsdl')
    ),
    "AttachmentNames" => array('currencyconvertor.asmx', 'currencyconvertor.wsdl'),
    "AttachmentMimeTypes" => array('application/asmx', 'application/wsdl')
) ;;
```

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

### Example SCP Connection

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

An example of creating an SCP connection with specific server details. Ensure to replace placeholders with your actual server information.

```SQL
CALL SYSADMIN.createConnection(name => 'scp', jbossCLITemplateName => 'scp', cconnectionOrResourceAdapterProperties => 'host=myserver,port=22, user=JohnDoe,password=JohnsSecretPassword') ;;
CALL SYSADMIN.createDataSource(name => 'scp', translator => 'ufile', modelProperties => '', translatorProperties => '') ;;
```

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

### Example of Editing a Backup Job

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

An example demonstrating how to use the `SYSADMIN.editBackupJob` procedure with specific parameters. The `jobId` or `jobUuid` parameter must be specified.

```sql
CALL "SYSADMIN.editBackupJob"(
	"jobid" => 8,
    "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',)
);;
```

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

### Example ODBC Connection URL

Source: https://docs.datavirtuality.com/v4/configuration-of-odbc-clients

An example of a fully configured ODBC connection URL for Kerberos authentication, including specific server and port values.

```text
DRIVER={DataVirtuality Unicode(x64)};D6={gsslib=gssapi krbsrvname=DVServer};SSLmode=disable;PORT=35434;SERVER=DV01;DATABASE=datavirtuality
```

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

### Get Installed License Information

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

Retrieves detailed information about the currently installed license, including expiration date, trial status, holder, start date, consumer type and amount, and limit information. Use this to check license validity and scope.

```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;
```

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

### Sample boot.log Entries

Source: https://docs.datavirtuality.com/v4/logging-and-log-files

This snippet shows typical configuration and environment details found in the boot.log file, such as database connections, JAVA_HOME, runtime information, hostnames, and VM arguments.

```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 ...
```

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

### Examples for Setting Global Index Creation Policy (SQL)

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

These SQL CALL examples demonstrate setting the global index creation policy to NEW_ACCEPTED, ACCEPTED, and 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');;
```

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

### Export with Custom Configuration

Source: https://docs.datavirtuality.com/v4/data-virtuality-upgrade-utility-exporter

This example shows how to specify custom host, port, SSL settings, database name, and an output filename for the export.

```bash
export.bat --username admin --password admin --host localhost --port 31002 --ssl true --database dvbase-1 --file upgrade-server1-to-server2.sql
```

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

### Example Virtual Procedure Creation

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

This example demonstrates creating a virtual procedure named `dateaxis` that generates a date series between a start and end date. Note the use of double semicolons `;;` as a statement separator.

```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;;
```

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

### Example: All Catalogs Configuration

Source: https://docs.datavirtuality.com/v4/clickhouse

Shows how to configure the importer to use all available catalogs by setting the catalog property to '%'.

```sql
importer.catalog="%"
```

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

### Exclude Tables with Negative Lookahead

Source: https://docs.datavirtuality.com/v4/amazon-aurora-mysql-edition

Example of excluding all tables except those starting with 'public.br' or 'public.mk' using a negative lookahead in `importer.excludeTables`.

```sql
importer.excludeTables=(?!public\.(br|mk)).*
```

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

### Get Row Count with returnExtremeValue

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

This example demonstrates calling the returnExtremeValue procedure without specifying an incremental field. In this case, the procedure returns no data, and the count is 0.

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

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

### SYSADMIN.getInstalledLicense

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

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

```APIDOC
## SYSADMIN.getInstalledLicense

### Description
This procedure provides license information.

### Method
CALL

### Endpoint
SYSADMIN.getInstalledLicense

### Parameters
#### Output Parameters
- **expDate** (timestamp) - NOT NULL - 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 allowed by the license.
- **info** (string) - NOT NULL - Additional information about the license.
- **limitInfo** (string) - NOT NULL - Information about license limits.

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

### Response
#### Success Response (200)
- **expDate** (timestamp) - Expiration date of the license.
- **trial** (boolean) - Indicates if the license is a trial version.
- **holder** (string) - The name of the license holder.
- **startDate** (timestamp) - The start date of the license.
- **consumerType** (string) - The type of consumer the license is for.
- **consumerAmount** (integer) - The amount of consumers allowed by the license.
- **info** (string) - Additional information about the license.
- **limitInfo** (string) - Information about license limits.
```

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

### Include and Exclude Tables

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

When both include and exclude patterns are present, the include pattern is applied first, followed by the exclude pattern. This example includes tables starting with 'Account' but excludes 'AccountPartner'.

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

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

### Example: Multiple Catalog Configuration

Source: https://docs.datavirtuality.com/v4/clickhouse

Demonstrates how to specify multiple database catalogs or patterns for import. Wildcards and comma separation are supported.

```sql
importer.catalog="catalog1,catalog2,pattern1%,pattern2%"
```

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

### Windows: Run Simple Query

Source: https://docs.datavirtuality.com/v4/data-virtuality-command-line-client-dsql

Example of running a simple SQL query directly using DSQL on Windows. Ensure username and password are provided.

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

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

### Get Current Virtual Database Info (SQL)

Source: https://docs.datavirtuality.com/v4/virtual-database-management

Use this procedure to retrieve the ID, name, and version of the currently active virtual database. No specific setup is required beyond having access to the SYSADMIN schema.

```sql
SYSADMIN.getCurrentVdb(OUT id biginteger NOT NULL, OUT name string NOT NULL, OUT version biginteger NOT NULL)
```

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

### Call Procedure to Get Active Users Data

Source: https://docs.datavirtuality.com/v4/modular-google-analytics-data-connector

Execute a previously created procedure to retrieve specific analytics data. Ensure you provide the correct property ID, start date, and end date. Setting preview to true can be useful for testing.

```SQL
CALL analytics_data_ds.ActiveUsersInCountry(
    propertyId => <propertyId>,
    startDate => '2023-06-01',
    endDate => '2023-06-21',
    preview => true
);;
```

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

### Install CData Virtuality on Debian-based Distributions

Source: https://docs.datavirtuality.com/v4/data-virtuality-server-as-a-service

Install the CData Virtuality DEB package using dpkg. This command installs the server package.

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

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

### Configure and Set MySQL as Analytical Storage

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

This example demonstrates setting up a MySQL data source and then configuring it as the analytical storage. It includes creating a connection, defining the data source, setting it as the DWH, and finally verifying the configuration.

```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();;
```

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

### Start Resource Adapter Command

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

Adds an RA start line to the output script. This command is used to start a resource adapter with specified archive and class name.

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

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

### Create Installation Directory and Set Permissions

Source: https://docs.datavirtuality.com/v4/data-virtuality-server-as-a-service

Create the directory where CData Virtuality will be installed and ensure the dedicated user has ownership. This is a prerequisite for downloading and unpacking the server files.

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

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

### Create View and Convert to XML

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

This example demonstrates creating a sample view and then using the tableToXml procedure to convert it into an XML format. This is useful for testing or generating XML from small datasets.

```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: Create a Custom Backup Job

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

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

```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 pyodbc for Python

Source: https://docs.datavirtuality.com/v4/python

Install the pyodbc module using pip. This module is used for connecting to databases via ODBC drivers. Note that Linux installations may require additional components for compilation.

```bash
$ pip install pyodbc
```

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

### Create and Alter View Examples

Source: https://docs.datavirtuality.com/v4/alter-view

Demonstrates creating a view and then altering it to change its definition, including adding column specifications and foreign key constraints.

```SQL
CREATE VIEW views.creditcard AS SELECT * FROM adventureworks.creditcard;
```

```SQL
ALTER VIEW views.creditcard AS SELECT * from (SELECT * FROM adventureworks.creditcard) a;
```

```SQL
ALTER VIEW views.creditcard(a integer, b integer, PRIMARY KEY(a)) AS SELECT * FROM adventureworks.creditcard;
```

```SQL
ALTER VIEW views.userOrder(userId INTEGER, userName INTEGER, orderId INTEGER, FOREIGN KEY(orderId) REFERENCES views.orders(orderId)) AS
SELECT u.userId, u.userName, o.orderId
FROM views.users u
JOIN views.orders o ON u.userId = o.userId;;
```

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

### Install missing dependencies and CData Virtuality ODBC Driver on RedHat/CentOS

Source: https://docs.datavirtuality.com/v4/installing-data-virtuality-odbc-linux

Install necessary dependencies like libtool-ltdl and unixODBC64-libs before installing the CData Virtuality ODBC Driver RPM on RedHat or CentOS systems.

```bash
$ yum install libtool-ltdl unixODBC64-libs
$ rpm -i dv-odbc-<version>-<architecture>.rpm
```

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

### Start Embedded PostgreSQL (Linux)

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

Use this command to manually start the embedded PostgreSQL service on Linux.

```shell
dvserver/pgsql/embeddedpg_start.sh
```

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

### Start Embedded PostgreSQL (Windows)

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

Use this command to manually start the embedded PostgreSQL service on Windows.

```batch
dvserver\pgsql\embeddedpg_start.bat
```

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

### SQL OFFSET and FETCH FIRST Examples

Source: https://docs.datavirtuality.com/v4/limit-clause

Shows how to use the SQL 2008 OFFSET and FETCH FIRST clauses to skip a specified number of rows and then retrieve a limited number of subsequent rows.

```sql
OFFSET 500 ROWS
```

```sql
OFFSET 500 ROWS FETCH NEXT 100 ROWS ONLY
```

```sql
FETCH FIRST ROW ONLY
```

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

### Full psycopg2 Connection Example

Source: https://docs.datavirtuality.com/v4/python

A comprehensive example demonstrating how to construct a psycopg2 connection string and establish a connection to the Data Virtuality Server. All server details and credentials are defined as variables.

```python
import psycopg2 

# Data Virtuality Server Details
host = "localhost"
port = "35433"
database = "datavirtuality"
sslmode = "require"
uid = "admin"
pwd = "admin"

# psycopg2 connection string
con_string = "dbname={} user={} host={} password={} port={} sslmode={}".format(database, uid, host, pwd, port, sslmode)

# Establish connection
con = psycopg2.connect(con_string)
cur = con.cursor()
cur.execute("select * from <schema>.<table>")
cur.fetchall()
```

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

### Example Boolean Criteria

Source: https://docs.datavirtuality.com/v4/criteria

Examples of valid boolean criteria, including comparisons, arithmetic, and string functions.

```SQL
(balance > 2500.0)
```

```SQL
100*(50 - x)/(25 - y) > z
```

```SQL
concat(areaCode,concat('-',phone)) LIKE '314%1'
```

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

### Install psycopg2-binary

Source: https://docs.datavirtuality.com/v4/python

Installs the psycopg2-binary package for development and testing purposes. This package does not require a compiler or external libraries.

```bash
pip install psycopg2-binary
```

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

### SQL Logical Plan Example

Source: https://docs.datavirtuality.com/v4/query-planner

Illustrates the logical plan generated for a simple SQL select statement, showing the mapping of SQL clauses to plan nodes.

```sql
Project(groups=[anon_grp0], props={PROJECT_COLS=[anon_grp0.agg0 AS expr1]})
 Group(groups=[anon_grp0], props={SYMBOL_MAP={anon_grp0.agg0=MAX(g1.e1)}})
  Select(groups=[g1], props={SELECT_CRITERIA=e2 = 1})
   Source(groups=[g1])
```

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

### Linux: Run Simple Query

Source: https://docs.datavirtuality.com/v4/data-virtuality-command-line-client-dsql

Example of running a simple SQL query directly using DSQL on Linux. Ensure username and password are provided.

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

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

### Prepare Target Table from View

Source: https://docs.datavirtuality.com/v4/preparetargettable

This example demonstrates preparing a target table from an existing view. The `isSourceATable` parameter should be set to TRUE.

```SQL
CREATE VIEW views.v1 AS 
	SELECT * FROM (CALL views.p1()) x;;

CALL UTILS.prepareTargetTable(
    sourceObject => 'views.v1',
    isSourceATable => TRUE,
    target_table => 'dwh.TableBasedOnViewsV1',
    preview => FALSE,
    cleanupMethod => 'DROP'
);;
```

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

### SQL: Pushdown NOT Supported Example

Source: https://docs.datavirtuality.com/v4/function-pushdown

This example illustrates a query where the MD5 function cannot be pushed down, requiring internal implementation.

```sql
SELECT
    md5("salespersonid")
FROM
    "no_pushdown.SalesOrderHeader_ALL"
;;
```

```sql
md5(no_pushdown.SalesOrderHeader_All.salespersonid)
```

```sql
SELECT no_pushdown.SalesOrderHeader_All.salespersonid FROM no_pushdown.SalesOrderHeader_All
```

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

### Install unixODBC on RedHat/CentOS

Source: https://docs.datavirtuality.com/v4/installing-data-virtuality-odbc-linux

Install the unixODBC driver manager on RedHat or CentOS systems using the yum package manager.

```bash
$ yum install unixODBC
```

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

### OpenID Connect Export via Tokens

Source: https://docs.datavirtuality.com/v4/data-virtuality-upgrade-utility-exporter

This example shows how to authenticate using OpenID Connect with an access token and refresh token. Ensure all required OpenID Connect parameters and tokens are correctly provided.

```bash
export.bat --host localhost --auth-type OPENID --configuration-url http://localhost:8080/realms/oauth/.well-known/openid-configuration --client-id oauth1 --client-secret 1FrHGnl1cdeNxUYzpKtaALk11pTeF11a --access-token dDo4MTgwIl0sInJlc291cmNlX2FjY2VzcyI6eyJvZGF0YTQtb2F1dGgiOnsicm9sZXMiOlsiYWRtaW4tcm9sZSJdfX0sInNjb3BlIjoiZW1haWwgcHJvZmlsZSIsInNpZCI6 --refresh-token CwianRpIjoiMDgzZjYzZjItYTcwMy00ZjE4LTg2N2EtNjJiYWY0ZWU1ZDVkIiwiaXNzIjoiaHR0cDovL2xvY2FsaG9zdDo4MDgwL3JlYWxtcy9vZGF0YS1vYXV0aCIsImF1ZCI6Imh0dHA6Ly8
```

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

### Install unixODBC on Debian/Ubuntu

Source: https://docs.datavirtuality.com/v4/installing-data-virtuality-odbc-linux

Install the unixODBC driver manager on Debian or Ubuntu-based systems using the apt-get package manager.

```bash
$ apt-get install unixodbc
```

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

### Create View and Convert to JSON

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

This example demonstrates creating a sample view and then using the tableToJson procedure to convert it into a JSON format. This procedure can consume significant memory, so avoid using it on very large tables.

```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'
);;
```

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

### Check if unixODBC is installed

Source: https://docs.datavirtuality.com/v4/installing-data-virtuality-odbc-linux

Verify if the unixODBC driver manager is already installed on your Linux system by checking for the 'isql' utility.

```bash
$ which isql
```

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

### Prepare Target Table with DELETE Cleanup

Source: https://docs.datavirtuality.com/v4/preparetargettable

This example demonstrates preparing a target table using the 'DELETE' cleanup method. It first inserts data and then calls prepareTargetTable to ensure the table is ready for further operations.

```SQL
INSERT INTO dwh.TestIncremental Values (1, 15);;
INSERT INTO dwh.TestIncremental Values (2, 25);;
 
CALL UTILS.prepareTargetTable(
    sourceObject => 'views.p3',
    isSourceATable => FALSE,
    target_table => 'dwh.TestIncremental',
    preview => FALSE,
    incrementalField => 'incrementalValue',
    cleanupMethod => 'DELETE'    
);;
```

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

### Start a Job Immediately

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

This procedure starts a job immediately by creating a schedule. The 'uuid' parameter is available since v4.1.

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