16.20 - DBCExtension.GLOP_Change Stored Procedure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

Purpose

Use GLOP_Change to change the GLOP data and any of the following settings:
  • GLOP_Data for existing pages
  • Ageing time for read-only GLOP data
  • Disable field of GLOP data
  • Length of the GLOP data to map

You cannot use GLOP_Change to remove or add GLOP data to an existing set.

Definition

REPLACE PROCEDURE DBCExtension.GLOP_Change
   (IN Set_name     VARCHAR(257) CHARACTER SET UNICODE,
    IN Type_g       CHAR(2) CHARACTER SET LATIN,
    IN Dependency   CHAR(4) CHARACTER SET LATIN,
    IN Association  VARCHAR(257) CHARACTER SET UNICODE,
    IN Data Name    VARCHAR(128) CHARACTER SET UNICODE,
    IN Map_Index    BYTEINT,
    IN Disable      CHAR(1) CHARACTER SET LATIN,
    IN Ageing       INTEGER,
    IN Length       INTEGER,
    IN Page         INTEGER,
    IN Data         BLOB(2097088000)
   )

where:

Set_name
Name of a GLOP set or the name of a table, depending on the value of the Type_g argument.
If Type_g is…
  • not NULL, then Set_name is the name of the GLOP set to change. If the name is not fully qualified, the current default database is assumed. If the name does not exist, the system reports an error.
  • NULL, then Set_name is the name of a table containing the equivalent data in multiple rows of the GLOP set or sets to change. If the table is not located in the current default database, then the name must be fully qualified.

    All other arguments are ignored, so they should be set to NULL.

    For details on the definition that the table must have, see Usage Notes.

Type_g
Type of GLOP to change. The valid values are:
  • 'SY', meaning System GLOP.
  • 'RO', meaning Role GLOP.
  • 'US', meaning User GLOP.
  • 'SE', meaning Session GLOP.
  • 'TR', meaning Transaction GLOP.
  • 'RE', meaning Request GLOP.
  • 'XR', meaning External Routine GLOP.
  • NULL, which means the Set_name argument supplies a table name to contain the data.

For details on the meanings of the GLOP types, see GLOP Types.

Dependency
When the change to a particular GLOP becomes active in memory. It contains GLOP replace dependency information.

You must pass a value of NULL, which means no dependency, change immediately. Other values are reserved for future releases.

Association
Association for particular GLOP types.
If Type_g is…
  • 'RO', then the association is the role name.
  • 'US', then the association is the user name.
  • 'XR', then the association is the external routine name.

    If the external routine is not located in the current default database, the name must be fully qualified. For a UDF, the external routine name must be the specific name of the routine.

  • any other value, then the association is ignored and should be set to NULL.

If the association name does not exist in the database, the system reports an error.

Data_Name
Name of the GLOP data to change. If the name does not exist, the system reports an error.
Map_Index
Position in the map array the GLOP address is to be set in the external routine. Valid values are 0 to 7.
Disable
Whether to enable or disable the data. The valid values are:
  • 'D', which specifies to disable the data.
  • 'E', which specifies to enable the GLOP.
  • NULL, which specifies to not change the value.
Ageing
Whether to age a read-only GLOP out of memory when not mapped for the given number of minutes. The valid values are:
  • > 0, which specifies to age the GLOP out of memory when not mapped for the given number of minutes.
  • 0, which specifies to not age the GLOP out of memory.
  • NULL, which specifies to not change the value.
Length
Mapped length of the GLOP data, independent of the actual length of the data. If the data is longer, it will be truncated when mapped. If the data is shorter, the remaining length will be zeroed when mapped. The valid values are:
  • > -1, which specifies to set the length to the specified value.
  • NULL, which specifies to retain the current length.
  • -1, which specifies to set the length to the size of the largest GLOP page for the set ID, set type, and map index.
Page
Number of the GLOP page to be changed. If the value is NULL, no data will be changed. If the GLOP page does not exist, the system reports an error. This value is only needed for read-only GLOP data; however, a value of 1 can be specified for read/write or globally modifiable GLOP data.
Data
Actual data to be changed. If this value is NULL, the data is not changed. If a zero length byte string is passed, the data is deleted but the GLOP page will be retained. The data can be specified either directly or it can be generated by specifying a UDF that returns a BLOB to this argument.

Authorization

An administrator who is changing GLOP data for many users must have the UPDATE privilege on the GLOP_Set, GLOP_Map and GLOP_Data tables (in the DBCExtension database). The Set_Name argument must be fully qualified to include the database the GLOP set is being changed for.

To change your created GLOP sets when you do not have the UPDATE privilege on the GLOP_Set, GLOP_Map and GLOP_Data tables (in the DBCExtension database), you must:
  • Have the GLOP or DROP GLOP privilege on the database for which the GLOP is being changed.
  • Be the creator or owner of the GLOP set and have the GLOP MEMBER privilege.

To change an 'RO', 'US' or 'XR' GLOP the current authorization must at least own or have one privilege on the respective role, user, or external routine.

If the table name containing the GLOP set information is being passed in the Set_name argument the user running the procedure must have SELECT privileges to the specified table.

Usage Notes

Use this procedure to change the attributes or data of a GLOP set. Do not use it to remove or add additional GLOP data to the set. The changes cause all changed GLOP data to be replaced in memory.

If the Type_g argument is NULL, the Set_name argument must specify a table name that contains the data to be changed in the GLOP set. The table must have the following definition. It can be a temporary table.

CREATE TABLE  GLOP_Change_Table
   (Sequence     INTEGER NOT NULL,
    Set_name     VARCHAR(257) CHARACTER SET UNICODE,
    Type_g       CHAR(2) CHARACTER SET LATIN,
    Dependency   CHAR(4) CHARACTER SET LATIN,
    Association  VARCHAR(128) CHARACTER SET UNICODE,
    Data_Name    VARCHAR(128) CHARACTER SET UNICODE,
    Map_Index    BYTEINT,
    Disable      CHAR(1) CHARACTER SET LATIN,
    Ageing       INTEGER,
    Length       INTEGER,
    Page         INTEGER,
    Data         BLOB(2097088000))
PRIMARY INDEX (Sequence);

The descriptions of the defined columns are identical to the stored procedure arguments and have the same name. The only additional column is the Sequence column that must contain a number increasing in the order the data is to be applied. The system reports an error if the sequence does not increase.

Changes do not become permanent until there is a change in the Set_name or the last row has been processed. It works this way so that dependency information or index mappings can be applied at once to prevent a partial change from causing GLOP mapping to take place at the wrong time. However, all changes are rolled back when there is an error.

Restrictions

Do not call GLOP_Change from within a transaction because it creates its own transaction, possibly more than one, and might execute DDL statements.

The procedure is designed to run from a Teradata mode session.

Example: DBCExtension.GLOP_Change Stored Procedure

This simple example updates an 'Engineering' set GLOP with new update aluminum stress/strength data, for various alloys of aluminum. The data already exists; it is simply being replaced with newer data. The data is needed by the 'Stress01' UDF, which uses the data to do structural analysis on an engineering project. This is read-only GLOP data and it can simply be replaced for the next transaction when 'Stress01' is called.

CALL DBCExtension.GLOP_Change(
   'Engineering',             -- Set name
   'XR',                      -- Type: UDF
   NULL,                      -- No dependency
   'Stress01',                -- UDF that uses data
   'Aluminum_Stress_Tables',  -- the name of GLOP
   NULL,                      -- No change to map index
   NULL,                      -- No change to disable setting
   NULL,                      -- Do not change ageing value
   -1,                        -- Set GLOP length to data size
   1,                         -- Change page 1
   Engin_Strs_Gen('Al')       -- UDF to create the stress data
   );