SAP HANA SP3 SQL Script Guide: Database Administration

5星 · 超过95%的资源需积分: 9 108 浏览量更新于2024-07-29 1 收藏 1.49MB PDF 举报

"SAPHANA数据库-SQL脚本指南" 这篇文档是SAP HANA数据库SQL脚本的指南，特别针对SAP HANA Software Service Pack 3 (SPS03)版本。该指南主要面向两类读者：顾问和管理员，提供公共访问。文档版本为1.0，发布日期为2011年11月7日，由SAP AG版权所有。请注意，未经SAP AG明确许可，不得复制或以任何形式传输此出版物的任何部分。由于信息可能会在未预先通知的情况下变更，因此这份指南的内容可能存在更新或变动。SAP HANA数据库中可能包含其他软件供应商的专有软件组件，这反映了SAP产品集成和合作的特性。文档中提到了几个与知识产权相关的商标，例如Sybase、Adaptive Server、SQL Anywhere等，它们是Sybase Inc.的商标或注册商标。所有其他提及的商标则归其各自所有者所有。此外，文档还提及了微软（Microsoft）的Windows、Outlook和PowerPoint，这些都是微软公司的注册商标。IBM的相关产品如DB2、System i、System p等，以及IBM的商标也有所提及。 SAP HANA SQL脚本指南可能涵盖了以下主题： 1. SAP HANA SQL脚本语言的基础概念和语法，包括如何创建、修改和删除数据库对象，如表、视图、索引等。 2. 数据操作语言（DML）命令，如INSERT、UPDATE、DELETE和SELECT，用于数据的插入、更新、删除和查询。 3. 数据定义语言（DDL）命令，用于定义数据库结构，包括创建和修改表结构、视图、存储过程等。 4. 事务处理和并发控制，确保多用户环境下数据的一致性和完整性。 5. SQL脚本的执行计划分析，帮助优化查询性能。 6. 使用SQL脚本进行数据加载和数据迁移的方法。 7. 安全性管理，如用户权限、角色分配和访问控制。 8. 存储过程和函数的编写与调用，增强数据库的功能和自动化流程。 9. 性能监控和调优工具，用于分析SQL查询的性能并进行优化。 10. 高级功能，如列式存储、实时分析、内存优化等SAP HANA特有的特性。这份指南对于理解和掌握SAP HANA数据库的SQL脚本编写至关重要，可以帮助用户充分利用SAP HANA SP3版本的功能来管理和操作数据。通过深入学习，读者可以有效地提升在SAP HANA环境中进行数据库管理和开发的能力。

SAP HANA Database – SQLScript Guide

SAP In-Memory Appliance (SAP HANA) Page 9 of 50

SQLScript Processing Overview

In order to better understand the features of SQLScript, and their impact on the execution time of a

procedure, it is instructive to understand how SQLScript procedures are processed in the SAP HANA

database, see the picture below.

When a user defines a new procedure, e.g. using the CREATE PROCEDURE statement discussed later

in this document, the SAP HANA database query compiler processes the statement in a similar way to

an SQL statement. In the figure above, the compilation steps are represented by the arrows, and the

data structures used in this process are shown as nodes in the process flow. A step by step analysis of

the process flow follows below:

 Parse the statement - Detect and report simple syntactic errors.

 Check the statements semantic correctness - Derive types for variables and check their use is

consistent.

 Optimize the code - Optimization distinguishes between declarative logic shown in the upper

branch and imperative logic shown in the lower branch. We discuss how the SAP HANA

database recognizes them below.

 Code generation - For declarative logic the compiler creates calculation models to represent

the data flow defined by the SQLScript code. It will be optimized further by the calculation

engine, when it is instantiated. For imperative logic the code blocks are translated into L

nodes

 The calculation models generated in the previous step are combined into a stacked

calculation model.

 The compiled procedure creates content in the database catalog and the repository.

Invocation of a procedure can be divided into two phases:

„L“ is a new language developed by SAP for executing safe procedural code in SAP HANA

Database.

SAP HANA Database – SQLScript Guide

Page 10 of 50 SAP In-Memory Appliance (SAP HANA)

1) Compilation - During compilation the call to the procedure is rewritten for processing by the

calculation engine.

2) Execution - The execution commences with binding actual parameters to the calculation

models generated n the definition phase. When the calculation models are instantiated they

can be optimized based on concrete input provided. Optimizations include predicate or

projection embedding in the database. Finally the instantiated calculation model is executed

using any of the available parts of the SAP HANA database.

With SQLScript one can implement applications both using imperative orchestration logic and

(functional) declarative logic, and this is also reflected in the way SQLScript processing works for both

coding styles. Imperative logic is executed sequentially and declarative logic is executed by exploiting

the internal architecture of the SAP HANA database utilizing its potential for parallelism.

Orchestration-Logic

Orchestration logic is used to implement data flow and control flow logic using DDL-, DML- and SQL-

Query statements as well as imperative language constructs like loops and conditionals. The

orchestration logic can also execute declarative logic that is defined in the functional extension by

calling the corresponding procedures. In order to achieve an efficient execution on both levels, the

statements are transformed into a dataflow graph to the maximum extent possible, i.e. the

compilation step extracts data-flow oriented snippets out of the orchestration logic and maps them

to data-flow constructs. The calculation engine serves as execution engine of the resulting data flow

graph. Since the language L is used as intermediate language for translating SQLScript into a

calculation model, the range of mappings may span the full spectrum – from a single internal L-node

for a complete SQLScript script in its simplest form, up to a fully resolved data-flow graph without

any imperative code left for an internal L-node. Typically the dataflow graph provides more

opportunities for optimization and thus better performance.

To transform the application logic into a complex data flow graph two prerequisites have to be

fulfilled:

 All data flow operations have to be side-effect free, i.e. they must not change any global

state either in the database or in the application logic.

 All control flows can be transformed into a static dataflow graph.

In SQLScript the optimizer will transform a sequence of assignments of SQL query result sets to table

variables into parallelizable dataflow constructs. The imperative logic is usually represented as a

single node in the dataflow graph, and thus it will be executed sequentially.

Example for Orchestration-Logic:

CREATE PROCEDURE orchestrationProc LANGUAGE SQLSCRIPT AS

v_id BIGINT;

v_name VARCHAR(30);

v_pmnt BIGINT;

v_msg VARCHAR(200);

CURSOR c_cursor1 (p_payment BIGINT) FOR

SELECT id, name, payment FROM control_tab

剩余49页未读，继续阅读

elizabethIII

粉丝: 2
资源: 29

SAP HANA SP3 SQL Script Guide: Database Administration

SAP HANA SQL Script Reference

SAP HANA SQLScript Guide

sap hana sqlscript

sap script

sql script

SAP HANA 1.0 SQL Script 开发指南

SAP HANA SQL Script

SAP HANA SQLScript 教程

SAPHANA SQL Script 使用指南

SAP HANA SQL Script编程指南

最新资源