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

SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
qwr1571437338192.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1147
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Use GLOP_Add to add GLOP data and GLOP mappings to a GLOP set.

Definition

CREATE PROCEDURE DBCExtension.GLOP_Add
   (IN Set_name     VARCHAR(257) CHARACTER SET UNICODE,
    IN Type_g       CHAR(2) CHARACTER SET LATIN,
    IN Association  VARCHAR(257) CHARACTER SET UNICODE,
    IN Data_Name    VARCHAR(128) CHARACTER SET UNICODE,
    IN Shared       CHAR(1) CHARACTERS SET LATIN,
    IN Map_Index    BYTEINT,
    IN Map_Page     VARCHAR(1) CHARACTER SET LATIN,
    IN Mode_g       CHAR(1) CHARACTER SET LATIN,
    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 set including the database it is associated with. If the name is not fully qualified, the current default database is assumed. A set name must be a unique TVM name. The system reports an error if the set name is not associated with the GLOP set the user has access to.
  • NULL, then Set_name is the name of a table containing the equivalent data in multiple rows to build the GLOP set. If the table is not located in the current default database, then name must be fully qualified.

    All other arguments are ignored, so they can 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.
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 already exists for GLOP data or if the name does not exist in the database, the system reports an error.

Data_Name
Name assigned to the GLOP data for the given set database. An error is reported if the data name exists for the given database and the Data argument is not set to NULL.
Shared
GLOP mapping attributes, where the valid values are:
  • 'Y', meaning that the GLOP mapping is to be shared.

    A GLOP mapping can be shared between the GLOP types in the same set or other sets defined in the same database.

  • 'N', meaning that the GLOP mapping is not to be shared.

For details on GLOP mapping attributes, see GLOP Data Attributes.

Map_Index
Position in the map array the GLOP address is to be found in the external routine. Valid values are 0 to 7.
Map_Page
[for read-only GLOP data] Whether to make this the initial page to map for the index specified by the Map_Index argument. Valid values are:
  • 'Y', which specifies to make this the initial page to map for the index specified by the Map_Index argument.
  • 'N' or NULL, which specifies to not make this the initial page to map for the specified index.

If there is already a page that is mapped at this index, the system returns an error.

Mode_g
GLOP modification attribute. Valid values are:
  • 'R', which specifies that it is a read-only GLOP.

    By default, a read-only GLOP is always normal.

  • 'W', which specifies that it is a read/write GLOP.
  • 'M', which specifies that it is a read/write GLOP that can be globally modified.

For details on GLOP modification attributes, see GLOP Data Attributes.

Disable
One of the following values:
  • 'D', which specifies to disable the data.

    The GLOP page is unavailable until some future date.

  • 'E', which specifies to enable the GLOP.
Ageing
[for read-only GLOP data] Number of minutes to wait before ageing this page out of memory. The valid values are:
  • > 0, which specifies to age this page out of memory if this number of minutes elapses and the page is not mapped.
  • 0, which specifies not to age this page out of memory.
  • -1, which specifies to use the ageing value that was set for the initial page.

    If this is the initial page, the ageing value is set to 0.

Length
Mapped length of the GLOP being created here. This argument is not relevant if the GLOP named by Data_Name already exists. The rest of the description assumes the GLOP data is being created for the first time. The length field can be independent of the actual length of the supplied 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. A NULL will cause an error return for a new GLOP data. For read-only GLOP data, all pages have the same length.

A value of -1 for read-only GLOP data sets the length to the length of the biggest GLOP page. A value of -1 will for read/write or globally modifiable GLOP data sets the length to the length of the data. Data must be specified in that case.

Page
[for read-only GLOP data] Number of the GLOP page to be created. GLOP page numbers must be sequential, where the value of the initial page is 1.

If the GLOP mode is not read-only, this parameter is ignored. The system reports an error if a gap exists in the page number sequence or if the page already exists.

Data
Actual data to be loaded into the system table. The data is only supplied when the GLOP Data_Name is being added the very first time to a GLOP set. For all subsequent references via the Data_Name field, that use this data in another set or the same set, the field is set to NULL. The data can be specified either directly or it can be generated by specifying a UDF that returns a BLOB to this argument. The data can be NULL. If that is the case a GLOP specified by the Length parameter will be zeroed and mapped. Passing NULL for read-only GLOP data results in an error.

Authorization

An administrator who is adding GLOP data for many users must:
  • Have the INSERT privilege on the GLOP_Set, GLOP_Map, and GLOP_Data tables (in the DBCExtension database).
  • Include the database the set is being added for in the Set_Name argument.
  • Execute the CREATE GLOP SET statement for each new set being created prior to calling GLOP_Add.
To add to one of your granted and defined GLOP sets when you do not have the INSERT privilege on the GLOP_Set, GLOP_Map, and GLOP_Data tables (in the DBCExtension database), you must:
  • Have the GLOP or CREATE GLOP privilege on the database the GLOP is being added to.
  • Be the creator or owner of the GLOP set and have the GLOP MEMBER privilege.

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

If the Set_name argument specifies a table name containing the GLOP set information, the caller of 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 this procedure to define new GLOP data and its attributes and mapping for a GLOP set. It cannot be used to drop or replace existing data in the set. Call GLOP_Remove to drop GLOP data and GLOP_Change if data is to be changed. The procedure will expect the GLOP set to exist already in the database it is being created for, so issue a CREATE GLOP SET statement using the name of the new GLOP set to create, prior to calling this procedure the first time for the given GLOP set name.

All references to the same GLOP data must be associated with the same database. Referencing the same GLOP data in multiple sets is done by specifying the Data_Name and the Data the first time the GLOP is being added. For all subsequent referenced additions to the same data, the Data_Name must be supplied and the Data argument must be NULL. The first time the GLOP data is added determines what its contents are to be. If the GLOP has no data, setting the Data argument to NULL for the first GLOP add, will accomplish that feat.

All parameters are checked for valid combinations of data and consistency. In some cases, the supplied information is ignored. For example, an ageing value is ignored for read/write GLOP data since it has no meaning (but it causes no harm to supply a value). If there is an inconsistency in any of the parameter settings that are not called out specifically in the parameter section, the system reports an error.

Do not call this procedure 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.

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

CREATE TABLE GLOP_Add_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,
    Data_Name    VARCHAR(128) CHARACTER SET UNICODE,
    Shared       CHAR(1) CHARACTERS SET LATIN,
    Map_Index    BYTEINT,
    Map_Page     VARCHAR(1) CHARACTER SET LATIN,
    Mode_g       CHAR(1) CHARACTER SET LATIN,
    Disable      CHAR(1) CHARACTER SET LATIN,
    Ageing       INTEGER,
    Length       INTEGER,
    Page         INTEGER NOT NULL,
    Data         BLOB(2097088000))
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 which must contain a number increasing in the order the data is to be applied.. The data will be sorted in ascending order on Sequence. An error will be reported if the sequence number does not increase or there is a gap in the Page numbers, which must be in ascending order for multipage read-only GLOP data. More than one set can be added using this technique.

Example: DBCExtension.GLOP_Add Stored Procedure

Create an XML document containing parsing rules for PDF documents. This is the first time.

CREATE GLOP SET Web_XML_Document_Markup;

CALL DBCExtension.GLOP_Add(
   'Web_XML_Document_Markup',  -- the set name
   'SE',                       -- this is session GLOP data
   NULL,                       -- Not relevant for session GLOP data
   'PDF_Mode',                 -- named the GLOP data
   NULL,                       -- shared flag not relevant
   0,                          -- Put in map index 0
   'Y',                        -- Make this the initial map page
   'R',                        -- Read-only
   'E',                        -- Not disabled
   10,                         -- Ageing is 10
   -1,                         -- Set GLOP length to data length
   1,                          -- Creating page one
   Gen_XML_PDF_Markup(...));   -- a UDF creates the GLOP data

This is read-only GLOP data so it is not necessary to make it a session type of GLOP, but it might be useful for dependency changes in the future, making it easy to replace it on session boundaries.