Section 2.1. SQL Statements and DB2 Commands

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

NOTE

SQL statements and DB2 commands can be specified in uppercase or lowercase. However, in Linux/UNIX some of the commands are case-sensitive; see Appendix B for a detailed explanation of the use of uppercase versus lowercase in DB2.

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

 db2x 

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.

NOTE

On the Windows platform, db2 must be entered in the DB2 Command Window, not at the operating system prompt. The DB2 Command Window and the DB2 CLP are discussed in detail in 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.