16.20 - DBCExtension.GLOP_Remove 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_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.

Definition

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
   )

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

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

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.
  • 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 will be removed) unless it is NULL, which means that 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.

Authorization

An administrator who is removing GLOP data for many 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 your own set when you do not have the DELETE privilege on the DBCExtension.GLOP_Set, DBCExtension.GLOP_Map, and DBCExtension.GLOP_Data tables, you must:
  • Have the GLOP or DROP GLOP privilege on the database from which the GLOP is being removed.
  • Be the creator or owner of the GLOP set and have the GLOP MEMBER privilege.

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.

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. In some cases, the supplied information will 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.

The procedure should not be called 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.

When all references to a specific GLOP data type are removed the data is deleted also. For a non shared 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 it is deleted.

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

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.

Example: DBCExtension.GLOP_Remove Stored Procedure

Remove the entries for the 'Auditor' role, which is no longer being used.

CALL DBCExtension.GLOP_Remove(
   'Accounting',   -- the accounting GLOP set
   'RO',           -- removing a role
   'Auditor',      -- role being removed from the set
   2,              -- mapped to index 2
   'T');           -- remove only specified type

If this is the last one, then execute the DROP GLOP SET statement:

DROP GLOP SET Accounting;