extensibility

Version: 13.00.00.00 - Created: 15 Jul 2015

The XML Data type, introduced in Teradata 14.10, provides the following new capabilities: •A new XML data type that allows you to store XML content in a compact binary form that preserves the information set of the XML document, including the hierarchy information and type information derived from XML validation •Functions and methods on the XML type that support common XML operations like parsing, validation, transformation (XSLT) and Query (XPath and XQuery) •Supports the XQuery query language for querying and transforming XML content •Supports the following SQL/XML functions and stored procedures:     •XMLDOCUMENT •XMLELEMENT •XMLFOREST •XMLCONCAT •XMLCOMMENT •XMLPI •XMLTEXT •XMLAGG •XMLPARSE •XMLVALIDATE •XMLQUERY •XMLSERIALIZE •XMLTABLE •AS_SHRED_BATCH •XMLPUBLISH •XMLPUBLISH_STREAM Benefits •Removes the requirement to map between hierarchical and relational models prior to storing the XML contents. Previously, this was required when using the XML shredding and publishing functionality in Teradata XML Services. •Provides the ability to preserve document identity. In contrast, the shredding facility that Teradata XML Services supports only extracts the values out of the XML document without retaining the document identity. •Provides a compact method for storing XML content where the internal representation is 5~10 times smaller than the original text representation. •Provides standard query language support for querying XML using XQuery 1.0 and XPath 2.0. •Provides a more efficient method for parsing, transforming and querying XML content. •Integrates the functionality in the Teradata XML Services offering into Teradata Database using syntax that conforms to the ANSI SQL/XML specification.   Considerations •The XML type accommodates values up to 2GB in size. However, operations like XSLT and XQuery, which load documents into memory, are only supported on documents that are smaller in size, where the processing operation does not require more memory than specified by the XML_MemoryLimit DBS Control field. This field caps the amount of memory available for XML operations so that these operations do not strain memory resources. A pseudo-streaming version of XQuery is allowed on large documents via the XMLEXTRACT method on the XML type. •XML path indexes are not supported. •XML schemas and XSLT stylesheets are not managed by Teradata Database.   Schema and Stylesheet Consolidation Utility The Schema and Stylesheet consolidation utility  is available for download as a zip file on this page. There is one significant change in this utility compared to the user documentation. The user documentation speaks of two executables ConsolidateSchema.exe and ConsolidateStylesheet.exe. In this version of the utility the two have been combined into a single executable csldgen.exe. The Readme.txt file included in the zip file gives more details regarding the need for schema and stylesheet consolidation and how to perform such consolidation. This utility is only relevant if your schemas and stylesheets use imports and includes.

Version: 13.00.00.00 - Created: 15 Oct 2014

Teradata XML Services provide assistance in database transformation of XML structures to and from relational structures. This is primarily an enterprise fit feature. XML in this context is regarded as a data format that is used to describe incoming or outgoing warehouse data. A key concept for this feature is that we are not transforming to store XML but rather to maintain a relational data model or to integrate relational data into an enterprise XML message structure! The relational data model is bested suited for enterprise analytics. XML structures are best suited for enterprise integration. Teradata XML Services is supported on Database Versions 13, 13.10 and 14. As of Teradata Database version 14.10, much of the XML Services functionality has been implemented as part of the XML data type in the database, and Teradata XML Services as a separate download will not be supported for 14.10 and future versions. Mappings created for XML shredding and publishing can be used for 14.10 as well (except for XSLT shredding),  but the names of the stored procedures will change. Please see the Teradata XML book in the database user documentation for further details. This feature will be delivered asynchronously from any specific Teradata warehouse release. The delivery format will be as a web download, available for each Teradata server platform. The feature is considered a part of the Teradata product and will be supported through normal support channels. Teradata XML Services consists of the following components: Xerces XML parser and Xalan XSLT transformer packaged as a platform specific operating system library. Shredding Framework which consists of a combination of stored procedures and functions. A stored procedure controls the shredding process. When shredding one to a few documents the stored procedure directly invokes the data maintenance DML. When shredding many documents, the stored procedure uses a set-based approach through the invocation of a table generation function. Parallel Publishing Framework which consists of a combination of stored procedures and functions. A stored procedure will control the publishing process. The stored procedure can either return a string representing an XML object type or a SQL statement that represents the XML data stream. The SQL statement can be reused in views, macros, fast export, etc. General purpose XSLT transformation function. Two XPATH search functions, one that returns a scalar character value and one that returns an XML fragment character value. XML schema validation function. XML Schema and Stylesheet loading and dependency resolution. XML schema generation procedures. Perl based installation process. For community-based support and to share your implementation ideas and concerns, please visit the Extensibility forum.

Version: 13.10.00.00 - Created: 13 Feb 2012

Teradata 13.10 features Block Level Compression (BLC), which provides the capability to perform compression on whole data blocks at the file system level before the data blocks are actually written to storage. Like any compression features, BLC helps save space and reduce I/O. This BLC utility is for Teradata users to run against TD 13.10 system to select the list of BLC candidate tables and evaluate BLC impact on space and speed for each specific table in interest, to get information for selecting appropriate tables to apply BLC. To learn how to use the BLC Utility package, please see the article Block Level Compression evaluation with the BLC utility.   For community support for this package, please visit the Extensibility Forum.

Version: 1.0.0.2 - Created: 12 Nov 2010

The ALC (ALgorithmic Compression) test package contains UDFs simulating TD13.10 built-in compression functions, test templates for Latin and Unicode character columns and step-by-step instructions. It is intended for TD users to run over specific data at column level to determine compression rates of TD 13.10 built-in compression algorithms. The test results provide information for selecting an appropriate algorithm for specific data. These tests use read-only operations and they can be executed on any release that supports UDFs (V2R6.2 & forward). It is recommended to run these tests off peak hours - they will use a significant amount of system resources (CPU bound). Usage To learn how to install and use the test package, please see Selecting an ALC compression algorithm. For community support for this package, please visit the Extensibility forum.

Version: 1.0 - Created: 07 Jun 2010

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.