DBCExtension.GLOP_Change Stored Procedure | Teradata Vantage - DBCExtension.GLOP_Change Stored Procedure - Advanced SQL Engine - Teradata Database

Required Privileges

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

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

Syntax

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

Syntax Elements

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.

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.

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.