Starting from TD 13.10, Teradata DIPUDT script creates the SQLRestrictedWords_TBF function and the SQLRestrictedWords view in the SYSLIB database that allow users and client products to query for Teradata SQL Restricted words.

For previous releases, we provide this package online for you to download and install the same table function and create view for the specific release of your server. Please start by reading the README (also included in the download package).

For community support for UDFs, please visit the Extensibility forum.

README

******************
*                *
*  Introduction  *
*                *
******************

Starting from TD 13.10, Teradata Database has a new feature that allows users 
and client products to query Teradata Restricted words. It requires running 
DIPUDT or DIPALL to get the feature enabled, i.e., to get a table UDF 
SYSLIB.SQLRestrictedWords_TBF installed and a view SYSLIB.SQLRestrictedWords 
created, and to grant appropriate access rights to users.

For previous releases, we provide this UDF package online for you to download 
and install the same table function and create view for the specific release of
your DB server. The following instructions guide you to do so step by step.


=================================================================================
Step 1 Install table function SYSLIB.SQLRestrictedWords_TBF()

    =============================================================================
    Step 1.1 Open the package and extract all files to c:/temp in your client 
        machine from which you will run bteq.


    =============================================================================
    Step 1.2 Go to folder c:/temp/udfrestrictedwords/, you will see folders for
        different operating systems: linux, mpras, win32 and win64; go to the 
        folder that is the same as your DB server OS, you will see the UDF 
        object file: udfsqlrestrcitedwords.o.


    =============================================================================
    Step 1.3 Start BTEQ from the above folder, logon as DBC and run the 
        following REPLACE FUNCTION script to install the table UDF.

        REPLACE FUNCTION SYSLIB.SQLRestrictedWords_TBF ()
        RETURNS TABLE ( restricted_word     VARCHAR(30) CHARACTER SET LATIN, 
                        release_introduced  CHAR(5) CHARACTER SET LATIN,
                        release_dropped     CHAR(5) CHARACTER SET LATIN,
                        category            CHAR(1) CHARACTER SET LATIN,
                        ANSI_restricted     CHAR(1) CHARACTER SET LATIN)
        LANGUAGE C
        NO SQL
        DETERMINISTIC
        PARAMETER STYLE SQL
        EXTERNAL NAME 'CO!udfsqlrestrictedwords!udfsqlrestrictedwords.o';

        You can ignore: "*** Warning: 5607 Check output for possible warnings 
        encountered in compiling and/or linking UDF/XSP/UDM/UDT."

    =============================================================================
    Step 1.4 Verify the table UDF is installed correctly.

        HELP FUNCTION SQLRestrictedWords_TBF ();

        sel count(*) from table (SQLRestrictedWords_TBF()) as t1; 

=================================================================================
Step 2 Create view SYSLIB.SQLRestrictedWords for specific release of your DB 
    server over table function SYSLIB.SQLRestrictedWords_TBF()

    If your server is of TD 13.0 release, run:

        CREATE VIEW SYSLIB.SQLRestrictedWords AS 
        SELECT restricted_word, category, ANSI_restricted
        FROM TABLE (SQLRestrictedWords_TBF()) as t1
        WHERE release_introduced <= '13.00' AND 
              (release_dropped IS NULL OR release_dropped > '13.00');

    If your server is of TD 12.0 release, run:

        CREATE VIEW SYSLIB.SQLRestrictedWords AS 
        SELECT restricted_word, category, ANSI_restricted
        FROM TABLE (SQLRestrictedWords_TBF()) as t1
        WHERE release_introduced <= '12.00' AND 
              (release_dropped IS NULL OR release_dropped > '12.00');

    If your server is of TD 6.2 release, run:
 
        CREATE VIEW SYSLIB.SQLRestrictedWords AS 
        SELECT restricted_word, category, ANSI_restricted
        FROM TABLE (SQLRestrictedWords_TBF()) as t1
        WHERE release_introduced <= '06.02' AND 
              (release_dropped IS NULL OR release_dropped > '06.02');

=================================================================================
Step 3 Verify view SYSLIB.SQLRestrictedWords is created correctly.


     show view SYSLIB.SQLRestrictedWords; 

     sel count(*) from SYSLIB.SQLRestrictedWords; 

=================================================================================
Step 4 Grant appropriate access rights to users

    grant execute function on syslib.SQLRestrictedWords_TBF to public;

    grant select on syslib.SQLRestrictedWords to public;

=================================================================================
Step 5 Logon as a non-dbc user, verify non-dbc users can execute the table 
    function and select from the view.

     HELP FUNCTION SQLRestrictedWords_TBF ();

     sel count(*) from table (SQLRestrictedWords_TBF()) as t1; 

     show view SYSLIB.SQLRestrictedWords; 

     sel count(*) from SYSLIB.SQLRestrictedWords; 

******************
*                *
*  Sample Usages *
*                *
******************

=================================================================================
1 Get the Restricted Words list specific for the your DBS version

    Use the view directly: SELECT * FROM SYSLIB.SQLRestrictedWords;

=================================================================================
2 Get the Restricted Words list for specific versions using the table UDF

   Use the table UDF SQLRestrictedWords_TBF with conditions for the specific 
   version in interest. For instances:

   ==============================================================================
   2.1 Get all restricted words in TD 13.10:

       SELECT * FROM TABLE (SQLRestrictedWords_TBF()) as t1
       WHERE release_introduced <= '13.10' AND 
             (release_dropped IS NULL OR release_dropped > '13.10');

   ==============================================================================
   2.2 Get all restricted words in TD 13.0:

       SELECT * FROM TABLE (SQLRestrictedWords_TBF()) as t1
       WHERE release_introduced <= '13.00' AND 
             (release_dropped IS NULL OR release_dropped > '13.00');

   ==============================================================================
   2.3 Get all restricted words in TD 12.0:

       SELECT * FROM TABLE (SQLRestrictedWords_TBF()) as t1
       WHERE release_introduced <= '12.00' AND 
             (release_dropped IS NULL OR release_dropped > '12.00');

   ==============================================================================
   2.4 Get all restricted words in TD 6.2:

       SELECT * FROM TABLE (SQLRestrictedWords_TBF()) as t1
       WHERE release_introduced <= '06.02' AND 
             (release_dropped IS NULL OR release_dropped > '06.02');

******************
*                *
*      NOTES     *
*                *
******************

=================================================================================
Note 1: If you want to remove the table UDF and the view, you can run the 
following from DBC:

     DROP FUNCTION SYSLIB.SQLRestrictedWords_TBF ();

     DROP view SYSLIB.SQLRestrictedWords; 

=================================================================================
Note 2: If you drop the table UDF after you complete all steps and then 
re-install it following Step 1, you will also need to complete all the 
steps 2 ~ 4 in order to get the view and the table UDF working properly for 
non-dbc users.