JCL / VSAM Technical Questions

Where can program checkpoints be stored for use in a restart ?
(1) CHCKPOINT DD statement
(2) SYSCHK DD statement
(3) CHKOUT DD statement
(4) CHECK or DD statemen

What is the function of DD name parameter with a 2 part structure; Audit.Report ?
(1) Override the Audit DD statement in the report
(2) Override the Report DD statement in the Audit procedure
(3) Concatenate a dataset onto a DD statement in a called module
(4) Delete the Audit DD statement in the Report



What is the purpose of the ‘DD KEYLEN’ parameter?
(1) Specify the length of a data set key in bytes
(2) Override the key length of an SMS defined VSAM dataset
(3) Override the key length specified in the dataset label
(4) All of the above


Must tape dataset definitions include VOL=SER specifications?
(1) Yes
(2) No
(3) Only for uncataloged datasets
(4) Only for cataloged datasets


In order to continue a job after a return code of 12 in step1, what the step2 EXEC statement include?
(1) CONTINUE
(2) COND = (12,NE)
(3) COND = (12,GE)
(4) COND = (12,EQ)


In SMS datasets, what is the function of the ‘DD MGMTCLAS’ keyword?
(1) Specify a management class for a new dataset
(2) Lookup a storage profile from the class library
(3) Begin a storage class definition block
(4) Alters the default storage profile for the dataset


How can the submitting user’s RACF authority be overridden in a job stream?
(1) Through the USER parameter
(2) Through the USER and PASSWORD parameter
(3) By notifying the console operation to override the authority
(4) Through the RACF parameter


What parameters can be used to limit the number of records written to a SYSOUT dataset ?
(1) LIMIT
(2) OUTLIM
(3) SIZE
(4) MAX


What statement can be used to send data to another MVS JES3 node ?
(1) LINK
(2) CONNECT
(3) XMIT
(4) SEND


If a (+1) generation dataset is created in the first step of a job, how can it be referenced in later steps of the same job for input?
(1) As the (0) generation
(2) As the (+1) generation
(3) As the (-1) generation
(4) Without the generation number


If a generation data set is specified as input without the generation number, what will the input to the DD ?
(1) the most recent generation created
(2) the earliest generation created
(3) a concatenation of all the cataloged generations
4. a jcl error will be returned to the job



What happens to the NOTIFY message when a user logs off the system
before the job is completed?



1)JOB ends with a jcl error.
2)NOTIFY message is created
3)Msg saved until the user logs on again.
4)No messages generated.



How do you Restart a particular step in a JCL? jcl




1)Restart=stepname.procname
2)Restart=procname.stepname
3)Restart=procname
4)Restart=stepname.procname.ddname



What is the parameter to be passed in the job card for the unlimited
time,irrespective of the job class?



1)TIME=0
2)TIME=1440
3)TIME=UNLIMIT
4)TIME=NONE



What is the maximum number of GDG versions we can create?




1)255
2)105
3)15
4)99




If SYMBOLIC PARAMETER is specified on PROC AND EXEC which one is
executed?



1)PROC takes preference
2)EXEC takes preference
3)BOTH takes preference
4)Nothing




How will you make return code to Zero in IDCAMS? jcl




1)using set command
2)using COMPUTE command
3)Using MOVE statement
4) Not possible.




How will you submit JCL via COBOL program ?




1)Using IMS
2)Use a file with sysout=(*,intrdr)
3)Not Possible
4)None of the above



If there are two jobs with class A ,prty=9 and class B with prty=10 l
and if class A has a higer prty which one will execute first?



1)Job with class A and prty=9
2)class B with prty=10
3)Both will not be executed.
4)Both will be executed simultaneously?
5)class has no effect on prty




What will happen if I specify DISP=MOD for a new file? jcl




1)It will change disp as NEW.
2)It will not create.
3)It will show error.
4)None of the above.







How to referback the VOLUME given in previous step? jcl




1)By specifying vol=ser
2)By specifying *.stepname.ddname
3)Both
4)None of the above.

Which of the following is valid in a catolog procedure




1)SYSIN DD *
2)JCLLIB
3)JOBLIB
4)EXEC procname



What is the version number of gdg that is created in the same step




1)+1
2)0
3)-1
4)none




What SUM FIELDS = None will do in sort




1)It will copy dataset without sorting.
2)It will sort the records.
3)It will eleminate the duplicates.
4)It will not copy at all.


What is the return code if u print a empty VSAM file?




1)0
2)118
3)120
4)160


Which of the following is compulsary for KSDS creation?




1)Space
2)DATA component
3)INDEX component.
4)CISZ

How to set return code of a job to zero with out making any changes in
job steps?



1)using SET command
2)using MOVE command
3)using IF and SET command
4)not possible


How many ways do you know to pass external data into program?




1)2
2)1
3)3
4)4

In variable record length LRECL value is largest record in the file plus four byte. Here this four bytes contains

A. The information saying that it is variable
B. the actual length of each variable length record in the file
C. Both A and B
D. None of the above


I have 5 steps in a jcl . I have to execute step 3 , bypass step 4 and execute step 5 . How to achieve this

1.using cond statement
2.using restart=stepname
3.using iebedit utility
4.both 1 & 3

What are the changes to be made in jcl so as to do testing

1.DD statement with the data used to test the execution of the program.
2.Instream data is used to test the data
3.Dummy statement is used to test the execution of the program\
4. Can not be tested


Can I copy a fixed block record length file to a VB and vice versa

1.Only from fixed block record length to VB
2.Only from VB to fixed block length file is possible
3.Both 1 & 2
4. Both not possible


Can you submit multiple jobs in a jcl simultaneously

1.No,Only one
2.Not more than 2
3.yes
4. Insufficient information


What are the causes for soc01, soc04, soc05 soc07 and socb abends

1.Data Exception, Operation Exception, Addressing Exception, Protection Exception, Divisible by Zero
2.Operation Exception, Data Exception, Addressing Exception, Protection Exception, Divisible by Zero
3.Operation Exception, protection Exception, Addressing Exception, Data
Exception, Divisible by Zero
4. All the above


.What will happen if you attempt to restart a job in the middle of a // IF and // ENDIF

1.It will execute.
2.It will not execute the step in the middle of a // IF and // because of condition codes.
3.the condition code of the previous step will be applied in cond parameter and goes for execution


What is the difference between the data definition name and data set name in the DD statement of jcl

1. DDname is used in farward reference while referring the datasets where dataset name is used to name the dataset to DD.
2. DDname is used to name the dataset and dataset name is used for searching the dataset
3. DDname is used to refer the dataset and dsname is for creating the dataset
4. All the above


How do you skip a particular step in a proc or a job

1.using cond parameter in step
2.using restart parameter
3.using cond parameter in job statement
4. Both B and C

If an application program accesses a base cluster in alternate key sequence, then DD statements are needed for which data sets? (f)
a) Base cluster
b) Alternate index
c) Path
d) a and b
e) a and c
f) a, b and c


Which of the following backs up data in CI mode? (b)
a) REPRO only
b) EXPORT only
c) Both REPRO and EXPORT
d) Neither REPRO nor EXPORT


What is SOC4 error stands for
A. Storage Violation error B. READING file that is not opened
C. Invalid address reference D. In all the above case
Key D


. How can unused space allocation be returned to the system when a dataset is closed ?
(A) RETURN
(B) RLSE
(C) CONTIG
(D) CLOSE
Key B


What is S322 abend?
A. Space not found in PDS
B. Operator cancelled the job
C. Time out Abend
D. Secondary request is not coded
Key C

8. JES2 allocates the data set
A. For all the steps before the job is scheduled
B. allocates of data set required by a step are done only just before the step executes
C. Both the case A & B are possible
D. None of the above
Key B



11. Given an input file with duplicates how to remove the duplicate records from the file using jcl

A.SUM FIELDS=(3,4,ZD)
B. SUM FIELDS=NONE
C. using ALLDUPS
D. Not possible

Key B


) Can an individual step be restricted from using all the job’s allowed CPU time ?
(1) Yes
(2) No
(3) Only in Catalogued Procedure
(4) Through the STEPTIME parameter


How can the disposition of sysout datasets be set for an entire jobstream ?
(1) SYSOUT parameter
(2) OUTPUT parameter
(3) DEFAULT parameter
(4) DEST keyword


19) What is the maximum length of a single line of JCL ?
(1) 71 bytes
(2) 60 bytes
(3) 55 bytes
(4 ) 80 bytes



21) Many JCL statements contain specific values designed to direct & control the execution of the statement. What are these fields called ?
(1) Linkage Variables
(2) Parameters
(3) Control Libraries
(4) Includes


26. How can return codes be tested before execution of a job step?
(1) Through the RC keyword
(2) Through the CODE keyword
(3) Through the COND keyword
(4) Through the RETURNCD keyword

27. How can values be passed from the job stream to an executable program?
(1) Through the PARM keyword
(2) Through the VALUE parameter
(3) Through the PGM parameter
(4) All of the above
35. How can a stopped job be started again? - JCL
(1) Through the RETURN parameter
(2) Through the STARTUP parameter
(3) Through the RESTART parameter
(4) Through the CHECKPNT keyword


35. What is the prefix character for symbolic parameter ?
(1) % (2) & (3) @ (4) {}


36. If both the JOBCAT & STEPCAT statements are coded in a step, which will be executed?
(1) JOBCAT
(2) STEPCAT
3. the one coded first in the JCL stream
(4) both the statements will be executed in sequence


How many instream procedures can be called within a single job? jcl




1)255
2)15
3)25
4)30




An instream procedure SAMPLE is coded in a jcl. A cataloged proc
SAMPLE is also available system library. When SAMPLE is invoked which
is invoked?


1)Instream Procedure.
2)Catalog Proc
3)Both will be ignored.
4)




If both JOBLIB and STEPLIB are coded in a job which one be executed?




1)JOBLIB
2)STEPLIB
3)PROCLIB
4)PROCLIB



When no MSGLEVEL parameter is coded then jcl




1)only i/p statements are printed.
2)Only i/p and proc statements
3)Only JOB statements is shown in the o/p.
4) Job, Input and output Allocation msg shown


Maximum number of DD statements in a job




1)3270 * step
2)255 *step
3)3273 * no of steps
4)3274



which parameter is to be given on the JOB statement to check the
syntax of a JCL?



1)TYPRUN=TEST
2)TYPRUN=SCAN
3)TYPRUN=HOLD
4)Both A & B


Two JOBS with name JOBNAME are submitted , then jcl




1)Both JOBS are cancelled by the operator.
2)Both JOBS wait in queue.
3)First runs while the 2nd waits in queue.
4)1st JOB is run and Second is cancelled.




Which one of the following is a positional parameter in EXEC statemen jcl




1)ACCT
2)PARM
3)PROC
4)COND




Is random access possible in esds?




1)Yes
2)No
3) Using RRN

4) Using Index key



What is Abend code S806 U0000?




1)Data exception.
2)Memory violation.
3)Load module not found.
4)duplicate dataset









If you code time in both job statement and exec which will overwrite
the other



1)Time specified in the job statment
2)Time specified in exec statement
3)it will give a jcl error
4)It will ignore both.



How many tracks for 15 cylinders




1)450 tracks
2)15 tracks
3)150 tracks
4)225 tracks

Which of the following is not a positional parameter ?




1)Accounting information.
2)Abnormal termination in DISP.
3)Record format in DCB.
4)Programmers name




If you code both symbolic and overriding parameters which will be
Executed?



1)Symbolic parameters.
2)Overriding parameters.
3)will give error.
4)None will be executed.



For which of the following sysprint is not neccessary




1)IEBGERNER
2)IEFBR14
3)SORT
4)ICETOOL

What is the return code of IEBCOMPR when both the datasets are not
equal?



1)0
2)4
3)8
4)12



For a New dataset which parameter is compulsary?




1)DISP
2)DCB
3)SPACE
4)VOLUME



Which of the following is not in REPRO command?




1)Replace.
2)Reuse.
3)Skip
4)readnext

What is the purpose of BLDINDEX?




1)To create a path
2)To create a alternate index.
3)To create a logical relation.
4)To load an Index.

How many RDF fields are there if the record format is variable?




1)One
2)Two
3)Three
4)Depends on records

How many times the seconday spaces is allocated for cylinders?




1)255
2)80
3)150
4)122



When you are dealing with variable length data set ie if RECFM=V then what will be the value of LRECL parameter?
A. Similar to RECFM=F option
B. LRECL option is not a mandatory
C. LRECL value is largest record in the file plus 4 bytes.
D. LRECL value smaller record plus 4 bytes

1. Can we use empty VSAM as input

1.No
2.Yes
3.Both
4. None of the above



Can we delete a data set using iefbr14 , iebgener

1.yes,both can delete the dataset
2.No,cannot delete dataset in iebgener
3.No,it is not possible on both the utilities
4. None of the above

Can I concatenate a ps file , pds member and a gdg . if so how.

1.No,only ps can be concatenated
2.No,GDG cannot be concatenated
3.yes,using iebgener utility
4. None of the above


What is the default value the region parameter will take.

1.0k/0m
2.1k
3.100k
4. 2K/2M



Name the system libraries from which modules are retrieved at execution.

1.SYS1.LINKLIB
2.ZOS.PROCLIB
3.Both from SYS1.LINKLIB and ZOS.PROCLIB
4. None of the above

how many types of libraries are there in jcl

1. Five
2. Two
3. Three
4. None



What are SD37 , SB37 and SE37 abends

1. Space abends
2. Job exceeds maximum wait time
3. Region problem
4. None of the above

How to pass the temp. data set from one step to another

1.using disp=shr parameter
2.using disp=old parameter
3.using disp=(new, pass) parameter
4. Not possible


Can you execute a proc from another proc [ nested procs. ]

1.No,nested procedures is not possible
2.Yes,can go for nested procedures
3.only procedure can be called by another procedure if the procedure is cataloged
4. both B and C


How is the keyword DUMMY interpreted by jcl

1. .Is coded on the DD statement to test the execution the program without reading or writing to a data set.
2.It is coded to omit the DD statements
3.It is coded to pass data to the program
4. both B and C



How is normal data set passed from one step to another

1. Using backward reference or using disp=shr
2. No,data set cannot be passed from one step to another
3. Only temporary data sets can be passed from one step to another
4. None of the above


How do you access a file that had a disposition keep

1.Indicates that the data set is to be retained or keep in DASD Volumes upon successful execution of the job
2.used to share the datasets over different steps
3.used to catalog the dataset
4. None of the above

VSAM processing options could be specified in (e)
a) Catalog
b) Program control blocks
c) JCL
d) None
e) All the above

Which of the following is/are not supported by COBOL? (d)
a) Skip-sequential processing
b) RBA addressing
c) Replacing existing record
d) a and b
e) a, b and c
f) a and c

An alternate index itself is which cluster type? (d)
a) LDS
b) RRDS
c) ESDS
d) KSDS
e) None

Which of the following requires a DELETE/DEFINE before the command can be used to restore an
existing data set? (a)
a) REPRO only
b) IMPORT only
c) Both REPRO and IMPORT
d) Neither REPRO nor IMPORT

Diagrammatic representation of the evolution of AS/400 from the scratch to the present

Interview Questions on SPUFI

1. 
   Even though the records are fetched by SPUFI for the query,the SQLCODE is shown as 100. Why?
2. We mentioned MAX SELECT LINES ===> 200000. As only 5 rows returned SPUFI returned SQLCODE 100.If we change this MAX SELECT LINES to 1
   How do you comment the SQL in the SPUFI input dataset?
   By prefixing – -
   When you run a Update Query in SPUFI, the changes will be made permanent automatically?
   It depends on the AUTOCOMMIT option.
   I ran a query to fetch remarks which is of 250 VARCHAR item of an employee. But SPUFI shows only 80 characters. There is truncation. What could be the possible reason?
   That could be the limit you set up for CHAR item.

Mainframe DB2 Interview Questions

IDMSDB2 Study Material


1.2.1 What is SQL?

Structured Query Language (SQL) is a standardized language for defining
and manipulating data in a relational database. In accordance with the
relational model of data, the database is perceived as a set of tables,
relationships are represented by values in tables, and data is retrieved
by specifying a result table that can be derived from one or more tables.
DB2 transforms the specification of a result table into a sequence of
internal operations that optimize data retrieval. This transformation
occurs when the SQL statement is prepared. This transformation is also
known as binding.


1.2.2 Static SQL

The source form of a static SQL statement is embedded within an
application program written in a host language such as COBOL. The
statement is prepared before the program is executed and the operational
form of the statement persists beyond the execution of the program.

A source program containing static SQL statements must be processed by an
SQL precompiler before it is compiled. The precompiler checks the syntax
of the SQL statements, turns them into host language comments, and
generates host language statements to invoke DB2.


1.2.3 Dynamic SQL

A dynamic SQL statement is prepared during the execution of an SQL
application, and the operational form of the statement is not persistent.
The source form of the statement is a character string passed to DB2 by an
application program using the static SQL statement PREPARE or EXECUTE
IMMEDIATE. DB2 also uses dynamic SQL for system-directed access (1) .


1.2.4 Interactive SQL

In this book, interactive SQL refers to SQL statements submitted to SPUFI
(SQL processor using file input). SPUFI prepares and executes these
statements dynamically.



1.2.6 Indexes

An index is an ordered set of pointers to rows of a base table. Each
index is based on the values of data in one or more columns. An index is
an object that is separate from the data in the table. When you define an
index using the CREATE INDEX statement, DB2 builds this structure and
maintains it automatically.

Indexes can be used by DB2 to improve performance and ensure uniqueness.
In most cases, access to data is faster with an index. A table with a
unique index cannot have rows with identical keys.

1.2.7 Keys

A key is one or more columns that are identified as such in the
description of a table, an index, or a referential constraint.
The same column can be part of more than one key. A key composed of more than
one column is called a composite key.

A composite key is an ordered set of columns of the same table. The
ordering of the columns is not constrained by their ordering within the
table. The term value, when used with respect to a composite key, denotes
a composite value. Thus, a rule such as, "the value of the foreign key
must be equal to the value of the primary key" means that each component
of the value of the foreign key must be equal to the corresponding
component of the value of the primary key.

A unique key is a key that is constrained so that no two of its values are
equal. The constraint is enforced by DB2 during the execution of the LOAD
utility and the SQL INSERT and UPDATE statements. The mechanism used to
enforce the constraint is a unique index. Thus, every unique key is a key
of a unique index. Such an index is also said to have the UNIQUE
attribute.


1.2.7.1 Primary Keys

A primary key is a unique key that is a part of the definition of a table.
A table cannot have more than one primary key, and the columns of a
primary key cannot contain null values. Primary keys are optional and can
be defined in SQL CREATE TABLE or ALTER TABLE statements.

The unique index on a primary key is called a primary index. When a
primary key is defined in an SQL CREATE TABLE statement, the table is
marked unavailable until the primary index is created by the user.

When a primary key is defined in an SQL ALTER TABLE statement, a unique
index must already exist on the columns of that primary key. This unique
index is designated as the primary index.


1.2.7.2 Foreign Keys

A foreign key is a key that is specified in the definition of a
referential constraint. A foreign key refers to or is related to a
specific primary key. A table can have zero or more foreign keys. The
value of a composite foreign key is null if any component of the value is
null.

1.2.8 Storage Structures

In DB2, a storage structure is a set of one or more VSAM data sets that
hold DB2 tables or indexes. A storage structure is also called a page
set. A storage structure can be one of the following:

table space A table space can hold one or more base tables. All tables
are kept in table spaces. A table space can be defined
using the CREATE TABLESPACE statement.

index space An index space contains a single index. An index space is
defined when the index is defined using the CREATE INDEX
statement.


1.2.9 Storage Groups

Defining and deleting the data sets of a storage structure can be left to
DB2. If it is left to DB2, the storage structure has an associated
storage group. The storage group is a list of DASD volumes on which DB2
can allocate data sets for associated storage structures.

1.2.10 Databases

In DB2, a database is a set of table spaces and index spaces. These index
spaces contain indexes on the tables in the table spaces of the same
database. Databases are defined using the CREATE DATABASE statement and
are primarily used for administration. Whenever a table space is created,
it is explicitly or implicitly assigned to an existing database.


1.2.11 Catalog

Each DB2 maintains a set of tables containing information about the data
under its control. These tables are collectively known as the catalog.
The catalog tables contain information about DB2 objects such as tables,
views, and indexes. In contrast, the catalogs maintained by
access method services are known as "integrated catalog facility
catalogs."

Tables in the catalog are like any other database tables with respect to
retrieval. If you have authorization, you can use SQL statements to look
at data in the catalog tables in the same way that you retrieve data from
any other table in the system. Each DB2 ensures that the catalog contains
accurate descriptions of the objects that the DB2 controls.

1.2.12 Views

A view provides an alternative way of looking at the data in one or more
tables. A view is a named specification of a result table. The
specification is an SQL SELECT statement that is effectively executed
whenever the view is referenced in an SQL statement. At any time, the
view consists of the rows that would result if the subselect were
executed. Thus, a view can be thought of as having columns and rows just
like a base table. However, columns added to the base tables after the
view is defined do not appear in the view. For retrieval, all views can
be used like base tables.

Views can be used to control access to a table and make data easier to
use. Access to a view can be granted without granting access to the
table. The view can be defined to show only portions of data in the
table. A view can show summary data for a given table, combine two or
more tables in meaningful ways, or show only the selected rows that are
pertinent to the process using the view.


Example: The following SQL statement defines a view named XYZ. The view
represents a table whose columns are named EMPLOYEE and WHEN_HIRED. The
data in the table comes from the columns EMPNO and HIREDATE of the sample
table DSN8310.EMP. The rows from which the data is taken are for
employees in departments A00 and D11.

CREATE VIEW XYZ (EMPLOYEE, WHEN_HIRED)
AS SELECT EMPNO, HIREDATE
FROM DSN8310.EMP
WHERE WORKDEPT IN ('A00', 'D11');

An index cannot be created for a view. However, an index created for a
table on which a view is based might improve the performance of operations
on the view.

Read-only views cannot be used for insert, update, and delete operations.
Read-only views are discussed under the CREATE VIEW statement in Chapter 6
of SQL Reference.

Like all result tables, the table represented by a view is not persistent.
On the other hand, the definition of a view persists in the DB2 catalog.
An SQL DROP VIEW statement can drop a view, and the definition of the view
is removed from the catalog. The definition of a view is also removed
from the catalog when any view or base table on which the view depends is
dropped.


1.2.13 Application Processes

All SQL programs execute as part of an application process. An
application process involves the execution of one or more programs, and is
the unit to which DB2 allocates resources and locks. Different
application processes may involve the execution of different programs, or
different executions of the same program. The means of initiating and
terminating an application process are dependent on the environment.



1.2.14 Packages and Application Plans

A package contains control structures used to execute SQL statements.
Packages are produced during program preparation. The control structures
can be thought of as the bound or operational form of SQL statements taken
from a database request module (DBRM). The DBRM contains SQL statements
extracted from the source program during program preparation. All control
structures in a package are derived from the SQL statements embedded in a
single source program.

An application plan relates an application process to a local instance of
DB2, specifies processing options, and contains one or both of the
following elements:

_ A list of package names
_ The bound form of SQL statements taken from one or more DBRMs.

Every DB2 application requires an application plan. Plans and packages
are created using the DB2 subcommands BIND PLAN and BIND PACKAGE,
respectively.


1.2.15 Authorization and Privileges

Before it can execute a specific SQL statement, a process must have
appropriate DB2 authority. A process derives this authority from its
authorization IDs. These supply the needed authority in the following
ways:

_ By their ownership of objects referred to in the statement
_ By their possession of various DB2 authorities and privileges.




2.1.1 Result Tables

The data retrieved through SQL is always in the form of a table. In the
DB2 library, this table is called a result table. Like the tables from
which the data is retrieved, 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 DSN8310.EMP
WHERE WORKDEPT = 'D11'
ORDER BY LASTNAME;

gives this result:

LASTNAME FIRSTNME PHONENO
=============== ============ ========
ADAMSON BRUCE 4510
BROWN DAVID 4501
JOHN REBA 0672
JONES WILLIAM 0942
LUTZ JENNIFER 0672
PIANKA ELIZABETH 3782
SCOUTTEN MARILYN 1682
STERN IRVING 6423
WALKER JAMES 2986
YAMAMOTO KIYOSHI 2890
YOSHIMURA MASATOSHI 2890

The result table is displayed in this form after it is fetched and
formatted by SPUFI. Your results might not look the same.



2.1.3 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 DSN8310.DEPT;

gives this result:

DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
====== ==================================== ====== ======== =============
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00
B01 PLANNING 000020 A00
C01 INFORMATION CENTER 000030 A00
D01 DEVELOPMENT CENTER ------ A00
D11 MANUFACTURING SYSTEMS 000060 D01
D21 ADMINISTRATION SYSTEMS 000070 D01
E01 SUPPORT SERVICES 000050 A00
E11 OPERATIONS 000090 E01
E21 SOFTWARE SUPPORT 000100 E01
F22 BRANCH OFFICE F2 ------ E01
G22 BRANCH OFFICE G2 ------ E01
H22 BRANCH OFFICE H2 ------ E01
I22 BRANCH OFFICE I2 ------ E01
J22 BRANCH OFFICE J2 ------ E01

The SELECT statement example retrieves data from each column (SELECT *) of
each retrieved row of the DSN8310.DEPT table. Because no WHERE clause is
specified, data from all rows are retrieved.

The dashes for MGRNO in the fourth row of the result table indicate that
this value is null. It is null because a manager is not identified for
this department. Null values are described under "Selecting Rows with
Null Values" in topic 2.1.5.1.

2.1.4 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 DSN8310.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 DSN8310.DEPT table. You can retrieve one
column or up to as many as 750 columns.





Use a WHERE clause to select data from only 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 a predicate is evaluated for a row, it yields one of the following:

TRUE Apply the operation to the row.

FALSE Do not apply the operation to the row.

UNKNOWN Do not apply the operation to the row.




+------------------------------------------------------------------------+
¦ Table 2. Comparison Operators Used in Conditions ¦
+------------------------------------------------------------------------¦
¦ Type of ¦ Specified ¦ Example ¦
¦ comparison ¦ with... ¦ ¦
+--------------------------------+--------------+------------------------¦
¦ Equal to null ¦ IS NULL ¦ PHONENO IS NULL ¦
+--------------------------------+--------------+------------------------¦
¦ Equal to ¦ = ¦ DEPTNO = 'X01' ¦
+--------------------------------+--------------+------------------------¦
¦ Not equal to ¦ <> ¦ DEPTNO <> 'X01' ¦
+--------------------------------+--------------+------------------------¦
¦ Less than ¦ < ¦ AVG(SALARY) < 30000 ¦
+--------------------------------+--------------+------------------------¦
¦ Less than or equal to ¦ <= ¦ AGE <= 25 ¦
+--------------------------------+--------------+------------------------¦
¦ Not less than ¦ >= ¦ AGE >= 21 ¦
+--------------------------------+--------------+------------------------¦
¦ Greater than ¦ > ¦ SALARY > 2000 ¦
+--------------------------------+--------------+------------------------¦
¦ Greater than or equal to ¦ >= ¦ SALARY >= 5000 ¦
+--------------------------------+--------------+------------------------¦
¦ Not greater than ¦ <= ¦ SALARY <= 5000 ¦
+--------------------------------+--------------+------------------------¦
¦ Similar to another value ¦ LIKE ¦ NAME LIKE '%SMITH%' or ¦
¦ ¦ ¦ STATUS LIKE 'N_' ¦
+--------------------------------+--------------+------------------------¦
¦ At least one of two conditions ¦ OR ¦ HIREDATE < ¦
¦ ¦ ¦ '1965-01-01' OR SALARY ¦
¦ ¦ ¦ < 16000 ¦
+--------------------------------+--------------+------------------------¦
¦ Both of two conditions ¦ AND ¦ HIREDATE < ¦
¦ ¦ ¦ '1965-01-01' AND ¦
¦ ¦ ¦ SALARY < 16000 ¦
+--------------------------------+--------------+------------------------¦
¦ Between two values ¦ BETWEEN ¦ SALARY BETWEEN 20000 ¦
¦ ¦ ¦ AND 40000 ¦
+--------------------------------+--------------+------------------------¦
¦ 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. See
"Selecting Rows Using the NOT Keyword or Comparison Operators" in
topic 2.1.5.4 for more information about using the NOT keyword.


2.1.5.1 Selecting Rows with Null Values

A null value indicates the absence of a column value from a row. A null
value is not the same as zero or all blanks.

A WHERE clause can specify a column that, for some rows, contains a null
value. Normally, values from such a row are not retrieved, because a null
value is neither less than, equal to, nor greater than the value specified
in the condition. To select values from rows that contain null values;
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.5.2 Selecting Rows by Character or Numeric Data Values

Use the equal (=) comparison operator to select data only from the rows
that contain a data value in the specified column that is equivalent to
the value specified. To select only the rows where the department number
is A00, use WHERE WORKDEPT = 'A00' in your SQL statement:

SELECT WORKDEPT, FIRSTNME, LASTNAME
FROM DSN8310.EMP
WHERE WORKDEPT = 'A00';

The statement retrieves the department number and the first and last name
of each employee in department A00.


2.1.5.3 Selecting Rows Using Inequalities

You can use the following inequality comparison operators in predicates:

_ less than (<)
_ greater than (>)
_ less than or equal to (<=)
_ greater than or equal to (>=).

To select all employees hired before January 1, 1960, use:

SELECT HIREDATE, FIRSTNME, LASTNAME
FROM DSN8310.EMP
WHERE HIREDATE < '1960-01-01';

The example retrieves the date hired and the name for each employee hired
before 1960.


2.1.5.4 Selecting Rows Using the NOT Keyword or Comparison Operators

Use the NOT keyword or comparison operators (<>, >=, and <=) to select all
rows except the rows identified with the search condition. (2) The NOT
keyword or comparison operators must precede the search condition. To
select all managers whose compensation is not greater than $30,000, use:

SELECT WORKDEPT, EMPNO
FROM DSN8310.EMP
WHERE NOT (SALARY + BONUS + COMM) > 30000 AND JOB = 'MANAGER'
ORDER BY WORKDEPT;

The following WHERE clauses are equivalent:

_ WHERE NOT DEPTNO ='A00'
_ WHERE DEPTNO <> 'A00'

The NOT keyword cannot be used with the comparison operators. The
following WHERE clause results in an error:

Wrong: WHERE DEPT NOT = 'A00'

You also can precede other SQL keywords with NOT: NOT LIKE, NOT IN, or
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) Although the not sign (¬) can be used with comparison
operators, it is not recommended. See Chapter 3 of SQL
Reference for more information on basic predicates.



2.1.5.5 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.



2.1.5.5.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 DSN8310.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 DSN8310.DEPT
WHERE DEPTNAME LIKE '%CENTER%';

Assume the DEPTNO column is defined as a three-character column of fixed
length. The sample SQL statement

...WHERE DEPTNO LIKE 'E%1';

could be specified to select all department numbers that begin with E and
end with 1. If E1 is one of your department numbers, it is not selected
because the blank that follows 1 is not taken into account. If the DEPTNO
column had been defined as a three-character column of varying-length,
department E1 would have been selected because varying-length columns can
have any number of characters, up to and including the maximum number
specified when the column was created.

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 DSN8310.DEPT
WHERE DEPTNO LIKE 'E%1%';



2.1.5.5.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 DSN8310.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.) If two-character
department numbers were also allowed, 'E_1' would select only
three-character department numbers that begin with E and end with 1.

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 DSN8310.EMP
WHERE PHONENO LIKE '378_';

2.1.5.5.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 is not preceded by
an escape character, indicates that the string can be followed by any
number of (or no) characters. In this example, putting '++' in the string
would allow you to search for a single plus sign (+) as part of the
string.



2.1.6 Selecting Rows Subject to Multiple Conditions

The following sections explain ways to use more than one predicate. The
order in which the statement is processed does not depend on the order in
which you specify comparisons. Using parentheses with multiple search
conditions can help control the order of processing.



2.1.6.1 Using AND, OR, and Parentheses to Relate Predicates

Use AND, OR, and parentheses to form Boolean combinations of predicates.
Use AND to specify multiple predicates that must be satisfied:

SELECT EMPNO, HIREDATE, SALARY
FROM DSN8310.EMP
WHERE HIREDATE < '1965-01-01' AND SALARY < 16000;

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.

Use OR when specifying two predicates, of which at least one must be
satisfied:

SELECT EMPNO, HIREDATE, SALARY
FROM DSN8310.EMP
WHERE HIREDATE < '1965-01-01' OR SALARY < 16000;

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.

If you use more than two conditions with AND or OR, you can use
parentheses to make sure the intended condition is interpreted correctly.

For example, this WHERE clause:

WHERE (HIREDATE < '1965-01-01' AND SALARY < 20000) OR (EDLEVEL < 13)

selects the row of each employee that satisfies at least one of the
following conditions:

_ The employee was hired before 1965 AND is paid less than $20,000.
_ The employee's education level is less than 13.

Based on this WHERE clause, the selected rows are for employees 000290,
000310, and 200310.

With the parentheses moved, however, the meaning of the WHERE clause
changes significantly:

WHERE HIREDATE < '1965-01-01' AND (SALARY < 20000 OR EDLEVEL < 13)

This clause selects the row of each employee that satisfies both of the
following conditions:

_ The employee was hired before 1965.
_ The employee's salary is less than $20,000 OR the employee's education
level is less than 13.

Based on this WHERE clause, two rows are selected: one for employee
000310 and one for employee 200310.

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 $20,000.

SELECT EMPNO
FROM DSN8310.EMP
WHERE (HIREDATE < '1965-01-01' AND SALARY < 20000)
OR (HIREDATE > '1965-01-01' AND SALARY > 20000);

When using the NOT condition with AND and OR, the placement of the
parentheses is important.

In this SQL statement, only the first predicate (SALARY >= 50000) is
negated.

SELECT EMPNO, EDLEVEL, JOB
FROM DSN8310.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 $50,000.
_ The employee's education level is less than 18.

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 DSN8310.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.6.2 Using BETWEEN to Specify Ranges to Select

You can retrieve data from each row whose column has a value within two
limits; use BETWEEN.

Specify the lower boundary of the BETWEEN condition first, then the upper
boundary. The limits are inclusive. If you specify WHERE column-name
BETWEEN 6 AND 8 (and if 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

SELECT DEPTNO, MGRNO
FROM DSN8310.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

SELECT EMPNO, SALARY
FROM DSN8310.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 when comparing floating point data in
your program with decimal data in the database to define a tolerance
around the two numbers being compared. Assume the following:

_ MYTABLE is a table in the database that has a decimal column named
COL1.

_ There is a row in MYTABLE that has a value of 113.01 for COL1.

_ The host variable HOSTVAR is a floating point number that represents
113.01.


To compare HOSTVAR (3) to COL1, DB2 has to convert 113.01 to a floating
point value. Because floating point numbers are approximations, the
following query might not yield the expected result (that is, a host
variable floating point value for 113.01 might not be considered
equivalent to the floating point representation of the decimal value,
113.01, that DB2 uses for the comparison).

Possibly wrong:
SELECT *
FROM MYTABLE
WHERE :HOSTVAR = COL1;

The following embedded SQL statement uses a host variable, FUZZ, that
contains a tolerance factor.

Better:
SELECT *
FROM MYTABLE
WHERE :HOSTVAR BETWEEN (COL1 - :FUZZ) AND (COL1 + :FUZZ);

(3) The HOSTVAR cannot be issued interactively because a host
variable is used. See "Accessing Data Using Host Variables
and Host Structures" in topic 3.1.4 for more information
about using host variables to access data.

2.1.6.3 Using IN to Specify Values in a List

You can use the IN predicate to retrieve data from 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 DSN8310.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, the WHERE clause in
the SELECT statement above could be coded:

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 DSN8310.EMP
WHERE SEX NOT IN ('F', 'M');


2.1.7 Using Concatenation Operations: 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 DSN8310.EMP;

concatenates the last name, comma, and first name of each result row.
CONCAT is the preferred keyword. See Chapter 3 of SQL Reference for more
information on expressions using the concatenation operator.


2.1.8 Using Calculated Values

Calculations can be performed on numeric data or datetime data. See
Chapter 3 of SQL Reference for detailed information about calculations
involving date, time, and timestamp data.


2.1.8.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, SALARY / 52
FROM DSN8310.EMP
WHERE WORKDEPT = 'A00';


you get this result:

EMPNO SALARY/12 SALARY/52
====== ============== ==============
000010 4395.83333333 1014.42307692
000110 3875.00000000 894.23076923
000120 2437.50000000 562.50000000
200010 3875.00000000 894.23076923
200120 2437.50000000 562.50000000


Here a name is given to each unnamed column to help you follow the
example, although the name shown (for example, SALARY/12) does not appear
when the example is executed through SPUFI. If a column in a result comes
directly from a column in a table, SPUFI uses the column name as a
heading; if the column is the result of a calculation made by DB2, SPUFI
does not give it a heading.

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 DSN8310.EMP
WHERE BONUS + COMM > 5000;

which gives the following result:


WORKDEPT EMPNO SALARY BONUS COMM
======== ====== ============ ============ ============
A00 000010 52750.00 1000.00 4220.00
A00 200010 46500.00 1000.00 4220.00

2.1.8.3 Using Datetime Data

If you will be using dates, you should 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 "How To Create a New
Department Table" in topic 2.2.1.1.1), 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 is executed, one person represented
in YEMP is 27 years, 0 months, and 29 days old but is not shown in the
results. What happens is this:

If the data type of the column is decimal, 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. See Chapter 3 of
SQL Reference for more information about datetime operands and durations.)
As a date, the result of the calculation (27/00/29) is not legitimate, so
it is transformed into 26/12/29. Based on this erroneous transformation,
DB2 then recognizes the person as 26 years old, not 27. If, however, the
table is created with BIRTHDATE as a DATE column, CURRENT DATE - BIRTHDATE
is a duration, and the problem is resolved.

If you have existing date data that is not stored in datetime columns, you
can use conversions to avoid errors. The following examples illustrate a
few conversion techniques, given the existing type of data:

_ For DECIMAL(8,0) values of the form yyyymmdd in column C1, use SUBSTR
to isolate the pieces and then use CONCAT to reassemble them in ISO
format (with hyphens):

DATE(SUBSTR(DIGITS(C2),1,4)CONCAT'-'
CONCAT SUBSTR(DIGITS(C2),5,2)
CONCAT'-'CONCAT
SUBSTR(DIGITS(C2),7,2))



2.1.9 Using Built-In Functions

Two types of built-in functions are available for use with a SELECT
statement: column and scalar.

Subtopics
2.1.9.1 Using Column Functions
2.1.9.2 Using Scalar Functions


2.1.9 Using Built-In Functions

Two types of built-in functions are available for use with a SELECT
statement: column and scalar.



2.1.9.1 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 which can then be retrieved. The calculated values are based on
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), MIN(SALARY), AVG(SALARY), MAX(SALARY), COUNT(*)
FROM DSN8310.EMP
WHERE WORKDEPT = 'D11';

The following result is displayed:


SUM MIN AVG MAX COUNT(*)
(SALARY) (SALARY) (SALARY) (SALARY)
========= ========= =============== ========= ========
276620.00 18270.00 25147.27272727 32250.00 11



DISTINCT can be used with the SUM, AVG, and COUNT functions. DISTINCT
means that the selected function will be performed on only the unique
values in a column. The specification of DISTINCT with the MAX and MIN
functions has no effect on the result and is not advised.

In this case SUM and AVG can only be applied to numbers. MIN, MAX, and
COUNT can be applied to values of any type.

The following SQL statement counts the number of employees described in
the table.

SELECT COUNT(*)
FROM DSN8310.EMP;

This SQL statement calculates the average education level of employees in
a set of departments.

SELECT AVG(EDLEVEL)
FROM DSN8310.EMP
WHERE WORKDEPT LIKE '_0_';

The SQL statement below counts the different jobs in the DSN8310.EMP
table.

SELECT COUNT(DISTINCT JOB)
FROM DSN8310.EMP;


2.1.9.2 Using Scalar Functions

A scalar function also produces a single value, but unlike a column
function, a scalar function's argument is a single value. If the scalar
function has several arguments, each argument results in a single value.
The SQL statement below returns the year each employee in a particular
department was hired:

SELECT YEAR(HIREDATE)
FROM DSN8310.EMP
WHERE WORKDEPT = 'A00';

gives this result:

YEAR(HIREDATE)
==============
1972
1965
1965
1958
1963

The scalar function YEAR produces a single scalar value for each row of
DSN8310.EMP that satisfies the search condition. In this example, there
are three rows that satisfy the search condition, so YEAR is applied three
times resulting in three scalar values.

Table 3 shows the scalar functions that can be used. For complete details
on using these functions see Chapter 4 of SQL Reference.

+------------------------------------------------------------------------+
¦ Table 3. Scalar Functions ¦
+------------------------------------------------------------------------¦
¦ Scalar ¦ Returns... ¦ Example ¦
¦ Function ¦ ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ CHAR ¦ a string representation of ¦ CHAR(HIREDATE) ¦
¦ ¦ its first argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ DATE ¦ a date derived from its ¦ DATE('1989-03-02') ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ DAY ¦ the day part of its ¦ DAY(DATE1 - DATE2) ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ DAYS ¦ an integer representation ¦ DAYS('1990-01-08') - ¦
¦ ¦ of its argument. ¦ DAYS(HIREDATE) + 1 ¦
+-------------+-----------------------------+----------------------------¦
¦ DECIMAL ¦ a decimal representation of ¦ DECIMAL(AVG(SALARY), 8,2) ¦
¦ ¦ a numeric value. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ DIGITS ¦ a character string ¦ DIGITS(COLUMNX) ¦
¦ ¦ representation of its ¦ ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ FLOAT ¦ floating-point ¦ FLOAT(SALARY)/COMM ¦
¦ ¦ representation of its ¦ ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ HEX ¦ a hexadecimal ¦ HEX(BCHARCOL) ¦
¦ ¦ representation of its ¦ ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ HOUR ¦ the hour part of its ¦ HOUR(TIMECOL) > 12 ¦
¦ ¦ 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 ¦ the minute part of its ¦ MINUTE(TIMECOL) = 0 ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ MONTH ¦ the month part of its ¦ MONTH(BIRTHDATE) = 5 ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ SECOND ¦ the seconds part of its ¦ SECOND(RECEIVED) ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ SUBSTR ¦ a substring of a string. ¦ SUBSTR(FIRSTNME,2,3) ¦
+-------------+-----------------------------+----------------------------¦
¦ TIME ¦ a time derived from its ¦ TIME(TSTMPCOL) < ¦
¦ ¦ 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) + ¦
¦ ¦ not null. ¦ SMLLINT2 > 1000 ¦
+-------------+-----------------------------+----------------------------¦
¦ VARGRAPHIC ¦ a graphic string from its ¦ VARGRAPHIC (:MIXEDSTRING) ¦
¦ ¦ argument. ¦ ¦
+-------------+-----------------------------+----------------------------¦
¦ YEAR ¦ the year part of its ¦ YEAR(BIRTHDATE) = 1956 ¦
¦ ¦ argument. ¦ ¦
+------------------------------------------------------------------------+



2.1.10 Putting the Rows in Order: ORDER BY
ORDER BY lets you specify the order in which rows are retrieved.



2.1.10.1 Specifying the Column Names

The order of the selected rows is based on the column identified in the
ORDER BY clause; this column is the ordering column.

To specify that the result will be retrieved in ascending order by the
values in the HIREDATE column, state:

SELECT EMPNO, LASTNAME, HIREDATE
FROM DSN8310.EMP
WHERE WORKDEPT = 'A00'
ORDER BY HIREDATE ASC;

This is the result:

EMPNO LASTNAME HIREDATE
====== =============== ==========
000110 LUCCHESI 1958-05-16
000120 O'CONNELL 1963-12-05
000010 HAAS 1965-01-01
200010 HEMMINGER 1965-01-01
200120 ORLANDO 1972-05-05

The example retrieves data showing the seniority of employees. Rows are
shown in ascending order, based on each row's HIREDATE column value. ASC
is the default sorting 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 DSN8310.EMP
WHERE SEX = 'F'
ORDER BY WORKDEPT DESC;

It gives you this result:

WORKDEPT LASTNAME EMPNO
======== =============== ======
E21 WONG 200330
E11 HENDERSON 000090
E11 SCHNEIDER 000280
E11 SETRIGHT 000310
E11 SCHWARTZ 200280
E11 SPRINGER 200310
D21 PULASKI 000070
D21 JOHNSON 000260
D21 PEREZ 000270
D11 PIANKA 000160
D11 SCOUTTEN 000180
D11 LUTZ 000220
D11 JOHN 200220
C01 KWAN 000030
C01 QUINTANA 000130
C01 NICHOLLS 000140
C01 NATZ 200140
A00 HAAS 000010
A00 HEMMINGER 200010

Rows are sorted in the EBCDIC collating sequence. 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 an order based on the second column identified 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.

1) This SQL statement:

SELECT JOB, EDLEVEL, LASTNAME
FROM DSN8310.EMP
WHERE WORKDEPT = 'E21'
ORDER BY JOB, EDLEVEL;

gives this result:

JOB EDLEVEL LASTNAME
======== ======= ===============
FIELDREP 14 LEE
FIELDREP 14 WONG
FIELDREP 16 GOUNOT
FIELDREP 16 ALONZO
FIELDREP 16 MEHTA
MANAGER 14 SPENSER

2) This SQL statement:

SELECT JOB, EDLEVEL, LASTNAME
FROM DSN8310.EMP
WHERE WORKDEPT = 'E21'
ORDER BY EDLEVEL, JOB;

gives this result:

JOB EDLEVEL LASTNAME
======== ======= ===============
FIELDREP 14 LEE
FIELDREP 14 WONG
MANAGER 14 SPENSER
FIELDREP 16 MEHTA
FIELDREP 16 GOUNOT
FIELDREP 16 ALONZO

When a null value is encountered, its value is treated as if it were
higher than all other values. Therefore, a null value appears last in an
ascending sort and first in a descending sort.

A field procedure can also be used to change the normal collating
sequence. See SQL Reference and Administration Guide for more detailed
information about sorting (string comparisons) and field procedures. All
columns identified in the ORDER BY clause must be identified in the SELECT
clause. For example, the following SQL statement orders the selected
information first by department, next by job, and lastly by date of hire.

SELECT LASTNAME, WORKDEPT, JOB, HIREDATE
FROM DSN8310.EMP
ORDER BY WORKDEPT, JOB, HIREDATE;



2.1.10.2 Specifying the Column Numbers

A column derived from a function or an expression is an unnamed column and
every column in the result table of a UNION ALL statement is an unnamed
column. To order the rows of a result table by an unnamed column, use its
numerical position in the series of columns listed in the SELECT clause.
For example, ORDER BY 3 orders the rows by the third column of the result
table.

The following SELECT statement example calculates the length of service of
every employee in Department E21. The results are put in descending order
by length of service. The ORDER BY clause says, "Order the results by the
values in the third column of results, in descending order."

SELECT EMPNO, LASTNAME, (CURRENT DATE - HIREDATE)
FROM DSN8310.EMP
WHERE WORKDEPT = 'E21'
ORDER BY 3 DESC;

gives this result:

EMPNO LASTNAME
====== =============== ========
200340 ALONZO 450830
000340 GOUNOT 450830
000320 MEHTA 270628
000330 LEE 161110
200330 WONG 161110
000100 SPENSER 120715

You can use the column number even if the ordering column has a name.

The following SQL statement lists names and phone numbers of all employees
in ascending order by LASTNAME.

SELECT LASTNAME, PHONENO
FROM DSN8310.EMP
ORDER BY 1;



2.1.11 Eliminating Duplicate Rows: DISTINCT

The DISTINCT keyword removes duplicate rows from your result. Each row
contains unique data.

The following SELECT statement lists the department numbers of the
administrating departments:

SELECT DISTINCT ADMRDEPT
FROM DSN8310.DEPT;

which produces the following result:

ADMRDEPT
========
A00
D01
E01

Compare the result of the previous example with this one:

SELECT ADMRDEPT
FROM DSN8310.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.12 Summarizing Group Values: GROUP BY

Use GROUP BY to specify the application of a column function to each group
of column-name values.

Except for the grouping columns (the columns named in the GROUP BY
clause), any other column value that is selected must be specified 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 DSN8310.EMP
GROUP BY WORKDEPT;

You can use ORDER BY to specify the order in which rows are retrieved.
GROUP BY accumulates column values from the selected rows by group, but
does not order the groups.

The following SQL statement calculates the average salary for each
department:

SELECT WORKDEPT, AVG(SALARY)
FROM DSN8310.EMP
GROUP BY WORKDEPT
ORDER BY WORKDEPT;

The following result is displayed:

WORKDEPT AVG(SALARY)
======== ===============
A00 40850.00000000
B01 41250.00000000
C01 29722.50000000
D11 25147.27272727
D21 25668.57142857
E01 40175.00000000
E11 21020.00000000
E21 24086.66666666

The average salary for each department is calculated. The GROUP BY clause
in this example specifies that you want DB2 to apply a function (AVG) to
each group of column values (SALARY) and return one row for each group of
department numbers (WORKDEPT). WORKDEPT can be selected without a column
function because its value determines the group (that is, every member of
each group has the same WORKDEPT value).

You can also specify that you want the rows grouped by more than one
column. For example, you can find the average salary for men and women in
departments A00 and C01. The following SQL statement:

SELECT WORKDEPT, SEX, AVG(SALARY)
FROM DSN8310.EMP
WHERE WORKDEPT IN ('A00', 'C01')
GROUP BY WORKDEPT, SEX;

gives this result:

WORKDEPT SEX AVG(SALARY)
======== === ===========
A00 F 49625.00000000
A00 M 35000.00000000
C01 F 29722.50000000

The rows are grouped first by department number and next (within each
department) by sex before DB2 derives the average SALARY value for each
group.

If there are null values in the column you specify in the GROUP BY clause,
DB2 considers those values equal and returns a single-row result
summarizing the data in those rows with null values.

When it is used, the GROUP BY clause follows the FROM clause and any WHERE
clause, and precedes the ORDER BY clause.

The SQL statement below lists, for each job, the number of employees with
that job and their average salary.

SELECT JOB, COUNT(*), AVG(SALARY)
FROM DSN8310.EMP
GROUP BY JOB
ORDER BY JOB;

For static SQL, GROUP BY can be used only in cursor declarations. Using
GROUP BY in any other way (SELECT INTO statement, for example) results in
an error. See "Chapter 3-2. Using a Cursor to Retrieve a Set of Rows" in
topic 3.2 for more information on cursors.



2.1.13 Selecting Groups Subject to Conditions: HAVING

Use HAVING to specify a condition that each group to be retrieved 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)
FROM DSN8310.EMP
GROUP BY WORKDEPT
HAVING COUNT(*) > 1
ORDER BY WORKDEPT;

gives this result:

WORKDEPT AVG(SALARY)
======== ===============
A00 40850.00000000
C01 29722.50000000
D11 25147.27272727
D21 25668.57142857
E11 21020.00000000
E21 24086.66666666

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

The HAVING clause tests a property of the group. To specify that you want
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, you can use the HAVING clause. Assuming you are only
interested in departments A00 and D11, the following SQL statement tests
the group property, MIN(EDLEVEL):

SELECT WORKDEPT, AVG(SALARY), MIN(EDLEVEL)
FROM DSN8310.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) MIN(EDLEVEL)
======== =============== ============
A00 49625.00000000 18
D11 25817.50000000 17

When GROUP BY and HAVING are both specified, 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 use multiple predicates in a HAVING clause by connecting them
with AND and OR, and you can use HAVING NOT for any predicate of a search
condition.


2.1.14 Selecting From More Than One Table (Joining Tables)

Data from two or more tables can be combined or joined. To join data, you
need to do the following:

1. In the FROM clause, identify the names of the tables to be joined.

2. In the WHERE clause, specify a search condition for the join.


The search condition for the join is the link between the tables that
restricts the selection of rows. For example, the search condition could
identify two columns that must be equal (one from each of the tables being
joined) in order for the rows to be joined and included in the result.

Each row of the result table contains data that has been joined from both
tables (for rows that satisfy the search condition). Each column of the
result table contains data from one, but not both, of the tables.

If you do not specify a search condition in the WHERE clause, the result
table contains all possible combinations of rows for the tables identified
in the FROM clause. If this happens, the number of rows in the result
table is equal to the product of the number of rows in each table
specified.



2.1.14.1 Example: Joining Two Tables

The SELECT statement example below retrieves all manager department
numbers, managers last and first names, and department names from table
DSN8310.DEPT and DSN8310.EMP and lists them in an order based on
department number.

This SQL statement:

SELECT DEPTNO, LASTNAME, FIRSTNME, DEPTNAME
FROM DSN8310.DEPT, DSN8310.EMP
WHERE MGRNO = EMPNO
ORDER BY DEPTNO;

gives this result:


DEPTNO LASTNAME FIRSTNME DEPTNAME
====== ========= ========= ====================================
A00 HAAS CHRISTINE SPIFFY COMPUTER SERVICES DIV.
B01 THOMPSON MICHAEL PLANNING
C01 KWAN SALLY INFORMATION CENTER
D11 STERN IRVING MANUFACTURING SYSTEMS
D21 PULASKI EVA ADMINISTRATION SYSTEMS
E01 GEYER JOHN SUPPORT SERVICES
E11 HENDERSON EILEEN OPERATIONS
E21 SPENSER THEODORE SOFTWARE SUPPORT



The SELECT statement example displays data retrieved from two tables:

_ DEPTNO and DEPTNAME come from DSN8310.DEPT.
_ LASTNAME and FIRSTNME come from DSN8310.EMP.

The WHERE clause makes the connection between the two tables. Column
values are selected only from those rows where MGRNO (in DSN8310.DEPT)
equals EMPNO (in DSN8310.EMP).

The following SQL statement lists (in descending order by education level)
education level, first name, last name, and department name for each
employee whose education level is greater than 18.

SELECT EDLEVEL, FIRSTNME, LASTNAME, DEPTNAME
FROM DSN8310.EMP, DSN8310.DEPT
WHERE (WORKDEPT = DEPTNO) AND (EDLEVEL > 18)
ORDER BY EDLEVEL DESC;



2.1.14.2 Example: Joining a Table to Itself

The following example joins table DSN8310.PROJ to itself and returns the
number and name of each "major" project followed by the number and name of
the project that is part of it. In this example, A indicates the first
instance of table DSN8310.PROJ and B indicates a second instance of this
table. The join condition is such that the value in column PROJNO in
table DSN8310.PROJ A must be equal to a value in column MAJPROJ in table
DSN8310.PROJ B.

This SQL statement:

SELECT A.PROJNO, A.PROJNAME, B.PROJNO, B.PROJNAME
FROM DSN8310.PROJ A, DSN8310.PROJ B
WHERE A.PROJNO = B.MAJPROJ;


gives this result:


PROJNO PROJNAME PROJNO PROJNAME
====== ======================== ======= ========================
AD3100 ADMIN SERVICES AD3110 GENERAL AD SYSTEMS
AD3110 GENERAL AD SYSTEMS AD3111 PAYROLL PROGRAMMING
AD3110 GENERAL AD SYSTEMS AD3112 PERSONNEL PROGRAMMG
.
.
.
OP2010 SYSTEMS SUPPORT OP2013 DB/DC SUPPORT



2.1.14.3 Example: An Outer Join Using UNION

The query in the following example is often called an outer join. This
query returns rows for the following:

_ All employees assigned to departments
_ All employees that are not assigned to departments
_ All departments that do not have employees assigned to them.

SELECT DEPTNO, LASTNAME, FIRSTNME, DEPTNAME
FROM DSN8310.DEPT, DSN8310.EMP
WHERE MGRNO = EMPNO -- first condition
UNION ALL
SELECT DEPTNO, '*' , '*' , DEPTNAME
FROM DSN8310.DEPT A
WHERE NOT EXISTS (SELECT * FROM DSN8310.EMP -- second condition
WHERE EMPNO = A.MGRNO)
UNION ALL
SELECT '*' , LASTNAME, FIRSTNME, '*'
FROM DSN8310.EMP B
WHERE NOT EXISTS (SELECT * FROM DSN8310.DEPT -- third condition
WHERE MGRNO = B.EMPNO);



2.1.15 Specifying 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. You use UNION to
merge lists of values from two or more tables. You can use any of the
clauses and techniques you have learned so far when coding SELECT
statements, including ORDER BY.

UNION is often used 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 DSN8310.EMP
WHERE WORKDEPT = 'D11'
UNION
SELECT EMPNO
FROM DSN8310.EMPPROJACT
WHERE PROJNO = 'MA2112' OR
PROJNO = 'MA2113' OR
PROJNO = 'AD3111'
ORDER BY 1;


gives the following combined result table containing values in ascending
order with no duplicates:

======
000060
000150
000160
000170
000180
000190
000200
000210
000220
000230
000240
200170
200220

Any ORDER BY clause must appear after the last SELECT statement that is
part of the union. In this example, the sequence of the results is based
on the first column of the result table. ORDER BY specifies the order of
the final result table.

To specify the columns by which DB2 is to order the results, use numbers
(in a union, you cannot use column names for this). The number refers to
the position of the column in the result table.

To identify which SELECT statement each row is from, a constant can be
included at the end of the select list of each SELECT statement in the
union. When DB2 returns your results, the last column contains the
constant for the SELECT statement that was the source of that row. For
example, you can write:

SELECT A, B, 'A1' ... UNION SELECT X, Y, 'B2'

When a row is returned, it includes a value (either A1 or B2) to indicate
the source of the row's values.




2.1.16 Specifying UNION ALL

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 DSN8310.EMP
WHERE WORKDEPT = 'D11'
UNION ALL
SELECT EMPNO
FROM DSN8310.EMPPROJACT
WHERE PROJNO = 'MA2112' OR
PROJNO = 'MA2113' OR
PROJNO = 'AD3111'
ORDER BY 1;


gives this result:

======
000060
000150
000150
000150
000160
000160
000170
000170
000170
000170
000180
000180
000190
000190
000190
000200
000210
000210
000210
000220
000230
000230
000230
000230
000230
000240
000240
200170
200220



2.1.17 Retrieving Data Using System-Directed Access

System-directed access allows one DB2 to execute statements at another
DB2. If you are using DB2's system-directed access, the statements you
compose for remote objects differ from those you compose for local objects
in the way they identify tables and views. For remote objects, you must
use either three-part table and view names or aliases.



2.1.17.1 Using Three-Part Table and View Names

A three-part table or view name consists of three identifiers separated by
periods:

_ The first identifier is the location name for the object.
_ The second identifier is the owning authorization ID.
_ The third identifier is the actual table name.

For example, the name DALLAS.DSN8310.EMP could represent a table at the
DALLAS location. The owning authorization ID is DSN8310., and the table
name is EMP. The location name could be the name of your local subsystem,
instead of a remote location.

Suppose that you want the name, employee number, and department ID of
every employee whose last name ends in "son" in table DSN8310.EMP at
location DALLAS. If you have the appropriate authority, you could run the
following query:

SELECT LASTNAME, MIDINIT, FIRSTNME, EMPNO, WORKDEPT
FROM DALLAS.DSN8310.EMP
WHERE LASTNAME LIKE '%SON';


2.1.17.2 Using Aliases

An alias, like a synonym, is a DB2 object that represents a table or a
view. Unlike a synonym, an alias can represent remote tables and views,
and it can be used by anyone, not just its creator. In addition, you do
not need DB2 authority to use it. However, you must have authority to use
the table or view that it represents.

A reference to an alias could be a one-, two-, or three-part name. The
rules are basically the same as those used to refer to a table or a view:

_ A one-part name refers to a local alias. If the statement being
executed is dynamic, the owner of the alias is your current SQL
authorization ID. Otherwise, it is the value specified on the
QUALIFIER bind option. If a value is not specified on the QUALIFIER
bind option, then the owner of your package or plan is the qualifier
of the alias.

Example: A reference to EMP in an interactively executed query could
refer to the alias SMITH.EMP if your current SQL authorization ID is
SMITH.

_ A two-part name also refers to a local alias. As is true for a table
or view, the first qualifier identifies the owner.

Example: JONES.NEWTAB could refer to an alias named NEWTAB and owned
by JONES.

_ A three-part name could refer to either a local or a remote alias. As
is true for a table or view, the first qualifier specifies the
location, and the second qualifier identifies the owner. If the alias
is remote, it must represent a table or view at its own location. The
alias for a remote object is resolved at bind time, and existence
checking is performed at execution time.

Example: A statement issued at the SAN_FRANCISCO subsystem refers to
an alias at DALLAS. The alias referred to must represent a table or
view at DALLAS and nowhere else.


Assume now that the alias SMITH.DALEMP has been defined at your local
subsystem for the table DALLAS.DSN8310.EMP. You could then substitute the
alias for this table name in the previous query.

The result would look like this:

SELECT LASTNAME, MIDINIT, FIRSTNME, EMPNO, WORKDEPT
FROM SMITH.DALEMP
WHERE LASTNAME LIKE '%SON';

An advantage to using a locally defined alias is that the SQL statements
in which it appears need not be changed if the table or view for the alias
is either moved to another location or renamed. To make these statements
valid, drop the original alias and create it again, and, for imbedded SQL,
rebind the program in which it appears


2.1.17.3 Creating Aliases

To create aliases, you need either SYSADM authority or the CREATEALIAS
privilege. If you do not have that authorization, you must use aliases
created by others. With SYSADM authority, you can create aliases to be
owned by others. For example, you could create the alias appearing in the
previous example, with the statement

CREATE ALIAS SMITH.DALEMP FOR DALLAS.DSN8310.EMP;

For more on aliases and their creation, see the description of CREATE
ALIAS in SQL Reference.


2.2 Chapter 2-2. Creating Tables and Modifying Data
This chapter summarizes these features:

_ "Creating Your Own Tables: CREATE TABLE" in topic 2.2.1
_ "Modifying DB2 Data: INSERT, UPDATE, and DELETE" in topic 2.2.2
_ "Dropping Tables: DROP" in topic 2.2.3.

See SQL Reference and Section 4 (Volume 2) of Administration Guide for
more information about creating tables and modifying data.


2.2.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 CHAR(8) NOT NULL,
DESCRIPTION VARCHAR(60) NOT NULL WITH DEFAULT,
MFGCOST DECIMAL(8,2),
MFGDEPT CHAR(3),
MARKUP SMALLINT,
SALESDEPT CHAR(3),
CURDATE DATE NOT NULL WITH DEFAULT);

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.

- NOT NULL, when the column cannot contain null values and does not
have a default value.

- NOT NULL WITH DEFAULT, when the column cannot contain null values
but does have a default value, as follows:

- 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.



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 Creating Work Tables

Before executing sample SQL statements that insert, update, and delete
rows, you probably want to create work tables (duplicates of the
DSN8310.EMP and DSN8310.DEPT tables) that you can practice with so 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 are 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 DSN8310.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.1 How To Create a New Department Table

Use the following statements to create a new department table called
YDEPT, modeled after an existing table called DSN8310.DEPT, and an index
for YDEPT:

CREATE TABLE YDEPT
LIKE DSN8310.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);

An INSERT statement is used with a SELECT clause to copy rows from one
table to another. The following statement fills the table:

INSERT INTO YDEPT
SELECT *
FROM DSN8310.DEPT;

The INSERT statement is explained in "Modifying DB2 Data: INSERT, UPDATE,
and DELETE" in topic 2.2.2.




2.2.1.1.2 How to Create a New Employee Table

You can use the following statements to create and fill a new employee
table called YEMP.

CREATE TABLE YEMP
(EMPNO CHAR(6) NOT NULL,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3) ,
PHONENO CHAR(4) ,
HIREDATE DATE ,
JOB CHAR(8) ,
EDLEVEL SMALLINT ,
SEX CHAR(1) ,
BIRTHDATE DATE ,
SALARY DECIMAL(9, 2) ,
BONUS DECIMAL(9, 2) ,
COMM DECIMAL(9, 2) ,
PRIMARY KEY(EMPNO),
FOREIGN KEY RED (WORKDEPT) REFERENCES YDEPT
ON DELETE SET NULL);

This statement also creates a referential constraint between the foreign
key in YEMP (WORKDEPT) and the primary key in YDEPT (DEPTNO). Now, create
an index with the following statement:

CREATE UNIQUE INDEX YEMPX ON YEMP (EMPNO);

The following statement fills the table:

INSERT INTO YEMP
SELECT *
FROM DSN8310.EMP;


2.2.1.2 Creating Tables with Referential Constraints

The CREATE TABLE statement can create tables with primary keys or foreign
keys in order to establish referential constraints.

You can define a single primary key composed of specific columns. (These
columns cannot allow nulls.) However, the definition of the table is
incomplete until its primary index is created. The primary index is a
unique index that matches the primary key and enforces the uniqueness of
the primary key.

When you specify a foreign key, a referential constraint is defined with a
delete rule. Delete rules are described in "Deleting from Tables with
Referential Constraints" in topic 2.2.2.4.1. Examples of creating tables
with referential constraints can be found in Appendix A, "DB2 Sample
Tables" in topic APPENDIX1.1.

When the referential constraint is defined, DB2 enforces the constraint on
every SQL INSERT, DELETE, and UPDATE operation, and through the LOAD
utility.

For an example of a CREATE TABLE statement that defines both a primary key
and a foreign key, see "How to Create a New Employee Table" in
topic 2.2.1.1.2.


2.2.1.3 Defining a View

A view does not contain data; it is a stored definition of a set of rows
and columns. A view can present any or all of the data in one or more
tables, and, in most cases, can be used interchangeably with tables.
Using views can simplify writing SQL statements.

Use the CREATE VIEW statement to define a view and give the view a name,
just as you do for a table.

CREATE VIEW VDEPTM AS
SELECT DEPTNO, MGRNO, LASTNAME, ADMRDEPT
FROM DSN8310.DEPT, DSN8310.EMP
WHERE DSN8310.EMP.EMPNO = DSN8310.DEPT.MGRNO;

This view adds each department manager's name to the department data in
the DSN8310.DEPT table.

When a program accesses the data defined by a view, DB2 uses the view
definition to return a set of rows the program can access with SQL
statements. Now that the view VDEPTM exists, you can manipulate data by
means of it. To see the departments administered by department D01 and
the managers of those departments, execute the following statement:

SELECT DEPTNO, LASTNAME
FROM VDEPTM
WHERE ADMRDEPT = 'DO1';

When a view is created, the USER and CURRENT SQLID special registers may
be referenced in the CREATE VIEW statement. When the view is referenced,
the value used for USER or CURRENT SQLID is related to the person
executing the SQL statement (SELECT, UPDATE, INSERT, or DELETE) rather
than the person who created the view. In other words, a reference to a
special register in the definition of a view refers to its runtime value.

You can use views to limit access to certain kinds of data, such as salary
information. Views can also be used to do the following:

_ Make a subset of a table's data available to an application. For
example, a view based on the employee table might contain rows for a
particular department only.

_ Combine data from two or more tables and make the combined data
available to an application. By using a SELECT statement that matches
values in one table with those in another table, you can create a view
that presents data from both tables. However, data defined by this
type of view can only be selected. You cannot update, delete, or
insert data into a view that joins two tables.

_ Perform functions or operations on data in a table, and make the
resulting data available to an application. For example, the
resulting data computed by DB2 can be:

- The sum of the values in a column

- The maximum value in a column

- The average of the values in a column

- The length of a value in a column

- The value in a column converted to another data type

- The result of an arithmetic expression applied to one or more
columns, such as (COLB + COLA)/COLC.


2.2.1.4 Changing Data through a View

Some views are considered to be read-only, while others are subject update
or insert restrictions. (See Chapter 6 of SQL Reference for more
information about read-only views.) If a view does not have update
restrictions, there are some additional things to consider:

_ The owner of the plan or package that contains the program must be
authorized to update, delete, or insert rows into the view. You are
so authorized if you have created that view or have privileges for the
table on which the view is based. Otherwise, to bind the program you
have to obtain authorization through a GRANT statement.

_ When inserting a row into a table (via a view), the row must have a
value for each column of the table that does not have a default value.
If a column in the table on which the view is based is not specified
in the view's definition, and if the column does not have a default
value, you cannot insert rows into the table via the view.

_ Views that can be updated are subject to the same referential
constraints as the tables upon which they are defined.


2.2.2 Modifying DB2 Data: INSERT, UPDATE, and DELETE

Tables can be modified using the INSERT, UPDATE, and DELETE statements.

Subtopics
2.2.2.1 Inserting a Row: INSERT
2.2.2.2 Updating Current Values: UPDATE
2.2.2.3 Updating Tables with Referential Constraints
2.2.2.4 Deleting Rows: DELETE2.2.2.1 Inserting a Row: INSERT

Use an INSERT statement to add new rows to a table or view. Using an
INSERT statement, you can do the following:

_ Specify values for columns of a single row to be inserted in the
INSERT statement.

_ Include a SELECT statement in the INSERT statement to tell DB2 that
data for the new row (or rows) is contained in another table or view.
"Filling a Table from Another Table: Mass INSERT" in topic 2.2.2.1.3,
explains how to use the SELECT statement within an INSERT statement to
add multiple rows to a table.


In either case, for every row you insert, you must provide a value for any
column that does not have a default value.

You can name all columns for which you are providing values.
Alternatively, you can omit the column name list; when the program is
bound, DB2 inserts the name of each column of the table or view into the
column name list.

By naming the columns, you need not list the values based on their
position in the table. When you list the column names, supply their
corresponding values in the same order as the listed column names.

It is a good idea to name all columns into which you are inserting values
because:

_ Your source statements are more self-descriptive.
_ You can verify that you are giving the values in order.
_ Your insert statement is independent of the table format.

For example,

INSERT INTO YDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION)
VALUES ('E31', 'DOCUMENTATION', '000010', 'E01', ' ');

Because LOCATION column is a 16-character field, the single blank
specified for insertion is automatically padded with enough blanks to fill
in the 16-character field.

After inserting a new department row into your YDEPT table, you can use a
SELECT statement to see what you have loaded into the table. This SQL
statement:

SELECT *
FROM YDEPT
WHERE DEPTNO LIKE 'E%'
ORDER BY DEPTNO;

shows you all the new department rows that you have inserted:

DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
====== ==================================== ====== ======== ===========
E01 SUPPORT SERVICES 000050 A00 --
E11 OPERATIONS 000090 E01 --
E21 SOFTWARE SUPPORT 000100 E01 --
E31 DOCUMENTATION 000010 E01 --

There are other ways to enter data into tables:

_ You can copy one table into another, as explained in "Filling a Table
from Another Table: Mass INSERT" in topic 2.2.2.1.3.

_ You can write an application program to enter large amounts of data
into a table. For details, see "Section 3. Coding SQL in Your Host
Application Program" in topic 3.0.

_ You can use the DB2 LOAD utility to enter data from other sources.
See Command and Utility Reference for more information about the LOAD
utility.



2.2.2.1.1 Inserting into Tables with Referential Constraints

If you are inserting into a parent table:

_ If the primary index does not currently exist, then define a unique
index on the primary key.

_ Do not enter duplicate values for the primary key.

_ Do not insert a null value for any column of the primary key.


If you are inserting into a dependent table:

_ Each non-null value you insert into a foreign key column must be equal
to some value in the primary key (the primary key is in the parent
table).

_ If any field in the foreign key is null, the entire foreign key is
considered null.

_ If the index enforcing the primary key of the parent table has been
dropped, the INSERT into either the parent table or dependent table
fails.


For example, the sample application project table (PROJ) has foreign keys
on the department number (DEPTNO), referencing the department table, and
the employee number (RESPEMP), referencing the employee table. Every row
inserted into the project table must have a value of RESPEMP that is
either equal to some value of EMPNO in the employee table or is null. The
row must also have a value of DEPTNO that is equal to some value of DEPTNO
in the department table. (The null value is not allowed because DEPTNO in
the project table is defined as NOT NULL.)



2.2.2.1.2 Using an INSERT Statement in an Application Program

If DB2 finds an error while executing the INSERT statement, it stops
inserting data. Nothing is inserted into the table, and error codes are
set in the SQLCODE and SQLSTATE fields of the SQLCA. If the row is
inserted without error, however, the SQLERRD(3) field of the SQLCA has a
value of 1. SQLERRD(3) is the third of six integer variables named
SQLERRD. INSERT with subselect can insert more than one row. The number
of rows is reflected in SQLERRD(3). See Appendix C of SQL Reference for
more information.


Examples: This statement inserts information about a new employee into
the YEMP table. Since YEMP has a foreign key WORKDEPT referencing the
primary key DEPTNO in YDEPT, the value being inserted for WORKDEPT (E31)
must be a value of DEPTNO in YDEPT.

INSERT INTO YEMP
VALUES ('000400', 'RUTHERFORD', 'B', 'HAYES', 'E31',
'5678', '1983-01-01', 'MANAGER', 16, 'M', '1943-07-10', 24000,
500, 1900);

The following statement also inserts a row into the YEMP table. However,
several column values are not specified. Because the unspecified columns
allow it, null values are inserted into columns not named: PHONENO,
EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM, and HIREDATE. Since YEMP has
a foreign key WORKDEPT referencing the primary key DEPTNO in YDEPT, the
value being inserted for WORKDEPT (D11) must be a value of DEPTNO in
YDEPT.

INSERT INTO YEMP
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, JOB)
VALUES ('000410', 'MILLARD', 'K', 'FILLMORE', 'D11', 'MANAGER');

2.2.2.1.3 Filling a Table from Another Table: Mass INSERT

Use a SELECT statement within an INSERT statement to select rows from one
table to be inserted into another table.

This SQL statement is used to create a table named TELE:

CREATE TABLE TELE
(NAME2 VARCHAR(15) NOT NULL,
NAME1 VARCHAR(12) NOT NULL,
PHONE CHAR(4));

This statement copies data from DSN8310.EMP into the newly created table:

INSERT INTO TELE
SELECT LASTNAME, FIRSTNME, PHONENO
FROM DSN8310.EMP
WHERE WORKDEPT = 'D21';


The two previous statements create and fill a table, TELE, that looks like
this:

NAME2 NAME1 PHONE
=============== ============ =====
PULASKI EVA 7831
JEFFERSON JAMES 2094
MARINO SALVATORE 3780
SMITH DANIEL 0961
JOHNSON SYBIL 8953
PEREZ MARIA 9001
MONTEVERDE ROBERT 3780

The CREATE TABLE statement example creates a table which, at first, is
empty. The table has columns for last names, first names, and phone
numbers, but does not have any rows.

The INSERT statement fills the newly created table with data selected from
the DSN8310.EMP table: the names and phone numbers of employees in
Department D21. (The SELECT statement within the INSERT statement
specifies the data you want selected from one table to be inserted into
another table.)


Example: The following CREATE statement creates a table that contains an
employee's department name as well as the phone number. The SELECT
statement within the INSERT statement fills the DLIST table with data from
rows selected from two existing tables, DSN8310.DEPT and DSN8310.EMP. The
example also illustrates a join of two tables.

CREATE TABLE DLIST
(DEPT CHAR(3) NOT NULL,
DNAME VARCHAR(36) ,
LNAME VARCHAR(15) NOT NULL,
FNAME VARCHAR(12) NOT NULL,
INIT CHAR ,
PHONE CHAR(4) );

INSERT INTO DLIST
SELECT DEPTNO, DEPTNAME, LASTNAME, FIRSTNME, MIDINIT, PHONENO
FROM DSN8310.DEPT, DSN8310.EMP
WHERE DEPTNO = WORKDEPT;


2.2.2.2 Updating Current Values: UPDATE

To change the data in a table, use the UPDATE statement. You can also use
the UPDATE statement to delete a value from a row's column (without
removing the row) by changing the column's value to NULL.

For example, suppose an employee has been relocated. To update several
items of the employee's data in the YEMP work table to reflect the move,
you can execute:

UPDATE YEMP
SET JOB = 'MANAGER ',
PHONENO ='5678'
WHERE EMPNO = '000400';

The SET clause names the columns you want updated and provides the values
you want them changed to. The value you specify can be:

A column name. Replace the column's current value with the contents
of another column in the same row.

A constant. Replace the column's current value with the constant.

A null value. Replace the column's current value with a null value.
The column must have been defined as capable of containing a null
value when the table was created or when the column was added, or an
error occurs.

A host variable. Replace the column's current value with the contents
of the host variable.




A special register. Replace the column's current value with a special
register value:

_ CURRENT DATE
_ CURRENT DEGREE
_ CURRENT PACKAGESET
_ CURRENT SERVER
_ CURRENT SQLID
_ CURRENT TIME
_ CURRENT TIMESTAMP
_ CURRENT TIMEZONE
_ USER


An expression. Replace the column's current value with the value that
results from an expression.


Next, identify the rows to be updated:

_ To update a single row, use a WHERE clause that locates one, and only
one, row

_ To update several rows, use a WHERE clause that locates only the rows
you want to update.


If you omit the WHERE clause; DB2 updates every row in the table or view
with the values you supply.

If DB2 finds an error while executing your UPDATE statement (for instance,
an update value that is too large for the column), it stops updating and
returns error codes in the SQLCODE and SQLSTATE fields in the SQLCA. No
rows in the table are changed (rows already changed, if any, are restored
to their previous values).


Examples: The following statement supplies a missing middle initial and
changes the job for employee 000200.

UPDATE YEMP
SET MIDINIT = 'H', JOB = 'FIELDREP'
WHERE EMPNO = '000200';

The following statement gives everyone in department D11 a $400 raise.
The statement can update several rows.

UPDATE YEMP
SET SALARY = SALARY + 400.00
WHERE WORKDEPT = 'D11';


2.2.2.3 Updating Tables with Referential Constraints

If you are updating a parent table, you cannot modify a primary key for
which dependent rows exist. (Doing so would violate referential
constraints for dependent tables and leave some rows without a parent.)
Also, you cannot give a primary key a null value.

If you are updating a dependent table, any non-null foreign key values
that you enter must match the primary key for each relationship in which
the table is a dependent. For example, department numbers in the employee
table depend on the department numbers in the department table; you can
assign no department to an employee, but you cannot assign an employee to
a department that does not exist.

If an UPDATE against a table with a referential constraint fails, all
changes made during the operation are rolled back.


2.2.2.4 Deleting Rows: DELETE
You can use the DELETE statement to remove entire rows from a table. The
DELETE statement removes zero or more rows of a table, depending on how
many rows satisfy the search condition you specified in the WHERE clause.
If you omit a WHERE clause from a DELETE statement, DB2 removes all the
rows from the table or view you have named. The DELETE statement does not
remove specific columns from the row.

This DELETE statement deletes each row in the YEMP table that has an
employee number 000060.

DELETE FROM YEMP
WHERE EMPNO = '000060';

When this statement is executed, DB2 deletes any row from the YEMP table
that meets the search condition.

If DB2 finds an error while executing your DELETE statement, it stops
deleting data and returns error codes in the SQLCODE and SQLSTATE fields
in the SQLCA. The table's data is not changed.



2.2.2.4.1 Deleting from Tables with Referential Constraints

If a table has a primary key and dependent tables, an attempt to delete a
row must obey the delete rules specified for the table. All delete rules
of all affected relationships must be satisfied in order for the delete
operation to succeed. If a referential constraint is violated, the DELETE
fails.


Delete Rules:

DELETE RESTRICT
The row can be deleted only if no other row depends on it. If a
dependent row exists in the relationship, the DELETE fails.

For example, you cannot delete a department from the department table
if it is still responsible for some project, which is described by a
dependent row in the project table.

DELETE SET NULL
Each nullable column of the foreign key in each dependent row is set
to null. This means that a nullable column is set to null only if it
is a member of a foreign key that references the row being deleted.
Only the dependent rows that are immediate descendents are affected.

For example, you can delete an employee from the employee table even
if the employee manages some department. In that case, the value of
MGRNO is set to null in the department table.

DELETE CASCADE
First the named rows are deleted, then the dependent rows are deleted,
honoring the delete rules of their dependents.

For example, you can delete a department by deleting its row in the
department table; that also deletes the rows for all departments that
report to it, all departments that report to them, and so forth.

If all is successful, all descendent rows are either deleted or their
nullable columns have been nullified depending on the delete rules.
Encountering ON DELETE SET NULL ends one branch of the cascade.

For example, deleting a department from the department table sets
WORKDEPT (in the employee table) to null for every employee assigned
to that department. Because no rows of the employee table are
actually deleted, the delete does not cascade any further.

If a descendent table has a delete rule of RESTRICT and a row is found
such that a descendent row cannot be deleted, the DELETE fails.


The number of rows deleted is returned in SQLERRD(3) in the SQLCA. This
number includes only the number of rows deleted in the table specified in
the DELETE statement. It does not include those rows deleted according to
the CASCADE rule.


Self-Referencing Tables and Cycles: Special restrictions apply if the
table is a member of a cycle of relationships or is self-referencing. See
Chapter 6 of SQL Reference for more information about these restrictions.



2.2.2.4.2 Deleting Every Row in a Table

The DELETE statement is a powerful statement that deletes all rows of a
table unless you specify a WHERE clause that limits the deletion. (With
segmented table spaces, deleting all rows of a table is very fast.) For
example, this statement:

DELETE FROM YDEPT;

deletes every row in the YDEPT table. If the statement is executed, the
table continues to exist (that is, you can insert rows into it) but it is
empty. All existing views and authorizations on the table remain intact
when using DELETE. If you use DROP, all views and authorizations are
dropped which can invalidate plans and packages. Refer to "Dropping
Tables: DROP" in topic 2.2.3 for a description of the DROP statement.


2.2.3 Dropping Tables: DROP

This SQL statement drops the YEMP table:

DROP TABLE YEMP;

Use the DROP TABLE statement with care: When a table is dropped, it loses
its data as well as its definition. When you drop a table, all synonyms,
views, indexes, and referential constraints associated with that table are
also dropped. All authorities granted on the table are lost. Similarly,
a view can be dropped, using the DROP VIEW statement. This does not cause
a loss of data in the table on which the view was created.

With the proper authorization, you can also drop a database. When a
database is dropped, all tables, views, indexes, and referential
constraints defined on that database are also dropped.

DROP TABLE drops all constraints in which the table is a parent or
dependent. When a table is dropped, all indexes on the table (including
the primary index) are implicitly dropped. If a primary index is
explicitly dropped, the definition of its table is changed to incomplete
and an SQL warning message is issued. If a table is defined as
incomplete, programs cannot use the table.

Dropping a table is NOT equivalent to deleting all its rows. Instead, when
you drop a table you also drop all the relationships in which the table
participates, either as parent or dependent. This can affect application
programs that depend on the existence of a parent table, so use DROP
carefully.

For more information on the DROP statement, see Chapter 6 of SQL
Reference.


2.3 Chapter 2-3. Using Subqueries

This chapter presents a conceptual overview of subqueries, shows how to
include subqueries in either a WHERE or a HAVING clause, and shows how to
use correlated subqueries.

Subtopics
2.3.1 Conceptual Overview
2.3.2 Using Subqueries
2.3.3 Using Correlated Subqueries


2.3.1 Conceptual Overview
Suppose you want a list of the employee numbers, names, and commissions of
all employees working on a particular project, say project number MA2111.
The first part of the SELECT statement is easy to write:

SELECT EMPNO, LASTNAME, COMM
FROM DSN8310.EMP
WHERE EMPNO

.
.
.

But you cannot go further because the DSN8310.EMP table does not include
project number data. You do not know which employees are working on
project MA2111 without issuing another SELECT statement against the
DSN8310.EMPPROJACT table.

You can nest one SELECT statement within another to solve this problem.
The inner SELECT statement is called a subquery. The SELECT statement
surrounding the subquery is called the outer SELECT.

SELECT EMPNO, LASTNAME, COMM
FROM DSN8310.EMP
WHERE EMPNO IN
(SELECT EMPNO
FROM DSN8310.EMPPROJACT
WHERE PROJNO = 'MA2111');

To better understand what results from this SQL statement, imagine that
DB2 goes through the following process:

1. DB2 evaluates the subquery to obtain a list of EMPNO values:

.
.
.
(SELECT EMPNO
FROM DSN8310.EMPPROJACT
WHERE PROJNO = 'MA2111');

The evaluation results in an interim result table:

(from DSN8310.EMPPROJACT)
+------+
¦000200¦
+------¦
¦000220¦
+------+

2. The interim result table then serves as a list in the search condition
of the outer SELECT. Effectively, DB2 executes this statement:

SELECT EMPNO, LASTNAME, COMM
FROM DSN8310.EMP
WHERE EMPNO IN
('000200 ', '000220');

As a consequence, the result table looks like this:

EMPNO LASTNAME COMM
Fetch +------------------------------+
1 --_ ¦ 000200 ¦ BROWN ¦ 2217 ¦
+---------+-----------+--------¦
2 --_ ¦ 000220 ¦ LUTZ ¦ 2387 ¦
+------------------------------+



2.3.1.1 Correlation

The purpose of a subquery is to supply information needed to qualify a row
(WHERE clause) or a group of rows (HAVING clause). This is done through
the result table that the subquery produces. Conceptually, the subquery
is invoked whenever a new row or group of rows must be qualified. In
fact, if the subquery is the same for every row or group, it is executed
only once.

A case in point is the previous query. Its content is the same for every
row of the table DSN8310.EMP. Subqueries like this are said to be
uncorrelated.

Some subqueries do vary in content from row to row or group to group. The
mechanism that allows this is called correlation, and the subqueries are
said to be correlated. Correlated subqueries are described on page 2.3.3.
All of the information described preceding that section applies to both
correlated and uncorrelated subqueries.



2.3.1.3 The Subquery Result Table

A subquery must produce a one-column result table unless the EXISTS
keyword is used. This means that the SELECT clause in a subquery must
name a single column or contain a single expression. For example, both of
the following SELECT clauses would be acceptable:

SELECT AVG(SALARY)
SELECT EMPNO

The result table produced by a subquery can have zero or more rows. For
some usages, no more than one row is allowed.


2.3.1.4 Subqueries with UPDATE, DELETE, and INSERT

When you use a subquery in an UPDATE, DELETE, or INSERT statement, the
subquery cannot be based on the same table as the UPDATE, DELETE, or
INSERT statement.

2.3.2 Using Subqueries

There are a number of ways to include a subquery in either a WHERE or
HAVING clause. They are as follows:

_ Basic predicate
_ Quantified Predicates: ALL, ANY, and SOME
_ Using the IN Keyword
_ Using the EXISTS Keyword
_ Correlated subqueries.


2.3.2.1 Basic Predicate

You can use a subquery immediately after any of the comparison operators.
If you do, the subquery can return at most one value. DB2 compares that
value with the value to the left of the comparison operator.

For example, the following SQL statement returns the employee numbers,
names, and salaries for employees whose education level is higher than the
average company-wide education level.

SELECT EMPNO, LASTNAME, SALARY
FROM DSN8310.EMP
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM DSN8310.EMP);

2.3.2.2 Quantified Predicates: ALL, ANY, and SOME

You can use a subquery after a comparison operator followed by the keyword
ALL, ANY, or SOME. When used in this way, the subquery can return zero,
one, or many values, including null values. You use ALL, ANY, and SOME in
the following ways:

_ Use ALL to indicate that the value you have supplied must compare in
the indicated way to all the values the subquery returns. For
example, suppose you use the greater-than comparison operator with
ALL:

.
.
.
WHERE expression > ALL (subquery)

To satisfy this WHERE clause, the value in the expression must be
greater than all the values (that is, greater than the highest value)
returned by the subquery. If the subquery returns an empty set (that
is, no values were selected), the predicate is satisfied.

_ Use ANY or SOME to indicate that the value you have supplied must
compare in the indicated way to at least one of the values the
subquery returns. For example, suppose you use the greater-than
comparison operator with ANY:

.
.
.
WHERE expression > ANY (subquery)

To satisfy this WHERE clause, the value in the expression must be
greater than at least one of the values (that is, greater than the
lowest value) returned by the subquery. If what the subquery returns
is empty, the condition is not satisfied.


The results when a subquery returns one or more null values could in some
cases surprise you. For applicable rules, read the description of
quantified predicates in Chapter 3 of SQL Reference.

2.3.2.3 Using the IN Keyword

You can use IN to say that the value in the expression must be among the
values returned by the subquery. Using IN is equivalent to using "= ANY"
or "= SOME."


2.3.2.4 Using the EXISTS Keyword

In the subqueries presented thus far, DB2 evaluates the subquery and uses
the result as part of the WHERE clause of the outer SELECT. In contrast,
when you use the keyword EXISTS, DB2 simply checks whether the subquery
returns one or more rows. If it does, the condition is satisfied; if it
does not (if it returns no rows), the condition is not satisfied. For
example:

SELECT EMPNO,LASTNAME
FROM DSN8310.EMP
WHERE EXISTS
(SELECT *
FROM DSN8310.PROJ
WHERE PRSTDATE > '1986-01-01'); 2.3.3 Using Correlated Subqueries

In the subqueries previously described, DB2 executes the subquery once,
substitutes the result of the subquery in the right side of the search
condition, and evaluates the outer-level SELECT based on the value of the
search condition. You can also write a subquery that DB2 has to
re-evaluate when it examines a new row (WHERE clause) or group of rows
(HAVING clause) as it executes the outer SELECT. This is called a
correlated subquery.


In the example, the search condition holds if any project represented in
the DSN8310.PROJ table has an estimated start date which is later than 1
January 1986. This example does not show the full power of EXISTS,
because the result is always the same for every row examined for the outer
SELECT. As a consequence, either every row appears in the results, or
none appear. In a more powerful example, the subquery itself would be
correlated, and would change from row to row.

As shown in the example, you do not need to specify column names in the
subquery of an EXISTS clause. Instead, you can code SELECT *. You can
also use the EXISTS keyword with the NOT keyword in order to select rows
when the data or condition you specify does not exist; that is, you can
code

WHERE NOT EXISTS (SELECT ...);



2.3.3 Using Correlated Subqueries

In the subqueries previously described, DB2 executes the subquery once,
substitutes the result of the subquery in the right side of the search
condition, and evaluates the outer-level SELECT based on the value of the
search condition. You can also write a subquery that DB2 has to
re-evaluate when it examines a new row (WHERE clause) or group of rows
(HAVING clause) as it executes the outer SELECT. This is called a
correlated subquery.


2.3.3.1 An Example of a Correlated Subquery

Suppose that you want a list of all the employees whose education levels
are higher than the average education levels in their respective
departments. To get this information, DB2 must search the DSN8310.EMP
table. For each employee in the table, DB2 needs to compare the
employee's education level to the average education level for the
employee's department.

This is the point at which a correlated subquery differs from a
noncorrelated subquery. In the earlier example on subqueries, the purpose
was to compare the education level to the average of the entire company,
thus looking at the entire table. With the correlated subquery, only the
department which corresponds to the particular employee in question is
evaluated.

In the subquery, you tell DB2 to compute the average education level for
the department number in the current row. A query that does this follows:

SELECT EMPNO, LASTNAME, WORKDEPT, EDLEVEL
FROM DSN8310.EMP X
WHERE EDLEVEL >
(SELECT AVG(EDLEVEL)
FROM DSN8310.EMP
WHERE WORKDEPT = X.WORKDEPT);

A correlated subquery looks like a noncorrelated one, except for the
presence of one or more correlated references. In the example, the single
correlated reference is the occurrence of X.WORKDEPT in the WHERE clause
of the subselect. In this clause, the qualifier X is the correlation name
defined in the FROM clause of the outer SELECT statement. X is introduced
as the name of one of the instances of the table DSN8310.EMP.

Consider what happens when the subquery is executed for a given row of
DSN8310.EMP. Before it is executed, the occurrence of X.WORKDEPT is
replaced with the value of the WORKDEPT column for that row. Suppose, for
example, that the row is for CHRISTINE HAAS. Her work department is A00,
which is the value of WORKDEPT for this row. The subquery executed for
this row is therefore:

(SELECT AVG(EDLEVEL)
FROM DSN8310.EMP
WHERE WORKDEPT = 'A00');

Thus, for the row considered, the subquery produces the average education
level of Christine's department. This is then compared in the outer
statement to Christine's own education level. For some other row for
which WORKDEPT has a different value, that value appears in the subquery
in place of A00. For example, for the row for MICHAEL L THOMPSON, this
value is B01, and the subquery for his row delivers the average education
level for department B01.

The result table produced by the query has the following values:

(from DSN8310.EMP)
EMPNO LASTNAME WORKDEPT EDLEVEL
Fetch +------------------------------------------+
1--_ ¦ 000010 ¦ HAAS ¦ A00 ¦ 18 ¦
+---------+-----------+----------+---------¦
2--_ ¦ 000030 ¦ KWAN ¦ C01 ¦ 20 ¦
+---------+-----------+----------+---------¦
3--_ ¦ 000090 ¦ HENDERSON ¦ E11 ¦ 16 ¦
+---------+-----------+----------+---------¦
4--_ ¦ 000110 ¦ LUCCHESI ¦ A00 ¦ 19 ¦
+---------+-----------+----------+---------¦
_ _ _ _
_ _ _ _
_ _ _ _


2.3.3.2 Using Correlated Names in References

A correlated reference can appear only in a search condition in a
subquery. The reference should be of the form X.C, where X is a
correlation name and C is the name of a column in the table that X
represents.

The correlation name is defined in the FROM clause of some query. This
query could be the outer-level SELECT, or any of the subqueries that
contain the reference. Suppose, for example, that a query contains
subqueries A, B, and C, and that A contains B and B contains C. Then a
correlation name used in C could be defined in B, A, or the outer SELECT.

You can define a correlation name for each table name appearing in a FROM
clause. Simply append the correlation name after its table name. Leave
one or more blanks between a table name and its correlation name, and
place a comma after the correlation name if it is followed by another
table name. The following FROM clause, for example, defines the
correlation names TA and TB for the tables TABLEA and TABLEB, and no
correlation name for the table TABLEC.

FROM TABLEA TA, TABLEC, TABLEB TB

Any number of correlated references can appear in a subquery. There are
no restrictions on variety. For example, one correlated name in a
reference can be defined in the outer SELECT, while another can be defined
in a containing subquery.

2.3.3.4 Using Correlated Subqueries in a DELETE Statement

When you use a correlated subquery in a DELETE statement, the correlation
name represents the row you delete. DB2 evaluates the correlated subquery
once for each row in the table named in the DELETE statement to decide
whether or not to delete the row.

For example, suppose that a department considers a project to be completed
when the combined amount of time currently spent on it is half a person's
time or less. The department then deletes the rows for that project from
the DSN8310.PROJ table. In the example statements that follow, PROJ and
PROJACT are independent tables.

DELETE FROM DSN8310.PROJ X
WHERE .5 >
(SELECT SUM(ACSTAFF)
FROM DSN8310.PROJACT
WHERE PROJNO = X.PROJNO);

To process this statement, DB2 determines for each project (represented by
a row in the DSN8310.PROJ table) whether or not the combined staffing for
that project is less than 0.5. If it is, DB2 deletes that row from the
DSN8310.PROJ table.

To continue this example, suppose a row in the DSN8310.PROJ table has been
deleted. Rows related to the deleted project in the DSN8310.PROJACT table
must also be deleted. To do this, use:

DELETE FROM DSN8310.PROJACT X
WHERE NOT EXISTS
(SELECT *
FROM DSN8310.PROJ
WHERE PROJNO = X.PROJNO);

DB2 determines, for each row in the DSN8310.PROJACT table, whether a row
with the same project number exists in the DSN8310.PROJ table. If not,
the DSN8310.PROJACT row is deleted.

A subquery of a DELETE statement must not reference the same table from
which rows are deleted. In the sample application, some departments
administer other departments. Consider the following statement, which
seems to delete every department that does not administer another one:

DELETE FROM DSN8310.DEPT X
WHERE NOT EXISTS (SELECT * FROM DSN8310.DEPT
WHERE ADMRDEPT = X.DEPTNO);

The result of an operation must not depend on the order in which rows of a
table are accessed. If this statement could be executed, its result would
depend on whether the row for any department was accessed before or after
deleting the rows for the departments it administers. Hence, the operation
is prohibited.

The same rule extends to dependent tables involved in referential
constraints. If a DELETE statement has a subquery that references a table
that is involved in the DELETE operation, the last delete rule in the path
to that table must be RESTRICT. For example, without referential
constraints, the following statement deletes departments from the
department table whose managers are not listed correctly in the employee
table:

DELETE FROM DSN8310.DEPT THIS
WHERE NOT DEPTNO =
(SELECT WORKDEPT
FROM DSN8310.EMP
WHERE EMPNO = THIS.MGRNO);

With the referential constraints defined for the sample tables, the
statement causes an error. The delete operation involves the table
referred to in the subquery (DSN8310.EMP is a dependent of DSN8310.DEPT)
and the last delete rule in the path to EMP is SET NULL, not RESTRICT. If
the statement could be executed, its results would again depend on the
order in which rows were accessed.


2.4 Chapter 2-4. Using SPUFI: Executing SQL from Your Terminal

This chapter explains how to enter and execute SQL statements at a TSO
terminal using the SPUFI (SQL processor using file input) facility. You
can execute most of the interactive SQL examples shown in "Section 2.
Using SQL Queries" by following the instructions provided in this chapter
and using the sample tables shown in Appendix A, "DB2 Sample Tables" in
topic APPENDIX1.1.

The instructions assume that ISPF is available to you. statement from
your terminal. This chapter also describes how to use SPUFI to perform
other tasks:

_ Display the names of tables you can use
_ Display the names of the columns in a table
_ Allocate a partitioned data set to use as an input data set
_ Change the SPUFI defaults.



2.4.1 Step 1. Invoke SPUFI and Allocate an Input Data Set

To invoke SPUFI, select SPUFI from the DB2I Primary Option Menu as shown
in Figure 6.


+----------------------------------------------------------------------------------+
¦ ¦
¦ DSNEPRI DB2I PRIMARY OPTION MENU SSID: DSN ¦
¦ COMMAND ===> 1 ¦
¦ ¦
¦ Select one of the following DB2 functions and press ENTER. ¦
¦ ¦
¦ 1 SPUFI (Process SQL statements) ¦
¦ 2 DCLGEN (Generate SQL and source language declarations) ¦
¦ 3 PROGRAM PREPARATION (Prepare a DB2 application program to run) ¦
¦ 4 PRECOMPILE (Invoke DB2 precompiler) ¦
¦ 5 BIND/REBIND/FREE (BIND, REBIND, or FREE plans or packages) ¦
¦ 6 RUN (RUN an SQL program) ¦
¦ 7 DB2 COMMANDS (Issue DB2 commands) ¦
¦ 8 UTILITIES (Invoke DB2 utilities) ¦
¦ D DB2I DEFAULTS (Set global parameters) ¦
¦ X EXIT (Leave DB2I) ¦
¦ ¦
¦ ¦
¦ ¦
¦ PRESS: END to exit HELP for more information ¦
¦ ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 6. The DB2I Primary Option Menu with Option 1 Selected

The SPUFI panel is then displayed as shown in Figure 7.

Data you enter on the SPUFI panel tells DB2 how to process your input data
set, and where to send the output.

When the SPUFI panel is first displayed, enter the name of an input data
set (where you will put SQL statements you want DB2 to execute) and an
output data set (where DB2 will put the results of your queries). You can
also enter new processing option defaults to specify how you want the next
SPUFI processing sequence to proceed.

The next time (and subsequent times) the SPUFI panel is displayed, the
data entry fields on the panel will contain the values that were set on
the panel previously. You can specify data set names and processing
options each time the SPUFI panel displays, as needed. Values you do not
change will remain in effect.


+----------------------------------------------------------------------------------+
¦ ¦
¦ DSNESP01 SPUFI SSID: DSN ¦
¦ ===> ¦
¦ Enter the input data set name: (Can be sequential or partitioned) ¦
¦ 1 DATA SET NAME..... ===> EXAMPLES(XMP1) ¦
¦ 2 VOLUME SERIAL..... ===> (Enter if not cataloged) ¦
¦ 3 DATA SET PASSWORD. ===> (Enter if password protected) ¦
¦ ¦
¦ Enter the output data set name: (Must be a sequential data set) ¦
¦ 4 DATA SET NAME..... ===> RESULT ¦
¦ ¦
¦ Specify processing options: ¦
¦ 5 CHANGE DEFAULTS... ===> Y (Y/N - Display SPUFI defaults panel?) ¦
¦ 6 EDIT INPUT........ ===> Y (Y/N - Enter SQL statements?) ¦
¦ 7 EXECUTE........... ===> Y (Y/N - Execute SQL statements?) ¦
¦ 8 AUTOCOMMIT........ ===> Y (Y/N - Commit after successful run?) ¦
¦ 9 BROWSE OUTPUT..... ===> Y (Y/N - Browse output data set?) ¦
¦ ¦
¦ For remote SQL processing: ¦
¦ 10 CONNECT LOCATION ===> ¦
¦ ¦
¦ ¦
¦ PRESS: ENTER to process END to exit HELP for more information ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 7. The SPUFI Panel Filled In


Fill out the SPUFI panel as follows:

1, 2, 3 INPUT DATA SET NAME
Identify the input data set in fields 1 through 3. This data set
contains one or more SQL statements that you want to execute.

_ Allocate this data set before you invoke SPUFI. The name must
conform to standard TSO naming conventions.

_ The data set can be empty before you begin the session. You can
then add the SQL statements by editing the data set from SPUFI.

_ The data set can be either sequential or partitioned, but it must
have the following DCB characteristics:

- A record format (RECFM) of either F or FB

- A logical record length (LRECL) of either 79 or 80. Use 80
for any data set that was not created by the EXPORT command
of QMF


_ Data in the data set can begin in column 1. It can extend to
column 71 if the logical record length is 79, and to column 72 if
the logical record length is 80. The last 8 bytes of the records
are assumed to be reserved for sequence numbers.


If you use this panel a second time, the name of the data set you
used previously appears. To create a new member of an existing
partitioned data set, change only the member name. If you need to
allocate an input data set, refer to ISPF/PDF Version 3 for MVS Guide
and Reference for information on ISPF and allocating data sets.

4 OUTPUT DATA SET NAME
Enter the name of a data set to receive the output of the SQL
statement. The data set need not be allocated previously.

The simplest choice is shown in Figure 7. In this example, RESULT
was entered. SPUFI allocates a data set named userid.RESULT and
sends all output to that data set. If a data set named userid.RESULT
already exists, SPUFI sends DB2 output to it, replacing all existing
data.

Look at the processing options:

5 CHANGE DEFAULTS
The SPUFI defaults need not be changed for this example. However, if
you specify Y(YES) you can look at the SPUFI defaults panel. See
"Step 2. Change SPUFI Defaults (Optional)" in topic 2.4.2 for more
information about the values you can specify and how they affect
SPUFI processing and output characteristics.

6 EDIT INPUT
To edit the input data set, leave Y(YES) on line 6. You can use the
ISPF editor to create a new member of the input data set and enter
SQL statements in it. (To process a data set that already contains a
set of SQL statements you want to execute immediately, enter N(NO).
Specifying N bypasses the step described in "Step 3. Enter SQL
Statements" in topic 2.4.3.)

7 EXECUTE
To execute SQL statements contained in the input data set, leave
Y(YES) on line 7.

8 AUTOCOMMIT
To make changes to the DB2 data permanent, leave Y(YES) on line 8.
Specifying Y makes SPUFI issue COMMIT if all statements execute
successfully. If all statements do not execute successfully, SPUFI
issues a ROLLBACK statement, and changes already made to the file
(back to the last commit point) are deleted. We suggest that you
read about the COMMIT and the ROLLBACK functions in "The ISOLATION
Option" in topic 4.1.2.7.2 or Chapter 6 of SQL Reference.

9 BROWSE OUTPUT
To look at the results of your query, leave Y(YES) on line 9. The
results are saved in the output data set. You can look at them at
any time, until you delete or write over the data set.

10 CONNECT LOCATION
Specify the name of the application server, if applicable, to which
you want to submit SQL statements for execution. SPUFI will then
issue a type 1 CONNECT statement to this application server.

An installation job is added to bind SPUFI remotely. SPUFI is bound
locally as a package. Subsequent processing of SQL statements in the
input data set is based upon successful execution of the CONNECT
statement. If the connect request fails, SQL return codes and error
messages are placed in the output data set.



When you finish with the SPUFI panel, press the ENTER key. Because you
specified YES on line 5 of the SPUFI panel, the next panel you see is the
SPUFI Defaults panel, as shown in Figure 8.


+----------------------------------------------------------------------------------+
¦ ¦
¦ DSNESP02 CURRENT SPUFI DEFAULTS SSID: DSN ¦
¦ ===> ¦
¦ Enter the following to control your SPUFI session: ¦
¦ 1 ISOLATION LEVEL ... ===> RR (RR=Repeatable Read, CS=Cursor Stability) ¦
¦ 2 MAX SELECT LINES .. ===> 250 (Maximum number of lines to be ¦
¦ returned from a SELECT) ¦
¦ Output data set characteristics: ¦
¦ 3 RECORD LENGTH...... ===> 4092 (LRECL= logical record length) ¦
¦ 4 BLOCKSIZE ......... ===> 4096 (Size of one block) ¦
¦ 5 RECORD FORMAT...... ===> VB (RECFM= F, FB, FBA, V, VB, or VB) ¦
¦ 6 DEVICE TYPE........ ===> SYSDA (Must be a DASD unit name) ¦
¦ ¦
¦ Output format characteristics: ¦
¦ 7 MAX NUMERIC FIELD . ===> 33 (Maximum width for numeric field) ¦
¦ 8 MAX CHAR FIELD .... ===> 80 (Maximum width for character field) ¦
¦ 9 COLUMN HEADING .... ===> NAMES (NAMES, LABELS, ANY, or BOTH) ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
¦ PRESS: ENTER to process END to exit HELP for more information ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 8. The SPUFI Defaults Panel


2.4.2 Step 2. Change SPUFI Defaults (Optional)

Default values are provided for each user the first time SPUFI is used.
Defaults are set for all options except the DB2 subsystem name. Any
changes you make to these values remain in effect until the values are
changed again. Initial default values are shown in Figure 8 in
topic 2.4.1.

Specify values for the following options on the CURRENT SPUFI DEFAULTS
panel:

1 ISOLATION LEVEL
See "The ISOLATION Option" in topic 4.1.2.7.2 for more information.

2 MAX SELECT LINES
The maximum number of output lines to be returned as a result of
executing a SELECT statement. To limit the number of rows retrieved,
enter another maximum number.

3 RECORD LENGTH
The record length must be at least 80 bytes. The default value
allows a 4092-byte record.

4 BLOCKSIZE
Follow normal block size selection rules. For F, the block size is
equal to record length. For FB and FBA, choose a block size that is
an even multiple of LRECL. For VB and VBA only, the block size must
be 4 bytes larger than the block size for FB or FBA.

5 RECORD FORMAT
The record format default is VB (variable-length blocked).

6 DEVICE TYPE
SYSDA specifies that MVS is to select an appropriate direct access
storage device.

7 MAX NUMERIC FIELD
The maximum width of a numeric value column in your output.

8 MAX CHAR FIELD
The maximum width of a character value column in your output.
DATETIME and GRAPHIC data strings are externally represented as
characters, and so they fall under the default values for character
fields.

9 COLUMN HEADING
You can specify NAMES, LABELS, ANY or BOTH for column headings.

_ NAME (default) uses column names only.
_ LABEL uses column labels. Leave the title blank if there is no
label.
_ ANY uses existing column labels or column names.
_ BOTH creates two title lines, one with names and one with labels.


When you have specified SPUFI options, press the ENTER key to continue.
SPUFI continues by processing the next processing option for which YES has
been specified. If all other processing options are NO, SPUFI continues
by displaying the SPUFI panel.

If you press the END key, you return to the SPUFI panel, but all changes
made on the SPUFI Defaults panel are lost. If you press ENTER, your
changes are saved.


2.4.3 Step 3. Enter SQL Statements

Next, SPUFI lets you edit the input data set. Initially, editing consists
of entering an SQL statement into the input data set. You can also edit
an input data set that contains SQL statements and you can change, delete,
or insert SQL statements.

The ISPF Editor shows you an empty EDIT panel.

On the panel, use the ISPF EDIT program to enter SQL statements that you
want to execute, as shown in Figure 9.

Move the cursor to the first input line and enter the first part of an SQL
statement. You can enter the rest of the SQL statement on subsequent
lines, as shown in Figure 9. Line indentation and entry on several lines
are not necessary, although this format is easier to read.

You can put more than one SQL statement in the input data set. You can
put an SQL statement on one line of the input data set or on more than one
line. When the data set is processed, DB2 executes the statements one
after the other. Do not put more than one SQL statement on a single line.
The first one is executed, but other SQL statements on the same line are
ignored.

When using SPUFI, end each SQL statement with a semicolon (;). This tells
SPUFI that the statement is complete.

When you have entered the SQL statements that you want, press the END PF
key to save the file and to begin execution.


+----------------------------------------------------------------------------------+
¦ ¦
¦ EDIT --------userid.EXAMPLES(XMP1) --------------------- COLUMNS 001 072 ¦
¦ COMMAND INPUT ===> SAVE SCROLL ===> PAGE ¦
¦ ********************************** TOP OF DATA *********************** ¦
¦ 000100 SELECT LASTNAME, FIRSTNME, PHONENO ¦
¦ 000200 FROM DSN8310.EMP ¦
¦ 000300 WHERE WORKDEPT= 'D11' ¦
¦ 000400 ORDER BY LASTNAME; ¦
¦ ********************************* BOTTOM OF DATA ********************* ¦
¦ ¦
¦ ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 9. The EDIT Panel: After Entering an SQL Statement

Pressing the END PF key saves the data set. You can save the data set and
continue editing it by entering the SAVE command. In fact, it is a good
practice to save the data set after every 10 minutes or so of editing.

Figure 9 shows what the panel looks like if you enter the sample SQL
statement, followed by a SAVE command.

The editing step is bypassed when you reset the EDIT INPUT processing
option by specifying:

EDIT INPUT ... ===> NO


You can put comments about SQL statements either on separate lines or on
the same line. In either case, two hyphens (--) are used to begin a
comment. Everything to the right of the two hyphens is ignored by DB2.


2.4.4 Step 4. Process SQL Statements

SPUFI passes the input data set to DB2 for processing. The SQL statement
in the input data set EXAMPLES(XMP1) is executed. Output is sent to the
output data set userid.RESULT.

The DB2 processing step is bypassed when you specify the EXECUTE
processing option:

EXECUTE ..... ===> NO


2.4.4.1 Quitting While the Statement Is Executing

Your SQL statement might take a long time to execute, depending on how
large a table DB2 has to search, or on how many rows DB2 has to process.
To interrupt DB2's execution, press the PA1 key and respond to the
prompting message that asks you if you really want to stop processing.
This cancels the executing SQL statement and returns you to the ISPF-PDF
menu.

What happens to the output data set? This depends on how far execution
has progressed before you interrupted the execution of the input data set.
DB2 might not have opened the output data set yet, or the output data set
might contain all or part of the results data produced so far.


2.4.5 Step 5. Browse the Output

SPUFI formats and displays the output data set using the ISPF Browse
program. The output from the sample program is shown in Figure 10. An
output data set contains these items for each SQL statement executed by
DB2:

_ The SQL statement that was executed, copied from the input data set

_ The results of executing the SQL statement

_ A set of messages about the execution of the SQL statement

When executing a SELECT statement using SPUFI, an error-free result is
indicated by the message "SQLCODE IS 100." If the message SQLCODE IS
100 is the only result of a SELECT statement executed using SPUFI, DB2
was unable to find any rows that satisfied the condition specified by
the statement.

For all other types of SQL statements executed with SPUFI, an
error-free result is indicated by the message "SQLCODE IS 0."

_ Errors return an SQLSTATE code to the SPUFI panel

_ At the end of the data set are summary statistics that describe the
execution of the input data set as a whole.



+------------------------------------------------------------------------------------------+
¦ ¦
¦ ¦
¦ ¦
¦ BROWSE-- userid.RESULT COLUMNS 001 072 ¦
¦ COMMAND INPUT ===> SCROLL ===> PAGE ¦
¦ --------+---------+---------+---------+---------+---------+---------+---------+ ¦
¦ SELECT LASTNAME, FIRSTNME, PHONENO 00010000 ¦
¦ FROM DSN8310.EMP 00020000 ¦
¦ WHERE WORKDEPT = 'D11' 00030000 ¦
¦ ORDER BY LASTNAME; 00040000 ¦
¦ ---------+---------+---------+---------+---------+---------+---------+---------+ ¦
¦ LASTNAME FIRSTNME PHONENO ¦
¦ ADAMSON BRUCE 4510 ¦
¦ BROWN DAVID 4501 ¦
¦ JOHN REBA 0672 ¦
¦ JONES WILLIAM 0942 ¦
¦ LUTZ JENNIFER 0672 ¦
¦ PIANKA ELIZABETH 3782 ¦
¦ SCOUTTEN MARILYN 1682 ¦
¦ STERN IRVING 6423 ¦
¦ WALKER JAMES 2986 ¦
¦ YAMAMOTO KIYOSHI 2890 ¦
¦ YOSHIMURA MASATOSHI 2890 ¦
¦ DSNE610I NUMBER OF ROWS DISPLAYED IS 11 ¦
¦ DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100 ¦
¦ ---------+---------+---------+---------+---------+---------+---- ¦
¦ ---------+---------+---------+---------+---------+---------+---- ¦
¦ DSNE617I COMMIT PERFORMED, SQLCODE IS 0 ¦
¦ DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ¦
¦ ---------+---------+---------+---------+---------+---------+---- ¦


Figure 10. Result Data Set from the Sample Problem



3.0 Section 3. Coding SQL in Your Host Application Program

3.1 Chapter 3-1. Basics of Coding SQL in an Application Program

Suppose you are coding a COBOL application program to access data in a DB2
database. When your program executes an SQL statement, the program needs
to communicate with DB2. When DB2 completes processing an SQL statement,
DB2 sends back a return code; your program should test the return code to
examine the results of the operation.

To communicate with DB2, you need to:

_ Delimit SQL statements, as described in "Delimiting an SQL Statement"
in topic 3.1.2.

_ Declare the tables you use, as described in "Declaring Table and View
Definitions" in topic 3.1.3. (This is optional.)

_ Declare the data items used to pass data between DB2 and a host
language, as described in "Accessing Data Using Host Variables and
Host Structures" in topic 3.1.4.

_ Code SQL statements to access DB2 data. See "Accessing Data Using
Host Variables and Host Structures" in topic 3.1.4.

The SQL language is described in "Section 2. Using SQL Queries" in
topic 2.0 and in SQL Reference. Details about how to use SQL
statements within an application program are described in "Chapter
3-4. Embedding SQL Statements in Host Languages" in topic 3.4.

_ Declare a communications area (SQLCA) or handle exceptional conditions
that are indicated with return codes from DB2, in the SQLCA. See
"Checking the Execution of SQL Statements" in topic 3.1.5 for more
information.



In addition to these basic requirements, you should also consider several
special topics in "Chapter 3-2. Using a Cursor to Retrieve a Set of Rows"
in topic 3.2 and "Chapter 3-3. Using DCLGEN" in topic 3.3. "Chapter 3-2.
Using a Cursor to Retrieve a Set of Rows" discusses how you can use a
cursor in your application program to select a set of rows and then
process the set one row at a time. "Chapter 3-3. Using DCLGEN" discusses
how to use DB2's declarations generator, DCLGEN, to obtain accurate SQL
DECLARE statements for tables and views.

This book includes information about using SQL in application programs
written in assembler, C, COBOL, FORTRAN, and PL/I. You can also use SQL
in application programs written in the following languages:

Ada. See IBM Ada/370 SQL Module Processor for DB2 Database Manager
User's Guide for more information about writing applications in Ada.

APL2 (*). See APL2 Programming: Using Structured Query Language (SQL)
for more information about writing applications in APL2.

BASIC. See IBM BASIC Language Reference for more information about
writing applications in BASIC.

IBM SAA AD/Cycle* Prolog/MVS & VM Version 1. See IBM SAA AD/Cycle
Prolog/MVS & VM Programmer for more information about writing
applications in Prolog/MVS & VM.

(*) Trademark of the IBM Corporation.


3.1.1 Conventions Used in Examples of Coding SQL Statements

The SQL statements shown in this section use the following conventions:

_ The SQL statement is coded as part of a COBOL application program.
Each SQL example is shown on several lines, with each clause of the
statement on a separate line.

_ The APOST and APOSTSQL precompiler options are assumed (although they
are not the defaults). Character string literals within SQL and host
language statements are delimited by apostrophes (').

_ The SQL statements access data in the sample tables that are shipped
as part of DB2. Those tables contain data that a manufacturing
company might keep about its employees and its current projects. They
are described in Appendix A, "DB2 Sample Tables" in topic APPENDIX1.1.

_ An SQL example does not necessarily show the complete syntax of an SQL
statement. For the complete description and syntax of any of the
statements described in this book, see Chapter 6 of SQL Reference.

_ Examples do not take referential constraints into account. For more
information about how referential constraints affect SQL statements,
and examples of SQL statements operating with referential constraints,
see "Chapter 2-2. Creating Tables and Modifying Data" in topic 2.2.


Some of the examples vary from these conventions. Exceptions are noted
where they occur.


3.1.2 Delimiting an SQL Statement

Bracket an SQL statement in your program between EXEC SQL and a statement
terminator. The terminators for the languages described in this book are:

Language SQL Statement Terminator

Assembler End of line or end of last continued line

C Semicolon (;)

COBOL END-EXEC

FORTRAN End of line or end of last continued line

PL/I Semicolon (;)



For example, use EXEC SQL and END-EXEC to delimit an SQL statement in a
COBOL program, like this:

EXEC SQL
an SQL statement
END-EXEC.




Before your program issues SQL statements that retrieve, update, delete,
or insert data, you can declare the tables and views your program accesses
by including an SQL DECLARE statement in your program.

You do not have to declare tables or views, but there are advantages if
you do. One advantage is documentation; for example, the DECLARE
statement specifies the structure of the table or view you are working
with, and the data type of each column. You can refer to the DECLARE
statement for the column names and data types in the table or view.
Another advantage is that the DB2 precompiler uses your declarations to
make sure you have used correct column names and data types in your SQL
statements. The DB2 precompiler issues a warning message when the column
names and data types do not correspond to the SQL DECLARE statements in
your program.

A way to declare a table or view is to code a DECLARE statement in the
WORKING-STORAGE SECTION or LINKAGE SECTION within the DATA DIVISION of
your COBOL program. Specify the name of the table and list each column
and its data type. When you declare a table or view, you specify DECLARE
table-name TABLE regardless of whether the table-name refers to a table or
a view.

For example, the DECLARE TABLE statement for the DSN8310.DEPT table looks
like this:

EXEC SQL
DECLARE DSN8310.DEPT TABLE
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6) ,
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16) )

END-EXEC.

As an alternative to coding the DECLARE statement yourself, you can use
DCLGEN, the declarations generator supplied with DB2. For more
information about using DCLGEN, see "Chapter 3-3. Using DCLGEN" in
topic 3.3.




3.1.4 Accessing Data Using Host Variables and Host Structures

You can access data using host variables and host structures.

A host variable is a data item declared in the host language (in this
case, COBOL) for use within an SQL statement. Using host variables, you
can:

_ Retrieve data and put it into the host variable for use by the
application program

_ Use the data in the host variable to insert into a table or to change
the contents of a row

_ Use the data in the host variable when evaluating a WHERE or HAVING
clause.

Etc.


A host structure is a group of host variables that is referred to by a
single name in an SQL statement. Host structures are defined by
statements of the host language.


3.1.4.1 Using Host Variables

Any valid host variable name can be used in an SQL statement. The name
must be declared in the host program before it is used. (For more
information see the appropriate language section in "Chapter 3-4.
Embedding SQL Statements in Host Languages" in topic 3.4.)

In your application program written in the language of your choice, the
host variable declaration should as close as possible match the types of
the associated data in the data base to get the best performance. For
more performance suggestions, see "Section 5. Additional Programming
Techniques" in topic 5.0 and Volume 3 of Administration Guide.

A host variable can be used to represent a data value but cannot be used
to represent a table, view, or column name. (Table, view, or column names
can be specified at execution time using dynamic SQL. See "Chapter 5-1.
Coding Dynamic SQL in Application Programs" in topic 5.1 for more
information.)

Host variables follow the naming conventions of the host language. (In
this chapter, COBOL is assumed to be the host language.) Host variables
used within SQL statements must be preceded by a colon (:) to tell DB2
that the variable is not a column name. (4) Host variables outside of SQL
statements must not be preceded by a colon.

For more information about declaring host variables, see the appropriate
language section:

_ Assembler: "Using Host Variables" in topic 3.4.1.4
_ C: "Using Host Variables" in topic 3.4.2.4
_ COBOL: "Using Host Variables" in topic 3.4.3.4
_ FORTRAN: "Using Host Variables" in topic 3.4.4.4
_ PL/I: "Using Host Variables" in topic 3.4.5.4.

(4) The colon is required by the SQL standards and other
implementations of SQL. (Although the colon is currently
optional in certain contexts, this feature could be withdrawn
in future releases of DB2. Therefore it is always best to
use the colon. A message is issued when a colon does not
precede the name of a host variable in an SQL statement.)


3.1.4.1.1 Retrieving Data into a Host Variable

You can use a host variable to specify a program data area that is to
contain the column values of a retrieved row or rows.


Retrieving a Single Row of Data: The INTO clause of the SELECT statement
names one or more host variables to contain the column values returned.
The elementary data items involved correspond one-for-one with the list of
column names in the SELECT list.

For example, suppose you are retrieving the EMPNO, LASTNAME, and WORKDEPT
column values from rows in the DSN8310.EMP table. You can define a data
area in your program to hold each column, then name the data areas with an
INTO clause, as in the following example (where each host variable is
preceded by a colon):

EXEC SQL
SELECT EMPNO, LASTNAME, WORKDEPT
INTO :CBLEMPNO, :CBLNAME, :CBLDEPT
FROM DSN8310.EMP
WHERE EMPNO = :EMPID
END-EXEC.

In the DATA DIVISION of the program, the host variables CBLEMPNO, CBLNAME,
and CBLDEPT must be declared such that each is compatible with the data
type contained in the DSN8310.EMP table columns EMPNO, LASTNAME, and
WORKDEPT.

If the SELECT statement returns more than one row, this is an error, and
any data returned is undefined and unpredictable. To avoid this error see
"Retrieving Multiple Rows of Data."


Specifying a List of Items in a SELECT Clause: When specifying a list of
items in the SELECT clause, you are not restricted to the column names of
tables and views. A set of column values intermixed with host variable
values and constants can be returned. For example:

MOVE 4476 TO RAISE.
MOVE '000220' TO PERSON.
EXEC SQL
SELECT EMPNO, LASTNAME, SALARY, :RAISE, SALARY + :RAISE
INTO :EMP-NUM, :PERSON-NAME, :EMP-SAL, :EMP-RAISE, :EMP-TTL
FROM DSN8310.EMP
WHERE EMPNO = :PERSON
END-EXEC.


The results are shown below with column headings that represent the names
of the host variables:

EMP-NUM PERSON-NAME EMP-SAL EMP-RAISE EMP-TTL
======= =========== ======= ========= =======
000220 LUTZ 29840 4476 34316


Retrieving Multiple Rows of Data: If you are unsure about the number of
rows that will be returned, or if you expect that more than one row will
be returned, then you must use an alternative to the SELECT ... INTO
statement.

DB2 has a mechanism called a cursor that enables an application to process
a set of rows and retrieve one row at a time from the result table. This
mechanism is described in "Chapter 3-2. Using a Cursor to Retrieve a Set
of Rows" in topic 3.2.


3.1.4.1.2 Inserting and Updating Data

You can set or change a value in a DB2 table to the value of a host
variable. Use the host variable name in the SET clause of UPDATE or the
VALUES clause of INSERT. This example changes an employee's phone number:

EXEC SQL
UPDATE DSN8310.EMP
SET PHONENO = :NEWPHONE
WHERE EMPNO = :EMPID
END-EXEC.


3.1.4.1.3 Searching Data
You can use a host variable to specify a value in the predicate of a
search condition or to replace a constant in an expression. For example,
if you have defined a field called EMPID that contains an employee number,
you can retrieve the name of the employee whose number is 000110 with:

MOVE '000110' TO EMPID.
EXEC SQL
SELECT LASTNAME
INTO :PGM-LASTNAME
FROM DSN8310.EMP
WHERE EMPNO = :EMPID
END-EXEC.

3.1.4.1.4 Using Indicator Variables with Host Variables

Indicator variables are small integers that are used to:

Indicate whether the values of associated host variables are null

Verify that the value of a retrieved character string has not been
truncated when retrieved

Insert null values from host variables into columns.

Retrieving Data into Host Variables: If the value for the column you are
retrieving is null, DB2 puts a negative value in the indicator variable.
If it is null because of a numeric or character conversion error, or an
arithmetic expression error, DB2 sets the indicator variable to -2. See
"Handling Arithmetic or Conversion Errors" in topic 3.1.5.3 for more
information.

If you do not use an indicator variable and DB2 retrieves a null value, an
error results.

When DB2 retrieves the value of a column, you can test the indicator
variable. If the indicator variable's value is less than zero, the column
value is null. When the column value is null, DB2 puts nothing into the
host variable; its value is unchanged.

You can also use an indicator variable to verify that a retrieved
character string value has not been truncated. If the indicator variable
contains a positive integer, it specifies the original length of the
string.

You specify an indicator variable, preceded by a colon, immediately after
the host variable. Optionally, you can use the word INDICATOR between the
host variable and its indicator variable. Thus, the following two
examples are equivalent:

+--------------------------------------------------------------------------------------------------+
¦ EXEC SQL ¦ EXEC SQL ¦
¦ SELECT PHONENO ¦ SELECT PHONENO ¦
¦ INTO :CBLPHONE:INDNULL ¦ INTO :CBLPHONE INDICATOR :INDNULL ¦
¦ FROM DSN8310.EMP ¦ FROM DSN8310.EMP ¦
¦ WHERE EMPNO = :EMPID ¦ WHERE EMPNO = :EMPID ¦
¦ END-EXEC. ¦ END-EXEC. ¦
+--------------------------------------------------------------------------------------------------+

You can then test INDNULL for a negative value. If it is negative, the
corresponding value of PHONENO is null, and you can disregard the contents
of CBLPHONE.

When a column value is fetched using a cursor, you can use the same
technique to determine whether the column value is null or not.


Inserting Null Values into Columns Using Host Variables: You can use an
indicator variable to insert a null value from a host variable into a
column. When DB2 processes INSERT and UPDATE statements, it checks the
indicator variable (if it exists). If the indicator variable is negative,
the column value is set to null value. If the indicator variable is
greater than -1, the associated host variable contains a value for the
column.

For example, you could put a value in a column (using INSERT or UPDATE),
but you are not sure whether the value is always specified with the input
data. To allow the possibility that the column's value might be null, you
can code:

EXEC SQL
UPDATE DSN8310.EMP
SET PHONENO = :NEWPHONE:PHONEIND
WHERE EMPNO = :EMPID
END-EXEC.

When NEWPHONE contains other than a null value, set PHONEIND to zero by
preceding the statement with:

MOVE 0 TO PHONEIND.

Otherwise, to tell DB2 that NEWPHONE contains a null value, set PHONEIND
to a negative value, as in the following statement:

MOVE -1 TO PHONEIND.


3.1.4.1.5 Considerations

If you transfer data between a DB2 column and a host variable, and the two
do not have the same data type or length attribute, you can expect the
data format to change. Values might be truncated, padded, or rounded
somehow. If you transfer or compare data, see Chapter 3 of SQL Reference
for the rules associated with these operations.


3.1.4.2 Using Host Structures

A host structure can be substituted for one or more host variables and,
like host variables, indicator variables (or structures) can be used with
host structures.


3.1.4.2.1 Example: Using a Host Structure

In the following example, assume that your COBOL program includes the
following SQL statement:

EXEC SQL
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT
INTO :EMPNO, :FIRSTNME, :MIDINIT, :LASTNAME, :WORKDEPT
FROM DSN8310.VEMP
WHERE EMPNO = :EMPID
END-EXEC.

In this example, if you want to avoid listing host variables, you can
substitute the name of a structure, say :PEMP, that contains :EMPNO,
:FIRSTNME, :MIDINIT, :LASTNAME, and :WORKDEPT. The example then reads:

EXEC SQL
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT
INTO :PEMP
FROM DSN8310.VEMP
WHERE EMPNO = :EMPID
END-EXEC.

You can declare a host structure yourself, or you can use DCLGEN to
generate a COBOL record description, PL/I structure declaration, or C
structure declaration that corresponds to the columns of a table For more
details about coding a host structure in your program, see "Chapter 3-4.
Embedding SQL Statements in Host Languages" in topic 3.4. For more
information on using DCLGEN and the restrictions that apply to the C
language, see "Chapter 3-3. Using DCLGEN" in topic 3.3.

After the host structure is defined, you can refer to it in an SQL
statement instead of listing several host variables (that is, the names of
the data items that make up the host structure).

3.1.4.2.2 Using Indicator Variables with Host Structures

You can define an indicator structure (an array of halfword integer
variables) to support a host structure. Indicator structures are defined
in the DATA DIVISION of your COBOL program. If the column values your
program retrieves into a host structure can be null, you can attach an
indicator structure name to the host structure name. This allows DB2 to
notify your program about each null value returned to a host variable in
the host structure. For example:

01 PEMP-ROW.
10 EMPNO PIC X(6).
10 FIRSTNME.
49 FIRSTNME-LEN PIC S9(4) USAGE COMP.
49 FIRSTNME-TEXT PIC X(12).
10 MIDINIT PIC X(1).
10 LASTNAME.
49 LASTNAME-LEN PIC S9(4) USAGE COMP.
49 LASTNAME-TEXT PIC X(15).
10 WORKDEPT PIC X(3).
10 EMP-BIRTHDATE PIC X(10).
01 INDICATOR-TABLE.
02 EMP-IND PIC S9(4) COMP OCCURS 6 TIMES.
.
.
.
MOVE '000230' TO EMPNO.
.
.
.
EXEC SQL
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, BIRTHDATE
INTO :PEMP-ROW:EMP-IND
FROM DSN8310.EMP
WHERE EMPNO = :EMPNO
END-EXEC.

In this example, EMP-IND is an array containing six values, each of which
can be tested for a negative value. If, for example, EMP-IND(6) contains
a negative value, the corresponding host variable in the host structure
(EMP-BIRTHDATE) contains a null value.

Because this example selects rows from the DSN8310.EMP table, some of the
EMP-IND array values are always zero. The first five columns of each row
are defined NOT NULL. In the above example, DB2 selects the values for a
row of data into a host structure. Therefore, you must use a
corresponding structure for the indicator variables to determine which (if
any) selected column values are null. For information on using the IS
NULL keyword phrase in WHERE clauses, see "Chapter 2-1. Retrieving Data"
in topic 2.1.


3.1.5 Checking the Execution of SQL Statements

A program that includes SQL statements needs to have an area set apart for
communication with DB2; this area is called the SQL communication area
(SQLCA). When DB2 processes an SQL statement in your program, it places
return codes in the SQLCODE (5) and SQLSTATE fields of the SQLCA. The
return codes indicate whether the statement you executed succeeded or
failed.

Because the SQLCA is a valuable problem-diagnosis tool, it is a good idea
to include the instructions necessary to display some of the information
contained in the SQLCA in your application programs. For example, the
contents of SQLERRD(3), which indicates the number of rows updated,
inserted, or deleted by DB2, could be useful. If SQLWARN0 is set to W, at
least one of the SQL warning flags (SQLWARN1 through SQLWARNA) is set.
See Appendix C of SQL Reference for a description of all the fields in the
SQLCA.

The WHENEVER statement can be used to make a decision in your program
based on a condition that is indicated in the SQLCA.

+--- Batch, CICS, IMS, and TSO ------------------------------------------+
¦ ¦
¦ The -911 SQLCODE indicates that a unit of work has been rolled back. ¦
¦ If the impact of this action is not taken into consideration when ¦
¦ writing the code, the data integrity of your system can be ¦
¦ compromised. ¦
¦ ¦
+------------------------------------------------------------------------+

(5) SQLCODE can be a stand-alone integer variable. SQLCA is
not used if SQLCODE is a stand-alone integer variable.


3.1.5.1 SQLCODE and SQLSTATE

Whenever an SQL statement is executed, a return code is placed in the
SQLCODE and SQLSTATE fields of the SQLCA. Although both fields serve
basically the same purpose (indicating whether the statement execution was
successful or not) there are some differences between the two fields.


SQLCODE: DB2 returns the following codes in SQLCODE:

_ If SQLCODE = 0, execution was successful.
_ If SQLCODE > 0, execution was successful with a warning.
_ If SQLCODE < 0, execution was not successful.

SQLCODE 100 indicates "no data" was found.

The meaning of SQLCODEs other than 0 and 100 varies with the particular
product implementing SQL.


SQLSTATE: SQLSTATE allows an application program to check for errors in
the same way for different IBM database management systems. See Appendix
C of Messages and Codes for a complete list of possible SQLSTATE values.


3.1.5.2 The WHENEVER Statement

The WHENEVER statement causes DB2 to check the SQLCA and continue
processing your program, or branch to another area in your program if an
error, exception, or warning exists as a result of executing an SQL
statement. Your program can then examine the SQLCODE or SQLSTATE fields
to take an action specific to the error or exception situation.

The WHENEVER statement allows you to specify what should be done whenever
a general condition is true. You can specify more than one WHENEVER
statement in your program. When you do this, the first WHENEVER statement
applies to all subsequent SQL statements in the source program until
another WHENEVER statement is specified.

The WHENEVER statement looks like this:

EXEC SQL
WHENEVER condition action
END-EXEC

There are three conditions you can specify:

SQLWARNING Indicates what should be done when SQLWARN0 = W or SQLCODE
contains a positive value other than 100. SQLWARN0 can be
set for several different reasons. For example, if a column
value was truncated when it was moved into a host variable;
it is possible your program would not regard this as an
error.

SQLERROR Indicates what should be done when DB2 returns an error code
as the result of an SQL statement (SQLCODE < 0).

NOT FOUND Indicates what should be done when DB2 cannot find a row to
satisfy your SQL statement or when there are no more rows to
fetch (SQLCODE = 100).



You can also specify the action you want taken:

CONTINUE
Specifies the next sequential statement of the source program.

GOTO or GO TO host-label
Specifies the statement identified by host-label. For
host-label, substitute a single token, optionally preceded by
a colon. The form of the token depends on the host language.
In COBOL, for example, it can be section-name or an
unqualified paragraph-name.


The WHENEVER statement must precede the first SQL statement it is to
affect. However, if your program checks the SQLCODE directly, the check
must be done after the SQL statement is executed.


3.1.5.3 Handling Arithmetic or Conversion Errors

Numeric or character conversion errors or arithmetic expression errors can
set an indicator variable to -2. For example, division by zero and
arithmetic overflow does not necessarily halt the execution of a SELECT
statement. If the error occurs in the SELECT list, the statement can
continue to execute and return good data for rows in which the error does
not occur, if indicator variables have been used.

For rows in which the error does occur, one or more selected items have no
meaningful value. This error is flagged by a -2 in the indicator variable
for the affected host variable, and an SQLCODE of +802 (SQLSTATE '01519')
in the SQLCA.

3.2 Chapter 3-2. Using a Cursor to Retrieve a Set of Rows

DB2 has a mechanism called a cursor to allow an application program to
retrieve a set of rows. "Chapter 3-1. Basics of Coding SQL in an
Application Program" in topic 3.1, showed how to use a SELECT INTO
statement to retrieve a single row of data. This chapter explains how
your application program can select a set of rows using a cursor, and then
process the set one row at a time.


3.2.1 Cursor Functions

DB2 can be used to retrieve and process a set of rows. Each row in the
set satisfies the criteria specified in the search conditions of an SQL
statement. However, when a set of rows is selected by your program, the
program cannot process all the rows at once. The program needs to process
the rows one at a time.

To help illustrate the concept of a cursor, assume that DB2 builds a
result table (6) to hold all the rows retrieved by executing the SELECT
statement. DB2 uses a cursor to make rows from the result table available
to your program. A cursor identifies the current row of the result table
specified by a SELECT statement. When you use a cursor, your program can
retrieve each row sequentially from the result table until end-of-data
(that is, the not found condition, SQLCODE=100 and SQLSTATE = '02000') is
reached. The set of rows obtained as a result of executing the SELECT
statement can consist of zero, one, or many rows, depending on the number
of rows that satisfy the SELECT statement search condition.

The SELECT statement referred to in this section must be within a DECLARE
CURSOR statement and cannot include an INTO clause. The DECLARE CURSOR
statement defines and names the cursor, identifying the set of rows to be
retrieved with the SELECT statement of the cursor.

The result table of a cursor is processed much like a sequential data set.
The cursor must be opened (with an OPEN statement) before any rows are
retrieved. A FETCH statement is used to retrieve the cursor's current
row. FETCH can be executed repeatedly until all rows have been retrieved.
When the end-of-data condition occurs, you must close the cursor with a
CLOSE statement (similar to end-of-file processing).

Your program can have several cursors. Each cursor requires its own:

_ DECLARE CURSOR statement to define the cursor
_ OPEN and CLOSE statements to open and close the cursor
_ FETCH statement to retrieve rows from the cursor's result table.


Declarations for host variables that are referred to in a DECLARE CURSOR
statement must precede the DECLARE CURSOR statement. Refer to Chapter 6
of SQL Reference for further information.

You can use cursors to fetch, update, or delete a row of a table, but you
cannot use them to insert a row into a table.

(6) DB2 implements the concept of a result table in different
ways, depending on the complexity of the SELECT statement.
However, the concept is the same regardless of the
implementation.

3.2.2 How to Use a Cursor: An Example
Suppose your program examines data about people in department D11. The
data is kept in the DSN8310.EMP table. The following shows the SQL
statements you must include in a COBOL program to define and use a cursor.
In this example, the cursor is used by the program to process a set of
rows from the DSN8310.EMP table.

+------------------------------------------------------------------------+
¦ Table 5. SQL Statements Required to Define and Use a Cursor in a COBOL ¦
¦ Program ¦
+------------------------------------------------------------------------¦
¦ SQL Statement ¦ Described in Section ¦
+------------------------------------+-----------------------------------¦
¦ EXEC SQL ¦ "Step 1: Define the Cursor" in ¦
¦ DECLARE THISEMP CURSOR FOR ¦ topic 3.2.2.1 ¦
¦ SELECT EMPNO, LASTNAME, ¦ ¦
¦ WORKDEPT, JOB ¦ ¦
¦ FROM DSN8310.EMP ¦ ¦
¦ WHERE WORKDEPT = 'D11' ¦ ¦
¦ FOR UPDATE OF JOB ¦ ¦
¦ END-EXEC. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ EXEC SQL ¦ "Step 2: Open the Cursor" in ¦
¦ OPEN THISEMP ¦ topic 3.2.2.2 ¦
¦ END-EXEC. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ EXEC SQL ¦ "Step 3: Specify What to Do When ¦
¦ WHENEVER NOT FOUND ¦ End-of-Data Is Reached" in ¦
¦ GO TO CLOSE-THISEMP ¦ topic 3.2.2.3 ¦
¦ END-EXEC. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ EXEC SQL ¦ "Step 4: Retrieve a Row Using ¦
¦ FETCH THISEMP ¦ the Cursor" in topic 3.2.2.4 ¦
¦ INTO :EMP-NUM, :NAME2, ¦ ¦
¦ :DEPT, :JOB-NAME ¦ ¦
¦ END-EXEC. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ ... for specific employees ¦ "Step 5a: Update the Current ¦
¦ in Department D11, ¦ Row" in topic 3.2.2.5 ¦
¦ update the JOB value: ¦ ¦
¦ ¦ ¦
¦ EXEC SQL ¦ ¦
¦ UPDATE DSN8310.EMP ¦ ¦
¦ SET JOB = :NEW-JOB ¦ ¦
¦ WHERE CURRENT OF THISEMP ¦ ¦
¦ END-EXEC. ¦ ¦
¦ ¦ ¦
¦ ... then print the row. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ ... for other employees, ¦ "Step 5b: Delete the Current ¦
¦ delete the row: ¦ Row" in topic 3.2.2.6 ¦
¦ ¦ ¦
¦ EXEC SQL ¦ ¦
¦ DELETE FROM DSN8310.EMP ¦ ¦
¦ WHERE CURRENT OF THISEMP ¦ ¦
¦ END-EXEC. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ Branch back to fetch and ¦ ¦
¦ process the next row. ¦ ¦
+------------------------------------+-----------------------------------¦
¦ CLOSE-THISEMP. ¦ "Step 6: Close the Cursor" in ¦
¦ EXEC SQL ¦ topic 3.2.2.7 ¦
¦ CLOSE THISEMP ¦ ¦
¦ END-EXEC. ¦ ¦
+------------------------------------------------------------------------+



3.2.2.1 Step 1: Define the Cursor

To define and identify a set of rows to be accessed with a cursor, issue a
DECLARE CURSOR statement. The DECLARE CURSOR statement names a cursor and
specifies a SELECT statement. The SELECT statement defines a set of rows
that, conceptually, make up the result table. The DECLARE CURSOR
statement looks like this:

EXEC SQL
DECLARE cursor-name CURSOR FOR
SELECT column-name-list
FROM table-name
WHERE search-condition
FOR UPDATE OF column-name
END-EXEC.

The SELECT statement shown here is rather simple. You can code several
other types of clauses in a SELECT statement within a DECLARE CURSOR
statement. Chapter 6 of SQL Reference illustrates several more clauses
that can be used within a SELECT statement.


Updating a Column: If you intend to update a column in any (or all) of
the rows of the identified table, include the FOR UPDATE OF clause, which
names each column you intend to update. The effects of the FOR UPDATE OF
clause are affected by the NOFOR and STDSQL precompiler options. These
are described in Table 24 in topic 4.2.2.4. If you do not specify the
names of columns you intend to update, and you do not specify the
STDSQL(86) option or the NOFOR precompiler options, you receive error
codes in the SQLCODE and SQLSTATE fields of the SQLCA.

A column of the identified table can be updated even though it is not part
of the result table. In this case, you do not need to name the column in
the SELECT statement (but do not forget to name it in the FOR UPDATE OF
clause). When the cursor retrieves a row (using FETCH) that contains a
column value you want to update, you can use UPDATE ... WHERE CURRENT OF
to update the row.

For example, assume that each row of the result table includes the EMPNO,
LASTNAME, and WORKDEPT columns from the DSN8310.EMP table. If you want to
update the JOB column (one of the columns in the DSN8310.EMP table), the
DECLARE CURSOR statement must include FOR UPDATE OF JOB even though JOB is
omitted from the SELECT statement.


Read-Only Result Table: Read-only result tables cannot be updated using a
cursor. Read-only result table specifications are described in greater
detail in SQL Reference.


3.2.2.2 Step 2: Open the Cursor

To tell DB2 you are ready to process the first row of the result table,
have your program issue the OPEN statement. When this happens, DB2
processes the SELECT statement within the DECLARE CURSOR statement to
identify a set of rows using the current value of any host variables
specified in the SELECT statement. The result table can contain zero,
one, or many rows, depending on the extent to which the search condition
is satisfied. The OPEN statement looks like this:

EXEC SQL
OPEN cursor-name
END-EXEC.

When used with cursors, the CURRENT DATE, CURRENT TIME, and CURRENT
TIMESTAMP special registers are evaluated once when the OPEN statement is
executed; the value returned in the register is then used on all
subsequent FETCH statements.



3.2.2.3 Step 3: Specify What to Do When End-of-Data Is Reached

Test the SQLCODE field for a value of 100 or the SQLSTATE field for a
value of '02000' to determine if the last row of data has been retrieved.
These codes occur when a FETCH statement has retrieved the last row in the
result table and your program issues a subsequent FETCH. For example:

IF SQLCODE = 100 GO TO DATA-NOT-FOUND.

An alternative to this technique is to code the WHENEVER NOT FOUND
statement. The WHENEVER NOT FOUND statement can result in a branch to
another part of your program, where a CLOSE statement is issued. The
WHENEVER NOT FOUND statement looks like this:

EXEC SQL
WHENEVER NOT FOUND GO TO symbolic-address
END-EXEC.

Your program must anticipate an end-of-data condition whenever a cursor is
used to fetch a row, and it must be prepared to handle this situation when
it occurs. For further information about the WHENEVER NOT FOUND
statement, see "Checking the Execution of SQL Statements" in topic 3.1.

3.2.2.4 Step 4: Retrieve a Row Using the Cursor

To move the contents of a selected row into your program host variables,
use the FETCH statement. The SELECT statement within the DECLARE CURSOR
statement identifies rows that contain the column values your program
wants (that is, the result table is defined), but DB2 does not retrieve
any data for your application program until FETCH is issued.

When your program issues the FETCH statement, DB2 uses the cursor to point
to the next row in the result table, making it the current row. DB2 then
moves the current row contents into your program host variables (specified
with the INTO clause). This sequence is repeated each time FETCH is
issued, until you have processed all rows in the result table.

The FETCH statement looks like this:

EXEC SQL
FETCH cursor-name
INTO :host variable1, :host variable2
END-EXEC.

When querying a remote subsystem with FETCH, it is possible that you may
experience reduced efficiency. To combat this problem, you can use block
fetch. For more information see "How to Ensure Block Fetching" in
topic 4.1.4.6.2. Block fetch processes rows ahead of the application's
current row. Block fetch cannot be used when a cursor is used for update
or delete.

3.2.2.5 Step 5a: Update the Current Row

When your program has retrieved the current row, you can update its data
by using the UPDATE statement. To do this, issue an UPDATE...WHERE
CURRENT OF statement; it is intended specifically for use with a cursor.
The UPDATE ... WHERE CURRENT OF statement looks like this:

EXEC SQL
UPDATE table-name
SET column1 = value, column2 = value
WHERE CURRENT OF cursor-name
END-EXEC.

When used with a cursor, the UPDATE statement differs from the one
described in "Chapter 2-2. Creating Tables and Modifying Data" in
topic 2.2.

_ You update only one row--the current row.

_ The WHERE clause identifies the cursor that points to the row to be
updated.

_ Each column to be updated must have been named previously in the FOR
UPDATE OF clause of the SELECT statement associated with the DECLARE
CURSOR statement. (7)


After you have updated a row, the cursor points to the current row until
you issue a FETCH statement for the next row.

Remember that you cannot update a row if your update violates any
referential constraints the table might have. Refer to "Updating Tables
with Referential Constraints" in topic 2.2.2.3 for more information.

"Updating Current Values: UPDATE" in topic 2.2.2.2 showed you how to use
the UPDATE statement repeatedly when you update all rows that meet a
specific search condition. Alternatively, you can use the UPDATE... WHERE
CURRENT OF statement repeatedly when you want to obtain a copy of the row,
examine it, and then update it.

(7) If you do not specify the names of columns you intend to
update, you receive an error code in the SQLCODE and SQLSTATE
fields of the SQLCA when you try to update the columns. This
is true only if you have not specified the STDSQL(86) option
or the NOFOR precompile options.



3.2.2.6 Step 5b: Delete the Current Row

When your program has retrieved the current row, you can delete the row by
using the DELETE statement. To do this, you issue a DELETE...WHERE
CURRENT OF statement; it is intended specifically for use with a cursor.
The DELETE...WHERE CURRENT OF statement looks like this:

EXEC SQL
DELETE FROM table-name
WHERE CURRENT OF cursor-name
END-EXEC.

When used with a cursor, the DELETE statement differs from the one you
learned in "Chapter 2-2. Creating Tables and Modifying Data" in
topic 2.2.

_ You delete only one row--the current row.
_ The WHERE clause identifies the cursor that points to the row to be
deleted.

Deleting a row does not require any additional modifications to the
DECLARE statement.

After you have deleted a row, you cannot update or delete another row
using that cursor until you issue a FETCH statement to position the cursor
on the next row.

"Deleting Rows: DELETE" in topic 2.2.2.4 showed you how to use the DELETE
statement to delete all rows that meet a specific search condition.
Alternatively, you can use the DELETE...WHERE CURRENT OF statement
repeatedly when you want to obtain a copy of the row, examine it, and then
delete it.

Remember that you cannot delete a row if doing so will result in the
violation of any referential constraints the table might have. In the
example on page 3.2.2, the employee cannot be deleted from the employee
table unless the employee has already been deleted from the project table
and the project activity table. This is because of the way the
referential constraints have been defined on these tables. Refer to
"Updating Tables with Referential Constraints" in topic 2.2.2.3 for more
information.


3.2.2.7 Step 6: Close the Cursor

When you are finished processing the rows of the result table and you want
to use the cursor again, issue a CLOSE statement to close the cursor:

EXEC SQL
CLOSE cursor-name
END-EXEC.

If you are finished processing the rows of the "result table" and you do
not want to use the cursor, you can let DB2 automatically close the cursor
when your program terminates. When a cursor is closed, locks can be
freed. To release page locks as soon as possible, issue a CLOSE statement
as soon as you are finished with the cursor. For further information see
"Planning for Concurrency" in topic 4.1.2.


3.2.3 Maintaining Cursor Position

If your program completes a unit of work (that is, it either commits or
rolls back the changes made so far), DB2 automatically closes all open
cursors that were not declared with the WITH HOLD option. You can reopen
the cursor after it has been closed, but you begin processing at the
beginning of the result table.

To maintain a cursor and its position across commit points, use the WITH
HOLD option of the DECLARE CURSOR statement. The commit process releases
only locks that are not required to maintain cursor position. After the
commit process, open cursors are not closed. A cursor is positioned after
the last row retrieved and before the next logical row of the result table
to be returned.

The following example shows how to use a cursor to fetch data without
writing code to reposition the cursor after a commit point:

EXEC SQL
DECLARE EMPLUPDT CURSOR WITH HOLD FOR
SELECT EMPNO, LASTNAME, PHONENO, JOB, SALARY, WORKDEPT
FROM DSN8310.EMP
WHERE WORKDEPT < 'D11'
ORDER BY EMPNO
END-EXEC.

A cursor declared in this way can be closed when:

_ A CLOSE cursor, ROLLBACK, or CONNECT statement is issued
_ A CAF CLOSE function is issued
_ The application program terminates.

If the program abends, the cursor position is lost; to prepare for
restart, your program must reposition the cursor.

The following restrictions apply for declaring WITH HOLD cursors:

DECLARE CURSOR WITH HOLD should not be used with the new user signon
from a DB2 attachment facility, because all open cursors are closed.

Do not declare a WITH HOLD cursor in a thread that could become
inactive. If you do, its locks will be held indefinitely.

+--- IMS ------------------------------------------------------------+
¦ ¦
¦ You cannot make use of DECLARE CURSOR...WITH HOLD for message ¦
¦ processing programs (MPP) and message-driven batch message ¦
¦ processing (BMP). Each message is a new user for DB2, and no ¦
¦ cursors, whether declared using WITH HOLD or not, are continued ¦
¦ for a new user. You can use WITH HOLD for non-message-driven BMP ¦
¦ and DL/I batch programs. ¦
¦ ¦
+--------------------------------------------------------------------+

+--- CICS -----------------------------------------------------------+
¦ ¦
¦ In CICS applications, you can use DECLARE CURSOR...WITH HOLD to ¦
¦ indicate that a cursor should not close at a commit or sync point. ¦
¦ However, SYNCPOINT ROLLBACK closes all cursors, and end-of-task ¦
¦ (EOT) closes all cursors before DB2 reuses or terminates the ¦
¦ thread. Because pseudo-conversational transactions usually have ¦
¦ multiple EXEC CICS RETURN statements and thus span multiple EOTs, ¦
¦ the scope of a held cursor is limited. Across EOTs, a cursor ¦
¦ declared WITH HOLD must be reopened and repositioned just as if ¦
¦ the WITH HOLD option had not been specified. ¦
¦ ¦
¦ You should always close cursors that you no longer need. If you ¦
¦ let DB2 close a CICS attachment cursor, the cursor might not close ¦
¦ until DB2 reuses or terminates the thread. ¦
¦ ¦
+-------------------------------------------------------------


3.3 Chapter 3-3. Using DCLGEN

DCLGEN, the declarations generator supplied with DB2, produces a DECLARE
statement you can use in a PL/I, C, or COBOL program, so that you do not
need to code the statement yourself.

DCLGEN generates a table declaration and puts it into a member of a
partitioned data set that you can include in your program. When you use
DCLGEN to generate a table's declaration, DB2 gets the relevant
information from the DB2 catalog, which contains information about the
table's definition, and the definition of each column within the table.
DCLGEN uses this information to produce a complete SQL DECLARE statement
for the table or view and a matching PL/I or C structure declaration or
COBOL record description. You can use DCLGEN for table declarations only
if the table you are declaring already exists.

DCLGEN must be used before the program is precompiled. Supply DCLGEN with
the table or view name before you precompile your program. To use the
declarations in your program, use the SQL INCLUDE statement.

DB2 must be active before you can use DCLGEN. You can invoke DCLGEN in
several different ways:

_ From ISPF through DB2I. Select the DCLGEN option on the DB2I Primary
Option Menu panel. Next, fill in the DCLGEN panel with the
information it needs to build the declarations and press ENTER.

_ Directly from TSO. To do this, sign on to TSO, issue the TSO command
"DSN", and then issue the subcommand "DCLGEN".

_ From a CLIST, running in TSO foreground or background, that issues DSN
then DCLGEN.

_ With JCL. Supply the required information, using JCL, and run DCLGEN
in batch.

If you wish to invoke DCLGEN in the foreground, and your table names
include DBCS characters, you need to use a terminal that can input and
display double-byte characters. If you do not have such a terminal,
you can enter DBCS character using the hex mode of ISPF edit.



3.3.1 Invoking DCLGEN through DB2I

The easiest way to invoke DCLGEN is through DB2I. Figure 14 shows the
DCLGEN panel you reach by selecting option 2, DCLGEN, on the DB2I Primary
Options Menu. For more instructions on using DB2I, see "Using ISPF and
DB2 Interactive (DB2I)" in topic 4.2.7.1.


+----------------------------------------------------------------------------------+
¦ ¦
¦ DSNEDP01 DCLGEN SSID: DSN ¦
¦ ===> ¦
¦ ¦
¦ Enter table name for which declarations are required: ¦
¦ 1 SOURCE TABLE NAME ===> (Unqualified table name) ¦
¦ 2 TABLE OWNER ===> ¦
¦ 3 AT LOCATION ..... ===> (Optional) ¦
¦ ¦
¦ Enter destination data set: (Can be sequential or partitioned) ¦
¦ 4 DATA SET NAME ... ===> ¦
¦ 5 DATA SET PASSWORD ===> (If password protected) ¦
¦ ¦
¦ Enter options as desired: ¦
¦ 6 ACTION .......... ===> (ADD new or REPLACE old declaration) ¦
¦ 7 COLUMN LABEL .... ===> (Enter YES for column label) ¦
¦ 8 STRUCTURE NAME .. ===> (Optional) ¦
¦ 9 FIELD NAME PREFIX ===> (Optional) ¦
¦ 10 DELIMIT DBCS .... ===> (Enter YES to delimit DBCS identifiers) ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
¦ PRESS: ENTER to process END to exit HELP for more information ¦
¦ ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 14. DCLGEN Panel

The following information explains the options on the DCGLEN panel and how
to fill in the necessary fields in order to invoke the declarations
generator.

SOURCE TABLE NAME
Is the unqualified table name for which you want DCLGEN to
produce SQL data declarations. The table can be stored at
your DB2 location or at another DB2 location. To specify a
table name at another DB2 location, enter the table qualifier
in the TABLE OWNER field and the location name in the AT
LOCATION field. DCLGEN generates a three-part table name from
the SOURCE TABLE NAME, TABLE OWNER, and AT LOCATION fields.
You can also use an alias for a table name.

To specify a table name that contains special characters or
blanks, enclose the name in apostrophes. For example, to
specify a table named DON'S TABLE, enter the following:

'DON''S TABLE'

DBCS table names do not have to be enclosed in apostrophes.

The underscore is not considered a special character. A table
named JUNE_PROFITS does not have to be enclosed in
apostrophes.

TABLE OWNER
Is the table name qualifier. If you do not specify this
value, and the table is a local table, DB2 assumes that the
table qualifier is your TSO logon ID. If the table is at a
remote location, you must specify this value.

AT LOCATION
Is the location of the table in an interconnected network.
The AT keyword option is used to prefix the table name on the
SQL DECLARE statement as follows:

location_name.owner_id.table_name

for example,

PLAINS_GA.CARTER.CROP_YIELD_89

If no location is specified, then this option defaults to the
local location name.

This option applies to system-directed access only (that is,
the location named must be another DB2 system).

DATA SET NAME
Is the data set name you allocated to contain the declarations
that DCLGEN produces. It can be either sequential or
partitioned. If this data set is password protected, you must
supply the password in field 4.

DATA SET PASSWORD
Is the data set password for the data set specified in field 3
if the data set is password protected.

ACTION
Tells what to do with the output when it is sent to a
partitioned data set. (The option is ignored if the data set
you specified in field 3 is sequential.)

ADD indicates that an old version of the output does not
exist. A new member is created with the specified data
set name.

REPLACE indicates that an old version is replaced if it
already exists.


COLUMN LABEL
Includes labels declared on any columns of the table or view
DCLGEN is operating on. If you enter YES in this field, and
if the table or view for which you are creating declarations
includes column labels, those labels is included as comments
in the data declarations DCLGEN produces. Specifying NO
causes DCLGEN to ignore any column labels it encounters.

STRUCTURE NAME
Names the generated data structure. The name can be up to 31
bytes in length. If the name is not a DBCS string, and the
first character is not alphabetic, then the name must be
enclosed in apostrophes. If you leave this field blank,
DCLGEN generates a name that contains the table or view name
with a prefix of DCL. If the language is COBOL or PL/I, and
the table or view name consists of a DBCS string, the prefix
consists of DBCS characters.

If you use the C language, the letters you enter will not be
folded to uppercase letters.

FIELD NAME PREFIX
Prefixes names that are generated for fields in the DCLGEN
output. The value you choose can be up to 28 bytes in length
and is used as the prefix for the field name. For example, if
you choose ABCDE, the field names generated are ABCDE1,
ABCDE2, and so on. If the name is a DBCS string, DBCS
equivalents of the suffix numbers are generated. If you leave
this field blank, the field names are the same as the column
names in the table or view.

If you use the C language, the letters you enter will not be
folded to uppercase letters.

DELIMIT DBCS
Specifies whether to delimit DBCS table names and column names
in the DCLGEN table declaration. If you enter YES, DBCS table
and column names are surrounded by SQL delimiters. YES is the
default.


A table or column name in the DECLARE statement is generated as a
non-delimited identifier unless at least one of the following is true:

_ The name contains special characters and is not a DBCS string.

_ The name is a DBCS string, and you have requested that DBCS names be
delimited.


If you are using an SQL reserved word as an identifier, you must edit the
DCLGEN output in order to add the appropriate SQL delimiters.


3.3.2 Including the Data Declarations in Your Program

Use the following SQL INCLUDE statement to insert the table declaration
and COBOL record description produced through the DCLGEN process into your
source program:

EXEC SQL
INCLUDE member name
END-EXEC.

For example, to include a description for the DSN8310.EMP table, code:

EXEC SQL
INCLUDE DECEMP
END-EXEC.

In this example, DECEMP is a name of a member of a partitioned data set
that contains the table declaration and a corresponding COBOL record
description of the DSN8310.EMP table. (A COBOL record description is a
two-level host structure that corresponds to the columns of a table's row.
Host structures are described in "Chapter 3-4. Embedding SQL Statements
in Host Languages" in topic 3.4.) To get a current description of the
table, use DCLGEN to generate the table's declaration and store it as
member DECEMP in a library (usually a partitioned data set) just before
you precompile the program.

For various reasons, there are times when DCLGEN does not produce the
results you expect. You might need to edit the results, tailoring the
output to your specific needs. For example, DCLGEN does not generate
columns that are named NOT NULL WITH DEFAULT.


3.3.3 DCLGEN Support of C, COBOL, and PL/I Languages

Variable names provided by DCLGEN are derived from the source in the
database. In Table 6, var represents variable names that are provided by
DCLGEN when it is necessary to clarify the host language declaration.

+---------------------------------------------------------------------------------------------------------------------+
¦ Table 6. Declarations Generated by DCLGEN ¦
+---------------------------------------------------------------------------------------------------------------------¦
¦ SQL Data Type ¦ C ¦ COBOL ¦ PL/I ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ SMALLINT ¦ short int ¦ PIC S9(4) ¦ BIN FIXED(15) ¦
¦ ¦ ¦ USAGE COMP ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ INTEGER ¦ long int ¦ PIC S9(9) ¦ BIN FIXED(31) ¦
¦ ¦ ¦ USAGE COMP ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ DECIMAL(p,s) or ¦ Not generated (no ¦ PIC S9(p-s)V9(s) ¦ DEC FIXED(p,s) ¦
¦ NUMERIC(p,s) ¦ exact equivalent); ¦ USAGE COMP-3 ¦ If p>15, a warning ¦
¦ ¦ comment replaces ¦ If p>18, a warning ¦ is generated. ¦
¦ ¦ the declaration. ¦ is generated. ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ REAL or ¦ float ¦ USAGE COMP-1 ¦ BIN FLOAT(n) ¦
¦ FLOAT(n) ¦ ¦ ¦ ¦
¦ 1 <= n <= 21 ¦ ¦ ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ DOUBLE PRECISION ¦ double ¦ USAGE COMP-2 ¦ BIN FLOAT(n) ¦
¦ or FLOAT(n) ¦ ¦ ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ CHAR(1) ¦ char ¦ PIC X(1) ¦ CHAR(1) ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ CHAR(n) ¦ char var [n+1] ¦ PIC X(n) ¦ CHAR(n) ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ VARCHAR(n) ¦ struct ¦ 10 var. ¦ CHAR(n) VAR ¦
¦ ¦ {short int var_len; ¦ 49 var_LEN PIC 9(4) USAGE COMP. ¦ ¦
¦ ¦ char var_data[n]¦ 49 var_TEXT PIC X(n). ¦ ¦
¦ ¦ } var; ¦ ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ GRAPHIC(n) ¦ Not generated (no ¦ PIC G(n) USAGE DISPLAY-1.(1) ¦ GRAPHIC(n) ¦
¦ ¦ exact equivalent); ¦ or ¦ ¦
¦ ¦ comment replaces ¦ PIC N(n).(1) ¦ ¦
¦ ¦ declaration. ¦ ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ VARGRAPHIC(n) ¦ Not generated (no ¦ 10 var. ¦ GRAPHIC(n) VAR ¦
¦ ¦ exact equivalent); ¦ 49 var_LEN PIC 9(4) USAGE COMP. ¦ ¦
¦ ¦ comment replaces ¦ 49 var_TEXT PIC G(n) USAGE DISPLAY-1.(1) ¦ ¦
¦ ¦ declaration. ¦ or ¦ ¦
¦ ¦ ¦ 10 var. ¦ ¦
¦ ¦ ¦ 49 var_LEN PIC 9(4) USAGE COMP. ¦ ¦
¦ ¦ ¦ 49 var_TEXT PIC N(n).(1) ¦ ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ DATE ¦ char var[11](2) ¦ PIC X(10)(2) ¦ CHAR(10)(2) ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ TIME ¦ char var[9](3) ¦ PIC X(8)(3) ¦ CHAR(8)(3) ¦
+---------------------+------------------------+-------------------------------------------------+--------------------¦
¦ TIMESTAMP ¦ char var[27] ¦ PIC X(26) ¦ CHAR(26) ¦
+---------------------------------------------------------------------------------------------------------------------¦
¦ Notes: ¦
¦ ¦
¦ 1. DCLGEN chooses the format based on the character you specify as the DBCS symbol on the COBOL Defaults panel. ¦
¦ ¦
¦ 2. This declaration is used unless there is a date installation exit for formatting dates, in which case the ¦
¦ length is that specified for the LOCAL DATE LENGTH installation option. ¦
¦ ¦
¦ 3. This declaration is used unless there is a time installation exit for formatting times, in which case the ¦
¦ length is that specified for the LOCAL TIME LENGTH installation option. ¦
+---------------------------------------------------------------------------------------------------------------------+

For further details about the DCLGEN subcommand, see Chapter 2 of Command
and Utility Reference.

3.3.4 Example: Adding a Table Declaration and Host-Variable Structure to a Library

This example adds an SQL table declaration and a corresponding
host-variable structure to a library. This example is based on the
following scenario:

_ The library name is prefix.TEMP.COBOL.
_ The member will be a new member named VPHONE.
_ The table is a local table named DSN8310.VPHONE.
_ The host-variable structure is for VS COBOL II.
_ The structure receives the default name DCLVPHONE.

Information you must enter is shown in boldface type.



3.3.4.1 Step 1. Specify VS COBOL II as the Host Language

Select option D on the ISPF/PDF menu to display the DB2I Defaults panel.

Specify COB2 as the application language as shown in Figure 15, and then
press Enter. The COBOL Defaults panel is then displayed as shown in
Figure 16.

Fill in the COBOL defaults panel as necessary and press Enter to save the
new defaults, if any, and return to the DB2I Primary Option menu.


+----------------------------------------------------------------------------------+
¦ ¦
¦ ¦
¦ ¦
¦ DSNEOP01 DB2I DEFAULTS ¦
¦ COMMAND ===>_ ¦
¦ ¦
¦ Change defaults as desired: ¦
¦ ¦
¦ 1 DB2 NAME ............. ===> DSN (Subsystem identifier) ¦
¦ 2 DB2 CONNECTION RETRIES ===> 0 (How many retries for DB2 connection¦
¦ 3 APPLICATION LANGUAGE ===> COB2 (ASM/ASMH,C,COBOL/COB2,FORTRAN,PLI) ¦
¦ 4 LINES/PAGE OF LISTING ===> 80 (A number from 5 to 999) ¦
¦ 5 MESSAGE LEVEL ........ ===> I (Information, Warning, Error, Severe¦
¦ 6 SQL STRING DELIMITER ===> DEFAULT (DEFAULT, ' or ") ¦
¦ 7 DECIMAL POINT ........ ===> . (. or ,) ¦
¦ 8 STOP IF RETURN CODE >= ===> 8 (Lowest terminating return code) ¦
¦ 9 NUMBER OF ROWS ===> 20 (For ISPF Tables) ¦
¦ ¦
¦ 10 DB2I JOB STATEMENT: (Optional if your site has a SUBMIT exit) ¦
¦ ===> //USRT001A JOB (ACCOUNT),'NAME' ¦
¦ ===> //* ¦
¦ ===> //* ¦
¦ ===> //* ¦
¦ ¦
¦ ¦
¦ PRESS: ENTER to process END to cancel HELP for more information ¦
¦ ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 15. DB2I Defaults Panel--Changing the Application Language


+----------------------------------------------------------------------------------+
¦ ¦
¦ ¦
¦ ¦
¦ DSNEOP02 COBOL DEFAULTS ¦
¦ COMMAND ===>_ ¦
¦ ¦
¦ Change defaults as desired: ¦
¦ ¦
¦ 1 COBOL STRING DELIMITER ===> (DEFAULT, ' or ") ¦
¦ 2 DBCS SYMBOL FOR DCLGEN ===> (G/N - Character in PIC clause) ¦
¦ ¦
¦ ¦


Figure 16. The COBOL Defaults Panel. Shown only if the application
language is COBOL or COB2.


3.3.4.2 Step 2. Create the Table Declaration and Host Structure

Select option 2 on the DB2I Primary Option menu, and press Enter to
display the DCLGEN panel.

Fill in the fields as shown in Figure 17, and then press Enter.


+----------------------------------------------------------------------------------+
¦ ¦
¦ DSNEDP01 DCLGEN SSID: DSN ¦
¦ ===> ¦
¦ Enter table name for which declarations are required: ¦
¦ ¦
¦ 1 SOURCE TABLE NAME ===> DSN8310.VPHONE ¦
¦ 2 TABLE OWNER ===> ¦
¦ 3 AT LOCATION ..... ===> (Location of table, optional) ¦
¦ ¦
¦ Enter destination data set: (Can be sequential or partitioned) ¦
¦ 4 DATA SET NAME ... ===> TEMP(VPHONEC) ¦
¦ 5 DATA SET PASSWORD ===> (If password protected) ¦
¦ ¦
¦ Enter options as desired: ¦
¦ 6 ACTION .......... ===> ADD (ADD new or REPLACE old declaration) ¦
¦ 7 COLUMN LABEL .... ===> NO (Enter YES for column label) ¦
¦ 8 STRUCTURE NAME .. ===> (Optional) ¦
¦ 9 FIELD NAME PREFIX ===> (Optional) ¦
¦ 10 DELIMIT DBCS ===> YES (Enter YES to delimit DBCS identifiers) ¦
¦ ¦
¦ ¦
¦ PRESS: ENTER to process END to exit HELP for more information ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 17. DCLGEN Panel--Selecting Source Table and Destination Data Set

If the operation is successful, a message is displayed at the top of your
screen as shown in Figure 18.


+----------------------------------------------------------------------------------+
¦ ¦
¦ EXECUTION COMPLETE, MEMBER VPHONEC ADDED ¦
¦ *** ¦
¦ ¦


Figure 18. Successful Completion Message


DB2 then displays the screen as shown in Figure 19. Press Enter to return
to the DB2I Primary Option menu.


+----------------------------------------------------------------------------------+
¦ ¦
¦ DSNEDP01 DCLGEN SSID: DSN ¦
¦ ===> ¦
¦ DSNE294I SYSTEM RETCODE=000 USER OR DSN RETCODE=0 ¦
¦ Enter table name for which declarations are required: ¦
¦ 1 SOURCE TABLE NAME ===> DSN8310.VPHONE ¦
¦ 2 TABLE OWNER ===> ¦
¦ 3 AT LOCATION ..... ===> (Location of table, optional) ¦
¦ ¦
¦ Enter destination data set: (Can be sequential or partitioned) ¦
¦ 4 DATA SET NAME ... ===> TEMP(VPHONEC) ¦
¦ 5 DATA SET PASSWORD ===> (If password protected) ¦
¦ ¦
¦ Enter options as desired: ¦
¦ 6 ACTION .......... ===> ADD (ADD new or REPLACE old declaration) ¦
¦ 7 COLUMN LABEL .... ===> NO (Enter YES for column label) ¦
¦ 8 STRUCTURE NAME .. ===> (Optional) ¦
¦ 9 FIELD NAME PREFIX ===> (Optional) ¦
¦ 10 DELIMIT DBCS ===> (Enter YES to delimit DBCS identifiers) ¦
¦ ¦
¦ ¦
¦ PRESS: ENTER to process END to exit HELP for more information ¦
¦ ¦
+----------------------------------------------------------------------------------+


Figure 19. DCLGEN Panel--Displaying System and User Return Codes



3.3.4.3 Step 3. Examine the Results

To browse or edit the results, first exit from DB2I by entering X on the
command line of the DB2I Primary Option menu. The ISPF/PDF menu is then
displayed, and you can select either the browse or the edit option to view
the results.

For this example, the data set to edit is prefix.TEMP.COBOL(VPHONEC),
which is shown in Figure 20.


***** DCLGEN TABLE(DSN8310.VPHONE) ***
***** LIBRARY(SYSADM.TEMP.COBOL(VPHONEC)) ***
***** QUOTE ***
***** ... IS THE DCLGEN COMMAND THAT MADE THE FOLLOWING STATEMENTS ***
EXEC SQL DECLARE DSN8310.VPHONE TABLE
( LASTNAME VARCHAR(15) NOT NULL,
FIRSTNAME VARCHAR(12) NOT NULL,
MIDDLEINITIAL CHAR(1) NOT NULL,
PHONENUMBER VARCHAR(4) NOT NULL,
EMPLOYEENUMBER CHAR(6) NOT NULL,
DEPTNUMBER CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL
) END-EXEC.
***** COBOL DECLARATION FOR TABLE DSN8310.VPHONE ******
01 DCLVPHONE.
10 LASTNAME.
49 LASTNAME-LEN PIC S9(4) USAGE COMP.
49 LASTNAME-TEXT PIC X(15).
10 FIRSTNAME.
49 FIRSTNAME-LEN PIC S9(4) USAGE COMP.
49 FIRSTNAME-TEXT PIC X(12).
10 MIDDLEINITIAL PIC X(1).
10 PHONENUMBER.
49 PHONENUMBER-LEN PIC S9(4) USAGE COMP.
49 PHONENUMBER-TEXT PIC X(4).
10 EMPLOYEENUMBER PIC X(6).
10 DEPTNUMBER PIC X(3).
10 DEPTNAME.
49 DEPTNAME-LEN PIC S9(4) USAGE COMP.
49 DEPTNAME-TEXT PIC X(36).
***** THE NUMBER OF COLUMNS DESCRIBED BY THIS DECLARATION IS 7 ******


Figure 20. DCLGEN Results Displayed in Edit Mode



4.1 Chapter 4-1. Designing a DB2 Database Application

As with any application, you must plan and design programs to meet the
application's requirements. Designing a DB2 database application requires
that you also plan for binding, locking, recovery, and perhaps for using
distributed data. This chapter includes the following subjects:

_ "Planning to Precompile and Bind" in topic 4.1.1 describes the DB2
bind process, the program preparation process, and methods of using
packages within a plan. "Chapter 4-2. Preparing an Application
Program to Run" in topic 4.2 provides specific details about
controlling those processes.

_ "Planning for Concurrency" in topic 4.1.2 describes what you might
want your application to do about DB2 locks to make best use of the
resources in an environment where other programs run concurrently.

_ "Planning for Recovery" in topic 4.1.3 describes considerations for
designing an application to recover from an interruption as quickly as
possible.

_ "Planning to Access Distributed Data" in topic 4.1.4 describes how to
use DB2's system-directed access and application-directed access to
access distributed data; it describes how each one might affect your
program design.




4.1.1 Planning to Precompile and Bind

DB2 application programs include SQL statements. You cannot compile these
programs until you change the SQL statements into language recognized by
your compiler or assembler. To do this, you must communicate the SQL
requests to DB2 by some other means. The DB2 precompiler does the
following:

_ Replaces the SQL statements in your source programs with compilable
code.

_ Creates a database request module (DBRM), which communicates your SQL
requests to DB2 during the bind process.


The entire program preparation process is illustrated in Figure 21.
"Chapter 4-2. Preparing an Application Program to Run" in topic 4.2
supplies specific details about accomplishing these steps.

After you have precompiled your source program, you create a load module,
possibly one or more packages, and an application plan. It does not
matter which you do first. Creating a load module is similar to compiling
and link-editing an application containing no SQL statements. Creating a
package or an application plan, a process unique to DB2, involves binding
one or more DBRMs.


PICTURE 2


Figure 21. Program Preparation Overview. Two processes are performed:
The compile and link-edit process, and the bind process.



4.1.1.1 Planning to Precompile

The DB2 precompiler provides many options. Most of the options do not
affect the way you design or code the program. They allow you to tell the
precompiler what you have already done--for example, what host language
you use or what value you depend on for the maximum precision of a decimal
number. Or, they tell the precompiler what you want it to do--how many
lines per page in the listing or whether you want a cross-reference
report. In many cases, you may want to accept the default value provided.

A few options, however, can affect the way you code. For example, you
need to know if you are using NOFOR or STDSQL(86) before you begin coding.

Before you begin coding, please review the list of options in Table 24 in
topic 4.2.2.4.

4.1.1.2 Planning to Bind

To access DB2 data, an SQL statement requires an access path. For dynamic
SQL, such as statements issued through SPUFI, DB2 determines the access
path when the statement executes. For statements that are not executed
often, this method is usually acceptable. However, an application
typically runs the same SQL statements repeatedly. In this case,
determining the access path at execution time wastes system resources,
because the same path must be determined repeatedly. To reduce use of
system resources, the access paths used each time the statements execute
can be established once through binding.

Depending upon how you design your DB2 application, you might bind all
your DBRMs in one operation, creating only a single application plan. Or,
you might bind some or all of your DBRMs into separate packages in
separate operations. After that, you must still perform a bind process
for the entire application, listing the packages that are included and
binding any DBRMs that are not bound into packages. Regardless of what
the plan contains, you must bind a plan before the application can run.



4.1.1.2.1 Deciding How to Use Packages

The question of how to use packages affects your application design from
the beginning. For example, you might decide to put certain SQL
statements together in the same program in order to precompile them into
the same DBRM and then bind them into the same package.

At its simplest, you can bind each DBRM into its own package. A
one-to-one correspondence between programs and packages might easily allow
you to keep track of each. However, your application could consist of too
many packages to track easily.

At the other extreme, you can bind all your DBRMs to a single plan. This
approach has the disadvantage that, whenever your plan is rebound, the
operation includes all of the DBRMs, even though not all of them have
changed. For more information about rebinding, see"Automatic Rebinding"
in topic 4.1.1.3 .

You must decide how to use packages based on your application design and
your operational objectives. Keep in mind the following:

_ When using packages, the entire plan need not be rebound when a change
is made to one SQL statement. Only the package associated with the
changed statement has to be rebound.

_ Binding packages allows you to add programs to an existing application
plan without having to rebind the entire plan. Packages can be bound
into a collection, which is a group of associated packages. When you
bind the plan, you can associate an entire package collection with it;
later, you can add other packages to the collection, or replace
existing packages, without rebinding the plan.

_ Binding DBRMs as packages helps you to support multiple versions of
the same program. To maintain multiple versions in a plan without
packages requires separate plans for each version, and therefore
separate plan names and RUN commands. Isolating versions of a program
by using packages allows you to maintain only one plan and helps to
make program migration and fallback easier. For example, you could
concurrently maintain development, test, and production levels of a
program by binding each level of each program as a separate package,
all within a single plan.

_ Most options specified when binding the plan apply only to the DBRMs
bound directly to the plan. You can use different options when you
bind a package that is included in the plan.

_ By using packages, you can use different qualifiers for SQL statements
in different parts of your application. When you bind a package, you
can name a qualifier for the unqualified object names in its DBRM.
When you bind the plan, you can name a different qualifier for
unqualified object names in DBRMs bound directly to the plan. Hence,
by rebinding, you can redirect your SQL statements from, say, a test
table to a production table.

+--- CICS -----------------------------------------------------------+
¦ ¦
¦ With packages, you probably do not need a dynamic plan selection ¦
¦ and its accompanying exit routine. A package listed within a plan ¦
¦ is not accessed until it is executed. However, it is possible to ¦
¦ use dynamic plan selection and packages together. Doing so can ¦
¦ reduce the number of plans in an application, and fewer plans can ¦
¦ mean that less effort is required to maintain the dynamic plan ¦
¦ exit). See "Using Packages with Dynamic Plan Selection" in ¦
¦ topic 4.2.4.4 for information on using packages with dynamic plan ¦
¦ selection. ¦
¦ ¦
+--------------------------------------------------------------------+


Before you bind packages, consider the following:

_ Develop a naming convention and strategy for the most effective and
efficient use of your packages.

_ Determine whether your application requires that all resources be
acquired when the plan is allocated or when your program first uses
the resources. DBRMs bound directly to a plan can acquire resources
at either allocation or first use, depending on what you specify in
the ACQUIRE option. Packages, by default, always acquire resources
when they are first used.

4.1.1.2.2 Deciding What to Put into a Plan

Input to binding the plan can include DBRMs only, a package list only, or
a combination of the two.


Binding All DBRMs to a Plan: Binding all DBRMs to a plan is suitable for
small applications that are unlikely to change or that require all
resources to be acquired when the plan is allocated rather than when your
program first uses them.


Binding with a Package List Only: Binding a plan that includes only a
package list makes maintenance easier when the application will change
significantly over time. Because different BIND options can be specified
when binding each package, you can control such things as qualifiers and
isolation level.


Binding with Both DBRMs and a Package List: Binding DBRMs directly to the
plan and specifying a package list is suitable for maintaining existing
applications. You can add a package list when rebinding an existing plan.
To migrate gradually to using packages, bind DBRMs as packages when some
change is needed.


4.1.1.2.3 Rebinding a Package

The following example shows the options for rebinding a package in the
collection GROUP1. In the example below, the location ID is SNTERSA, the
package ID is PROGA, and the version ID is V1. The connection types shown
in the REBIND subcommand replace connection types that might have been
specified during the original BIND operation.

REBIND PACKAGE(SNTRESA.GROUP1.PROGA.(V1)) ENABLE(CICS,REMOTE)


The asterisk can also be used on the REBIND subcommand. In the following
example, all packages in all collections at the local DB2 system are
rebound. The asterisk does not apply to packages at remote sites.

REBIND PACKAGE(*)

4.1.1.2.4 Rebinding a Plan

If DBRMs for an application are bound directly to a plan, then the entire
plan must be recreated using BIND with the REPLACE option.


4.1.2 Planning for Concurrency

DB2 allows more than one application process to access the same data at
essentially the same time. This is known as concurrency, and it cannot be
provided without cost. To control such undesirable effects as lost
updates, access to uncommitted data, and unrepeatable reads, concurrency
must be controlled.

Lost updates. Without concurrency control, two processes, A and B,
might both read the same row from the database, and both calculate new
values for one of its columns, based on what they read. If A updates
the row with its new value, and then B updates the same row, A's
update is lost.

Access to uncommitted data. Additionally, without concurrency
control, process A might update a value in the database, and process B
might read that value before it was committed. Then, if A's value is
not later committed, but backed out, B's calculations are based on
uncommitted (and presumably incorrect) data.

Unrepeatable reads. Some processes require the following sequence of
events: A reads a row from the database and then goes on to process
other SQL requests. Later, A reads the first row again and must find
the same values it read the first time. Without control, process B
could have changed the row between the two reads.


To prevent those situations from occurring, DB2 uses locks to control
concurrency. A lock associates a DB2 resource with an application process
in a way that affects how other processes can access the same resource.
The process associated with the resource is said to "hold" or "own" the
lock. DB2 uses locks to ensure that no process accesses data that has
been changed, but not yet committed, by another process.

To preserve data integrity, a process acquires locks implicitly, that is,
under DB2 control. It is never necessary for a process to request a lock
explicitly to conceal uncommitted data. Therefore, sometimes you need not
do anything about DB2 locks. Nevertheless, processes acquire locks on the
basis of certain general parameters; you can make better use of your
resources and improve concurrency by understanding the effects of those
parameters.

There are two major classes of locks in DB2.

Transaction locks are used primarily to control access by SQL
statements. Those locks are the ones over which you have the most
control, and the ones we describe in most detail. Hereafter, lock
(with no qualifier) refers to transaction lock.

Drain locks are used to control access by DB2 utilities and commands.
A means called draining allows utilities and commands to acquire
partial or full control of a needed object with least interruption to
concurrent access. For information on concurrency control by
utilities and commands, see Section 7 (Volume 3) of Administration
Guide.


DB2 transaction locks can be compared in four basic aspects: object,
size, duration, and mode.



4.1.2.1 The Object of a Lock

The object of a lock is the resource being locked. You have most control
over locks on user data in tables. But there are also locks on DB2
internal objects. Most of those you are never aware of, but sometimes you
have to consider locks on:

_ The skeleton cursor table (SKCT) representing an application plan and
the skeleton package table (SKPT) representing a package
_ The database descriptor (DBD) representing each DB2 database.

Locks on data include some items occasionally overlooked. A lock on user
data also can control indexes on that data. Some operations that lock
user data also lock portions of the DB2 catalog. Also, operations subject
to referential constraints can require locks on related tables. For
example, if you try to delete a row from a parent table, DB2 might have to
delete rows from the dependent table as well. If you try to update a
primary key column of a parent row, DB2 has to check whether a dependent
row exists. In both those cases, DB2 must lock and read data in the
dependent table as well as the parent table. (For more information on
referential constraints, see Section 4 (Volume 2) of Administration

The maximum size of a Tablespace can be 64Gb.
It is the storage unit for Recovery and Reorganisation.
A large Tablespace can be partitioned. In that case, the unit of Recovery and Reorg. is each individual partition.
There are three types of Tablespaces :
Simple Tablespace
Partitioned Tablespace
Segmented Tablespace

Simple Tablespaces
Generally it contains a single table. However it can contain multiple tables.
It is possible to store the records from related tables together, in an interleaved fashion.
This particularly improves the performance of table join queries.
However the queries involving sequential tablespace scans will suffer.

Partitioned Tablespaces
A Partitioned Tablespace contains exactly 1 table.
Very large tables are generally partitioned.
The partitioning is done on the value ranges of a column or combination of columns.
Each individual partition can be individually recovered or reorganised.
Each partition can be associated to independent storage groups and thus stored on different DASD volumes to reduce the I/O load.


Segmented Tablespaces
A Segmented Tablespace can contain multiple tables.
However the cross clustering of tables is not allowed.
A Table can span across multiple Segments.
One Segment can contain records from a single Table only.
Segment size can be defined while defining the table using SEGSIZE parameter.
It has to be a multiple of 4 pages ranging from 4 to 64 pages.


4.1.2.2 The Size of a Lock

Size (sometimes scope or level) applies only to locks on data in tables.
It refers to the amount of data controlled. As Figure 22 shows, the same
piece of data can be controlled by locks of different sizes. A table
space lock (the largest size) controls the most data, a page lock (the
smallest) controls the least.
Simple table space or
Segmented table space partitioned table space
+---------------------------+ +---------------------------+
¦ ¦ ¦ ¦
¦ Table space lock ¦ ¦ Table space lock ¦
+---------------------------+ +---------------------------+
¦ ¦
+---------------+ ¦
¦ ¦ ¦
¦ Table lock ¦ ¦
+---------------+ ¦
¦ ¦
+---------+ +---------+
¦ Page ¦ ¦ Page ¦
¦ lock ¦ ¦ lock ¦
+---------+ +---------+

Figure 22. Sizes of Objects Locked


Locking larger or smaller amounts of data allows you to trade performance
for concurrency. When page locks are used:

_ Concurrency improves, meaning better response times and higher
throughput rates for many users.
_ Processing time and use of storage increases. That is especially
evident in batch processes that scan or update a large number of
pages.

When only table or table space locks are used:

_ Processing time and storage usage is reduced.
_ Concurrency can be reduced, meaning longer response times for some
users but better throughput for one user.

The principal means of controlling the size of locks are:

_ Installation options. For guidance on using the applicable options,
see Section 7 (Volume 3) of Administration Guide.
_ The LOCKSIZE clause of the CREATE and ALTER TABLESPACE statements.
Its use is also described in Section 7 (Volume 3) of Administration
Guide.
_ The SQL LOCK TABLE statement, described in "Explicitly Locking a
Table" in topic 4.1.2.8.

More Than One Table in a Table Space: In a segmented table space, a lock
can apply to the entire table space, to a single table, or to a single
page. In a simple table space or a partitioned table space, a lock can
apply only to the entire table space or to a single page.

In a simple table space, a single page can contain rows from more than one
table. A lock on the page or on the entire table space locks all the data
in the page or table space, no matter what table the data belongs to. A
lock needed to access data from one table can make data from other tables
temporarily unavailable.

In a segmented table space, however, rows from different tables are
contained in different pages. Hence, locking a page in a segmented table
space does not lock data from more than one table.


Partitioned Table Spaces: A partitioned table space is locked by locking
each of the partitions. Utility jobs can control separate partitions of a
table space or index space.




4.1.2.3 The Duration of a Lock

The duration of a lock is the length of time the lock is held. It varies
according to when the lock is acquired and when it is released.


Duration of Table and Table Space Locks: Table and table space locks can
be acquired when a plan is first allocated, or you can delay acquiring
them until the resource they lock is first used. For the principal means
of controlling the duration of these locks, see: "The ACQUIRE and RELEASE
Options" in topic 4.1.2.7.1.


Duration of Page Locks: Page locks are acquired when the page is first
accessed. When they are released depends on many factors. For details on
controlling the duration of page locks, see "The ISOLATION Option" in
topic 4.1.2.7.2.


4.1.2.4 The Mode of a Lock

The mode (sometimes state) of a lock tells what access to the locked
object is permitted to the lock owner and to any concurrent application
processes.

Figure 23 shows the possible modes for page locks; Figure 24 shows the
modes for table and table space locks. When a page is locked, the table
or table space containing it is also locked. In that case, the table or
table space lock has one of the intent modes: IS, IX, or SIX. (In
contrast, the modes S, U, and X of table and table space locks are
sometimes called gross modes.)


Locks on Indexes: The transaction lock acquired on a table space applies
equally to all indexes on all tables in the table space. The lock
acquired on a table in a segmented table space applies to all indexes on
the table. Thus, if an application process has an S or X lock on a table
space, the indexes are also locked in S or X mode and index pages or
subpages are not locked separately. If the process has an IS, IX, or SIX
lock on the table space, particular index pages or subpages can be locked
separately.

Utilities do not typically require transaction locks. They can acquire
control on a table space without locking its indexes, or on an index
without locking the table space, through the claim and drain mechanisms.
Thus, some utilities can operate concurrently with other utilities, or
with SQL access (possibly read-only). For more specific information, see
Section 7 (Volume 3) of Administration Guide and the descriptions of
particular utilities in Chapter 3 of Command and Utility Reference.

+------------------------------------------------------------------------+
¦ ¦
¦ Page Lock Modes ¦
¦ ¦
¦ Modes and their effects are listed in the order of increasing control ¦
¦ over resources. ¦
¦ ¦
¦ S (SHARE) ¦
¦ The lock owner and any concurrent processes can read, but not ¦
¦ change, the locked page. Concurrent processes can acquire S or ¦
¦ U locks on the page or might read data without acquiring a page ¦
¦ lock. ¦
¦ ¦
¦ U (UPDATE) ¦
¦ The lock owner can read, but not change, the locked page; ¦
¦ however, the owner can promote the lock to an X lock and then ¦
¦ can change the page. Processes concurrent with the U lock can ¦
¦ acquire S locks and read the page, but no concurrent process can ¦
¦ acquire a U lock. ¦
¦ ¦
¦ U locks reduce the chance of deadlocks when the lock owner is ¦
¦ reading a page to determine whether to change it. If the lock ¦
¦ is promoted to mode X, that lock is held at least until a commit ¦
¦ point. But if the lock is not promoted, it might be released ¦
¦ before the commit point. ¦
¦ ¦
¦ X (EXCLUSIVE) ¦
¦ The lock owner can read or change the locked page. Concurrent ¦
¦ processes cannot acquire any lock on the page nor can they ¦
¦ access the locked page. ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
+------------------------------------------------------------------------+
Figure 23. Modes of Page Locks

+------------------------------------------------------------------------+
¦ ¦
¦ Table and Table Space Lock Modes ¦
¦ ¦
¦ Modes and their effects are listed in the order of increasing control ¦
¦ over resources. ¦
¦ ¦
¦ ¦
¦ ¦
¦ IS (INTENT SHARE) ¦
¦ The lock owner can read data in the table or table space, but ¦
¦ not change it. Concurrent processes can both read and change ¦
¦ the data. The lock owner might acquire a page lock on any data ¦
¦ it reads. ¦
¦ ¦
¦ IX (INTENT EXCLUSIVE) ¦
¦ The lock owner and concurrent processes can read and change data ¦
¦ in the table or table space. The lock owner must acquire a page ¦
¦ lock on any data it reads or changes. ¦
¦ ¦
¦ S (SHARE) ¦
¦ The lock owner and any concurrent processes can read, but not ¦
¦ change, data in the table or table space. The lock owner does ¦
¦ not need page locks on data it reads. ¦
¦ ¦
¦ U (UPDATE) ¦
¦ The lock owner can read, but not change, the locked data; ¦
¦ however, the owner can promote the lock to an X lock and then ¦
¦ can change the data. Processes concurrent with the U lock can ¦
¦ acquire S locks and read the data, but no concurrent process can ¦
¦ acquire a U lock. The lock owner does not need page locks. ¦
¦ ¦
¦ U locks reduce the chance of deadlocks when the lock owner is ¦
¦ reading data to determine whether to change it. ¦
¦ ¦
¦ SIX (SHARE with INTENT EXCLUSIVE) ¦
¦ The lock owner can read and change data in the table or table ¦
¦ space. Concurrent processes can read data in the table or table ¦
¦ space, but not change it. Only when the lock owner changes ¦
¦ data, does it acquire page locks. ¦
¦ ¦
¦ X (EXCLUSIVE) ¦
¦ The lock owner can read or change data in the table or table ¦
¦ space. No concurrent process can access the data. The lock ¦
¦ owner does not need page locks. ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
¦ ¦
+------------------------------------------------------------------------+
Figure 24. Modes of Table and Table Space Locks



4.1.2.5 Lock Compatibility

As Figure 23 in topic 4.1.2.4 and Figure 24 in topic 4.1.2.4 show, locks
of some modes do not shut out all other users. Assume that application
process A holds a lock on a table space that process B also wants to
access. DB2 requests, on behalf of B, a lock of some particular mode. If
the mode of A's lock permits B's request, the two locks (or modes) are
said to be compatible.

If the two locks are not compatible, B cannot proceed. It must wait until
A releases its lock. (And, in fact, it must wait until all existing
incompatible locks are released.)

Compatibility for page locks is easy to define: Table 19 shows whether
locks of any two modes are compatible (Yes) or not (No).

+------------------------------+
¦ Table 19. Compatibility of ¦
¦ Page Lock Modes ¦
+------------------------------¦
¦ Page ¦ ¦ ¦ ¦
¦ Lock Mode¦ S ¦ U ¦ X ¦
+------------+-----+-----+-----¦
¦ S ¦ Yes ¦ Yes ¦ No ¦
+------------+-----+-----+-----¦
¦ U ¦ Yes ¦ No ¦ No ¦
+------------+-----+-----+-----¦
¦ X ¦ No ¦ No ¦ No ¦
+------------------------------+