IBM i

7.5

Database
SQL programming

IBM
 Note
Before using this information and the product it supports, read the information in “Notices” on page
401.

This edition applies to IBM i 7.5 (product number 5770-SS1) and to all subsequent releases and modifications until
otherwise indicated in new editions. This version does not run on all reduced instruction set computer (RISC) models nor
does it run on CISC models.
This document may contain references to Licensed Internal Code. Licensed Internal Code is Machine Code and is
licensed to you under the terms of the IBM License Agreement for Machine Code.
© Copyright International Business Machines Corporation 1998, 2022.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract with
IBM Corp.
Contents

SQL programming.................................................................................................. 1
What's new for IBM i 7.5..............................................................................................................................1
PDF file for SQL programming..................................................................................................................... 2
Introduction to Db2 for i Structured Query Language................................................................................ 3
SQL concepts.......................................................................................................................................... 3
SQL relational database and system terminology........................................................................... 4
SQL and system naming conventions...............................................................................................5
Types of SQL statements.................................................................................................................. 6
SQL communication area..................................................................................................................7
SQL diagnostics area.........................................................................................................................8
SQL objects............................................................................................................................................. 8
Schemas............................................................................................................................................ 8
Journals and journal receivers......................................................................................................... 8
Catalogs.............................................................................................................................................8
Tables, rows, and columns............................................................................................................... 9
Aliases............................................................................................................................................... 9
Views................................................................................................................................................. 9
Indexes..............................................................................................................................................9
Constraints...................................................................................................................................... 10
Triggers............................................................................................................................................10
Stored procedures.......................................................................................................................... 10
Sequences.......................................................................................................................................11
Global variables.............................................................................................................................. 11
User-defined functions................................................................................................................... 11
User-defined types......................................................................................................................... 11
XSR objects..................................................................................................................................... 11
SQL packages..................................................................................................................................12
Application program objects................................................................................................................12
User source file............................................................................................................................... 13
Output source file member.............................................................................................................13
Program...........................................................................................................................................14
SQL package....................................................................................................................................14
Module.............................................................................................................................................14
Service program..............................................................................................................................15
Data definition language............................................................................................................................15
Creating a schema................................................................................................................................ 15
Creating a table.................................................................................................................................... 15
Adding and removing constraints...................................................................................................16
Referential integrity and tables...................................................................................................... 16
Adding and removing referential constraints........................................................................... 17
Example: Adding referential constraints.................................................................................. 17
Example: Removing constraints..................................................................................................... 18
Check pending................................................................................................................................ 18
Creating a table using LIKE.................................................................................................................. 19
Creating a table using AS..................................................................................................................... 20
Creating and altering a materialized query table................................................................................ 20
Creating a system-period temporal table............................................................................................21
Declaring a global temporary table......................................................................................................22
Creating a table with remote server data............................................................................................ 22
Creating a row change timestamp column.......................................................................................... 23
Creating auditing columns................................................................................................................... 23

iii
 Creating and altering an identity column.............................................................................................24
Using ROWID........................................................................................................................................ 25
Creating and using sequences............................................................................................................. 25
Comparison of identity columns and sequences........................................................................... 27
Defining field procedures..................................................................................................................... 28
Field definition for field procedures............................................................................................... 29
Specifying the field procedure........................................................................................................29
When field procedures are invoked................................................................................................29
Parameter list for execution of field procedures........................................................................... 30
The field procedure parameter value list (FPPVL)....................................................................32
Parameter value descriptors for field procedures....................................................................33
Field-definition (function code 8)............................................................................................. 33
Field-encoding (function code 0)..............................................................................................34
Field-decoding (function code 4)..............................................................................................35
Example field procedure program............................................................................................ 36
General guidelines for writing field procedures.............................................................................47
Index considerations.................................................................................................................47
Thread considerations...............................................................................................................48
Debug considerations............................................................................................................... 48
Guidelines for writing field procedures that mask data................................................................ 48
Example field procedure program that masks data................................................................. 51
Creating descriptive labels using the LABEL statement..................................................................... 52
Describing an SQL object using COMMENT......................................................................................... 53
Changing a table definition.................................................................................................................. 54
Adding a column............................................................................................................................. 54
Changing a column..........................................................................................................................54
Allowable conversions of data types..............................................................................................55
Deleting a column........................................................................................................................... 56
Order of operations for the ALTER TABLE statement.................................................................... 56
Using CREATE OR REPLACE TABLE................................................................................................ 57
Creating and using ALIAS names.........................................................................................................58
Creating and using views......................................................................................................................58
WITH CHECK OPTION on a view.................................................................................................... 60
WITH CASCADED CHECK OPTION............................................................................................60
WITH LOCAL CHECK OPTION................................................................................................... 61
Example: Cascaded check option............................................................................................. 62
Creating indexes................................................................................................................................... 63
Creating and using global variables..................................................................................................... 63
Replacing existing objects....................................................................................................................64
Dropping a database object................................................................................................................. 64
DROP IF EXISTS..............................................................................................................................65
Restrict on drop.............................................................................................................................. 65
Catalogs in database design................................................................................................................ 67
Getting catalog information about a table..................................................................................... 67
Getting catalog information about a column................................................................................. 67
Data manipulation language......................................................................................................................68
Retrieving data using the SELECT statement...................................................................................... 68
Basic SELECT statement.................................................................................................................68
Specifying a search condition using the WHERE clause................................................................69
Expressions in the WHERE clause............................................................................................ 70
Comparison operators...............................................................................................................72
NOT keyword............................................................................................................................. 72
GROUP BY clause............................................................................................................................72
HAVING clause................................................................................................................................74
ORDER BY clause............................................................................................................................75
Static SELECT statements.............................................................................................................. 77
Handling null values........................................................................................................................77
Special registers in SQL statements...............................................................................................79

iv
 Casting data types.......................................................................................................................... 80
Date, time, and timestamp data types........................................................................................... 81
Specifying current date and time values.................................................................................. 81
Date/time arithmetic................................................................................................................. 81
Boolean data type...........................................................................................................................82
Row change expressions................................................................................................................ 82
Handling duplicate rows................................................................................................................. 82
Defining complex search conditions.............................................................................................. 83
Special considerations for LIKE................................................................................................ 85
Multiple search conditions within a WHERE clause................................................................. 85
Using OLAP specifications.............................................................................................................. 86
Joining data from more than one table.......................................................................................... 92
Inner join................................................................................................................................... 92
Left outer join............................................................................................................................ 93
Right outer join.......................................................................................................................... 94
Exception join............................................................................................................................ 94
Cross join................................................................................................................................... 95
Full outer join.............................................................................................................................96
Multiple join types in one statement........................................................................................ 96
Using table expressions..................................................................................................................97
Using recursive queries.................................................................................................................. 99
Using the UNION keyword to combine subselects......................................................................111
Specifying the UNION ALL keyword....................................................................................... 114
Using the EXCEPT keyword.......................................................................................................... 115
Using the INTERSECT keyword.................................................................................................... 117
Data retrieval errors......................................................................................................................119
Selecting data from a remote database.......................................................................................120
Inserting rows using the INSERT statement..................................................................................... 121
Inserting rows using the VALUES clause..................................................................................... 122
Inserting rows using a select-statement..................................................................................... 123
Inserting multiple rows using the blocked INSERT statement................................................... 124
Inserting data into tables with referential constraints................................................................124
Inserting values into an identity column......................................................................................125
Selecting inserted values............................................................................................................. 126
Inserting data from a remote database....................................................................................... 126
Changing data in a table using the UPDATE statement.................................................................... 127
Updating a table using a scalar-subselect...................................................................................128
Updating a table with rows from another table........................................................................... 128
Updating tables with referential constraints............................................................................... 129
Examples: UPDATE rules........................................................................................................ 129
Updating an identity column........................................................................................................ 130
Updating data as it is retrieved from a table................................................................................130
Removing rows from a table using the DELETE statement...............................................................132
Removing rows from tables with referential constraints............................................................ 132
Example: DELETE rules...........................................................................................................134
Removing rows from a table using the TRUNCATE statement......................................................... 135
Merging data ...................................................................................................................................... 136
Overriding system generation of column values............................................................................... 137
Using subqueries................................................................................................................................137
Subqueries in SELECT statements............................................................................................... 138
Subqueries and search conditions......................................................................................... 139
Usage notes on subqueries.....................................................................................................139
Including subqueries in the WHERE or HAVING clause........................................................ 139
Correlated subqueries.................................................................................................................. 141
Correlated names and references.......................................................................................... 141
Example: Correlated subquery in a WHERE clause............................................................... 141
Example: Correlated subquery in a HAVING clause.............................................................. 143
Example: Correlated subquery in a select-list....................................................................... 143

v
 Example: Correlated subquery in an UPDATE statement...................................................... 144
Example: Correlated subquery in a DELETE statement.........................................................145
Processing special data types................................................................................................................. 145
Large objects...................................................................................................................................... 145
Large object data types................................................................................................................ 145
Large object locators.................................................................................................................... 146
Example: Using a locator to work with a CLOB value.................................................................. 146
Example: LOBLOC in C............................................................................................................ 147
Example: LOBLOC in COBOL................................................................................................... 148
Indicator variables and LOB locators...........................................................................................149
LOB file reference variables......................................................................................................... 149
Example: Extracting CLOB data to a file.......................................................................................150
Example: LOBFILE in C............................................................................................................151
Example: LOBFILE in COBOL.................................................................................................. 151
Example: Inserting data into a CLOB column.............................................................................. 152
Displaying the layout of LOB columns..........................................................................................152
Journal entry layout of LOB columns........................................................................................... 153
User-defined distinct types................................................................................................................153
Using strongly typed UDTs............................................................................................................154
Defining a strongly typed UDT................................................................................................ 155
Examples: Using strongly typed UDTs.................................................................................... 156
Using weakly typed UDTs............................................................................................................. 159
Defining a weakly typed UDT.................................................................................................. 160
Altering a table to use a weakly typed UDT............................................................................160
Weakly typed UDTs and database catalogs............................................................................160
Examples: Using UDTs, UDFs, and LOBs........................................................................................... 161
Example: Defining the UDT and UDFs ......................................................................................... 161
Example: Using the LOB function to populate the database.......................................................162
Example: Using UDFs to query instances of UDTs....................................................................... 162
Example: Using LOB locators to manipulate UDT instances....................................................... 163
Using DataLinks..................................................................................................................................163
Linking control levels in DataLinks............................................................................................... 164
NO LINK CONTROL..................................................................................................................164
FILE LINK CONTROL with FS permissions............................................................................. 164
FILE LINK CONTROL with DB permissions.............................................................................164
Working with DataLinks................................................................................................................ 165
Sort sequences and normalization in SQL.............................................................................................. 167
Sort sequence used with ORDER BY and row selection................................................................... 167
Sort sequence and ORDER BY......................................................................................................168
Sort sequence and row selection................................................................................................. 169
Sort sequence and views................................................................................................................... 170
Sort sequence and the CREATE INDEX statement............................................................................171
Sort sequence and constraints.......................................................................................................... 171
ICU sort sequence..............................................................................................................................171
Normalization..................................................................................................................................... 172
Data protection........................................................................................................................................ 173
Security for SQL objects.....................................................................................................................173
Authorization ID............................................................................................................................174
Views.............................................................................................................................................174
Column masks and row permissions........................................................................................... 174
Auditing.........................................................................................................................................174
Data integrity...................................................................................................................................... 175
Concurrency..................................................................................................................................175
Journaling..................................................................................................................................... 176
Commitment control.....................................................................................................................177
Savepoints.................................................................................................................................... 181
Atomic operations........................................................................................................................ 182
Constraints....................................................................................................................................184

vi
 Adding and using check constraints....................................................................................... 184
Save and restore SQL objects.......................................................................................................185
Damage tolerance.........................................................................................................................186
Index recovery.............................................................................................................................. 186
Catalog integrity............................................................................................................................187
User auxiliary storage pool...........................................................................................................187
Independent auxiliary storage pool............................................................................................. 187
Routines and triggers...............................................................................................................................188
Stored procedures..............................................................................................................................188
Defining an external procedure.................................................................................................... 189
Defining an SQL procedure...........................................................................................................189
Defining a procedure with default parameters............................................................................ 194
Calling a stored procedure........................................................................................................... 195
Using the CALL statement where procedure definition exists...............................................196
Using the embedded CALL statement where no procedure definition exists....................... 197
Using the embedded CALL statement with an SQLDA...........................................................197
Using the dynamic CALL statement where no CREATE PROCEDURE exists......................... 198
Examples: CALL statements................................................................................................... 199
Returning result sets from stored procedures.............................................................................202
Example 1: Calling a stored procedure that returns a single result set................................ 203
Example 2: Calling a stored procedure that returns a result set from a nested procedure..204
Writing a program or SQL procedure to receive the result sets from a stored procedure..........208
Indicator variables and stored procedures..................................................................................213
Returning a completion status to the calling program................................................................ 215
Passing parameters from Db2 to external procedures............................................................... 215
Parameter style SQL................................................................................................................215
Parameter style GENERAL...................................................................................................... 217
Parameter style GENERAL WITH NULLS................................................................................ 217
Parameter style DB2GENERAL............................................................................................... 218
Parameter style Java...............................................................................................................218
Parameter passing conventions for stored procedures and user-defined functions.......................218
Dynamic compound statement..........................................................................................................223
Using user-defined functions.............................................................................................................224
UDF concepts................................................................................................................................225
Writing UDFs as SQL functions..................................................................................................... 227
Example: SQL scalar UDFs......................................................................................................227
Example: SQL table UDFs....................................................................................................... 227
Writing UDFs as external functions.............................................................................................. 228
Registering UDFs..................................................................................................................... 228
Passing arguments from Db2 to external functions...............................................................230
Table function considerations.................................................................................................235
Error processing for UDFs....................................................................................................... 236
Threads considerations...........................................................................................................236
Parallel processing.................................................................................................................. 237
Fenced or unfenced considerations....................................................................................... 237
Defining UDFs with default parameters....................................................................................... 237
Examples: UDF code.....................................................................................................................238
Example: Square of a number UDF.........................................................................................238
Example: Counter....................................................................................................................239
Example: Weather table function........................................................................................... 240
Using UDFs in SQL statements..................................................................................................... 245
Using parameter markers or the NULL values as function arguments.................................. 245
Using qualified function references........................................................................................246
Using unqualified function references....................................................................................246
Invoking UDFs with named arguments.................................................................................. 247
Summary of function references............................................................................................ 247
SQL routine names............................................................................................................................. 249
Triggers............................................................................................................................................... 250

vii
 SQL triggers...................................................................................................................................251
BEFORE SQL triggers...............................................................................................................251
AFTER SQL triggers................................................................................................................. 252
Multiple event SQL triggers.....................................................................................................253
INSTEAD OF SQL triggers....................................................................................................... 254
Handlers in SQL triggers......................................................................................................... 256
Using SIGNAL within an SQL trigger....................................................................................... 257
SQL trigger transition tables................................................................................................... 258
External triggers........................................................................................................................... 259
Varying length parameter lists for external procedures and functions ........................................... 259
Using the INCLUDE statement...........................................................................................................260
Array support in SQL procedures and functions............................................................................... 264
Debugging an SQL routine..................................................................................................................266
Obfuscating an SQL routine or SQL trigger........................................................................................267
Managing SQL and external routine objects......................................................................................268
Improving performance of procedures and functions...................................................................... 269
Improving implementation of procedures and functions............................................................270
Redesigning routines for performance........................................................................................ 272
Working with JSON data..........................................................................................................................273
JSON concepts................................................................................................................................... 273
Using JSON_TABLE............................................................................................................................ 276
Generating JSON data........................................................................................................................280
HTTP functions overview.........................................................................................................................285
Using SQL in different environments...................................................................................................... 291
Using a cursor.....................................................................................................................................291
Types of cursors............................................................................................................................291
Examples: Using a cursor............................................................................................................. 292
Step 1: Defining the cursor..................................................................................................... 294
Step 2: Opening the cursor..................................................................................................... 295
Step 3: Specifying what to do when the end of data is reached............................................295
Step 4: Retrieving a row using a cursor.................................................................................. 296
Step 5a: Updating the current row......................................................................................... 296
Step 5b: Deleting the current row.......................................................................................... 297
Step 6: Closing the cursor.......................................................................................................297
Example: Using the OFFSET clause with a cursor....................................................................... 298
Using the multiple-row FETCH statement................................................................................... 298
Multiple-row FETCH using a host structure array.................................................................. 299
Multiple-row FETCH using a row storage area....................................................................... 300
Unit of work and open cursors..................................................................................................... 302
Dynamic SQL applications................................................................................................................. 303
Running dynamic SQL statements............................................................................................... 303
CCSID of dynamic SQL statements.............................................................................................. 303
Processing non-SELECT statements............................................................................................ 304
Using the PREPARE and EXECUTE statements...................................................................... 304
Processing SELECT statements and using a descriptor.............................................................. 305
Fixed-list SELECT statements.................................................................................................305
Varying-list SELECT statements............................................................................................. 306
SQL descriptor areas...............................................................................................................306
SQLDA format..........................................................................................................................307
Example: A SELECT statement for allocating storage for SQLDA.......................................... 309
Example: A SELECT statement using an allocated SQL descriptor....................................... 313
Parameter markers................................................................................................................. 316
Using interactive SQL......................................................................................................................... 317
Starting interactive SQL................................................................................................................318
Using the statement entry function............................................................................................. 319
Prompting..................................................................................................................................... 319
Syntax checking...................................................................................................................... 321
Statement processing mode...................................................................................................321

viii
 Subqueries.............................................................................................................................. 321
CREATE TABLE prompting.......................................................................................................321
Entering DBCS data................................................................................................................. 321
Using the list selection function................................................................................................... 322
Example: Using the list selection function............................................................................. 322
Session services description........................................................................................................ 324
Exiting interactive SQL..................................................................................................................325
Using an existing SQL session...................................................................................................... 326
Recovering an SQL session...........................................................................................................326
Accessing remote databases with interactive SQL......................................................................326
Using the SQL statement processor.................................................................................................. 328
Execution of statements after errors occur................................................................................. 330
Commitment control in the SQL statement processor................................................................ 330
Source listing for the SQL statement processor.......................................................................... 330
Using the RUNSQL CL command....................................................................................................... 331
Distributed relational database function and SQL..................................................................................333
Db2 for i distributed relational database support.............................................................................334
Db2 for i distributed relational database example program.............................................................335
SQL package support......................................................................................................................... 336
Valid SQL statements in an SQL package.....................................................................................336
Considerations for creating an SQL package............................................................................... 337
CRTSQLPKG authorization...................................................................................................... 337
Creating a package on a database other than Db2 for i......................................................... 337
Target release (TGTRLS) parameter....................................................................................... 337
SQL statement size................................................................................................................. 338
Statements that do not require a package............................................................................. 338
Package object type................................................................................................................ 338
ILE programs and service programs.......................................................................................338
Package creation connection..................................................................................................338
Unit of work............................................................................................................................. 338
Creating packages locally....................................................................................................... 339
Labels...................................................................................................................................... 339
Consistency token................................................................................................................... 339
SQL and recursion................................................................................................................... 339
CCSID considerations for SQL........................................................................................................... 339
Connection management and activation groups.............................................................................. 340
Source code for PGM1.................................................................................................................. 340
Source code for PGM2.................................................................................................................. 341
Source code for PGM3.................................................................................................................. 341
Multiple connections to the same relational database............................................................... 343
Implicit connection management for the default activation group............................................ 344
Implicit connection management for nondefault activation groups...........................................344
Distributed support............................................................................................................................ 345
Determining the connection type.................................................................................................345
Connect and commitment control restrictions............................................................................347
Determining the connection status..............................................................................................348
Distributed unit of work connection considerations....................................................................349
Ending connections...................................................................................................................... 350
Distributed unit of work..................................................................................................................... 350
Managing distributed unit of work connections...........................................................................350
Checking the connection status................................................................................................... 352
Cursors and prepared statements............................................................................................... 352
DRDA stored procedure considerations............................................................................................ 353
WebSphere MQ with Db2........................................................................................................................ 353
WebSphere MQ messages................................................................................................................. 354
WebSphere MQ message handling.............................................................................................. 354
Db2 MQ services..................................................................................................................... 354
Db2 MQ policies...................................................................................................................... 355

ix
 Db2 MQ functions...............................................................................................................................355
Db2 MQ dependencies................................................................................................................. 357
Db2 MQ tables....................................................................................................................................357
Db2 MQ CCSID conversion.................................................................................................................365
Websphere MQ transactions..............................................................................................................366
Basic messaging with WebSphere MQ.............................................................................................. 367
Sending messages with WebSphere MQ........................................................................................... 368
Retrieving messages with WebSphere MQ........................................................................................368
Application to application connectivity with WebSphere MQ...........................................................369
Db2 for i sample tables............................................................................................................................370
Sample tables.....................................................................................................................................370
Department table (DEPARTMENT)...............................................................................................370
DEPARTMENT..........................................................................................................................371
Employee table (EMPLOYEE)........................................................................................................372
EMPLOYEE............................................................................................................................... 374
Employee photo table (EMP_PHOTO).......................................................................................... 375
EMP_PHOTO............................................................................................................................ 375
Employee resumé table (EMP_RESUME)..................................................................................... 375
EMP_RESUME.......................................................................................................................... 376
Employee to project activity table (EMPPROJACT)..................................................................... 376
EMPPROJACT.......................................................................................................................... 377
Project table (PROJECT)...............................................................................................................379
PROJECT..................................................................................................................................380
Project activity table (PROJACT).................................................................................................. 381
PROJACT................................................................................................................................. 382
Activity table (ACT)....................................................................................................................... 384
ACT.......................................................................................................................................... 384
Class schedule table (CL_SCHED)................................................................................................385
CL_SCHED............................................................................................................................... 385
In-tray table (IN_TRAY)................................................................................................................385
IN_TRAY.................................................................................................................................. 386
Organization table (ORG)..............................................................................................................387
ORG..........................................................................................................................................387
Staff table (STAFF)........................................................................................................................387
STAFF.......................................................................................................................................388
Sales table (SALES)...................................................................................................................... 389
SALES...................................................................................................................................... 389
Sample XML tables.............................................................................................................................390
Product table (PRODUCT)............................................................................................................ 391
PRODUCT.................................................................................................................................391
Purchase order table (PURCHASEORDER).................................................................................. 392
PURCHASEORDER...................................................................................................................393
Customer table (CUSTOMER)....................................................................................................... 396
CUSTOMER.............................................................................................................................. 396
Catalog table (CATALOG).............................................................................................................. 398
CATALOG..................................................................................................................................398
Suppliers table (SUPPLIERS)....................................................................................................... 398
SUPPLIERS.............................................................................................................................. 398
Inventory table (INVENTORY)...................................................................................................... 398
INVENTORY............................................................................................................................. 399
Product Supplier table (PRODUCTSUPPLIER)............................................................................. 399
PRODUCTSUPPLIER................................................................................................................399
Db2 for i CL command descriptions...................................................................................................399

Notices..............................................................................................................401
Programming interface information........................................................................................................ 402
Trademarks.............................................................................................................................................. 402

x
Terms and conditions.............................................................................................................................. 403

xi
xii
SQL programming
The Db2® for IBM® i database provides a wide range of support for Structured Query Language (SQL).
The examples of SQL statements shown in this topic collection are based on the sample tables and
assume that the following statements are true:
• Each SQL example is shown on several lines, with each clause of the statement on a separate line.
• SQL keywords are highlighted.
• Table names provided in the sample tables use the schema CORPDATA. Table names that are not found
in the Sample Tables should use schemas you create.
• The SQL naming convention is used.
• The APOST and APOSTSQL precompiler options are assumed although they are not the default options
in COBOL. Character string literals within SQL and host language statements are delimited by single-
quotation marks (').
• A sort sequence of *HEX is used, unless otherwise noted.
Whenever the examples vary from these assumptions, it is stated.
Because this topic collection is for the application programmer, most of the examples are shown as if
they were written in an application program. However, many examples can be slightly changed and run
interactively by using interactive SQL. The syntax of an SQL statement, when using interactive SQL, differs
slightly from the format of the same statement when it is embedded in a program.
Note: By using the code examples, you agree to the terms of the “Code license and disclaimer
information” on page 399.
Related concepts
Embedded SQL programming
Related reference
Db2 for i sample tables
These sample tables are referred to and used in the SQL programming and the SQL reference topic
collections.
Db2 for i SQL reference

What's new for IBM i 7.5

Read about new or significantly changed information for the SQL programming topic collection.
The major new features and information covered in this book include:
• SQL support for the Boolean data type: “Boolean data type” on page 82
• The DROP of an object can be successful when the object does not exist: “DROP IF EXISTS” on page
65
• The RESTRICT ON DROP clause can be used to prevent tables from being dropped: “Restrict on drop”
on page 65
• Sequences support the FOR SYSTEM NAME clause: “Creating and using sequences” on page 25
• TRY_CAST returns the null value rather than an error when a cast operation fails. “Casting data types”
on page 80
• The HTTP_PATCH and HTTP_PATCH_VERBOSE function are added to QSYS2. “HTTP functions overview”
on page 285
• Weakly typed user defined types: “Using weakly typed UDTs” on page 159
• PROGRAM NAME supported for SQL functions and procedures: “SQL routine names” on page 249
• Using SIGNAL in SQL triggers: “Using SIGNAL within an SQL trigger” on page 257

© Copyright IBM Corp. 1998, 2022 1

 What's new since the initial IBM i 7.5 publication
The following revisions or additions have been made to the SQL Programming documentation since the
first 7.5 publication:
• May 2023 update
– The RESTRICT ON DROP clause is supported for SQL and external functions and procedures:
“Restrict on drop” on page 65
– The HTTP functions in QSYS2 support a BLOB version: “HTTP functions overview” on page 285
• December 2022 update
– Table functions can be qualified using 3-part names to access data on a remote system: “Selecting
data from a remote database” on page 120
– Override generation of GENERATED ALWAYS column values: “Overriding system generation of column
values” on page 137

What's new since the first IBM i 7.4 publication

The following enhancements are new since the initial release of IBM i 7.4:
• A set of HTTP functions in QSYS2 can make HTTP requests that use web services. These functions use
the HTTP Transport support provided by the AXISC APIs. “HTTP functions overview” on page 285
• External functions and procedures removed the limit of 32 routine attributes that can be saved in the
associated program or service program object.

How to see what's new or changed

To help you see where technical changes have been made, the information center uses:
• The image to mark where new or changed information begins.
• The image to mark where new or changed information ends.
In PDF files, you might see revision bars (|) in the left margin of new and changed information.
To find other information about what's new or changed this release, see the Memo to users.

PDF file for SQL programming

You can view and print a PDF file of this information.
To view or download the PDF version of this document, select SQL programming.

Saving PDF files

To save a PDF on your workstation for viewing or printing:
1. Right-click the PDF link in your browser.
2. Click the option that saves the PDF locally.
3. Navigate to the directory in which you want to save the PDF.
4. Click Save.

Downloading Adobe Reader

You need Adobe Reader installed on your system to view or print these PDFs. You can download a free
copy from the Adobe Web site (http://get.adobe.com/reader/) .

2 IBM i: SQL programming

Introduction to Db2 for i Structured Query Language
Structured Query Language (SQL) is a standardized language for defining and manipulating data in a
relational database. These topics describe the IBM i implementation of the SQL using the Db2 for i
database and the IBM Db2 Query Manager and SQL Development Kit for i licensed program.
SQL manages information based on the relational model of data. SQL statements can be embedded in
high-level languages, dynamically prepared and run, or run interactively. For information about embedded
SQL, see Embedded SQL programming.
SQL consists of statements and clauses that describe what you want to do with the data in a database and
under what conditions you want to do it.
SQL can access data in a remote relational database, using the IBM Distributed Relational Database
Architecture™ (DRDA).
Related concepts
Distributed database programming
Related reference
Distributed relational database function and SQL
A distributed relational database consists of a set of SQL objects that are spread across interconnected
computer systems.

SQL concepts
Db2 for i SQL consists of several main parts, such as SQL runtime support, precompilers, and interactive
SQL.
• SQL runtime support
SQL run time parses SQL statements and runs any SQL statements. This support is part of the IBM i
licensed program, which allows applications that contain SQL statements to be run on systems where
the IBM Db2 Query Manager and SQL Development Kit for i licensed program is not installed.
• SQL precompilers
SQL precompilers support precompiling embedded SQL statements in host languages. The following
languages are supported:
– ILE C
– ILE C++
– ILE COBOL
– COBOL
– PL/I
– RPG III (part of RPG)
– ILE RPG
The SQL host language precompilers prepare an application program that contains SQL statements. The
host language compilers then compile the precompiled host source programs. For more information
about precompiling, see Preparing and running a program with SQL statements in the Embedded SQL
programming information. The precompiler support is part of the IBM Db2 Query Manager and SQL
Development Kit for i licensed program.
• Run SQL Scripts in IBM i Access Client Solutions (ACS)
For information about ACS, see https://www.ibm.com/support/pages/ibm-i-access-client-solutions
• Run SQL Statements (RUNSQLSTM) CL command
The RUNSQLSTM command can be used to run a series of SQL statements that are stored in a source
file or a source stream file. For more information about the RUNSQLSTM command, see “Using the SQL
statement processor” on page 328.

SQL programming 3
 • Run SQL (RUNSQL) CL command
The RUNSQL command can be used to run a single SQL statements. For more information about the
RUNSQL command, see “Using the RUNSQL CL command” on page 331.
• DB2® Query Manager
DB2 Query Manager provides a prompt-driven interactive interface that allows you to create data, add
data, maintain data, and run reports on the databases. Query Manager is part of the IBM Db2 Query
Manager and SQL Development Kit for i licensed program. For more information, see Query Manager

Use .

• SQL REXX interface

The SQL REXX interface allows you to run SQL statements in a REXX procedure. For more information
about using SQL statements in REXX procedures, see Coding SQL statements in REXX applications in
the Embedded SQL programming information.
• Call Level Interface (SQL CLI)
Using the SQL call level interface, you can perform all the SQL functions without the need to precompile.
This is a standard set of procedure calls to prepare SQL statements, run SQL statements, fetch rows
of data, and even perform advanced functions, such as accessing the catalogs and binding program
variables to output columns.
For a complete description of all the available functions and their syntax, see SQL call level interface in
the Database section of the IBM i Information Center.
• Process Extended Dynamic SQL (QSQPRCED) API
This application programming interface (API) provides an extended dynamic SQL capability. You can
prepare SQL statements into an SQL package and run them by using this API. Statements that are
prepared into a package by this API persist until the package or statement is explicitly dropped. For
more information about the QSQPRCED API, see Process Extended Dynamic SQL (QSQPRCED) API. For
general information about APIs, see Application programming interfaces.
• Syntax Check SQL Statement (QSQCHKS) API
This API syntax checks SQL statements. For more information about the QSQCHKS API, see
Syntax Check SQL Statement (QSQCHKS) API. For general information about APIs, see Application
programming interfaces.
• Db2 Multisystem
This feature (option 27) enables the use of SQL partitioned tables, which allows an SQL table to contain
more than one partition (member) and therefore be much larger in size. For more information, see Db2
Multisystem.
• DB2 Symmetric Multiprocessing
This feature (Option 26) of the operating system provides the query optimizer with additional methods
for retrieving data that include parallel processing. Symmetric multiprocessing (SMP) is a form of
parallelism achieved on a single system where multiple processors (CPU and I/O processors) that share
memory and disk resource work simultaneously toward achieving a single end result. This parallel
processing means that the database manager can have more than one (or all) of the system processors
working on a single query simultaneously. For more information, see Controlling parallel processing for
queries in the Database performance and query optimization topic collection.

SQL relational database and system terminology

In the relational model of data, all data is perceived as existing in tables. Db2 for i objects are created and
maintained as system objects.
The following table shows the relationship between system terms and SQL relational database terms.

4 IBM i: SQL programming

Table 1. Relationship of system terms to SQL terms
System terms SQL terms
Library. Groups related objects and allows you to Schema. Consists of a library, a journal, a journal
find the objects by name. receiver, an SQL catalog, and optionally a data
dictionary. A schema groups related objects and
allows you to find the objects by name.
Physical file. A set of records. Table. A set of columns and rows.
Member. A defined subset of records in a physical Partition. A defined subset of rows in a table.
file.
Record. A set of fields. Row. The horizontal part of a table containing a
serial set of columns.
Field. One or more characters of related Column. The vertical part of a table of one data
information of one data type. type.
Logical file. A subset of fields and records of one View. A subset of columns and rows of one or
or more physical files. more tables.
SQL package. An object type that is used to run Package. An object type that is used to run SQL
SQL statements. statements.
User Profile Authorization name or Authorization ID.

Related concepts
Distributed database programming

SQL and system naming conventions

You can use either the system (*SYS) or the SQL (*SQL) naming convention in Db2 for i programming.
The naming convention used affects the method for qualifying file and table names and the terms used
on the interactive SQL displays. The naming convention used is selected by a parameter on the SQL
commands or by using the SET OPTION statement.

System naming (*SYS)

In the system naming convention, tables and other SQL objects in an SQL statement are qualified by
schema name in the form:

schema/table

or

schema.table

SQL naming (*SQL)

In the SQL naming convention, tables and other SQL objects in an SQL statement are qualified by schema
name in the form:

schema.table

Related reference
Qualification of unqualified object names

SQL programming 5
 Types of SQL statements
There are several basic types of SQL statements. They are listed here according to their functions.
• SQL schema statements, also known as data definition language (DDL) statements
• SQL data and data change statements, also known as data manipulation language (DML) statements
• Dynamic SQL statements
• Embedded SQL host language statements

SQL schema statements SQL data statements

ALTER FUNCTION ALLOCATE CURSOR

ALTER MASK ASSOCIATE LOCATORS
ALTER PERMISSION CALL
ALTER PROCEDURE CLOSE
ALTER SEQUENCE DECLARE CURSOR
ALTER TABLE DELETE
ALTER TRIGGER FETCH
COMMENT FREE LOCATOR
CREATE ALIAS HOLD LOCATOR
CREATE FUNCTION INSERT
CREATE INDEX LOCK TABLE
CREATE MASK OPEN
CREATE PERMISSION REFRESH TABLE
CREATE PROCEDURE SELECT
CREATE SCHEMA SELECT INTO
CREATE SEQUENCE SET variable
CREATE TABLE UPDATE
CREATE TRIGGER VALUES
CREATE TYPE VALUES INTO
CREATE VARIABLE
CREATE VIEW
DROP
GRANT
LABEL
RENAME
REVOKE
TRANSFER OWNERSHIP

SQL data change statements SQL connection statements

DELETE CONNECT
INSERT DISCONNECT
MERGE RELEASE
TRUNCATE SET CONNECTION
UPDATE

6 IBM i: SQL programming

SQL transaction statements SQL session statements

COMMIT DECLARE GLOBAL TEMPORARY TABLE

RELEASE SAVEPOINT SET CURRENT DEBUG MODE
ROLLBACK SET CURRENT DECFLOAT ROUNDING MODE
SAVEPOINT SET CURRENT DEGREE
SET TRANSACTION SET CURRENT IMPLICIT XMLPARSE OPTION
SET CURRENT TEMPORAL SYSTEM_TIME
SET ENCRYPTION PASSWORD
SET PATH
SET SCHEMA
SET SESSION AUTHORIZATION

Dynamic SQL statements Embedded SQL host language statements

ALLOCATE DESCRIPTOR BEGIN DECLARE SECTION

compound (dynamic) DECLARE PROCEDURE
DEALLOCATE DESCRIPTOR DECLARE STATEMENT
DESCRIBE DECLARE VARIABLE
DESCRIBE CURSOR END DECLARE SECTION
DESCRIBE INPUT GET DIAGNOSTICS
DESCRIBE PROCEDURE INCLUDE
DESCRIBE TABLE SET OPTION
EXECUTE SET RESULT SETS
EXECUTE IMMEDIATE SIGNAL
GET DESCRIPTOR TAG
PREPARE WHENEVER
SET DESCRIPTOR

SQL statements can operate on objects that are created by SQL as well as externally described physical
files and single-format logical files. They cannot refer to the interactive data definition utility (IDDU)
dictionary definition for program-described files. Program-described files appear as a table with only a
single column.
Related concepts
Data definition language
Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database
objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, variables,
masks, permissions, and aliases.
Data manipulation language
Data manipulation language (DML) describes the portion of SQL that manipulates or controls data.
Related reference
Db2 for i SQL reference

SQL communication area

The SQL communication area (SQLCA) is a set of variables that provides an application program with
information about its execution of SQL statements. The SQLCA is updated at the end of the execution of
every SQL statement.
Related concepts
SQLCA (SQL communication area)
Handling SQL error return codes using the SQLCA

SQL programming 7
 SQL diagnostics area
The SQL diagnostics area maintained by the database manager provides information about the SQL
statement that is most recently run. Your application program can access the SQL diagnostics area using
the GET DIAGNOSTICS statement.
Related concepts
Using the SQL diagnostics area
Related reference
GET DIAGNOSTICS statement

SQL objects
SQL objects are schemas, journals, catalogs, tables, aliases, views, indexes, constraints, triggers, masks,
permissions, sequences, stored procedures, user-defined functions, user-defined types, global variables,
and SQL packages. SQL creates and maintains these objects as system objects.

Schemas
A schema provides a logical grouping of SQL objects. A schema consists of a library, a journal, a journal
receiver, a catalog, and, optionally, a data dictionary.
Tables, views, and system objects (such as programs) can be created, moved, or restored into any system
library. All system files can be created or moved into an SQL schema if the SQL schema does not contain a
data dictionary. If the SQL schema contains a data dictionary then:
• Source physical files or nonsource physical files with one member can be created, moved, or restored
into an SQL schema.
• Logical files cannot be placed in an SQL schema because they cannot be described in the data
dictionary.
You can create and own many schemas.

Journals and journal receivers

A journal and a journal receiver are used to record changes to tables and views in the database.
Journals and journal receivers are used in processing the SQL COMMIT, ROLLBACK, SAVEPOINT, and
RELEASE SAVEPOINT statements. Journals and journal receivers can also be used as audit trails or for
forward or backward recovery.
Related concepts
Journal management
Commitment control

Catalogs
An SQL catalog is a collection of tables and views that describe tables, views, indexes, procedures,
functions, sequences, triggers, masks, permissions, variables, constraints, programs, packages, and XSR
objects.
This information is contained in a set of cross-reference tables in libraries QSYS and QSYS2. In each SQL
schema there is a set of views built over the catalog tables that contains information about the objects in
the schema.
A catalog is automatically created when you create a schema. You cannot drop or explicitly change the
catalog.
Related reference
Catalog

8 IBM i: SQL programming

Tables, rows, and columns
A table is a two-dimensional arrangement of data that consists of rows and columns.
The row is the horizontal part containing one or more columns. The column is the vertical part containing
one or more rows of data of one data type. All data for a column must be of the same type. A table in SQL
is a keyed or non-keyed physical file.
A materialized query table is a table that is used to contain materialized data that is derived from one or
more source tables specified by a select-statement.
A system-period temporal table is a table that has a related history table that is used by the system to save
all historical versions of rows for the table.
A partitioned table is a table whose data is contained in one or more local partitions (members).
Related concepts
Db2 Multisystem
Related reference
Data types
Creating and altering a materialized query table
A materialized query table is a table whose definition is based on the result of a query, and whose data is
in the form of precomputed results that are taken from the table or tables on which the materialized query
table definition is based.

Aliases
An alias is an alternate name for a table or view.
You can use an alias to refer to a table or view in those cases where an existing table or view can be
referred to. Additionally, aliases can refer to a specific member of a table. An alias can also be a three-part
name with an RDB name that refers to a remote system.
Related reference
Aliases

Views
A view appears like a table to an application program. However, a view contains no data and only logically
represents one or more tables over which it is created.
A view can contain all the columns and rows of the given tables or a subset of them. The columns can
be arranged differently in a view than they are in the tables from which they are taken. A view in SQL is a
special form of a nonkeyed logical file.
Related reference
Views

Indexes
An SQL index is a subset of the data in the columns of a table that are logically arranged in either
ascending or descending order.
Each index defines a set of columns or expressions as keys. These keys are used for ordering, grouping,
and joining. The index is used by the system for faster data retrieval.
Db2 for i supports two types of indexes: binary radix tree indexes and encoded vector indexes (EVIs).
Creating an index is optional. You can create any number of indexes. You can create or drop an index
at any time. The index is automatically maintained by the system. However, because the indexes are
maintained by the system, a large number of indexes can adversely affect the performance of the
applications that change the table.

SQL programming 9
 Related concepts
Creating an index strategy
Related reference
CREATE INDEX

Constraints
A constraint is a rule enforced by the database manager to limit the values that can be inserted, deleted,
or updated in a table.
Db2 for i supports the following constraints:
• Unique constraints
A unique constraint is the rule that the values of the key are valid only if they are unique. You can create
a unique constraint using the CREATE TABLE or ALTER TABLE statement. Although the CREATE INDEX
statement can create a unique index that also guarantees uniqueness, such an index is not a constraint.
Unique constraints are enforced during the execution of INSERT and UPDATE statements. A PRIMARY
KEY constraint is a form of the UNIQUE constraint. The difference is that a PRIMARY KEY cannot contain
any nullable columns.
• Referential constraints
A referential constraint is the rule that the values of the foreign key are valid only if one of the following
conditions is met:
– They appear as values of a parent key.
– Some component of the foreign key is null.
Referential constraints are enforced during the execution of INSERT, UPDATE, and DELETE statements.
• Check constraints
A check constraint is the rule that limits the values allowed in a column or group of columns. You can
create a check constraint using the CREATE TABLE or ALTER TABLE statement. Check constraints are
enforced during the execution of INSERT and UPDATE statements. To satisfy the constraint, each row
of data inserted or updated in the table must make the specified condition either TRUE or unknown
(because of a null value).
Related reference
Constraints
The Db2 for i database supports unique, referential, and check constraints.

Triggers
A trigger is a set of actions that runs automatically whenever a specified event occurs to a specified table
or view.
An event can be an insert, an update, a delete, or a read operation. A trigger can run either before or after
the event. Db2 for i supports SQL insert, update, and delete triggers and external triggers.
Related tasks
Triggering automatic events in your database

Stored procedures
A stored procedure is a program that can be called with the SQL CALL statement.
Db2 for i supports external procedures and SQL procedures. An external procedure can be any system
program, service program, or REXX procedure. It cannot be a System/36 program or procedure. An SQL
procedure is defined entirely in SQL and can contain SQL statements, including SQL control statements.
Related concepts
Stored procedures

10 IBM i: SQL programming

A procedure (often called a stored procedure) is a program that can be called to perform operations. A
procedure can include both host language statements and SQL statements. Procedures in SQL provide the
same benefits as procedures in a host language.

Sequences
A sequence is a data area object that provides a quick and easy way of generating unique numbers.
You can use a sequence to replace an identity column or a user-generated numeric column. A sequence
has uses similar to these alternatives.
Related reference
Creating and using sequences
Sequences are similar to identity columns in that they both generate unique values. However, sequences
are objects that are independent of any tables. You can use sequences to generate values quickly and
easily.

Global variables
A global variable is a named variable that can be created, accessed, and modified using SQL.
A global variable can provide a unique value for a session. The variable can be used as part of any
expression in places such as a query, a create view, or an insert statement.

User-defined functions
A user-defined function is a program that can be called like any built-in functions.
Db2 for i supports external functions, SQL functions, and sourced functions. An external function can be
any system ILE program or service program. An SQL function is defined entirely in SQL and can contain
SQL statements, including SQL control statements. A sourced function is built over any built-in or any
existing user-defined function. You can create a scalar function or a table function as either an SQL
function or an external function.
Related concepts
Using user-defined functions
In writing SQL applications, you can implement some actions or operations as a user-defined function
(UDF) or as a subroutine in your application. Although it might appear easier to implement new operations
as subroutines, you might want to consider the advantages of using a UDF instead.

User-defined types
A user-defined type is a data type that you can define independently of the data types that are provided by
the database management system.
Distinct data types map to built-in types. Array data types are defined using a built-in type as the element
type and a maximum cardinality value.
Related concepts
User-defined distinct types
A user-defined distinct type (UDT) allows the Db2 for i built-in data types to be reused for more specific
purposes.

XSR objects
An XSR object is one or more XML schema documents that have been registered in the XML schema
repository with the same name.
You can use an XSR object during validation of an XML document or during annotated XML schema
decomposition.

SQL programming 11
 SQL packages
An SQL package is an object that contains the control structure produced when the SQL statements in an
application program are bound to a remote relational database management system (DBMS).
The DBMS uses the control structure to process SQL statements encountered while running the
application program.
SQL packages are created when a relational database name (RDB parameter) is specified on a Create SQL
(CRTSQLxxx) command and a program object is created. Packages can also be created with the Create
SQL Package (CRTSQLPKG) command.
Note: The xxx in this command refers to the host language indicators: CI for ILE C, CPPI for ILE C++, CBL
for COBOL, CBLI for ILE COBOL, PLI for PL/I, RPG for RPG/400®, and RPGI for ILE RPG.
SQL packages can also be created with the Process Extended Dynamic SQL (QSQPRCED) API. The SQL
packages mentioned within this topic collection refer exclusively to distributed program SQL packages.
The QSQPRCED API uses SQL packages to provide extended dynamic SQL support.
Related reference
Distributed relational database function and SQL
A distributed relational database consists of a set of SQL objects that are spread across interconnected
computer systems.
Process Extended Dynamic SQL (QSQPRCED) API

Application program objects

Several objects are created when a Db2 for i application program is being precompiled.
Db2 for i supports both non-ILE and ILE precompilers. Application programs can be either distributed or
nondistributed.
With the Db2 for i database, you might need to manage the following objects:
• The original source
• Optionally, the module object for ILE programs
• The program or service program
• The SQL package for distributed programs
With a nondistributed non-ILE Db2 for i program, you must manage only the original source and the
resulting program. The following figure shows the objects involved and the steps that happen during the
precompile and compile processes for a nondistributed non-ILE Db2 for i program. The user source file
precompiles the source to a temporary source file member. This member is then compiled into a program.

With a nondistributed ILE Db2 for i program, you might need to manage the original source, the modules,
and the resulting program or service program. The following figure shows the objects involved and
the steps that happen during the precompile and compile processes for a nondistributed ILE Db2
for i program when OBJTYPE(*PGM) is specified on the precompile command. The user source file
precompiles the source to a temporary source file member. This member is then compiled into a module
that binds to a program.

12 IBM i: SQL programming

With a distributed non-ILE Db2 for i program, you must manage the original source, the resulting program,
and the resulting package. The following figure shows the objects and the steps that occur during the
precompile and compile processes for a distributed non-ILE Db2 for i program. The user source file
precompiles the source to a temporary source file member. This member is then compiled into a program.
After the program is created, an SQL package is created to hold the program.

With a distributed ILE Db2 for i program, you must manage the original source, module objects, the
resulting program or service program, and the resulting packages. An SQL package can be created for
each distributed module in a distributed ILE program or service program. The following figure shows
the objects and the steps that occur during the precompile and compile processes for a distributed ILE
Db2 for i program. The user source file precompiles the source to a temporary source file member. This
member is then compiled into a module that binds to a program. After the program is created, an SQL
package is created to hold the program.

Note: The access plans associated with the Db2 for i distributed program object are not created until the
program is run locally.
Related tasks
Preparing and running a program with SQL statements

User source file

A source file member or a source stream file contains the application language and SQL statements. You
can create and maintain the source file member by using the source entry utility (SEU), a part of the IBM
Rational® Development Studio for i licensed program.

Output source file member

By default, the precompile process creates a temporary source file QSQLTxxxxx in the QTEMP library.
However, you can specify the output source file as a permanent file on the precompile command.
If the precompile process uses the QTEMP library, the system automatically deletes the file when the job
is completed. A member with the same name as the program name is added to the output source file. This
member contains the following items:

SQL programming 13
 • Calls to the SQL runtime support, which have replaced embedded SQL statements
• Parsed and syntax-checked SQL statements
By default, the precompiler calls the host language compiler.
Related tasks
Preparing and running a program with SQL statements

Program
A program is an object that is created as a result of the compilation process for non-ILE compilations or as
a result of the bind process for ILE compilations.
An access plan is a set of internal structures and information that tells SQL how to run an embedded SQL
statement most effectively. It is created only when the program has been successfully created. Access
plans are not created during program creation for SQL statements if the statements refer to an object,
such as a table or view, that cannot be found or to which you are not authorized.
The access plans for such statements are created when the program is run. If, at that time, the table
or view still cannot be found or you are still not authorized, a negative SQLCODE is returned. Access
plans are stored and maintained in the program object for non-distributed SQL programs and in the SQL
package for distributed SQL programs.

SQL package
An SQL package contains the access plans for a distributed SQL program.
An SQL package is an object that is created when:
• You successfully create a distributed SQL program by specifying the relational database (RDB)
parameter on the CREATE SQL (CRTSQLxxx) commands.
• You run the Create SQL Package (CRTSQLPKG) command.
When a distributed SQL program is created, the name of the SQL package and an internal consistency
token are saved in the program. They are used at run time to find the SQL package and to verify that
the SQL package is correct for this program. Because the name of the SQL package is critical for running
distributed SQL programs, an SQL package cannot be:
• Moved
• Renamed
• Duplicated
• Restored to a different library

Module
A module is an Integrated Language Environment® (ILE) object that you create by compiling source code
using the Create Module (CRTxxxMOD) command (or any of the Create Bound Program (CRTBNDxxx)
commands, where xxx is C, CBL, CPP, or RPG).
You can run a module only if you use the Create Program (CRTPGM) command to bind it into a program.
You typically bind several modules together, but you can bind a module by itself. Modules contain
information about the SQL statements; however, the SQL access plans are not created until the modules
are bound into either a program or service program.
Related reference
Create Program (CRTPGM) command

14 IBM i: SQL programming

 Service program
A service program is an Integrated Language Environment (ILE) object that provides a means of packaging
externally supported callable routines (functions or procedures) into a separate object.
Bound programs and other service programs can access these routines by resolving their imports to the
exports provided by a service program. The connections to these services are made when the calling
programs are created. This improves call performance to these routines without including the code in the
calling program.

Data definition language

Data definition language (DDL) describes the portion of SQL that creates, alters, and deletes database
objects. These database objects include schemas, tables, views, sequences, catalogs, indexes, variables,
masks, permissions, and aliases.
Related concepts
Types of SQL statements
There are several basic types of SQL statements. They are listed here according to their functions.
Related tasks
Getting started with SQL

Creating a schema
A schema provides a logical grouping of SQL objects. To create a schema, use the CREATE SCHEMA
statement.
A schema consists of a library, a journal, a journal receiver, a catalog, and optionally, a data dictionary.
Tables, views, and system objects (such as programs) can be created, moved, or restored into any system
libraries. All system files can be created or moved into an SQL schema if the SQL schema does not contain
a data dictionary. If the SQL schema contains a data dictionary then:
• Source physical files or non-source physical files with one member can be created, moved, or restored
into an SQL schema.
• Logical files cannot be placed in an SQL schema because they cannot be described in the data
dictionary.
You can create and own many schemas.
You can create a schema using the CREATE SCHEMA statement. For example, create a schema called
DBTEMP:

CREATE SCHEMA DBTEMP;

Related reference
CREATE SCHEMA

Creating a table
A table can be visualized as a two-dimensional arrangement of data that consists of rows and columns. To
create a table, use the CREATE TABLE statement.

The row is the horizontal part containing one or more columns. The column is the vertical part containing
one or more rows of data of one data type. All data for a column must be of the same type. A table in SQL
is a keyed or non-keyed physical file.
You can create a table using the CREATE TABLE statement. You provide a name for the table. If the table
name is not a valid system object name, you can use the optional FOR SYSTEM NAME clause to specify a
system name.
The definition includes the names and attributes of its columns. The definition can include other
attributes of the table, such as the primary key.

SQL programming 15
 Example: Given that you have administrative authority, create a table named 'INVENTORY' with the
following columns:
• Part number: Integer between 1 and 9999, and must not be null
• Description: Character of length 0 to 24
• Quantity on hand: Integer between 0 and 100000
The primary key is PARTNO.

CREATE TABLE INVENTORY

(PARTNO SMALLINT NOT NULL,
DESCR VARCHAR(24 ),
QONHAND INT,
PRIMARY KEY(PARTNO));

Related concepts
Data types

Adding and removing constraints

Constraints can be added to a new table or to an existing table. To add a unique or primary key, a
referential constraint, or a check constraint, use the CREATE TABLE or the ALTER TABLE statement. To
remove a constraint, use the ALTER TABLE statement.
For example, add a primary key to an existing table using the ALTER TABLE statement:

ALTER TABLE CORPDATA.DEPARTMENT

ADD PRIMARY KEY (DEPTNO);

To make this key a unique key, replace the keyword PRIMARY with UNIQUE.
You can remove a constraint using the same ALTER TABLE statement:

ALTER TABLE CORPDATA.DEPARTMENT

DROP PRIMARY KEY (DEPTNO);

Referential integrity and tables

Referential integrity is the condition of a set of tables in a database in which all references from one table
to another are valid.
Consider the following example:
• CORPDATA.EMPLOYEE serves as a master list of employees.
• CORPDATA.DEPARTMENT acts as a master list of all valid department numbers.
• CORPDATA.EMP_ACT provides a master list of activities performed for projects.
Other tables refer to the same entities described in these tables. When a table contains data for which
there is a master list, that data should actually appear in the master list, or the reference is not valid. The
table that contains the master list is the parent table, and the table that refers to it is a dependent table.
When the references from the dependent table to the parent table are valid, the condition of the set of
tables is called referential integrity.
Stated another way, referential integrity is the state of a database in which all values of all foreign keys
are valid. Each value of the foreign key must also exist in the parent key or be null. This definition of
referential integrity requires an understanding of the following terms:
• A unique key is a column or set of columns in a table that uniquely identify a row. Although a table can
have several unique keys, no two rows in a table can have the same unique key value.
• A primary key is a unique key that does not allow nulls. A table cannot have more than one primary key.
• A parent key is either a unique key or a primary key that is referenced in a referential constraint.

16 IBM i: SQL programming

• A foreign key is a column or set of columns whose values must match those of a parent key. If any
column value used to build the foreign key is null, the rule does not apply.
• A parent table is a table that contains the parent key.
• A dependent table is the table that contains the foreign key.
• A descendent table is a table that is a dependent table or a descendent of a dependent table.
Enforcement of referential integrity prevents the violation of the rule that states that every non-null
foreign key must have a matching parent key.
SQL supports the referential integrity concept with the CREATE TABLE and ALTER TABLE statements.
Related reference
CREATE TABLE
ALTER TABLE

Adding and removing referential constraints

You can use the CREATE TABLE statement or the ALTER TABLE statement to add a referential constraint.
To remove a referential constraint, use the ALTER TABLE statement.
Constraints are rules that ensure that references from one table, a dependent table, to data in another
table, the parent table, are valid. You use referential constraints to ensure referential integrity.
With a referential constraint, non-null values of the foreign key are valid only if they also appear as values
of a parent key. When you define a referential constraint, you specify:
• A primary or unique key
• A foreign key
• Delete and update rules that specify the action taken with respect to dependent rows when the parent
row is deleted or updated.
Optionally, you can specify a name for the constraint. If a name is not specified, one is automatically
generated.
After a referential constraint is defined, the system enforces the constraint on every INSERT, DELETE, and
UPDATE operation performed through SQL or any other interface, including IBM i Access Client Solutions
(ACS), CL commands, utilities, or high-level language statements.
Related reference
CREATE TABLE
ALTER TABLE

Example: Adding referential constraints

You define a referential constraint that every department number in the sample employee table must
appear in the department table. The referential constraint ensures that every employee belongs to an
existing department.
The following SQL statements create the CORPDATA.DEPARTMENT and CORPDATA.EMPLOYEE tables
with those constraint relationships defined.

CREATE TABLE CORPDATA.DEPARTMENT

(DEPTNO CHAR(3) NOT NULL PRIMARY KEY,
DEPTNAME VARCHAR(29) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL
CONSTRAINT REPORTS_TO_EXISTS
REFERENCES CORPDATA.DEPARTMENT (DEPTNO)
ON DELETE CASCADE);

CREATE TABLE CORPDATA.EMPLOYEE

(EMPNO CHAR(6) NOT NULL PRIMARY KEY,
FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHAR(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHAR(3) CONSTRAINT WORKDEPT_EXISTS
REFERENCES CORPDATA.DEPARTMENT (DEPTNO)

SQL programming 17
 ON DELETE SET NULL ON UPDATE RESTRICT,
PHONENO CHAR(4),
HIREDATE DATE,
JOB CHAR(8),
EDLEVEL SMALLINT NOT NULL,
SEX CHAR(1),
BIRTHDATE DATE,
SALARY DECIMAL(9,2),
BONUS DECIMAL(9,2),
COMM DECIMAL(9,2),
CONSTRAINT UNIQUE_LNAME_IN_DEPT UNIQUE (WORKDEPT, LASTNAME));

In this case, the DEPARTMENT table has a column of unique department numbers (DEPTNO) which
functions as a primary key, and is a parent table in two constraint relationships:
REPORTS_TO_EXISTS
is a self-referencing constraint in which the DEPARTMENT table is both the parent and the dependent
in the same relationship. Every non-null value of ADMRDEPT must match a value of DEPTNO. A
department must report to an existing department in the database. The DELETE CASCADE rule
indicates that if a row with a DEPTNO value n is deleted, every row in the table for which the
ADMRDEPT is n is also deleted.
WORKDEPT_EXISTS
establishes the EMPLOYEE table as a dependent table, and the column of employee department
assignments (WORKDEPT) as a foreign key. Thus, every value of WORKDEPT must match a value of
DEPTNO. The DELETE SET NULL rule says that if a row is deleted from DEPARTMENT in which the
value of DEPTNO is n, then the value of WORKDEPT in EMPLOYEE is set to null in every row in which
the value was n. The UPDATE RESTRICT rule says that a value of DEPTNO in DEPARTMENT cannot be
updated if there are values of WORKDEPT in EMPLOYEE that match the current DEPTNO value.
Constraint UNIQUE_LNAME_IN_DEPT in the EMPLOYEE table causes LASTNAME to be unique within a
department. While this constraint is unlikely, it illustrates how a constraint made up of several columns
can be defined at the table level.

Example: Removing constraints

When you remove the primary key over the DEPTNO column in the DEPARTMENT table, other tables are
affected.
You also remove the REPORTS_TO_EXISTS constraint that is defined on the DEPARTMENT table and the
WORKDEPT_EXISTS constraint that is defined on the EMPLOYEE table, because the primary key that you
remove is the parent key in those constraint relationships.

ALTER TABLE CORPDATA.EMPLOYEE DROP PRIMARY KEY;

You can also remove a constraint by name, as in the following example:

ALTER TABLE CORPDATA.DEPARTMENT

DROP CONSTRAINT UNIQUE_LNAME_IN_DEPT;

Check pending
Referential constraints and check constraints can be in a check pending state, where potential violations
of the constraints exist.
For referential constraints, a violation occurs when potential mismatches exist between parent and
foreign keys. For check constraints, a violation occurs when potential values exist in columns that are
limited by the check constraint. When the system determines that a constraint might have been violated
(such as after a restore operation), the constraint is marked as check pending. When this happens,
restrictions are placed on the use of tables involved in the constraint. For referential constraints, the
following restrictions apply:
• No input or output operations are allowed on the dependent file.
• Only read and insert operations are allowed on the parent file.

18 IBM i: SQL programming

 When a check constraint is in check pending, the following restrictions apply:
• Read operations are not allowed on the file.
• Insert and update operations are allowed and the constraint is enforced.
To get a constraint out of check pending, follow these steps:
1. Disable the relationship with the Change Physical File Constraint (CHGPFCST) CL command.
2. Correct the key (foreign, parent, or both) data for referential constraints or column data for check
constraints.
3. Enable the constraint again with the CHGPFCST CL command.
You can identify the rows that are in violation of the constraint with the Display Check Pending Constraint
(DSPCPCST) CL command or by looking in the Database Maintenance folder in IBM i Navigator.
Related concepts
Check pending status in referential constraints
Related tasks
Working with constraints that are in check pending status

Creating a table using LIKE

You can create a table that looks like another table. That is, you can create a table that includes all of the
column definitions from an existing table.
The following definitions are copied:
• Column names (and system column names)
• Data type, length, precision, and scale
• CCSID
• FIELDPROC
If the LIKE clause immediately follows the table name and is not enclosed in parentheses, the following
attributes are also included:
• Column text (LABEL)
• Column heading (LABEL)
• Default value
• Hidden attribute
• Identity attribute
• Nullability
If the specified table or view contains an identity column, you must specify the option INCLUDING
IDENTITY on the CREATE TABLE statement if you want the identity column to exist in the new table. The
default behavior for CREATE TABLE is EXCLUDING IDENTITY. There are similar options to include the
default value, the hidden attribute, and the row change timestamp attribute. If the specified table or view
is a non-SQL-created physical file or logical file, any non-SQL attributes are removed.
Create a table EMPLOYEE2 that includes all of the columns in EMPLOYEE:

CREATE TABLE EMPLOYEE2 LIKE EMPLOYEE;

Related reference
CREATE TABLE

SQL programming 19
Creating a table using AS
You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE
TABLE AS statement.
All of the expressions that can be used in a SELECT statement can be used in a CREATE TABLE AS
statement. You can also include all of the data from the table or tables that you are selecting from.
For example, create a table named EMPLOYEE3 that includes a subset of column definitions and data
from the EMPLOYEE table where the WORKDEPT = D11.

CREATE TABLE EMPLOYEE3 AS

(SELECT EMPNO, LASTNAME, JOB
FROM EMPLOYEE
WHERE WORKDEPT = 'D11') WITH DATA;

If the specified table or view contains an identity column, you must specify the option INCLUDING
IDENTITY on the CREATE TABLE statement if you want the identity column to exist in the new table.
The default behavior for CREATE TABLE is EXCLUDING IDENTITY. There are similar options to include
the default value, the hidden attribute, and the row change timestamp attribute. The WITH NO DATA
clause indicates that the column definitions are to be copied without the data. If you want to include
the data in the new table EMPLOYEE3, include the WITH DATA clause. If the specified query includes a
non-SQL-created physical file or logical file, any non-SQL result attributes are removed.
Related concepts
Retrieving data using the SELECT statement
The SELECT statement tailors your query to gather data. You can use the SELECT statement to retrieve a
specific row or retrieve data in a specific way.
Related reference
Creating a table with remote server data
You can create a table on the local server that references one or more tables on a remote server.
CREATE TABLE

Creating and altering a materialized query table

A materialized query table is a table whose definition is based on the result of a query, and whose data is
in the form of precomputed results that are taken from the table or tables on which the materialized query
table definition is based.
If the optimizer determines that a query runs faster against a materialized query table than it does against
the base table or tables, the query will run against the materialized query table. You can directly query a
materialized query table. For more information about how the optimizer uses materialized query tables,
see the Database performance and query optimization topic.
Assume a very large transaction table named TRANS contains one row for each transaction processed
by a company. The table is defined with many columns. Create a materialized query table for the TRANS
table that contains daily summary data for the date and amount of a transaction by issuing the following:

CREATE TABLE STRANS

AS (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM
FROM TRANS
GROUP BY YEAR, MONTH, DAY )
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER;

This materialized query table specifies that the table is not populated at the time that it is created
by using the DATA INITIALLY DEFERRED clause. REFRESH DEFERRED indicates that changes made to
TRANS are not reflected in STRANS. Additionally, this table is maintained by the user, enabling the user to
use ALTER, INSERT, DELETE, and UPDATE statements.
To populate the materialized query table or refresh the table after it has been populated, use the
REFRESH TABLE statement. This causes the query associated with the materialized query table to be

20 IBM i: SQL programming

 run and causes the table to be filled with the results of the query. To populate the STRANS table, run the
following statement:

REFRESH TABLE STRANS;

You can create a materialized query table from an existing base table as long as the result of the
select-statement provides a set of columns that match the columns in the existing table (same number of
columns and compatible column definitions). For example, create a table TRANSCOUNT. Then, change the
base table TRANSCOUNT into a materialized query table:
To create the table:

CREATE TABLE TRANSCOUNT

(ACCTID SMALLINT NOT NULL,
LOCID SMALLINT,
YEAR DATE
CNT INTEGER);

You can alter this table to be a materialized query table:

ALTER TABLE TRANSCOUNT

ADD MATERIALIZED QUERY
(SELECT ACCTID, LOCID, YEAR, COUNT(*) AS CNT
FROM TRANS
GROUP BY ACCTID, LOCID, YEAR )
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER;

Finally, you can change a materialized query table back to a base table. For example:

ALTER TABLE TRANSCOUNT

DROP MATERIALIZED QUERY;

In this example, the table TRANSCOUNT is not dropped, but it is no longer a materialized query table.
Related concepts
Tables, rows, and columns
A table is a two-dimensional arrangement of data that consists of rows and columns.

Creating a system-period temporal table

You can define a pair of tables that are used to maintain the current data as well as the historical data for
a table. These tables are called a system-period temporal table and a history table.
A system-period temporal table is defined just like any other table except that it must have three
additional timestamp columns and a system period. The history table is defined with identical columns
to the system-period temporal table. An ALTER TABLE statement is used to connect the two tables in a
versioned relationship.
For example, suppose you want to keep track of all changes to the DEPARTMENT table. You would define
the table as follows:

CREATE OR REPLACE TABLE DEPARTMENT

(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
MGRNO CHAR(6),
ADMRDEPT CHAR(3) NOT NULL,
LOCATION CHAR(16),
START_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
END_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
TS_ID TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME (START_TS, END_TS),
PRIMARY KEY (DEPTNO));

You would define the corresponding history table as follows:

CREATE TABLE DEPARTMENT_HIST LIKE DEPARTMENT;

SQL programming 21
 Then you would tie them together in a versioning relationship like this:

ALTER TABLE DEPARTMENT ADD VERSIONING USE HISTORY TABLE DEPARTMENT_HIST;

Once versioning is defined for the system-period temporal table, updates and deletes to it cause the
version of the row prior to the change to be inserted as a row in the history table. The special row begin
and row end timestamp columns are set by the system to indicate the time span when the data for the
historical row was the active data.
You can write a query that will automatically return data from both the system-period temporal table and
the history table.
For example, to see what the DEPARTMENT table looked like six months ago, issue the following query:

SELECT * FROM DEPARTMENT FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 6 MONTHS;

Related reference
Working with system-period temporal tables
CREATE TABLE
ALTER TABLE

Declaring a global temporary table

You can create a temporary table for use with your current session. To create a temporary table, use the
DECLARE GLOBAL TEMPORARY TABLE statement.
This temporary table does not appear in the system catalog and cannot be shared by other sessions.
When you end your session, the rows of the table are deleted and the table is dropped.
The syntax of this statement is similar to that of the CREATE TABLE statement and can include the LIKE or
AS clause.
For example, create a temporary table ORDERS:

DECLARE GLOBAL TEMPORARY TABLE ORDERS

(PARTNO SMALLINT NOT NULL,
DESCR VARCHAR(24),
QONHAND INT)
ON COMMIT DELETE ROWS;

This table is created in QTEMP. To reference the table using a schema name, use either SESSION or
QTEMP. You can issue SELECT, INSERT, UPDATE, and DELETE statements against this table, the same as
any other table. You can drop this table by issuing the DROP TABLE statement:

DROP TABLE ORDERS;

Related reference
DECLARE GLOBAL TEMPORARY TABLE

Creating a table with remote server data

You can create a table on the local server that references one or more tables on a remote server.
Along with the select-statement, you can specify copy options to get attributes such as the default values
or identity column information copied for the new table. The WITH DATA or WITH NO DATA clause must
be specified to indicate whether to populate the table from the remote system.
For example, create a table named EMPLOYEE4 that includes column definitions from the EMPLOYEE
table on remote server REMOTESYS. Include the data from the remote system as well.

CREATE TABLE EMPLOYEE4 AS

(SELECT PROJNO, PROJNAME, DEPTNO
FROM REMOTESYS.TESTSCHEMA.EMPLOYEE
WHERE DEPTNO = 'D11') WITH DATA;

22 IBM i: SQL programming

 You can also create this table as a global temporary table, which will create it in QTEMP. In this example,
different column names are provided for the new table. The table definition will pick up the default values
for its columns from the remote server.

DECLARE GLOBAL TEMPORARY TABLE EMPLOYEE4 (Project_number, Project_name, Department_number) AS

(SELECT PROJNO, PROJNAME, DEPTNO
FROM REMOTESYS.TESTSCHEMA.EMPLOYEE
WHERE DEPTNO = 'D11') WITH DATA INCLUDING DEFAULTS;

The following restrictions apply to using a remote server as the source for the new table:
• The materialized query table clauses are not allowed.
• A column with a FIELDPROC cannot be listed in the select list.
• The copy options cannot be specified if the remote server is Db2 for LUW or Db2 for z/OS®.
Related reference
Creating a table using AS
You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE
TABLE AS statement.

Creating a row change timestamp column

Every time a row is added or changed in a table with a row change timestamp column, the row change
timestamp column value is set to the timestamp corresponding to the time of the insert or update
operation.
The data type of a row change timestamp column must be TIMESTAMP. You can define only one row
change timestamp column in a table.
When you create a table, you can define a column in the table to be a row change timestamp column. For
example, create a table ORDERS with columns called ORDERNO, SHIPPED_TO, ORDER_DATE, STATUS,
and CHANGE_TS. Define CHANGE_TS as a row change timestamp column.

CREATE TABLE ORDERS

(ORDERNO SMALLINT,
SHIPPED_TO VARCHAR(36),
ORDER_DATE DATE,
STATUS CHAR(1),
CHANGE_TS TIMESTAMP FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP NOT NULL);

When a row is inserted into the ORDERS table, the CHANGE_TS column for the row is set to the timestamp
of the insert operation. Any time a row in ORDERS is updated, the CHANGE_TS column for the row is
modified to reflect the timestamp of the update operation.
You can drop the row change timestamp attribute from a column:

ALTER TABLE ORDER

ALTER COLUMN CHANGE_TS
DROP ROW CHANGE TIMESTAMP;

The column CHANGE_TS remains as a TIMESTAMP column in the table, but the system no longer
automatically updates timestamp values for this column.

Creating auditing columns

Every time a row is added or changed in a table that has an auditing column, the value of the audit column
is generated by the database manager. These generated values are maintained for both SQL and native
changes to the row.
There are three types of values that the system uses to maintain status information for any modification
to a row: the type of data change, a special register, or a built-in global variable. You can have multiple
columns in a table that track this information. Each column defined as one of these generated expression
columns must have a data type that exactly matches the required definition for the item being generated.

SQL programming 23
 • A column defined to contain a generated data change operation column will be updated with an I or U
to indicate whether the last modification was an insert or an update. For a history table, a value of D can
be generated to indicate that the row was deleted.
• A column defined to contain a generated special register value will be assigned the current value of the
special register when the data change operation occurs.
• A column defined to contain a generated built-in global variable value will be assigned the current value
of the global variable when the data change operation occurs.
When you create a table, you can define columns for these generated expressions. For example, create a
table EMPLOYEE with columns called EMPNO, NAME, WORKDEPT, and SALARY. Define auditing columns
to track the type of change, the user who made the change, the application that made the change, and the
qualified job name where the change originated.

CREATE TABLE EMPLOYEE

(EMPNO CHAR(6),
NAME VARCHAR(50),
WORKDEPT CHAR(3),
SALARY DECIMAL(9,2),
EMP_CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
EMP_CHANGE_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER),
EMP_CHANGE_APPLNAME VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_APPLNAME),
EMP_CHANGE_JOBNAME VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME));

When you add a generated expression column to an existing table, defining the IMPLICITLY HIDDEN
attribute for the column as well can prevent existing applications that use SQL from requiring
modifications. Hidden columns are excluded when a SELECT *, an INSERT without a column list, or an
UPDATE using ROW determines its implicit list of columns. The only time a hidden column is included is
when it is explicitly mentioned by name.
These auditing columns can be especially useful when using a system-period temporal table. Since all
the historical rows are kept in the corresponding history table, an auditing column will complement the
history by recording information such as who was responsible for each change.
Related reference
CREATE TABLE
Using a system-period temporal table for tracking auditing information

Creating and altering an identity column

Every time a row is added to a table with an identity column, the identity column value for the new row is
generated by the system.
Only columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, or NUMERIC can be created as identity
columns. You are allowed only one identity column per table. When you are changing a table definition,
only a column that you are adding can be specified as an identity column; existing columns cannot.
When you create a table, you can define a column in the table to be an identity column. For example,
create a table ORDERS with three columns called ORDERNO, SHIPPED_TO, and ORDER_DATE. Define
ORDERNO as an identity column.

CREATE TABLE ORDERS

(ORDERNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 500
INCREMENT BY 1
NOCYCLE),
SHIPPED_TO VARCHAR (36) ,
ORDER_DATE DATE);

This column is defined with a starting value of 500, incremented by 1 for every new row inserted, and will
fail when the maximum value is reached. In this example, the maximum value for the identity column is
the maximum value for the data type. Because the data type is defined as SMALLINT, the range of values
that can be assigned to ORDERNO is from 500 to 32767. Once the identity column value reaches 32767,
the range of values has been exhausted and no additional rows can be inserted.

24 IBM i: SQL programming

 If the column is defined as CYCLE, when the column value reaches 32767, it will restart at 500 again.
If 500 is still assigned to a column, and a unique key is specified on the identity column, a duplicate
key error is returned. The next insert operation will attempt to use 501. If you do not have a unique key
specified for the identity column, 500 is generated again, causing duplicate identity column values in the
table.
For a larger range of values, specify the column to be data type INTEGER or BIGINT. If you want the value
of the identity column to decrease, specify a negative value for the INCREMENT option. It is also possible
to specify the exact range of numbers by using MINVALUE and MAXVALUE.
You can modify the attributes of an existing identity column using the ALTER TABLE statement. For
example, you want to restart the identity column with a new value:

ALTER TABLE ORDER

ALTER COLUMN ORDERNO
RESTART WITH 1;

You can also drop the identity attribute from a column:

ALTER TABLE ORDER

ALTER COLUMN ORDERNO
DROP IDENTITY;

The column ORDERNO remains as a SMALLINT column, but the identity attribute is dropped. The system
will no longer generate values for this column.
Related reference
Comparison of identity columns and sequences
While identity columns and sequences are similar in many ways, there are also differences.
Inserting values into an identity column
You can insert a value into an identity column or allow the system to insert a value for you.
Updating an identity column
You can update the value in an identity column to a specified value or have the system generate a new
value.

Using ROWID
Using ROWID is another way to have the system assign a unique value to a column. ROWID is similar to
identity columns. But rather than being an attribute of a numeric column, it is a separate data type.
To create a table similar to the identity column example:

CREATE TABLE ORDERS

(ORDERNO ROWID
GENERATED ALWAYS,
SHIPPED_TO VARCHAR (36) ,
ORDER_DATE DATE);

Creating and using sequences

Sequences are similar to identity columns in that they both generate unique values. However, sequences
are objects that are independent of any tables. You can use sequences to generate values quickly and
easily.
Sequences are not tied to a column in a table and are accessed separately. Additionally, they are not
treated as any part of a transaction's unit of work.
You create a sequence using the CREATE SEQUENCE statement. For an example similar to the identity
column example, create a sequence ORDER_SEQ:

CREATE SEQUENCE ORDER_SEQ

START WITH 500
INCREMENT BY 1
MAXVALUE 1000

SQL programming 25
 NOCYCLE
CACHE 24;

This sequence is defined with a starting value of 500, incremented by 1 for every use, and will not recycle
when the maximum value is reached. In this example, the maximum value for the sequence is 1000. Once
1000 is generated, no additional values can be provided and an error is issued.
After this sequence is created, you can insert values into a column using the sequence. For example,
insert the next value of the sequence ORDER_SEQ into a table ORDERS with columns ORDERNO and
CUSTNO.
First, create the table ORDERS:

CREATE TABLE ORDERS

(ORDERNO SMALLINT NOT NULL,
CUSTNO SMALLINT);

Then, insert the sequence value:

INSERT INTO ORDERS (ORDERNO, CUSTNO)

VALUES (NEXT VALUE FOR ORDER_SEQ, 12);

Running the following statement returns the values in the columns:

SELECT *
FROM ORDERS;

Table 2. Results for SELECT from table ORDERS

ORDERNO CUSTNO
500 12

In this example, the next value for sequence ORDER is inserted into the ORDERNO column. Issue the
INSERT statement again. Then run the SELECT statement.

Table 3. Results for SELECT from table ORDERS

ORDERNO CUSTNO
500 12
501 12

You can also insert the previous value for the sequence ORDER by using the PREVIOUS VALUE expression.
You can use NEXT VALUE and PREVIOUS VALUE in the following expressions:
• Within the select-clause of a SELECT statement or SELECT INTO statement as long as the statement
does not contain a DISTINCT keyword, a GROUP BY clause, an ORDER BY clause, a UNION keyword, an
INTERSECT keyword, or an EXCEPT keyword
• Within a VALUES clause of an INSERT statement
• Within the select-clause of the fullselect of an INSERT statement
• Within the SET clause of a searched or positioned UPDATE statement, though NEXT VALUE cannot be
specified in the select-clause of the subselect of an expression in the SET clause
The CREATE SEQUENCE statement provides a way to define an IBM i system name for the generated
object. If the name of the sequence is longer than ten characters, use the FOR SYSTEM NAME clause to
specify the system name to be assigned to the object. This example shows both names being provided
on the CREATE SEQUENCE statement. Either name can be used when referencing the sequence in an SQL
statement.

CREATE SEQUENCE ORDER_SEQUENCE FOR SYSTEM NAME ORDER_SEQ

START WITH 500
INCREMENT BY 1

26 IBM i: SQL programming

 MAXVALUE 1000
NOCYCLE
CACHE 24;

You can alter a sequence by issuing the ALTER SEQUENCE statement. Sequences can be altered in the
following ways:
• Restarting the sequence
• Changing the increment between future sequence values
• Setting or eliminating the minimum or maximum values
• Changing the number of cached sequence numbers
• Changing the attribute that determines whether the sequence can cycle or not
• Changing whether sequence numbers must be generated in order of request
For example, change the increment of values of sequence ORDER from 1 to 5:

ALTER SEQUENCE ORDER_SEQ

INCREMENT BY 5;

After this change is complete, run the INSERT statement again and then the SELECT statement. Now the
table contains the following columns.

Table 4. Results for SELECT from table ORDERS

ORDERNO CUSTNO
500 12
501 12
528 12

Notice that the next value that the sequence uses is a 528. At first glance, this number appears to be
incorrect. However, look at the events that lead up to this assignment. First, when the sequence was
originally create, a cache value of 24 was assigned. The system assigns the first 24 values for this cache.
Next, the sequence was altered. When the ALTER SEQUENCE statement is issued, the system drops the
assigned values and starts up again with the next available value; in this case the original 24 that was
cached, plus the next increment, 5. If the original CREATE SEQUENCE statement did not have the CACHE
clause, the system automatically assigns a default cache value of 20. If that sequence was altered, then
the next available value is 25.
Related concepts
Sequences
A sequence is a data area object that provides a quick and easy way of generating unique numbers.

Comparison of identity columns and sequences

While identity columns and sequences are similar in many ways, there are also differences.
Examine these differences before you decide which to use.
An identity column has the following characteristics:
• An identity column can be defined as part of a table when the table is created or it can be added to a
column using alter table. After a table is created, the identity column characteristics can be changed.
• An identity column automatically generates values for a single table.
• When an identity column is defined as GENERATED ALWAYS, the values used are always generated by
the database manager. Applications are not allowed to provide their own values when changing the
contents of the table.
• The IDENTITY_VAL_LOCAL function can be used to see the most recently assigned value for an identity
column.

SQL programming 27
 A sequence has the following characteristics:
• A sequence is a system object of type *DTAARA that is not tied to a table.
• A sequence generates sequential values that can be used in any SQL statement.
• There are two expressions used to retrieve the next values in the sequence and to look at the previous
value assigned for the sequence. The PREVIOUS VALUE expression returns the most recently generated
value for the specified sequence for a previous statement within the current session. The NEXT VALUE
expression returns the next value for the specified sequence. The use of these expressions allows the
same value to be used across several SQL statements within several tables.
While these are not all of the characteristics of identity columns and sequences, these characteristics can
help you determine which to use depending on your database design and the applications that use the
database.
Related reference
Creating and altering an identity column
Every time a row is added to a table with an identity column, the identity column value for the new row is
generated by the system.

Defining field procedures

Field procedures are assigned to a table by the FIELDPROC clause of the CREATE TABLE and ALTER
TABLE statements. A field procedure is a user-written exit routine that transforms values in a single
column.
When values in the column are changed, or new values inserted, the field procedure is invoked for each
value, and can transform that value (encode it) in any way. The encoded value is then stored. When
values are retrieved from the column, the field procedure is invoked for each value, which is encoded, and
must decode it back to the original value. Any indexes defined on a non-derived column that uses a field
procedure are built with encoded values.
The transformation your field procedure performs on a value is called field-encoding. The same routine is
used to undo the transformation when values are retrieved; that operation is called field-decoding. Values
in columns with a field procedure are described to Db2 in two ways:
1. The description of the column as defined in CREATE TABLE or ALTER TABLE appears in the catalog
table QSYS2.SYSCOLUMNS. That is the description of the field-decoded value, and is called the column
description.
2. The description of the encoded value, as it is stored in the database, appears in the catalog
table QSYS2.SYSFIELDS. That is the description of the field-encoded value, and is called the field
description.
Important: The field-decoding function must be the exact inverse of the field-encoding function. For
example, if a routine encodes 'ALABAMA' to '01', it must decode '01' to 'ALABAMA'. A violation of this rule
can lead to unpredictable results. See “General guidelines for writing field procedures” on page 47.
Field procedures can also perform masking of data when decoded (retrieved). In this case, the field
procedure would decode '01' to 'ALABAMA’ for certain users or environments and for other users or
environments may return a masked value such as ’XXXXXXXX’ instead. See “Guidelines for writing field
procedures that mask data” on page 48.
Field procedures can be defined for columns in a physical file. However, if the Change Physical File
(CHGPF) command is used to alter the definition, the field procedure is removed and must be re-
established.

28 IBM i: SQL programming

Field definition for field procedures
The field procedure is also invoked when the table is created or altered, to define the data type and
attributes of an encoded value to Db2. That operation is called field-definition.
The data type of the encoded value can be any valid SQL data type except ROWID or DATALINK. Also
a field procedure cannot be associated with any column having values generated as IDENTITY, ROW
CHANGE TIMESTAMP, ROW BEGIN, ROW END, TRANSACTION START ID, or a generated expression.
If a DDS-created physical file is altered to add a field procedure, the encoded attribute data type cannot
be a LOB type or DataLink. If an SQL table is altered to add a field procedure, the encoded attribute
precision field must be 0 if the encoded attribute data type is any of the integer types.
A field procedure may not be added to a column that has a default value of CURRENT DATE, CURRENT
TIME, CURRENT TIMESTAMP, or USER.
A column defined with a user-defined data type can have a field procedure if the source type of the
user-defined data type is any of the allowed SQL data types. Db2 casts the value of the column to the
source type before it passes it to the field procedure.

Specifying the field procedure

To name a field procedure for a column, use the FIELDPROC clause of the CREATE TABLE or ALTER TABLE
statement, followed by the name of the procedure and, optionally, a list of parameters.
The optional parameter list that follows the procedure name is a list of constants, enclosed in
parentheses, called the literal list. The literal list is converted by Db2 into a data structure called the
field procedure parameter value list (FPPVL). The FPPVL is passed to the field procedure during the
field-definition operation. At that time, the procedure can modify it or return it unchanged. The output
form of the FPPVL is called the modified FPPVL. It is stored in the Db2 QSYS2.SYSFIELDS catalog table as
part of the column description. The modified FPPVL is passed again to the field procedure whenever that
procedure is invoked for field-encoding or field-decoding.

When field procedures are invoked

A field procedure that is specified for a column is invoked in three general situations.
• For field-definition, when the CREATE TABLE or ALTER TABLE statement that names the procedure is
executed. During this invocation, the procedure is expected to:
– Determine whether the data type and attributes of the column are valid.
– Verify the literal list, and change it if desired.
– Provide the field description of the column.
• For field-encoding, when a column value is to be encoded. Encoding occurs for any value that:
– Is inserted in the column by an SQL INSERT statement, SQL MERGE statement, or native write
operation.
– Is changed by an SQL UPDATE statement, SQL MERGE statement, or native update operation.
– If the data needs to be copied and the target column has a field procedure, it is possible that the
field procedure may be invoked to encode the copied data. Examples include the SQL statements
ALTER TABLE or CREATE TABLE (with a LIKE or as-result-table clause) and the CL commands CPYF or
RGZPFM.
– Is compared to a column with a field procedure. The QAQQINI option
FIELDPROC_ENCODED_COMPARISON is used by the optimizer to decide if the column value is
decoded or if the variable, constant, or join column is encoded.
– At CREATE or ALTER TABLE time for the DEFAULT value, if the column has a field procedure.
If there are any after or read triggers, the field procedure is invoked before any of these triggers.
For before triggers, there may be multiple invocations of the field procedure with encode and decode
operations. The number of calls to the field procedure depends on many factors including the type of

SQL programming 29
 trigger and if the trigger changes the data in the trigger buffer. The database manager will ensure that
the field procedure is called to encode the data that will be inserted into the table.
• For field-decoding, when a stored value is to be field-decoded back into its original value. This occurs
for any value that is:
– Retrieved by an SQL SELECT or FETCH statement, or by a native read operation.
– If the data needs to be copied and the source column has a field procedure, it is possible that the
field procedure may be invoked to decode the data prior to making the copy. Examples include the
SQL statements ALTER TABLE or CREATE TABLE (with a LIKE or as-result-table clause) and the CL
commands CPYF or RGZPFM.
– Is compared to a column with a field procedure. The QAQQINI option
FIELDPROC_ENCODED_COMPARISON is used by the optimizer to decide if the column value is
decoded or if the variable or constant is encoded.
A field procedure is never invoked to process a null value. It is also not invoked for a DELETE operation
without a WHERE clause when the table has no DELETE triggers. The field procedure is invoked for
empty strings.
Recommendation: Avoid encoding blanks in a field procedure. When Db2 for i compares the values of two
strings with different lengths, it temporarily pads the shorter string with the appropriate blank characters
(for example, EBCDIC or double-byte blanks) up to the length of the longer string. If the shorter string is
the value of a column with a field procedure, padding is done to the encoded value, but the pad character
is not encoded. Therefore, if the procedure changes blanks to some other character, encoded blanks at
the end of the longer string are not equal to padded blanks at the end of the shorter string. That situation
can lead to errors; for example, some strings that ought to be equal might not be recognized as such.
Therefore, encoding blanks in a field procedure is not recommended.

Parameter list for execution of field procedures

The field procedure parameter list communicates general information to a field procedure.
The parameter list tells what operation is to be done and allows the field procedure to signal errors. Db2
provides storage for all parameters that are passed to the field procedure. Therefore, parameters are
passed to the field procedure by address.
When defining and using the parameters in the field procedure, care should be taken to ensure that no
more storage is referenced for a given parameter than is defined for that parameter. The parameters are
all stored in the same space and exceeding a given parameter's storage space can overwrite another
parameter's value. This, in turn, can cause the field procedure to see invalid input data or cause the value
returned to the database to be invalid.
Parameter 1
A small (2 byte) integer that describes the function to be performed. This parameter is input only.
Supported values are:
• 0 – field-encoding
• 4 – field-decoding
• 8 – field-definition
Parameter 2
A structure that defines the field procedure parameter value list (FPPVL).
• For function code 8, this parameter is input/output.
• For function code 0 and 4, this parameter contains the output of the function code 8 call. This
parameter is input only
Parameter 3
The decoded data attribute that is defined by the Column Value Descriptor (CVD). This is the column
attributes that were specified at CREATE TABLE or ALTER TABLE time. This parameter is input only.

30 IBM i: SQL programming

Parameter 4
The decoded data. The exact structure is dependent on function code.
• If function code 8, then the NULL value. This parameter is input only.
• If function code 0, then the data to be encoded. This parameter is input only.
• If function code 4, then the location to place the decoded data. This parameter is output only.
Parameter 5
The encoded data attribute that is defined by the Field Value Descriptor (FVD).
• If function code 8, then the structure containing the encoded data attributes. This parameter is
output only.
• If function code 0 or 4 , then a structure containing the encoded data attributes that was returned
by the function 8 call. This parameter is input only.
Parameter 6
The encoded data that is defined by the Field Value Descriptor (FVD). The exact structure is
dependent on function code.
• If function code 8, then the NULL value. This parameter is input only.
• If function code 0, then the location to place the encoded data. This parameter is output only.
• If function code 4, then the encoded form of the data. This parameter is input only.
Parameter 7
The SQLSTATE (character(5)). This parameter is input/output.
This parameter is set by Db2 to '00000' before calling the field procedure. It can be set by the field
procedure. While normally the SQLSTATE is not set by a field procedure, it can be used to signal an
error to the database as follows:
• If the field procedure detects an error, it should set the SQLSTATE to '38xxx', where xxx may be one
of several possible strings. For more information, see Db2 Messages and Codes.
Warnings are not supported for field procedures
Parameter 8
The message text area (varchar(1000)). This parameter is input/output.
This argument is set by Db2 to the empty string before calling the field procedure. It is a
VARCHAR(1000) value that can be used by the field procedure to send message text back when
an SQLSTATE error is signaled by the field procedure. Message text is ignored by Db2 unless the
SQLSTATE parameter is set by the field procedure. The message text is assumed to be in the job
CCSID.
Parameter 9
A 128-byte structure containing additional information for the field procedure. This parameter is input
only.
This structure is set by Db2 before calling the field procedure.
• sqlfpNoMask - For field procedures that mask data, it indicates that the caller is a system function
that requires that the data be decoded without masking. For example, in some cases, RGZPFM and
ALTER TABLE may need to copy data. If the field procedure ignores this parameter and masks data
when these operations are performed, the column data will be lost. Hence, it is critical that a field
procedure that masks data properly handle this parameter.
Supported values are:
– '0' - Normal masking applied if needed.
– '1' - Do not mask, this decode operation is being performed on behalf of the database manager.
• sqlfpOperation - This parameter indicates whether the field procedure is being called for an
encode operation that is building a key value for a key positioning operation. Key positioning
operations such as RPG SETLL, RPG CHAIN, and COBOL START are common in applications that

SQL programming 31
 use the non-SQL interface for data access. This parameter can be useful for field procedure that
mask data.
Supported values are:
– '0' - Not called for key positioning.
– '1' - Called for key positioning.
If a field procedure encounters a masked value on an encode request to build a key value, there are
two actions for the field procedure to take:
– Return SQLSTATE '09501' in parameter 7 which will cause the key positioning operation to fail
with a field procedure error.
– Return the masked value as the encoded value in parameter 6, so that the key positioning
operation can continue using the masked value. In this case, the key positioning operation may or
may not be successful. For example, an RPG SETLL request will likely be successful. However, an
RPG CHAIN operation will likely fail with a record not found error.
Include SQLFP in QSYSINC/H describes these parameters.

The field procedure parameter value list (FPPVL)

The field procedure parameter value list communicates the literal list, supplied in the CREATE TABLE or
ALTER TABLE statement, to the field procedure during field-definition.
At that time, the field procedure can reformat the FPPVL; it is the reformatted FPPVL that is stored in
QSYS2.SYSFIELDS and communicated to the field procedure during field-encoding and field-decoding as
the modified FPPVL.
The following tables describe the FPPVL:
Table 5. sqlfpFieldProcedureParameterList_T

Offse
Name t Data Type Description

sqlfpOptParmValueListLength 0 4-byte integer Length in bytes of this structure

sqlfpNumberOfOptionalParms 4 4-byte integer Number of value descriptors that follow.

Equal to the number of parameters in the
FIELDPROC clause. Zero if no parameters
were listed.

sqlfpParmList 8 structure A list containing

sqlfpOptionalParameterValueDescriptor_T sqlfpNumberOfOptionalParms count of
sqlfpOptionalParameterValueDescriptor_T
items.

Table 6. sqlfpOptionalParameterValueDescriptor_T

Offse
Name t Data Type Description

sqlfpOptDescLength 0 4-byte integer Length in bytes of this structure

sqlfpParmDesc 4 structure sqlfpParameterDescription_T Parameter description

reserved2 38 character(12) Not used

sqlfpParmData 40 The optional parameter data value.

• If the value is a varying-length string, the
first 2 bytes contains its length.
• If the value is a LOB or XML string, the first
4 bytes contains its length.
• If this value is numeric, the internal
numeric representation of the data.
• If a datetime value, the value is in *ISO
format.

32 IBM i: SQL programming

 Parameter value descriptors for field procedures
A parameter value descriptor describes the data type and other attributes of a value.
Parameter value descriptors are used with field procedures in these ways:
• During field-definition, they describe each constant in the field procedure parameter value list (FPPVL).
The set of these optional parameter value descriptors are part of the FPPVL control block.
• During field-encoding and field-decoding, the decoded (column) value and the encoded (field) attributes
are described by the column value descriptor (CVD) and the field value descriptor (FVD).
The column value descriptor (CVD) contains a description of a column value. During field-encoding, the
CVD describes the value to be encoded. During field-decoding, it describes the decoded value to be
supplied by the field procedure. During field-definition, it describes the column as defined in the CREATE
TABLE or ALTER TABLE statement.
The field value descriptor (FVD) contains a description of a field value. During field-encoding, the FVD
describes the encoded value to be returned from the field procedure. During field-decoding, it describes
the value to be decoded. During field-definition a description of the encoded value must put into the FVD.
The following table describes a parameter value descriptor:
Table 7. sqlfpParameterDescription_T

Offse
Name t Data Type Description

sqlfpSqlType 0 2-byte integer SQL data type of this parameter. See

Appendix D of the SQL Reference for
supported values.

sqlfpByteLength 2 unsigned 4-byte integer Length in bytes of this parameter. For

datetime parameters, the length of the string
representation of the parameter.

sqlfpLength 6 unsigned 4-byte integer Length in characters of this parameter. If

this is a not a character or graphic type,
sqlfpLength and sqlfpByteLength are the
same value.

sqlfpPrecision 10 2-byte integer Precision if this is a numeric parameter that

has precision (decimal, zoned, binary with
precision and scale).

sqlfpScale 12 2-byte integer Scale if this is a numeric parameter that has

scale (decimal, zoned, binary with precision
and scale). Scale of 0 if this is a date or time
parameter. Scale of 6 if this is a timestamp
parameter.

sqlfpCcsid 14 unsigned 2-byte integer CCSID of this parameter if character or

graphic or XML.

sqlfpAllocatedLength 16 unsigned 2-byte integer The allocated length specified for the
column on the CREATE TABLE or ALTER
TABLE statement.

reserved1 18 character(14) Reserved.

Field-definition (function code 8)

The input provided to the field-definition operation, and the output required, are as follows:
• Parameter 1
Input - A small (2 byte) integer that describes the function to be performed (8 - field-definition).
• Parameter 2
Input/Output - A structure that defines the field procedure parameter value list (FPPVL). This is an
auto-extendable space. The minimum length of this structure is 8 bytes. The maximum returned
length of this structure is 32K.

SQL programming 33
 • Parameter 3
Input - The structure sqlfpParameterDescription_T containing the decoded data attributes.
• Parameter 4
Not used.
• Parameter 5
Output - The structure sqlfpParameterDescription_T containing the encoded data attributes. The
output sqlfpParameterDescription_T must be valid with the appropriate CCSID, length, precision, and
scale fields set.
• Parameter 6
Not used.
• Parameter 7
Input/Output - The SQLSTATE (character(5)).
• Parameter 8
Input/Output - The message text area (varchar(1000)).
• Parameter 9
Input - Reserved.
Errors returned by a field procedure result in SQLCODE -681 (SQLSTATE '23507'), which is set in the SQL
communication area (SQLCA) and the DB2_RETURNED_SQLCODE and RETURNED_SQLSTATE condition
area item of the SQL diagnostics area. The contents of Parameter 7 and 8 are placed into the tokens,
in SQLCA, as field SQLERRMT and in the SQL Diagnostic area condition area item MESSAGE_TEXT. The
meaning of the error message is determined by the field procedure.
Invalid data in Parameter 5, sqlfpParameterDescription_T, or an invalid length in Parameter 2 results in
SQLCODE -685 (SQLSTATE '58002'). If the database manager is unable to invoke the field procedure then
SQLCODE -682 (SQLSTATE '57010') is returned.
The FPPVL can be redefined to suit the field procedure, and returned as the modified FPPVL, subject to
the following restriction:
• sqlfpOptParmValueListLength must contain the actual length of the modified FPPVL. If no parameter list
is returned, then sqlfpOptParmValueListLength must be set to 8.
The modified FPPVL is recorded in the catalog table QSYS2.SYSFIELDS, and is passed to the field
procedure during field-encoding and field-decoding. The modified FPPVL need not have the format
of a field procedure parameter list, and it need not describe constants by optional parameter value
descriptors.
The nullability attribute of the column may not be changed.
If the encoded data attribute is a character, graphic or XML type, the CCSID value must be set to a valid
CCSID for the data type.
If the column has a non-null default value, the encoded default value must not exceed the length allowed
for the column's default value.

Field-encoding (function code 0)

The input provided to the field-encoding operation, and the output required, are as follows:
• Parameter 1
Input - A small (2 byte) integer that describes the function to be performed (0 - field-encoding).
• Parameter 2
Input - A structure that defines the modified field procedure parameter value list (FPPVL).
• Parameter 3
Input - A structure described by sqlfpParameterDescription_T containing the decoded data attributes.
• Parameter 4
Input – Data to be encoded.

34 IBM i: SQL programming

 If the value is a varying-length string, the first 2 bytes contains its length. If the value is a LOB or XML,
then the first 4 bytes contains the length. If the value is numeric, the internal numeric representation
of the data. If a datetime value, the value is in *ISO format.
• Parameter 5
Input - A structure described by sqlfpParameterDescription_T containing the encoded data attributes.
• Parameter 6
Output – Location to place the encoded data.
If the encoded value is a varying-length string, the first 2 bytes must contain the length. If the
encoded value is a LOB or XML, then the first 4 bytes must contain the length. If the value is numeric,
the internal numeric representation of the data. If a datetime value, the value must be in *ISO format.
• Parameter 7
Input/Output - The SQLSTATE (character(5)).
• Parameter 8
Input/Output - The message text area (varchar(1000)).
• Parameter 9
Input - Reserved.
Errors returned by a field procedure result in SQLCODE -681 (SQLSTATE '23507'), which is set in the SQL
communication area (SQLCA) and the DB2_RETURNED_SQLCODE and RETURNED_SQLSTATE condition
area item of the SQL diagnostics area. The contents of Parameter 7 and 8 are placed into the tokens,
in SQLCA, as field SQLERRMT and in the SQL Diagnostic area condition area item MESSAGE_TEXT. If the
database manager is unable to invoke the field procedure then SQLCODE -682 (SQLSTATE '57010') is
returned.

Field-decoding (function code 4)

The input provided to the field-decoding operation, and the output required, are as follows:
• Parameter 1
Input - A small (2 byte) integer that describes the function to be performed (4 - field-decoding).
• Parameter 2
Input - A structure that defines the modified field procedure parameter value list (FPPVL).
• Parameter 3
Input - A structure described by sqlfpParameterDescription_T containing the decoded data attributes.
• Parameter 4
Output – Location to place the decoded data.
If the decoded value is a varying-length string, the first 2 bytes must contain the length. If the
decoded value is a LOB or XML, then the first 4 bytes must contain the length. If the value is numeric,
the internal numeric representation of the data. If a datetime value, the value must be in *ISO format.
• Parameter 5
Input - A structure described by sqlfpParameterDescription_T containing the encoded data attributes.
• Parameter 6
Input - encoded data
If the value is a varying-length string, the first 2 bytes contains its length. If the value is a LOB or XML,
then the first 4 bytes contains the length. If the value is numeric, the internal numeric representation
of the data. If a datetime value, the value is in *ISO format.
• Parameter 7
Input/Output - The SQLSTATE (character(5)).
• Parameter 8
Input/Output - The message text area (varchar(1000)).
• Parameter 9
Input - Indicates that the caller is a system function that requires that the data be decoded without
masking.

SQL programming 35
 Errors returned by a field procedure result in SQLCODE -681 (SQLSTATE '23507'), which is set in the SQL
communication area (SQLCA) and the DB2_RETURNED_SQLCODE and RETURNED_SQLSTATE condition
area item of the SQL diagnostics area. The contents of Parameter 7 and 8 are placed into the tokens,
in SQLCA, as field SQLERRMT and in the SQL Diagnostic area condition area item MESSAGE_TEXT. If the
database manager is unable to invoke the field procedure then SQLCODE -682 (SQLSTATE '57010') is
returned.

Example field procedure program

Add field procedure FP1 to column C1. The Field Procedure FP1 takes one parameter which indicates the
number of bytes of the column the field procedure should operate on.

ALTER TABLE TESTTAB ALTER C1 SET FIELDPROC FP1(10)

The following two field procedure programs demonstrate the same operations being done first in ILE RPG
and then in C. These programs illustrate how a field procedure program could use the IBM i Cryptographic
Services APIs to encrypt and decrypt fixed length character, variable length character, or character LOB
data.
The program demonstrates some of the possibilities with field procedure programs but does not attempt
to handle every possible situation and error. Note how the following items are handled:
• Changing of the encoded data type from the decoded data type
• The addressing differences required for fixed length character, varying length character, and CLOB data.
• Usage of the optional parm structure (sqlfpOptionalParameterValueDescriptor_T).
More information about IBM i Cryptographic Services APIs, see Cryptographic Services APIs

Example field procedure written in ILE RPG

For a field procedure written in ILE RPG, you should consider the following items:
• For better performance, ACTGRP(*CALLER) and STGMDL(*INHERIT) is recommended when you compile
your program. This example has these keywords in the Control statements (H specs).
• The THREAD keyword must be specified on the Control statements. This example has
THREAD(*CONCURRENT), but THREAD(*SERIALIZE) could also be used.

ctl-opt main(SampleFieldProcProgram);
ctl-opt option(*srcstmt);
ctl-opt stgmdl(*inherit);
ctl-opt thread(*concurrent);
/if defined(*crtbndrpg)
ctl-opt actgrp(*caller);
/endif

/copy QSYSINC/QRPGLESRC,QC3CCI
/copy QSYSINC/QRPGLESRC,QUSEC
/copy QSYSINC/QRPGLESRC,SQL
/copy QSYSINC/QRPGLESRC,SQLFP

// QSYSINC/H QC3DTAEN
dcl-pr Qc3EncryptData extproc(*dclcase);
clearData pointer value;
clearDataLen int(10) const;
clearDataFormat char(8) const;
algorithmDesc likeds(QC3D0200); // Qc3_Format_ALGD0200
algorithmDescFormat char(8) const;
keyDesc likeds(T_key_descriptor0200) const;
keyDescFormat char(8) const;
cryptoServiceProvider char(1) const;
cryptoDeviceName char(10) const;
encryptedData pointer value;
lengthOfAreaForEncryptedData int(10) const;
lengthOfEncryptedDataReturned int(10);
errorCode likeds(QUSEC);
end-pr;

// QSYSINC/H QC3DTADE
dcl-pr Qc3DecryptData extproc(*dclcase);

36 IBM i: SQL programming

 encryptedData pointer value;
encryptedDataLen int(10) const;
algorithmDesc likeds(QC3D0200); // Qc3_Format_ALGD0200
algorithmDescFormat char(8) const;
keyDesc likeds(T_key_descriptor0200) const;
keyDescFormat char(8) const;
cryptoServiceProvider char(1) const;
cryptoDeviceName char(10) const;
clearData pointer value;
lengthOfAreaForClearData int(10) const;
lengthOfClearDataReturned int(10);
errorCode likeds(QUSEC);
end-pr;

// Constants from QSYSINC/H QC3CCI

dcl-c Qc3_AES 22;
dcl-c Qc3_ECB '0';
dcl-c Qc3_Pad_Char '1';
dcl-c Qc3_Bin_String '0';
dcl-c Qc3_Key_Parms 'KEYD0200';
dcl-c Qc3_Alg_Block_Cipher 'ALGD0200';
dcl-c Qc3_Data 'DATA0100';
dcl-c Qc3_Any_CSP '0';

// Constants from QSYSINC/H SQL

dcl-c SQL_TYP_CLOB 408; // CLOB - varying length string
dcl-c SQL_TYP_NCLOB 409; // (SQL_TYP_CLOB + 1 for NULL)

dcl-c SQL_TYP_VARCHAR 448; // VARCHAR(i) - varying length string

// (2 byte length)
dcl-c SQL_TYP_NVARCHAR 449; // (SQL_TYP_VARCHAR + 1 for NULL)
dcl-c SQL_TYP_CHAR 452; // CHAR(i) - fixed length string
dcl-c SQL_TYP_NCHAR 453; // (SQL_TYP_CHAR + 1 for NULL)
dcl-c SQL_TYP_BLOB 404; // BLOB - varying length string
dcl-c SQL_TYP_NBLOB 405; // (SQL_TYP_BLOB + 1 for NULL)

// Other constants
dcl-c KEY_MGMT_SIZE 16;
dcl-c MAX_VARCHAR_SIZE 32767;
dcl-c MAX_CLOB_SIZE 100000;

dcl-ds T_key_descriptor0200 template qualified;

desc likeds(QC3D020000);
key char(KEY_MGMT_SIZE);
end-ds;

// T_DECODED_VARCHAR is the same as a VARCHAR field in RPG

// but it is convenient to define it as a structure
// for this purpose
dcl-ds T_DECODED_VARCHAR qualified template;
len int(5);
data char(MAX_VARCHAR_SIZE);
end-ds;

dcl-ds T_DECODED_CLOB qualified template;

len int(10);
data char(MAX_CLOB_SIZE);
end-ds;

dcl-ds T_ENCODED_VARCHAR qualified template;

len int(5);
keyManagementData char(KEY_MGMT_SIZE);
data char(MAX_VARCHAR_SIZE);
end-ds;

dcl-ds T_ENCODED_CLOB qualified template;

len int(10);
keyManagementData char(KEY_MGMT_SIZE);
data char(MAX_CLOB_SIZE);
end-ds;

dcl-ds T_DECODED_DATA qualified template;

varchar likeds(T_DECODED_VARCHAR) pos(1);
clob likeds(T_DECODED_CLOB) pos(1);
end-ds;

dcl-ds T_ENCODED_DATA qualified template;

varchar likeds(T_ENCODED_VARCHAR) pos(1);
clob likeds(T_ENCODED_CLOB) pos(1);
end-ds;

dcl-ds T_optional qualified template;

SQL programming 37
 bytes uns(10);
type_indicator char(1);
end-ds;

// Main procedure
dcl-proc SampleFieldProcProgram;

dcl-pi *n EXTPGM('FP_EXV1RPG');
FuncCode uns(5) const;
OptionalParms likeds(T_optional);
DecodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
DecodedData likeds(T_DECODED_DATA);
EncodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
EncodedData likeds(T_ENCODED_DATA);
SqlState char(5);
Msgtext varchar(1000); // SQLFMT DS in QSYSINC/SQLFP is an RPG VARCHAR
end-pi;

dcl-ds ErrCode likeds(QUSEC) inz;

dcl-ds ALGD0200 likeds(QC3D0200) inz;

dcl-ds T_key_descriptor0200 qualified inz;
desc LIKEDS(QC3D020000);
key char(KEY_MGMT_SIZE);
end-ds;
dcl-ds KeyDesc0200 likeds(T_key_descriptor0200) inz;

dcl-s DecryptedDataLen int(10);

dcl-s DecryptedData char(MAX_CLOB_SIZE) based(Decrypted_Datap);
dcl-s Decrypted_Datap pointer;

dcl-s EncryptedDataLen int(10);

dcl-s EncryptedData char(MAX_CLOB_SIZE) based(Encrypted_Datap);
dcl-s Encrypted_Datap pointer;

dcl-s RtnLen int(10);

dcl-s KeyManagement char(KEY_MGMT_SIZE) based(KeyMgmtp);
dcl-s KeyMgmtp pointer;

ErrCode = *allx'00';
ErrCode.QUSBPRV = %size(QUSEC); // Bytes_provided

if FuncCode = 8; // create or alter time

FieldCreatedOrAltered (%addr(OptionalParms)
: DecodedDataType
: EncodedDataType
: SqlState
: Msgtext);
return;
endif;

// Initialize the Algorithm Description Format

ALGD0200 = *allx'00';
ALGD0200.QC3BCA = Qc3_AES; // set block cipher algorithm
ALGD0200.QC3BL = 16; // set block length
ALGD0200.QC3MODE = Qc3_ECB; // set mode
ALGD0200.QC3PO = Qc3_Pad_Char; // set pad option

// Initialize the Key Description Format

KeyDesc0200 = *allx'00';
KeyDesc0200.desc.QC3KT = Qc3_AES; // set key type
KeyDesc0200.desc.QC3KSL = 16; // set key string length
KeyDesc0200.desc.QC3KF = Qc3_Bin_String; // set key format

if FuncCode = 0; // encode

// Get the actual length of the data depending on the type

select;
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
DecryptedDataLen = DecodedData.varchar.len;
Decrypted_Datap = %addr(DecodedData.varchar.data);
when DecodedDataType.SQLFST = SQL_TYP_CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
DecryptedDataLen = DecodedData.Clob.len;
Decrypted_Datap = %addr(DecodedData.Clob.data);
other; // must be fixed Length
// for fixed length, only the data is passed, get the
// length of the data from the data type parameter
DecryptedDataLen = DecodedDataType.SQLFBL; // byte length
Decrypted_Datap = %addr(DecodedData);
endsl;

38 IBM i: SQL programming

 // Determine if the encoded data type is varchar or CLOB based on
// the optional parameter information that was saved at create time.
if OptionalParms.type_indicator = '0'; // encoded data is varchar
Encrypted_Datap = %addr(EncodedData.Varchar.data);
KeyMgmtp = %addr(EncodedData.Varchar.keyManagementData);
else; // encoded data is CLOB
Encrypted_Datap = %addr(EncodedData.Clob.data);
KeyMgmtp = %addr(EncodedData.Clob.keyManagementData);
endif;

if DecryptedDataLen > 0; // have some data to encrypt.

// get the encrypt key
getKeyMgmt('E' : KeyManagement : KeyDesc0200.key);
// Set the number of bytes available for encrypting. Subtracting
// off the bytes used for "key management".
EncryptedDataLen = EncodedDataType.SQLFBL - KEY_MGMT_SIZE;
// Encrypt the data
Qc3EncryptData(Decrypted_Datap
: DecryptedDataLen
: Qc3_Data
: ALGD0200
: Qc3_Alg_Block_Cipher
: KeyDesc0200
: Qc3_Key_Parms
: Qc3_Any_CSP
: ' '
: Encrypted_Datap
: EncryptedDataLen
: RtnLen
: ErrCode);
RtnLen += KEY_MGMT_SIZE; // add in the Key Area size
else; // length is 0
RtnLen = 0;
endif;
// store the length (number of bytes that database needs to write)
// in either the 2 or 4 byte length field based on the encrypted
// data type
if OptionalParms.type_indicator = '0';
EncodedData.Varchar.len = RtnLen;
else;
EncodedData.Clob.len = RtnLen;
endif;
elseif FuncCode = 4; // decode
// Determine if the encoded data type is varchar or CLOB based on the
// optional parameter information that was saved at create time. Set
// pointers to the key management data, the user encrypted data, and
// the length of the data.
if OptionalParms.type_indicator = '0'; // varchar
KeyMgmtp = %addr(EncodedData.Varchar.keyManagementData);
Encrypted_Datap = %addr(EncodedData.Varchar.data);
EncryptedDataLen = EncodedData.Varchar.len;
else; // CLOB
KeyMgmtp = %addr(EncodedData.Clob.keyManagementData);
Encrypted_Datap = %addr(EncodedData.Clob.data);
EncryptedDataLen = EncodedData.Clob.len;
endif;
// Set the number of bytes to decrypt. Subtract
// off the bytes used for "key management".
EncryptedDataLen -= KEY_MGMT_SIZE;
if EncryptedDataLen > 0; // have data to decrypt
// Set the pointer to where the decrypted data should
// be placed.
select;
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
Decrypted_Datap = %addr(DecodedData.varchar.data);
when DecodedDataType.SQLFST = SQL_TYP_CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
decryptedDataLen = DecodedData.Clob.len;
decrypted_Datap = %addr(DecodedData.Clob.data);
other; // must be fixed Length
decrypted_Datap = %addr(DecodedData);
endsl;

// get the decrypt key

getKeyMgmt('D' : KeyManagement : KeyDesc0200.key);
// get the maximum number of bytes available for the
// decode space
DecryptedDataLen = DecodedDataType.SQLFBL;
// decrtype the data
Qc3DecryptData(Encrypted_Datap

SQL programming 39
 : EncryptedDataLen
: ALGD0200
: Qc3_Alg_Block_Cipher
: KeyDesc0200
: Qc3_Key_Parms
: Qc3_Any_CSP
: ' '
: Decrypted_Datap
: DecryptedDataLen
: RtnLen
: ErrCode);
else; // 0 length data
RtnLen = 0;
endif;
// tell the database manager how many characters of data are being returned
select;
when DecodedDataType.SQLFST = SQL_TYP_VARCHAR
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
DecodedData.varchar.len = RtnLen;
when DecodedDataType.SQLFST = SQL_TYP_CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
DecodedData.clob.len = RtnLen;
other;
// must be fixed Length and the full number of characters must be
// returned
endsl;
else; // unsupported option -- error
SqlState = '38003';
endif;

if ErrCode.QUSBAVL > 0; // Something failed on encrypt/decrypt

// set an error and return the exception id
SqlState = '38004';
msgtext = ErrCode.QUSEI; // Exception_Id
endif;

end-proc SampleFieldProcProgram;

// procedure FieldCreatedOrAltered
dcl-proc FieldCreatedOrAltered;
dcl-pi *n extproc(*dclcase);
OptionalParms_p pointer value;
DecodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
EncodedDataType likeds(SQLFPD); // sqlfpParameterDescription_T
SqlState char(5);
Msgtext varchar(1000);
end-pi;

// Note that while optional parameters are not supported on input into
// this fieldproc, it will set information into the structure for
// usage by encode/decode operations. The length of this
// structure must be at least 8 bytes long, so the length is not
// reset.

// The optional parameter as it is passed in to this program

dcl-ds inputOptionalParms likeds(SQLFFPPL) // sqlfpFieldProcedureParameterList_T
based(OptionalParms_p);

// The optional parameter as it is modified by this program

// to later be passed for Encrypt and Decrypt
dcl-ds outputOptionalParms likeds(T_optional)
based(OptionalParms_p);

dcl-c errortext1 'Unsupported type in fieldproc.';

if inputOptionalParms.SQLFNOOP <> 0; // sqlfpNumberOfOptionalParms

// this fieldproc does not handle optional parameters
SqlState = '38001';
return;
endif;

select;
when DecodedDataType.SQLFST = SQL_TYP_CHAR // Fixed char
or DecodedDataType.SQLFST = SQL_TYP_NCHAR;
// set the encode data type to VarChar
EncodedDataType.SQLFST = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area
to
// store "constant" information to be used by the fieldproc on encode and decode
operations.
// Indicate that the encode type is varchar.
outputOptionalParms.type_indicator = '0';

40 IBM i: SQL programming

 when DecodedDataType.SQLFST = SQL_TYP_VARCHAR // Varying char
or DecodedDataType.SQLFST = SQL_TYP_NVARCHAR;
// set the data type to BLOB */
EncodedDataType.SQLFST = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area
to
// store "constant" information to be used by the fieldproc on encode and decode
operations.
// Indicate that the encode type is varchar.
outputOptionalParms.type_indicator = '0';
when DecodedDataType.SQLFST = SQL_TYP_CLOB // CLOB
or DecodedDataType.SQLFST = SQL_TYP_NCLOB;
// set the data type to BLOB */
EncodedDataType.SQLFST = SQL_TYP_BLOB;
// This example shows how the fieldproc pgm can modify the optional parm data area
to
// store "constant" information to be used by the fieldproc on encode and decode
operations.
// Indicate that the encode type is CLOB.
outputOptionalParms.type_indicator = '1';
other;
// this field proc does not handle any other data types
SqlState = '38002';
msgtext = errortext1;
return;
endsl;

// finish setting the rest of encoded data type values

// the null-ness of the encoded and decoded type must match

if %bitand(DecodedDataType.SQLFST : x'01') = 1;
EncodedDataType.SQLFST = %bitor(EncodedDataType.SQLFST : x'01'); // set to null
capable
endif;

// Determine the result length by adding one byte for the pad character counter and
// rounding the length up to a multiple of 15-- the AES encryption alogrithm
// will return the encrypted data in a multiple of 15.
// This example also shows how additional data can be stored by the fieldproc
// program in the encrypted data. An additional 16 bytes are added for use by
// the fieldproc program.
// Note that this fieldproc does not check for exceeding the maximum length for
// the data type. There may also be other conditions that are not handled by
// this sample fieldproc program.
EncodedDataType.SQLFL =
(%div(DecodedDataType.SQLFL + 16 : 16) * 16) + KEY_MGMT_SIZE; // characters
EncodedDataType.SQLFBL = EncodedDataType.SQLFL; // Byte
// result is *HEX CCSID
EncodedDataType.SQLFC = 65535;

if DecodedDataType.SQLFST = SQL_TYP_CHAR
or DecodedDataType.SQLFST = SQL_TYP_NCHAR; // fixed length character
// need to set the allocated length for fixed length since the default value
// must fit in the allocated portion of varchar. Note that if the varchar or
// CLOB had a default value of something other than the empty string, the
// allocated length must be set appropriately but this fieldproc does not
// handle this situtation.
EncodedDataType.SQLFAL = EncodedDataType.SQLFL;
endif;
end-proc FieldCreatedOrAltered;

// procedure getKeyMgmt
dcl-proc getKeyMgmt;

dcl-pi *n extproc(*dclcase);
type char(1) const;
keyMgmt char(KEY_MGMT_SIZE);
keyData char(KEY_MGMT_SIZE);
end-pi;

// This is a trivial key management idea and is used to demonstrate how additional
// information may be stored in the encoded data which is written to the table and
// be used to communicate between the encode and decode operations.
if type = 'E'; // encoding, set the current key
keyMgmt = 'KEYTYPE2';
keyData = '0123456789ABCDEG'; // end in G
elseif keyMgmt = 'KEYTYPE1'; // decoding, determine which key to use
keyData = '0123456789ABCDEF'; // end in F
elseif keyMgmt = 'KEYTYPE2';
keyData = '0123456789ABCDEG'; // end in G
endif;

SQL programming 41
 end-proc getKeyMgmt;

Example field procedure written in C

For better performance for a field procedure written in C, ACTGRP(*CALLER), TERASPACE(*YES) and
STGMDL(*INHERIT) are recommended when you compile your program.

#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <QC3CCI.H>
#include <QUSEC.H>
#include <QC3DTAEN.H>
#include <QC3DTADE.H>
#include <SQL.h>
#include <SQLFP.h>

#define KEY_MGMT_SIZE 16
typedef _Packed struct
{
unsigned short int len;
char data[];
}T_VARCHAR;

typedef _Packed struct

{
unsigned long len;
char data[];
}T_CLOB;

typedef _Packed struct

{
unsigned short int len;
char keyManagementData[KEY_MGMT_SIZE];
char data[];
}T_ENCODED_VARCHAR;

typedef _Packed struct

{
unsigned long len;
char keyManagementData[KEY_MGMT_SIZE];
char data[];
}T_ENCODED_CLOB;

typedef _Packed struct

{
unsigned long bytes;
char type_indicator;
char not_used[3];
}T_optional;

typedef struct KeyDescriptor0200

{
Qc3_Format_KEYD0200_T desc;
char key[KEY_MGMT_SIZE];
} T_key_descriptor0200;

static void fieldCreatedOrAltered(void *argv[]);

static void KeyMgmt(char type, char *keyMgmt, char *keyData);

main(int argc, void *argv[])

{
short *funccode = argv[1];
T_optional *optionalParms = argv[2];
char *sqlstate = argv[7];
sqlfpMessageText_T *msgtext = argv[8];
sqlfpOptionalParameterValueDescriptor_T *optionalParmPtr;
T_VARCHAR *VarCharStrp;
T_CLOB *ClobStrp;
T_ENCODED_VARCHAR *VarCharEncodedStrp;
T_ENCODED_CLOB *ClobEncodedStrp;
char *Encrypted_Datap;
char *Decrypted_Datap;
int EncryptedDataLen;
int DecryptedDataLen;
int RtnLen;
char *keyMgmtp;

42 IBM i: SQL programming

char Qc3_Any_CSP_Flag = Qc3_Any_CSP;
Qc3_Format_ALGD0200_T *ALGD0200;
T_key_descriptor0200 *KeyDesc0200;
Qus_EC_t ERRCODE;

memset(&ERRCODE, '\x00', sizeof(Qus_EC_t));

ERRCODE.Bytes_Provided = sizeof(Qus_EC_t);

if (*funccode == 8) // create time

{
fieldCreatedOrAltered(argv);
return;
}

// allocate space and initialize space for Algorithm Description Format

ALGD0200 = (Qc3_Format_ALGD0200_T *)malloc(sizeof(Qc3_Format_ALGD0200_T));
memset(ALGD0200, '\x00', sizeof(Qc3_Format_ALGD0200_T));
ALGD0200->Block_Cipher_Alg = Qc3_AES;
ALGD0200->Block_Length = 16;
ALGD0200->Mode = Qc3_ECB;
ALGD0200->Pad_Option = Qc3_Pad_Char;

// allocate space and initialize space for Key Description Format

KeyDesc0200 =
(T_key_descriptor0200 *)malloc(sizeof(T_key_descriptor0200));
memset(KeyDesc0200, '\x00', sizeof(T_key_descriptor0200));
KeyDesc0200->desc.Key_Type = Qc3_AES ;
KeyDesc0200->desc.Key_String_Len = 16;
KeyDesc0200->desc.Key_Format = Qc3_Bin_String;

sqlfpParameterDescription_T *decodedDataType = argv[3];

sqlfpParameterDescription_T *encodedDataType = argv[5];

if (*funccode == 0) // encode
{
// Address the data and get the actual length of the data.
switch(decodedDataType->sqlfpSqlType)
{
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
{
// varchar data is passed with a 2 byte length followed
// by the data
VarCharStrp = argv[4];
DecryptedDataLen = VarCharStrp->len;
Decrypted_Datap = VarCharStrp->data;
break;
}
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
{
// CLOB data is passed with a 4 byte length followed
// by the data
ClobStrp = argv[4];
DecryptedDataLen = ClobStrp->len;
Decrypted_Datap = ClobStrp->data;
break;
}
default:// must be fixed Length
{
// for fixed length, only the data is passed, get the
// length of the data from the data type parameter
DecryptedDataLen = decodedDataType->sqlfpByteLength;
Decrypted_Datap = argv[4];
break;
}
}
// Determine if the encoded data type is varchar or CLOB based on
// the optional parameter information that was saved at create time.
if (optionalParms->type_indicator == '0') // encoded data is varchar
{
VarCharEncodedStrp = argv[6];
Encrypted_Datap = VarCharEncodedStrp->data;
keyMgmtp = VarCharEncodedStrp->keyManagementData;
}
else // encoded data is CLOB
{
ClobEncodedStrp = argv[6];
Encrypted_Datap = ClobEncodedStrp->data;
keyMgmtp = ClobEncodedStrp->keyManagementData;
}
if (DecryptedDataLen >0) // have some data to encrypt.

SQL programming 43
 {
// get the encrypt key
KeyMgmt('E', keyMgmtp, KeyDesc0200->key);
// Set the number of bytes available for encrypting. Subtracting
// off the bytes used for "key management".
EncryptedDataLen = encodedDataType->sqlfpByteLength - KEY_MGMT_SIZE;
// Encrypt the data
Qc3EncryptData(Decrypted_Datap,
&DecryptedDataLen,
Qc3_Data,
(char *)ALGD0200,
Qc3_Alg_Block_Cipher,
(char *)KeyDesc0200,
Qc3_Key_Parms,
&Qc3_Any_CSP_Flag,
" ",
Encrypted_Datap,
&EncryptedDataLen,
&RtnLen,
&ERRCODE);
RtnLen += KEY_MGMT_SIZE; // add in the Key Area size
}
else // length is 0
RtnLen = 0;
// store the length (number of bytes that database needs to write)
// in either the 2 or 4 byte length field based on the encrypted
// data type
if (optionalParms->type_indicator == '0')
VarCharEncodedStrp->len = RtnLen;
else
ClobEncodedStrp->len = RtnLen;
}
else if (*funccode == 4) // decode
{
// Determine if the encoded data type is varchar or CLOB based on the
// optional parameter information that was saved at create time. Set
// pointers to the key management data, the user encrypted data, and
// the length of the data.
if (optionalParms->type_indicator == '0') // varchar
{
VarCharEncodedStrp = argv[6];
keyMgmtp = VarCharEncodedStrp->keyManagementData;
Encrypted_Datap = VarCharEncodedStrp->data;
EncryptedDataLen = VarCharEncodedStrp->len;
}
else // CLOB
{
ClobEncodedStrp = argv[6];
keyMgmtp = ClobEncodedStrp->keyManagementData;
Encrypted_Datap = ClobEncodedStrp->data;
EncryptedDataLen = ClobEncodedStrp->len;
}
// Set the number of bytes to decrypt. Subtract
// off the bytes used for "key management".
EncryptedDataLen -= KEY_MGMT_SIZE;
if (EncryptedDataLen > 0) // have data to decrypt
{
// Set the pointer to where the decrypted data should
// be placed.
switch (decodedDataType->sqlfpSqlType)
{
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
{
VarCharStrp = argv[4];
Decrypted_Datap = VarCharStrp->data;
break;
}
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
{
ClobStrp = argv[4];
Decrypted_Datap = ClobStrp->data;
break;
}
default: // must be fixed Length
{
Decrypted_Datap = argv[4];
break;
}
}
// get the decrypt key

44 IBM i: SQL programming

 KeyMgmt('D', keyMgmtp, KeyDesc0200->key);
// get the maximum number of bytes available for the
// decode space
DecryptedDataLen = decodedDataType->sqlfpByteLength;
// decrtype the data
Qc3DecryptData(Encrypted_Datap,
&EncryptedDataLen,
(char *)ALGD0200,
Qc3_Alg_Block_Cipher,
(char *)KeyDesc0200,
Qc3_Key_Parms,
&Qc3_Any_CSP_Flag,
" ",
Decrypted_Datap,
&DecryptedDataLen,
&RtnLen,
&ERRCODE);
}
else // 0 length data
RtnLen = 0;

// tell the database manager how many characters of data are being returned
switch (decodedDataType->sqlfpSqlType)
{
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
VarCharStrp->len = RtnLen;
break;
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
ClobStrp->len = RtnLen;
break;
default:
// must be fixed Length and the full number of characters must be
// returned
break;
}
}
else // unsupported option -- error
memcpy(sqlstate, "38003",5);

if (ERRCODE.Bytes_Available > 0) // Something failed on encrypt/decrypt

{
// set an error and return the exception id
memcpy(sqlstate, "38004",5);
msgtext->sqlfpMessageTextLength = 7;
memcpy(msgtext->sqlfpMessageTextData, ERRCODE.Exception_Id, 7);
}
// free allocated storage
free(KeyDesc0200);
free(ALGD0200);
}

static void fieldCreatedOrAltered(void *argv[])

{
char *sqlstate = argv[7];
sqlfpMessageText_T *msgtext = argv[8];
char *errortext1="Unsupported type in fieldproc.";

// Note that while optional parameters are not supported on input into
// this fieldproc, it will set information into the structure for
// usage by encode/decode operations. The length of this
// structure must be at least 8 bytes long, so the length is not
// reset.
sqlfpFieldProcedureParameterList_T *inputOptionalParms = argv[2];
T_optional *outputOptionalParms = argv[2];

sqlfpParameterDescription_T *decodedDataType = argv[3];

sqlfpParameterDescription_T *encodedDataType = argv[5];

if (inputOptionalParms->sqlfpNumberOfOptionalParms != 0)
{
// this fieldproc does not handle input optional parameters
memcpy(sqlstate,"38001",5);
return;
}

switch(decodedDataType->sqlfpSqlType)
{
case SQL_TYP_CHAR: /* Fixed char */
case SQL_TYP_NCHAR:

SQL programming 45
 // set the encode data type to VarChar
encodedDataType->sqlfpSqlType = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode
operations.
// Indicate that the encode type is varchar.
outputOptionalParms->type_indicator = '0';
break;
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
/* set the data type to BLOB */
encodedDataType->sqlfpSqlType = SQL_TYP_VARCHAR;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode
operations.
// Indicate that the encode type is varchar.
outputOptionalParms->type_indicator = '0';
break;

case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
/* set the data type to BLOB */
encodedDataType->sqlfpSqlType = SQL_TYP_BLOB;
// This example shows how the fieldproc pgm can modify the optional parm data area to
// store "constant" information to be used by the fieldproc on encode and decode
operations.
// Indicate that the encode type is BLOB.
outputOptionalParms->type_indicator = '1';
break;
default:
/* this field proc does not handle any other data types */
memcpy(sqlstate,"38002",5);
memcpy(msgtext->sqlfpMessageTextData,
errortext1, sizeof(errortext1));
msgtext->sqlfpMessageTextLength=sizeof(errortext1);
return;
}

// finish setting the rest of encoded data type values

// the null-ness of the encoded and decoded type must match

if (decodedDataType->sqlfpSqlType % 2 == 1)
++encodedDataType->sqlfpSqlType; // set to null capable

// Determine the result length by adding one byte for the pad character counter and
// rounding the length up to a multiple of 15-- the AES encryption alogrithm
// will return the encrypted data in a multiple of 15.
// This example also shows how additional data can be stored by the fieldproc
// program in the encrypted data. An additional 16 bytes are added for use by
// the fieldproc program.
// Note that this fieldproc does not check for exceeding the maximum length for
// the data type. There may also be other conditions that are not handled by
// this sample fieldproc program.
encodedDataType->sqlfpLength =
(((decodedDataType->sqlfpLength + 16) /16) * 16) + KEY_MGMT_SIZE;
encodedDataType->sqlfpByteLength = encodedDataType->sqlfpLength;
// result is *HEX CCSID
encodedDataType->sqlfpCcsid = 65535;

if (decodedDataType->sqlfpSqlType == SQL_TYP_CHAR ||
decodedDataType->sqlfpSqlType == SQL_TYP_NCHAR) // fixed length character
{
// need to set the allocated length for fixed length since the default value
// must fit in the allocated portion of varchar. Note that if the varchar or
// CLOB had a default value of something other than the empty string, the
// allocated length must be set appropriately but this fieldproc does not
// handle this situtation.
encodedDataType->sqlfpAllocatedLength = encodedDataType->sqlfpLength;
}
}

// This is a trivial key management idea and is used to demonstrate how additional
// information may be stored in the encoded data which is written to the table and
// be used to communicate between the encode and decode operations.
static void KeyMgmt(char type, char *keyMgmt, char *keyData)
{
if (type == 'E') // encoding, set the current key
{
memcpy((char *)keyMgmt, "KEYTYPE2 ", KEY_MGMT_SIZE);
memcpy(keyData, "0123456789ABCDEG", 16);
}
else // decoding, determine which key to use

46 IBM i: SQL programming

 if (memcmp(keyMgmt, "KEYTYPE1 ", KEY_MGMT_SIZE) == 0)
memcpy(keyData, "0123456789ABCDEF", 16);
else
if (memcmp(keyMgmt, "KEYTYPE2 ", KEY_MGMT_SIZE) == 0)
memcpy(keyData, "0123456789ABCDEG", 16);
}

General guidelines for writing field procedures

The following considerations must be considered when writing field procedures:
• Must be an ILE *PGM object. *SRVPGMs, OPM *PGMs, and JAVA objects are not supported.
• Authority to the field procedure *PGM object is checked when the field procedure is added to the table.
Authority checking is not done when the field procedure is invoked.
– Create the field procedure program so that it runs under the user profile of the user who created it. In
this way, users who do not have the same level of authority to the program will not encounter errors.
– Create the program with USRPRF(*OWNER) and *EXCLUDE public authority. Do not grant authorities
to the field procedure program to USER(*PUBLIC). Avoid having the field procedure program altered
or replaced by other users.
• No SQL is allowed in a field procedure.
• The field procedure will not be called if the data to be encoded or decoded is the null value.
• On an encode operation, packed decimal and zoned decimal values will be converted to the preferred
sign prior to calling the user field procedure program.
• The field procedure must be deterministic. For SQE, caching of results will occur based on the QAQQINI
FIELDPROC_ENCODED_COMPARISON.
• The field procedure must be parallel capable and capable of running in a multi-threaded environment.
For RPG, this means the THREAD control specification keyword must be specified. For COBOL, this
means the THREAD(SERIALIZE) process option must be specified.
• Must be capable of running in both a fenced and non-fenced environment.
• The program cannot be created with ACTGRP(*NEW). If the program is created with ACTGRP(*CALLER),
the program will run in the default activation group.
• Field procedure programs are expected to be short running. It is recommended that the field procedure
program avoid commitment control and native database operations.
• Create the program in the physical file's library.
• If an error occurs or is detected in the field procedure program, the field procedure program should set
the SQLSTATE and message text parameters. If the SQLSTATE parameter is not set to indicate an error,
database assumes that the field procedure ran successfully. This might cause the user data to end up in
an inconsistent state.
Warning: Field procedures are a productive way both to provide application functions and to manage
information. However, field procedure programs could provide the ability for someone with devious
intentions to create a "Trojan horse"1 on your system. This is why it is important to restrict who has the
authority to alter a table. If you are managing object authority carefully, the typical user will not have
sufficient authority to add a field procedure program.

Index considerations
Indexes may be recovered at IPL time based on the RECOVER parameter of CRTPF, CRTLF, CHGPF,
or CHGLF commands. Indexes that are based on a column that has a field procedure have special
considerations.
Use of PASE(QSH) and JAVA within field procedures must be avoided if the index keys are built over
expressions that contain columns with field procedures or the sparse index criteria references a column

1 In history, the Trojan horse was a large hollow wooden horse that was filled with Greek soldiers. After the
horse was introduced within the walls of Troy, the soldiers climbed out of the horse and fought the Trojans.
In the computer world, a program that hides destructive functions is often called a Trojan horse.

SQL programming 47
 with an associated field procedure. If use of PASE or JAVA is required, consider changing indexes to
RECOVER(*NO) so that they are not recovered during the IPL process and recovered during an open
operation instead.
The following restrictions apply to keys for both SQL indexes and DDS keyed files.
• If the column has a field procedure, the index key must be a column. No expressions (derivations) are
allowed. This includes DDS keywords like SST and CONCAT.
• Sort sequence cannot be applied to the column.
• If the field procedure column is part of a foreign key, the corresponding parent key column must use the
same field procedure.
• The WHERE clause of the SQL Create Index or the Select/Omit criteria of a DDS logical file cannot
reference a column that has a field procedure.
See the SQL Reference for more details on indexes and field procedures.

Thread considerations
A field procedure runs in the same job as the operation that initiated the field procedure. However, the
field procedure may or may not run in a different system thread (fenced or not fenced) which are separate
from the thread from the initiating request.
Because the field procedure runs in the same job, it shares much of the same environment as
the initiating request. However, because it may run under a separate thread, the following threads
considerations apply:
• Field procedures do not inherit any program adopted authority that may have been active at the time
the request was initiated. Field procedure authority comes from the authority associated with the field
procedure program or from the authority of the user running.
• The field procedure cannot perform any operation that is blocked from being run in a secondary thread.
• The field procedure program must be created such that it either runs under a named activation group or
in the activation group of its caller (ACTGRP parameter). Programs that specify *CALLER will run in the
default activation group.

Debug considerations
There are some things to keep in mind when debugging field procedures.
Since field procedures can run in a secondary thread, it is recommended that debugging should be done
using STRSRVJOB or the graphical debugger.
For natively run field procedures, the database manager uses the job default wait time. If the field
procedure does not return within that specified time, an error is returned. This default wait time may
need to be increased when debugging field procedures. For example, to change the default wait time to 5
minutes: CHGJOB DFTWAIT(300)

Guidelines for writing field procedures that mask data

Field procedures can be used to mask data for certain users or environments when data is decoded. Field
procedures that mask data must be coded to handle special situations to ensure data is not corrupted.
The following special situations must be handled by a field procedure that masks data:
• Field-decoding
– Masking must only be performed for field-decoding. It must not be performed for field-encoding. If
masking was performed for field-encoding, the masked data would be stored in the table and the
actual value would be lost.
– In some cases, system code needs to copy data internally (the data is not being returned to
the user in these cases). For example, in some cases, RGZPFM, ALTER TABLE, and CRTDUPOBJ
must copy data internally. Likewise, data passed internally to triggers must not be masked. During
these operations, when the data is read, field-decoding will occur and when the data is written,

48 IBM i: SQL programming

 field-encoding will occur. If masking is performed in these cases during field-decoding, the mask data
will then be written and the actual data will be lost.
To prevent corruption, the ninth parameter to the field procedure indicates whether this is a system
operation where masking must not be performed. It is critical that the field procedure be written to
check this parameter during field-decoding and if the parameter indicates that masking must not be
performed, the field procedure must not mask regardless of the user or environment.
• Field-encoding
– For native update and insert operations, the field procedure must be able to identify when masked
data is being passed to the field procedure and take special actions. For example, a field procedure
might be written to mask a credit card number column. That same user may be authorized to read
and update the table through an RPG application that performs READ and UPDATE operations. When
the READ is performed, the credit card number is masked to prevent the user from seeing it, but
when the user performs the UPDATE, the masked data will be passed back to database on the
UPDATE operation and the field procedure will be called to encode the data. If the field procedure
does not recognize that the value being passed is masked, the masked data would be encoded and
stored in the table and the original value in the row would be corrupted with an encoded masked
data.
To prevent corruption, the field procedure must recognize on field-encoding that the data is masked.
Instead of encoding the data, the field procedure must return a warning SQLSTATE value of ’09501’ in
the seventh parameter.
- For an UPDATE operation, ’09501’ indicates to Db2 that the current value for the column should be
used.
- For an INSERT operation, ’09501’ indicates to Db2 that the default value should be used for the
associated column value.
Query Considerations: There are several considerations that apply to queries that reference a column of
a table that has a field procedure that masks data:
• Depending on how the optimizer implements a query, the same query may return different rows and
values for different users or environments. This will occur in cases where optimizer must decode the
data in order to perform comparisons or evaluate expressions in a query. If masking is performed for
one user but not for another user, the result of the decode operation will be very different, so the
resulting rows and values can also be quite different for the two users.
For example, assume that a field procedure returns (decodes) data for user profile MAIN without
masking and returns (decodes) data for user profile QUSER with masking. An application contains the
following query:

SELECT * FROM orders WHERE cardnum = '112233'

By default, the optimizer will try to implement the search condition (logically) as follows:

WHERE cardnum = FieldProc ENCODE('112233')

This is the best performing implementation since it allows Db2 to compare the encoded version of the
constant ’112233’ with the encoded version of the CARDNUM values that are stored in the orders table.
Since the optimizer did not decode the data to perform the comparison, the query will return the same
rows for the MAIN and QUSER user profiles. The only difference will be that QUSER will see masked
values in the result rows for the CARDNUM column.
The implementation of queries that reference a field procedure column can be controlled by
the QAQQINI FIELDPROC_ENCODED_COMPARISON option. The default value for this option is
*ALLOW_EQUAL. This option enables the optimizer to implement the comparison using the encoded
values.

SQL programming 49
 In the previous example, if the FIELDPROC_ENCODED_COMPARISON option was changed to *NONE,
the query would return different rows for the two users. When the value is *NONE, an equal comparison
will be implemented internally by Db2 as follows:

WHERE FieldProc DECODE(cardnum)='112233'

In this case, Db2 has to decode the CARDNUM values for every row in the table to compare against
the original constant '112233'. This means that the comparison for the MAIN user profile will compare
the decoded and unmasked card number values (112233, 332211, etc) to ’112233’. The MAIN user
profile will find the orders associated with the specified card number (112233). However, the query
will not return any rows for the QUSER user profile. That is because the comparison for QUSER will be
comparing the masked value of the card numbers (****33, ****11, etc) with the constant ’112233’.
For more information on how the QAQQINI FIELDPROC_ENCODED_COMPARISON option affects field
procedures see the Database Performance and Query Optimization topic in the Information Center.
• REFRESH of a materialized query table is affected by the QAQQINI
FIELDPROC_ENCODED_COMPARISON option. If the materialized query table references a column with
a field procedure that masks, it is imperative that the REFRESH of the MQT be issued by a user that is
allowed to see unmasked data. Otherwise, the results in the MQT will be incorrect for all users.
• CREATE TABLE LIKE, CREATE TABLE AS, DECLARE GLOBAL TEMPORARY TABLE LIKE, or DECLARE
GLOBAL TEMPORARY TABLE AS are affected by the QAQQINI FIELDPROC_ENCODED_COMPARISON
option. If the statements are issued by a user that is not allowed to see unmasked data, the resulting
table will contain masked data.
• OPNQRYF and Query/400® are not affected by the QAQQINI FIELDPROC_ENCODED_COMPARISON
option. The optimizer always processes by decoding values (similar to a
FIELDPROC_ENCODED_COMPARISON option of *NONE).
• Select/omit DDS-created logical files are also not affected by the QAQQINI
FIELDPROC_ENCODED_COMPARISON option. The logical file is processed by decoding values (similar
to a FIELDPROC_ENCODED_COMPARISON option of *NONE).
Best Practices: There are two QAQQINI options that are strongly recommended for use if you have field
procedures that mask data:
FIELDPROC_ENCODED_COMPARISON
The default option for FIELDPROC_ENCODED_COMPARISON is *ALLOW_EQUAL which works very well
for field procedures that do not mask data. If field procedures are used that do mask data, however,
*NONE is the most secure and recommended option.
CACHE_RESULTS
The default option for CACHE_RESULTS is *SYSTEM. In many cases, this option works well. However,
if field procedures that mask data are used, you should specify *JOB for CACHE_RESULTS.
Since these two options can affect the behavior of field procedures that mask data, it is also important to
ensure that only authorized users be allowed to specify new or different QAQQINI options:
• CHGQRYA command
Verify that only authorized users can execute the CHGQRYA command. By default only users with job
control (*JOBCTL) special authority or have the QIBM_DB_SQLADM function usage are authorized to the
CHGQRYA command.
• QUSRSYS/QAQQINI file
Verify that only authorized users can create the QUSRSYS/QAQQINI file or update it if it already exists.
By default *PUBLIC has *USE authority to QUSRSYS which is not be enough authority to create a new
QUSRSYS.QAQQINI file.

50 IBM i: SQL programming

Example field procedure program that masks data
Add field procedure FP1 to column C1. The field procedure FP1 expects an additional parameter which it
uses to indicate a version level for the field procedure.

ALTER TABLE TESTTAB ALTER C1 SET FIELDPROC FP1(1)

#include "string.h"
#include <QSYSINC/H/SQLFP>
void reverse(char *in, char *out, long length);
main(int argc, void *argv[])
{
short *funccode = argv[1];
sqlfpFieldProcedureParameterList_T *optionalParms = argv[2];
char *sqlstate = argv[7];
sqlfpMessageText_T *msgtext = argv[8];
int version;
sqlfpOptionalParameterValueDescriptor_T *optionalParmPtr;
sqlfpInformation_T *info = argv[9];
int masked;
if (optionalParms->sqlfpNumberOfOptionalParms != 1)
{
memcpy(sqlstate,"38001",5);
return;
}
optionalParmPtr = (void *)&(optionalParms->sqlfpParmList);
version = *((int *)&optionalParmPtr->sqlfpParmData);
/*******************************************************************/
/* CREATE CALL */
/*******************************************************************/
if (*funccode == 8) /* create time */
{
sqlfpParameterDescription_T *inDataType = argv[3];
sqlfpParameterDescription_T *outDataType = argv[5];
if (inDataType->sqlfpSqlType !=452 &&
inDataType->sqlfpSqlType !=453 ) /* only support fixed length char */
{
memcpy(sqlstate,"38002",5);
return;
}
/* an example of how an optional parm could be used */
/* In this case it is used to add version support if it is */
/* expected that the fieldproc program may support multiple */
/* versions */
if (version != 1) /* only support version 1 at this time */
{
memcpy(sqlstate,"38003",5);
return;
}

/* do something here to determine the result data type */

/* ..... */
/* in this example input and output types are exactly the same */
/* so just copy */
memcpy(outDataType, inDataType, sizeof(sqlfpParameterDescription_T));
}
/*******************************************************************/
/* ENCODE (WRITE) CALL */
/*******************************************************************/
else if (*funccode == 0) /* encode */
{
sqlfpParameterDescription_T *decodedDataType = argv[3];
char *decodedData = argv[4];
char *encodedData = argv[6];
/* Detect that the value passed on encode is masked. */
/* Return 09501 to tell DB that: */
/* - The field should not be updated for an update operation */
/* - The default value should be used for an insert operation*/
if ( memcmp(decodedData, "XXXXXXXXXXXX", 12) == 0 )
{
memcpy(sqlstate,"09501",5);
}
else
{
reverse(decodedData, encodedData, decodedDataType->sqlfpByteLength);
}
}
/*******************************************************************/
/* DECODE (READ) CALL */
/*******************************************************************/

SQL programming 51
 else if (*funccode == 4) /* decode */
{
char *decodedData = argv[4];
sqlfpParameterDescription_T *encodedDataType = argv[5];
char *encodedData = argv[6];
/* The 9th paramter indicates that the column must not be */
/* masked. For example, during ALTER TABLE or RGZPFM. */
if ( info->sqlfpNoMask == '1' )
{
reverse(encodedData, decodedData, encodedDataType->sqlfpByteLength);
return;
}
else
{
reverse(encodedData, decodedData, encodedDataType->sqlfpByteLength);
/* Mask the data when appropriate */
/* Assume mask is set to 0 when it should not be masked */
/* and 1 when it shoulbe be masked */
if (masked == 1)
{
memcpy(decodedData, "XXXXXXXXXXXX",
encodedDataType->sqlfpByteLength);
}
}
return;
}
/*******************************************************************/
/* ERROR- UNSUPPORTED OPTION */
/*******************************************************************/
else /* unsupported option -- error */
memcpy(sqlstate, "38003",5);
}
/*******************************************************************/
/* REVERSE */
/*******************************************************************/
void reverse(char *in, char *out, long length)
{
int i;
for (i=0;i<length; ++i) {
out[length - (i+1)] = in[i];
}
}

Creating descriptive labels using the LABEL statement

Sometimes a text description is useful for an object (such as a table or an index) or useful as column
text or column headings. You can create a more descriptive label for these names by using the LABEL
statement.
These labels can be seen in the SQL catalog in the LABEL column.
The LABEL statement looks like this:

LABEL ON
TABLE CORPDATA.DEPARTMENT IS 'Department Structure Table';

LABEL ON
COLUMN CORPDATA.DEPARTMENT.ADMRDEPT IS 'Reports to Dept.';

After these statements are run, the table named DEPARTMENT displays the text description as
Department Structure Table and the column named ADMRDEPT displays the heading Reports to Dept.
The label for an object or a column cannot be more than 50 bytes and the label for a column heading
cannot be more than 60 bytes (blanks included). Here are the examples of LABEL statements for column
headings:
This LABEL statement provides column heading 1 and column heading 2:

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.EMPNO IS
'Employee Number';

52 IBM i: SQL programming

 This LABEL statement provides three levels of column headings for the SALARY column:

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.SALARY IS
'Yearly Salary (in dollars)';

This LABEL statement removes the column heading for SALARY:

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.SALARY IS '';

This LABEL statement provides a DBCS column heading with two levels specified:

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.SALARY IS
'<AABBCCDD> <EEFFGG>';

This LABEL statement provides the column text for the EDLEVEL column:

*...+....1....+....2....+....3....+....4....+....5....+....6..*
LABEL ON COLUMN CORPDATA.EMPLOYEE.EDLEVEL TEXT IS
'Number of years of formal education';

Related reference
LABEL

Describing an SQL object using COMMENT

After you create an SQL object, such as a table or view, you can provide object information for future
reference using the COMMENT statement.
The information can be the purpose of the object, who uses it, and anything unusual or special about it.
You can also include similar information about each column of a table or view. A comment is especially
useful if your names do not clearly indicate the contents of the columns or objects. In that case, use a
comment to describe the specific contents of the column or objects. Usually, your comment must not be
more than 2000 characters. If the object already contains a comment, the old comment is replaced by the
new one.
An example of using COMMENT follows:

COMMENT ON TABLE CORPDATA.EMPLOYEE IS

'Employee table. Each row in this table represents
one employee of the company.';

Getting comments after running a COMMENT statement

After you run a COMMENT statement for a table, your comments are stored in the LONG_COMMENT
column of SYSTABLES. Comments for the other objects are stored in the LONG_COMMENT column of the
appropriate catalog table. The following example gets the comments that are added by the COMMENT
statement in the previous example:

SELECT LONG_COMMENT
FROM CORPDATA.SYSTABLES
WHERE NAME = 'EMPLOYEE';

Related reference
COMMENT

SQL programming 53
Changing a table definition
You can change the definition of a table by adding a new column, changing an existing column definition
such as its length or default value, dropping an existing column, adding or removing constraints, changing
partitioning for the table, or altering one of many other options.
To change a table definition, you can use the ALTER TABLE statement. You can add, change, or drop
columns and add or remove constraints all with one ALTER TABLE statement. You can also include other
options to define or change partitioning, add a materialized query table definition, or change your media
preference for the table to use solid state disk storage media.
A second option for changing a table definition is to use the CREATE OR REPLACE TABLE statement.
With CREATE OR REPLACE TABLE, all the changes to the table definition are made with the standard
CREATE TABLE statement. If the table already exists, any changes between the new table definition and
the existing table definition are recognized and applied to the existing table. For example, based on the
content of the CREATE OR REPLACE TABLE statement, several new columns could be added, the data
type of a column could be changed, and a primary key could be defined.
Related reference
ALTER TABLE

Adding a column
When you add a new column to a table, the column is initialized with its default value for all existing rows.
If NOT NULL is specified, a default value must also be specified.
You can add a column to a table using the ADD COLUMN clause of the SQL ALTER TABLE statement.
The altered table may consist of up to 8000 columns. The sum of the byte counts of the columns must not
be greater than 32766 or, if a VARCHAR or VARGRAPHIC column is specified, 32740. If a LOB column is
specified, the sum of record data byte counts of the columns must not be greater than 15 728 640.
Related reference
ALTER TABLE

Changing a column
You can change a column definition in a table using the ALTER COLUMN clause of the ALTER TABLE
statement.
When you change the data type of an existing column, the old and new attributes must be compatible. You
can always change a character, graphic, or binary column from fixed length to varying length or LOB; or
from varying length or LOB to fixed length.
When you convert to a data type with a longer length, data is padded with the appropriate pad character.
When you convert to a data type with a shorter length, data might be lost because of truncation. An
inquiry message prompts you to confirm the request.
If you have a column that does not allow the null value and you want to change it to now allow the null
value, use the DROP NOT NULL clause. If you have a column that allows the null value and you want to
prevent the use of null values, use the SET NOT NULL clause. If any of the existing values in that column
are the null value, the ALTER TABLE will not be performed and an SQLCODE of -190 will result.
Related reference
Allowable conversions of data types
When you change the data type of an existing column, the old and new attributes must be compatible.
Related information
ALTER TABLE

54 IBM i: SQL programming

Allowable conversions of data types
When you change the data type of an existing column, the old and new attributes must be compatible.

Table 8. Allowable conversions

From data type To data type
Decimal Numeric
Decimal Bigint, Integer, Smallint
Decimal Decfloat
Decimal Float
Numeric Decimal
Numeric Bigint, Integer, Smallint
Numeric Decfloat
Numeric Float
Bigint, Integer, Smallint Decimal
Bigint, Integer, Smallint Numeric
Bigint, Integer, Smallint Decfloat
Bigint, Integer, Smallint Float
Float Decimal
Float Numeric
Float Bigint, Integer, Smallint
Float Decfloat
Character DBCS-open
Character UCS-2 or UTF-16 graphic
DBCS-open Character
DBCS-open UCS-2 or UTF-16 graphic
DBCS-either Character
DBCS-either DBCS-open
DBCS-either UCS-2 or UTF-16 graphic
DBCS-only DBCS-open
DBCS-only DBCS graphic
DBCS-only UCS-2 or UTF-16 graphic
DBCS graphic UCS-2 or UTF-16 graphic
UCS-2 or UTF-16 graphic Character
UCS-2 or UTF-16 graphic DBCS-open
UCS-2 or UTF-16 graphic DBCS graphic
distinct type source type
source type distinct type

SQL programming 55
 When you change an existing column, only the attributes that you specify are changed. All other attributes
remain unchanged. For example, you have a table with the following table definition:

CREATE TABLE EX1 (COL1 CHAR(10) DEFAULT 'COL1',

COL2 VARCHAR(20) ALLOCATE(10) CCSID 937,
COL3 VARGRAPHIC(20) ALLOCATE(10)
NOT NULL WITH DEFAULT);

After you run the following ALTER TABLE statement, COL2 still has an allocated length of 10 and CCSID
937, and COL3 still has an allocated length of 10.

ALTER TABLE EX1 ALTER COLUMN COL2 SET DATA TYPE VARCHAR(30)
ALTER COLUMN COL3 DROP NOT NULL;

Related reference
Changing a column
You can change a column definition in a table using the ALTER COLUMN clause of the ALTER TABLE
statement.

Deleting a column
You can delete a column using the DROP COLUMN clause of the ALTER TABLE statement.
Dropping a column deletes that column from the table definition. If CASCADE is specified, any views,
indexes, and constraints dependent on that column will also be dropped. If RESTRICT is specified, and
any views, indexes, or constraints are dependent on the column, the column will not be dropped and
SQLCODE of -196 will be issued.

ALTER TABLE DEPT

DROP COLUMN NUMDEPT;

Related reference
ALTER TABLE

Order of operations for the ALTER TABLE statement

Operations for the ALTER TABLE statement are performed in a defined order.
The ALTER TABLE statement is performed as this set of steps shows:
1. Drop constraints
2. Drop materialized query table
3. Drop partition
4. Drop partitioning
5. Drop columns for which the RESTRICT option was specified
6. Alter column definitions (this includes adding columns and dropping columns for which the CASCADE
option was specified)
7. Alter partition
8. Add or alter materialized query table
9. Add partitioning to a table
10. Add constraints
Within each of these steps, the order in which you specify the clauses is the order in which they are
performed, with one exception. If any columns are being dropped, that operation is logically done before
any column definitions are added or altered, in case record length is increased as a result of the ALTER
TABLE statement.

56 IBM i: SQL programming

Using CREATE OR REPLACE TABLE
The OR REPLACE option on the CREATE TABLE statement can be used to change an existing table
definition.
Using CREATE OR REPLACE TABLE lets you consolidate the master definition of a table into one
statement. You do not need to maintain the source for the original CREATE TABLE statement plus a
complex list of ALTER TABLE statements needed to recreate the most current version of a table. This
CREATE TABLE statement can be executed to deploy the current definition of the table either as a new
table or to replace a prior version of the table.
There are options to either keep the existing data in the table or to clear the data from the table during the
replace. The default is to keep all data. If you elect to clear all the data, your new table definition does not
need to be compatible with the original version. In all cases, other objects that depend on the table, such
as referential constraints, triggers, and views, must remain satisfied or the replace will fail.
Suppose your original table was this basic INVENTORY table.

CREATE TABLE INVENTORY

(PARTNO SMALLINT NOT NULL,
DESCR VARCHAR(24),
QONHAND INT,
PRIMARY KEY(PARTNO));

Perhaps over time, you have updated the column names to be more descriptive, changed the DESCR
column to be a longer Unicode column, and added a timestamp column for when the row was last
updated. The following statement reflects all of these changes and can be executed against any prior
version of the table, as long as the column names can be matched to the prior column names and the data
types are compatible.

CREATE OR REPLACE TABLE INVENTORY

(PART_NUMBER FOR PARTNO SMALLINT NOT NULL,
DESCRIPTION FOR DESCR VARGRAPHIC(500) CCSID 1200,
QUANTITY_ON_HAND FOR QONHAND INT,
LAST_MODIFIED FOR MODIFIED TIMESTAMP
NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
PRIMARY KEY(PARTNO));

Partitioned tables can be modified using CREATE OR REPLACE TABLE. The following example
demonstrates splitting a single partition into multiple partitions.
Suppose your original table was defined to have 3 partitions

CREATE TABLE PARTITIONED

(KEYFLD BIGINT,
DATAFLD VARCHAR(200))
PARTITION BY RANGE (KEYFLD)
(PARTITION FIRST STARTING 0 ENDING 100,
PARTITION SECOND STARTING 100 EXCLUSIVE ENDING 200,
PARTITION THIRD STARTING 200 EXCLUSIVE ENDING 300);

To break the second partition into 3 pieces, modify the original CREATE TABLE statement to redefine the
partitions.

CREATE OR REPLACE TABLE PARTITIONED

(KEYFLD BIGINT,
DATAFLD VARCHAR(200))
PARTITION BY RANGE (KEYFLD)
(PARTITION FIRST STARTING 0 ENDING 100,
PARTITION SECOND STARTING 100 EXCLUSIVE ENDING 150,
PARTITION SPLIT1 STARTING 151 EXCLUSIVE ENDING 175,
PARTITION SPLIT2 STARTING 176 EXCLUSIVE ENDING 200,
PARTITION THIRD STARTING 200 EXCLUSIVE ENDING 300);

Now the table will have 5 partitions with the data spread among them according to the new definition.
This example uses the default of ON REPLACE PRESERVE ALL ROWS. That means that all data for all rows
is guaranteed to be kept. If data from an existing partition doesn't fit in any new partition, the statement
fails. To remove a partition and the data from that partition, omit the partition definition from the CREATE

SQL programming 57
 OR REPLACE TABLE statement and use ON REPLACE PRESERVE ROWS. This will preserve all the data that
can be assigned to the remaining partitions and discard any that no longer has a defined partition.

Creating and using ALIAS names

When you refer to an existing table or view, or to a physical file that consists of multiple members, you can
avoid using file overrides by creating an alias. To create an alias, use the CREATE ALIAS statement.
You can create an alias for:
• A table or view
• A member of a table
A table alias defines a name for the file, including the specific member name. You can use this alias name
in an SQL statement in the same way that a table name is used. Unlike overrides, alias names are objects
that exist until they are dropped.
For example, if there is a multiple member file MYLIB.MYFILE with members MBR1 and MBR2, an alias
can be created for the second member so that SQL can easily refer to it.

CREATE ALIAS MYLIB.MYMBR2_ALIAS FOR MYLIB.MYFILE (MBR2);

When alias MYLIB.MYMBR2_ALIAS is specified on the following insert statement, the values are inserted
into member MBR2 in MYLIB.MYFILE:

INSERT INTO MYLIB.MYMBR2_ALIAS VALUES('ABC', 6);

Alias names can also be specified on DDL statements. Assume that MYLIB.MYALIAS is an alias for table
MYLIB.MYTABLE. The following DROP statement drops table MYLIB.MYTABLE:

DROP TABLE MYLIB.MYALIAS;

If you really want to drop the alias name instead, specify the ALIAS keyword on the drop statement:

DROP ALIAS MYLIB.MYALIAS;

Related reference
CREATE ALIAS

Creating and using views

A view can be used to access data in one or more tables or views. You create a view by using a SELECT
statement.
For example, create a view that selects only the family name and the department of all the managers:

CREATE VIEW CORPDATA.EMP_MANAGERS FOR SYSTEM NAME EMPMANAGER AS

SELECT LASTNAME, WORKDEPT FROM CORPDATA.EMPLOYEE
WHERE JOB = 'MANAGER';

Since the view name, EMP_MANAGERS, is too long for a system object name, the FOR SYSTEM NAME
clause can be used to provide the system name. Without adding this clause, a name like EMP_M00001
will be generated for the system object.
After you create the view, you can use it in SQL statements just like a table. You can also change
the data in the base table through the view. The following SELECT statement displays the contents of
EMP_MANAGERS:

SELECT *
FROM CORPDATA.EMP_MANAGERS;

The results follow.

58 IBM i: SQL programming

LASTNAME WORKDEPT
THOMPSON B01
KWAN C01
GEYER E01
STERN D11
PULASKI D21
HENDERSON E11
SPENSER E21

If the select list contains elements other than columns such as expressions, functions, constants, or
special registers, and the AS clause was not used to name the columns, a column list must be specified
for the view. In the following example, the columns of the view are LASTNAME and YEARSOFSERVICE.

CREATE VIEW CORPDATA.EMP_YEARSOFSERVICE

(LASTNAME, YEARSOFSERVICE) AS
SELECT LASTNAME, YEAR (CURRENT DATE - HIREDATE)
FROM CORPDATA.EMPLOYEE;

Because the results of querying this view change as the current year changes, they are not included here.
You can also define the previous view by using the AS clause in the select list to name the columns in the
view. For example:

CREATE VIEW CORPDATA.EMP_YEARSOFSERVICE AS

SELECT LASTNAME,
YEARS (CURRENT_DATE - HIREDATE) AS YEARSOFSERVICE
FROM CORPDATA.EMPLOYEE;

Using the UNION keyword, you can combine two or more subselects to form a single view. For example:

CREATE VIEW D11_EMPS_PROJECTS AS

(SELECT EMPNO
FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11'
UNION
SELECT EMPNO
FROM CORPDATA.EMPPROJACT
WHERE PROJNO = 'MA2112' OR
PROJNO = 'MA2113' OR
PROJNO = 'AD3111');

This view has the following data.

Table 9. Results of creating a view as UNION

EMPNO
000060
000150
000160
000170
000180
000190
000200
000210

SQL programming 59
 Table 9. Results of creating a view as UNION (continued)
EMPNO
000220
000230
000240
200170
200220

Views are created with the sort sequence in effect at the time the CREATE VIEW statement is run. The
sort sequence applies to all character, or UCS-2 or UTF-16 graphic comparisons in the CREATE VIEW
statement subselect.
You can also create views using the WITH CHECK OPTION clause to specify the level of checking when
data is inserted or updated through the view.
Related concepts
Retrieving data using the SELECT statement
The SELECT statement tailors your query to gather data. You can use the SELECT statement to retrieve a
specific row or retrieve data in a specific way.
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Related reference
Using the UNION keyword to combine subselects
Using the UNION keyword, you can combine two or more subselects to form a fullselect.
CREATE VIEW

WITH CHECK OPTION on a view

WITH CHECK OPTION is an optional clause on the CREATE VIEW statement. It specifies the level of
checking when data is inserted or updated through a view.
If WITH CHECK OPTION is specified, every row that is inserted or updated through the view must conform
to the definition of the view. The option cannot be specified if the view is read-only. The definition of the
view must not include a subquery.
If the view is created without a WITH CHECK OPTION clause, insert and update operations that are
performed on the view are not checked for conformance to the view definition. Some checking might still
occur if the view is directly or indirectly dependent on another view that includes WITH CHECK OPTION.
Because the definition of the view is not used, rows that do not conform to the definition of the view might
be inserted or updated through the view. This means that the rows cannot be selected again through the
view.
Related reference
CREATE VIEW

WITH CASCADED CHECK OPTION

The WITH CASCADED CHECK OPTION clause specifies that every row that is inserted or updated through
a view must conform to the definition of the view.
In addition, the search conditions of all dependent views are checked when a row is inserted or updated.
If a row does not conform to the definition of the view, that row cannot be retrieved through the view.

60 IBM i: SQL programming

For example, consider the following updatable view:

CREATE VIEW V1 AS SELECT COL1

FROM T1 WHERE COL1 > 10;

Because no WITH CHECK OPTION is specified, the following INSERT statement is successful even though
the value being inserted does not meet the search condition of the view.

INSERT INTO V1 VALUES (5);

Create another view over V1, specifying the WITH CASCADED CHECK OPTION clause:

CREATE VIEW V2 AS SELECT COL1

FROM V1 WITH CASCADED CHECK OPTION;

The following INSERT statement fails because it produces a row that does not conform to the definition of
V2:

INSERT INTO V2 VALUES (5);

Consider one more view created over V2:

CREATE VIEW V3 AS SELECT COL1

FROM V2 WHERE COL1 < 100;

The following INSERT statement fails only because V3 is dependent on V2, and V2 has a WITH
CASCADED CHECK OPTION.

INSERT INTO V3 VALUES (5);

However, the following INSERT statement is successful because it conforms to the definition of V2.
Because V3 does not have a WITH CASCADED CHECK OPTION, it does not matter that the statement
does not conform to the definition of V3.

INSERT INTO V3 VALUES (200);

WITH LOCAL CHECK OPTION

The WITH LOCAL CHECK OPTION clause is identical to the WITH CASCADED CHECK OPTION clause
except that you can update a row so that it can no longer be retrieved through the view. This can happen
only when the view is directly or indirectly dependent on a view that was defined with no WITH CHECK
OPTION clause.
For example, consider the same updatable view used in the previous example:

CREATE VIEW V1 AS SELECT COL1

FROM T1 WHERE COL1 > 10;

Create second view over V1, this time specifying WITH LOCAL CHECK OPTION:

CREATE VIEW V2 AS SELECT COL1

FROM V1 WITH LOCAL CHECK OPTION;

The same INSERT statement that failed in the previous CASCADED CHECK OPTION example succeeds
now because V2 does not have any search conditions, and the search conditions of V1 do not need to be
checked since V1 does not specify a check option.

INSERT INTO V2 VALUES (5);

Consider one more view created over V2:

CREATE VIEW V3 AS SELECT COL1

FROM V2 WHERE COL1 < 100;

SQL programming 61
 The following INSERT is successful again because the search condition on V1 is not checked due to
the WITH LOCAL CHECK OPTION on V2, versus the WITH CASCADED CHECK OPTION in the previous
example.

INSERT INTO V3 VALUES (5);

The difference between LOCAL and CASCADED CHECK OPTION lies in how many of the dependent views'
search conditions are checked when a row is inserted or updated.
• WITH LOCAL CHECK OPTION specifies that the search conditions of only those dependent views that
have the WITH LOCAL CHECK OPTION or WITH CASCADED CHECK OPTION are checked when a row is
inserted or updated.
• WITH CASCADED CHECK OPTION specifies that the search conditions of all dependent views are
checked when a row is inserted or updated.

Example: Cascaded check option

This example shows how the check option is enforced on a number of dependent views that are defined
with or without a check option.
Use the following table and views:

CREATE TABLE T1 (COL1 CHAR(10));

CREATE VIEW V1 AS SELECT COL1

FROM T1 WHERE COL1 LIKE 'A%';

CREATE VIEW V2 AS SELECT COL1

FROM V1 WHERE COL1 LIKE '%Z'
WITH LOCAL CHECK OPTION;

CREATE VIEW V3 AS SELECT COL1

FROM V2 WHERE COL1 LIKE 'AB%';

CREATE VIEW V4 AS SELECT COL1

FROM V3 WHERE COL1 LIKE '%YZ'
WITH CASCADED CHECK OPTION;

CREATE VIEW V5 AS SELECT COL1

FROM V4 WHERE COL1 LIKE 'ABC%';

Different search conditions are going to be checked depending on which view is being operated on with an
INSERT or UPDATE statement.
• If V1 is operated on, no conditions are checked because V1 does not have a WITH CHECK OPTION
specified.
• If V2 is operated on,
– COL1 must end in the letter Z, but it doesn't need to start with the letter A. This is because the check
option is LOCAL, and view V1 does not have a check option specified.
• If V3 is operated on,
– COL1 must end in the letter Z, but it does not need to start with the letter A. V3 does not have a check
option specified, so its own search condition must not be met. However, the search condition for V2
must be checked because V3 is defined on V2, and V2 has a check option.
• If V4 is operated on,
– COL1 must start with 'AB' and must end with 'YZ'. Because V4 has the WITH CASCADED CHECK
OPTION specified, every search condition for every view on which V4 is dependent must be checked.
• If V5 is operated on,
– COL1 must start with 'AB', but not necessarily 'ABC'. This is because V5 does not specify a check
option, so its own search condition does not need to be checked. However, because V5 is defined
on V4, and V4 had a cascaded check option, every search condition for V4, V3, V2, and V1 must be
checked. That is, COL1 must start with 'AB' and end with 'YZ'.

62 IBM i: SQL programming

 If V5 were created WITH LOCAL CHECK OPTION, operating on V5 means that COL1 must start with 'ABC'
and end with 'YZ'. The LOCAL CHECK OPTION adds the additional requirement that the third character
must be a 'C'.

Creating indexes
You can use indexes to sort and select data. In addition, indexes help the system retrieve data faster for
better query performance.
Use the CREATE INDEX statement to create indexes. The following example creates an index over the
column LASTNAME in the CORPDATA.EMPLOYEE table:

CREATE INDEX CORPDATA.INX1 ON CORPDATA.EMPLOYEE (LASTNAME);

You can also create an index that does not exactly match the data for a column in a table. For example,
you can create an index that uses the uppercase version of an employee name:

CREATE INDEX CORPDATA.INX2 ON CORPDATA.EMPLOYEE (UPPER(LASTNAME));

Most expressions allowed by SQL can be used in the definition of the key columns.
You can create any number of indexes. However, because the indexes are maintained by the system, a
large number of indexes can adversely affect performance. One type of index, the encoded vector index
(EVI), allows for faster scans that can be more easily processed in parallel.
If an index is created that has exactly the same attributes as an existing index, the new index shares the
existing indexes' binary tree. Otherwise, another binary tree is created. If the attributes of the new index
are exactly the same as another index, except that the new index has fewer columns, another binary tree
is still created. It is still created because the extra columns prevent the index from being used by cursors
or UPDATE statements that update those extra columns.
Indexes are created with the sort sequence in effect at the time the CREATE INDEX statement is run. The
sort sequence applies to all SBCS character fields, or UCS-2 or UTF-16 graphic fields of the index.
Related concepts
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Creating an index strategy
Related reference
CREATE INDEX

Creating and using global variables

You can use global variables to assign specific variable values for a session.
Use the CREATE VARIABLE statement to create a global variable. The following example creates a global
variable that defines a user class.

CREATE VARIABLE USER_CLASS INT DEFAULT (CLASS_FUNC(USER));

This variable will have its initial value set based on the result of invoking a function called CLASS_FUNC.
This function is assumed to assign a class value such as administrator or clerk based on the USER special
register value.
A global variable is instantiated for a session the first time it is referenced. Once it is set, it will maintain
its value unless explicitly changed within the session.

SQL programming 63
 A global variable can be used in a query to determine what results will be returned. In the following
example, a list of all employees from department A00 are listed. Only a session that has a global variable
with a USER_CLASS value of 1 will see the salaries for these employees.

SELECT EMPNO, LASTNAME, CASE WHEN USER_CLASS = 1 THEN SALARY ELSE NULL END
FROM EMPLOYEE
WHERE WORKDEPT = 'A00';

Global variables can be used in any context where an expression is allowed. Unlike a host variable, a
global variable can be used in a CREATE VIEW statement.

Replacing existing objects

You can replace an existing object using a CREATE statement rather than always needing to drop the
object first.
For many SQL objects, you can optionally replace an existing object when using the CREATE SQL
statement. The existing object is effectively dropped before the new object is created. The following
SQL statements have that option:
• CREATE ALIAS
• CREATE FUNCTION
• CREATE MASK
• CREATE PERMISSION
• CREATE PROCEDURE
• CREATE SEQUENCE
• CREATE TABLE
• CREATE TRIGGER
• CREATE VARIABLE
• CREATE VIEW
When the replace option is used for any of these statements, many object specific attributes are retained,
including the object authorizations, auditing configuration, and object description. The object definition is
replaced by the new definition.

Example: Create or replace sequence

To create a sequence called MYSEQUENCE, or replace a sequence of that name if it exists, use the
following SQL statement

CREATE OR REPLACE SEQUENCE MYSEQUENCE AS BIGINT;

The sequence will be created if it does not already exist. If it does exist, the privileges from the existing
sequence will be transferred to the new sequence.

Dropping a database object

The DROP statement deletes an object. Depending on the action requested, any objects that are directly
or indirectly dependent on that object might also be deleted or might prevent the drop from happening.
For example, if you drop a table, any aliases, constraints, triggers, views, or indexes associated with that
table are also dropped. Whenever an object is deleted, its description is deleted from the catalog.
For example, to drop table EMPLOYEE, issue the following statement:

DROP TABLE EMPLOYEE RESTRICT;

Related reference
DROP

64 IBM i: SQL programming

DROP IF EXISTS
Each of the DROP statements supports an IF EXISTS clause. This allows the drop of an object to complete
successfully, with no error being issued, if the object does not exist.
For example, to drop the EMPLOYEE table from WORKLIB, issue the following statement:

DROP TABLE IF EXISTS WORKLIB.EMPLOYEE;

Restrict on drop
To prevent an unintended drop of a table, function, or procedure, the RESTRICT ON DROP attribute can be
added to the definition of the object.

Table, physical file, or source physical file

RESTRICT ON DROP can be added to tables, physical files, or source physical files. It is enforced for all
methods of deleting a table, physical file, or source physical file, including library level operations. It does
not prevent using the OR REPLACE option to redefine the table. Since it is a file level attribute, it does not
apply to requests to delete rows or remove a member.
When creating a table, it can be established like this:

CREATE TABLE TEST_TABLE (ID INT, NAME VARCHAR(50), ADDRESS VARCHAR(50)) WITH RESTRICT ON DROP;

It can be added to an existing table, database physical file, or source physical file by using the ALTER
TABLE statement:

ALTER TABLE TEST_TABLE ADD RESTRICT ON DROP;

Once the attribute has been added to the table, the table cannot be dropped or deleted, regardless of the
user's authority. When RESTRICT ON DROP prevents a database file from being deleted, SQLCODE -672
(SQLSTATE 55035) is returned by the SQL statement, or a CPF32BF escape message is returned to the CL
command.
Before the table or file can be dropped, the attribute must be removed using an ALTER TABLE statement.

ALTER TABLE TEST_TABLE DROP RESTRICT ON DROP;

RESTRICT ON DROP cannot be added to a table in QTEMP.

The CRTDUPOBJ CL command propagates the RESTRICT ON DROP attribute to the new file. A database
file created any other way will not have the RESTRICT ON DROP attribute unless it is explicitly specified
on the CREATE TABLE statement.
The MOVOBJ, RNMOBJ, and RSTOBJ CL commands maintain the RESTRICT ON DROP attribute.

Functions and procedures

The RESTRICT ON DROP attribute can be added to both SQL routines and external routines.
SQL routines
For SQL routines, RESTRICT ON DROP prevents dropping the routine and deleting the associated program
or service program object.

CREATE FUNCTION SQUARE (P1 INT)

RETURNS INT
WITH RESTRICT ON DROP
RETURN P1 * P1;

It can be added to an existing SQL routine with an ALTER statement.

SQL programming 65
 ALTER PROCEDURE MYLIB.PROC1
WITH RESTRICT ON DROP;

Once the attribute is associated with an SQL routine, the routine cannot be dropped and the associated
program or service program cannot be deleted, regardless of the user's authority.
Before the procedure or function can be dropped, the attribute must be removed using an ALTER
FUNCTION or ALTER PROCEDURE statement.

ALTER PROCEDURE MYLIB.PROC1

WITHOUT RESTRICT ON DROP;

When RESTRICT ON DROP prevents an SQL routine from being dropped or deleted, SQLCODE -672
(SQLSTATE 55035) is returned by the SQL statement, or a CPF32BF escape message is returned to the CL
command.
The MOVOBJ, RNMOBJ, and RSTOBJ CL commands maintain the RESTRICT ON DROP attribute. The
CRTDUPOBJ CL command propagates the RESTRICT ON DROP attribute to the new program or service
program.
External routines
For external routines, the RESTRICT ON DROP attribute can apply to the routine definition or it can
include the program or service program object as well.
To prevent a procedure from being dropped, use the following statement.

CREATE PROCEDURE MYLIB.PROC2 (P1 INT)

EXTERNAL NAME APPLIB.PGM1
WITH RESTRICT ON DROP;

To add the attribute to an existing function and prevent both the function and the service program it uses
from being dropped or deleted, use the following ALTER FUNCTION statement.

ALTER FUNCTION MYLIB.FUNC3

WITH RESTRICT ON DROP INCLUDING EXTERNAL PROGRAM;

Before a procedure or function can be dropped, the attribute must be removed. Use the ALTER
PROCEDURE statement to remove it from a procedure.

ALTER PROCEDURE MYLIB.PROC2

WITHOUT RESTRICT ON DROP;

When the INCLUDING EXTERNAL PROGRAM clause is used, it is only enforced when the routine's
definition is successfully recorded in the program or service program object. Message SQL7909 is issued
when an external routine is created or altered and the *PGM or *SRVPGM could not be modified.
The RESTRICT ON DROP will prevent the routine from being dropped, but the INCLUDING EXTERNAL
PROGRAM will not be enforced.
When RESTRICT ON DROP prevents an external routine from being dropped, SQLCODE -672 (SQLSTATE
55035) is returned by the SQL statement. When RESTRICT ON DROP prevents an external routine's
program from being deleted, a CPF32BF escape message is returned to the CL command. If multiple
routines are registered for the same program or service program, the INCLUDING EXTERNAL PROGRAM
clause must be removed from all the routines before the object can be dropped.
The MOVOBJ, RNMOBJ, and RSTOBJ CL commands maintain the RESTRICT ON DROP attribute. The
CRTDUPOBJ CL command propagates the RESTRICT ON DROP attribute to the new program or service
program.

66 IBM i: SQL programming

Catalogs in database design
A catalog is automatically created when you create a schema. There is also a system-wide catalog that is
always in the QSYS2 library.
When an SQL object is created in a schema, information is added to both the system catalog tables
and the schema's catalog tables. When an SQL object is created in a library, only the QSYS2 catalog is
updated. A table created with DECLARE GLOBAL TEMPORARY TABLE is not added to a catalog.
As the following examples show, you can display catalog information. You cannot insert, delete, or update
catalog information. You must have SELECT privileges on the catalog views to run the following examples.
Related reference
Db2 for i catalog views

Getting catalog information about a table

The SYSTABLES view contains a row for each table and view in the SQL schema. The SYSTABLES view
provides information such as the object type (table or view), the object name, the owner of the object, and
the schema where the object exists.
The following example statement displays information for the CORPDATA.DEPARTMENT table:

SELECT *
FROM CORPDATA.SYSTABLES
WHERE TABLE_NAME = 'DEPARTMENT';

Getting catalog information about a column

The SYSCOLUMNS view contains a row for each column of a table and view in the schema.
The following example statement displays all the column names in the CORPDATA.DEPARTMENT table:

SELECT *
FROM CORPDATA.SYSCOLUMNS
WHERE TABLE_NAME = 'DEPARTMENT';

The result of the previous example statement is a row of information for each column in the table.
For specific information about each column, specify a select-statement like this:

SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE, LENGTH, HAS_DEFAULT

FROM CORPDATA.SYSCOLUMNS
WHERE TABLE_NAME = 'DEPARTMENT';

In addition to the column name for each column, the select-statement shows:
• The name of the table that contains the column
• The data type of the column
• The length attribute of the column
• If the column allows default values
The result looks like this.

COLUMN_NAME TABLE_NAME DATA_TYPE LENGTH HAS_DEFAULT

DEPTNO DEPARTMENT CHAR 3 N
DEPTNAME DEPARTMENT VARCHAR 29 N
MGRNO DEPARTMENT CHAR 6 Y
ADMRDEPT DEPARTMENT CHAR 3 N

SQL programming 67
Data manipulation language
Data manipulation language (DML) describes the portion of SQL that manipulates or controls data.
Related concepts
Types of SQL statements
There are several basic types of SQL statements. They are listed here according to their functions.

Retrieving data using the SELECT statement

The SELECT statement tailors your query to gather data. You can use the SELECT statement to retrieve a
specific row or retrieve data in a specific way.
If SQL is unable to find a row that satisfies the search condition, an SQLCODE of +100 is returned.
If SQL finds errors while running your select-statement, a negative SQLCODE is returned. If SQL finds
more host variables than results, +326 is returned.
Related reference
Creating a table using AS
You can create a table from the result of a SELECT statement. To create this type of table, use the CREATE
TABLE AS statement.
Creating and using views
A view can be used to access data in one or more tables or views. You create a view by using a SELECT
statement.

Basic SELECT statement

The basic format and syntax of the SELECT statement consists of several required and optional clauses.
You can write SQL statements on one line or on many lines. For SQL statements in precompiled programs,
the rules for the continuation of lines are the same as those of the host language (the language the
program is written in). A SELECT statement can also be used by a cursor in a program. Finally, a SELECT
statement can be prepared in a dynamic application.
Notes:
1. The SQL statements described in this section can be run on SQL tables and views, and database
physical and logical files.
2. Character strings specified in an SQL statement (such as those used with WHERE or VALUES clauses)
are case-sensitive; that is, uppercase characters must be entered in uppercase and lowercase
characters must be entered in lowercase.

WHERE ADMRDEPT='a00' (does not return a result)

WHERE ADMRDEPT='A00' (returns a valid department number)

Comparisons might not be case sensitive if a shared-weight sort sequence is used where uppercase
and lowercase characters are treated as the same characters.
A SELECT statement can include the following:
1. The name of each column you want to include in the result.
2. The name of the table or view that contains the data.
3. A search condition to identify the rows that contain the information you want.
4. The name of each column used to group your data.
5. A search condition that uniquely identifies a group that contains the information you want.
6. The order of the results.
7. An offset into the result set to enable skipping a number of rows.
8. The number of rows to return.

68 IBM i: SQL programming

A SELECT statement looks like this:

SELECT column names

FROM table or view name
WHERE search condition
GROUP BY column names
HAVING search condition
ORDER BY column-name
OFFSET number of rows
FETCH FIRST n ROWS ONLY

The SELECT and FROM clauses must be specified. The other clauses are optional.
With the SELECT clause, you specify the name of each column you want to retrieve. For example:

SELECT EMPNO, LASTNAME, WORKDEPT

You can specify that only one column be retrieved, or as many as 8000 columns. The value of each
column you name is retrieved in the order specified in the SELECT clause.
If you want to retrieve all columns that do not have the hidden attribute (in the same order as they appear
in the table's definition), use an asterisk (*) instead of naming the columns:

SELECT *

The FROM clause specifies the table that you want to select data from. You can select columns from more
than one table. When issuing a SELECT, you must specify a FROM clause. Issue the following statement:

SELECT *
FROM EMPLOYEE;

The result is all of the columns and rows from the table EMPLOYEE.
The SELECT list can also contain expressions, including constants, special registers, and scalar fullselects.
An AS clause can be used to give the resulting column a name. For example, issue the following
statement:

SELECT LASTNAME, SALARY * .05 AS RAISE

FROM EMPLOYEE
WHERE EMPNO = '200140';

The result of this statement follows.

Table 10. Results for query

LASTNAME RAISE
NATZ 1421

Specifying a search condition using the WHERE clause

The WHERE clause specifies a search condition that identifies the row or rows that you want to retrieve,
update, or delete.
The number of rows you process with an SQL statement then depends on the number of rows that satisfy
the WHERE clause search condition. A search condition consists of one or more predicates. A predicate
specifies a test that you want SQL to apply to a specified row or rows of a table.
In the following example, WORKDEPT = 'C01' is a predicate, WORKDEPT and 'C01' are expressions, and
the equal sign (=) is a comparison operator. Note that character values are enclosed in apostrophes
('); numeric values are not. This applies to all constant values wherever they are coded within an SQL

SQL programming 69
 statement. For example, to specify that you are interested in the rows where the department number is
C01, issue the following statement:

... WHERE WORKDEPT = 'C01'

In this case, the search condition consists of one predicate: WORKDEPT = 'C01'.
To further illustrate WHERE, put it into a SELECT statement. Assume that each department listed in the
CORPDATA.DEPARTMENT table has a unique department number. You want to retrieve the department
name and manager number from the CORPDATA.DEPARTMENT table for department C01. Issue the
following statement:

SELECT DEPTNAME, MGRNO

FROM CORPDATA.DEPARTMENT
WHERE DEPTNO = 'C01';

The result of this statement is one row.

Table 11. Result table

DEPTNAME MGRNO
INFORMATION CENTER 000030

If the search condition contains character, or UCS-2 or UTF-16 graphic column predicates, the sort
sequence that is in effect when the query is run is applied to those predicates. If a sort sequence is not
being used, character constants must be specified in uppercase or lowercase to match the column or
expression they are being compared to.
Related concepts
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Related reference
Defining complex search conditions
In addition to the basic comparison predicates, such as = and >, a search condition can contain any of
these predicates: BETWEEN, IN, EXISTS, IS NULL, and LIKE.
Multiple search conditions within a WHERE clause
You can qualify your request further by coding a search condition that includes several predicates.

Expressions in the WHERE clause

An expression in a WHERE clause names or specifies something that you want to compare to something
else.
The expressions you specify can be:
• A column name names a column. For example:

... WHERE EMPNO = '000200'

EMPNO names a column that is defined as a 6-byte character value.

• An expression identifies two values that are added (+), subtracted (-), multiplied (*), divided (/), have
exponentiation (**), or concatenated (CONCAT or ||) to result in a value. The most common operands of
an expression are:
– A constant
– A column
– A host variable
– A global variable
– A function

70 IBM i: SQL programming

 – A special register
– A scalar fullselect
– Another expression
For example:

... WHERE INTEGER(PRENDATE - PRSTDATE) > 100

When the order of evaluation is not specified by parentheses, the expression is evaluated in the
following order:
1. Prefix operators
2. Exponentiation
3. Multiplication, division, and concatenation
4. Addition and subtraction
Operators on the same precedence level are applied from left to right.
• A constant specifies a literal value for the expression. For example:

... WHERE 40000 < SALARY

SALARY names a column that is defined as a 9-digit packed decimal value (DECIMAL(9,2)). It is
compared to the numeric constant 40000.
• A host variable identifies a variable in an application program. For example:

... WHERE EMPNO = :EMP

• A special register identifies a special value defined by the database manager. For example:

... WHERE LASTNAME = USER

• The NULL value specifies the condition of having an unknown value.

... WHERE DUE_DATE IS NULL

A search condition can specify many predicates separated by AND and OR. No matter how complex
the search condition, it supplies a TRUE or FALSE value when evaluated against a row. There is also an
unknown truth value, which is effectively false. That is, if the value of a row is null, this null value is not
returned as a result of a search because it is not less than, equal to, or greater than the value specified in
the search condition.
To fully understand the WHERE clause, you need to know the order SQL evaluates search conditions and
predicates, and compares the values of expressions. This topic is discussed in the Db2 for i SQL reference
topic collection.
Related concepts
Using subqueries
You can use subqueries in a search condition as another way to select data. Subqueries can be used
anywhere an expression can be used.
Related reference
Defining complex search conditions
In addition to the basic comparison predicates, such as = and >, a search condition can contain any of
these predicates: BETWEEN, IN, EXISTS, IS NULL, and LIKE.
Expressions

SQL programming 71
 Comparison operators
SQL supports several comparison operators.

Comparison operator Description

<> or ¬= or != Not equal to
= Equal to
< Less than
> Greater than
<= or ¬> or !> Less than or equal to (or not greater than)
> = or ¬< or !< Greater than or equal to (or not less than)

NOT keyword
You can precede a predicate with the NOT keyword to specify that you want the opposite of the
predicate's value (that is, TRUE if the predicate is FALSE).
NOT applies only to the predicate it precedes, not to all predicates in the WHERE clause. For example, to
indicate that you are interested in all employees except those working in the department C01, you can
say:

... WHERE NOT WORKDEPT = 'C01'

which is equivalent to:

... WHERE WORKDEPT <> 'C01'

GROUP BY clause
The GROUP BY clause allows you to find the characteristics of groups of rows rather than individual rows.
When you specify a GROUP BY clause, SQL divides the selected rows into groups such that the rows of
each group have matching values in one or more columns or expressions. Next, SQL processes each group
to produce a single-row result for the group. You can specify one or more columns or expressions in the
GROUP BY clause to group the rows. The items you specify in the SELECT statement are properties of
each group of rows, not properties of individual rows in a table or view.
Without a GROUP BY clause, the application of SQL aggregate functions returns one row. When GROUP BY
is used, the function is applied to each group, thereby returning as many rows as there are groups.
For example, the CORPDATA.EMPLOYEE table has several sets of rows, and each set consists of rows
describing members of a specific department. To find the average salary of people in each department,
you can issue:

SELECT WORKDEPT, DECIMAL (AVG(SALARY),5,0)

FROM CORPDATA.EMPLOYEE
GROUP BY WORKDEPT;

The result is several rows, one for each department.

WORKDEPT AVG-SALARY
A00 40850
B01 41250
C01 29722
D11 25147
D21 25668

72 IBM i: SQL programming

WORKDEPT AVG-SALARY
E01 40175
E11 21020
E21 24086

Notes:
1. Grouping the rows does not mean ordering them. Grouping puts each selected row in a group, which
SQL then processes to derive characteristics of the group. Ordering the rows puts all the rows in
the results table in ascending or descending collating sequence. Depending on the implementation
selected by the database manager, the resulting groups might appear to be ordered.
2. If there are null values in the column you specify in the GROUP BY clause, a single-row result is
produced for the data in the rows with null values.
3. If the grouping occurs over character, or UCS-2 or UTF-16 graphic columns, the sort sequence in effect
when the query is run is applied to the grouping.
When you use GROUP BY, you list the columns or expressions you want SQL to use to group the rows. For
example, suppose that you want a list of the number of people working on each major project described in
the CORPDATA.PROJECT table. You can issue:

SELECT SUM(PRSTAFF), MAJPROJ

FROM CORPDATA.PROJECT
GROUP BY MAJPROJ;

The result is a list of the company's current major projects and the number of people working on each
project.

SUM(PRSTAFF) MAJPROJ
6 AD3100
5 AD3110
10 MA2100
8 MA2110
5 OP1000
4 OP2000
3 OP2010
32.5 ?

You can also specify that you want the rows grouped by more than one column or expression. For
example, you can issue a select statement to find the average salary for men and women in each
department, using the CORPDATA.EMPLOYEE table. To do this, you can issue:

SELECT WORKDEPT, SEX, DECIMAL(AVG(SALARY),5,0) AS AVG_WAGES

FROM CORPDATA.EMPLOYEE
GROUP BY WORKDEPT, SEX;

The result follows.

WORKDEPT SEX AVG_WAGES

A00 F 49625
A00 M 35000
B01 M 41250

SQL programming 73
 WORKDEPT SEX AVG_WAGES
C01 F 29722
D11 F 25817
D11 M 24764
D21 F 26933
D21 M 24720
E01 M 40175
E11 F 22810
E11 M 16545
E21 F 25370
E21 M 23830

Because you did not include a WHERE clause in this example, SQL examines and processes all rows in
the CORPDATA.EMPLOYEE table. The rows are grouped first by department number and next (within each
department) by sex before SQL derives the average SALARY value for each group.
Related concepts
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Related reference
ORDER BY clause
The ORDER BY clause specifies the particular order in which you want selected rows returned. The order
is sorted by ascending or descending collating sequence of a column's or an expression's value.

HAVING clause
The HAVING clause specifies a search condition for the groups selected by the GROUP BY clause.
The HAVING clause says that you want only those groups that satisfy the condition in that clause.
Therefore, the search condition you specify in the HAVING clause must test properties of each group
rather than properties of individual rows in the group.
The HAVING clause follows the GROUP BY clause and can contain the same kind of search condition as
you can specify in a WHERE clause. In addition, you can specify aggregate functions in a HAVING clause.
For example, suppose that you want to retrieve the average salary of women in each department. To do
this, use the AVG aggregate function and group the resulting rows by WORKDEPT and specify a WHERE
clause of SEX = 'F'.
To specify that you want this data only when all the female employees in the selected department have
an education level equal to or greater than 16 (a college graduate), use the HAVING clause. The HAVING
clause tests a property of the group. In this case, the test is on MIN(EDLEVEL), which is a group property:

SELECT WORKDEPT, DECIMAL(AVG(SALARY),5,0) AS AVG_WAGES, MIN(EDLEVEL) AS MIN_EDUC

FROM CORPDATA.EMPLOYEE
WHERE SEX='F'
GROUP BY WORKDEPT
HAVING MIN(EDLEVEL)>=16;

The result follows.

WORKDEPT AVG_WAGES MIN_EDUC

A00 49625 18

74 IBM i: SQL programming

WORKDEPT AVG_WAGES MIN_EDUC
C01 29722 16
D11 25817 17

You can use multiple predicates in a HAVING clause by connecting them with AND and OR, and you can
use NOT for any predicate of a search condition.
Note: If you intend to update a column or delete a row, you cannot include a GROUP BY or HAVING clause
in the SELECT statement within a DECLARE CURSOR statement. These clauses make it a read-only cursor.
Predicates with arguments that are not aggregate functions can be coded in either WHERE or HAVING
clauses. It is typically more efficient to code the selection criteria in the WHERE clause because it is
handled earlier in the query processing. The HAVING selection is performed in post processing of the
result table.
If the search condition contains predicates involving character, or UCS-2 or UTF-16 graphic columns, the
sort sequence in effect when the query is run is applied to those predicates.
Related concepts
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Related reference
Using a cursor
When SQL runs a SELECT statement, the resulting rows comprise the result table. A cursor provides a way
to access a result table.

ORDER BY clause
The ORDER BY clause specifies the particular order in which you want selected rows returned. The order
is sorted by ascending or descending collating sequence of a column's or an expression's value.
For example, to retrieve the names and department numbers of female employees listed in the
alphanumeric order of their department numbers, you can use this select-statement:

SELECT LASTNAME,WORKDEPT
FROM CORPDATA.EMPLOYEE
WHERE SEX='F'
ORDER BY WORKDEPT;

The result follows.

LASTNAME WORKDEPT
HAAS A00
HEMMINGER A00
KWAN C01
QUINTANA C01
NICHOLLS C01
NATZ C01
PIANKA D11
SCOUTTEN D11
LUTZ D11
JOHN D11

SQL programming 75
 LASTNAME WORKDEPT
PULASKI D21
JOHNSON D21
PEREZ D21
HENDERSON E11
SCHNEIDER E11
SETRIGHT E11
SCHWARTZ E11
SPRINGER E11
WONG E21

Note: Null values are ordered as the highest value.

The column specified in the ORDER BY clause does not need to be included in the SELECT clause. For
example, the following statement will return all female employees ordered with the largest salary first:

SELECT LASTNAME,FIRSTNME
FROM CORPDATA.EMPLOYEE
WHERE SEX='F'
ORDER BY SALARY DESC;

If an AS clause is specified to name a result column in the select-list, this name can be specified in the
ORDER BY clause. The name specified in the AS clause must be unique in the select-list. For example, to
retrieve the full names of employees listed in alphabetic order, you can use this select-statement:

SELECT LASTNAME CONCAT FIRSTNME AS FULLNAME

FROM CORPDATA.EMPLOYEE
ORDER BY FULLNAME;

This select-statement can optionally be written as:

SELECT LASTNAME CONCAT FIRSTNME

FROM CORPDATA.EMPLOYEE
ORDER BY LASTNAME CONCAT FIRSTNME;

Instead of naming the columns to order the results, you can use a number. For example, ORDER BY 3
specifies that you want the results ordered by the third column of the results table, as specified by the
select-list. Use a number to order the rows of the results table when the sequencing value is not a named
column.
You can also specify whether you want SQL to collate the rows in ascending (ASC) or descending (DESC)
sequence. An ascending collating sequence is the default. In the previous select-statement, SQL first
returns the row with the lowest FULLNAME expression (alphabetically and numerically), followed by rows
with higher values. To order the rows in descending collating sequence based on this name, specify:

... ORDER BY FULLNAME DESC

You can specify a secondary ordering sequence (or several levels of ordering sequences) as well as a
primary one. In the previous example, you might want the rows ordered first by department number, and
within each department, ordered by employee name. To do this, specify:

... ORDER BY WORKDEPT, FULLNAME

If character columns, or UCS-2 or UTF-16 graphic columns are used in the ORDER BY clause, ordering for
these columns is based on the sort sequence in effect when the query is run.

76 IBM i: SQL programming

Related concepts
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Related reference
GROUP BY clause
The GROUP BY clause allows you to find the characteristics of groups of rows rather than individual rows.

Static SELECT statements

For a static SELECT statement (one embedded in an SQL program), an INTO clause must be specified
before the FROM clause.
The INTO clause names the host variables (variables in your program used to contain retrieved column
values). The value of the first result column specified in the SELECT clause is put into the first host
variable named in the INTO clause; the second value is put into the second host variable, and so on.
The result table for a SELECT INTO should contain just one row. For example, each row in the
CORPDATA.EMPLOYEE table has a unique EMPNO (employee number) column. The result of a SELECT
INTO statement for this table, if the WHERE clause contains an equal comparison on the EMPNO column,
will be exactly one row (or no rows). Finding more than one row is an error, but one row is still returned.
You can control which row will be returned in this error condition by specifying the ORDER BY clause. If
you use the ORDER BY clause, the first row in the result table is returned.
If you want more than one row to be the result of a SELECT INTO statement, use a DECLARE CURSOR
statement to select the rows, followed by a FETCH statement to move the column values into host
variables one or many rows at a time.
When using the select-statement in an application program, list the column names to give your program
more data independence. There are two reasons for this:
1. When you look at the source code statement, you can easily see the one-to-one correspondence
between the column names in the SELECT clause and the host variables named in the INTO clause.
2. If a column is added to a table or view you access and you use "SELECT * ...," and you create the
program again from source, the INTO clause does not have a matching host variable named for the
new column. The extra column causes you to get a warning (not an error) in the SQLCA (SQLWARN3
will contain a "W"). When using the GET DIAGNOSTICS statement, the RETURNED_SQLSTATE item will
have a value of '01503'.
Related reference
Using a cursor
When SQL runs a SELECT statement, the resulting rows comprise the result table. A cursor provides a way
to access a result table.

Handling null values

A null value indicates the absence of a column value in a row. A null value is an unknown value; it is not
the same as zero or all blanks.
Null values can be used as a condition in the WHERE and HAVING clauses. For example, a WHERE clause
can specify a column that, for some rows, contains a null value. A basic comparison predicate using
a column that contains null values does not select a row that has a null value for the column. This is
because a null value is not less than, equal to, or greater than the value specified in the condition. The IS
NULL predicate is used to check for null values. To select the values for all rows that contain a null value
for the manager number, you can specify:

SELECT DEPTNO, DEPTNAME, ADMRDEPT

FROM CORPDATA.DEPARTMENT
WHERE MGRNO IS NULL;

The result follows.

SQL programming 77
 DEPTNO DEPTNAME ADMRDEPT
D01 DEVELOPMENT CENTER A00
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

To get the rows that do not have a null value for the manager number, you can change the WHERE clause
like this:

WHERE MGRNO IS NOT NULL

Another predicate that is useful for comparing values that can contain the NULL value is the DISTINCT
predicate. Comparing two columns using a normal equal comparison (COL1 = COL2) will be true if both
columns contain an equal non-null value. If both columns are null, the result will be false because null
is never equal to any other value, not even another null value. Using the DISTINCT predicate, null values
are considered equal. So COL1 is NOT DISTINCT from COL2 will be true if both columns contain an equal
non-null value and also when both columns are the null value.
For example, suppose that you want to select information from two tables that contain null values. The
first table T1 has a column C1 with the following values.

C1
2
1
null

The second table T2 has a column C2 with the following values.

C2
2
null

Run the following SELECT statement:

SELECT *
FROM T1, T2
WHERE C1 IS DISTINCT FROM C2;

The result follows.

C1 C2
1 2
1 -
2 -
- 2

For more information about the use of null values, see the Db2 for i SQL reference topic collection.

78 IBM i: SQL programming

Special registers in SQL statements
You can specify certain special registers in SQL statements. A special register, for example, CURRENT
DATE, contains information that can be referenced in SQL statements.
For locally run SQL statements, the special registers and their contents are shown in the following table.

Special registers Contents

The accounting string for the client connection.
CURRENT CLIENT_ACCTNG
CLIENT ACCTNG

The application name for the client connection.

CURRENT CLIENT_APPLNAME
CLIENT APPLNAME

The program ID for the client connection.

CURRENT CLIENT_PROGRAMID
CLIENT PROGRAMID

The client user ID for the client connection.

CURRENT CLIENT_USERID
CLIENT USERID

The workstation name for the client connection.

CURRENT CLIENT_WRKSTNNAME
CLIENT WRKSTNNAME

The current date.

CURRENT DATE
CURRENT_DATE

The debug mode to be used when creating or

CURRENT DEBUG MODE
altering routines.
The rounding mode to be used when working with
CURRENT DECFLOAT ROUNDING MODE
decimal floating point values.
The number of tasks the database manager should
CURRENT DEGREE
run in parallel.
The whitespace handling options to be used for
CURRENT IMPLICIT XMLPARSE OPTION
XML data when implicitly parsed without validation.
The SQL path used to resolve unqualified data type
CURRENT PATH
names, procedure names, and function names in
CURRENT_PATH
dynamically prepared SQL statements.
CURRENT FUNCTION PATH

The schema name used to qualify unqualified

CURRENT SCHEMA
database object references where applicable in
dynamically prepared SQL statements.
The name of the relational database currently
CURRENT SERVER
being used.
CURRENT_SERVER

CURRENT TEMPORAL SYSTEM_TIME The timestamp to use when querying a

system_period temporal table.
The current time.
CURRENT TIME
CURRENT_TIME

SQL programming 79
 Special registers Contents
The current date and time in timestamp format.
CURRENT TIMESTAMP
CURRENT_TIMESTAMP

A duration of time that links local time to Universal

CURRENT TIMEZONE
Time Coordinated (UTC) using the formula:
CURRENT_TIMEZONE
local time -
CURRENT TIMEZONE = UTC

It is taken from the system value QUTCOFFSET.

The primary authorization identifier of the job.
CURRENT USER
CURRENT_USER

The runtime authorization identifier (user profile) of

SESSION_USER
the job.
USER

The authorization identifier (user profile) of the

SYSTEM_USER
user connected to the database.

If a single statement contains more than one reference to any of CURRENT DATE, CURRENT TIME, or
CURRENT TIMESTAMP special registers, or the CURDATE, CURTIME, or NOW scalar functions, all values
are based on a single clock reading.
For remotely run SQL statements, the values for special registers are determined at the remote system.
When a query over a distributed table references a special register, the contents of the special register on
the system that requests the query are used. For more information about distributed tables, see the DB2
Multisystem topic collection.

Casting data types

Sometimes you need to cast or change the type of an expression to a different data type or to the same
data type with a different length, precision, or scale.
For example, if you want to compare two columns of different types, such as a user-defined type based
on a character and an integer, you can change the character to an integer or the integer to a character to
make the comparison possible. A data type that can be changed to another data type is castable from the
source data type to the target data type.
You can use cast functions or CAST specification to explicitly cast a data type to another data type. For
example, if you have a column of dates (BIRTHDATE) defined as DATE and want to cast the column data
type to CHARACTER with a fixed length of 10, enter the following:

SELECT CHAR (BIRTHDATE,USA)

FROM CORPDATA.EMPLOYEE;

You can also use the CAST specification to cast data types directly:

SELECT CAST(BIRTHDATE AS CHAR(10))

FROM CORPDATA.EMPLOYEE;

You can use the TRY_CAST specification when there is a possibility that the cast could fail. For example,
a character column might be expected to contain a valid integer value, but for some rows it does not. For
rows where the cast is not successful, TRY_CAST returns the null value rather than an error.

SELECT TRY_CAST(EMPNO AS INT)

FROM CORPDATA.EMPLOYEE;

80 IBM i: SQL programming

Related reference
Casting between data types

Date, time, and timestamp data types

Date, time, and timestamp are data types that are represented in an internal form not seen by an SQL
user.
Date, time, and timestamp can be represented by character string values and assigned to character string
variables. The database manager recognizes the following as date, time, and timestamp values:
• A value returned by the DATE, TIME, or TIMESTAMP scalar function.
• A value returned by the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register.
• A value of a character string in the ANSI/ISO standard date, time, or timestamp format, for example,
DATE '1950-01-01'.
• A character string when it is an operand of an arithmetic expression or a comparison and the other
operand is a date, time, or timestamp. For example, in the predicate:

... WHERE HIREDATE < '1950-01-01'

if HIREDATE is a date column, the character string '1950-01-01' is interpreted as a date.

• A character string variable or constant used to set a date, time, or timestamp column in either the SET
clause of an UPDATE statement, or the VALUES clause of an INSERT statement.
When the CURRENT TIMESTAMP special register or a variable with the TIMESTAMP data type is used with
a precision greater than 6, the timestamp value is a combination of the system clock and uniqueness bits.
The uniqueness bits are assigned in an ascending order. Therefore, comparison operations for timestamps
with any precision will represent an accurate order of when the timestamps were assigned.
Related reference
Data types

Specifying current date and time values

You can specify a current date, time, or timestamp in an expression by using one of these special
registers: CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP.
The value of each is based on a time-of-day clock reading obtained during the running of the statement.
Multiple references to CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP within the same SQL
statement use the same value. The following statement returns the age (in years) of each employee in the
EMPLOYEE table when the statement is run:

SELECT YEAR(CURRENT DATE - BIRTHDATE)

FROM CORPDATA.EMPLOYEE;

The CURRENT TIMEZONE special register allows a local time to be converted to Universal Time
Coordinated (UTC). For example, if you have a table named DATETIME that contains a time column type
with a name of STARTT, and you want to convert STARTT to UTC, you can use the following statement:

SELECT STARTT - CURRENT TIMEZONE

FROM DATETIME;

Date/time arithmetic
Addition and subtraction are the only arithmetic operators applicable to date, time, and timestamp values.
You can increment and decrement a date, time, or timestamp by a duration; or subtract a date from a
date, a time from a time, or a timestamp from a timestamp.
Related reference
Datetime arithmetic in SQL

SQL programming 81
 Boolean data type
The Boolean data type represents the values of true, false, and unknown.
Boolean constants TRUE, FALSE, and UNKNOWN represent the corresponding Boolean truth values. ON
and OFF are synonyms for TRUE and FALSE. UNKNOWN is identical to the NULL constant.
When performing comparisons and assignments, a Boolean value is compatible with character and
graphic strings, and numeric values that are integer or decimal floating point values. String values
representing true are 't' , 'true' , 'y', 'yes' , 'on', and '1'. False can be represented by 'f', 'false', 'n', 'no',
'off', and '0'. Any combination of uppercase and lowercase characters are recognized. Numeric values are
interpreted as false if they have a value of 0. Otherwise, they are true.
The normal comparison predicates can be used with Boolean values. A Boolean predicate of IS TRUE or
IS FALSE can be used as well. In addition, a Boolean value can be specified alone in a predicate, which is
an implicit use of the IS TRUE predicate. For comparisons, true is greater than false.
The following three predicates have the same meaning:

... WHERE SHIPPED = TRUE

... WHERE SHIPPED IS TRUE

... WHERE SHIPPED

Altering a column between any other data type and Boolean is not supported.

Row change expressions

The ROW CHANGE TIMESTAMP and ROW CHANGE TOKEN expressions can be used to determine when a
row was last changed.
To use a ROW CHANGE TIMESTAMP expression for a table, the table must be defined to have a row
change timestamp column.
The following query can find all the orders that are at least four weeks old and can list when they were last
modified:

SELECT ORDERNO, ROW CHANGE TIMESTAMP FOR ORDERS

FROM ORDERS
WHERE ORDER_DATE < CURRENT DATE - 4 WEEKS;

The ROW CHANGE TOKEN expression can be used for both tables that have a row change timestamp and
tables that do not. It represents a modification point for a row. If a table has a row change timestamp, it
is derived from the timestamp. If a table does not have a row change timestamp, it is based on an internal
modification time that is not row-based, so it is not as accurate as for a table that has a row change
timestamp.

Handling duplicate rows

When SQL evaluates a select-statement, several rows might qualify to be in the result table, depending
on the number of rows that satisfy the search condition of the select-statement. Some of the rows in the
result table might be duplicate.
You can specify that you do not want any duplicates by using the DISTINCT keyword, followed by the list
of expressions:

SELECT DISTINCT JOB, SEX

...

DISTINCT means that you want to select only the unique rows. If a selected row duplicates another row in
the result table, the duplicate row is ignored (it is not put into the result table). For example, suppose you
want a list of employee job codes. You do not need to know which employee has what job code. Because

82 IBM i: SQL programming

it is probable that several people in a department have the same job code, you can use DISTINCT to
ensure that the result table has only unique values.
The following example shows how to do this:

SELECT DISTINCT JOB

FROM CORPDATA.EMPLOYEE
WHERE WORKDEPT = 'D11';

The result is two rows.

JOB
DESIGNER
MANAGER

If you do not include DISTINCT in a SELECT clause, you might find duplicate rows in your result, because
SQL returns the JOB column's value for each row that satisfies the search condition. Null values are
treated as duplicate rows for DISTINCT.
If you include DISTINCT in a SELECT clause and you also include a shared-weight sort sequence, fewer
values might be returned. The sort sequence causes values that contain the same characters to be
weighted the same. If 'MGR', 'Mgr', and 'mgr' are all in the same table, only one of these values is
returned.
Related concepts
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.

Defining complex search conditions

In addition to the basic comparison predicates, such as = and >, a search condition can contain any of
these predicates: BETWEEN, IN, EXISTS, IS NULL, and LIKE.
A search condition can include a scalar fullselect.
For character, or UCS-2 or UTF-16 graphic column predicates, the sort sequence is applied to the
operands before evaluation of the predicates for BETWEEN, IN, EXISTS, and LIKE clauses.
You can also perform multiple search conditions.
• BETWEEN ... AND ... is used to specify a search condition that is satisfied by any value that falls on or
between two other values. For example, to find all employees who were hired in 1987, you can use this:

... WHERE HIREDATE BETWEEN '1987-01-01' AND '1987-12-31'

The BETWEEN keyword is inclusive. A more complex, but explicit, search condition that produces the
same result is:

... WHERE HIREDATE >= '1987-01-01' AND HIREDATE <= '1987-12-31'

• IN says you are interested in rows in which the value of the specified expression is among the values
you listed. For example, to find the names of all employees in departments A00, C01, and E21, you can
specify:

... WHERE WORKDEPT IN ('A00', 'C01', 'E21')

• EXISTS says you are interested in testing for the existence of certain rows. For example, to find out if
there are any employees that have a salary greater than 60000, you can specify:

EXISTS (SELECT * FROM EMPLOYEE WHERE SALARY > 60000)

SQL programming 83
 • IS NULL says that you are interested in testing for null values. For example, to find out if there are any
employees without a phone listing, you can specify:

... WHERE EMPLOYEE.PHONE IS NULL

• LIKE says you are interested in rows in which an expression is similar to the value you supply. When you
use LIKE, SQL searches for a character string similar to the one you specify. The degree of similarity is
determined by two special characters used in the string that you include in the search condition:
_
An underline character stands for any single character.
%
A percent sign stands for an unknown string of 0 or more characters. If the percent sign starts the
search string, then SQL allows 0 or more character(s) to precede the matching value in the column.
Otherwise, the search string must begin in the first position of the column.
Note: If you are operating on MIXED data, the following distinction applies: an SBCS underline character
refers to one SBCS character. No such restriction applies to the percent sign; that is, a percent sign
refers to any number of SBCS or DBCS characters. See the Db2 for i SQL reference topic collection for
more information about the LIKE predicate and MIXED data.
Use the underline character or percent sign either when you do not know or do not care about all the
characters of the column's value. For example, to find out which employees live in Minneapolis, you can
specify:

... WHERE ADDRESS LIKE '%MINNEAPOLIS%'

SQL returns any row with the string MINNEAPOLIS in the ADDRESS column, no matter where the string
occurs.
In another example, to list the towns whose names begin with 'SAN', you can specify:

... WHERE TOWN LIKE 'SAN%'

If you want to find any addresses where the street name isn't in your master street name list, you can
use an expression in the LIKE expression. In this example, the STREET column in the table is assumed
to be upper case.

... WHERE UCASE (:address_variable) NOT LIKE '%'||STREET||'%'

If you want to search for a character string that contains either the underscore or percent character,
use the ESCAPE clause to specify an escape character. For example, to see all businesses that have a
percent in their name, you can specify:

... WHERE BUSINESS_NAME LIKE '%@%%' ESCAPE '@'

The first and last percent characters in the LIKE string are interpreted as the normal LIKE percent
characters. The combination '@%' is taken as the actual percent character.
Related concepts
Using subqueries
You can use subqueries in a search condition as another way to select data. Subqueries can be used
anywhere an expression can be used.
Sort sequences and normalization in SQL
A sort sequence defines how characters in a character set relate to each other when they are compared or
ordered. Normalization allows you to compare strings that contain combining characters.
Related reference
Specifying a search condition using the WHERE clause

84 IBM i: SQL programming

The WHERE clause specifies a search condition that identifies the row or rows that you want to retrieve,
update, or delete.
Expressions in the WHERE clause
An expression in a WHERE clause names or specifies something that you want to compare to something
else.
Predicates

Special considerations for LIKE

Here are some considerations for using the LIKE predicate.
• When host variables are used in place of string constants in a search pattern, you should consider using
varying length host variables. This allows you to:
– Assign previously used string constants to host variables without any changes.
– Obtain the same selection criteria and results as if a string constant were used.
• When fixed-length host variables are used in place of string constants in a search pattern, you should
ensure that the value specified in the host variable matches the pattern previously used by the string
constants. All characters in a host variable that are not assigned a value are initialized with a blank.
For example, if you do a search using the string pattern 'ABC%' in a varying length host variable, these
are some of the values that can be returned:

'ABCD ' 'ABCDE' 'ABCxxx' 'ABC '

However, if you do a search using the search pattern 'ABC%' contained in a host variable with a fixed
length of 10, these values can be returned, assuming that the column has a length of 12:

'ABCDE ' 'ABCD ' 'ABCxxx ' 'ABC '

Note: All returned values start with 'ABC' and end with at least 6 blanks. Blanks are used because the
last 6 characters in the host variable are not assigned a specific value.
If you want to do a search using a fixed-length host variable where the last 7 characters can be
anything, search for 'ABC%%%%%%%'. These are some of the values that can be returned:

'ABCDEFGHIJ' 'ABCXXXXXXX' 'ABCDE' 'ABCDD'

Multiple search conditions within a WHERE clause

You can qualify your request further by coding a search condition that includes several predicates.
The search condition you specify can contain any of the comparison operators or the predicates
BETWEEN, DISTINCT, IN, LIKE, EXISTS, IS NULL, and IS NOT NULL.
You can combine any two predicates with AND and OR. In addition, you can use the NOT keyword to
specify that the search condition that you want is the negated value of the specified search condition. A
WHERE clause can have as many predicates as you want.
• AND says that, for a row to qualify, the row must satisfy both predicates of the search condition. For
example, to find out which employees in department D21 were hired after December 31, 1987, specify:

...
WHERE WORKDEPT = 'D21' AND HIREDATE > '1987-12-31'

• OR says that, for a row to qualify, the row can satisfy the condition set by either or both predicates of
the search condition. For example, to find out which employees are in either department C01 or D11,
you can specify :

...
WHERE WORKDEPT = 'C01' OR WORKDEPT = 'D11'

Note: You can also use IN to specify this request: WHERE WORKDEPT IN ('C01', 'D11').

SQL programming 85
 • NOT says that, to qualify, a row must not meet the criteria set by the search condition or predicate that
follows the NOT. For example, to find all employees in the department E11 except those with a job code
equal to analyst, you can specify:

...
WHERE WORKDEPT = 'E11' AND NOT JOB = 'ANALYST'

When SQL evaluates search conditions that contain these connectors, it does so in a specific order. SQL
first evaluates the NOT clauses, next evaluates the AND clauses, and then the OR clauses.
You can change the order of evaluation by using parentheses. The search conditions enclosed in
parentheses are evaluated first. For example, to select all employees in departments E11 and E21 who
have education levels greater than 12, you can specify:

...
WHERE EDLEVEL > 12 AND
(WORKDEPT = 'E11' OR WORKDEPT = 'E21')

The parentheses determine the meaning of the search condition. In this example, you want all rows that
have a:
• WORKDEPT value of E11 or E21, and
• EDLEVEL value greater than 12
If you did not use parentheses:

...
WHERE EDLEVEL > 12 AND WORKDEPT = 'E11'
OR WORKDEPT = 'E21'

Your result is different. The selected rows are rows that have:
• WORKDEPT = E11 and EDLEVEL > 12, or
• WORKDEPT = E21, regardless of the EDLEVEL value
If you are combining multiple equal comparisons, you can write the predicate with the ANDs as shown in
the following example:

...
WHERE WORKDEPT = 'E11' AND EDLEVEL = 12 AND JOB = 'CLERK'

You can also compare two lists, for example:

...
WHERE (WORKDEPT, EDLEVEL, JOB) = ('E11', 12, 'CLERK')

When two lists are used, the first item in the first list is compared to the first item in the second list, and so
on through both lists. Thus, each list must contain the same number of entries. Using lists is identical to
writing the query with AND. Lists can only be used with the equal and not equal comparison operators.
Related reference
Specifying a search condition using the WHERE clause
The WHERE clause specifies a search condition that identifies the row or rows that you want to retrieve,
update, or delete.

Using OLAP specifications

Online analytical processing (OLAP) specifications provide the ability to return ranking, row numbering,
and other aggregate function information as a scalar value in a query result.

Example: Ranking and row numbering

Suppose that you want a list of the top 10 salaries along with their ranking. The following query generates
the ranking number for you:

86 IBM i: SQL programming

 SELECT EMPNO, SALARY,
RANK() OVER(ORDER BY SALARY DESC),
DENSE_RANK() OVER(ORDER BY SALARY DESC),
ROW_NUMBER() OVER(ORDER BY SALARY DESC)
FROM EMPLOYEE
FETCH FIRST 10 ROWS ONLY;

This query returns the following information.

Table 12. Results of the previous query

EMPNO SALARY RANK DENSE_RANK ROW_NUMBER
000010 52,750.00 1 1 1
000110 46,500.00 2 2 2
200010 46,500.00 2 2 3
000020 41,250.00 4 3 4
000050 40,175.00 5 4 5
000030 38,250.00 6 5 6
000070 36,170.00 7 6 7
000060 32,250.00 8 7 8
000220 29,840.00 9 8 9
200220 29,840.00 9 8 10

In this example, the SALARY descending order with the top 10 returned. The RANK column shows the
relative ranking of each salary. Notice that there are two rows with the same salary at position 2. Each of
those rows is assigned the same rank value. The following row is assigned the value of 4. RANK returns a
value for a row that is one more than the total number of rows that precede that row. There are gaps in the
numbering sequence whenever there are duplicates.
In contrast, the DENSE_RANK column shows a value of 3 for the row directly after the duplicate rows.
DENSE_RANK returns a value for a row that is one more than the number of distinct row values that
precede it. There will never be gaps in the numbering sequence.
ROW_NUMBER returns a unique number for each row. For rows that contain duplicate values according to
the specified ordering, the assignment of a row number is arbitrary; the row numbers could be assigned in
a different order for the duplicate rows when the query is run another time.

Example: Ranking groups

Suppose that you want to find out which department has the highest average salary and the quantile of
the average salary for each department. The following query groups the data by department, determines
the average salary for each department, ranks the resulting averages, and shows a quantile for the
average salary.

SELECT WORKDEPT, INT(AVG(SALARY)) AS AVERAGE,

RANK() OVER(ORDER BY AVG(SALARY) DESC) AS AVG_SALARY,
NTILE(3) OVER(ORDER BY AVG(SALARY) DESC) AS QUANTILE
FROM EMPLOYEE
GROUP BY WORKDEPT;

This query returns the following information.

Table 13. Results of previous query

WORKDEPT AVERAGE AVG_SALARY QUANTILE
B01 41,250 1 1

SQL programming 87
 Table 13. Results of previous query (continued)
WORKDEPT AVERAGE AVG_SALARY QUANTILE
A00 40,850 2 1
E01 40,175 3 1
C01 29,722 4 2
D21 25,668 5 2
D11 25,147 6 2
E21 24,086 7 3
E11 21,020 8 3

In this example, the NTILE function has an argument of 3, meaning that the results are to be grouped into
3 equal-sized sets. Since the result set is not evenly divisible by the number of quantiles, an additional
row is included in each of the two lowest number quantiles.

Example: Ranking within a department

Suppose that you want a list of employees along with how their bonus ranks within their department.
Using the PARTITION BY clause, you can specify groups that are to be numbered separately.

SELECT LASTNAME, WORKDEPT, BONUS,

DENSE_RANK() OVER(PARTITION BY WORKDEPT ORDER BY BONUS DESC)
AS BONUS_RANK_IN_DEPT
FROM EMPLOYEE
WHERE WORKDEPT LIKE 'E%';

This query returns the following information.

Table 14. Results of the previous query

BONUS_RANK_
IN_DEPT
LASTNAME WORKDEPT BONUS
GEYER E01 800.00 1
HENDERSON E11 600.00 1
SCHNEIDER E11 500.00 2
SCHWARTZ E11 500.00 2
SMITH E11 400.00 3
PARKER E11 300.00 4
SETRIGHT E11 300.00 4
SPRINGER E11 300.00 4
SPENSER E21 500.00 1
LEE E21 500.00 1
GOUNOT E21 500.00 1
WONG E21 500.00 1
ALONZO E21 500.00 1
MENTA E21 400.00 2

88 IBM i: SQL programming

Example: Ranking and ordering by table expression results
Suppose that you want to find the top five employees whose salaries are the highest along with their
department names. The department name is in the department table, so a join operation is needed.
Because ordering is already being done in the nested table expression, that ordering can also be used for
determining the ROW_NUMBER value. The ORDER BY ORDER OF table clause is used to do this.

SELECT ROW_NUMBER() OVER(ORDER BY ORDER OF EMP),

EMPNO, SALARY, DEPTNO, DEPTNAME
FROM (SELECT EMPNO, WORKDEPT, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 5 ROWS ONLY) EMP,
DEPARTMENT
WHERE DEPTNO = WORKDEPT;

This query returns the following information.

Table 15. Results of the previous query

ROW_NUMBER EMPNO SALARY DEPTNO DEPTNAME
1 000010 52,750.00 A00 SPIFFY COMPUTER
SERVICE DIV.
2 000110 46,500.00 A00 SPIFFY COMPUTER
SERVICE DIV.
3 200010 46,500.00 A00 SPIFFY COMPUTER
SERVICE DIV.
4 000020 41,250.00 B01 PLANNING
5 000050 40,175.00 E01 SUPPORT
SERVICES

Example: Using OLAP Aggregates and CUME_DIST

Suppose that you want to find the rolling sum of the salaries for employees in department D11 and also
the distribution of the salary.

SELECT ROW_NUMBER() OVER() AS ROW, LASTNAME, SALARY,

SUM(SALARY) OVER(ORDER BY SALARY
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROLLING_TOTAL_RANGE,
SUM(SALARY) OVER(ORDER BY SALARY
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ROLLING_TOTAL_ROWS,
DECIMAL(CUME_DIST() OVER (ORDER BY SALARY),4,3) AS DISTRIBUTION
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
ORDER BY SALARY;

This query returns the following information.

Table 16. Results of the previous query

ROLLING_ ROLLING_
TOTAL TOTAL_
RANGE ROWS
ROW LASTNAME SALARY DISTRIBUTION
1 JONES 18,270.00 18,270.00 18,270.00 .091
2 WALKER 20,450.00 38,720.00 38,720.00 .182
3 SCOUTTEN 21,340.00 60,060.00 60,060.00 .273
4 PIANKA 22,250.00 82,310.00 82,310.00 .364

SQL programming 89
 Table 16. Results of the previous query (continued)

ROLLING_ ROLLING_
TOTAL TOTAL_
RANGE ROWS
ROW LASTNAME SALARY DISTRIBUTION
5 YOSHIMURA 24,680.00 131,670.00 106,990.00 .545
6 YAMAMOTO 24,680.00 131,670.00 131,670.00 .545
7 ADAMSON 25,280.00 156,950.00 156,950.00 .636
8 BROWN 27,740.00 184,690.00 184,690.00 .727
9 LUTZ 29,840.00 244,370.00 214,530.00 .909
10 JOHN 29,840.00 244,370.00 244,370.00 .909
11 STERN 32,250.00 276,620.00 276,620.00 1.000

This example shows two ways of defining the window to be used for calculating the value of a group.
The first way to define the window is with RANGE, which defines a group for all rows that have the same
order by value. Row numbers 5 and 6 have the same salary value, so they are treated as a group. Their
salaries are summed together and added to the previous total to generate the same value for each of the
rows as seen in the ROLLING_TOTAL_RANGE column.
The second way to define the window is with ROWS, which treats each row as a group. In the
ROLLING_TOTAL_ROWS column each row shows the sum calculated up to and including the current
row. For rows that have the same salary value, such as rows 5 and 6, the order in which they are returned
is not defined.
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the default aggregation group and
could be omitted from the ROLLING_TOTAL_RANGE specification.
An ORDER BY is specified for the entire query to guarantee the rows are returned ordered.

Example: Using a RANGE windowing specification

Suppose you want to further analyze the salaries for DEPT D11.
This example groups salaries together by windowing, using a range to work with salary values 1000 less
than or greater than each row's salary. It also returns which salary is the first value and the last value for
the group used to calculate the sum.

SELECT LASTNAME, SALARY,

SUM(SALARY) OVER(ORDER BY SALARY) AS ROLLING_TOTAL,
SUM(SALARY) OVER(ORDER BY SALARY
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS WINDOWED_TOTAL,
FIRST_VALUE(SALARY) OVER(ORDER BY SALARY
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING),
LAST_VALUE(SALARY) OVER(ORDER BY SALARY
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING)
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
ORDER BY SALARY;

This query returns the following information.

Table 17. Results of the previous query

ROLLING_ WINDOWED_
TOTAL TOTAL
LASTNAME SALARY FIRST_VALUE LAST_VALUE
JONES 18,270.00 18,270.00 18,270.00 18,270.00 18,270.00

90 IBM i: SQL programming

Table 17. Results of the previous query (continued)

ROLLING_ WINDOWED_
TOTAL TOTAL
LASTNAME SALARY FIRST_VALUE LAST_VALUE
WALKER 20,450.00 38,720.00 41,790.00 20,450.00 21,340.00
SCOUTTEN 21,340.00 60,060.00 64,040.00 20,450.00 22,250.00
PIANKA 22,250.00 82,310.00 43,590.00 21,340.00 25,280.00
YOSHIMURA 24,680.00 131,670.00 74,640.00 24,680.00 25,280.00
YAMAMOTO 24,680.00 131,670.00 74,640.00 24,680.00 25,280.00
ADAMSON 25,280.00 156,950.00 74,640.00 24,680.00 25,280.00
BROWN 27,740.00 184,690.00 27,740.00 27,740.00 27,740.00
LUTZ 29,840.00 244,370.00 59,680.00 29,840.00 29,840.00
JOHN 29,840.00 244,370.00 59,680.00 29,840.00 29,840.00
STERN 32,250.00 276,620.00 32,250.00 32,250.00 32,250.00

For each employee, a group is defined that contains all other employees in department D11 with salaries
that fall within a range of 1000 below (PRECEDING) or 1000 above (FOLLOWING) that employee's salary.
The WINDOWED_TOTAL column returns the sum of all the salaries in that group. The FIRST_VALUE
column returns the lowest salary value that is part of the group. The LAST_VALUE column returns the
highest salary value that is part of the group. Any employee that has a salary that is more than 1000 from
the closest other salary is its own group.
An ORDER BY is specified for the entire query to guarantee the rows are returned ordered.

Example: Using a ROWS windowing specification

This example groups salaries together by windowing, using ROWS to work with salary values that are one
row before and after each employee salary. The average of the 3 rows is returned.

SELECT LASTNAME, SALARY,

DECIMAL(AVG(SALARY) OVER(ORDER BY SALARY
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)7,2) AS AVG_SALARY
FROM EMPLOYEE
WHERE WORKDEPT = 'D11'
ORDER BY SALARY;

This query returns the following information.

Table 18. Results of the previous query

LASTNAME SALARY AVG_SALARY
JONES 18,270.00 19,360.00
WALKER 20,450.00 20,020.00
SCOUTTEN 21,340.00 21,346.66
PIANKA 22,250.00 22,756.66
YOSHIMURA 24,680.00 23,870.00
YAMAMOTO 24,680.00 24,880.00
ADAMSON 25,280.00 25,900.00
BROWN 27,740.00 27,620.00

SQL programming 91
 Table 18. Results of the previous query (continued)
LASTNAME SALARY AVG_SALARY
LUTZ 29,840.00 29,140.00
JOHN 29,840.00 30,643.33
STERN 32,250.00 31,045.00

Joining data from more than one table

Sometimes the information you want to see is not in a single table. To form a row of the result table, you
might want to retrieve some column values from one table and some column values from another table.
You can retrieve and join column values from two or more tables into a single row.
Several different types of joins are supported by Db2 for i: inner join, left outer join, right outer join, left
exception join, right exception join, and cross join.

Usage notes on join operations

When you join two or more tables, consider the following items:
• If there are common column names, you must qualify each common name with the name of the table
(or a correlation name). Column names that are unique do not need to be qualified.
• If you do not list the column names you want, but instead use SELECT *, SQL returns rows that consist of
all the columns of the first table, followed by all the columns of the second table, and so on.
• You must be authorized to select rows from each table or view specified in the FROM clause.
• The sort sequence is applied to all character, or UCS-2 or UTF-16 graphic columns being joined.

Inner join
An inner join returns only the rows from each table that have matching values in the join columns. Any
rows that do not have a match between the tables do not appear in the result table.
With an inner join, column values from one row of a table are combined with column values from another
row of another (or the same) table to form a single row of data. SQL examines both tables specified for the
join to retrieve data from all the rows that meet the search condition for the join. There are two ways of
specifying an inner join: using the JOIN syntax, and using the WHERE clause.
Suppose you want to retrieve the employee numbers, names, and project numbers for all employees
that are responsible for a project. In other words, you want the EMPNO and LASTNAME columns from
the CORPDATA.EMPLOYEE table and the PROJNO column from the CORPDATA.PROJECT table. Only
employees with last names starting with 'S' or later should be considered. To find this information, you
need to join the two tables.

Inner join using the JOIN syntax

To use the inner join syntax, both of the tables you are joining are listed in the FROM clause, along with the
join condition that applies to the tables.
The join condition is specified after the ON keyword and determines how the two tables are to be
compared to each other to produce the join result. The condition can be any comparison operator; it does
not need to be the equal operator. Multiple join conditions can be specified in the ON clause separated by
the AND keyword. Any additional conditions that do not relate to the actual join are specified in either the
WHERE clause or as part of the actual join in the ON clause.

SELECT EMPNO, LASTNAME, PROJNO

FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

92 IBM i: SQL programming

In this example, the join is done on the two tables using the EMPNO and RESPEMP columns from the
tables. Since only employees that have last names starting with at least 'S' are to be returned, this
additional condition is provided in the WHERE clause.
This query returns the following output.

EMPNO LASTNAME PROJNO

000250 SMITH AD3112
000060 STERN MA2110
000100 SPENSER OP2010
000020 THOMPSON PL2100

Inner join using the WHERE clause

To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter
both the join condition and the additional selection condition in the WHERE clause.
The tables to be joined are listed in the FROM clause, separated by commas.

SELECT EMPNO, LASTNAME, PROJNO

FROM CORPDATA.EMPLOYEE, CORPDATA.PROJECT
WHERE EMPNO = RESPEMP
AND LASTNAME > 'S';

This query returns the same output as the previous example.

Joining data with the USING clause

You can use the USING clause for a shorthand way of defining join conditions. The USING clause is
equivalent to a join condition where each column from the left table is compared to a column with the
same name in the right table.
For example, look at the USING clause in this statement:

SELECT EMPNO, ACSTDATE

FROM CORPDATA.PROJACT INNER JOIN CORPDATA.EMPPROJACT
USING (PROJNO, ACTNO)
WHERE ACSDATE > '1982-12-31';

The syntax is equivalent to the join condition in the following statement:

SELECT EMPNO, ACSTDATE

FROM CORPDATA.PROJACT INNER JOIN CORPDATA.EMPPROJACT
ON CORPDATA.PROJACT.PROJNO = CORPDATA.EMPPROJACT.PROJNO AND
CORPDATA.PROJACT.ACTNO = CORPDATA.EMPPROJACT.ACTNO
WHERE ACSTDATE > '1982-12-31';

Left outer join

A left outer join returns all the rows that an inner join returns plus one row for each of the other rows in
the first table that do not have a match in the second table.
Suppose you want to find all employees and the projects they are currently responsible for. You want to
see those employees that are not currently in charge of a project as well. The following query will return a
list of all employees whose names are greater than 'S', along with their assigned project numbers.

SELECT EMPNO, LASTNAME, PROJNO

FROM CORPDATA.EMPLOYEE LEFT OUTER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

The result of this query contains some employees that do not have a project number. They are listed in the
query, but have the null value returned for their project number.

SQL programming 93
 EMPNO LASTNAME PROJNO
000020 THOMPSON PL2100
000060 STERN MA2110
000100 SPENSER OP2010
000170 YOSHIMURA -
000180 SCOUTTEN -
000190 WALKER -
000250 SMITH AD3112
000280 SCHNEIDER -
000300 SMITH -
000310 SETRIGHT -
200170 YAMAMOTO -
200280 SCHWARTZ -
200310 SPRINGER -
200330 WONG -

Note: Using the RRN or RID scalar functions to return the relative record number for a column in the table
on the right in a left outer join or exception join will return a value of 0 for the unmatched rows.

Right outer join

A right outer join returns all the rows that an inner join returns plus one row for each of the other rows
in the second table that do not have a match in the first table. It is the same as a left outer join with the
tables specified in the opposite order.
The query that was used as the left outer join example can be rewritten as a right outer join as follows:

SELECT EMPNO, LASTNAME, PROJNO

FROM CORPDATA.PROJECT RIGHT OUTER JOIN CORPDATA.EMPLOYEE
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

The results of this query are identical to the results from the left outer join query.

Exception join
A left exception join returns only the rows from the first table that do not have a match in the second
table.
Using the same tables as before, return those employees that are not responsible for any projects.

SELECT EMPNO, LASTNAME, PROJNO

FROM CORPDATA.EMPLOYEE EXCEPTION JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

This join returns the following output.

EMPNO LASTNAME PROJNO

000170 YOSHIMURA -
000180 SCOUTTEN -
000190 WALKER -

94 IBM i: SQL programming

EMPNO LASTNAME PROJNO
000280 SCHNEIDER -
000300 SMITH -
000310 SETRIGHT -
200170 YAMAMOTO -
200280 SCHWARTZ -
200310 SPRINGER -
200330 WONG -

An exception join can also be written as a subquery using the NOT EXISTS predicate. The previous query
can be rewritten in the following way:

SELECT EMPNO, LASTNAME

FROM CORPDATA.EMPLOYEE
WHERE LASTNAME > 'S'
AND NOT EXISTS
(SELECT * FROM CORPDATA.PROJECT
WHERE EMPNO = RESPEMP);

The only difference in this query is that it cannot return values from the PROJECT table.
There is a right exception join, too, that works just like a left exception join but with the tables reversed.

Cross join
A cross join, also known as a Cartesian Product join, returns a result table where each row from the first
table is combined with each row from the second table.
The number of rows in the result table is the product of the number of rows in each table. If the tables
involved are large, this join can take a very long time.
A cross join can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM
clause separated by commas without using a WHERE clause to supply join criteria.
Suppose that the following tables exist.

Table 19. Table A

ACOL1 ACOL2
A1 AA1
A2 AA2
A3 AA3

Table 20. Table B

BCOL1 BCOL2
B1 BB1
B2 BB2

The following two select statements produce identical results.

SELECT * FROM A CROSS JOIN B;

SELECT * FROM A, B;

The result table for either of these SELECT statements looks like this.

SQL programming 95
 ACOL1 ACOL2 BCOL1 BCOL2
A1 AA1 B1 BB1
A1 AA1 B2 BB2
A2 AA2 B1 BB1
A2 AA2 B2 BB2
A3 AA3 B1 BB1
A3 AA3 B2 BB2

Full outer join

Like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full
outer join also returns nonmatching rows from both tables.
Suppose that you want to find all employees and all of their projects. You want to see those employees
that are not currently in charge of a project as well as any projects that do not have an employee assigned.
The following query returns a list of all employees whose names are greater than 'S', along with their
assigned project numbers:

SELECT EMPNO, LASTNAME, PROJNO

FROM CORPDATA.EMPLOYEE FULL OUTER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

Because there are no projects without an assigned employee, the query returns the same rows as a left
outer join. Here are the results.

EMPNO LASTNAME PROJNO

000020 THOMPSON PL2100
000060 STERN MA2110
000100 SPENSER OP2010
000170 YOSHIMURA -
000180 SCOUTTEN -
000190 WALKER -
000250 SMITH AD3112
000280 SCHNEIDER -
000300 SMITH -
000310 SETRIGHT -
200170 YAMAMOTO -
200280 SCHWARTZ -
200310 SPRINGER -
200330 WONG -

Multiple join types in one statement

Sometimes you need to join more than two tables to produce the result that you want.
If you want to return all the employees, their department names, and the projects they are responsible
for, if any, you need to join the EMPLOYEE table, the DEPARTMENT table, and the PROJECT table to get the
information. The following example shows the query and the results:

96 IBM i: SQL programming

 SELECT EMPNO, LASTNAME, DEPTNAME, PROJNO
FROM CORPDATA.EMPLOYEE INNER JOIN CORPDATA.DEPARTMENT
ON WORKDEPT = DEPTNO
LEFT OUTER JOIN CORPDATA.PROJECT
ON EMPNO = RESPEMP
WHERE LASTNAME > 'S';

EMPNO LASTNAME DEPTNAME PROJNO

000020 THOMPSON PLANNING PL2100
000060 STERN MANUFACTURING SYSTEMS MA2110
000100 SPENSER SOFTWARE SUPPORT OP2010
000170 YOSHIMURA MANUFACTURING SYSTEMS -
000180 SCOUTTEN MANUFACTURING SYSTEMS -
000190 WALKER MANUFACTURING SYSTEMS -
000250 SMITH ADMINISTRATION SYSTEMS AD3112
000280 SCHNEIDER OPERATIONS -
000300 SMITH OPERATIONS -
000310 SETRIGHT OPERATIONS -

Using table expressions

You can use table expressions to specify an intermediate result table.
Table expressions can be used in place of a view to avoid creating the view when general use of the view
is not required. Table expressions consist of nested table expressions (also called derived tables) and
common table expressions.
Nested table expressions are specified within parentheses in the FROM clause. For example, suppose
you want a result table that shows the manager number, department number, and maximum salary for
each department. The manager number is in the DEPARTMENT table, the department number is in both
the DEPARTMENT and EMPLOYEE tables, and the salaries are in the EMPLOYEE table. You can use a
table expression in the FROM clause to select the maximum salary for each department. You can also
add a correlation name, T2, following the nested table expression to name the derived table. The outer
select then uses T2 to qualify columns that are selected from the derived table, in this case MAXSAL and
WORKDEPT. Note that the MAX(SALARY) column selected in the nested table expression must be named
in order to be referenced in the outer select. The AS clause is used to do that.

SELECT MGRNO, T1.DEPTNO, MAXSAL

FROM CORPDATA.DEPARTMENT T1,
(SELECT MAX(SALARY) AS MAXSAL, WORKDEPT
FROM CORPDATA.EMPLOYEE E1
GROUP BY WORKDEPT) T2
WHERE T1.DEPTNO = T2.WORKDEPT
ORDER BY DEPTNO;

The result of the query follows.

MGRNO DEPTNO MAXSAL

000010 A00 52750.00
000020 B01 41250.00
000030 C01 38250.00
000060 D11 32250.00
000070 D21 36170.00

SQL programming 97
 MGRNO