Teradata SQL Data Definition Language (15.0)：详细教程与特许概述

需积分: 9 176 浏览量更新于2024-07-19 收藏 12.66MB PDF 举报

SQL Data Definition Language (SQL DDL)是Teradata数据库的核心组成部分，它在Teradata Database系统中扮演着定义、创建、修改和管理数据结构的关键角色。在Teradata的产品体系中，SQL DDL是实现高级数据仓库管理和分析能力的基础，如Active Data Warehousing和Active Enterprise Intelligence等。Teradata SQL DDL在Release 15.0中得到了显著提升，文档B035-1184-015K发布于2014年6月，体现了Teradata Corporation及其附属公司在数据库技术领域的专业知识和持续创新。 Teradata SQL DDL支持一系列功能，包括但不限于创建和管理表（如星型或雪花模式）、索引、视图、存储过程、触发器以及数据类型定义。这些操作使得用户能够灵活地设计和组织数据，以适应复杂的业务需求和数据分析场景。此外，Teradata还提供了一些独特特性，如SQL-MapReduce，它将SQL查询扩展到了大数据处理，允许用户利用Hadoop生态系统进行并行计算。 Teradata的其他知名产品，如Aster、BYNET、Claraview、DecisionCast、Gridscale、MyCommerce等，都是在强大的SQL DDL支持下实现高效数据处理和商业智能应用的。Teradata Decision Experts和Teradata Labs则代表了Teradata在前沿技术研发上的探索和实践，不断推动数据库技术的发展。同时，Teradata SQL DDL遵循了相关的知识产权法规，比如Apache、Hadoop、Mac、OS X等商标分别属于Apache Software Foundation、Apple Inc.等公司，确保了技术的合规性和兼容性。例如，Teradata与Adaptec和AMDOpteron等供应商合作，集成他们的技术，以提供全面的数据解决方案。在使用Teradata SQL DDL时，用户需要注意的是，所有提及的产品和服务都是受版权保护的，仅限于Teradata Corporation及其关联公司的授权许可范围内。通过了解和遵循这些规定，开发人员和管理员可以充分利用Teradata的强大功能，为企业的决策制定和业务增长提供强大而灵活的数据基础设施。

Chapter 1: ALTER CONSTRAINT - COMMENT

ALTER FUNCTION (External Form)

16 SQL Data Definition Language Detailed Topics

The following table summarizes how the UDF protection mode options should be used:

Special Considerations for Java UDFs

Java UDFs can only be run in EXECUTE PROTECTED mode. If you specify EXECUTE NOT

PROTECTED for a Java UDF, the request aborts and returns an error message to the

requestor.

For Java UDFs, the COMPILE option operates identically to UDFs written in C or C++ with

the exception that the extension the JAR file references in the Java UDF EXTERNAL NAME

clause is redistributed to all affected nodes on the system. Redistribution is useful when a JAR

file is missing on a given node. Note that you cannot specify the COMPILE ONLY option for

Java UDFs.

The ONLY clause on the COMPILE option for UDFs written in Java generates preamble files,

but does not redistribute their associated JAR file.

For more information, see “CREATE FUNCTION (External Form)/ REPLACE FUNCTION

(External Form)” on page 240 and “CREATE FUNCTION/REPLACE FUNCTION” in SQL

Data Definition Language Syntax and Examples

IF … THEN specify …

you are still developing and debugging a

function

EXECUTE PROTECTED.

the function is written in Java EXECUTE PROTECTED.

You cannot alter a Java function to run in unprotected

mode.

the function opens a file or uses another

operating system resource that requires

tracking by the operating system

This includes anything that causes the OS

to allocate system context, including open

files, pipes, semaphores, tokens, threads

(processes), and so on.

EXECUTE PROTECTED.

Running such a function in unprotected mode could

interfere with the proper operation of the Teradata

Database.

the function is a computational function

that does not use any operating system

resources

EXECUTE NOT PROTECTED.

Running a UDF in unprotected mode speeds up the

processing of the function considerably.

Use this option only after thoroughly debugging the

function and making sure it produces the correct

output.

Chapter 1: ALTER CONSTRAINT - COMMENT

ALTER FUNCTION (External Form)

SQL Data Definition Language Detailed Topics 17

UDF Servers

Protected mode UDFs, including secure mode UDFs, run under a separate process called a

server. UDFs set up to require external security run in a subtype of protected mode called

secure mode. The system sets up separate processes, or servers, to support both protected

mode and secure mode functions (see SQL External Routine Programming for details).

The following series of events occurs the first time a secure mode UDF (the process is

essentially identical for protected mode UDFs) is invoked in a session.

1 The UDF server setup logic looks at the authorization and determines whether a secure

server process is already set up for the given OS user authorization.

Because this is the first time the UDF has been invoked in the session, no secure server

process has been set up.

2 The authorization is not set up, so the process validates the OS user by making a validation

check on the user name and password.

If the logon attempt using the provided authorization information fails, the system returns

an error to the session.

3 If the authorization is successful, the system creates the secure server process and the UDF

executes.

If this is not the first time the UDF has been executed for the session, the following series of

events occurs:

1 The UDF secure server setup logic reads the authorization and determines that there is

already a secure server process set up with that authorization.

2 The UDF is executed using the existing secure server.

If the maximum number of secure servers has been created and a new request comes in for a

given authorization for which there is no established secure server, the following series of

events occurs:

1 The UDF secure server logic attempts to find a secure server with the given authorization.

It does not find one and determines that the maximum number of secures servers has

already been set up.

2 The authorization is not set up, so the process validates the OS user by making a validation

check on the user name and password.

If the logon attempt using the provided authorization information fails, the system returns

an error to the session.

3 The UDF secure server logic finds the least used secure server process and terminates it.

4 The new secure server process is created and the UDF is executed.

Chapter 1: ALTER CONSTRAINT - COMMENT

ALTER FUNCTION (External Form)

18 SQL Data Definition Language Detailed Topics

Recompiling and Redistributing an Existing Function

The COMPILE option is intended to be used by DBAs to recompile functions that have been

moved to a platform, system, or restored database other than the one in which they were

created.

Specify the COMPILE option to recompile an existing function. If its source code is present,

then the system recompiles it, generates the object, recreates the

.dll or .so file for

functions written in C or C++, and distributes the

.dll or .so files to all nodes of the

system. If the procedure is written in Java, the COMPILE option recompiles the existing

function, generates the object, and distributes the associated JAR files to all nodes of the

system. The object is replaced with the recompiled version.

If the function was created with the object only (that is, there is no source code present to be

recompiled), the function object is used to recreate the

.dll or .so file and distribute it to

all nodes of the system.

If the function is restored from a different platform to a new platform whose objects are not

compatible, the COMPILE option fails. When this happens, you must recreate the function

with the correct object or source code.

You cannot specify this option if the function was installed as part of a package.

Functions defined to run in unprotected mode and recompiled with this option do not retain

the ability to run in unprotected mode unless you specify EXECUTE NOT PROTECTED (see

“When to Specify Unprotected Mode” on page 15) as part of a follow-up ALTER FUNCTION

request.

For example, suppose you have a UDF named WriteMQ, which has been set to run in

EXECUTE NOT PROTECTED mode by an ALTER FUNCTION request that was submitted

after the function was created using a CREATE FUNCTION request (see “CREATE

FUNCTION (External Form)/ REPLACE FUNCTION (External Form)” on page 240) and

then thoroughly debugged. Some time later, it becomes necessary for WriteMQ to be

recompiled for some reason, so you must perform the following multistep procedure if

WriteMQ is to be both recompiled and to retain its ability to run in unprotected mode (

because

you cannot specify both COMPILE [ONLY] and EXECUTE [NOT] PROTECTED in the same ALTER

FUNCTION request)

1 You first submit the following ALTER FUNCTION request to recompile the WriteMQ

UDF:

ALTER FUNCTION WriteMQ COMPILE;

2 After the successful completion of this request, the system resets the protection mode for

WriteMQ to EXECUTE PROTECTED, so you must run a second ALTER FUNCTION

request to set its protection mode back to EXECUTE NOT PROTECTED:

ALTER FUNCTION WriteMQ EXECUTE NOT PROTECTED;

When you specify COMPILE ONLY, then only the UDF is recompiled and no new dynamic

linked library are distributed to database nodes. The COMPILE ONLY option is not available

for Java functions.

Chapter 1: ALTER CONSTRAINT - COMMENT

ALTER METHOD

20 SQL Data Definition Language Detailed Topics

ALTER METHOD

When to Specify Unprotected Mode

To protect the system from new external routines that have not passed your quality metrics,

the default execution mode for all external routines is EXECUTE PROTECTED. If the default

specification for CREATE METHOD and REPLACE METHOD were EXECUTE NOT

PROTECTED, then an error in the created function could crash the system.

Protected mode is a database state that traps on memory address violations, corrupted data

structures, and illegal computations, such as divide-by-zero, to ensure they do not crash the

system.

Protected mode does not detect or protect against CPU and memory loops or operating system I/

Os such as opening external files to read from or write to them.

This execution mode isolates all of the data the external routine might access as a separate

process, or “server” in its own local work space (see SQL External Routine Programming for

details). If any memory violation or other system error occurs, then the error is localized to

the routine and the transaction executing it, not the entire database (see “Protected and

Unprotected Execution Modes” on page 246 for more information).

Because this isolation also makes the routine run slower, you should change its protection

mode to unprotected once it has passed all your quality metrics for being put into production

use. You change the execution mode for the routine with a simple ALTER METHOD request

(see “ALTER METHOD” on page 20) and specifying the EXECUTE NOT PROTECTED

option.

Do not make this change for any method that makes OS system calls. Any such method should

always be run in protected or secure mode. You do not need to recompile or relink the external

routine after changing its protection mode.

As a rule, the DBA should only specify direct execution mode (assigned by the EXECUTE

NOT PROTECTED option) for a method that performs CPU-only operations after it has

been thoroughly debugged. No other user than the DBA, not even the software engineer that

writes a UDF, should be granted the privileges to alter it to run in unprotected mode. Never

run external routines that cause the OS to consume system resources in unprotected mode.

This includes anything that causes the OS to allocate system context, including open files,

pipes, semaphores, tokens, threads (processes), and so on.

剩余1065页未读，继续阅读

iHoneyBadger

粉丝: 0
资源: 2

Teradata SQL Data Definition Language (15.0)：详细教程与特许概述

LabVIEW通过sql_lv连接MySQL数据库操作指南

MonetDB SQL引擎Catalog深度解析

netctoss项目SQL初始化脚本详细介绍

sql_data

data.rar_SQL_Server_SQL_

data_definition_language

sql_parse_src.rar_C语言 sql解析_sql parse _sql_parse_sqlparse c++_s

tushu.rar_SQL vc++_VC+++SQL_c++SQL_sql C++_visual c++ sql

Delphi_SQL.zip_DelPhi SQl_delphi sql_sql delphi

avon.rar_SQL_Server_SQL_

最新资源