AS/400e - Astradyne

1 AS/400eDB2 UDBforAS/400 SQLR eferenceVersion4 AS/400eDB2 UDBforAS/400 SQLR eferenceVersion4 Copyright International Business Machines Corporation 1998, 2000. All rights Government Users Restricted Rights Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM DB2 UDB for AS/400 should read the SQL Reference Relating to Examples of SQLS tatements ..xiiHow to Read Syntax for Describing Mixed Data Values .. xivWhat s new for V4R5 in the SQL Reference bookxivChapter 1. Query Dynamic Call Level .. and Access.

2 Processes, Concurrency, and Recovery12 Threads .. Relational (Type 1) and CONNECT (Type 2) .. Distributed Unit of Work24 Data Representation Sets and Code Character Sets and ..31 Authorization and 2. Language of Unqualified Object Names .. 45 SQL Names and System Names: and the SQL IDs and Authorization-Names ..51 Character Objects (LOBs).. of Data Between Data Type Rules for Type String and Graphic String Type Rules for Operations That DATE or PATH, CURRENT_PATH, orCURRENT FUNCTION SERVER or 88 CURRENT TIME or Copyright IBM Corp.

3 1998, 2000iiiCURRENT TIMESTAMP TIMEZONE Column Name Qualifiers to Avoid Ambiguity .. 92 Column Name Qualifiers in Column Variables in Dynamic to LOB File Reference Variables .. 98 Host Structures in C, C++, COBOL, PL/I, and RPG99 Host Structure Arrays in C, C++, COBOL, PL/I, ..101 Function ..103 Function the Concatenation Arithmetic Integer and Decimal Decimal Arithmetic in Types as Operands and Arithmetic in of ..127 Search 3. Built-In or or or .. or or or .. UDB for AS/400 SQL Reference V4R5|||| or.

4 Or .. or 4. of a of a of a 5. SQL Statements Are a Statement in an Preparation and Invocation of a Invocation of a select-statement .. 243 Interactive Return DECLARE |||||| (Type1).. (Type2).. DISTINCT FUNCTION (Scalar).. FUNCTION (Sourced).. for System Name UDB for AS/400 SQL Reference V4R5 DECLARE DECLARE Marker (Function or Procedure Privileges).. (Package Privileges).. (Table Privileges).. (User-Defined Type Privileges).. Marker .. (Function or Procedure Privileges).

5 (Package Privileges).. (Table Privileges).. (User-Defined Type Privileges).. RESULT UDB for AS/400 SQL Reference 6. SQL Procedures routine A. SQL B. SQL SQLCA C. SQL Descriptor Area(SQLDA)..551 Field Descriptions in an Occurrence of SQLVAR552 Determining How Many SQLVAR and or ||||||||||||||||||||||||Unrecognized and Unsupported SQLTYPES .. 559 INCLUDE SQLDA ++..560 For ILE ILE D. Reserved E. CCSID F. Considerations for UsingDistributed Relational Database .. 583 CONNECT (Type 1) and CONNECT (Type 2) the CONNECT rules that apply589 Connecting to Application Servers That OnlySupport Remote Unit of G.

6 DB2 UDB for AS/400 Catalog UDB for AS/400 SQL Reference V4R5 About DB2 UDB for AS/400 SQL ReferenceThis book defines Structured Query Language (SQL) as supported by DB2 QueryManager and SQL Development Kit. It contains reference information for the tasksof system administration, database administration, application programming, andoperation. This manual includes syntax, usage notes, keywords, and examples foreach of the SQL statements used on the AS/400 ComplianceDB2 UDB for AS/400 Version 4 Release 5 complies with the following IBM andIndustry SQL Standards:vISO (International Standards Organization) 9075: 1992, Database Language SQL -Entry LevelvISO (International Standards Organization) 9075-4: 1996, Database Language SQL- Part 4.

7 Persistent Stored Modules (SQL/PSM)vANSI (American National Standards Institute) , Database LanguageSQL - Entry LevelvANSI (American National Standards Institute) 4: 1996, DatabaseLanguage SQL - Part 4: Persistent Stored Modules (SQL/PSM)vIBM SQL Reference Version 2, should read the SQL Reference bookThis book is intended for programmers who want to write applications that willuse SQL to access an AS/400 is assumed that you possess an understanding of system administration,database administration, or application programming for the AS/400, as providedby the SQL Programming Concepts book and that you have some knowledge ofthe following.

8 VCOBOL for AS/400vILE C for AS/400vVisualAge C++ for OS/400vILE COBOL for AS/400vAS/400 Toolbox for Java or AS/400 Developer Kit for JavavILE RPG for AS/400vAS/400 PL/IvREXXvRPG III (part of RPG for AS/400)vStructured Query Language (SQL)References in this book to RPG and COBOL refer to the RPG or COBOL languagein general. References to COBOL for AS/400, ILE COBOL for AS/400, ILE RPG forAS/400, or RPG III (part of RPG for AS/400) refer to specific elements of theproduct where they differ from each other. Copyright IBM Corp. 1998, 2000xiThis manual is a reference rather than a tutorial.

9 It assumes you are alreadyfamiliar with SQL programming. This manual also assumes that you will bewriting applications solely for the you need more information about using SQL statements, statement syntax, andparameters, see the SQL Programming Concepts you are planning applications that are portable to other IBM environments, itwill be necessary for you to refer to books for those environments in addition tothis one (such asIBM SQL Reference Version 2,SC26-8416).Assumptions Relating to Examples of SQL StatementsThe examples of SQL statements shown in this guide are based on the sampletables in Appendix A of the SQL Programming Concepts book and assume thefollowing:vThey are shown in the interactive SQL environment or written in COBOL.

10 EXECSQL and END-EXEC are used to delimit an SQL statement in a COBOL program. A description of how to use SQL statements in a COBOL program isprovided in the SQL Programming with Host Languages SQL example is shown on several lines, with each clause of the statementon a separate keywords are names provided in Appendix A of the SQL Programming Concepts bookuse the collection CORPDATA. Table names that are not provided in thatappendix should use collections that you columns are enclosed in parentheses, ().vThe SQL naming convention is APOST and APOSTSQL precompiler options are assumed (although they arenot the default in COBOL).