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 .. 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.

2 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. 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.

3 127 Search 3. Built-In or or or .. or or or .. UDB for AS/400 SQL Reference V4R5|||| or .. 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) .. (Package Privileges).. (Table Privileges).. (User-Defined Type Privileges).. RESULT UDB for AS/400 SQL Reference 6.

4 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. 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.

5 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: 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: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.

6 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. 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.

7 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). Character-string constants within SQL and hostlanguage statements are delimited by apostrophes ( ).vA sort sequence of *HEX is the examples vary from these assumptions, it is to Read Syntax DiagramsThroughout this book, syntax is described using the structure defined as follows:vRead the syntax diagrams from left to right, from top to bottom, following thepath of the symbol indicates the beginning of a symbol indicates that the statement syntax is continued on the symbol indicates that a statement is continued from the previous symbol indicates the end of a of syntactical units other than complete statements start with the symbol and end with the items appear on the horizontal line (the main path).

8 Required_item vOptional items appear below the main UDB for AS/400 SQL Reference V4R5 required_itemoptional_item If an optional item appears above the main path, that item has no effect on theexecution of the statement and is used only for readability. required_itemoptional_item vIf you can choose from two or more items, they appear vertically, in a youmustchoose one of the items, one item of the stack appears on the mainpath. required_itemrequired_choice1required_ch oice2 If choosing one of the items is optional, the entire stack appears below the mainpath. required_itemoptional_choice1optional_ch oice2 If one of the items is the default, it will appear above the main path and theremaining choices will be shown below. required_itemdefault_choiceoptional_choi ceoptional_choice vAn arrow returning to the left, above the main line, indicates an item that can berepeated.

9 Required_item repeatable_item If the repeat arrow contains a comma, you must separate repeated items with acomma. required_item ,repeatable_item A repeat arrow above a stack indicates that you can repeat the items in DB2 UDB for AS/400 SQL ReferencexiiivKeywords appear in uppercase (for example,FROM). They must be spelled exactlyas shown. Variables appear in all lowercase letters (for example,column-name).They represent user-supplied names or punctuation marks, parentheses, arithmetic operators, or other such symbolsare shown, you must enter them as part of the for Describing Mixed Data ValuesWhen mixed data values are shown in the examples, the following conventionsapply:sbcs-stringdbcs-strings hift-outshift-inRepresents the EBCDIC control character (X )Represents the EBCDIC control character (X )Represents a string of zero or more single-byte charactersRepresents a string of zero or more double-byte charactersRepresents a DBCS apostrophe (EBCDIC X )Represents a DBCS G (EBCDIC X )0E0F427D42C7 ConventionMeaningWhat s new for V4R5 in the SQL Reference bookThe major new features covered in this book include.

10 VBIGINT data typevCOUNT_BIG column functionvATAN2, CEILING, DIFFERENCE, RADIANS, RAND, ROUND, SIGN,SOUNDEX, and TRUNCATE scalar functionsvJava ProceduresvExpressions in INSERT with a VALUES clausevSeveral SQL procedure and function enhancements (GET DIAGNOSTICS, GOTO, SIGNAL, RESIGNAL, RETURN in proceduresA vertical line (|) to the left of the text indicates a change or UDB for AS/400 SQL Reference V4R5 Chapter 1. ConceptsRelational DatabaseA relational database is a database that can be perceived as a set of tables and canbe manipulated in accordance with the relational model of data. The relationaldatabase contains a set of objects used to store, access, and manage data. The set ofobjects includes tables, views, indexes, and is only one relational database on any AS/400 system. It consists of all thedatabase objects stored locally on the AS/400 system.)