S

Sample questions/exams, 17

SAP, 42

Satellite Administration Center, 65–66, 569

Satellite control database, 65

Satellites, 65

SAVEPOINT, 327

Savepoints, 236, 326–329, 362, 597

stored procedures and, 236

Scalability, data warehousing, 86

Scalar, table, row functions, 229, 289, 330, 582

Scheduling a certification exam, 17–19

Schemas, 13, 170, 192–196, 251, 418–419, 418, 450, 459, 606

ALTERIN privilege in, 115

authorities and privileges using, GRANT and, 128

authorities and privileges using, REVOKE and, 138–139

Control Center to create, 195–196, 195, 196

CREATE SCHEMA for, 193–195

CREATEIN privilege in, 115, 128, 138–139, 149t, 156, 574

CURRENT SCHEMA in, 419

CURRENT SQLID in, 419

Database Administrator (DBADM) and, 196

DROPIN privilege in, 115, 128, 138–139

explicit vs. implicit creation of, 196

IMPLICIT_SCHEMA privilege and, 113, 193

naming, 193

object name in, 192, 193, 418

privileges available with, 115, 115, 193

qualifier in, 192, 193, 418

schema name in, 192, 193, 418

SET SCHEMA for, 419

System Administrator (SYSADM) and, 196

table object assigned to, 192, 193

XML, 9

Scoring, 47

Search conditions, SELECT, 272

Searched CASE expressions, 291–295

Searched delete operations, 265, 360, 594

Searched update operations, 263

Searching

CASE expressions and, 291–295, 358

GROUP BY CUBE clause in, 283–284

GROUP BY ROLLUP clause in, 281–282

indexes and, 210

Net Search Extender and, 57

search conditions and SELECT, 272

searched delete operations and, 265, 360, 594

searched update operations and, 263

Text Extender and, 55, 56, 572

SECADM. See Security Administrator (SECADM)

Section scores panel, in IBM Certification exam, 33, 33

Security, 50, 97–160. See also authorities; privileges

access control in, 97, 98

authentication in, 98–103, 150. See also authentication

authorities in, 97, 103–112, 104. See also authorities

DB2 9 Family Fundamentals exam (Exam 730) and, 97

Distributed Computing Environment (DCE) security services and, 98, 155

encryption and, 100, 155

Generic Security Service Application Program Interface (GSS-API) and, 101, 155

Kerberos for, 100, 155, 573, 581

passwords in, 98–99, 150

privileges and, 97, 103, 104, 113–123. See also privileges

trusted vs. untrusted clients and, 102–103

user IDs in, 98–99

Security Administrator (SECADM), 105, 106, 111–112, 127, 137, 147t, 149t

SELECT, 67, 116, 117, 118, 123, 129, 130, 134, 139, 140, 144, 145, 149t, 151, 155, 156, 158, 171, 255, 257, 265–314, 338–349, 354, 569, 574, 575, 576, 577, 585, 587, 588–591, 594, 595, 596, 607, 613

ambiguous cursors and, 315

BETWEEN predicate and, 274–275

case sensitivity in, 277

common table expressions and, 289–291, 360–361

Cursor Stability isolation level and, 468, 469, 472–473, 474, 476, 610, 612, 613

cursors vs., 314–322. See also cursors

DELETE and, 264

DISTINCT clause in, 267, 269, 270, 271, 355

EXCEPT ALL set operator in, 313–314, 313, 313

EXCEPT set operator in, 311–312, 311, 312, 590

EXISTS predicate in, 279

FETCH FIRST clause and, 268, 271, 287–288, 588–589

FROM clause in, 267–268, 271, 291, 331, 363, 598

GROUP BY clause and, 79, 210, 227, 268, 271, 280–281, 358, 359, 592, 595

GROUP BY CUBE clause in, 283–284

GROUP BY ROLLUP clause in, 281–282

grouping expressions and, 282

HAVING clause and, 268, 271, 284–285, 346, 359, 592

IN predicate in, 277–279, 596

INSERT and, 260

INTERSECT ALL set operator in, 310, 310

INTERSECT set operator in, 309, 309, 591

isolation levels and, 477

joining tables using, 295–306. See also joining tables

LIKE predicate and, 275–277, 591

locks and, 486t, 486

nested, 266

nonaggregate columns and GROUP BY in, 281

NULL predicate in, 279–280

ORDER BY clause and, 79, 210, 227, 268, 271, 285–287, 358, 592, 595, 596

queries using, 265

Read Stability isolation level and, 468, 469t, 471, 472, 476, 497, 501, 610, 611, 613

read-only cursors and, 314–315

relational predicates (comparison operators) and, 272–274

Repeatable Read isolation level and, 468, 469t, 469–470, 470, 476, 495, 501, 610, 613

result set from, 265

search conditions and, 272

set operators and combined query results using, 306–314

subqueries using, 277, 289

subselects and, 260

syntax of, 266

TRUE/FALSE/Unknown and, 272, 284

Uncommitted Read isolation level and, 325, 468, 469t, 474–476, 475, 498, 499, 501, 610, 611, 613

UNION ALL set operator in, 308, 308, 357

UNION set operator in, 307, 307

updatable cursors and, 315

UPDATE and, 262

user-defined data types and, 227

views and, 204, 205, 433

WHERE clause and, 268, 271, 272

wildcard characters and, 276–277

WITH keyword and, 291

XML and, 333

XQuery and, 337

Self-referencing tables, rows, referential integrity constraints and, 385t

Self-tuning memory, 3

Sequences, 192, 217–220, 247, 250, 252–253, 583–584

authorities and privileges using, GRANT and, 131

authorities and privileges using, REVOKE and, 141

characteristics of, 217

CREATE SEQUENCE for, 218–220

data types and, 217

identity columns vs, 218

NEXT VALUE and, 119, 159, 217, 219, 580

PREVIOUS VALUE and, 119, 159, 217, 580

privileges available with, 119, 119

Serialization, XML, 9

SERVER, 573, 581

SERVER_ENCRYPT, 100, 155, 573, 581

Servers, 161–162

authentication and, 99, 155

authorities and privileges using, GRANT and, 133–134

authorities and privileges using, REVOKE and, 143–144

federated, 121. See also federation, 121

privileges available with, 121–122, 122

SET INTEGRITY, 398–401

Set operators and combined query results using, 306–314, 590–591, 596

SET SCHEMA, 419

SETSESSIONUSER, 111

SHARE, 612

Share (S), 480t, 484, 485, 486t, 488t, 489, 497, 500

Share with intent exclusive (SIX), 481t, 486t, 488t

Shredding XML documents, 57–60, 96, 572, 573

Simple CASE expressions, 291–295

Single-precision (REAL, FLOAT) data type, 367–368

Small integer (SMALLINT) data type, 366–367, 366

Smart phones, 37

SMP configuration, 50

SMS, 3, 11

Snapshot monitor data, 3, 73

Software Development Kit (SDK), 47, 240

Solaris. See Sun Solaris

Sorting, 592

Sourced function, 229, 330

Sourced routines, 586

Spatial Extender, 40, 55, 58, 91, 95–96, 572

Spatial Extender Client, 40

SQL, 2, 6, 8, 9, 13, 45, 48, 50, 52, 255–333, 570, 585

Audio, Video, and Image (AVI) extender for, 56

authorities and, 105

CASE expressions in, 291–295, 358

categories of statements in, 256

Command Editor in, 66–71, 67, 256

Command Line Processor (CLP) in, 82–83, 83, 256

common table expressions and, 289–291, 360–361

Compound, 10

cursors to retrieve results from, 314–322. See also cursors

Data Control Language (DCL), 256, 257

Data Definition Language (DDL), 256, 257

Data Manipulation Language (DML), 256, 257–266

Database Administrator (DBADM) authority and, 110–111

DELETE. See DELETE

Developer Workbench in, 10, 83–84, 85, 89, 94, 569, 570

Embedded, 9, 47, 54, 256, 257, 585, 477

GRANT statement in. See GRANT, 112

INSERT. See INSERT, 257

isolation levels and, 477

joining tables using SELECT and, 295–306. See also joining tables

packages and, 240

precompilers for, 240

REVOKE. See REVOKE

routines in, 586

SELECT. See SELECT

set operators and combined query results using, 306–314

SQL Assist tool and, 66, 67–68, 68

SQL Performance Monitor and, 74–75, 89, 93, 569

SQL Warehousing Tool in, 46

stored procedures and, 331–333

syntax of, 257

Task Center in, 71–72, 72, 94, 569, 570

transactions and transaction boundaries in, 256, 322–329, 596–597. See also transactions and transaction boundaries

UPDATE. See UPDATE

user-defined functions and, 329–331

Visual Explain and, 69–71, 69

XQuery and, 335–337. See also XQuery

SQL Assist tool, 67–68, 68

SQL Communications Area (SQLCA), 9

SQL Descriptor Area (SQLDA), cursors and, 318, 320

SQL Optimizer, informational constraints and, 374, 397–398, 398, 605

SQL Performance Monitor, 74–74, 89, 93, 569

SQL Precompiler, 240

SQL scalar, table, row functions, 229, 330

SQL stored procedures, 231–233

SQL Warehousing Dataflow Operators, 14

SQL Warehousing Tool, 14, 46

SQL/400, 36

SQLBP.1/2, 168

SQLCA, 71

SQLDBCON, 168

SQLDBCONF, 168

SQLINSLK, 168

SQLJ, 8, 9, 48, 54, 84, 570

isolation levels and, 477

SQLOGCTL.LFH, 168

SQLOGDIR subdirectory, 168

SQLOGMIR.LFH, 168

SQLSGF.1/2, 168

SQLSPCS.1/2, 168

SQLTMPLK, 168

Standby databases, 11

START, 7

Statistics. See RUNSTATS

STOGROUP, 6

STOP, 7

Storage Optimization Feature, 44

Stored procedures, 10, 84, 192, 230–240, 244, 255, 331–333, 351, 363, 598

authorities and privileges using, 152, 154, 160

CALL for, 331–332, 363

clauses and modifiers of CREATE PROCEDURE and, 236–237

CREATE PROCEDURE for, 231–233, 234–240, 331, 332

Database Manager and, 237

DB2INFO and, 238–239

DETERMINISTIC and NOT DETERMINISTIC in, 236

Developer Workbench in, 10, 83–84, 85, 89, 94, 569, 570

division of business applications and logic and, 230

DROP and, 232

DROP PROCEDURE and, 232

external, 231, 233–240

FENCED and NOT FENCED in, 237

INCLUDE and, 236

library storage of, 234, 235, 237

MAIN vs. SUB in, 238

parameter passing in, 237–238

programming languages and, 233

savepoints in, 236, 326–329, 362, 597

SQL type, 231–233

user-defined functions and, 230

uses for, 230

WHENEVER and, 236

WITH RETURN FOR clause and, 233

Strong data typing a, 225, 373–374

Structured data types, 228. See also user-defined data types

Structured Query Language. See SQL

SUB, stored procedures and, 238

Subflows, 14

Subqueries/Subselects, SELECT and, 260, 277, 289, 592, 593

Subroutines, stored procedures and, 238

Subtypes, 228

Sun/Solaris, 8, 11, 36, 41, 42, 98, 155, 567, 568, 573

Super exclusive lock, 482t, 486t, 488t

Supertypes, 228

SUSE Enterprise Linux Server (SLES), 41, 41

Sybase, 38

Synchronization, 38, 66

SYSADM. See System Administrator (SYSADM)

SYSCAT, 193

SYSCATSPACE table space, 115, 169

SYSCTRL. See System Control (SYSCTRL)

SYSFUN, 193

SYSIBM, 193

SYSMAIN. See System Maintenance (SYSMAIN)

SYSMON. See System Monitor (SYSMON)

SYSSTAT, 193

System Administrator (SYSADM), 105–106, 108, 109, 110, 111, 118, 147t, 575–576, 579

authorities and privileges using, 129, 130, 134, 139, 140, 144, 147t, 147

granting authorities/privileges through, 124

schemas and, 196

System catalog tables, 169–170, 174

System commands, 164

System Control (SYSCTRL), 105–109, 110, 111, 147t, 149t, 574

System database directory, 179–180, 249

System Maintenance (SYSMAINT), 105, 106, 108–109, 110, 147t, 149t, 574

System Monitor (SYSMON), 105, 106, 109–110, 147t, 149t

System Operator, authorities and privileges using, 149t, 148

System z Integrated Information Processors (zIIP), 51

System/370, Database Connection Services (DCS) directory in, 181–182

System/390, Database Connection Services (DCS) directory in, 181–182

System-defined routines, 586

System-managed (SMS) files, 169, 174, 175