Statements Table 1. SQL Statements(continued) SOL ment Function Pa SET EVENT MONITOR Activates or deactivates an event monitor 702 STATE SET INTEGRITY ets the check pending state and checks data for constraint violations SET PASSTHRU Opens a session for submitting data source native SQL directly 72 to the data source SET PATH Changes the value of the CURRENT PATH special register. SET SCHEMA Changes the value of the CURRENT SCHEMA special register. ET SERVER OPTIoN Sets server option setting SET Variable Assigns values to NEW transition variables. 733 UPDATE Updates the values of one or more columns in one or more 738 VALUES INTO ecifies a result table of no more than one row and assigns the values to host variables WHENEVER Defines actions to be taken on the basis of SQL return codes. 753 6 SQL Reference, Volume 2
Table 1. SQL Statements (continued) SQL Statement Function Page SET EVENT MONITOR STATE Activates or deactivates an event monitor. 702 SET INTEGRITY Sets the check pending state and checks data for constraint violations. 704 SET PASSTHRU Opens a session for submitting data source native SQL directly to the data source. 724 SET PATH Changes the value of the CURRENT PATH special register. 726 SET SCHEMA Changes the value of the CURRENT SCHEMA special register. 729 SET SERVER OPTION Sets server option settings. 731 SET Variable Assigns values to NEW transition variables. 733 UPDATE Updates the values of one or more columns in one or more rows of a table. 738 VALUES INTO Specifies a result table of no more than one row and assigns the values to host variables. 751 WHENEVER Defines actions to be taken on the basis of SQL return codes. 753 Statements 6 SQL Reference, Volume 2
How SQL statements are invoked How SQL statements are invoked SQL statements are classified as executable or non-executable An executable statement can be invoked in four ways. It can be Embedded in an application program Embedded in an SQL procedure Prepared and executed dynamically Depending on the statement, some or all of these methods can be used (Statements embedded in REXX are prepared and executed dynamically. A non-executable statement can only be embedded in an application program. Another SQL statement construct is the select-statement. A select-statement can be invoked in three ways. It can be Included in DECLARE CURSOR, and executed implicitly by OPEN, FETCH and CLOSE (static invocation) Prepared dynamically, referenced in DECLARE CURSOR, and executed implicitly by OPEN, FETCH and CLOSE (dynamic invocation) Issued interactively Embedding a statement in an application program SQL statements can be included in a source program that will be submitted to a precompiler. Such statements are said to be embedded in the program. An embedded statement can be placed anywhere in the program where a host language statement is allowed. Each embedded statement must be preceded by the keywords EXEC SQL. Executable statements An executable statement embedded in an application program is executed every time a statement of the host language would be executed if it were specified in the same place. Thus, a statement within a loop is executed every time the loop is executed, and a statement within a conditional construct is executed only when the condition is satisfied An embedded statement can contain references to host variables. a host variable referenced in this way can be used in two ways. It can be used As input(the current value of the host variable is used in the execution of the statement As output(the variable is assigned a new value as a result of executing the statement) Chapter 1. Statements 7
How SQL statements are invoked SQL statements are classified as executable or non-executable. An executable statement can be invoked in four ways. It can be: v Embedded in an application program v Embedded in an SQL procedure. v Prepared and executed dynamically v Issued interactively Depending on the statement, some or all of these methods can be used. (Statements embedded in REXX are prepared and executed dynamically.) A non-executable statement can only be embedded in an application program. Another SQL statement construct is the select-statement. A select-statement can be invoked in three ways. It can be: v Included in DECLARE CURSOR, and executed implicitly by OPEN, FETCH and CLOSE (static invocation) v Prepared dynamically, referenced in DECLARE CURSOR, and executed implicitly by OPEN, FETCH and CLOSE (dynamic invocation) v Issued interactively Embedding a statement in an application program SQL statements can be included in a source program that will be submitted to a precompiler. Such statements are said to be embedded in the program. An embedded statement can be placed anywhere in the program where a host language statement is allowed. Each embedded statement must be preceded by the keywords EXEC SQL. Executable statements An executable statement embedded in an application program is executed every time a statement of the host language would be executed if it were specified in the same place. Thus, a statement within a loop is executed every time the loop is executed, and a statement within a conditional construct is executed only when the condition is satisfied. An embedded statement can contain references to host variables. A host variable referenced in this way can be used in two ways. It can be used: v As input (the current value of the host variable is used in the execution of the statement) v As output (the variable is assigned a new value as a result of executing the statement) How SQL statements are invoked Chapter 1. Statements 7
Executable statements In particular, all references to host variables in expressions and predicates are effectively replaced by current values of the variables; that is, the variables are used as input All executable statements should be followed by a test of the SQL return code Alternatively, the WHENEVER statement(which is itself non-executable)can be used to change the flow of control immediately after the execution of an embedded statement All objects referenced in data manipulation language(DML)statements must exist when the statements are bound to a database Non-executable statements An embedded non-executable statement is processed only by the precompiler The precompiler reports any errors encountered in the statement. The statement is never processed during program execution; therefore, such statements should not be followed by a test of the SQL return code Embedding a statement in an SQL procedure Statements can be included in the SQL-procedure-body portion of the CREATE PROCEDURE Statement. Such statements are said to be embedded in the SQL procedure Whenever an SQL statement description refers to a host-variable, an SQl-variable can be used if the statement is embedded in an OL procedure Dynamic preparation and execution An application program can dynamically build an SQL statement in the form of a character string placed in a host variable. In general, the statement built from some data available to the program(for example, input from a workstation). The statement(not a select-statement) constructed can b prepared for execution by means of the(embedded)PREPARE statement, and (embedded) EXECUTE IMMEDIATE statement can be used to prepare and executed by means of the(embedded) EXECUTE statement. Alternatively, execute the statement in one step A statement that is going to be dynamically prepared must not contain references to host variables. It can instead contain parameter markers. (For rules concerning parameter markers, see"PREPARE". )When the prepared statement is executed, the parameter markers are effectively replaced by current values of the host variables specified in the EXECUTE statement. Once prepared, a statement can be executed several times with different values for the host variables Parameter markers are not allowed in the eXecute IMMEDIATE Statement Successful or unsuccessful execution of the statement is indicated by the setting of an SQL return code in the SQLCA after the EXECUTE (or EXECUTE 8SQL Reference, Volume 2
In particular, all references to host variables in expressions and predicates are effectively replaced by current values of the variables; that is, the variables are used as input. All executable statements should be followed by a test of the SQL return code. Alternatively, the WHENEVER statement (which is itself non-executable) can be used to change the flow of control immediately after the execution of an embedded statement. All objects referenced in data manipulation language (DML) statements must exist when the statements are bound to a database. Non-executable statements An embedded non-executable statement is processed only by the precompiler. The precompiler reports any errors encountered in the statement. The statement is never processed during program execution; therefore, such statements should not be followed by a test of the SQL return code. Embedding a statement in an SQL procedure Statements can be included in the SQL-procedure-body portion of the CREATE PROCEDURE statement. Such statements are said to be embedded in the SQL procedure. Whenever an SQL statement description refers to a host-variable, an SQL-variable can be used if the statement is embedded in an SQL procedure. Dynamic preparation and execution An application program can dynamically build an SQL statement in the form of a character string placed in a host variable. In general, the statement is built from some data available to the program (for example, input from a workstation). The statement (not a select-statement) constructed can be prepared for execution by means of the (embedded) PREPARE statement, and executed by means of the (embedded) EXECUTE statement. Alternatively, an (embedded) EXECUTE IMMEDIATE statement can be used to prepare and execute the statement in one step. A statement that is going to be dynamically prepared must not contain references to host variables. It can instead contain parameter markers. (For rules concerning parameter markers, see “PREPARE”.) When the prepared statement is executed, the parameter markers are effectively replaced by current values of the host variables specified in the EXECUTE statement. Once prepared, a statement can be executed several times with different values for the host variables. Parameter markers are not allowed in the EXECUTE IMMEDIATE statement. Successful or unsuccessful execution of the statement is indicated by the setting of an SQL return code in the SQLCA after the EXECUTE (or EXECUTE Executable statements 8 SQL Reference, Volume 2
Dynamic preparation and execution IMMEDIATE) statement completes. The SQL return code should be checked as described above. For more information, see"SQL return codes"on page 10 Static invocation of a select-statement A select-statement can be included as a part of the (non-executable DECLARE CURSOR statement. Such a statement is executed every time the cursor is opened by means of the(embedded)OPEN statement. After the cursor is open, the result table can be retrieved, one row at a time, by successive executions of the fetch statement Used in this way, the select-statement can contain references to host variables These references are effectively replaced by the values that the variables have when the open statement executes Dynamic invocation of a select-statemen An application program can dynamically build a select-statement in the form f a character string placed in a host variable. In general, the statement is built from some data available to the program(for example, a query obtained from a workstation). The statement so constructed can be prepared for execution by means of the(embedded) PREPARE statement, and referenced by a(non-executable)DECLARE CURSOR statement. The statement is then executed every time the cursor is opened by means of the(embedded)OPEN statement. After the cursor is open, the result table can be retrieved, one row at a time by successive executions of the Fetch statement Used in this way, the select-statement must not contain references to host variables. It can contain parameter markers instead. The parameter markers re effectively replaced by the values of the host variables specified in the Interactive invocation A capability for entering SQL statements from a workstation is part of the architecture of the database manager. A statement entered in this way is said to be issued interactively. Such a statement must be an executable statement that does not contain parameter markers or references to host variables, because these make sense only in the context of an application program. SQL use with other host systems QL statement syntax exhibits minor variations among different types of host systems(DB2 for z/OS, DB2 for iSeries, DB2 Universal Database). Regardless of whether the SQL statements in an application are static or dynamic, it important- if the application is meant to access different database host systems-to ensure that the SQL statements and precompile/bind options are supported on the database systems that the application will access Chapter 1. Statements 9
IMMEDIATE) statement completes. The SQL return code should be checked, as described above. For more information, see “SQL return codes” on page 10. Static invocation of a select-statement A select-statement can be included as a part of the (non-executable) DECLARE CURSOR statement. Such a statement is executed every time the cursor is opened by means of the (embedded) OPEN statement. After the cursor is open, the result table can be retrieved, one row at a time, by successive executions of the FETCH statement. Used in this way, the select-statement can contain references to host variables. These references are effectively replaced by the values that the variables have when the OPEN statement executes. Dynamic invocation of a select-statement An application program can dynamically build a select-statement in the form of a character string placed in a host variable. In general, the statement is built from some data available to the program (for example, a query obtained from a workstation). The statement so constructed can be prepared for execution by means of the (embedded) PREPARE statement, and referenced by a (non-executable) DECLARE CURSOR statement. The statement is then executed every time the cursor is opened by means of the (embedded) OPEN statement. After the cursor is open, the result table can be retrieved, one row at a time, by successive executions of the FETCH statement. Used in this way, the select-statement must not contain references to host variables. It can contain parameter markers instead. The parameter markers are effectively replaced by the values of the host variables specified in the OPEN statement. Interactive invocation A capability for entering SQL statements from a workstation is part of the architecture of the database manager. A statement entered in this way is said to be issued interactively. Such a statement must be an executable statement that does not contain parameter markers or references to host variables, because these make sense only in the context of an application program. SQL use with other host systems SQL statement syntax exhibits minor variations among different types of host systems (DB2 for z/OS, DB2 for iSeries, DB2 Universal Database). Regardless of whether the SQL statements in an application are static or dynamic, it is important — if the application is meant to access different database host systems — to ensure that the SQL statements and precompile/bind options are supported on the database systems that the application will access. Dynamic preparation and execution Chapter 1. Statements 9
SQL use with other host systems Further information about SQL statements used in other host systems can be found in the DB2 Universal Database for iSeries SQL Reference and the DB2 Universal Database for OS/390 and z/OS SQL Reference SQL return codes application program containing executable SQL statements can use either CODE or So E values to handle codes from SQL There are two ways in which an application can get access to these values Include a structure named SQLCA. The SQLCA includes an integer variable named SQLCODE and a character string variable named SQLSTATE. In REXX, an SQLCA is provided automatically. In other lan guages,an SQLCA can be obtained by using the INCLUDE SQLCA statement If LANGLEVEL SQL92E is specified as a precompile option, a variable named SQLCODE or SQLSTaTE can be declared in the SQL declare section of the program. If neither of these variables is declared in the SQL declare section, it is assumed that a variable named SQLCODE is declared elsewhere in the program. With LANGLEVEL SQL92E, the program should not have an INCLUDE SQLCA statement. An SQLCODE is set by the database manager after each SQL statement executes. All database managers conform to the ISO/ ANSI SQL standard, as follows If SQLCODE =0 and SQLWARNO is blank, execution was successful If SQLCODE 100, " no data"was found. For example, a FETCH statement returned no data, because the cursor was positioned after the last row of the result table If SQLCODE >0 and not= 100, execution was successful with a warning If SQLCODE=0 and SQLWARNO ='W, execution was successful, but one or more warning indicators were set. If SQLCODE <0, execution was not successful The meaning of SQLCODE values other than 0 and 100 is product-specific SQLSTATE An SQLStatE is set by the database manager after each SQL statement executes. Application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE SQLSTATE Provides common codes for common error conditions. Application programs can test for specific errors or classes of errors. The coding scheme is the same for all IBM database managers, and is based on the ISO/ ANSI SQL92 standard SQL comments Static SQL statements can include host language or SQL comments. SQL comments are introduced by two hyphens 10 SQL Reference, Volume 2
Further information about SQL statements used in other host systems can be found in the DB2 Universal Database for iSeries SQL Reference and the DB2 Universal Database for OS/390 and z/OS SQL Reference. SQL return codes An application program containing executable SQL statements can use either SQLCODE or SQLSTATE values to handle return codes from SQL statements. There are two ways in which an application can get access to these values. v Include a structure named SQLCA. The SQLCA includes an integer variable named SQLCODE and a character string variable named SQLSTATE. In REXX, an SQLCA is provided automatically. In other languages, an SQLCA can be obtained by using the INCLUDE SQLCA statement. v If LANGLEVEL SQL92E is specified as a precompile option, a variable named SQLCODE or SQLSTATE can be declared in the SQL declare section of the program. If neither of these variables is declared in the SQL declare section, it is assumed that a variable named SQLCODE is declared elsewhere in the program. With LANGLEVEL SQL92E, the program should not have an INCLUDE SQLCA statement. SQLCODE An SQLCODE is set by the database manager after each SQL statement executes. All database managers conform to the ISO/ANSI SQL standard, as follows: v If SQLCODE = 0 and SQLWARN0 is blank, execution was successful. v If SQLCODE = 100, “no data” was found. For example, a FETCH statement returned no data, because the cursor was positioned after the last row of the result table. v If SQLCODE > 0 and not = 100, execution was successful with a warning. v If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful, but one or more warning indicators were set. v If SQLCODE < 0, execution was not successful. The meaning of SQLCODE values other than 0 and 100 is product-specific. SQLSTATE An SQLSTATE is set by the database manager after each SQL statement executes. Application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE. SQLSTATE provides common codes for common error conditions. Application programs can test for specific errors or classes of errors. The coding scheme is the same for all IBM database managers, and is based on the ISO/ANSI SQL92 standard. SQL comments Static SQL statements can include host language or SQL comments. SQL comments are introduced by two hyphens. SQL use with other host systems 10 SQL Reference, Volume 2