Technical Standard
`
`Data Management:
`Structured Query Language (SQL)
`Version 2
`
`C
`A
`
` SL
`
`I
`
`N
`
`T
`
`A
`N
`D
`
`D
`
`R
`
`A
`
`H
`C
`
`E T
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 1 of 288
`
`

`

`[This page intentionally left blank]
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 2 of 288
`
`

`

`X/Open CAE Specification
`
`Data Management:
`
`Structured Query Language (SQL), Version 2
`
`X/Open Company Ltd.
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 3 of 288
`
`

`

`(cid:211) March 1996, X/Open Company Limited
`All rights reserved. No part of this publication may be reproduced, stored in a retrieval system,
`or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording or
`otherwise, without the prior permission of the copyright owners.
`
`X/Open CAE Specification
`Data Management: Structured Query Language (SQL), Version 2
`ISBN: 1-85912-151-9
`X/Open Document Number: C449
`
`Published by X/Open Company Ltd., U.K.
`
`Any comments relating to the material contained in this document may be submitted to X/Open
`at:
`
`X/Open Company Limited
`Apex Plaza
`Forbury Road
`Reading
`Berkshire, RG1 1AX
`United Kingdom
`or by Electronic Mail to:
`XoSpecs@xopen.org
`
`ii
`
`X/Open CAE Specification
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 4 of 288
`
`

`

`Contents
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`1
`1.1
`1.2
`1.2.1
`1.2.2
`1.3
`1.3.1
`1.3.2
`1.3.3
`1.3.4
`1.4
`1.4.1
`1.4.2
`1.4.3
`1.4.4
`1.4.5
`1.4.6
`1.5
`
`2
`2.1
`2.1.1
`2.2
`2.3
`2.3.1
`2.3.2
`2.3.3
`2.3.4
`2.3.5
`2.4
`2.4.1
`2.4.2
`2.4.3
`2.4.4
`2.5
`2.5.1
`2.5.2
`2.5.3
`2.5.4
`2.5.5
`2.6
`2.6.1
`
`Introduction...............................................................................................
` Relational Database Standards ................................................................
` The X/Open Specification ........................................................................
` Audience....................................................................................................
` Compliance Terminology.......................................................................
` Compliance Policy......................................................................................
` Language Embedding.............................................................................
` Flagging Non-portable Usage ...............................................................
` Distributed Transaction Delimitation..................................................
` Character Set Support.............................................................................
` This Issue ......................................................................................................
` Transitional SQL.......................................................................................
` Internationalisation .................................................................................
` Alignment with SPIRIT SQL .................................................................
` Other New Material in this Issue .........................................................
` Substantive Changes in this Issue........................................................
` Documentation and Policy Changes in this Issue.............................
` SQL Registry ................................................................................................
`
`Concepts ......................................................................................................
` Introduction .................................................................................................
` General Terms...........................................................................................
` Data Types and Values...............................................................................
` Tables.............................................................................................................
` Attributes of Columns ............................................................................
` Types of Table ...........................................................................................
` Indexes.......................................................................................................
` System Views............................................................................................
` Integrity Constraints ...............................................................................
` Database System .........................................................................................
` Clients and Servers..................................................................................
` Database Organisation ...........................................................................
` Character Sets ...........................................................................................
` Collations...................................................................................................
` Using SQL.....................................................................................................
` Cursors.......................................................................................................
` Executable SQL Statements ...................................................................
` Embedded Constructs ............................................................................
` Dynamic SQL............................................................................................
` Return Status ............................................................................................
` Access Control.............................................................................................
` Users...........................................................................................................
`
`Data Management: Structured Query Language (SQL), Version 2
`
`1
`1
`2
`2
`2
`5
`5
`5
`6
`6
`7
`7
`9
`9
`10
`10
`12
`13
`
`15
`15
`15
`16
`17
`17
`17
`18
`18
`18
`19
`19
`20
`21
`23
`24
`24
`24
`24
`25
`25
`26
`26
`
`iii
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 5 of 288
`
`

`

`Contents
`
`
`
`
`
`
`
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`2.6.2
`2.6.3
`2.6.4
`2.7
`2.7.1
`2.7.2
`
`3
`3.1
`3.1.1
`3.1.2
`3.1.3
`3.1.4
`3.1.5
`3.1.6
`3.2
`3.2.1
`3.2.2
`3.2.3
`3.2.4
`3.3
`3.4
`3.4.1
`3.5
`3.6
`3.7
`3.8
`3.9
`3.9.1
`3.9.2
`3.9.3
`3.9.4
`3.9.5
`3.9.6
`3.9.7
`3.10
`3.10.1
`3.10.2
`3.10.3
`3.10.4
`3.10.5
`3.10.6
`3.10.7
`3.10.8
`3.11
`3.11.1
`3.11.2
`3.11.3
`
` Ownership.................................................................................................
` Authorisation Identifiers........................................................................
` Privileges ...................................................................................................
` Transactions .................................................................................................
` Transaction Attributes ............................................................................
` Concurrent Transactions ........................................................................
`
`Common Elements ................................................................................
` Notation and Language Structure...........................................................
` Notation.....................................................................................................
` Language Structure .................................................................................
` Format of Object Qualification..............................................................
` Format of Column Qualification ..........................................................
` Specifying the Character Set..................................................................
` Keywords...................................................................................................
` Generic Data Types.....................................................................................
` Character String .......................................................................................
` Numeric.....................................................................................................
` Date/Time.................................................................................................
` Interval.......................................................................................................
` Rules for Determining Data Types..........................................................
` Literals...........................................................................................................
` Pseudo-literals ..........................................................................................
` Assignment ..................................................................................................
` Comparison..................................................................................................
` Null Values...................................................................................................
` Indicator Variables......................................................................................
` Expressions ..................................................................................................
` Arithmetic Operators..............................................................................
` Date/Time and Interval Arithmetic ....................................................
` String Operations.....................................................................................
` Set Functions.............................................................................................
` Dynamic Parameters ...............................................................................
` CAST Function .........................................................................................
` Specifying a Collation .............................................................................
` Search Conditions and Predicates...........................................................
` Comparison Predicate.............................................................................
` Quantified Comparison Predicate........................................................
` BETWEEN Predicate ...............................................................................
` IN Predicate...............................................................................................
` LIKE Predicate ..........................................................................................
` NULL Predicate........................................................................................
` EXISTS Predicate......................................................................................
` OVERLAPS Predicate .............................................................................
` Queries..........................................................................................................
` Query Specifications ...............................................................................
` Joined Tables .............................................................................................
` Query Expressions...................................................................................
`
`26
`26
`27
`28
`28
`29
`
`31
`31
`31
`32
`35
`36
`36
`37
`39
`40
`41
`42
`44
`47
`48
`50
`51
`53
`54
`56
`57
`57
`57
`59
`63
`64
`65
`66
`67
`67
`68
`68
`68
`69
`69
`70
`70
`71
`71
`73
`75
`
`iv
`
`X/Open CAE Specification
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 6 of 288
`
`

`

`3.11.4
`3.11.5
`3.12
`
` Sub-queries................................................................................................
` Correlation ................................................................................................
` Row Value Constructor .............................................................................
`
`Contents
`
`
`
`
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`4
`4.1
`4.1.1
`4.1.2
`4.1.3
`4.2
`4.3
`4.4
`4.4.1
`4.4.2
`4.5
`4.5.1
`4.5.2
`4.5.3
`4.5.4
`4.5.5
`4.5.6
`4.6
`4.6.1
`4.6.2
`4.7
`
`5
`5.1
`5.2
`5.2.1
`5.2.2
`5.2.3
`5.2.4
`5.2.5
`5.2.6
`5.3
`5.3.1
`5.3.2
`5.3.3
`5.3.4
`5.3.5
`5.3.6
`5.3.7
`5.3.8
`5.3.9
`5.3.10
`5.3.11
`5.3.12
`
`76
`76
`78
`
`79
`79
`79
`80
`82
`84
`86
`87
`88
`89
`90
`90
`90
`91
`92
`93
`93
`94
`94
`94
`95
`
`Embedded Aspects ................................................................................
` Embedded SQL Host Program.................................................................
` Embedded SQL Constructs ...................................................................
` Embedded Host Variables and Indicator Variables..........................
` Data Types and Embedded Host Variables........................................
` SQL Declare Section ...................................................................................
` DECLARE AUTHORIZATION Statement............................................
` Cursors..........................................................................................................
` DECLARE CURSOR Statement ............................................................
` Dynamic DECLARE CURSOR Statement ..........................................
` SQL Statement Outcomes .........................................................................
` Outcome Categories................................................................................
` SQLSTATE Status Variable.....................................................................
` Diagnostics Area......................................................................................
` SQLCODE Status Variable.....................................................................
` Application Usage ...................................................................................
` Other Effects of Errors.............................................................................
` WHENEVER Statement ............................................................................
` Special Rule for C.....................................................................................
` Special Rules for COBOL .......................................................................
` Multiple Compilation Units .....................................................................
`
`Executable SQL Statements.............................................................
`97
`97
` Classification of SQL Statements.............................................................
`99
` General Diagnostics ...................................................................................
`99
` Syntax Checking ......................................................................................
` Expression Errors..................................................................................... 100
` Assignment Errors................................................................................... 100
` Constraint Checking ............................................................................... 101
` Read-only Transaction Violation.......................................................... 101
` Connection Errors.................................................................................... 101
` Data Definition Statements....................................................................... 102
` General Diagnostics ................................................................................ 102
` ALTER TABLE .......................................................................................... 102
` CREATE CHARACTER SET ................................................................. 103
` CREATE COLLATION ........................................................................... 104
` CREATE INDEX....................................................................................... 105
` CREATE SCHEMA.................................................................................. 106
` CREATE TABLE ....................................................................................... 107
` CREATE TRANSLATION ...................................................................... 110
` CREATE VIEW ......................................................................................... 111
` DROP CHARACTER SET...................................................................... 112
` DROP COLLATION................................................................................ 113
` DROP INDEX ........................................................................................... 113
`
`Data Management: Structured Query Language (SQL), Version 2
`
`v
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 7 of 288
`
`

`

`Contents
`
`5.3.13
`5.3.14
`5.3.15
`5.3.16
`5.3.17
`5.3.18
`5.4
`5.4.1
`5.4.2
`5.4.3
`5.4.4
`5.4.5
`5.4.6
`5.4.7
`5.4.8
`5.4.9
`5.5
`5.5.1
`5.5.2
`5.5.3
`5.5.4
`5.5.5
`5.5.6
`5.5.7
`5.5.8
`5.5.9
`5.5.10
`5.5.11
`5.5.12
`5.6
`5.6.1
`5.6.2
`5.6.3
`5.6.4
`5.7
`5.7.1
`5.7.2
`5.7.3
`5.7.4
`5.7.5
`5.7.6
`5.7.7
`5.7.8
`5.8
`5.8.1
`5.8.2
`5.8.3
`5.9
`
` DROP SCHEMA ...................................................................................... 113
` DROP TABLE............................................................................................ 114
` DROP TRANSLATION .......................................................................... 114
` DROP VIEW.............................................................................................. 114
` GRANT ...................................................................................................... 115
` REVOKE .................................................................................................... 116
` Data Manipulation Statements ................................................................ 118
` CLOSE........................................................................................................ 118
` Positioned DELETE ................................................................................. 118
` Searched DELETE.................................................................................... 118
` FETCH........................................................................................................ 119
` INSERT....................................................................................................... 119
` OPEN.......................................................................................................... 120
` SELECT INTO .......................................................................................... 121
` Positioned UPDATE ................................................................................ 121
` Searched UPDATE ................................................................................... 122
` Dynamic SQL Statements ......................................................................... 124
` SQL Descriptor Areas ............................................................................. 124
` USING and INTO Clauses..................................................................... 129
` ALLOCATE DESCRIPTOR.................................................................... 130
` DEALLOCATE DESCRIPTOR .............................................................. 130
` DESCRIBE ................................................................................................. 131
` EXECUTE .................................................................................................. 132
` EXECUTE IMMEDIATE ......................................................................... 133
` Dynamic FETCH...................................................................................... 133
` GET DESCRIPTOR.................................................................................. 134
` Dynamic OPEN........................................................................................ 135
` PREPARE................................................................................................... 136
` SET DESCRIPTOR................................................................................... 137
` Transaction Control Statements .............................................................. 139
` General Diagnostics ................................................................................ 139
` COMMIT ................................................................................................... 139
` ROLLBACK............................................................................................... 140
` SET TRANSACTION .............................................................................. 140
` Connection Statements .............................................................................. 142
` Current and Dormant Connections ..................................................... 142
` Default Connection ................................................................................. 142
` State Table.................................................................................................. 142
` Connection Context................................................................................. 143
` General Diagnostics ................................................................................ 143
` CONNECT ................................................................................................ 145
` DISCONNECT ......................................................................................... 146
` SET CONNECTION................................................................................ 146
` Session Statements ..................................................................................... 147
` SET CATALOG......................................................................................... 147
` SET NAMES.............................................................................................. 147
` SET SCHEMA........................................................................................... 148
` SET SESSION AUTHORIZATION.......................................................... 149
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`vi
`
`X/Open CAE Specification
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 8 of 288
`
`

`

`Contents
`
`
`
`5.10
`
` Diagnostic Statement ................................................................................. 150
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`Chapter
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`6
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`7
`7.1
`7.1.1
`7.1.2
`7.1.3
`7.1.4
`7.1.5
`7.1.6
`7.1.7
`7.2
`7.2.1
`7.3
`7.4
`7.5
`7.6
`7.7
`7.8
`
`Information Schema............................................................................. 155
`CHARACTER_SETS ...................................................................................... 156
`COLLATIONS ................................................................................................. 157
`COLUMN_PRIVILEGES............................................................................... 158
`COLUMNS ...................................................................................................... 160
`INDEXES ......................................................................................................... 164
`SCHEMATA..................................................................................................... 165
`SERVER_INFO ............................................................................................... 166
`SQL_LANGUAGES........................................................................................ 168
`TABLE_PRIVILEGES..................................................................................... 169
`TABLES ............................................................................................................ 171
`TRANSLATIONS............................................................................................ 172
`USAGE_PRIVILEGES ................................................................................... 173
`VIEWS .............................................................................................................. 174
`
`Implementation-specific Issues .................................................... 175
` Limits............................................................................................................. 175
` Supplementary Definitions.................................................................... 175
` Additional Limits..................................................................................... 177
` Storage Capacity ...................................................................................... 178
` Statement Complexity ............................................................................ 179
` Embedded Aspects (SPIRIT Only) ....................................................... 179
` Derived Limits (SPIRIT Only)............................................................... 179
` Language-specific Limits (SPIRIT Only) ............................................ 180
` Vendor-specific SQL................................................................................... 181
` Vendor Escape Clause............................................................................. 181
` Restrictions on Names ............................................................................... 183
` Data Definition Statements in Transactions.......................................... 183
` Commitment of Transactions................................................................... 184
` Error Treatment ........................................................................................... 184
` Textual Sequencing..................................................................................... 184
` SELECT ......................................................................................................... 185
`
`Appendix A
`
`A.1
`
`A.2
`
`A.3
`
`Syntax Summary..................................................................................... 187
` Common Elements ..................................................................................... 187
` Embedded Aspects ..................................................................................... 195
` Executable SQL Statements ...................................................................... 198
`
`Appendix B
`
`SQLSTATE Values................................................................................. 203
`
`Appendix C
`
`C.1
`
`C.2
`
`ISO Database Language SQL ......................................................... 209
` Included Features from Intermediate and Full SQL............................ 210
` Extensions to the International Standard .............................................. 212
`
`Data Management: Structured Query Language (SQL), Version 2
`
`vii
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 9 of 288
`
`

`

`Contents
`
`SPIRIT SQL, Issue 2 ............................................................................. 213
` Introduction to SPIRIT............................................................................... 213
` Conformance Requirements..................................................................... 215
` INCLUDE SQLCA ...................................................................................... 225
` Contents of the SQLCA .......................................................................... 225
` Sizing for Database Constructs................................................................ 227
` Resolution of Implementation-defined Items....................................... 228
` Interpretation of the International Standard......................................... 231
` Differences from X/Open SQL ................................................................ 231
` Conformance Claim in SQL_LANGUAGES......................................... 231
`
`SPIRIT SQL, Issue 3 ............................................................................. 233
` Conformance Requirements..................................................................... 233
` Sizing for Database Constructs................................................................ 237
` Resolution of Additional Implementation-defined Items.................. 238
` Enhanced Internationalisation ................................................................. 239
` Differences from X/Open SQL ................................................................ 241
` Conformance Claim in SQL_LANGUAGES......................................... 242
`
`Future Extensions................................................................................... 243
` Security ......................................................................................................... 243
` Adoption of Additional Material from SQL Standards...................... 243
` Host Language Issues ................................................................................ 243
`
`Glossary ....................................................................................................... 245
`
`Index............................................................................................................... 251
`
`Appendix D
`
`D.1
`
`D.2
`
`D.3
`
`D.3.1
`
`D.4
`
`D.5
`
`D.6
`
`D.7
`
`D.8
`
`Appendix E
`
`E.1
`
`E.2
`
`E.3
`
`E.4
`
`E.5
`
`E.6
`
`Appendix F
`
`F.1
`
`F.2
`
`F.3
`
`
`
`
`
`
`
`
`
`List of Figures
`
`5-1
`
`Reporting of Connection Errors (Flowchart) ........................................... 144
`
`List of Tables
`
`2-1
`
`3-1
`3-2
`3-3
`3-4
`3-5
`3-6
`3-7
`3-8
`3-9
`5-1
`5-2
`5-3
`
`Database Phenomena Possible at Various Transaction
`29
`Isolation Levels...............................................................................................
`39
`Classification of Data Types ........................................................................
`40
`Named Character String Data Types.........................................................
`41
`Named Numeric Data Types.......................................................................
`43
`Named Date/Time Data Types ..................................................................
`45
`Named INTERVAL Data Types..................................................................
`46
`Lengths of Named INTERVAL Data Types .............................................
`55
`Treatment of Null Values in Various Contexts ........................................
`59
`String Operations...........................................................................................
`63
`Set Functions...................................................................................................
`Object Types with General Diagnostics for Existence Checking......... 100
`Assumed Data Type of Dynamic Parameters Based on Context......... 136
`Implicit Setting of Item Descriptor Area Fields....................................... 138
`
`viii
`
`X/Open CAE Specification
`
`IPR2022-00976
`Fintiv Ex. 2017 | Page 10 of 288
`
`

`

`Preface
`
`X/Open
`X/Open is an independent, worldwide, open systems organisation supported by most of the
`world’s largest information systems suppliers, user organisations and software companies. Its
`mission is to bring to users greater value from computing, through the practical implementation
`of open systems.
`X/Open’s strategy for achieving this goal is to combine existing and emerging standards into a
`comprehensive,
`integrated, high-value and usable open system environment, called the
`Common Applications Environment (CAE). This environment covers the standards, above the
`hardware level, that are needed to support open systems. It provides for portability and
`interoperability of applications, and so protects investment in existing software while enabling
`additions and enhancements. It also allows users to move between systems with a minimum of
`retraining.
`X/Open defines this CAE in a set of specifications which in