Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

developerWorks Community:

  • Close [x]

DB2 9 Application Development exam 733 prep, Part 4: Embedded SQL programming

Build applications that interact with DB2

Roger E. Sanders (rsanders@netapp.com), Senior Manager - IBM Alliance Engineering, Network Appliance, Inc.
Author1 photo
Roger E. Sanders is a Senior Manager - IBM Alliance Engineering at Network Appliance, Inc. He has been designing and developing databases and database applications for more than 20 years and has been working with DB2 Universal Database since it was first introduced with OS/2 1.3 Extended Edition. He has written articles for IDUG Solutions Journal, Certification Magazine, and developerWorks, presented and taught classes at IDUG and RUG conferences, participated in the development of the DB2 certification exams, writes a regular column for DB2 Magazine and is the author of 9 books on DB2 UDB.
(An IBM developerWorks Master Author, Level 2)

Summary:  This tutorial introduces you to embedded SQL programming and walks you through how to construct an embedded SQL application. This tutorial introduces the process for converting one or more high-level programming language source code files containing embedded SQL into an executable application. This is the fourth in a series of nine tutorials designed to help you prepare for the DB2 Application Developer Certification exam (Exam 733).

View more content in this series

Date:  15 Feb 2007
Level:  Introductory PDF:  A4 and Letter (521 KB | 33 pages)Get Adobe® Reader®

Comments:  

Introduction to embedded SQL programming

Structured Query Language and embedded SQL

Structured Query Language (SQL) is a standardized language used to manipulate database objects and the data they contain. SQL is comprised of several different statements that are used to define, alter, and destroy database objects, and to insert, modify, delete, and retrieve data values. But because SQL is nonprocedural in nature, SQL is not a general-purpose programming language. (SQL statements are executed by the DB2 Database Manager, not by the operating system.) Therefore, database applications are usually developed by combining the decision and sequence control of a high-level programming language with the data storage, manipulation, and retrieval capabilities of SQL. Several methods are available for merging SQL with a high-level programming language, but the simplest approach is to embed SQL statements directly into the high-level programming language source code files that are used to create an application. This technique is known as embedded SQL programming.

One of the drawbacks to developing applications using embedded SQL is that high-level programming language compilers do not recognize, and therefore cannot interpret, SQL statements embedded in a source code file. Because of this, source code files containing embedded SQL statements must be preprocessed (by a process known as precompiling) before they can be compiled and linked to produce an executable application. To facilitate this preprocessing, each SQL statement embedded in a high-level programming language source code file must be prefixed with the keywords EXEC SQL and terminated with either a semicolon (in C/C++) or the keyword END-EXEC (in COBOL). Preprocessing is performed by a special tool known as the SQL precompiler; when the SQL precompiler encounters the EXEC SQL keywords, it replaces the text that follows (until it finds a semicolon (;) or the keyword END-EXEC) with a DB2-specific function call that forwards the SQL statement encountered to the DB2 Database Manager for processing.

Likewise, the DB2 Database Manager cannot work directly with high-level programming language variables. Instead, it must use special variables known as host variables to move data between an application and a database. (We will take a closer look at host variables in section below entitled "Declaring host variables.") Host variables look like any other high-level programming language variable; so, to be set apart, they must be defined in a special section known as a declare section. Also, in order for the SQL precompiler to distinguish host variables from other text in an SQL statement, all references to host variables must be preceded by a colon (:).


Static SQL

A static SQL statement is an SQL statement that can be hardcoded in an application program at development time because information about the structure and objects (i.e., tables, column, and data types) with which it is intended to interact with is known in advance. Since the details of a static SQL statement are known at development time, the work of analyzing the statement and selecting the optimum data access plan for executing the statement is performed by the DB2 optimizer as part of the development process. Because their operational form is stored in the database (as a package) and does not have to be generated at application runtime, static SQL statements execute quickly.

The downside to this approach is that all static SQL statements must be prepared (in other words, their access plans must be generated and stored in the database) before they can be executed. Furthermore, static SQL statements cannot be altered at runtime, and each application that uses static SQL must bind its operational packages to every database with which the application will interact. Additionally, because static SQL applications require prior knowledge of database objects, changes made to those objects after an application has been developed can produce undesirable results.

The following are examples of static SQL statements:

  SELECT COUNT(*) FROM employee

  UPDATE employee SET lastname = 'Jones' WHERE empid = '001'

  SELECT MAX(salary), MIN(salary) INTO :MaxSalary, :MinSalary FROM employee
        

Generally, static SQL statements are well suited for high-performance applications that execute predefined operations against a known set of database objects.


Dynamic SQL

Although static SQL statements are relatively easy to incorporate into an application, their use is somewhat limited because their format must be known in advance. Dynamic SQL statements, on the other hand, are much more flexible because they can be constructed at application runtime; information about a dynamic SQL statement's structure and the objects with which it plans to interact does not need to be known in advance. Furthermore, because dynamic SQL statements do not have a precoded, fixed format, the data objects they reference can change without affecting the statement (provided that objects referenced by the statement are not deleted).

Even though dynamic SQL statements are generally more flexible than static SQL statements, they are usually more complicated to incorporate into an application. And because the work of analyzing the statement to select the best access plan is performed at application runtime (again, by the DB2 optimizer), dynamic SQL statements can take longer to execute than their static SQL counterparts. (Because dynamic SQL statements can take advantage of the database statistics available at application runtime, there are some cases in which a dynamic SQL statement will execute faster than an equivalent static SQL statement, but those are the exception and not the norm.)

The following are examples of dynamic SQL statements:

 SELECT COUNT(*) FROM ?
	
 INSERT INTO EMPLOYEES VALUES (?, ?)
	
 DELETE FROM DEPARTMENT WHERE DEPTID = ?
        

Generally, dynamic SQL statements are well suited for applications that interact with a rapidly changing database or that allow users to define and execute ad-hoc queries.

2 of 8 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=195578
TutorialTitle=DB2 9 Application Development exam 733 prep, Part 4: Embedded SQL programming
publish-date=02152007
author1-email=rsanders@netapp.com
author1-email-cc=