IBM i: Database SQL programming

IBM

IBM i

Database

SQL programming

7.1

IBM

IBM i

Database

SQL programming

7.1

Note

Before using this information and the product it supports, read the information in ¡°Notices,¡± on

page 493.

This edition applies to IBM i 7.1 (product number 5770-SS1) and to all subsequent releases and modifications until

otherwise indicated in new editions. This version does not run on all reduced instruction set computer (RISC)

models nor does it run on CISC models.

? Copyright IBM Corporation 1998, 2010.

US Government Users Restricted Rights ¨C Use, duplication or disclosure restricted by GSA ADP Schedule Contract

with IBM Corp.

Contents

SQL programming . . . . . . . . . . 1

|

|

|

|

What's new for IBM i 7.1 . . . . . . . . . . 1

PDF file for SQL programming . . . . . . . . 4

Introduction to DB2 for i Structured Query Language 4

SQL concepts . . . . . . . . . . . . . 5

SQL relational database and system

terminology . . . . . . . . . . . . 6

SQL and system naming conventions . . . . 7

Types of SQL statements . . . . . . . . 7

SQL communication area . . . . . . . . 9

SQL diagnostics area. . . . . . . . . . 9

SQL objects . . . . . . . . . . . . . . 9

Schemas . . . . . . . . . . . . . . 9

Journals and journal receivers . . . . . . 9

Catalogs . . . . . . . . . . . . . 10

Tables, rows, and columns . . . . . . . 10

Aliases . . . . . . . . . . . . . . 10

Views . . . . . . . . . . . . . . 10

Indexes . . . . . . . . . . . . . . 11

Constraints . . . . . . . . . . . . 11

Triggers . . . . . . . . . . . . . 12

Stored procedures . . . . . . . . . . 12

Sequences . . . . . . . . . . . . . 12

Global variables . . . . . . . . . . . 12

User-defined functions. . . . . . . . . 12

User-defined types . . . . . . . . . . 13

XSR objects . . . . . . . . . . . . 13

SQL packages . . . . . . . . . . . 13

Application program objects . . . . . . . . 13

User source file . . . . . . . . . . . 15

Output source file member . . . . . . . 15

Program . . . . . . . . . . . . . 15

SQL package . . . . . . . . . . . . 15

Module . . . . . . . . . . . . . . 16

Service program . . . . . . . . . . . 16

Data definition language . . . . . . . . . . 16

Creating a schema . . . . . . . . . . . 16

Creating a table . . . . . . . . . . . . 17

Adding and removing constraints . . . . . 17

Referential integrity and tables . . . . . . 18

Adding and removing referential

constraints. . . . . . . . . . . . 18

Example: Adding referential constraints . . 19

Example: Removing constraints. . . . . . 20

Check pending . . . . . . . . . . . 20

Creating a table using LIKE . . . . . . . . 21

Creating a table using AS. . . . . . . . . 21

Creating and altering a materialized query table 22

Declaring a global temporary table . . . . . 23

Creating a table with remote server data . . . 23

Creating a row change timestamp column . . . 24

Creating and altering an identity column . . . 24

Using ROWID . . . . . . . . . . . . 25

Creating and using sequences . . . . . . . 26

Comparison of identity columns and

sequences . . . . . . . . . . . . . 27

? Copyright IBM Corp. 1998, 2010

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

|

Defining field procedures. . . . . . . .

Field definition for field procedures . . .

Specifying the field procedure . . . . .

When field procedures are invoked . . .

Parameter list for execution of field

procedures . . . . . . . . . . .

The field procedure parameter value list

(FPPVL) . . . . . . . . . . .

Parameter value descriptors for field

procedures . . . . . . . . . .

Field-definition (function code 8) . . .

Field-encoding (function code 0) . . .

Field-decoding (function code 4) . . .

Example field procedure program . . .

General guidelines for writing field

procedures . . . . . . . . . . .

Index considerations . . . . . . .

Thread considerations . . . . . . .

Debug considerations . . . . . . .

Guidelines for writing field procedures that

mask data . . . . . . . . . . . .

Example field procedure program that

masks data . . . . . . . . . .

Creating descriptive labels using the LABEL ON

statement . . . . . . . . . . . . .

Describing an SQL object using COMMENT ON

Changing a table definition . . . . . . .

Adding a column . . . . . . . . .

Changing a column. . . . . . . . .

Allowable conversions of data types . . .

Deleting a column . . . . . . . . .

Order of operations for the ALTER TABLE

statement . . . . . . . . . . . .

Using CREATE OR REPLACE TABLE . .

Creating and using ALIAS names . . . . .

Creating and using views. . . . . . . .

WITH CHECK OPTION on a view . . .

WITH CASCADED CHECK OPTION .

WITH LOCAL CHECK OPTION . . .

Example: Cascaded check option . . .

Creating indexes. . . . . . . . . . .

Creating and using global variables . . . .

Replacing existing objects. . . . . . . .

Catalogs in database design . . . . . . .

Getting catalog information about a table .

Getting catalog information about a column

Dropping a database object . . . . . . .

Data manipulation language. . . . . . . .

Retrieving data using the SELECT statement .

Basic SELECT statement . . . . . . .

Specifying a search condition using the

WHERE clause . . . . . . . . . .

Expressions in the WHERE clause . . .

Comparison operators . . . . . . .

NOT keyword . . . . . . . . .

GROUP BY clause . . . . . . . . .

.

.

.

.

28

29

29

29

. 30

. 32

.

.

.

.

.

32

33

34

35

36

.

.

.

.

37

38

38

38

. 38

. 41

. 42

43

. 44

. 44

. 44

. 45

. 46

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

46

47

48

48

50

51

51

52

53

53

54

54

55

55

55

56

56

56

.

.

.

.

.

57

58

59

60

60

iii

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download