Learning Visually with Examples [Electronic resources] نسخه متنی

2.1. SQL Statements and DB2 Commands

SQL is the standard language used for retrieving and modifying data in a relational database. An SQL council formed by several industry leading companies determines the standard for these SQL statements, and the different relational database management systems (RDBMSs) follow these standards to make it easier for customers to use their databases. This section introduces the different categories of SQL statements and presents some examples.

DB2 commands are directives specific to DB2 that allow you to perform tasks against a DB2 server. There are two types of DB2 commands:

• System commands

• Command Line Processor (CLP) commands

2.1.1. SQL Statements

SQL statements allow you to work with the data stored in your database. The statements are applied against the database you are connected to, not against the entire DB2 environment. There are three different classes of SQL statements.

• Data Definition Language (DDL) statements create, modify, or drop database objects. For example:

  
  CREATE INDEX ix1 ON t1 (salary) 
  ALTER TABLE t1 ADD hiredate DATE 
  DROP VIEW view1 
  

• Data Manipulation Language (DML) statements insert, update, delete, or select data from the database objects. For example:

  
  INSERT INTO t1 VALUES (10,'Johnson','Peter') 
  UPDATE t1 SET lastname = 'Smith' WHERE firstname = 'Peter' 
  DELETE FROM t1 
  SELECT * FROM t1 WHERE salary > 45000 
  

• Data Control Language (DCL) statements grant or revoke privileges or authorities to perform database operations on the objects in your database. For example:

  
  GRANT  select ON employee TO peter 
  REVOKE update ON employee FROM paul 
  

NOTE

SQL statements are commonly referred to simply as "statements" in most RDBMS books. For detailed syntax of SQL statements, see the

DB2 UDB SQL Reference manual.

NOTE

The file

Command_and_SQL_Examples.pdf on the CD-ROM accompanying this book includes a list of all SQL statements and DB2 commands and has examples for each one.

2.1.2. DB2 System Commands

You use DB2 system commands for many purposes, including starting services or processes, invoking utilities, and configuring parameters. Most DB2 system commands do not require the instancethe DB2 server engine processto be started (instances are discussed later in this chapter). DB2 system command names have the format

db2 
x 

where

x represents one or more characters. For example:

db2start 
db2set 
db2icrt 

NOTE

Many DB2 system commands provide a quick way to obtain syntax and help information about the command by using the

-h option. For example, typing

db2set h displays the syntax of the

db2set command, with an explanation of its optional parameters.

2.1.3. DB2 Command Line Processor (CLP) Commands

DB2 CLP commands are processed by the CLP tool (introduced in the next section). These commands typically require the instance to be started, and they can be used for database and instance monitoring and for parameter configuration. For example:

list applications 
create database 
catalog tcpip node 

You invoke the Command Line Processor by entering

db2 at an operating system prompt. If you enter

db2 and press the Enter key, you would be working with the CLP in interactive mode, and you can enter the CLP commands as shown above. On the other hand, if you don't want to work with the CLP in interactive mode, prefix each CLP command with

db2 . For example:

db2 list applications 
db2 create database 
db2 catalog tcpip node 

Many books, including this one, display CLP commands as db2

CLP_command for this reason. Chapter 4, Using the DB2 Tools, explains the CLP in greater detail.

Chapter 4, Using the DB2 Tools.

NOTE

A quick way to obtain syntax and help information about a CLP command is to use the question mark (

? ) character followed by the command. For example:

db2 ? catalog tcpip node 

or just

db2 ? catalog 

For detailed syntax of a command, see the

DB2 UDB Command Reference manual.