SQL programming

 

DB2 Universal Database™ for iSeries™ provides a wide range of support for Structured Query Language (SQL).

The examples of SQL statements shown in this topic collection are based on the sample tables and assume that the following statements are true:

• They are shown in the interactive SQL environment or they are written in ILE C or in COBOL. EXEC SQL and END-EXEC are used to delimit an SQL statement in a COBOL program.

• Each SQL example is shown on several lines, with each clause of the statement on a separate line.

• SQL keywords are highlighted.

• Table names provided in the sample tables use the schema CORPDATA. Table names that are not found in the Sample Tables should use schemas you create.

• Calculated columns are enclosed in parentheses, (), and brackets, [].

• The SQL naming convention is used.

• The APOST and APOSTSQL precompiler options are assumed although they are not the default options in COBOL. Character string literals within SQL and host language statements are delimited by single-quotation marks (').

• A sort sequence of *HEX is used, unless otherwise noted.

Whenever the examples vary from these assumptions, it is stated.

Because this topic collection is for the application programmer, most of the examples are shown as if they were written in an application program. However, many examples can be slightly changed and run interactively by using interactive SQL. The syntax of an SQL statement, when using interactive SQL, differs slightly from the format of the same statement when it is embedded in a program.

By using the code examples, you agree to the terms of the Code license and disclaimer information.

• What's new for V5R4
  This topic highlights the changes made to this topic collection for V5R4.

• Printable PDF
  Use this to view and print a PDF of this information.

• Introduction to DB2 UDB for iSeries Structured Query Language
  Structured Query Language (SQL) is a standardized language for defining and manipulating data in a relational database. These topics describe the System i™ implementation of the SQL using DB2^® UDB for iSeries and the DB2 Query Manager and SQL Development Kit licensed program.

• Data definition language
  Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, and aliases.

• Data manipulation language
  Data manipulation language (DML) describes the portion of SQL that manipulates or controls data.

• Sort sequences and normalization in SQL
  A sort sequence defines how characters in a character set relate to each other when they are compared or ordered. Normalization allows you to compare strings that contain combining characters.

• Data protection
  DB2 UDB for iSeries provides various methods for protecting SQL data from unauthorized users and for ensuring data integrity.

• Routines
  Routines are pieces of code or programs that you can call to perform operations.

• Processing special data types
  Most data types, such as INTEGER and CHARACTER, do not have any special processing characteristics. However, a few data types require special functions or locators to use them effectively.

• Using SQL in different environments
  You can use SQL in many different environments.

• Distributed relational database function and SQL
  A distributed relational database consists of a set of SQL objects that are spread across interconnected computer systems.

• Reference
  Reference information for SQL programming includes sample tables and CL commands.

 

Related concepts

Embedded SQL programming

 

Related reference

DB2 Universal Database for iSeries sample tables
SQL reference