IBM DB2 Call Level Interface 教程与参考指南

需积分: 9 182 浏览量更新于2024-07-20 收藏 2.74MB PDF 举报

"DB2 Call Level Interface Guide and Reference" DB2 Call Level Interface (CLI) 是IBM数据库产品DB2中的一种编程接口，它允许程序员通过标准的SQL调用来与数据库进行交互。CLI是数据库独立的，意味着它遵循开放组的SQL CLI标准，这使得应用程序可以在不同的数据库管理系统(DBMS)之间移植，只要这些系统支持CLI。CLI提供了访问数据库的强大功能，包括数据查询、插入、更新和删除，以及事务管理等。 CLI的核心特性包括： 1. **SQL语句执行**：CLI提供了一个API，允许程序执行SQL命令，如SELECT、INSERT、UPDATE和DELETE。程序员可以预编译SQL语句，提高执行效率。 2. **游标**：CLI支持游标，允许应用程序按需从结果集中逐行获取数据，这对于处理大量数据或分批处理结果集非常有用。 3. **参数绑定和数据类型转换**：CLI允许程序员将变量绑定到SQL语句中的占位符，实现参数化查询。此外，CLI会自动处理不同数据类型之间的转换。 4. **事务处理**：CLI提供了对事务的控制，包括提交、回滚和保存点，确保数据的一致性和完整性。 5. **错误处理**：当出现错误时，CLI会返回错误代码和消息，帮助开发者诊断问题。 6. **元数据获取**：CLI允许程序查询关于表、列、索引等数据库对象的信息，帮助动态构建SQL语句。 7. **批处理操作**：CLI支持批量执行SQL语句，提高性能，尤其在执行相同操作多次时。在"Call Level Interface Guide and Reference"文档中，IBM详细介绍了如何使用DB2 CLI，包括初始化环境、连接数据库、执行SQL语句、处理结果集、管理事务、错误处理机制，以及各种CLI函数和数据类型。此外，文档还可能包含示例代码，帮助开发者理解和应用CLI。该文档强调了在使用CLI前，用户应阅读"通知书"部分，了解版权信息、许可协议和法律声明。IBM提供CLI文档作为其专有信息的一部分，用户可以通过IBM代表或当地分支办公室订购此出版物。请注意，IBM提供CLI文档，并不包含任何产品保修。用户向IBM提供信息时，可能会被授予非独占使用权，IBM有权使用或分发这些信息，而无需对用户承担任何义务。版权信息显示，文档版权归国际商业机器公司（IBM Corporation）所有，自1993年和1997年起，保留所有权利。 DB2 CLI是开发人员与DB2数据库交互的关键工具，提供了高效且灵活的接口，适用于多种数据库操作和复杂的应用场景。"DB2 Call Level Interface Guide and Reference"是掌握CLI使用的权威参考资料，涵盖了从基本概念到高级特性的全面指导。

– Appendix B, “Migrating Applications” on page 645, summarizes what has

changed since the last release, and any incompatibilities and necessary steps

required for migrating existing applications.

– Appendix C, “DB2 CLI and ODBC” on page 655, discusses the differences

between ODBC and DB2 CLI.

– Appendix D, “Extended Scalar Functions” on page 659, describes the scalar

functions that can be accessed as DB2 functions, or using ODBC vendor

escape clauses.

– Appendix E, “SQLSTATE Cross Reference” on page 669, contains an

SQLSTATE table that lists the functions that may generate each SQLSTATE.

(Each function description in Chapter 5, “Functions” on page 175 lists the

possible SQLSTATEs for each function.)

– Appendix F, “Data Conversion” on page 685, contains information about SQL

and C data types, and conversion between them.

– Appendix H, “Pseudo Catalog Table for Stored Procedure Registration” on

page 705, describes how to create and maintain the DB2CLI.PROCEDURES

pseduo-catalog table.

– Appendix I, “Supported SQL Statements” on page 709, contains a list of the

SQL statements supported by DB2 Universal Database, and the subset of

SQL supported by DB2 CLI.

– Appendix J, “Example Code Listing” on page 713, lists the complete source

for some extensive examples.

xii CLI Guide and Reference

Chapter 1. Introduction to CLI

DB2 Call Level Interface (CLI) is IBM's callable SQL interface to the DB2 family of

database servers. It is a 'C' and 'C++' application programming interface for relational

database access, and it uses function calls to pass dynamic SQL statements as

function arguments. It is an alternative to embedded dynamic SQL, but unlike

embedded SQL, it does not require host variables or a precompiler.

DB2 CLI is based on the Microsoft** Open Database Connectivity** (ODBC)

specification, and the International Standard for SQL/CLI. These specifications were

chosen as the basis for the DB2 Call Level Interface in an effort to follow industry

standards and to provide a shorter learning curve for those application programmers

already familiar with either of these database interfaces. In addition, some DB2 specific

extensions have been added to help the application programmer specifically exploit

DB2 features.

The DB2 CLI driver also acts as an ODBC driver when loaded by an ODBC driver

manager. It conforms to level 2 of ODBC 2.0, and level 1 of ODBC 3.0. In addition it

also conforms to various ODBC 3.0 level 2 interface conformance items (202, 203, 205,

207, 209, and 211). Information regarding ODBC support and level 2 interface

conformance items is provided in Appendix C, “DB2 CLI and ODBC” on page 655.

DB2 CLI Background Information

To understand DB2 CLI or any callable SQL interface, it is helpful to understand what it

is based on, and to compare it with existing interfaces.

The X/Open Company and the SQL Access Group jointly developed a specification for

a callable SQL interface referred to as the

X/Open Call Level Interface

. The goal of this

interface is to increase the portability of applications by enabling them to become

independent of any one database vendor's programming interface. Most of the X/Open

Call Level Interface specification has been accepted as part of the ISO Call Level

Interface International Standard (ISO/IEC 9075-3:1995 SQL/CLI).

Microsoft developed a callable SQL interface called Open Database Connectivity

(ODBC) for Microsoft operating systems based on a preliminary draft of X/Open CLI.

Version 3 of ODBC conforms to almost all of ISO SQL/CLI. ODBC 3.0 does contain

considerable functionality that is not part of the International Standard; much of this is

being added to the the next draft of the standard.

The ODBC specification also includes an operating environment where database

specific ODBC Drivers are dynamically loaded at run time by a driver manager based

on the data source (database name) provided on the connect request. The application

is linked directly to a single driver manager library rather than to each DBMS's library.

The driver manager mediates the application's function calls at run time and ensures

they are directed to the appropriate DBMS specific ODBC driver. Since the ODBC

driver manager only knows about the ODBC-specific functions, DBMS-specific functions

cannot be accessed in an ODBC environment. DBMS-specific dynamic SQL statements

 Copyright IBM Corp. 1993, 1997 1

are supported via a mechanism called an escape clause which is described in “Using

Vendor Escape Clauses” on page 123.

ODBC is not limited to Microsoft operating systems, other implementations are available

on various platforms.

The DB2 CLI load library can be loaded as an ODBC driver by an ODBC driver

manager. For ODBC application development, you must obtain an ODBC Software

Development Kit (from Microsoft for Microsoft platforms, and from Visigenic Software,

Inc. for non-Microsoft platforms.) When developing ODBC applications that may

connect to DB2 servers, use this book (for information on DB2 specific extensions and

diagnostic information), in conjunction with the

ODBC 3.0 Programmer's Reference and

SDK Guide

Applications written directly to DB2 CLI link directly to the DB2 CLI load library. DB2

CLI includes support for many ODBC and ISO SQL/CLI functions, as well as DB2

specific functions. For a list of supported functions, refer to “DB2 CLI Function

Summary” on page 177.

For more information on the relationship between DB2 CLI and ODBC, refer to

Appendix C, “DB2 CLI and ODBC” on page 655.

The following DB2 features are available to both ODBC and DB2 CLI applications:

¹ The double byte (graphic) data types

¹ Stored Procedures

¹ Distributed Unit of Work (DUOW), two phase commit

¹ Compound SQL

¹ User Defined Types (UDT)

¹ User Defined Functions (UDF)

DB2 CLI also contains extensions to access DB2 features that can not be accessed by

ODBC applications:

¹ Support of Large Objects (LOBs), and LOB locators

¹ SQLCA access for detailed DB2 specific diagnostic information

Differences Between DB2 CLI and Embedded SQL

An application that uses an embedded SQL interface requires a precompiler to convert

the SQL statements into code, which is then compiled, bound to the database, and

executed. In contrast, a DB2 CLI application does not have to be precompiled or

bound, but instead uses a standard set of functions to execute SQL statements and

related services at run time.

This difference is important because, traditionally, precompilers have been specific to

each database product, which effectively ties your applications to that product. DB2 CLI

2 CLI Guide and Reference

enables you to write portable applications that are independent of any particular

database product. This independence means DB2 CLI applications do not have to be

recompiled or rebound to access different DB2 databases, including DRDA databases.

They just connect to the appropriate database at run time.

Comparing Embedded SQL and DB2 CLI

DB2 CLI and embedded SQL also differ in the following ways:

¹ DB2 CLI does not require the explicit declaration of cursors. DB2 CLI has a supply

of cursors that get used as needed. The application can then use the generated

cursor in the normal cursor fetch model for multiple row SELECT statements and

positioned UPDATE and DELETE statements.

¹ The OPEN statement is not used in DB2 CLI. Instead, the execution of a SELECT

automatically causes a cursor to be opened.

¹ Unlike embedded SQL, DB2 CLI allows the use of parameter markers on the

equivalent of the EXECUTE IMMEDIATE statement (the SQLExecDirect() function).

¹ A COMMIT or ROLLBACK in DB2 CLI is issued via the SQLEndTran() function call

rather than by passing it as an SQL statement.

¹ DB2 CLI manages statement related information on behalf of the application, and

provides a

statement handle

to refer to it as an abstract object. This handle

eliminates the need for the application to use product specific data structures.

¹ Similar to the statement handle, the

environment handle

and

connection handle

provide a means to refer to all global variables and connection specific information.

The

descriptor handle

describes either the parameters of an SQL statement or the

columns of a result set.

¹ DB2 CLI uses the SQLSTATE values defined by the X/Open SQL CAE

specification. Although the format and most of the values are consistent with values

used by the IBM relational database products, there are differences. (There are

also differences between ODBC SQLSTATES and the X/Open defined

SQLSTATES). Refer to Table 196 on page 669 for a cross reference of all DB2

CLI SQLSTATEs.

¹ DB2 CLI supports read-only scrollable cursors. With scrollable cursors, you can

scroll through a static read-only cursor as follows:

– Forward by one or more rows

– Backward by one or more rows

– From the first row by one or more rows

– From the last row by one or more rows.

Despite these differences, there is an important common concept between embedded

SQL and DB2 CLI:

DB2 CLI can execute any SQL statement that can be prepared

dynamically in embedded SQL.

Note: DB2 CLI can also accept some SQL statements that cannot be prepared

dynamically, such as compound SQL statements.

Chapter 1. Introduction to CLI 3

Table 220 on page 709 lists each SQL statement, and indicates whether or not it can

be executed using DB2 CLI. The table also indicates if the command line processor can

be used to execute the statement interactively, (useful for prototyping SQL statements).

Each DBMS may have additional statements that you can dynamically prepare. In this

case, DB2 CLI passes the statements to the DBMS. There is one exception: the

COMMIT and ROLLBACK statement can be dynamically prepared by some DBMSs but

are not passed. In this case, use the SQLEndTran() function to specify either the

COMMIT or ROLLBACK statement.

Advantages of Using DB2 CLI

The DB2 CLI interface has several key advantages over embedded SQL.

¹ It is ideally suited for a client-server environment, in which the target database is

not known when the application is built. It provides a consistent interface for

executing SQL statements, regardless of which database server the application is

connected to.

¹ It increases the portability of applications by removing the dependence on

precompilers. Applications are distributed not as embedded SQL source code

which must be preprocessed for each database product, but as compiled

applications or run time libraries.

¹ Individual DB2 CLI applications do not need to be bound to each database, only

bind files shipped with DB2 CLI need to be bound once for all DB2 CLI

applications. This can significantly reduce the amount of management required for

the application once it is in general use.

¹ DB2 CLI applications can connect to multiple databases, including multiple

connections to the same database, all from the same application. Each connection

has its own commit scope. This is much simpler using CLI than using embedded

SQL where the application must make use of multi-threading to achieve the same

result.

¹ DB2 CLI eliminates the need for application controlled, often complex data areas,

such as the SQLDA and SQLCA, typically associated with embedded SQL

applications. Instead, DB2 CLI allocates and controls the necessary data

structures, and provides a

handle

for the application to reference them.

¹ DB2 CLI enables the development of multi-threaded thread-safe applications where

each thread can have its own connection and a separate commit scope from the

rest. DB2 CLI achieves this by eliminating the data areas described above, and

associating all such data structures that are accessible to the application with a

specific handle. Unlike embedded SQL, a multi-threaded CLI application does not

need to call any of the context management DB2 APIs; this is handled by the DB2

CLI driver automatically.

¹ DB2 CLI provides enhanced parameter input and fetching capability, allowing

arrays of data to be specified on input, retrieving multiple rows of a result set

directly into an array, and executing statements that generate multiple result sets.

¹ DB2 CLI provides a consistent interface to query catalog (Tables, Columns,

Foreign Keys, Primary Keys, etc.) information contained in the various DBMS

4 CLI Guide and Reference

剩余777页未读，继续阅读

NEU0712

粉丝: 0
资源: 4

IBM DB2 Call Level Interface 教程与参考指南

DB2 V9.5 - Call Level Interface Guide and Reference, Volume 2.pdf

【图像融合】基于matlab深度学习医学图像融合【含Matlab源码 8038期】.md

【图像配准】基于matlab SIFT图像配准【含Matlab源码 1007期】.md

基于 YOLOv5（假设为 YOLOv11）的火灾火焰烟雾检测系统（包含详细的完整的程序和数据）

ahds-0.2.3-cp39-cp39-win_amd64.whl

【图像加密】基于matlab预测误差分类置乱图像加密解密【含Matlab源码 1846期】.md

【图像评价】基于matlab无参考图像质量评价NIQE【含Matlab源码 681期】.md

bsdiff4-1.1.9-cp35-cp35m-win_amd64.whl

pycosat-0.6.3-cp39-cp39-win_amd64.whl

wrapt-1.11.2-cp35-cp35m-win_amd64.whl

最新资源