Contents
About DB2 for i5/OS SQL Reference Standards compliance Who should read the SQL Reference How to use this book Assumptions relating to examples of SQL statements How to read the syntax diagrams Conventions used in this book SQL accessibility Printable PDFs What's new for V5R4 Concepts Relational database Structured Query Language Static SQL Dynamic SQL Extended Dynamic SQL Interactive SQL SQL Call Level Interface (CLI) and Open Database Connectivity (ODBC) Java DataBase Connectivity (JDBC) and embedded SQL for Java (SQLJ) programs OLE DB and ADO (ActiveX Data Object) .NET Schemas Tables Keys Constraints Indexes Triggers Views User-defined types Aliases Packages and access plans Routines Functions Procedures Sequences Authorization, privileges and object ownership Catalog Application processes, concurrency, and recovery Locking, commit, and rollback Unit of work Rolling back work Threads Isolation level Repeatable read Read stability Cursor stability Uncommitted read No commit Comparison of isolation levels Storage Structures Character conversion Character sets and code pages Coded character sets and CCSIDs Default CCSID Sort sequence Distributed relational database Application servers CONNECT (Type 1) and CONNECT (Type 2) Remote unit of work Application-directed distributed unit of work Data representation considerations Language elements Characters Tokens Identifiers SQL identifiers System identifiers Host identifiers Naming conventions SQL path Qualification of unqualified object names SQL names and system names: special considerations Aliases Authorization IDs and authorization names Example Data types Nulls Numbers Character strings Character encoding schemes Graphic strings Graphic encoding schemes Binary strings Large objects Limitations on use of strings Datetime values DataLink values Row ID values User-defined types Promotion of data types Casting between data types Assignments and comparisons Numeric assignments String assignments Datetime assignments DataLink assignments Row ID assignments Distinct type assignments Assignments to LOB locators Numeric comparisons String comparisons Datetime comparisons DataLink comparisons Row ID comparisons Distinct type comparisons Rules for result data types Numeric operands Character and graphic string operands Binary string operands Datetime operands DataLink operands ROWID operands Distinct type operands Conversion rules for operations that combine strings Constants Integer constants Floating-point constants Decimal constants Character-string constants Graphic-string constants Binary-string constants Datetime constants Decimal point Delimiters Special registers CURRENT DATE CURRENT DEBUG MODE CURRENT DEGREE CURRENT PATH CURRENT SCHEMA CURRENT SERVER CURRENT TIME CURRENT TIMESTAMP CURRENT TIMEZONE SESSION_USER SYSTEM_USER USER Column names Qualified column names Correlation names Column name qualifiers to avoid ambiguity Column name qualifiers in correlated references Unqualified column names in correlated references References to variables References to host variables Variables in dynamic SQL References to LOB variables References to LOB locator variables References to LOB file reference variables Host structures Host structure arrays Functions Types of functions Function invocation Function resolution Determining the best fit Best fit considerations Expressions Without operators With arithmetic operators With the concatenation operator Scalar fullselect Datetime operands and durations Datetime arithmetic in SQL Precedence of operations CASE expressions CAST specification OLAP specifications Sequence reference Predicates Basic predicate Quantified predicate BETWEEN predicate DISTINCT predicate EXISTS predicate IN predicate LIKE predicate NULL predicate Search conditions Examples Built-in functions Aggregate functions AVG COUNT COUNT_BIG MAX MIN STDDEV_POP or STDDEV STDDEV_SAMP SUM VAR_POP or VARIANCE or VAR VARIANCE_SAMP or VAR_SAMP Scalar functions Example ABS ACOS ADD_MONTHS ANTILOG ASIN ATAN ATANH ATAN2 BIGINT BINARY BIT_LENGTH BLOB CEILING CHAR CHARACTER_LENGTH CLOB COALESCE CONCAT COS COSH COT CURDATE CURTIME DATABASE DATAPARTITIONNAME DATAPARTITIONNUM DATE DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFWEEK_ISO DAYOFYEAR DAYS DBCLOB DBPARTITIONNAME DBPARTITIONNUM DECIMAL or DEC DECRYPT_BIT, DECRYPT_BINARY, DECRYPT_CHAR and DECRYPT_DB DEGREES DIFFERENCE DIGITS DLCOMMENT DLLINKTYPE DLURLCOMPLETE DLURLPATH DLURLPATHONLY DLURLSCHEME DLURLSERVER DLVALUE DOUBLE_PRECISION or DOUBLE ENCRYPT_RC2 ENCRYPT_TDES EXP EXTRACT FLOAT FLOOR GENERATE_UNIQUE GETHINT GRAPHIC HASH HASHED_VALUE HEX HOUR IDENTITY_VAL_LOCAL IFNULL INSERT INTEGER or INT JULIAN_DAY LAND LAST_DAY LCASE LEFT LENGTH LN LNOT LOCATE LOG10 LOR LOWER LTRIM MAX MICROSECOND MIDNIGHT_SECONDS MIN MINUTE MOD MONTH MONTHNAME MULTIPLY_ALT NEXT_DAY NOW NULLIF OCTET_LENGTH PI POSITION or POSSTR POWER QUARTER RADIANS RAISE_ERROR RAND REAL REPEAT REPLACE RIGHT ROUND ROWID RRN RTRIM SECOND SIGN SIN SINH SMALLINT SOUNDEX SPACE SQRT STRIP SUBSTR SUBSTRING TAN TANH TIME TIMESTAMP TIMESTAMP_ISO TIMESTAMPDIFF TRANSLATE TRIM TRUNCATE or TRUNC UCASE UPPER VALUE VARBINARY VARCHAR VARCHAR_FORMAT VARGRAPHIC WEEK WEEK_ISO XOR YEAR ZONED Queries Authorization subselect select-clause from-clause where-clause group-by-clause having-clause Examples of a subselect fullselect Rules for columns Examples of a fullselect select-statement common-table-expression order-by-clause fetch-first-clause update-clause read-only-clause optimize-clause isolation-clause Examples of a select-statement Statements How SQL statements are invoked Embedding a statement in an application program Dynamic preparation and execution Static invocation of a select-statement Dynamic invocation of a select-statement Interactive invocation SQL return codes SQLSTATE SQLCODE SQL comments ALLOCATE DESCRIPTOR Invocation Authorization Syntax Description Notes Examples ALTER PROCEDURE (External) Invocation Authorization Syntax Description Notes Examples ALTER PROCEDURE (SQL) Invocation Authorization Syntax Description Notes Examples ALTER SEQUENCE Invocation Authorization Syntax Description Notes Examples ALTER TABLE Invocation Authorization Syntax Description ADD COLUMN column-definition ALTER COLUMN column-alteration DROP COLUMN ADD unique-constraint ADD referential-constraint ADD check-constraint DROP ADD partitioning-clause DROP PARTITIONING ADD PARTITION ALTER PARTITION DROP PARTITION ADD MATERIALIZED QUERY materialized-query-definition ALTER MATERIALIZED QUERY materialized-query-table-alteration DROP MATERIALIZED QUERY ACTIVATE NOT LOGGED INITIALLY VOLATILE or NOT VOLATILE Notes Cascaded Effects Examples BEGIN DECLARE SECTION Invocation Authorization Syntax Description Examples CALL Invocation Authorization Syntax Description Notes Examples CLOSE Invocation Authorization Syntax Description Notes Example COMMENT Invocation Authorization Syntax Description Notes Examples COMMIT Invocation Authorization Syntax Description Notes Example CONNECT (Type 1) Invocation Authorization Syntax Description Notes Examples CONNECT (Type 2) Invocation Authorization Syntax Description Notes Examples CREATE ALIAS Invocation Authorization Syntax Description Notes Examples CREATE DISTINCT TYPE Invocation Authorization Syntax Description Notes Examples CREATE FUNCTION Notes CREATE FUNCTION (External Scalar) Invocation Authorization Syntax Description Notes Examples CREATE FUNCTION (External Table) Invocation Authorization Syntax Description Notes Example CREATE FUNCTION (Sourced) Invocation Authorization Syntax Description Notes Examples CREATE FUNCTION (SQL Scalar) Invocation Authorization Syntax Description Notes Example CREATE FUNCTION (SQL Table) Invocation Authorization Syntax Description Notes Example CREATE INDEX Invocation Authorization Syntax Description Notes Examples CREATE PROCEDURE Notes CREATE PROCEDURE (External) Invocation Authorization Syntax Description Notes Example CREATE PROCEDURE (SQL) Invocation Authorization Syntax Description Notes Example CREATE SCHEMA Invocation Authorization Syntax Description Notes Examples CREATE SEQUENCE Invocation Authorization Syntax Description Notes Examples CREATE TABLE Invocation Authorization Syntax Description column-definition LIKE as-subquery-clause copy-options unique-constraint referential-constraint check-constraint NOT LOGGED INITIALLY VOLATILE or NOT VOLATILE RCDFMT distribution-clause partitioning-clause Notes Rules for System Name Generation Examples CREATE TRIGGER Invocation Authorization Syntax Description Notes Examples CREATE VIEW Invocation Authorization Syntax Description Notes Examples DEALLOCATE DESCRIPTOR Invocation Authorization Syntax Description Notes Examples DECLARE CURSOR Invocation Authorization Syntax Description Notes Examples DECLARE GLOBAL TEMPORARY TABLE Invocation Authorization Syntax Description column-definition LIKE as-subquery-clause copy-options Notes Examples DECLARE PROCEDURE Invocation Authorization Syntax Description Notes Example DECLARE STATEMENT Invocation Authorization Syntax Description Example DECLARE VARIABLE Invocation Authorization Syntax Description Notes Example DELETE Invocation Authorization Syntax Description DELETE Rules Notes Examples DESCRIBE Invocation Authorization Syntax Description Notes Example DESCRIBE INPUT Invocation Authorization Syntax Description Notes Examples DESCRIBE TABLE Invocation Authorization Syntax Description Notes Example DISCONNECT Invocation Authorization Syntax Description Notes Examples DROP Invocation Authorization Syntax Description Note Examples END DECLARE SECTION Invocation Authorization Syntax Description Examples EXECUTE Invocation Authorization Syntax Description Notes Example EXECUTE IMMEDIATE Invocation Authorization Syntax Description Note Example FETCH Invocation Authorization Syntax Description single-fetch multiple-row-fetch Notes Example FREE LOCATOR Invocation Authorization Syntax Description Example GET DESCRIPTOR Invocation Authorization Syntax Description Notes Example GET DIAGNOSTICS Invocation Authorization Syntax Description Notes Example GRANT (Distinct Type Privileges) Invocation Authorization Syntax Description Note Example GRANT (Function or Procedure Privileges) Invocation Authorization Syntax Description Note Example GRANT (Package Privileges) Invocation Authorization Syntax Description Note Example GRANT (Sequence Privileges) Invocation Authorization Syntax Description Note Example GRANT (Table or View Privileges) Invocation Authorization Syntax Description Notes Examples HOLD LOCATOR Invocation Authorization Syntax Description Note Example INCLUDE Invocation Authorization Syntax Description Notes Example INSERT Invocation Authorization Syntax Description insert-multiple-rows INSERT Rules Notes Examples LABEL Invocation Authorization Syntax Description Notes Examples LOCK TABLE Invocation Authorization Syntax Description Notes Example OPEN Invocation Authorization Syntax Description Notes Examples PREPARE Invocation Authorization Syntax Description Notes Examples REFRESH TABLE Invocation Authorization Syntax Description Notes Example RELEASE (Connection) Invocation Authorization Syntax Description Notes Examples RELEASE SAVEPOINT Invocation Authorization Syntax Description Note Example RENAME Invocation Authorization Syntax Description Notes Examples REVOKE (Distinct Type Privileges) Invocation Authorization Syntax Description Notes Example REVOKE (Function or Procedure Privileges) Invocation Authorization Syntax Description Notes Example REVOKE (Package Privileges) Invocation Authorization Syntax Description Notes Example REVOKE (Sequence Privileges) Invocation Authorization Syntax Description Notes Example REVOKE (Table or View Privileges) Invocation Authorization Syntax Description Notes Examples ROLLBACK Invocation Authorization Syntax Description Notes Examples SAVEPOINT Invocation Authorization Syntax Description Note Example SELECT SELECT INTO Invocation Authorization Syntax Description Note Examples SET CONNECTION Invocation Authorization Syntax Description Notes Example SET CURRENT DEBUG MODE Invocation Authorization Syntax Description Notes Example SET CURRENT DEGREE Invocation Authorization Syntax Description Notes Example SET DESCRIPTOR Invocation Authorization Syntax Description Notes Example SET ENCRYPTION PASSWORD Invocation Authorization Syntax Description Notes Example SET OPTION Invocation Authorization Syntax Description Notes Examples SET PATH Invocation Authorization Syntax Description Notes Example SET RESULT SETS Invocation Authorization Syntax Description Notes Example SET SCHEMA Invocation Authorization Syntax Description Notes Examples SET SESSION AUTHORIZATION Invocation Authorization Syntax Description Notes Examples SET TRANSACTION Invocation Authorization Syntax Description Notes Examples SET transition-variable Invocation Authorization Syntax Description Notes Examples SET variable Invocation Authorization Syntax Description Notes Examples SIGNAL Invocation Authorization Syntax Description Notes Examples UPDATE Invocation Authorization Syntax Description UPDATE Rules Notes Examples VALUES Invocation Authorization Syntax Description Notes Examples VALUES INTO Invocation Authorization Syntax Description Notes Examples WHENEVER Invocation Authorization Syntax Description Notes Example SQL control statements References to SQL parameters and SQL variables References to SQL condition names References to SQL cursor names Summary of 'name' scoping in nested compound statements SQL-procedure-statement Syntax Notes assignment-statement Syntax Description Notes Example CALL statement Syntax Description Notes Example CASE statement Syntax Description Notes Examples compound-statement Syntax Description Notes Examples FOR statement Syntax Description Notes Example GET DIAGNOSTICS statement Syntax Description Notes Example GOTO statement Syntax Description Notes Example IF statement Syntax Description Example ITERATE statement Syntax Description Example LEAVE statement Syntax Description Notes Examples LOOP statement Syntax Description Examples REPEAT statement Syntax Description Example RESIGNAL statement Syntax Description Notes Example RETURN statement Syntax Description Notes Example SIGNAL statement Syntax Description Notes Example WHILE statement Syntax Description Example Appendix A. SQL limits Appendix B. Characteristics of SQL statements Actions allowed on SQL statements SQL statement data access indication in routines Considerations for using distributed relational database CONNECT (Type 1) and CONNECT (Type 2) differences Appendix C. SQLCA (SQL communication area) Field descriptions INCLUDE SQLCA declarations Appendix D. SQLDA (SQL descriptor area) Field descriptions in an SQLDA header Determining how many SQLVAR occurrences are needed Field descriptions in an occurrence of SQLVAR Fields in an occurrence of a base SQLVAR Fields in an occurrence of a secondary SQLVAR SQLTYPE and SQLLEN CCSID values in SQLDATA or SQLNAME Unrecognized and unsupported SQLTYPES INCLUDE SQLDA declarations For C and C++ For COBOL For ILE COBOL For PL/I For ILE RPG Appendix E. CCSID values Appendix F. DB2 for i5/OS catalog views Notes i5/OS catalog tables and views SYSCATALOGS SYSCHKCST SYSCOLUMNS SYSCOLUMNSTAT SYSCST SYSCSTCOL SYSCSTDEP SYSFUNCS SYSINDEXES SYSJARCONTENTS SYSJAROBJECTS SYSKEYCST SYSKEYS SYSPACKAGE SYSPARMS SYSPARTITIONINDEXSTAT SYSPARTITIONSTAT SYSPROCS SYSREFCST SYSROUTINEDEP SYSROUTINES SYSSEQUENCES SYSTABLEDEP SYSTABLEINDEXSTAT SYSTABLES SYSTABLESTAT SYSTRIGCOL SYSTRIGDEP SYSTRIGGERS SYSTRIGUPD SYSTYPES SYSVIEWDEP SYSVIEWS ODBC and JDBC catalog views SQLCOLPRIVILEGES SQLCOLUMNS SQLFOREIGNKEYS SQLPRIMARYKEYS SQLPROCEDURECOLS SQLPROCEDURES SQLSCHEMAS SQLSPECIALCOLUMNS SQLSTATISTICS SQLTABLEPRIVILEGES SQLTABLES SQLTYPEINFO SQLUDTS ANS and ISO catalog views AUTHORIZATIONS CHARACTER_SETS CHECK_CONSTRAINTS COLUMNS INFORMATION_SCHEMA_CATALOG_NAME PARAMETERS REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA SQL_FEATURES SQL_LANGUAGES SQL_SIZING TABLE_CONSTRAINTS TABLES USER_DEFINED_TYPES VIEWS Appendix G. Terminology differences Appendix H. Reserved schema names and reserved words Reserved schema names Reserved words Related information Index
[ Top of Page | Previous Page | Next Page | Contents |
Index ]