New Features and Enhancements › Full Support for Teradata 13

Full Support for Teradata 13

Support for Teradata in CA ERwin Data Modeler has been enhanced for new features introduced in Teradata 13. Modeling capability for these features has been added to CA ERwin Data Modeler.

New and enhanced editors include:

• Replication Rule Set Editor - Added to allow you to define the rules for automatically assigning objects to a replication group.
• Statistics Editor - Added to allow you to collect demographic data for one or more columns of a table, hash index, or join index and compute a statistical profile of the collected data. This editor is accessible using the Statistics tab in either the Table Editor, Hash Index Editor, or Join Index Editor.
• Table Editor - Revised to add controls for Teradata 13 features, such as Replication Group.

New features supported include the following:

Combined Old/New Table Trigger Support

The REFERENCING clause in the CREATE TRIGGER and REPLACE TRIGGER statements supports an OLD_NEW_TABLE option that lets you specify a single transition table name to reference old and new values for rows modified by a triggering statement.

Column Level Grant/Revoke

Extends Teradata support for column-level privileges to the INSERT and SELECT privileges, in conformance with the ANSI SQL:2003 standard.

New Permissions

The following new permissions have been added for Teradata 13:

• CREATE OWNER PROCEDURE - On stored procedures
• CTCONTROL - On User
• SHOW - On Database, User, Table, Hash Index and Join Index
• STATISTICS - On Database, User, Table, Hash Index and Join Index

Dynamic User Defined Types

Supports dynamic UDTs as input parameters to scalar, aggregate, and table user-defined functions (UDFs) written in C or C++. It adds a new input parameter data type, the Variant_Type UDT, which can be used to pass in a varying number of parameters to a UDF, as well as parameters with varying data types. The number of parameters and the data types of the parameters are determined at runtime. They can be varied each time the routine is invoked.

Geospatial Data Types

New native SQL/MM data types address the need to store, manage, and analyze geospatial information: ST_Point, ST_LineString, ST_Polygon, ST_GeomCollection, ST_MultiPoint, ST_MultiLineString, and ST_MultiPolygon, all of which are modeled by the ST_Geometry user-defined type (UDT).

The native GeoSequence data type is also modeled by the ST_Geometry type and can contain tracking information in addition to geospatial information. All of the native data types support methods for performing geometric calculations.

Java Stored Procedure Answer Sets

The CREATE PROCEDURE and REPLACE PROCEDURE statements for Java external stored procedures support the DYNAMIC RESULT SETS clause, allowing Java external stored procedures to create dynamic result sets.

Result sets can be returned to both the stored procedure and its caller or client application. For stored procedures, this means that the DECLARE CURSOR statement supports the following new options:

• WITH RETURN TO CALLER
• WITH RETURN TO CLIENT

Java User Defined Functions

The CREATE FUNCTION, REPLACE FUNCTION, and ALTER FUNCTION statements have been extended to support Java UDFs.

Stored Procedure Access Rights

New stored procedure access rights let you define access rights checking for the underlying objects of the SQL statements in the procedure. You can specify a SQL Security option during the creation of the stored procedure. When the stored procedure is compiled or executed, access rights checking will be done based on the definition of the SQL Security option as follows:

• CREATOR
• DEFINER
• INVOKER
• OWNER

With the new CREATE OWNER PROCEDURE privilege, if the SQL Security option is OWNER, and you need to create a stored procedure in a database that you do not own, you must have the CREATE OWNER PROCEDURE privilege on that database.

User Defined Types as Parameters

Support is provided as follows:

• Using user-defined types (UDTs) as input parameters or as the result parameter of an aggregate user-defined function (UDF)
• Using UDTs as input parameters or result parameters of a table UDF

Statistics

CA ERwin Data Modeler supports the COLLECT STATISTICS statement (with the exception of the transfer statistics clause) that lets you collect demographic data for one or more columns of a table, hash index, or join index and compute a statistical profile of the collected data..

Data Types

Additional data types have been added for Teradata 13. These data types are included in the CA ERwin Data Modeler and the Data Type Standards Model (DSM):

• PERIOD(DATE)
• PERIOD(TIME[(n)])
• PERIOD(TIME[(n)] WITH TIME ZONE)
• PERIOD(TIMESTAMP[(n)])
• PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)
• MBR – Minimum bounding rectangle
• ST_Geometry

For PERIOD data types, a period is represented by a PERIOD data type value and indicates when a particular event starts and ends.

Note: If you convert a Teradata 13 model to another target server/version, the new data types will require mapping to a similar data type for the new target. The following mappings will be required:

• Period Data Types

  As this data type contains two date/time values, it will be mapped to a varchar data type on all other target servers. No other target server supported by CA ERwin Data Modeler has an equivalent data type.

• MBR

  This spatial data type will be converted to a varchar(256) data type when converting to any other target server/version other than SQL Server 2008. Since SQL Server 2008 supports spatial data types, it will convert to the GEOMETRY data type.

• ST_Geometry

  This spatial data type will be converted to a BLOB or equivalent data type on any other target server/version other than SQL Server 2008. Since SQL Server 2008 supports spatial data types, it will convert to the GEOMETRY data type.

Elements of the user interface have been updated:

• Teradata Roles Editor has been renamed the Teradata Database Roles Editor.
• Reverse engineering from catalog and script have been updated to include Teradata 13 objects and properties.
• Forward engineering has been updated for new Teradata 13 objects.

When you save an r8 Teradata 13 model as an r7.3.x model, a target server switch is made to convert the r7.3.x model to Teradata 12. If you save an r8 Teradata 13 model as an r7.2.x model, a target server switch is made to convert the r7.2.x model to Teradata 2.x.