USER MANUALS

Modifying a Base View

You can change the following properties of a base view, using the statement ALTER TABLE:

  • Its internationalization configuration.

  • Its cache configuration (CACHE). If PARTIAL or FULL, the tuples extracted from the source as a result of executing the queries are stored in the local cache. The section Using the Cache explains how each cache mode works.

  • BATCHSIZEINCACHE. The rows obtained from the source are inserted in cache in batches. This parameter determines the number of rows per batch. The value DEFAULT is the Batch size defined for the database in the “Database Management” dialog (menu “Administration”). If the database does not have a specific Batch size, the cache module uses the value defined in the “Cache” dialog (“Administration > Server configuration” menu).

  • Its memory usage configuration (SWAP, SWAPSIZE and MAXRESULTSIZE). That is, the “swapping to disk” policy for queries that use the base view and involve a large number of tuples. See section Configuring Swapping Policies for more details.

  • The commands CREATE CACHE INDEX and DECLARE CACHE INDEX do exactly the same. Both create a cache index. See more about this in the section Cache Indexes of the Administration Guide.

  • DELETE CACHE INDEX deletes a cache index from the underlying data source. See more about this in the section Cache Indexes of the Administration Guide.

  • DECLARE VIEW INDEX defines a view index in VDP that already exists in the data source. CREATE VIEW INDEX creates a view index in the underlying data source. See more about this in the section Indexes of Views of the Administration Guide.

  • DELETE VIEW INDEX deletes a view index defined in VDP. DELETE REMOTE VIEW INDEX deletes a view index from the underlying data source and from VDP. See more about this in the section Indexes of Views of the Administration Guide.

  • Add, delete or modify a search method. Search methods are composed of rules that represent the restrictions with which a specific query should comply in order to be executed using this search method. Furthermore, each search method has an associated wrapper which contains the data necessary to translate the user query for the source and interpret its response. The section Assigning Wrappers to Search Methods provides more details on this matter.

  • DELEGATESTATSQUERY: if false, the queries executed to gather the statistics of this base view are not pushed down to the source. Instead, Virtual DataPort retrieves all the data (executes a SELECT * FROM table) and executes the aggregation functions locally. If true or not present, the queries are pushed down to the source if possible.

    Setting this to false is equivalent to selecting the check box “Do not delegate the generation of the statistics” of the “Statistics” tab of the views” “Advanced” wizard.

    Find more information about this option in the section Gathering the Statistics of Views of the Administration Guide.

  • The clauses SMART_ONLY, SMART_THEN_ATSOURCE_THROUGH_VDP and ATSOURCE_THROUGH_VDP_ONLY control what the Server will do to obtain the statistics of this view:

    • SMART_ONLY: the Server will obtain the statistics of the view from the system tables of the database. The information that can be obtained from these tables depends on the database to which the data source connects, so not all the statistics may be obtained.

      Adding this clause is equivalent to clearing the check box “Complete missing statistics…” of the “Statistics” tab.

    • SMART_THEN_ATSOURCE_THROUGH_VDP: the Server will obtain the statistics from the system tables of the database. If a statistic cannot be obtained from the catalog, a regular SELECT query will be executed to obtain it.

      Adding this clause is equivalent to selecting the check box “Complete missing statistics…” of the “Statistics” tab.

    • ATSOURCE_THROUGH_VDP_ONLY: the Server will not try to obtain any statistic from the system tables of the database. Instead, the Server will execute a regular SELECT query to obtain these statistics.

      This option is not available from the Administration Tool. It can only be set via VQL.

    Find more information about this option in the section Gathering the Statistics of Views of the Administration Guide.

  • Rename the view: ALTER TABLE <name> RENAME...

  • Add, change or remove the primary key of the base view (<add key> and <drop key> definitions). See more information about primary keys in the section Primary Keys of Views of the Administration Guide.

  • Modify the columns of the base view:

    • To rename a column, use ALTER COLUMN <current field name:identifier>  RENAME <new field name:identifier>

      For example:

      ALTER TABLE internet_inc (
    ALTER COLUMN iinc_id RENAME incidence_id
);

    • To modify the type of a column, use ALTER COLUMN <name:identifier> MODIFY <new type:identifier> <is field nullable>.

      When you change the type of a field, the current source type properties of the field get lost. If you need them, you have to add them again.

      For example:

      ALTER TABLE internet_inc (
    ALTER COLUMN incidence_id MODIFY decimal FALSE
);

    • To add or modify a source type property of a field, use ALTER COLUMN <field name:identifier> ADD ( <source type    property:identifier> = <source type value:literal> )

      If the property “source type property” already exists, the value provided replaces the existing one.

      For example:

      ALTER TABLE internet_inc (
    ALTER COLUMN summary ADD ( sourcetypesize = '1000')
);

    • To add or modify the description of a field, use

      ALTER COLUMN <field name:identifier> ADD ( DESCRIPTION = <description:literal> )

      For example:

      ALTER TABLE internet_inc (
    ALTER COLUMN iinc_id ADD ( DESCRIPTION = 'Identifier of the incident' )
);
Syntax of the statement ALTER TABLE
ALTER TABLE <name:identifier>
  [ I18N <name:identifier> ]
  [ CACHE {
      OFF
    | PARTIAL [ EXACT ] [ PRELOAD ]
    | FULL [ INCREMENTAL <incremental_condition:literal> ]
    | INVALIDATE [ ON CASCADE ]
      [ NOATOMIC [ INVALIDATEBLOCKSIZE <integer> ] ]
      [ WHERE <condition> ]
    }
  ]
  [ BATCHSIZEINCACHE { <integer> | DEFAULT } ]
  [ TIMETOLIVEINCACHE { <seconds:integer> | DEFAULT | NOEXPIRE } ]
  [ SWAP { ON | OFF | DEFAULT} ]
  [ SWAPSIZE <megabytes:integer> ]
  [ MAXRESULTSIZE <megabytes:integer> ]
  [ <table index clause> ]*
  [ <table search method clause> ]*
  [ { <add key> | <drop key> }]
  [ DELEGATESTATSQUERY = <boolean> ]
  [ {
          SMART_ONLY
        | SMART_THEN_ATSOURCE_THROUGH_VDP
        | ATSOURCE_THROUGH_VDP_ONLY
    } ]
  [ DESCRIPTION = <literal> ]
| ALTER TABLE <name:identifier> ( <alter column clause>+ )
| ALTER TABLE <name:identifier> RENAME <new_name:identifier>

<table index clause> ::=
      DECLARE { CACHE | VIEW } [ CLUSTER | HASH | OTHER ]
          INDEX <index_name:identifier> [ PRIMARY ] [ UNIQUE ]
          ON ( <table index field [ ,<table index field> ]* )
    | CREATE  [ OR REPLACE ] { CACHE | VIEW }
          INDEX <index_name:identifier> [ UNIQUE ]
          ON ( <table index field> [ ,<table index field> ]* )
    | DELETE CACHE INDEX <index_name:identifier>
    | DELETE [ REMOTE ] VIEW INDEX <index_name:identifier>

<table index field> :: = <index_field_name:identifier> [ ASC | DESC ]

<table search method clause> ::=
    ADD SEARCHMETHOD <name:identifier> (
        [ I18N <name:identifier> ]
        [ CONSTRAINTS ( [ <constraint clause> ]+ ) ]
        [ OUTPUTLIST ( <output clause> ) ]
        [ <wrapper clause> ]
       )
  | ALTER SEARCHMETHOD <name:identifier> (
        [ I18N { <name:identifier> | DEFAULT } ]
        [ CONSTRAINTS ( [ <constraint clause> ]+ ) ]
        [ OUTPUTLIST ( <output clause> ) ]
        [ <wrapper clause> ]
       )
  | DROP SEARCHMETHOD <name:identifier>

<alter column clause> ::=
    {   ALTER COLUMN <name:identifier> RENAME <new name:identifier>
      | ALTER COLUMN <name:identifier> MODIFY <new type:identifier>
            [ <nullable clause> ]
      | ALTER COLUMN <field name:identifier> ADD (
            <source type property:identifier> = <source type value:literal>
        )
      | ALTER COLUMN <field name:identifier> ADD (
            DESCRIPTION = <description:literal> )
    }

<nullable clause> ::= { TRUE | FALSE }

<constraint clause> ::=
    ADD <field> ( [ <operator> [, <operator> ]* ] )
        {
           <obligatoriness> <multiplicity>
                            [ ( <value_1:value> [ , <value_i:value> ]* ) ]
         |
           NOS { ZERO | 0 } ()
        }
  | DROP <integer>

<output clause> ::=
  <field> [ ,<field> ]*

<wrapper clause> ::=
    WRAPPER ( <wrapper type> <name:identifier> )
    ALTERNATIVE_WRAPPERS ( JDBC <wrapper_name:identifier> [, JDBC <wrapper_name:identifier> ]* ) ]
  | DROP WRAPPER

<wrapper type> ::=
    ARN
  | CUSTOM
  | DF
  | ESSBASE
  | GS
  | ITP
  | JDBC
  | JSON
  | LDAP
  | ODBC
  | SALESFORCE
  | SAPBWBAPI
  | SAPERP
  | WS
  | XML

<field> ::=
  <identifier>[.<identifier>]*

<obligatoriness> ::=
    OPT
  | OBL

<multiplicity> ::=
    ZERO
  | ONE
  | ANY
  | <integer>

<operator> includes “any” to represent any operator.

<add key> ::=
  ADD [ CONSTRAINT <name:literal> ]
  PRIMARY KEY ( <field_name:literal> [, <field_name:literal>]*)

<drop key> ::=
    DROP CONSTRAINT <name:literal>
  | DROP PRIMARY KEY

<condition clause> ::= (see Rules for forming functions) <query plan> ::= (see QUERYPLAN syntax)

Privileges Required to Create, Edit and Delete an Index in the underlying data source

The commands used to manage the underlying indexes require the following privileges:

  • “Connect” over the Virtual DataPort database of the JDBC data source used to create the base view.

  • “Execute” over the JDBC data source used to create the base view.

  • “Connect” over the Virtual DataPort database in which the base view is located.

  • “Write” over the base view in which the index will be created.

The user account set in the JDBC data source has to be able to create indexes in the underlying database.

Add feedback