Transcription

DB2for Linux, UNIX, and Windows Version 9 Release 7Developing Embedded SQL ApplicationsUpdated July, 2012SC27-2445-02

DB2for Linux, UNIX, and Windows Version 9 Release 7Developing Embedded SQL ApplicationsUpdated July, 2012SC27-2445-02

NoteBefore using this information and the product it supports, read the general information under Appendix B, “Notices,” onpage 213.Edition NoticeThis document contains proprietary information of IBM. It is provided under a license agreement and is protectedby copyright law. The information contained in this publication does not include any product warranties, and anystatements provided in this manual should not be interpreted as such.You can order IBM publications online or through your local IBM representative.v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/orderv To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at www.ibm.com/planetwideTo order DB2 publications from DB2 Marketing and Sales in the United States or Canada, call 1-800-IBM-4YOU(426-4968).When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in anyway it believes appropriate without incurring any obligation to you. Copyright IBM Corporation 1993, 2012.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.

ContentsChapter 1. Introduction to embeddedSQL . . . . . . . . . . . . . . . . 1Embedding SQL statements in a host language . . .Embedded SQL statements in C and C applications. . . . . . . . . . . . . .Embedded SQL statements in FORTRANapplications. . . . . . . . . . . . . .Embedded SQL statements in COBOL applicationsEmbedded SQL statements in REXX applicationsSupported development software for embedded SQLapplications. . . . . . . . . . . . . . .Setting up the embedded SQL developmentenvironment . . . . . . . . . . . . . .2245688Chapter 2. Designing embedded SQLapplications . . . . . . . . . . . . . 9Authorization Considerations for Embedded SQL . . 9Static and dynamic SQL statement execution inembedded SQL applications . . . . . . . . . 10Embedded SQL dynamic statements . . . . . 10Determining when to execute SQL statementsstatically or dynamically in embedded SQLapplications . . . . . . . . . . . . . 11Performance of embedded SQL applications . . . 1332-bit and 64-bit support for embedded SQLapplications . . . . . . . . . . . . . . 14Restrictions on embedded SQL applications . . . 15Restrictions on character sets using C and C toprogram embedded SQL applications. . . . . 15Restrictions on using COBOL to programembedded SQL applications . . . . . . . . 15Restrictions on using FORTRAN to programembedded SQL applications . . . . . . . . 16Restrictions on using REXX to programembedded SQL applications . . . . . . . . 16Recommendations for developing embedded SQLapplications with XML and XQuery . . . . . 17Concurrent transactions and multi-threadeddatabase access in embedded SQL applications . . 17Recommendations for using multiple threads . . 19Code page and country or region codeconsiderations for multi-threaded UNIXapplications . . . . . . . . . . . . . 20Troubleshooting multi-threaded embedded SQLapplications . . . . . . . . . . . . . 20Chapter 3. Programming embeddedSQL applications . . . . . . . . . . 23Embedded SQL source files . . . . . . . . .Embedded SQL application template in C . . . .Include files and definitions required for embeddedSQL applications . . . . . . . . . . . .Include files for C and C embedded SQLapplications . . . . . . . . . . . . . Copyright IBM Corp. 1993, 201223242727Include files for COBOL embedded SQLapplications . . . . . . . . . . . . . 29Include files for FORTRAN embedded SQLapplications . . . . . . . . . . . . . 32Declaring the SQLCA for Error Handling . . . . 34Error handling using the WHENEVER Statement . . 35Connecting to DB2 databases in embedded SQLapplications . . . . . . . . . . . . . . 36Data types that map to SQL data types inembedded SQL applications . . . . . . . . . 37Supported SQL data types in C and C embedded SQL applications . . . . . . . . 38Supported SQL data types in COBOL embeddedSQL applications . . . . . . . . . . . 45Supported SQL data types in FORTRANembedded SQL applications . . . . . . . . 48Supported SQL data types in REXX embeddedSQL applications . . . . . . . . . . . 50Host Variables in embedded SQL applications . . . 52Declaring host variables in embedded SQLapplications . . . . . . . . . . . . . 54Declaring Host Variables with the db2dclgnDeclaration Generator . . . . . . . . . . 55Column data types and host variables inembedded SQL applications . . . . . . . . 55Declaring XML host variables in embedded SQLapplications . . . . . . . . . . . . . 56Identifying XML values in an SQLDA . . . . 58Identifying null SQL values with null indicatorvariables . . . . . . . . . . . . . . 58Including SQLSTATE and SQLCODE hostvariables in embedded SQL applications. . . . 60Referencing host variables in embedded SQLapplications . . . . . . . . . . . . . 60Example: Referencing XML host variables inembedded SQL applications . . . . . . . . 61Host variables in C and C embedded SQLapplications . . . . . . . . . . . . . 62Host variables in COBOL. . . . . . . . . 88Host variables in FORTRAN. . . . . . . . 99Host variables in REXX . . . . . . . . . 105Executing XQuery expressions in embedded SQLapplications . . . . . . . . . . . . . . 111Executing SQL statements in embedded SQLapplications . . . . . . . . . . . . . . 113Comments in embedded SQL applications . . . 113Executing static SQL statements in embeddedSQL applications . . . . . . . . . . . 114Retrieving host variable information from theSQLDA structure in embedded SQL applications 114Providing variable input to dynamicallyexecuted SQL statements by using parametermarkers . . . . . . . . . . . . . . 125Calling procedures in embedded SQLapplications . . . . . . . . . . . . . 127iii

Reading and scrolling through result sets inembedded SQL applications . . . . . . . 128Error message retrieval in embedded SQLapplications . . . . . . . . . . . . . 133Disconnecting from embedded SQL applications 136Chapter 4. Building embedded SQLapplications . . . . . . . . . . . . 139Precompilation of embedded SQL applications withthe PRECOMPILE command . . . . . . . .Precompilation of embedded SQL applicationsthat access more than one database server . . .Embedded SQL application packages and accessplans . . . . . . . . . . . . . . .Package schema qualification using CURRENTPACKAGE PATH special register . . . . . .Precompiler generated timestamps . . . . .Errors and warnings from precompilation ofembedded SQL applications . . . . . . .Compiling and linking source files containingembedded SQL . . . . . . . . . . . . .Binding embedded SQL packages to a databaseEffect of DYNAMICRULES bind option ondynamic SQL . . . . . . . . . . . .Using special registers to control the statementcompilation environment . . . . . . . .Package recreation using the BIND commandand an existing bind file. . . . . . . . .Rebinding existing packages with the REBINDcommand . . . . . . . . . . . . .Bind considerations . . . . . . . . . .Blocking considerations . . . . . . . . .Advantages of deferred binding . . . . . .Performance improvements when using REOPToption of the BIND command . . . . . . .Binding applications and utilities (DB2 Connectserver). . . . . . . . . . . . . . . .Package storage and maintenance . . . . . .Package versioning . . . . . . . . . .Resolution of unqualified table names . . . .Building embedded SQL applications using thesample build script . . . . . . . . . . .Error-checking utilities . . . . . . . . .ivDeveloping Embedded SQL Applications140Building applications and routines written in Cand C . . . . . . . . . . . . . .Building applications and routines written inCOBOL . . . . . . . . . . . . . .Building and running embedded SQLapplications written in REXX . . . . . . .Building embedded SQL applications from thecommand line . . . . . . . . . . . . .Building embedded SQL applications written inC or C (Windows) . . . . . . . . . .161175189191191142Chapter 5. Deploying and runningembedded SQL applications . . . . . 193142Use of the db2dsdriver.cfg configuration file byembedded SQL application . . . . . . . .Restrictions on linking to libdb2.so . . . . 58158160. 193. 195Chapter 6. Enabling compatibilityfeatures for migration . . . . . . . . 197Appendix A. Overview of the DB2technical information . . . . . . . . 201DB2 technical library in hardcopy or PDF formatOrdering printed DB2 books . . . . . . . .Displaying SQL state help from the command lineprocessor . . . . . . . . . . . . . . .Accessing different versions of the DB2Information Center . . . . . . . . . . .Displaying topics in your preferred language in theDB2 Information Center . . . . . . . . . .Updating the DB2 Information Center installed onyour computer or intranet server . . . . . . .Manually updating the DB2 Information Centerinstalled on your computer or intranet server . .DB2 tutorials . . . . . . . . . . . . .DB2 troubleshooting information . . . . . . .Terms and Conditions . . . . . . . . . .201204205205206206208209210210Appendix B. Notices . . . . . . . . 213Index . . . . . . . . . . . . . . . 217

Chapter 1. Introduction to embedded SQLEmbedded SQL database applications connect to databases and execute embeddedSQL statements. Embedded SQL statements are embedded within a host languageapplication. Embedded SQL database applications support the embedding of SQLstatements to be executed statically or dynamically.You can develop embedded SQL applications for DB2 in the following hostprogramming languages: C, C , COBOL, FORTRAN, and REXX.Note: Support for embedded SQL in FORTRAN and REXX has been deprecatedand will remain at the DB2 Universal Database , Version 5.2 level.Building embedded SQL applications involves two prerequisite steps prior toapplication compilation and linking.v Preparing the source files containing embedded SQL statements using the DB2precompiler.The PREP (PRECOMPILE) command is used to invoke the DB2 precompiler, whichreads your source code, parses and converts the embedded SQL statements toDB2 run-time services API calls, and finally writes the output to a new modifiedsource file. The precompiler produces access plans for the SQL statements, whichare stored together as a package within the database.v Binding the statements in the application to the target database.Binding is done by default during precompilation (the PREP command). Ifbinding is to be deferred (for example, running the BIND command later), thenthe BINDFILE option needs to be specified at PREP time in order for a bind file tobe generated.Once you have precompiled and bound your embedded SQL application, it isready to be compiled and linked using the host language-specific developmenttools.To aid in the development of embedded SQL applications, you can refer to theembedded SQL template in C. Examples of working embedded SQL sampleapplications can also be found in the %DB2PATH%\SQLLIB\samples directory.Note: %DB2PATH% refers to the DB2 installation directoryStatic and dynamic SQLSQL statements can be executed in one of two ways: statically or dynamically.Statically executed SQL statementsFor statically executed SQL statements, the syntax is fully known atprecompile time. The structure of an SQL statement must be completelyspecified for a statement to be considered static. For example, the namesfor the columns and tables referenced in a statement must be fully knownat precompile time. The only information that can be specified at run timeare values for any host variables referenced by the statement. However,host variable information, such as data types, must still be precompiled.You precompile, bind, and compile statically executed SQL statements Copyright IBM Corp. 1993, 20121

before you run your application. Static SQL is best used on databaseswhose statistics do not change a great deal.Dynamically executed SQL statementsDynamically executed SQL statements are built and executed by anapplication at run-time. An interactive application that prompts the enduser for key parts of an SQL statement, such as the names of the tables andcolumns to be searched, is a good example of a situation suited fordynamic SQL.Related information:Installing and configuring Optim Performance Manager Extended InsightEmbedding SQL statements in a host languageStructured Query Language (SQL) is a standardized language which can be usedto manipulate database objects and the data they contain. Despite differencesbetween host languages, embedded SQL applications are all made up of threemain elements which are required to setup and issue an SQL statement:1. A DECLARE SECTION for declaring host variables. The declaration of theSQLCA structure does not need to be in the DECLARE section.2. The main body of the application, which consists of the setup and execution ofSQL statements.3. Placements of logic that either commit or rollback the changes made by theSQL statements.For each host language, there are differences between the general guidelines, whichapply to all languages, and rules that are specific to individual languages.Embedded SQL statements in C and C applicationsEmbedded SQL C and C applications consist of three main elements to setupand issue an SQL statement.v A DECLARE SECTION for declaring host variables. The declaration of theSQLCA structure does not need to be in the DECLARE section.v The main body of the application, which consists of the setup and execution ofSQL statementsv Placements of logic that either commit or rollback the changes made by the SQLstatementsCorrect C and C Element SyntaxStatement initializerEXEC SQLStatement stringAny valid SQL statementStatement terminatorSemicolon (;)For example, to issue an SQL statement statically within a C application, youmight include the following within your application code:EXEC SQL SELECT col INTO :hostvar FROM table;The following example demonstrates how to issue an SQL statement dynamicallyusing the host variable stmt1:2Developing Embedded SQL Applications

strcpy(stmt1, "CREATE TABLE table1(col1 INTEGER)");EXEC SQL EXECUTE IMMEDIATE :stmt1;The following guidelines and rules apply to the execution of embedded SQLstatements in C and C applications:v You can begin the SQL statement string on the same line as the EXEC SQLstatement initializer.v Do not split the EXEC SQL between lines.v You must use the SQL statement terminator. If you do not use it, theprecompiler will continue to the next terminator in the application. This cancause indeterminate errors.v C and C comments can be placed before the statement initializer or after thestatement terminator.v Multiple SQL statements and C or C statements may be placed on the sameline. For example:EXEC SQL OPEN c1; if (SQLCODE 0) EXEC SQL FETCH c1 INTO :hv;v Carriage returns, line feeds, and TABs can be included within quoted strings.The SQL precompiler will leave these as is.v Do not use the #include statement to include files containing SQL statements.SQL statements are precompiled before the module is compiled. The precompilerwill ignore the #include statement. Instead, use the SQL INCLUDE statement toimport the include files.v SQL comments are allowed on any line that is part of an embedded SQLstatement, with the exception of dynamically issued statements.– The format for an SQL comment is a double dash (--), followed by a string ofzero or more characters, and terminated by a line end.– Do not place SQL comments after the SQL statement terminator. These SQLcomments cause compilation errors because compilers interpret them as C orC syntax.– You can use SQL comments in a static statement string wherever blanks areallowed.– The use of C and C comment delimiters /* */ are allowed in both staticand dynamic embedded SQL statements.– The use of //-style C comments are not permitted within static SQLstatementsv SQL string literals and delimited identifiers can be continued over line breaks inC and C applications. To do this, use a back slash (\) at the end of the linewhere the break is desired. For example, to select data from the NAME columnin the staff table where the NAME column equals 'Sanders' you could dosomething similar to the following:EXEC SQL SELECT "NA\ME" INTO :n FROM staff WHERE name ’Sa\nders’;Any new line characters (such as carriage return and line feed) are not includedin the string that is passed to the database manager as an SQL statement.v Substitution of white space characters, such as end-of-line and TAB characters,occurs as follows:– When they occur outside quotation marks (but inside SQL statements),end-of-lines and TABs are substituted by a single space.– When they occur inside quotation marks, the end-of-line characters disappear,provided the string is continued properly for a C program. TABs are notmodified.Chapter 1. Embedded SQL3

Note that the actual characters used for end-of-line and TAB vary from platformto platform. For example, UNIX and Linux based systems use a line feed.Embedded SQL statements in FORTRAN applicationsEmbedded SQL statements in FORTRAN applications consist of the following threeelements:Correct FORTRAN Element SyntaxStatement initializerEXEC SQLStatement stringAny valid SQL statement with blanks as delimitersStatement terminatorEnd of source line.The end of the source line serves as the statement terminator. If the line iscontinued, the statement terminator will then be the end of the last continued line.For example:EXEC SQL SELECT COL INTO :hostvar FROM TABLEThe following rules apply to embedded SQL statements in FORTRAN applications:v Code SQL statements between columns 7 and 72 only.v Use full-line FORTRAN comments, or SQL comments, but do not use theFORTRAN end-of-line comment '!' character in SQL statements. This commentcharacter may be used elsewhere, including host variable declarations.v Use blanks as delimiters when coding embedded SQL statements, even thoughFORTRAN statements do not require blanks as delimiters.v Use only one SQL statement for each FORTRAN source line. Normal FORTRANcontinuation rules apply for statements that require more than one source line.Do not split the EXEC SQL statement initializer between lines.v SQL comments are allowed on any line that is part of an embedded SQLstatement. These comments are not allowed in dynamically executed statements.The format for an SQL comment is a double dash (--), followed by a string ofzero or more characters and terminated by a line end.v FORTRAN comments are allowed almost anywhere within an embedded SQLstatement. The exceptions are:– Comments are not allowed between EXEC and SQL.– Comments are not allowed in dynamically executed statements.– The extension of using ! to code a FORTRAN comment at the end of a line isnot supported within an embedded SQL statement.v Use exponential notation when specifying a real constant in SQL statements. Thedatabase manager interprets a string of digits with a decimal point in an SQLstatement as a decimal constant, not a real constant.v Statement numbers are not valid on SQL statements that precede the firstexecutable FORTRAN statement. If an SQL statement has a statement numberassociated with it, the precompiler generates a labeled CONTINUE statementthat directly precedes the SQL statement.v Use host variables exactly as declared when referencing host variables within anSQL statement.v Substitution of white space characters, such as end-of-line and TAB characters,occurs as follows:4Developing Embedded SQL Applications

– When they occur outside quotation marks (but inside SQL statements),end-of-lines and TABs are substituted by a single space.– When they occur inside quotation marks, the end-of-line characters disappear,provi