Describing Catalog Elements¶

The DESC statement allows obtaining a description of the elements in the Virtual DataPort server.

Syntax of the statement DESC¶

DESC QUERYPLAN { <query> | <CREATE REMOTE TABLE> }

DESC CATALOG_DATABASE

DESC SESSION

DESC VQL ASSOCIATION <name:identifier with database>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL DATABASE [ <name:identifier> ]
    [ ( <DESC DATABASE parameters> [, <DESC DATABASE parameters> ]* ) ]

DESC VQL DATASOURCE <data source type> <name:identifier with database>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL FOLDER <path:literal>
    [ ( <DESC FOLDER parameter> [, <DESC FOLDER parameter> ]* ) ]

DESC VQL GLOBAL_SECURITY_POLICY <name:identifier>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL LIST <element DESC statement> [, <element DESC statement>]*
    [ ( <DESC LIST parameter> [, <DESC LIST parameter> ]* ) ]

DESC VQL LISTENER JMS <name:identifier with database >
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL MAP { I18N | SIMPLE } <name:identifier>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL PROCEDURE <name:identifier with database >
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL RESOURCE <resource type>
    [ VERSION = <literal> ] NAME = <literal>

DESC VQL ROLE <name:identifier>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL TAG <name:identifier>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL TYPE <name:identifier with database>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL USER <name:identifier>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL [ INTERFACE ] VIEW <name:identifier with database>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL VIEWSTATSUMMARY <view name:identifier with database>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL { REST | SOAP } WEBSERVICE <name:identifier with database>
    [ FOR ( <operation or resource name:literal>
    [, <operation or resource name:literal>]* )]
    [ PRESERVE_OPERATIONS ]
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

DESC VQL WRAPPER <wrapper type> <name:identifier with database>
    [ ( <DESC parameter> [, <DESC parameter> ]* ) ]

<data source type> ::=
    CUSTOM | DF | ESSBASE | JDBC | JSON | LDAP | MONGODB | ODBC | OLAP
    | SALESFORCE | SAPBWBAPI | SAPERP | WS | XML }

<element DESC statement> ::=
    ASSOCIATION <name:identifier with database>
    | DATABASE_CONFIG <name:identifier>
    | DATASOURCE <type:datasource type> <name:identifier with database>
    | FOLDER [<name:database>] <path:literal>
    | GLOBAL_SECURITY_POLICY <name:identifier>
    | INTERFACE VIEW <name:identifier with database>
    | LISTENER JMS <name:identifier with database>
    | PROCEDURE <name:identifier with database>
    | ROLE <name:identifier>
    | TAG <name:identifier>
    | USER <name:identifier>
    | VIEW <name:identifier with database> [ WITH STATS ]
    | { REST | SOAP } WEBSERVICE <name:identifier with database>
        [ FOR ( <operation or resource name:literal>
        [, <operation or resource name:literal>]* ) ]
        [ PRESERVE_OPERATIONS ]

<resource type> ::=
 { ESSBASE | JAR| JDBC | JDBC OTHER | KERBEROS | LIBRARY | SAPJCO }

<wrapper type> ::=
    <data source type>

<DESC FOLDER parameter> ::=
    'includeContents' = { 'yes' | 'no' }       // 'no' by default
  | <DESC parameter>

<DESC DATABASE parameters> ::=
  | 'includeEnvSpecificElements' = { 'yes' | 'no' }    // 'no' by default
  | 'includeNonEnvSpecificElements' = { 'yes' | 'no' } // 'no' by default
  | 'includeVCSConfiguration' = { 'yes' | 'no' }       // 'no' by default
  | <DESC parameter>

<DESC parameter> ::=
    'dropElements' = { 'yes' | 'no' }                    // 'yes' by default
  | 'replaceExistingElements' = { 'yes' | 'no' }         // 'no' by default
  | 'includeCreateDatabase' = { 'yes' | 'no' }           // 'no' by default
  | 'includeDependencies' = { 'yes' | 'no' }             // 'yes' by default
  | 'include_dependencies_from_all_databases' = { 'yes' | 'no' } // 'no' by default
  | 'includeDeployments' = { 'yes' | 'no' }              // 'no' by default
  | 'includeJars' = { 'yes' | 'no' }                     // 'no' by default
  | 'includeStatistics' = { 'yes' | 'no' }               // 'no' by default
  | 'includeUserPrivileges' = { 'yes' | 'no' }           // 'no' by default
  | 'exclude_database_elements' = { 'yes' | 'no' }       // 'no' by default
  | 'includeCreateWebService' = { 'yes' | 'no' }         // 'no' by default
  | 'includeProperties' = { 'yes' | 'no' }               // 'no' by default
  | 'exclude_jdbc_wrapper_properties' = { 'yes' | 'no' } // 'no' by default
  | 'format_vql' = { 'yes' | 'no' | 'force'}             // 'no' by default

<DESC LIST parameter> ::=
    'includeServerProperties' = { 'yes' | 'no' }       // 'no' by default
  | 'includeWebContainerProperties' = { 'yes' | 'no' } // 'no' by default
  | <DESC parameter>
  | <DESC FOLDER parameter>

<query> ::= (see Syntax of the SELECT statement)

<CREATE REMOTE TABLE> ::= (see Syntax of the CREATE REMOTE TABLE statement)

DESC QUERYPLAN <query> returns the execution plan that the execution engine will use to execute that query. If you add TRACE at the end of the query, the query will also return this information; with DESC QUERYPLAN you avoid having to run the query.

DESC CATALOG_DATABASE returns the parameters of the connection to the database that stores the metadata of Virtual DataPort. If you did not configure Virtual DataPort to store the metadata on an external database, this command returns the parameters of the connection to the embedded Derby database.

DESC SESSION returns the name of the database that the user is connected to, along with her login name.

The statements of the following group return the VQL of the elements of the catalog. If the element is a wrapper, a view, a Web service or an association, the statement also returns the statements needed to create the elements it depends on.

For example, DESC VQL VIEW V will return the statement required to create the view V and the statements needed to create the data types, wrappers, data sources, stored procedures and other views required to define the view V. If you are executing DESC VQL VIEW but you do not need the VQL statements of the elements that this view depends on, add the parameter includeDependencies.

E.g. DESC VQL VIEW V ('includeDependencies' = 'no') returns the sentence CREATE VIEW V ..., but not the sentences to creates the views that V depends on and its data sources.

The statement DESC VQL FOLDER returns the VQL statements to recreate the folder. If you add the option 'includeContents' = 'yes', the output also includes the statements that recreate the elements inside the folder, including other folders. If you also add the option 'includeDependencies' = 'yes', the output also includes the statements to recreate the dependencies of the elements inside the folder.

For example, if there is a folder F1 with a view V in it, the command DESC VQL FOLDER F1 ('includeDependencies' = 'yes', 'includeContents' = 'yes') returns the statement to create the folder, the statements to create the view V and all the statements to create the elements that V depends on, even if they are in different folders.

Additionally, you can include dependencies from all databases by setting the parameter include_dependencies_from_all_databases to ‘yes’. This will include dependencies that are in other databases.

For instance, suppose we have two databases, db1 and db2. In db1, there’s a view v1, and in db2, there’s a view v2 that is a projection of v1. When executing DESC VQL VIEW db2.v2 ('includeDependencies' = 'yes', 'include_dependencies_from_all_databases' = 'yes'), it will generate VQL containing an initial block where dependencies from other databases are orderly included. In this case, it will include the view v1. After completing this block, it will generate the VQL for the requested view, in this case, v2.

As a special case, it’s worth mentioning that for DESC VQL DATABASE where this property (include_dependencies_from_all_databases) is included, even if the dropElements property is specified in the properties, the database generation statements will be replaced by CREATE OR REPLACE.

DESC VQL LIST A, B will return the statements required to create the elements A and B. For example, DESC VQL LIST VIEW V, DATASOURCE JDBC D returns the sentences to create the view V, the data source D and their dependencies.

To the DESC VQL WEBSERVICE you can add the token FOR followed by the name of one or more operations or resources. If you do this, the result will be an ALTER REST WEBSERVICE statement or an ALTER SOAP WEBSERVICE statement that will allow you to add these operations or resources to an existing web service. If you want to generate a CREATE OR REPLACE statement that will keep the existent operations or resources if the web service already exists, you can simply add PRESERVE_OPERATIONS token to the DESC VQL WEBSERVICE statement.

With the parameter includeProperties = yes, the values of the parameters that depend on the environment are variables instead of the actual values. For example, with this property, in the CREATE DATASOURCE JDBC statements, the value of DATABASEURI will be something like ${databases.common_database.datasources.jdbc.oracle_product.DATABASEURI} instead of the URI of the database.

When you add includeProperties = yes, add 'exclude_jdbc_wrapper_properties' = 'yes' if you want the parameters CATALOGNAME and SCHEMANAME of the CREATE WRAPPER JDBC statements to contain the actual value instead of a variable.

When you export a database with the option 'includeCreateDatabase'='yes', the output contains the statements CREATE DATABASE and ALTER DATABASE so the database is created with the same configuration, including the VCS settings of the database. If you do not want to export the VCS settings, add 'includeVCSConfiguration'='no'.

By default, the statistics of views are only included in the result when you export the VQL of the entire server, nor when you export a database nor when you export a view. To include the statistics of the views in the result, add the option 'includeStatistics'='yes'.

format_vql: if yes, the DESC VQL command returns the VQL of the derived views formatted.

USER MANUALS

Describing Catalog Elements¶