DBCExtension.GLOP_Remove Stored Procedure | Teradata Vantage - DBCExtension.GLOP_Remove Stored Procedure - Analytics Database - Teradata Vantage

Use GLOP_Remove to delete an entire GLOP set or remove individual components from a GLOP set.

Before you can use the DROP GLOP SET statement to drop a GLOP set definition, you must use GLOP_Remove to delete the GLOP set and its data.

Required Privileges

An administrator who is removing GLOP data for multiple users must have the DELETE privilege on the DBCExtension.GLOP_Set, DBCExtension.GLOP_Map, and DBCExtension.GLOP_Data tables. The Set_Name argument must include the database the set is being removed for.

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

If the Set_name argument passes in a table name containing the GLOP set information, the user running the procedure must have the SELECT privilege on the specified table.

For details on GLOP privileges, see Teradata Vantage™ - SQL Data Control Language, B035-1149.

Syntax

REPLACE PROCEDURE DBCExtension.GLOP_Remove
   (IN Set_name     VARCHAR(257) CHARACTER SET UNICODE,
    IN Type_g       CHAR(2) CHARACTER SET LATIN,
    IN Association  VARCHAR(257) CHARACTER SET UNICODE,
    IN Map_Index    BYTEINT,
    IN Options      CHAR(1) CHARACTER SET LATIN
   )

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 set from which components are to be removed. If the name is not fully qualified, the current default database is assumed. If the name does not exist, the system reports an error.

If Type_g is NULL, then Set_name is the name of a table containing the equivalent data in multiple rows of the GLOP set or sets to remove. If the table is not located in the current default database, then the name must be fully qualified. Set other arguments (which are ignored) to NULL. See Usage Notes.

Type_g

Type of GLOP, where 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.

Association

Association for particular GLOP types. NULL is a valid value for these GLOP types if the Options argument has a value of 'T'.

Valid values of Type_g, with their associations, are the following:

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

Map_Index

Mapped index position of the GLOP to be removed.

If Type_g is 'RO', 'US', or 'XR', then the map index entry is set to NULL when the last one of this type is removed.

If Type_g is any other value, then the GLOP index entry is removed.

Options

How much to remove. The valid values are:

• 'A', which specifies to remove all GLOP set entries for the GLOP set.

  The value of the Type_g argument is ignored (because the entire set is removed) unless Type_g is NULL, which means the Set_name argument supplies a table name to contain the data.

• 'T', which specifies to remove a GLOP set entry of the designated type.

  If the value of the Type_g argument is 'RO', 'US', or 'XR', and the value of the Association argument is NULL, then all GLOP data of that type is removed for the given set. If the Association argument is not NULL, then that specific GLOP data is removed for the given set.

  Individual pages of a read-only GLOP cannot be removed.

Usage Notes

Use the GLOP_Remove procedure to remove an existing GLOP set either in whole or in parts. To permanently delete all traces of the entire GLOP set, use the DROP GLOP SET statement after calling the GLOP_Remove procedure.

All parameters are checked for valid combinations of data and consistency. The supplied information may be ignored. For example, when removing a 'SE' GLOP set, the Association argument has no meaning. If the parameters are incorrect or are used out of context, the system reports an error.

Do not call the procedure from within a transaction. The procedure creates its own transaction or transactions, which may run DDL statements. The procedure is designed to run from a Teradata mode session.

When all references to a specific GLOP data type are removed, the data is deleted also. For a nonshared GLOP, that occurs when the entry of the specific type is deleted from the GLOP set. For a shared GLOP, this occurs when the last GLOP set reference to the GLOP is deleted.

If the Type_g field is NULL the procedure expects the Set_name parameter to contain a table name that is to contain the data to be removed from the GLOP set. The table (which can be temporary) must have the following definition.

CREATE TABLE GLOP_Remove_Table    /* the name can be anything */
   (Sequence       INTEGER NOT NULL,
    Set_name       VARCHAR(257) CHARACTER SET UNICODE,
    Type_g         CHAR(2) CHARACTER SET LATIN,
    Association    VARCHAR(257) CHARACTER SET UNICODE,
    Map_Index      BYTEINT,
    Options        CHAR(1) CHARACTER SET LATIN)
PRIMARY INDEX (Sequence);

The descriptions of the defined columns are identical to the stored procedure version 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. If the sequence number does not increase, the system reports an error.