Transcription
IBMIBM iDatabaseSQL programming7.1
IBMIBM iDatabaseSQL programming7.1
NoteBefore using this information and the product it supports, read the information in “Notices,” onpage 493.This edition applies to IBM i 7.1 (product number 5770-SS1) and to all subsequent releases and modifications untilotherwise indicated in new editions. This version does not run on all reduced instruction set computer (RISC)models nor does it run on CISC models. Copyright IBM Corporation 1998, 2010.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.
ContentsSQL programming . . . . . . . . . . 1 What's new for IBM i 7.1 . . . . . . . . . . 1PDF file for SQL programming . . . . . . . . 4Introduction to DB2 for i Structured Query Language 4SQL concepts . . . . . . . . . . . . . 5SQL relational database and systemterminology . . . . . . . . . . . . 6SQL and system naming conventions . . . . 7Types of SQL statements . . . . . . . . 7SQL communication area . . . . . . . . 9SQL diagnostics area. . . . . . . . . . 9SQL objects . . . . . . . . . . . . . . 9Schemas . . . . . . . . . . . . . . 9Journals and journal receivers . . . . . . 9Catalogs . . . . . . . . . . . . . 10Tables, rows, and columns . . . . . . . 10Aliases . . . . . . . . . . . . . . 10Views . . . . . . . . . . . . . . 10Indexes . . . . . . . . . . . . . . 11Constraints . . . . . . . . . . . . 11Triggers . . . . . . . . . . . . . 12Stored procedures . . . . . . . . . . 12Sequences . . . . . . . . . . . . . 12Global variables . . . . . . . . . . . 12User-defined functions. . . . . . . . . 12User-defined types . . . . . . . . . . 13XSR objects . . . . . . . . . . . . 13SQL packages . . . . . . . . . . . 13Application program objects . . . . . . . . 13User source file . . . . . . . . . . . 15Output source file member . . . . . . . 15Program . . . . . . . . . . . . . 15SQL package . . . . . . . . . . . . 15Module . . . . . . . . . . . . . . 16Service program . . . . . . . . . . . 16Data definition language . . . . . . . . . . 16Creating a schema . . . . . . . . . . . 16Creating a table . . . . . . . . . . . . 17Adding and removing constraints . . . . . 17Referential integrity and tables . . . . . . 18Adding and removing referentialconstraints. . . . . . . . . . . . 18Example: Adding referential constraints . . 19Example: Removing constraints. . . . . . 20Check pending . . . . . . . . . . . 20Creating a table using LIKE . . . . . . . . 21Creating a table using AS. . . . . . . . . 21Creating and altering a materialized query table 22Declaring a global temporary table . . . . . 23Creating a table with remote server data . . . 23Creating a row change timestamp column . . . 24Creating and altering an identity column . . . 24Using ROWID . . . . . . . . . . . . 25Creating and using sequences . . . . . . . 26Comparison of identity columns andsequences . . . . . . . . . . . . . 27 Copyright IBM Corp. 1998, 2010 Defining field procedures. . . . . . . .Field definition for field procedures . . .Specifying the field procedure . . . . .When field procedures are invoked . . .Parameter list for execution of fieldprocedures . . . . . . . . . . .The field procedure parameter value list(FPPVL) . . . . . . . . . . .Parameter value descriptors for fieldprocedures . . . . . . . . . .Field-definition (function code 8) . . .Field-encoding (function code 0) . . .Field-decoding (function code 4) . . .Example field procedure program . . .General guidelines for writing fieldprocedures . . . . . . . . . . .Index considerations . . . . . . .Thread considerations . . . . . . .Debug considerations . . . . . . .Guidelines for writing field procedures thatmask data . . . . . . . . . . . .Example field procedure program thatmasks data . . . . . . . . . .Creating descriptive labels using the LABEL ONstatement . . . . . . . . . . . . .Describing an SQL object using COMMENT ONChanging a table definition . . . . . . .Adding a column . . . . . . . . .Changing a column. . . . . . . . .Allowable conversions of data types . . .Deleting a column . . . . . . . . .Order of operations for the ALTER TABLEstatement . . . . . . . . . . . .Using CREATE OR REPLACE TABLE . .Creating and using ALIAS names . . . . .Creating and using views. . . . . . . .WITH CHECK OPTION on a view . . .WITH CASCADED CHECK OPTION .WITH LOCAL CHECK OPTION . . .Example: Cascaded check option . . .Creating indexes. . . . . . . . . . .Creating and using global variables . . . .Replacing existing objects. . . . . . . .Catalogs in database design . . . . . . .Getting catalog information about a table .Getting catalog information about a columnDropping a database object . . . . . . .Data manipulation language. . . . . . . .Retrieving data using the SELECT statement .Basic SELECT statement . . . . . . .Specifying a search condition using theWHERE clause . . . . . . . . . .Expressions in the WHERE clause . . .Comparison operators . . . . . . .NOT keyword . . . . . . . . .GROUP BY clause . . . . . . . . .28292929. 30. 32.3233343536.37383838. 38. 41. 4243. 44. 44. 44. 45. iii
HAVING clause . . . . . . . . . . . 62ORDER BY clause . . . . . . . . . . 63Static SELECT statements. . . . . . . . 65Handling null values . . . . . . . . . 65Special registers in SQL statements . . . . 66Casting data types . . . . . . . . . . 68Date, time, and timestamp data types . . . 68Specifying current date and time values . . 68Date/time arithmetic . . . . . . . . 69Row change expressions . . . . . . . . 69Handling duplicate rows . . . . . . . . 69Defining complex search conditions . . . . 70Special considerations for LIKE . . . . . 71Multiple search conditions within aWHERE clause . . . . . . . . . . 72Using OLAP specifications . . . . . . . 73Joining data from more than one table . . . 76Inner join . . . . . . . . . . . . 76Left outer join . . . . . . . . . . 77Right outer join . . . . . . . . . . 78Exception join . . . . . . . . . . 78Cross join . . . . . . . . . . . . 79Full outer join . . . . . . . . . . 80Multiple join types in one statement . . . 81Using table expressions . . . . . . . . 81Using recursive queries . . . . . . . . 83Using the UNION keyword to combinesubselects . . . . . . . . . . . . . 95Specifying the UNION ALL keyword. . . 98Using the EXCEPT keyword . . . . . . 100Using the INTERSECT keyword . . . . . 102Data retrieval errors . . . . . . . . . 104Inserting rows using the INSERT statement . . 105Inserting rows using the VALUES clause . . 106Inserting rows using a select-statement . . . 107Inserting multiple rows using the blockedINSERT statement . . . . . . . . . . 108Inserting data into tables with referentialconstraints . . . . . . . . . . . . 108Inserting values into an identity column . . 109Selecting inserted values. . . . . . . . 110Inserting data from a remote database . . . 110Changing data in a table using the UPDATEstatement . . . . . . . . . . . . . . 111Updating a table using a scalar-subselect . . 112Updating a table with rows from anothertable . . . . . . . . . . . . . . 112Updating tables with referential constraints113Examples: UPDATE rules . . . . . . 114Updating an identity column . . . . . . 114Updating data as it is retrieved from a table 114Removing rows from a table using the DELETEstatement . . . . . . . . . . . . . . 116Removing rows from tables with referentialconstraints . . . . . . . . . . . . 117Example: DELETE rules . . . . . . . 118Merging data . . . . . . . . . . . . 119Using subqueries . . . . . . . . . . . 120Subqueries in SELECT statements . . . . 121Subqueries and search conditions. . . . 122Usage notes on subqueries . . . . . . 122 ivIBM i: Database SQL programming Including subqueries in the WHERE orHAVING clause . . . . . . . . .Correlated subqueries . . . . . . . .Correlated names and references . . . .Example: Correlated subquery in aWHERE clause . . . . . . . . . .Example: Correlated subquery in aHAVING clause . . . . . . . . .Example: Correlated subquery in aselect-list . . . . . . . . . . . .Example: Correlated subquery in anUPDATE statement . . . . . . . .Example: Correlated subquery in aDELETE statement . . . . . . . .Sort sequences and normalization in SQL . . . .Sort sequence used with ORDER BY and rowselection . . . . . . . . . . . . . .Sort sequence and ORDER BY . . . . . .Sort sequence and row selection . . . . .Sort sequence and views . . . . . . . .Sort sequence and the CREATE INDEXstatement. . . . . . . . . . . . . .Sort sequence and constraints . . . . . . .ICU sort sequence . . . . . . . . . . .Normalization . . . . . . . . . . . .Data protection . . . . . . . . . . . . .Security for SQL objects . . . . . . . . .Authorization ID . . . . . . . . . .Views . . . . . . . . . . . . . .Auditing . . . . . . . . . . . . .Data integrity . . . . . . . . . . . .Concurrency. . . . . . . . . . . .Journaling . . . . . . . . . . . .Commitment control . . . . . . . . .Savepoints . . . . . . . . . . . .Atomic operations . . . . . . . . . .Constraints . . . . . . . . . . . .Adding and using check constraints . . .Save and restore functions . . . . . . .Damage tolerance . . . . . . . . . .Index recovery . . . . . . . . . . .Catalog integrity . . . . . . . . . .User auxiliary storage pool . . . . . . .Independent auxiliary storage pool . . . .Routines . . . . . . . . . . . . . . .Stored procedures . . . . . . . . . . .Defining an external procedure . . . . .Defining an SQL procedure. . . . . . .Defining a procedure with defaultparameters . . . . . . . . . . . .Calling a stored procedure . . . . . . .Using the CALL statement whereprocedure definition exists . . . . . .Using the embedded CALL statementwhere no procedure definition exists . .Using the embedded CALL statementwith an SQLDA . . . . . . . . .Using the dynamic CALL statement whereno CREATE PROCEDURE exists . . . .Examples: CALL statements . . . . .Returning result sets from stored 1167
Example 1: Calling a stored procedurethat returns a single result set . . . . .Example 2: Calling a stored procedurethat returns a result set from a nestedprocedure . . . . . . . . . . .Writing a program or SQL procedure toreceive the result sets from a storedprocedure . . . . . . . . . . . .Parameter passing conventions for storedprocedures and user-defined functions . . .Indicator variables and stored proceduresReturning a completion status to the callingprogram . . . . . . . . . . . . .Passing parameters from DB2 to externalproceduress . . . . . . . . . . . .Parameter style SQL . . . . . . . .Parameter style GENERAL . . . . . .Parameter style GENERAL WITH NULLSParameter style DB2GENERAL . . . .Parameter style Java . . . . . . . .Using user-defined functions . . . . . . .UDF concepts . . . . . . . . . . .Writing UDFs as SQL functions . . . . .Example: SQL scalar UDFs . . . . . .Example: SQL table UDFs . . . . . .Writing UDFs as external functions . . . .Registering UDFs . . . . . . . . .Passing arguments from DB2 to externalfunctions . . . . . . . . . . . .Table function considerations . . . . .Error processing for UDFs . . . . . .Threads considerations . . . . . . .Parallel processing. . . . . . . . .Fenced or unfenced considerations . . .Save and restore considerations . . . .Examples: UDF code . . . . . . . . .Example: Square of a number UDF . . .Example: Counter . . . . . . . . .Example: Weather table function . . . .Using UDFs in SQL statements . . . . .Using parameter markers or the NULLvalues as function arguments . . . . .Using qualified function references . . .Using unqualified function references . .Summary of function references . . . .Triggers . . . . . . . . . . . . . .SQL triggers . . . . . . . . . . . .BEFORE SQL triggers . . . . . . .AFTER SQL triggers . . . . . . . .Multiple event SQL triggers . . . . .INSTEAD OF SQL triggers . . . . . .Handlers in SQL triggers . . . . . .SQL trigger transition tables . . . . .External triggers . . . . . . . . . .Array support in SQL procedures . . . . .Debugging an SQL routine . . . . . . . .Obfuscating an SQL routine . . . . . . .Managing SQL and external routine objects . .Improving performance of procedures andfunctions . . . . . . . . . . . . . 215216217218219220221222224225225226227228230231 Improving implementation of procedures andfunctions . . . . . . . . . . . . .Redesigning routines for performance . . .Processing special data types . . . . . . . .Large objects . . . . . . . . . . . .Large object data types . . . . . . . .Large object locators . . . . . . . . .Example: Using a locator to work with aCLOB value . . . . . . . . . . . .Example: LOBLOC in C . . . . . . .Example: LOBLOC in COBOL . . . . .Indicator variables and LOB locators . . .LOB file reference variables . . . . . .Example: Extracting CLOB data to a file . .Example: LOBFILE in C . . . . . . .Example: LOBFILE in COBOL . . . . .Example: Inserting data into a CLOB columnDisplaying the layout of LOB columns . . .Journal entry layout of LOB columns . . .User-defined distinct types . . . . . . . .Defining a UDT . . . . . . . . . .Example: Money . . . . . . . . .Example: Resumé . . . . . . . . .Defining tables with UDTs . . . . . . .Example: Sales . . . . . . . . . .Example: Application forms . . . . .Manipulating UDTs . . . . . . . . .Examples: Using UDTs . . . . . . . .Example: Comparisons between UDTs andconstants . . . . . . . . . . . .Example: Casting between UDTs . . . .Example: Comparisons involving UDTsExample: Sourced UDFs involving UDTsExample: Assignments involving UDTsExample: Assignments in dynamic SQLExample: Assignments involving differentUDTs . . . . . . . . . . . . .Example: Using UDTs in UNION. . . .Examples: Using UDTs, UDFs, and LOBs . . .Example: Defining the UDT and UDFs . . .Example: Using the LOB function topopulate the database . . . . . . . .Example: Using UDFs to query instances ofUDTs . . . . . . . . . . . . . .Example: Using LOB locators to manipulateUDT instances . . . . . . . . . . .Using DataLinks . . . . . . . . . . .Linking control levels in DataLinks . . . .NO LINK CONTROL . . . . . . .FILE LINK CONTROL with FSpermissions . . . . . . . . . . .FILE LINK CONTROL with DBpermissions . . . . . . . . . . .Working with DataLinks . . . . . . .SQL statements and SQL/XML functions . . . .XML input and output overview . . . . . .Comparison of XML and relational models . .Tutorial for XML . . . . . . . . . . .Exercise 1: Creating a table that can storeXML data . . . . . . . . . . . 259261261v
Exercise 2: Inserting XML documents intoXML typed columns . . . . . . . . .Exercise 3: Updating XML documents storedin an XML column . . . . . . . . .Exercise 4: Validating XML documentsagainst XML schemas . . . . . . . .Exercise 5: Transforming with XSLTstylesheets . . . . . . . . . . . .Inserting XML data . . . . . . . . . .Addition of XML columns to existing tablesInsertion into XML columns . . . . . .XML parsing . . . . . . . . . . .SQL/XML publishing functions for constructingXML values . . . . . . . . . . . . .Example: Construct an XML document withvalues from a single table . . . . . . .Example: Construct an XML document withvalues from multiple tables. . . . . . .Example: Construct an XML document withvalues from table rows that contain nullelements . . . . . . . . . . . . .Example: Transforming with XSLT stylesheetsExample: Using XSLT as a formatting engineExample: Using XSLT for data exchange . .Example: Using XSLT to remove namespacesImportant considerations for transformingXML documents . . . . . . . . . .Special character handling in SQL/XMLpublishing functions . . . . . . . . .XML serialization . . . . . . . . . .Differences in an XML document afterstorage and retrieval . . . . . . . . .Data types for archiving XML documentsUsing XMLTABLE to reference XML content asa relational table . . . . . . . . . . .Example: Use XMLTABLE to handle missingelements . . . . . . . . . . . . .Example: Use XMLTABLE to subset resultdata . . . . . . . . . . . . . .Example: Use XMLTABLE to handle multiplevalues . . . . . . . . . . . . . .Example: Use XMLTABLE with namespacesExample: Number result rows forXMLTABLE . . . . . . . . . . . .Updating XML data . . . . . . . . . .Deletion of XML data from tables . . . .XML schema repository . . . . . . . . .Application programming language support . .XML column inserts and updates in CLIapplications . . . . . . . . . . . .XML data retrieval in CLI applications . . .Declaring XML host variables in embeddedSQL applications . . . . . . . . . .Example: Referencing XML host variablesin embedded SQL applications . . . .Recommendations for developingembedded SQL applications with XML . .Identifying XML values in an SQLDA . .Java . . . . . . . . . . . . . .XML data in JDBC applications . . . .XML data in SQLJ applications . . . .viIBM i: Database SQL 3294294295296297298298298298304 Routines . . . . . . . . . . . . .XML support in SQL procedures . . . .XML data type support in externalroutines . . . . . . . . . . . .XML data encoding . . . . . . . . . .Encoding considerations when storing orpassing XML data . . . . . . . . . .Encoding considerations for input of XMLdata to a database . . . . . . . . .Encoding considerations for retrieval ofXML data from a database . . . . . .Encoding considerations for passing XMLdata in routine parameters . . . . . .Encoding considerations for XML data inJDBC and SQLJ applications . . . . .Effects of XML encoding and serialization ondata conversion . . . . . . . . . .Encoding scenarios for input of internallyencoded XML data to a database . . . .Encoding scenarios for input of externallyencoded XML data to a database . . . .Encoding scenarios for retrieval of XMLdata with implicit serialization . . . .Encoding scenarios for retrieval of XMLdata with explicit XMLSERIALIZE . . .Mappings of encoding names to effectiveCCSIDs for stored XML data . . . . . .Mappings of CCSIDs to encoding names forserialized XML output data. . . . . . .Annotated XML schema decomposition . . .Decomposing XML documents withannotated XML schemas. . . . . . . .Registering and enabling XML schemas fordecomposition . . . . . . . . . . .Sources for annotated XML schemadecomposition . . . . . . . . . . .XML decomposition annotations . . . . .Specification and scope of XMLdecomposition annotations . . . . . .Annotations as attributes . . . . . .Annotations as structured child elementsGlobal annotations . . . . . . . .XML decomposition annotations Summary. . . . . . . . . . . .db2-xdb:defaultSQLSchema decompositionannotation . . . . . . . . . . .db2-xdb:rowSet decomposition annotationdb2-xdb:table decomposition annotationdb2-xdb:column decomposition annotationdb2-xdb:locationPath decompositionannotation . . . . . . . . . . .db2-xdb:expression decompositionannotation . . . . . . . . . . .db2-xdb:condition decompositionannotation . . . . . . . . . . .db2-xdb:contentHandling decompositionannotation . . . . . . . . . . .db2-xdb:normalization decompositionannotation . . . . . . . . . . .db2-xdb:order decomposition 335338341344348350
db2-xdb:truncate decompositionannotation . . . . . . . . . . .db2-xdb:rowSetMapping decompositionannotation . . . . . . . . . . .db2-xdb:rowSetOperationOrderdecomposition annotation . . . . . .Keywords for annotated XML schemadecomposition . . . . . . . . . .Treatment of CDATA sections in annotatedXML schema decomposition . . . . . .NULL values and empty strings in annotatedXML schema decomposition . . . . . .Checklist for annotated XML schemadecomposition . . . . . . . . . . .Examples of mappings in annotated XMLschema decomposition . . . . . . . . .Annotations of derived complex types . . .Decomposition annotation example: Mappingto an XML column . . . . . . . . .Decomposition annotation example: A valuemapped to a single table that yields a singlerow . . . . . . . . . . . . . .Decomposition annotation example: A valuemapped to a single table that yields multiplerows . . . . . . . . . . . . . .Decomposition annotation example: A valuemapped to multiple tables . . . . . . .Decomposition annotation example:Grouping multiple values mapped to a singletable . . . . . . . . . . . . . .Decomposition annotation example: Multiplevalues from different contexts mapped to asingle table . . . . . . . . . . . .XML schema to SQL types compatibility forannotated schema decomposition. . . . .Limits and restrictions for annotated XMLschema decomposition . . . . . . . .Schema for XML decomposition annotationsUsing SQL in different environments . . . . .Using a cursor . . . . . . . . . . . .Types of cursors . . . . . . . . . .Examples: Using a cursor . . . . . . .Step 1: Defining the cursor . . . . . .Step 2: Opening the cursor . . . . . .Step 3: Specifying what to do when theend of data is reached . . . . . . .Step 4: Retrieving a row using a cursorStep 5a: Updating the current row . . .Step 5b: Deleting the current row. . . .Step 6: Closing the cursor . . . . . .Using the multiple-row FETCH statementMultiple-row FETCH using a hoststructure array . . . . . . . . . .Multiple-row FETCH using a row storagearea . . . . . . . . . . . . .Unit of work and open cursors . . . . .Dynamic SQL applications . . . . . . . .Designing and running a dynamic SQLapplication . . . . . . . . . . . .CCSID of dynamic SQL statements . . . .Processing non-SELECT statements . . . 395395396396 Using the PREPARE and EXECUTEstatements . . . . . . . . . . .Processing SELECT statements and using adescriptor . . . . . . . . . . . .Fixed-list SELECT statements . . . . .Varying-list SELECT statements . . . .SQL descriptor areas . . . . . . . .SQLDA format . . . . . . . . . .Example: A SELECT statement forallocating storage for SQLDA . . . . .Example: A SELECT statement using anallocated SQL descriptor. . . . . . .Parameter markers . . . . . . . .Using interactive SQL . . . . . . . . .Starting interactive SQL . . . . . . . .Using the statement entry function . . . .Prompting . . . . . . . . . . . .Syntax checking . . . . . . . . .Statement processing mode. . . . . .Subqueries . . . . . . . . . . .CREATE TABLE prompting . . . . .Entering DBCS data . . . . . . . .Using the list selection function . . . . .Example: Using the list selection functionSession services description . . . . . .Exiting interactive SQL . . . . . . . .Using an existing SQL session . . . . . .Recovering an SQL session . . . . . . .Accessing remote databases with interactiveSQL . . . . . . . . . . . . . .Using the SQL statement processor . . . . .Execution of statements after errors occurCommitment control in the SQL statementprocessor . . . . . . . . . . . . .Source listing for the SQL statementprocessor . . . . . . . . . . . . .Using the RUNSQL CL command . . . . .Distributed relational database function and SQLDB2 for i distributed relational database supportDB2 for i distributed relational databaseexample program . . . . . . . . . . .SQL package support. . . . . . . . . .Valid SQL statements in an SQL package . .Considerations for creating an SQL packageCRTSQLPKG authorization . . . . . .Creating a package on a database otherthan DB2 for i . . . . . . . . . .Target release (TGTRLS) parameter . . .SQL statement size . . . . . . . .Statements that do not require a packagePackage object type . . . . . . . .ILE programs and service programs . . .Package creation connection . . . . .Unit of work . . . . . . . . . .Creating packages locally . . . . . .Labels . . . . . . . . . . . . .Consistency token . . . . . . . . .SQL and recursion. . . . . . . . .CCSID considerations for SQL. . . . . . .Connection management and activation groupsSource code for PGM1 . . . . . . . 432433433433434vii
Source code for PGM2 . . . . . . . .Source code for PGM3 . . . . . . . .Multiple connections to the same relationaldatabase . . . . . . . . . . . . .Implicit connection management for thedefault activation group . . . . . . . .Implicit connection management fornondefault activation groups . . . . . .Distributed support . . . . . . . . . .Determining the connection type . . . . .Connect and commitment control restrictionsDetermining the connection status . . . .Distributed unit of work connectionconsiderations . . . . . . . . . . .Ending connections . . . . . . . . .Distributed unit of work. . . . . . . . .Managing distributed unit of workconnections . . . . . . . . . . . .Checking the connection status . . . . .Cursors and prepared statements . . . . .DRDA stored procedure considerations. . . .WebSphere MQ with DB2 . . . . . . . . .WebSphere MQ messages . . . . . . . .WebSphere MQ message handling . . . .DB2 MQ services . . . . . . . . .DB2 MQ policies . . . . . . . . .DB2 MQ functions. . . . . . . . . . .DB2 MQ dependencies . . . . . . . .DB2 MQ tables . . . . . . . . . . . .DB2 MQ CCSID conversion . . . . . . .Websphere MQ transactions . . . . . . .Basic messaging with WebSphere MQ . . . .Sending messages with WebSphere MQ . . .Retrieving messages with WebSphere MQ . . .Application to application connectivity withWebSphere MQ. . . . . . . . . . . .Reference. . . . . . . . . . . . . . .DB2 for i sample tables . . . . . . . . .Department table (DEPARTMENT) . . . .DEPARTMENT . . . . . . . . . .Employee table (EMPLOYEE) . . . . . .EMPLOYEE . . . . . . . . . . .Employee photo table (EMP PHOTO) . . .EMP PHOTO . . . . . . . . . .viiiIBM i: Database SQL 3463463464465465466467468 Employee resumé table (EMP RESUME) .EMP RESUME . . . . . . . . .Employee to project activity table(EMPPROJACT) . . . . . . . . .EMPPROJACT . . . . . . . . .Project table (PROJECT) . . . . . . .PROJECT. . . . . . . . . . .Project activity table (PROJACT) . . . .PROJACT . . . . . . . . . .Activity table (ACT) . . . . . . . .ACT . . . . . . . . . . . .Class schedule table (CL SCHED) . . .CL SCHED . . . . . . . . . .In-tray table (IN TRAY) . . . . . . .IN TRAY . . . . . . . . . . .Organization table (ORG) . . . . . .ORG . . . . . . . . . . . .Staff table (STAFF) . . . . . . . .STAFF . . . . . . . . . . . .Sales table (SALES) . . . . . . . .SALES. . . . . . . . . . . .Sample XML tables . . . . . . . .Product table (PRODUCT) . . . . . .PRODUCT . . . . . . . . . .Purchase order table (PURCHASEORDER)PURCHASEORDER . . . . . . .Customer table (CUSTOMER) . . . . .CUSTOMER . . . . . . . . . .Catalog table (CATALOG) . . . . . .CATALOG . . . . . . . . . .Suppliers table (SUPPLIERS) . . . . .SUPPLIERS . . . . . . . . . .Inventory table (INVENTORY) . . . .INVENTORY . . . . . . . . .Product Supplier table(PRODUCTSUPPLIER) . . . . . . .PRODUCTSUPPLIER. . . . . . .DB2 for i CL command descriptions . . . . 468. 80481481482482483484485487488489489489490490490. 490. 491. 491Appendix. Notices . . . . . . . . . 493Programming interface information .Trademarks . . . . . . . . .Terms and conditions. . . . . . 494. 495. 495
SQL programmingThe DB2 for IBM i database provides a wide range of support for Structured Query Language (SQL).The examples of SQL statements shown in this topic collection are based on the sample tables andassume that the following statements are true:v They are shown in the interactive SQL environment or they are written in ILE C or in COBOL. EXECSQL and END-EXEC are used to delimit an SQL statement in a COBOL program.v Each SQL example is shown on several lines, with each clause of the statement on a separate line.v SQL keywords are highlighted.v Table names provided in the sample tables use the schema CORPDATA. Table names that are notfound in the Sample Tables should use schemas you create.v Calculated columns are enclosed in parentheses, (), and brackets, [].v The SQL naming convention is used.v The APOST and APOSTSQL precompiler options are assumed although they are not the defaultoptions in COBOL. Character string literals within SQL and host language statements are delimited bysingle-quotation marks (').v A sort sequence of *HEX is used, unless otherwise noted.Whenever the examples vary from these assumptions, it is stated.Because this topic collection is for the application programmer, most of the examples are shown as if theywere written in an application program. However, many examples can be slightly changed and runinteractively by using interactive SQL. The syntax of an SQL statement, when using interactive SQL,differs slightly from the format of the same statement when it is embedded in a program.Note: By using the code examples, you agree to the terms of the “Code license and disclaimerinformation” on page 491.Related concepts:Embedded SQL programmingRelated reference:“DB2 for i sample tables” on page 463These sample tables are referred to and used in the SQL programming and the SQL reference topiccollections.DB2 for i5/OS SQL reference What's new for IBM i 7.1 Read about new or signifi
PDF file for SQL pr ogramming . . 4 Intr oduction to DB2 for i Str uctur ed Query Language 4 SQL concepts . . 5 SQL r elational database and system . Data manipulation language . . 56 Retrieving data using the
