2.0 Section 2. Using SQL Queries Subtopics: 2.1 Chapter 2-1. Retrieving Data 2.2 Chapter 2-2.

Working with Tables and Modifying Data 2.3 Chapter 2-3. Joining Data from More Than One Table 2.4 Chapter 2-4. Using Subqueries 2.5 Chapter 2-5. Executing SQL from Your Terminal Using SPUFI 2.1 Chapter 2-1. Retrieving Data You can retrieve data using the SQL statement SELECT to specify a result table. You can embed SELECT statements in programs as character strings or as parts of other SQL statements. This chapter describes how to use SELECT statements interactively for exploring relational data or for testing SELECT statements that you plan to embed in programs. Subtopics: 2.1.1 Result Tables 2.1.2 Data Types 2.1.3 Selecting Columns: SELECT 2.1.4 Selecting Rows Using Search Conditions: WHERE 2.1.5 Using Operations and Functions 2.1.6 Putting the Rows in Order: ORDER BY 2.1.7 Summarizing Group Values: GROUP BY 2.1.8 Subjecting Groups to Conditions: HAVING 2.1.9 Merging Lists of Values: UNION 2.1.10 Special Registers 2.1.11 Finding Information in the DB2 Catalog 2.1.1 Result Tables The data retrieved through SQL is always in the form of a table. The DB2 library calls this table a result table. Like the tables from which you retrieve the data, a result table has rows and columns. A program fetches this data one row at a time.

Example: This SELECT statement: SELECT LASTNAME, FIRSTNME, PHONENO FROM DSN8410.EMP WHERE WORKDEPT = 'D11' ORDER BY LASTNAME; gives this result: LASTNAME ADAMSON BROWN JOHN JONES LUTZ PIANKA SCOUTTEN STERN WALKER YAMAMOTO YOSHIMURA 2.1.2 Data Types When you create a DB2 table, you define each column to have a specific data type. The data type of a column determines what you can and cannot do with it. When you perform operations on columns, the data must be compatible with the data type of the referenced column. For example, you cannot insert character data, like a last name, into a column whose data type is numeric. Similarly, you cannot compare columns containing incompatible data types. To better understand the concepts presented in this chapter, you must know the data types of the columns to which an example refers. 2.1.3 Selecting Columns: SELECT You have several options for selecting columns from a database for your result tables. This section describes how to select columns using a variety of techniques. FIRSTNME BRUCE DAVID REBA WILLIAM JENNIFER ELIZABETH MARILYN IRVING JAMES KIYOSHI MASATOSHI 6423 2986 2890 2890 PHONENO 4510 4501 0672 0942 0672 3782 1682 =============== ============ ========

Subtopics: 2.1.3.1 Selecting All Columns: SELECT * 2.1.3.2 Selecting Some Columns: SELECT column-name 2.1.3.3 Selecting Derived Columns: SELECT expression 2.1.3.4 Eliminating Duplicate Rows: DISTINCT 2.1.3.5 Naming Result Columns: AS 2.1.3.6 SQL Rules for Processing a SELECT Statement 2.1.3.1 Selecting All Columns: SELECT * You do not need to know the column names to select DB2 data. Use an asterisk (*) in the SELECT clause to indicate "all columns" from each selected row of the named table. This SQL statement: SELECT * FROM DSN8410.DEPT; gives this result: DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION ====== ==================================== ====== ======== ======== A00 B01 C01 D01 D11 D21 E01 E11 E21 F22 G22 H22 I22 J22 SPIFFY COMPUTER SERVICE DIV. PLANNING INFORMATION CENTER DEVELOPMENT CENTER MANUFACTURING SYSTEMS ADMINISTRATION SYSTEMS SUPPORT SERVICES OPERATIONS SOFTWARE SUPPORT BRANCH OFFICE F2 BRANCH OFFICE G2 BRANCH OFFICE H2 BRANCH OFFICE I2 BRANCH OFFICE J2 000020 A00 000010 A00 -----------------------------------000030 A00 ------ A00 000060 D01 000070 D01 000050 A00 000090 E01 ------ E01 ------ E01 ------ E01 ------ E01 ------ E01 000100 E01 ----------------------------------------------------------------

The SELECT statement example retrieves data from each column (SELECT *) of each retrieved row of the DSN8410.DEPT table. Because the example does not specify a WHERE clause, the statement retrieves data from all rows. SELECT * is recommended mostly for use with dynamic SQL and view definitions. You can use SELECT * in static SQL, but this is not recommended; if you add a column to the table to which SELECT * refers, the program might reference columns for which you have not defined receiving host variables. If you list the column names in a static SELECT statement instead of using an asterisk, you can avoid the problem just mentioned. You can also see the relationship between the receiving host variables and the columns in the result table. 2.1.3.2 Selecting Some Columns: SELECT column-name Select the column or columns you want by naming each column. All columns appear in the order you specify, not in their order in the table. This SQL statement: SELECT MGRNO, DEPTNO FROM DSN8410.DEPT; gives this result: MGRNO DEPTNO ====== ====== 000010 A00 000020 B01 000030 C01 ------ D01 000050 E01 000060 D11 000070 D21 000090 E11 000100 E21

------ F22 ------ G22 ------ H22 ------ I22 ------ J22 The example SELECT statement retrieves data contained in the two named columns of each row in the DSN8410.DEPT table. You can select data from one column or as many as 750 columns with a single SELECT statement. | 2.1.3.3 Selecting Derived Columns: SELECT expression | You can select columns derived from a constant, an expression, or a | function. This SQL statement: | SELECT EMPNO, (SALARY + BONUS + COMM) | FROM DSN8410.EMP;

| selects data from all rows in the DSN8410.EMP table, calculates the result | of the expression, and returns the columns in the order indicated in the | SELECT statement. In the result table, derived columns, such as (SALARY + | BONUS + COMM), do not have names. The AS clause allows you to give names | to unnamed columns. See "Naming Result Columns: AS" for information on | the AS clause. 2.1.3.4 Eliminating Duplicate Rows: DISTINCT The DISTINCT keyword removes duplicate rows from your result, so that each row contains unique data. The following SELECT statement lists the department numbers of the administrating departments: SELECT DISTINCT ADMRDEPT FROM DSN8410.DEPT; which produces the following result:

ADMRDEPT ======== A00 D01 E01 Compare the result of the previous example with this one:

SELECT ADMRDEPT FROM DSN8410.DEPT; which gives this result: ADMRDEPT ======== A00 A00 A00 A00 A00 D01 D01 E01 E01 E01 E01 E01 E01 E01 When the DISTINCT keyword is omitted, the ADMRDEPT column value of each selected row is returned, even though the result includes several duplicate rows. 2.1.3.5 Naming Result Columns: AS With AS, you can name result columns in a SELECT clause. This is particularly useful for a column derived from an expression or a function. The following examples show different ways to use the AS clause.

Example 1: The expression SALARY+BONUS+COMM has the name TOTAL_SAL. SELECT SALARY+BONUS+COMM AS TOTAL_SAL FROM DSN8410.EMP ORDER BY TOTAL_SAL; Example 2: You can specify result column names in the select-clause of a CREATE VIEW statement. You do not have to supply the column list of CREATE VIEW because the AS keyword names the derived column. The columns in the view EMP_SAL are EMPNO and TOTAL_SAL. CREATE VIEW EMP_SAL AS SELECT EMPNO,SALARY+BONUS+COMM AS TOTAL_SAL FROM DSN8410.EMP; Example 3: You can use the AS clause to give the same name to corresponding columns of a union. The third result column from the union of the two tables also has the name TOTAL_VALUE, even though it contains data derevied from columns in the database with different names. In this SQL statement: SELECT 'On hand' AS STATUS, PARTNO, QOH * COST AS TOTAL_VALUE FROM PART_ON_HAND UNION ALL SELECT 'Ordered' AS STATUS, PARTNO, QORDER * COST AS TOTAL_VALUE FROM ORDER_PART ORDER BY PARTNO, TOTAL_VALUE; the union of the two SELECT statements recognizes when a column of the first result table has the same name as a column of the second result table. The column STATUS and the derived column TOTAL_VALUE have the same name in the first and second result tables, and are combined in the union of the two result tables:

STATUS

PARTNO TOTAL_VALUE

----------- ------ ----------On hand Ordered 00557 00557 345.60 150.50

Example 4: Use the AS clause in a FROM clause to assign a name to a derived column that you want to refer to in a GROUP BY clause. Using the AS clause in the first SELECT clause causes an error, because the names assigned in the AS clause do not yet exist when the GROUP BY executes. However, you can use an AS clause of a subselect in the outer GROUP BY clause, because the subselect is at a lower level than the GROUP BY that references the name. This SQL statement: SELECT HIREYEAR, AVG(SALARY) FROM (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY FROM DSN8410.EMP) AS NEWEMP GROUP BY HIREYEAR; names HIREYEAR in the nested table expression, allowing you to use the name of that result column in the GROUP BY clause. 2.1.3.6 SQL Rules for Processing a SELECT Statement The rules of SQL dictate that a SELECT statement generate the same rows as if the clauses in the statement had been evaluated in this order: FROM WHERE GROUP BY HAVING SELECT DB2 does not necessarily process the clauses in this order internally, but the results you get will always look as if they had been processed in this order. Subselects are processed from the innermost to the outermost subselect. The ORDER BY clause can appear only in the outermost SELECT statement. If you use an AS clause to define a name in the outermost SELECT clause, only

the ORDER BY clause can refer to that name. If you use an AS clause in a subselect, you can refer to the name it defines outside of the subselect. *** For example, this SQL statement is not valid: SELECT EMPNO, (SALARY + BONUS + COMM) AS TOTAL_SAL FROM DSN8410.EMP WHERE TOTAL_SAL > 50000; This SQL statement, however, is valid: SELECT EMPNO, (SALARY + BONUS + COMM) AS TOTAL_SAL FROM DSN8410.EMP ORDER BY TOTAL_SAL;

2.1.4 Selecting Rows Using Search Conditions: WHERE Use a WHERE clause to select the rows that meet certain conditions. A WHERE clause specifies a search condition. A search condition consists of one or more predicates. A predicate specifies a test you want DB2 to apply to each table row.When DB2 evaluates a predicate for a row, it yields true, false, or unknown. Results are unknown only if an operand is null. The next sections illustrate different comparison operators that you can use in a predicate in a WHERE clause. The following table lists the comparison operators.

________________________________________________________________________ | Table 2. Comparison Operators Used in Conditions | Type of | comparison | Equal to null | Equal to | Specified | with... | IS NULL |= | Example | | | PHONENO IS NULL | | | | |________________________________________________________________________|

|________________________________|______________|________________________| |________________________________|______________|________________________| | DEPTNO = 'X01'

|________________________________|______________|________________________| | Not equal to | Less than | Less than or equal to | Not less than | Greater than | Greater than or equal to | Not greater than | Similar to another value | | | <> |< | <= | >= |> | >= | <= | LIKE | DEPTNO <> 'X01' | AVG(SALARY) < 30000 | AGE <= 25 | AGE >= 21 | SALARY > 2000 | SALARY >= 5000 | SALARY <= 5000 | | | | | | | |________________________________|______________|________________________| |________________________________|______________|________________________| |________________________________|______________|________________________| |________________________________|______________|________________________| |________________________________|______________|________________________| |________________________________|______________|________________________| |________________________________|______________|________________________| | NAME LIKE '%SMITH%' or | | | | STATUS LIKE 'N_'

|________________________________|______________|________________________| | At least one of two conditions | OR | | | | | < 16000 | AND | HIREDATE < | | HIREDATE < | | | | '1965-01-01' OR SALARY |

|________________________________|______________|________________________| | Both of two conditions | | | | | '1965-01-01' AND | SALARY < 16000 | BETWEEN | AND 40000

|________________________________|______________|________________________| | Between two values | | | SALARY BETWEEN 20000 | | |

|________________________________|______________|________________________| | Equals a value in a set | | | IN (X, Y, Z) | DEPTNO IN ('B01', | 'C01', 'D01') |

|________________________________|______________|________________________| You can also search for rows that do not satisfy one of the above conditions, by using the NOT keyword before the specified condition

2.1.4.1 Selecting Rows with Null Values A null value indicates the absence of a column value in a row. A null value is not the same as zero or all blanks. You can use a WHERE clause to retrieve rows that contain a null value in some column. Specify: WHERE column-name IS NULL You can also use a predicate to screen out null values. Specify: WHERE column-name IS NOT NULL 2.1.4.2 Selecting Rows Using Equalities and Inequalities You can use equal (=), inequality symbols, and NOT to specify search conditions in the WHERE clause.

2.1.4.2.1 Using Equal (=) You can use equal (=) to select rows for which a specified column contains a specified value. For example, to select only the rows where the department number is A00, use WHERE WORKDEPT = 'A00' in your SQL statement: # SELECT FIRSTNME, LASTNAME # # FROM DSN8410.EMP WHERE WORKDEPT = 'A00';

The statement retrieves the department number and the first and last name of each employee in department A00. 2.1.4.2.2 Using Inequalities You can use the following inequalities to specify search conditions: not equal to (<>) less than (<) less than or equal to (<=)

greater than (>) greater than or equal to (>=). To select all employees hired before January 1, 1960, you can use: SELECT HIREDATE, FIRSTNME, LASTNAME FROM DSN8410.EMP WHERE HIREDATE < '1960-01-01'; When strings are compared, DB2 uses EBCDIC collating sequence. The EBCDIC collating sequence is different from the ASCII collating sequence. For example, letters sort before digits in EBCDIC, and after digits in ASCII. 2.1.4.2.3 Using the NOT Keyword with Comparison Operators You can use the NOT keyword to select all rows except the rows identified with the search condition. The NOT keyword must precede the search condition. To select all managers whose compensation is not greater than $30,000, use:

SELECT WORKDEPT, EMPNO FROM DSN8410.EMP WHERE NOT (SALARY + BONUS + COMM) > 30000 AND JOB = 'MANAGER' ORDER BY WORKDEPT; The following WHERE clauses are equivalent: ________________________________________________________________________ | Table 3. Equivalent WHERE Clauses. Using a NOT keyword with | | comparison operators compared to using only comparison operators. | Equivalent Clause | | | | | |

|________________________________________________________________________| | Using NOT |____________________________________|___________________________________| | WHERE NOT DEPTNO = 'A00' | WHERE NOT DEPTNO < 'A00' | WHERE DEPTNO <> 'A00' | WHERE DEPTNO >= 'A00' |____________________________________|___________________________________|

|____________________________________|___________________________________| | WHERE NOT DEPTNO > 'A00' | WHERE NOT DEPTNO <> 'A00' | WHERE NOT DEPTNO <= 'A00' | WHERE NOT DEPTNO >= 'A00' | WHERE DEPTNO <= 'A00' | WHERE DEPTNO = 'A00' | WHERE DEPTNO > 'A00' | WHERE DEPTNO < 'A00' | | | | |____________________________________|___________________________________| |____________________________________|___________________________________| |____________________________________|___________________________________| |____________________________________|___________________________________| You cannot use the NOT keyword directly with the comparison operators. The following WHERE clause results in an error: Wrong: WHERE DEPT NOT = 'A00' You can precede other SQL keywords with NOT: NOT LIKE, NOT IN, and NOT BETWEEN are all acceptable. For example, the following two clauses are equivalent: WHERE MGRNO NOT IN ('000010', '000020') WHERE NOT MGRNO IN ('000010', '000020') *** 2.1.4.3 Selecting Values Similar to a Character String Use LIKE to specify a character string that is similar to the column value of rows you want to select: Use a percent sign (%) to indicate any string of zero or more characters. Use an underscore (_) to indicate any single character. Use the LIKE predicate with character or graphic data only, not with numeric or datetime data. Subtopics: 2.1.4.3.1 Selecting Values Similar to a String of Unknown Characters 2.1.4.3.2 Selecting a Value Similar to a Single Unknown Character 2.1.4.3.3 Selecting a Value Similar to a String Containing a % or an _ 2.1.4.3.1 Selecting Values Similar to a String of Unknown Characters

The percent sign (%) means "any string or no string." The following SQL statement selects data from each row for employees with the initials E H. SELECT FIRSTNME, LASTNAME, WORKDEPT FROM DSN8410.EMP WHERE FIRSTNME LIKE 'E%' AND LASTNAME LIKE 'H%'; The following SQL statement selects data from each row of the department table where the department name contains "CENTER" anywhere in its name. SELECT DEPTNO, DEPTNAME FROM DSN8410.DEPT WHERE DEPTNAME LIKE '%CENTER%'; Assume the DEPTNO column is a three-character column of fixed length. You can use this search condition: ...WHERE DEPTNO LIKE 'E%1'; to return rows with department numbers that begin with E and end with 1. If E1 is a department number, its third character is a blank and does not match the search condition. If you define the DEPTNO column as a three-character column of varying-length, department E1 would match the search condition; varying-length columns can have any number of characters, up to and including the maximum number specified when you create the column. The following SQL statement selects data from each row of the department table where the department number starts with an E and contains a 1. SELECT DEPTNO, DEPTNAME FROM DSN8410.DEPT WHERE DEPTNO LIKE 'E%1%'; 2.1.4.3.2 Selecting a Value Similar to a Single Unknown Character The underscore (_) means "any single character." In the following SQL statement,

SELECT DEPTNO, DEPTNAME FROM DSN8410.DEPT WHERE DEPTNO LIKE 'E_1'; 'E_1' means "E, followed by any character, followed by 1." (Be careful: '_' is an underscore character, not a hyphen.) 'E_1' selects only three-character department numbers that begin with E and end with 1; it does not select 'E1:' if it occurs. The SQL statement below selects data from each row whose four-digit phone number has the first three digits of 378. SELECT LASTNAME, PHONENO FROM DSN8410.EMP WHERE PHONENO LIKE '378_'; 2.1.4.3.3 Selecting a Value Similar to a String Containing a % or an _ To search for a % or an _ as a literal part of your string, use the ESCAPE clause and an escape character with the LIKE predicate. In the following example the ESCAPE '+' indicates that the + is the escape character in the search condition. For example: ...WHERE C1 LIKE 'AAAA+%BBB%' ESCAPE '+'

searches for a string starting with AAAA%BBB. The escape character (+) in front of the first % indicates that the % is a single character and that it is part of the search string. The second %, which an escape character does not precede, indicates that any number of (or no) characters can follow the string. In this example, putting '++' in the string allows you to search for a single plus sign (+) as part of the string. 2.1.4.4 Selecting Rows that Meet More Than One Condition You can use AND, OR, and NOT to combine search conditions. Use AND to specify that the search must satisfy both of the conditions. Use OR to specify that the

search must satisfy at least one of the conditions. Example 1: This example retrieves the employee number, date hired, and salary for each employee hired before 1965 and having a salary of less than $16,000 per year. SELECT EMPNO, HIREDATE, SALARY FROM DSN8410.EMP WHERE HIREDATE < '1965-01-01' AND SALARY < 16000; Example 2: This example retrieves the employee number, date hired, and salary for each employee who either was hired before 1965, or has a salary less than $16,000 per year, or both. SELECT EMPNO, HIREDATE, SALARY FROM DSN8410.EMP WHERE HIREDATE < '1965-01-01' OR SALARY < 16000; Subtopics: 2.1.4.4.1 Using Parenthesis with AND and OR 2.1.4.4.2 Using NOT with AND and OR 2.1.4.4.1 Using Parenthesis with AND and OR If you use more than two conditions with AND or OR, you can use parentheses to specify the order in which you want DB2 to evaluate the search conditions. If you move the parentheses, the meaning of the WHERE clause can change significantly. Example 1: To select the row of each employee that satisfies at least one of the following conditions: The employee's hire date is before 1965 AND salary is less than $20,000. The employee's education level is less than 13. This WHERE clause returns rows for employees 000290, 000310, and 200310: SELECT EMPNO FROM DSN8410.EMP WHERE (HIREDATE < '1965-01-01' AND SALARY < 20000) OR (EDLEVEL < 13);

Example 2: To select the row of each employee that satisfies both of the following conditions: The employee's hire date is before 1965. The employee's salary is less than $20,000 OR the employee's education level is less than 13. This WHERE clause returns rows for employees 000310 and 200310: SELECT EMPNO FROM DSN8410.EMP WHERE HIREDATE < '1965-01-01' AND (SALARY < 20000 OR EDLEVEL < 13); Example 3: The following SQL statement selects the employee number of each employee that satisfies one of the following conditions: Hired before 1965 and salary is less than $20,000 Hired after January 1, 1965, and salary is greater than $40,000. This WHERE clause returns rows for employees 000050, 000310, and 200310. SELECT EMPNO FROM DSN8410.EMP WHERE (HIREDATE < '1965-01-01' AND SALARY < 20000) OR (HIREDATE > '1965-01-01' AND SALARY > 40000); 2.1.4.4.2 Using NOT with AND and OR When using NOT with AND and OR, the placement of the parentheses is important. Example 1: In this example, NOT affects only the first search condition (SALARY >= 50000): SELECT EMPNO, EDLEVEL, JOB FROM DSN8410.EMP WHERE NOT (SALARY >= 50000) AND (EDLEVEL < 18); This SQL statement retrieves the employee number, education level, and job title of each employee who satisfies both of the following conditions: The employee's salary is less than $50,000.

The employee's education level is less than 18. Example 2: To negate a set of predicates, enclose the entire set in parentheses and precede the set with the NOT keyword. SELECT EMPNO, EDLEVEL, JOB FROM DSN8410.EMP WHERE NOT (SALARY >= 50000 AND EDLEVEL >= 18); This SQL statement retrieves the employee number, education level, and job title of each employee who satisfies at least one of the following conditions: The employee's salary is less than $50,000. The employee's education level is less than 18. 2.1.4.5 Using BETWEEN to Specify Ranges to Select You can use BETWEEN to select rows in which a column has a value within two limits. Specify the lower boundary of the BETWEEN predicate first, then the upper boundary. The limits are inclusive. For example, suppose you specify WHERE column-name BETWEEN 6 AND 8 where the value of the column-name column is an integer. DB2 selects all rows whose column-name value is 6, 7, or 8. If you specify a range from a larger number to a smaller number (for example, BETWEEN 8 AND 6), the predicate is always false. Example 1 SELECT DEPTNO, MGRNO FROM DSN8410.DEPT WHERE DEPTNO BETWEEN 'C00' AND 'D31'; The example retrieves the department number and manager number of each department whose number is between C00 and D31. Example 2

SELECT EMPNO, SALARY FROM DSN8410.EMP WHERE SALARY NOT BETWEEN 40000 AND 50000; The example retrieves the employee numbers and the salaries for all employees who either earn less than $40,000 or more than $50,000. You can use the BETWEEN predicate to define a tolerance factor to use when comparing floating-point values. Floating-point numbers are approximations of real numbers. As a result, a simple comparison might not evaluate to true, even if the same value was stored in both the COL1 and COL2 columns: ...WHERE COL1 = COL2 The following example uses a host variable named FUZZ as a tolerance factor: ...WHERE COL1 BETWEEN (COL2 - :FUZZ) AND (COL2 + :FUZZ) 2.1.4.6 Using IN to Specify Values in a List You can use the IN predicate to select each row that has a column value equal to one of several listed values. In the values list after IN, the order of the items is not important and does not affect the ordering of the result. Enclose the entire list in parentheses, and separate items by commas; the blanks are optional. SELECT DEPTNO, MGRNO FROM DSN8410.DEPT WHERE DEPTNO IN ('B01', 'C01', 'D01'); The example retrieves the department number and manager number for departments B01, C01, and D01. Using the IN predicate gives the same results as a much longer set of conditions separated by the OR keyword. For example, you could code the WHERE clause in the SELECT statement above as:

WHERE DEPTNO = 'B01' OR DEPTNO = 'C01' OR DEPTNO = 'D01' However, the IN predicate saves coding time and is easier to understand. The SQL statement below finds any sex code not properly entered. SELECT EMPNO, SEX FROM DSN8410.EMP WHERE SEX NOT IN ('F', 'M'); 2.1.5 Using Operations and Functions You can use operations and functions to control the appearance and values of rows and columns in your result tables. This section discusses each type of operation or function. Subtopics: 2.1.5.1 Concatenating Strings: CONCAT 2.1.5.2 Calculating Values in a Column or Across Columns 2.1.5.3 Using Column Functions 2.1.5.4 Using Scalar Functions 2.1.5.1 Concatenating Strings: CONCAT You can concatenate strings by using the CONCAT keyword. You can use CONCAT in any string expression. For example, SELECT LASTNAME CONCAT ',' CONCAT FIRSTNME FROM DSN8410.EMP; concatenates the last name, comma, and first name of each result row. 2.1.5.2 Calculating Values in a Column or Across Columns You can perform calculations on numeric or datetime data. See Chapter 3 of SQL Reference for detailed information about calculations involving date, time, and timestamp data.

Subtopics: 2.1.5.2.1 Using Numeric Data 2.1.5.2.2 Choosing between 15- and 31-Digit Precision for Decimal Numbers 2.1.5.2.3 Using Datetime Data 2.1.5.2.1 Using Numeric Data You can retrieve calculated values, just as you display column values, for selected rows. For example, if you write the following SQL statement: SELECT EMPNO, SALARY / 12 AS MONTHLY_SAL, SALARY / 52 AS WEEKLY_SAL FROM DSN8410.EMP WHERE WORKDEPT = 'A00'; you get this result: EMPNO MONTHLY_SAL 000010 4395.83333333 000110 3875.00000000 000120 2437.50000000 200010 3875.00000000 200120 2437.50000000 WEEKLY_SAL

====== ============== ============== 1014.42307692 894.23076923 562.50000000 894.23076923 562.50000000

The SELECT statement example displays the monthly and weekly salaries of employees in department A00. To retrieve the department number, employee number, salary, bonus, and commission for those employees whose combined bonus and commission is greater than $5000, write: SELECT WORKDEPT, EMPNO, SALARY, BONUS, COMM FROM DSN8410.EMP WHERE BONUS + COMM > 5000;

which gives the following result: WORKDEPT EMPNO SALARY A00 A00 000010 200010 52750.00 46500.00 BONUS 4220.00 4220.00 COMM ======== ====== ============ ============ ============ 1000.00 1000.00

2.1.5.2.2 Choosing between 15- and 31-Digit Precision for Decimal Numbers DB2 allows two sets of rules for determining the precision and scale of the result of an operation with decimal numbers. DEC15 rules allow a maximum precision of 15 digits in the result of an operation. Those rules are in effect when both operands have precisions less than 15, unless one of the circumstances that imply DEC31 rules applies. DEC31 rules allow a maximum precision of 31 digits in the result. Those rules are in effect if any of the following is true: Either operand of the operation has a precision greater than 15. # The operation is in a dynamic SQL statement, the installation # option for DECIMAL ARITHMETIC on panel DSNTIPF is DEC31 or 31, and # the value of DSNHDECM parameter DYNRULS is YES. # The operation is in a dynamic SQL statement, the dynamic SQL # statement is in an application precompiled with option DEC(31) and # bound with option DYNAMICRULES(BIND), and the DSNHDECM parameter # DYNRULS is NO. # The operation is in a dynamic SQL statement, the bind option # DYNAMICRULES(RUN) is specified, and the installation option for | DECIMAL ARITHMETIC, chosen on panel DSNTIPF, is DEC31 or 31. | The operation is in an embedded (static) SQL statement that you | precompiled with the DEC(31) option, or with the default for that | option when the install option DECIMAL ARITHMETIC is DEC31 or 31. | The choice of whether to use DEC15 or DEC31 is a trade-off. Choose:

| DEC15 to avoid an error when the calculated scale of the result of a | simple multiply or divide operation is less than 0. Although this | error can occur with either set of rules, it is more common with DEC31 | rules. | DEC31 to reduce the chance of overflow, or when dealing with | precisions greater than 15. | What You Can Do: For static SQL statements, the simplest fix is to | override DEC31 rules by specifying the precompiler option DEC(15). That | reduces the probability of errors for statements embedded in the program. # If the bind option DYNAMICRULES(BIND) applies for dynamic SQL statements, # and the value of DSNHDECM parameter DYNRULS is NO, you can use the | precompiler option DEC(15) to override DEC31 rules. | For a dynamic statement, or for a single static statement, use the scalar | function DECIMAL to specify values of the precision and scale for a result | that causes no errors. 2.1.5.2.3 Using Datetime Data If you use dates, assign datetime data types to all columns containing dates. This not only allows you to do more with your table but it can save you from problems like the following: Suppose that in creating the table YEMP (described in "Creating a New Department Table" in topic 2.2.1.1.3), you assign data type DECIMAL(8,0) to the BIRTHDATE column and then fill it with dates of the form yyyymmdd. You then execute the following query to determine who is 27 years old or older: SELECT EMPNO, FIRSTNME, LASTNAME FROM YEMP WHERE YEAR(CURRENT DATE - BIRTHDATE) > 26; Suppose now that, at the time the query executes, one person represented in YEMP is 27 years, 0 months, and 29 days old but does not show in the results. What happens is this: If the data type of the column is DECIMAL(8,0) DB2 regards BIRTHDATE as a

duration, and therefore calculates CURRENT DATE - BIRTHDATE as a date. (A duration is a number representing an interval of time. As a date, the result of the calculation (27/00/29) is not legitimate, so it transforms into 26/12/29. Based on this erroneous transformation, DB2 then recognizes the person as 26 years old, not 27. You can resolve the problem by creating the table with BIRTHDATE as a date column, so that CURRENT DATE BIRTHDATE results in a duration.

If you have stored date data in columns with types other than DATE or TIMESTAMP, you can use scalar functions to convert the stored data. The following examples illustrate a few conversion techniques: | For data stored as yyyymmdd in a DECIMAL(8,0) column named C2, use: | The DIGITS function to convert a numeric value to character format | The SUBSTR function to isolate pieces of the value | CONCAT to reassemble the pieces in ISO format (with hyphens) | The DATE function to have DB2 interpret the resulting character | string value ('yyyy-mm-dd') as a date. | For example: | | | | | DATE(SUBSTR(DIGITS(C2),1,4) CONCAT '-' '-' CONCAT CONCAT SUBSTR(DIGITS(C2),5,2) CONCAT SUBSTR(DIGITS(C2)7,2)) | For data stored as yyyynnn in a DECIMAL(7,0) column named C3, use: | The DIGITS function to convert the numeric value to character | format | The DATE function to have DB2 interpret the resulting character | string value ('yyyynnn') as a date. | DATE(DIGITS(C3)) | For data stored as yynnn in a DECIMAL(5,0) column named C4, use: | The DIGITS function to convert the numeric value to character | format | The character string constant '19' for the first part of the year

| CONCAT to reassemble the pieces in ISO format | The DATE function to have DB2 interpret the resulting character | string value ('19yynnn') as a date. | DATE('19' CONCAT DIGITS(C4))

2.1.5.3 Using Column Functions A column function produces a single value for a group of rows. You can # use the SQL column functions to calculate values based on entire columns # of data. The calculated values are from selected rows only (all rows that satisfy the WHERE clause). The column functions are as follows: SUM Returns the total value. MIN Returns the minimum value. AVG Returns the average value. MAX Returns the maximum value. COUNT Returns the number of selected rows. The following SQL statement calculates for department D11, the sum of employee salaries, the minimum, average, and maximum salary, and the count of employees in the department: SELECT SUM(SALARY) AS SUMSAL, MIN(SALARY) AS MINSAL, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL, COUNT(*) AS CNTSAL FROM DSN8410.EMP WHERE WORKDEPT = 'D11'; The following result is displayed:

SUMSAL

MINSAL

AVGSAL

MAXSAL

CNTSAL

========= ========= =============== ========= ======== 276620.00 18270.00 25147.27272727 32250.00 11 You can use DISTINCT with the SUM, AVG, and COUNT functions. DISTINCT means that the selected function operates on only the unique values in a column. Using DISTINCT with the MAX and MIN functions has no effect on the result and is not advised. You can use SUM and AVG only with numbers. You can use MIN, MAX, and COUNT with any data type. The following SQL statement counts the number of employees described in the table. SELECT COUNT(*) FROM DSN8410.EMP; This SQL statement calculates the average education level of employees in a set of departments. SELECT AVG(EDLEVEL) FROM DSN8410.EMP WHERE WORKDEPT LIKE '_0_'; The SQL statement below counts the different jobs in the DSN8410.EMP table. SELECT COUNT(DISTINCT JOB) FROM DSN8410.EMP; 2.1.5.4 Using Scalar Functions A scalar function also produces a single value, but unlike the argument of a column function, an argument of a scalar function is a single value. The SQL statement below returns the year each employee in a particular department was hired: SELECT YEAR(HIREDATE) AS HIREYEAR FROM DSN8410.EMP WHERE WORKDEPT = 'A00'; gives this result:

HIREYEAR ========= 1972 1965 1965 1958 1963 The scalar function YEAR produces a single scalar value for each row of DSN8410.EMP that satisfies the search condition. In this example, five rows satisfy the search condition, so YEAR results in five scalar values. Table 4 shows the scalar functions that you can use. For complete details on using these functions see Chapter 4 of SQL Reference. ________________________________________________________________________ | Table 4. Scalar Functions | Scalar | Function | CHAR | | DATE | | DAY | | DAYS | | Returns... | | | Example | | | | |________________________________________________________________________|

|_____________|_____________________________|____________________________| | a string representation of | CHAR(HIREDATE) | its first argument. | | | DATE('1989-03-02') | | DAY(DATE1 - DATE2) | | | | | |

|_____________|_____________________________|____________________________| | a date derived from its | argument. | the day part of its | argument. |

|_____________|_____________________________|____________________________|

|_____________|_____________________________|____________________________| | an integer representation | DAYS('1990-01-08') | of its argument. | DAYS(HIREDATE) + 1

|_____________|_____________________________|____________________________| | DECIMAL || | a decimal representation of | DECIMAL(AVG(SALARY), 8,2) | | | | DIGITS(COLUMNX) | | its first argument. | a character string

|_____________|_____________________________|____________________________| | DIGITS

| |

| representation of its | argument. | floating-point |

| |

|_____________|_____________________________|____________________________| | FLOAT | | | HEX | | | HOUR | | FLOAT(SALARY)/COMM | | | | HEX(BCHARCOL) | | | | HOUR(TIMECOL) > 12 | | | | | | | | representation of its | argument. | a hexadecimal | representation of its | argument.

|_____________|_____________________________|____________________________|

|_____________|_____________________________|____________________________| | the hour part of its | argument. |

|_____________|_____________________________|____________________________| | INTEGER | | an integer representation | INTEGER(AVG(SALARY)+.5) | | | | of its argument.

|_____________|_____________________________|____________________________| | LENGTH | the length of its argument. | LENGTH(ADDRESS) |_____________|_____________________________|____________________________| | MICROSECOND | the microsecond part of its | MICROSECOND(TSTMPCOL) <> 0 | | | argument. | | | MINUTE(TIMECOL) = 0 | | MONTH(BIRTHDATE) = 5 | | SECOND(RECEIVED) | | SUBSTR(FIRSTNME,2,3) | TIME(TSTMPCOL) < | | | | | | |_____________|_____________________________|____________________________| | MINUTE | | the minute part of its | | argument.

|_____________|_____________________________|____________________________| | MONTH | | the month part of its | | argument.

|_____________|_____________________________|____________________________| | SECOND | | the seconds part of its | | argument.

|_____________|_____________________________|____________________________| | SUBSTR | TIME | | a substring of a string. | a time derived from its | argument. |_____________|_____________________________|____________________________| | '13:00:00'

|_____________|_____________________________|____________________________| | TIMESTAMP | a timestamp derived from | TIMESTAMP(DATECOL, | | its argument or arguments. | TIMECOL) | | |

|_____________|_____________________________|____________________________| | VALUE | | the first argument that is | VALUE(SMLLINT1,100) + | SMLLINT2 > 1000 | | not null.

|_____________|_____________________________|____________________________| | VARGRAPHIC | a graphic string from its | VARGRAPHIC (:MIXEDSTRING) | | | YEAR | | argument. | | | YEAR(BIRTHDATE) = 1956 | | |_____________|_____________________________|____________________________| | the year part of its | argument. |

|_____________|_____________________________|____________________________| 2.1.5.4.1 Examples CHAR The CHAR function returns a string representation of a datetime value or a decimal number. This can be useful when the precision of the number is greater than the maximum precision supported by the host language. For example, if you have a number with a precision greater than 18, you can retrieve it into a host variable by using the CHAR function. Specifically, if BIGDECIMAL is a DECIMAL(33) column, you can define a fixed-length string, BIGSTRING CHAR(33), and execute the following statement: SELECT CHAR(MAX(BIGDECIMAL)) INTO :BIGSTRING FROM T; CHAR also returns a character string representation of a datetime value in a specified format. For example: SELECT CHAR(HIREDATE,USA) FROM DSN8410.EMP WHERE EMPNO='000010';

returns 01/01/1965. DECIMAL The DECIMAL function returns a decimal representation of a | numeric or character value. For example, DECIMAL can transform an integer value so that you can use it as a duration. Assume that the host variable PERIOD is of type INTEGER. The following example selects all of the starting dates (PRSTDATE) from the DSN8410.PROJ table and adds to them a duration specified in a host variable (PERIOD). To use the integer value in PERIOD as a duration, you must first make sure that DB2 interprets it as DECIMAL(8,0): EXEC SQL SELECT PRSTDATE + DECIMAL(:PERIOD,8) FROM DSN8410.PROJ; | You can also use the DECIMAL function to transform a character | string to a numeric value. The character string you transform | must conform to the rules for forming an SQL integer or decimal | constant. For information on these rules, see Chapter 3 of SQL | Reference. | Suppose you want to identify all employees that have a telephone | number that is evenly divisible by 13. However, the table | defines PHONENO as CHAR(4). To identify those employees, you | can use the query:

SELECT EMPNO, LASTNAME, PHONENO FROM DSN8410.EMP WHERE DECIMAL(PHONENO,4) = INTEGER(DECIMAL(PHONENO,4)/13) * 13; VALUE VALUE can return a chosen value in place of a null value. For example, the following SQL statement selects values from all the rows in table DSN8410.DEPT. If the department manager, MGRNO is missing (that is, null),

then a value of 'ABSENT' returns: SELECT DEPTNO, DEPTNAME, VALUE(MGRNO, 'ABSENT') FROM DSN8410.DEPT; 2.1.5.4.2 Nesting Column and Scalar Functions You can nest functions in the following ways: Scalar functions within scalar functions For example, you want to know the month and day of hire for a particular employee in department E11, and you want the result in USA format.

SELECT SUBSTR((CHAR(HIREDATE, USA)),1,5) FROM DSN8410.EMP WHERE LASTNAME = 'SMITH' AND WORKDEPT = 'E11'; gives the following result: 06/19 Scalar functions within column functions The argument of a column function must refer to a column; therefore, if that argument is a scalar function, the scalar function must refer to a column. For example, you want to know the average hiring age of employees in department A00. This statement: SELECT AVG(DECIMAL(YEAR(HIREDATE - BIRTHDATE))) FROM DSN8410.EMP WHERE WORKDEPT = 'A00'; gives the following result: 28.0 The actual form of the above result depends on how you define the host variable to which you assign the result (in this case, DECIMAL(3,1)). Column functions within scalar functions

For example, you want to know the hiring year that the last employee hired in department A00. This statement: SELECT YEAR(MAX(HIREDATE)) FROM DSN8410.EMP WHERE WORKDEPT = 'A00'; gives this result: 1972 2.1.6 Putting the Rows in Order: ORDER BY ORDER BY lets you specify the order for retrieving rows. Subtopics: 2.1.6.1 Specifying the Column Names 2.1.6.2 Referencing Derived Columns 2.1.6.1 Specifying the Column Names The order of the selected rows depends on the column you identify in the ORDER BY clause; this column is the ordering column. You can identify more than one column. You can list the rows in ascending or descending order. Null values appear last in an ascending sort and first in a descending sort. DB2 sorts strings in the EBCDIC collating sequence, sorts numbers algebraically, and sorts datetime values chronologically. Subtopics: 2.1.6.1.1 Listing Rows in Ascending Order 2.1.6.1.2 Listing Rows in Descending Order 2.1.6.1.3 Ordering by More Than One Column 2.1.6.1.1 Listing Rows in Ascending Order To retrieve the result in ascending order specify ASC. For example, to retrieve the employee numbers, last names, and hire dates of employees in department A00 in ascending order of hire dates, use the following SQL statement: SELECT EMPNO, LASTNAME, HIREDATE FROM DSN8410.EMP WHERE WORKDEPT = 'A00' ORDER BY HIREDATE ASC;

This is the result: EMPNO LASTNAME 000110 LUCCHESI 000120 O'CONNELL 000010 HAAS 200010 HEMMINGER 200120 ORLANDO default sorting order. 2.1.6.1.2 Listing Rows in Descending Order To put the rows in descending order, specify DESC. For example, to retrieve the department numbers, last names, and employee numbers of female employees in descending order of department numbers, use the following SQL statement: SELECT WORKDEPT, LASTNAME, EMPNO FROM DSN8410.EMP WHERE SEX = 'F' ORDER BY WORKDEPT DESC; It gives you this result: WORKDEPT LASTNAME E21 E11 E11 E11 E11 E11 D21 D21 D21 WONG HENDERSON SCHNEIDER SETRIGHT SCHWARTZ SPRINGER PULASKI JOHNSON PEREZ 200330 000090 000280 000310 200280 200310 000070 000260 000270 EMPNO HIREDATE 1958-05-16 1963-12-05 1965-01-01 1965-01-01 1972-05-05

====== =============== ==========

The example retrieves data showing the seniority of employees. ASC is the

======== =============== ======

D11 D11 D11 D11 C01 C01 C01 C01 A00 A00

PIANKA SCOUTTEN LUTZ JOHN KWAN QUINTANA NICHOLLS NATZ HAAS HEMMINGER

000160 000180 000220 200220 000030 000130 000140 200140 000010 200010

2.1.6.1.3 Ordering by More Than One Column To order the rows by more than one column's value, use more than one column name in the ORDER BY clause. When several rows have the same first ordering column value, those rows are in order of the second column you identify in the ORDER BY clause, and then on the third ordering column, and so on. For example, there is a difference between the results of the following two SELECT statements. The first one orders selected rows by job and next by education level. The second SELECT statement orders selected rows by education level and next by job. Example 1: This SQL statement: SELECT JOB, EDLEVEL, LASTNAME FROM DSN8410.EMP WHERE WORKDEPT = 'E21' ORDER BY JOB, EDLEVEL; gives this result: JOB EDLEVEL LASTNAME LEE WONG GOUNOT ALONZO MEHTA SPENSER ======== ======= =============== FIELDREP 14 FIELDREP 14 FIELDREP 16 FIELDREP 16 FIELDREP 16 MANAGER 14

Example 2: This SQL statement: SELECT JOB, EDLEVEL, LASTNAME FROM DSN8410.EMP WHERE WORKDEPT = 'E21' ORDER BY EDLEVEL, JOB; gives this result: JOB EDLEVEL LASTNAME LEE WONG SPENSER MEHTA GOUNOT ALONZO ======== ======= =============== FIELDREP 14 FIELDREP 14 MANAGER 14 FIELDREP 16 FIELDREP 16 FIELDREP 16

You can also use a field procedure to change the normal collating sequence. See SQL Reference and Administration Guide for more detailed information about sorting (string comparisons) and field procedures. The ORDER BY clause can only reference columns specified in the SELECT clause.

2.1.6.2 Referencing Derived Columns If you use the AS clause to name an unnamed column in a SELECT statement, you can use that name in the ORDER BY clause. For example, the following SQL statement orders the selected information by total salary: SELECT EMPNO, (SALARY + BONUS + COMM) AS TOTAL_SAL FROM DSN8410.EMP ORDER BY TOTAL_SAL; 2.1.7 Summarizing Group Values: GROUP BY Use GROUP BY to group rows by the values of one or more columns. You can then apply column functions to each group.

Except for the columns named in the GROUP BY clause, the SELECT statement must specify any other selected columns as an operand of one of the column functions. The following SQL statement lists, for each department, the lowest and highest education level within that department. SELECT WORKDEPT, MIN(EDLEVEL), MAX(EDLEVEL) FROM DSN8410.EMP GROUP BY WORKDEPT; If a column you specify in the GROUP BY clause contains null values, DB2 considers those null values to be equal. Thus, all nulls form a single group. When it is used, the GROUP BY clause follows the FROM clause and any WHERE clause, and precedes the ORDER BY clause. You can also group the rows by the values of more than one column. For example, the following statement finds the average salary for men and women in departments A00 and C01: | SELECT WORKDEPT, SEX, AVG(SALARY) AS AVG_SALARY FROM DSN8410.EMP WHERE WORKDEPT IN ('A00', 'C01') GROUP BY WORKDEPT, SEX; gives this result: | WORKDEPT SEX AVG_SALARY ======== === =========== A00 A00 C01 F M F 49625.00000000 35000.00000000 29722.50000000

DB2 groups the rows first by department number and next (within each department) by sex before DB2 derives the average SALARY value for each group. 2.1.8 Subjecting Groups to Conditions: HAVING Use HAVING to specify a search condition that each retrieved group must satisfy. The HAVING clause acts like a WHERE clause for groups, and can contain the same

kind of search conditions you can specify in a WHERE clause. The search condition in the HAVING clause tests properties of each group rather than properties of individual rows in the group. This SQL statement: SELECT WORKDEPT, AVG(SALARY) AS AVG_SALARY FROM DSN8410.EMP GROUP BY WORKDEPT HAVING COUNT(*) > 1 ORDER BY WORKDEPT; gives this result: WORKDEPT AVG_SALARY ======== A00 C01 D11 D21 E11 E21 =============== 40850.00000000 29722.50000000 25147.27272727 25668.57142857 21020.00000000 24086.66666666

Compare the preceding example with the second example shown in "Summarizing Group Values: GROUP BY" in topic 2.1.7. The HAVING COUNT(*) > 1 clause ensures that only departments with more than one member display. (In this case, departments B01 and E01 do not display.)

The HAVING clause tests a property of the group. For example, you could use it to retrieve the average salary and minimum education level of women in each department in which all female employees have an education level greater than or equal to 16. Assuming you only want results from departments A00 and D11, the following SQL statement tests the group property, MIN(EDLEVEL):

SELECT WORKDEPT, AVG(SALARY) AS AVG_SALARY, MIN(EDLEVEL) AS MIN_EDLEVEL FROM DSN8410.EMP WHERE SEX = 'F' AND WORKDEPT IN ('A00', 'D11') GROUP BY WORKDEPT HAVING MIN(EDLEVEL) >= 16; The SQL statement above gives this result: | WORKDEPT AVG_SALARY A00 D11 49625.00000000 18 25817.50000000 17 MIN_EDLEVEL

======== =============== ============

When you specify both GROUP BY and HAVING, the HAVING clause must follow the GROUP BY clause. A function in a HAVING clause can include DISTINCT if you have not used DISTINCT anywhere else in the same SELECT statement. You can also connect multiple predicates in a HAVING clause with AND and OR, and you can use NOT for any predicate of a search condition. For more information, see "Selecting Rows that Meet More Than One Condition" in topic 2.1.4.4. 2.1.9 Merging Lists of Values: UNION Using the UNION keyword, you can combine two or more SELECT statements to form a single result table. When DB2 encounters the UNION keyword, it processes each SELECT statement to form an interim result table, and then combines the interim result table of each statement. If you use UNION to combine two columns with the same name, the result table inherits that name. When you use the UNION statement, the SQLNAME field of the SQLDA contains the column names of the first operand. 2.1.9.1 Using UNION to Eliminate Duplicates You can use UNION to eliminate duplicates when merging lists of values obtained from several tables. For example, you can obtain a combined list of employee numbers that includes both of the following: People in department D11 People whose assignments include projects MA2112, MA2113, and AD3111.

For example, this SQL statement: SELECT EMPNO FROM DSN8410.EMP WHERE WORKDEPT = 'D11' UNION SELECT EMPNO FROM DSN8410.EMPPROJACT WHERE PROJNO = 'MA2112' OR PROJNO = 'MA2113' OR PROJNO = 'AD3111' ORDER BY EMPNO; gives the combined result table containing employee numbers in ascending order with no duplicates listed. If you have an ORDER BY clause, it must appear after the last SELECT statement that is part of the union. In this example, the first column of the final result table determines the final order of the rows. 2.1.9.2 Using UNION ALL to Keep Duplicates If you want to keep duplicates in the result of a UNION, specify the optional keyword ALL after the UNION keyword. This SQL statement: SELECT EMPNO FROM DSN8410.EMP WHERE WORKDEPT = 'D11' UNION ALL SELECT EMPNO FROM DSN8410.EMPPROJACT WHERE PROJNO = 'MA2112' OR PROJNO = 'MA2113' OR PROJNO = 'AD3111' ORDER BY EMPNO; gives the combined result table containing employee numbers in ascending order, and includes duplicate numbers.

2.1.10 Special Registers A special register is a storage area that DB2 defines for a process. You can use the SET statement to change the current value of a register. Where the register's name appears in other SQL statements, the current value of the register replaces the name when the statement executes.

You can specify certain special registers in SQL statements. CURRENT DATE or CURRENT_DATE CURRENT DEGREE CURRENT PACKAGESET CURRENT RULES CURRENT SERVER CURRENT SQLID CURRENT TIME or CURRENT_TIME CURRENT TIMESTAMP or CURRENT_TIMESTAMP CURRENT TIMEZONE USER If you want to see the value in a special register, you can use the SET host-variable statement to assign the value of a special register to a variable in your program 2.1.11 Finding Information in the DB2 Catalog The examples below show you how to access the DB2 system catalog tables to: List the tables that you can access List the column names of a table The contents of the DB2 system catalog tables can be a useful reference tool when you begin to develop an SQL statement or an application program. 2.1.11.1 Displaying a List of Tables You Can Use The catalog table, SYSIBM.SYSTABAUTH, lists table privileges granted to authorization IDs. To display the tables that you have authority to access (by privileges granted either to your authorization ID or to PUBLIC), you can

execute an SQL statement like that shown in the following example. To do this, you must have the SELECT privilege on SYSIBM.SYSTABAUTH. # SELECT DISTINCT TCREATOR, TTNAME # # FROM SYSIBM.SYSTABAUTH WHERE GRANTEE IN (USER, 'PUBLIC', 'PUBLIC*') AND GRANTEETYPE = ' ';

2.1.11.2 Displaying a List of Columns in a Table Another catalog table, SYSIBM.SYSCOLUMNS, describes every column of every table. Suppose you execute the previous example (displaying a list of tables you can access) and now want to display information about table DSN8410.DEPT. To execute the following example, you must have the SELECT privilege on SYSIBM.SYSCOLUMNS. SELECT NAME, COLTYPE, SCALE, LENGTH FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'DEPT' AND TBCREATOR = 'DSN8410'; 2.2 Chapter 2-2. Working with Tables and Modifying Data This chapter discusses the use of these statements: CREATE TABLE and DROP TABLE to control table definitions CREATE TABLE to control: Referential constraints: PRIMARY KEY, FOREIGN KEY, and REFERENCES Check constraints: CHECK and CONSTRAINT CREATE VIEW and DROP VIEW to control view definitions INSERT, UPDATE, and DELETE to modify data in tables. Subtopics: 2.2.1 Working with Tables 2.2.2 Working with Views 2.2.3 Modifying DB2 Data 2.2.1 Working with Tables You might need to create or drop the tables that you are working with. You might create new tables, copy existing tables, add columns, add or drop referential

and check constraints, or make any number of changes. This section discusses how to create and work with tables. Subtopics: 2.2.1.1 Creating Your Own Tables: CREATE TABLE 2.2.1.2 Creating Tables with Primary and Foreign Keys 2.2.1.3 Creating Tables with Check Constraints 2.2.1.4 Dropping Tables: DROP TABLE 2.2.1.1 Creating Your Own Tables: CREATE TABLE Use the CREATE TABLE statement to create a table. The following SQL statement creates a table named PRODUCT:

CREATE TABLE PRODUCT (SERIAL MFGCOST MFGDEPT MARKUP CURDATE CHAR(8) NOT NULL, DESCRIPTION VARCHAR(60) DEFAULT, DECIMAL(8,2), CHAR(3), SMALLINT, DATE DEFAULT);

SALESDEPT CHAR(3),

The elements of the CREATE statement are: CREATE TABLE, which names the table PRODUCT. A list of the columns that make up the table. For each column, specify: The column's name (for example, SERIAL). The data type and length attribute (for example, CHAR(8)). For further information about data types, see "Data Types" in topic 2.1.2. Optionally, a referential constraint or table check constraint. See Subtopics: 2.2.1.1.1 Identifying Defaults 2.2.1.1.2 Creating Work Tables 2.2.1.1.3 Creating a New Department Table

2.2.1.1.4 Creating a New Employee Table 2.2.1.1.1 Identifying Defaults If you want to constrain the inputs or identify the defaults, you can describe the columns using: NOT NULL, when the column cannot contain null values. UNIQUE, when the value for each row must be unique, and the column cannot contain null values. DEFAULT, when the column has one of the following DB2-assigned defaults: For numeric fields, zero is the default value. For fixed-length strings, blank is the default value. For variable-length strings, the empty string (string of zero-length) is the default value. For datetime fields, the current value of the associated special register is the default value. DEFAULT value, when you want to identify one of the following as the default value: A constant USER, which uses the run-time value of the USER special register CURRENT SQLID, which uses the SQL authorization ID of the process NULL You must separate each column description from the next with a comma, and enclose the entire list of column descriptions in parentheses. 2.2.1.1.2 Creating Work Tables Before testing SQL statements that insert, update, and delete rows, you should create work tables (duplicates of the DSN8410.EMP and DSN8410.DEPT tables), so that the original sample tables remain intact. This section shows how to create two work tables and how to fill a work table with the contents of another table. Each example shown in this chapter assumes you logged on using your own authorization ID. The authorization ID qualifies the name of each object you create. For example, if your authorization ID is SMITH, and you create table YDEPT, the name of the table is SMITH.YDEPT. If you want to access table

DSN8410.DEPT, you must refer to it by its complete name. If you want to access your own table YDEPT, you need only to refer to it as "YDEPT". 2.2.1.1.3 Creating a New Department Table Use the following statements to create a new department table called YDEPT, modeled after an existing table called DSN8410.DEPT, and an index for YDEPT: CREATE TABLE YDEPT LIKE DSN8410.DEPT; CREATE UNIQUE INDEX YDEPTX ON YDEPT (DEPTNO); You must use two statements to create YDEPT and its index as shown above. If you want DEPTNO to be a primary key as in the sample table, you must explicitly define the key. Use an ALTER TABLE statement: ALTER TABLE YDEPT PRIMARY KEY(DEPTNO); You can use an INSERT statement with a SELECT clause to copy rows from one