Teradata SQLRestrictedWords UDF Package

Downloads
Highlighted
Teradata Employee

Teradata SQLRestrictedWords UDF Package

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.

Tags (3)