UL/DB2 and SQL statements

Overview

This topic describes the specific SQL statements that you can use in a User Language procedure, and the rules and guidelines that apply when using those statements.

The topic also describes the $SPIFY interface, which lets a User Language programmer test an SQL statement before it is used in a procedure.

Prerequisite knowledge of SQL

This topic assumes that you are familiar with SQL syntax. It does not explain what a particular SQL statement does; rather, it explains how you use that statement in a User Language procedure.

Requirements for writing SQL statements

This section describes the general requirements that apply when you code an SQL statement in a User Language procedure.

Components of an SQL statement in a User Language procedure

An SQL statement can contain constants, SQL column names, and Model 204 %variables. An SQL statement cannot contain image names, screen names, or Model 204 field names.

EXEC DB2... END EXEC delimiters

You must precede each SQL statement in a User Language procedure with the keywords "EXEC DB2", and you must end each SQL statement with the keywords "END EXEC".

You can put the EXEC DB2 and END EXEC on a separate line from the rest of the statement, or you can include it on the same line. You cannot, however, break either the EXEC DB2 or the END EXEC strings onto more than one line.

Two valid SQL statements are shown below (the indentation has been added for legibility):

EXEC DB2 COMMIT WORK END EXEC

EXEC DB2 FETCH P INTO %POLN END EXEC

Continuing SQL statements

You do not need to use a continuation character to continue an SQL statement onto the following line.

Examples

EXEC DB2 DECLARE FOO CURSOR FOR SELECT S#,SNAME,STATUS,CITY FROM S END EXEC

You can continue a literal by placing a hyphen at the end of the line:

EXEC DB2 DELETE FROM DVPJB.S WHERE NAME = 'ALFRED E. - NEWMAN' END EXEC

Using quotation marks

Use single quotation marks (') around literal text in an SQL statement.

Example

EXEC DB2 DELETE FROM DVPJB.S WHERE NAME = 'ALFRED E. - NEWMAN' END EXEC

Note: This example assumes the default value of the DB2QUOTE parameter, which is an apostrophe ('). If your site has changed the value of DB2QUOTE, use that value instead.

SQL comments not supported

The UL/DB2 Interface does not support SQL comments.

Using multiple SQL statements in a procedure

You can have any number of SQL statements in a single User Language procedure. However, you can code only one SQL statement in each EXEC DB2... END EXEC block.

Example

EXEC DB2 SQL statement END EXEC ... EXEC DB2 SQL statement statement continued END EXEC ... EXEC DB2 SQL statement END EXEC

Checking $STATUS

Check $STATUS after each EXEC DB2... END EXEC block to make sure that the processing completed correctly. See $STATUS and $STATUSD for more information on checking $STATUS in a User Language procedure.

Connecting a User Language procedure to DB2

This section describes the CONNECT TO and DISCONNECT FROM statements that you use to create and terminate a connection, or thread, between a User Language procedure and DB2.

Strictly speaking, CONNECT TO and DISCONNECT FROM are not SQL statements, but Call Attach Facility calls. However, the same rules apply to using these statements in a User Language procedure that were described in the section Requirements for writing SQL statements.

You issue the CONNECT TO and DISCONNECT FROM statements relative to the way that you have defined the process for your UL/DB2 environment. For more information, see DEFINE PROCESS command: User Language to DB2.

CONNECT TO statement

Use the CONNECT TO statement to create a thread between a User Language procedure and DB2.

CONNECT TO must be the first EXEC DB2 statement in the procedure. If CONNECT TO is not the first EXEC DB2 statement in a procedure, the results are unpredictable.

Syntax

EXEC DB2 CONNECT TO symbolic_name END EXEC

where:

symbolic_name is the symbolic name from the DESTINATION parameter of the DEFINE PROCESS command.

Errors connecting to DB2

If the CONNECT TO statement within a User Language procedure fails, Model 204 displays an error message. See Errors involving Call Attach Facility calls for more information.

DISCONNECT FROM statement

The DISCONNECT FROM statement is optional, but it is highly recommended if the procedure is long running and you do not require any more access to DB2. The DISCONNECT statement releases system resources for other users.

Syntax

EXEC DB2 DISCONNECT FROM symbolic_name END EXEC

where:

symbolic_name is the symbolic name from the DESTINATION parameter of the DEFINE PROCESS command.

A DISCONNECT is performed for the user at user logout time or when the subtask control block is "stolen" for use by another DB2 request. Issue the DISCONNECT command only when no more DB2 requests are required by the running procedure.

See UL/DB2 internals for more information.

Defining and using cursors

This section describes the SQL statements that you can use to define a cursor and to manipulate the row that the cursor points to. This section also discusses multiple cursor support.

Supported cursor statements

The SQL cursor operation statements supported by UL/DB2 are:

• DECLARE
• OPEN
• FETCH
• CLOSE

In addition, two other supported statements use cursors:

• UPDATE ... CURRENT (positioned UPDATE)
• DELETE ... CURRENT (positioned DELETE)

These statements are described in Modifying DB2 tables.

Managing cursors

You must place statements that declare a cursor before any statement that references that cursor.

Try to close cursors in your application as soon as they are no longer needed. Closing a cursor minimizes the system resources required by your application.

DECLARE statement

Use the DECLARE statement to declare a cursor and its associated query.

Syntax

EXEC DB2 DECLARE cursor_name CURSOR FOR SELECT query_expression END EXEC

where:

cursor_name is from 1-18 characters.

See Using %variables in SQL statements for more information about the use of %variables in a query expression.

Example

EXEC DB2 DECLARE FOO CURSOR FOR SELECT P#,PNAME,CITY FROM DVPJB.P END EXEC

This block of code associates a cursor "FOO" named with the results that are returned by the SELECT statement "SELECT P#, PNAME, CITY FROM DVPJB.P".

OPEN statement

Use the OPEN statement to prepare a cursor for processing.

Syntax

EXEC DB2 OPEN cursor_name END EXEC

where:

cursor_name is a cursor that has been declared earlier in the procedure. When the OPEN statement is executed, the values of any %variables are substituted in the cursor declaration.

Example

EXEC DB2 OPEN FOO END EXEC

This block opens the cursor FOO, declared in the previous example.

Multiple cursor support

You can open and use more than one cursor simultaneously. The maximum number of cursors that you can have open simultaneously is 10.

A request for more than 10 cursors results in a compile-time error.

FETCH statement

Use the FETCH statement to place a single row of returned values into a %variable.

Syntax

EXEC DB2 FETCH cursor_name INTO %variable {{{INDICATOR} %variable},...} END EXEC

where:

• cursor_name is the name of a cursor previously declared and opened.
• INDICATOR keyword is optional when the indicator %variable is present.
• %variable is a User Language %variable that has been previously declared. The number of %variables must match number of SQL columns named in the query.

  See Using %variables in SQL statements for more information.

Example

The following block of code fetches P#, PNAME, and CITY from the table PVBJB.P and assigns them to the User Language variables %A, %B, and %C:

REPEAT FOREVER EXEC DB2 FETCH FOO INTO %A,%B,%C END EXEC IF $STATUS NE 0 AND $STATUS NE 100 THEN JUMP TO ERROR END IF PRINT 'NUMBER:' AND %A AND ' NAME:' AND %B AND ' CITY:' AND %C END REPEAT

A $STATUS of 100 indicates that the FETCH found no rows or no more rows. After each FETCH, test for a $STATUS of 100 and move out of the loop when you encounter it. See UL/DB2 error codes and messages for more information on $STATUS and error processing.

Also, test for $STATUS NE 0 and $STATUS NE 100 for a real error condition (an exceptional case).

CLOSE statement

The CLOSE statement closes a cursor that has been previously opened. To reduce the overhead of your program, close a cursor as soon as it is no longer required for your program.

Syntax

EXEC DB2 CLOSE cursor_name END EXEC

Example

The following example closes the cursor FOO:

EXEC DB2 CLOSE FOO END EXEC

Modifying DB2 tables

This section describes the SQL statements that you can use in a User Language procedure to insert, update, or delete data in a DB2 table.

The supported SQL statements that modify a DB2 table are:

• INSERT
• UPDATE (searched UPDATE)
• DELETE (searched DELETE)
• UPDATE ... CURRENT (positioned UPDATE)
• DELETE ... CURRENT (positioned DELETE)

INSERT statement

Use the INSERT statement to insert rows into a table one at a time, or to copy data from one table to another, processing multiple rows with a single statement.

Inserting Large Object fields

In this release the INSERT statement is not supported for Large Object fields.

Inserting a single row

Use the following syntax of the INSERT statement to process a single row at a time.

Syntax

EXEC DB2 INSERT INTO table_name VALUES ([%variable|literal|NULL],...) END EXEC

where:

%variable is a User Language %variable.

Example

The following code inserts the values "LONDON", "BUDDHA", "S9", and "3" into the table DVPJB.S:

DECLARE %ERR STRING LEN 240 DECLARE %NAME STRING LEN 20 DECLARE %CITY STRING LEN 10 DECLARE %STAT FIXED DECLARE %SER STRING LEN 5 %CITY = 'LONDON' %NAME = 'BUDDHA' %SER = 'S9' %STAT = 3 EXEC DB2 CONNECT TO BOSTON END EXEC IF $STATUS NE 0 THEN JUMP TO CONERROR END IF EXEC DB2 INSERT INTO DVPJB.S VALUES (%SER) END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF

Inserting multiple rows

The following syntax of the INSERT statement that processes multiple rows.

Syntax

EXEC DB2 INSERT INTO table_name column_list SELECT query_specification END EXEC

See Using %variables in SQL statements for more information about the use of %variables in a query specification.

You can use this construct to copy data from one DB2 table to another.

Example

The following code selects the SNAME, STATUS, and CITY fields from the S table and copies them into the table DVPJB.S:

EXEC DB2 CONNECT TO BOSTON END EXEC IF $STATUS NE 0 THEN JUMP TO CONERROR END IF EXEC DB2 INSERT INTO DVPJB.S (SNAME, STATUS, CITY) SELECT SNAME, STATUS, CITY FROM S END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF

Searched UPDATE statement

Use the searched UPDATE statement to update one row of a table at a time.

Syntax

EXEC DB2 UPDATE table_name SET {column = [%variable|literal|NULL]},... WHERE search_condition END EXEC

where:

%variable is a previously declared User Language %variable.

See Using %variables in SQL statements for more information about the use of %variables in a search condition.

Example

PRINT 'UPDATE' EXEC DB2 UPDATE S SET SNAME = 'BILL', CITY = 'SAN DIEGO' WHERE S# = %S END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF

Searched DELETE statement

Use the searched DELETE statement to delete one row or a table at a time.

Syntax

EXEC DB2 DELETE FROM table_name WHERE column = search_condition END EXEC

where:

%variable is a previously declared User Language %variable.

See Using %variables in SQL statements for more information about the use of %variables in a search condition.

Example

DECLARE %ERR STRING LEN 240 DECLARE %NAME STRING LEN 20 DECLARE %CITY STRING LEN 10 DECLARE %STAT FIXED DECLARE %SER STRING LEN 5 %CITY = 'LONDON' %NAME = 'BUDDHA' %SER = 'S9' %STAT = 3 EXEC DB2 DELETE FROM DVPJB.S WHERE CITY = %CITY END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF

UPDATE... CURRENT statement

Use the UPDATE... CURRENT statement as follows.

Syntax

EXEC DB2 UPDATE table_name SET {column = [%variable|literal|NULL]},... WHERE CURRENT OF cursor_name END EXEC

where:

%variable is a previously declared User Language %variable.

Example

EXEC DB2 DECLARE FOO CURSOR FOR SELECT S#,SNAME,CITY FROM DVPJB.S WHERE CITY = %CITY FOR UPDATE of SNAME END EXEC * EXEC DB2 OPEN FOO END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF * REPEAT FOREVER EXEC DB2 FETCH FOO INTO %A,%B,%C END EXEC IF $STATUS = 100 THEN LOOP END END IF IF $STATUS NE 0 THEN JUMP TO ERROR END IF PRINT 'NUMBER:' AND %A AND ' NAME:' AND %B AND ' CITY:' AND %C EXEC DB2 UPDATE DVPJB.S SET SNAME = %NAME WHERE CURRENT OF FOO END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF END REPEAT

DELETE... CURRENT statement

Use the DELETE... CURRENT statement as follows.

Syntax

EXEC DB2 DELETE FROM table_name WHERE CURRENT OF cursor_name END EXEC

Example

REPEAT FOREVER EXEC DB2 FETCH FOO INTO %A,%B,%C END EXEC IF $STATUS = 100 THEN LOOP END END IF IF $STATUS NE 0 THEN JUMP TO ERROR END IF PRINT 'NUMBER:' AND %A AND ' NAME:' AND %B AND ' CITY:' AND %C IF $DEBLANK(%B,1) EQ %NAME THEN PRINT 'DELETING' AND %B EXEC DB2 DELETE FROM DVPJB.S WHERE CURRENT OF FOO END EXEC IF $STATUS NE 0 THEN JUMP TO ERROR END IF END IF END REPEAT

Using %variables in SQL statements

This section explains how you use User Language %variables in SQL statements. It also discusses how to specify the INDICATOR type with the FETCH command.

%variables

You can use a User Language %variable in an SQL statement to provide a value to DB2 or to receive the result of an SQL's statement processing.

Do not place a colon before a %variable.

Using %variables for Input

The SQL statements that use a %variable to provide a value to DB2 are:

• UPDATE... SET... CURRENT
• UPDATE... SET ... WHERE
• INSERT... VALUES
• DELETE ... WHERE
• DECLARE ... CURSOR

UL/DB2 parses the input %variables to determine type and value. You can use a numeric %variable wherever a number can appear in an SQL statement. You can use a string %variable wherever a quoted string can appear in an SQL statement.

Example

The %CITY variable adds the value "LONDON" to the DVPJB.S table:

DECLARE %CITY STRING LEN 10 %CITY = 'LONDON' EXEC DB2 CONNECT TO BOSTON END EXEC IF $STATUS NE 0 THEN JUMP TO CONERROR END IF EXEC DB2 INSERT INTO DVPJB.S (CITY) VALUES (%CITY) END EXEC

Using %variables for output (FETCH statement)

The SQL FETCH statement uses a %variable to receive output.

The output is assigned to the %variables in the order in which it is returned from DB2. If data is truncated when assigned to the %variable, $STATUS and $STATUSD indicate that truncation occurred.

If more columns are available from the query row than there are %variables specified, the output data is still put in the %variables in the order returned, but the $STATUS and $STATUSD indicate that more data was available.

Example

%A, %B, and %C in the following code receive the values of the P#, PNAME, and CITY columns from the table PVBJB.P:

REPEAT FOREVER EXEC DB2 FETCH FOO INTO %A,%B,%C END EXEC IF $STATUS NE 0 AND $STATUS NE 100 THEN JUMP TO ERROR END IF PRINT 'NUMBER:' AND %A AND ' NAME:' AND %B AND ' CITY:' AND %C END REPEAT

Stripping blanks from string values returned by DB2

When DB2 returns a string to a User Language %variable, that string contains the number of blanks necessary to make the value equal to the defined maximum length of the %variable.

Example

In the following code, if the string being fetched by CUR1 into %BAR is three characters ("Joe"), that value is padded with enough blanks to make it 20 characters long ("Joe           "):

DECLARE %BAR STRING LEN 20 FETCH CUR1 INTO %BAR $LEN(%BAR) = 20

A User Language string with a length of 20 that is assigned a 3-character value ("Joe") has a length of three:

DECLARE %FOO STRING LEN 20 %FOO =' JOE' $LEN (%FOO) = 3

As a result, an equality comparison between the values of %FOO and %BAR is false. If you want a string value returned by DB2 to conform to the way that User Language construes string variables, you must use the $DEBLANK function to strip the extraneous blanks:

$DEBLANK(%BAR)

Data types

The User Language/DATABASE 2 Interface supports string, float, and fixed data types; these are all Model 204 data types. The assignment to and from these variable types follows the rules outlined in the IBM DATABASE 2 SQL Reference.

Specifying a numeric type when a string type is expected (or specifying a string type when a numeric type is expected) generates an error. The error code is returned by $STATUS and $STATUSD.

Specifying the INDICATOR type with the FETCH command

You can specify the INDICATOR type on FETCH commands.

Syntax

EXEC DB2 FETCH cursor_name INTO %variablea INDICATOR %indica, %variableb INDICATOR %indicb,... END EXEC

where:

%indica in this statement is the indicator variable for %variablea.

INDICATOR is an optional keyword; if a %variable is followed directly by a %variable with no intervening comma, it is assumed to be an indicator variable.

Example

EXEC DB2 FETCH cursor_name INTO %variablea %indica, %variableb END EXEC

Here, %indica is the indicator variable for %variablea.

Querying the INDICATOR variable

In UL/DB2, the User Language procedure must query the INDICATOR variable to see if the NULL indicator was set (-1). If the column is NULL and no indicator variable is present, $STATUSD is set to -305 (the SQLCODE). Retrieving $DB2EMSG displays the text associated with SQLCODE -305. The receiving %variable is not set when a null value is fetched.

Terminating transactions

UL/DB2 supports the COMMIT and ROLLBACK statements to terminate transactions.

COMMIT statement

A commit point occurs when you issue the COMMIT statement or when the request ends normally.

Syntax

EXEC DB2 COMMIT WORK END EXEC

The exception to this rule is the APSY AUTOCOMMIT=NO condition. In this case, there is no implicit COMMIT at procedure end; a DB2 COMMIT is issued only if a Model 204 User Language COMMIT statement is issued.

ROLLBACK statement

UL/DB2 issues a ROLLBACK statement if either Model 204 or DB2 terminates abnormally.

Syntax

EXEC DB2 ROLLBACK WORK END EXEC

$SPIFY interface

$SPIFY is a Model 204 function that you can use in a User Language request to issue SQL commands. $SPIFY is designed to give the User Language programmer an environment similar to the one that SQL Processor Using File Input (SPUFI) provides to DB2 application programmers in the TSO environment. $SPIFY gives you, in effect, a DB2 command line.

Using $SPIFY, you can issue Data Definition commands to set up tables and views and process SELECT statements. You can include multiple $SPIFY functions in a procedure.

$SPIFY takes any valid SQL statement as an argument; the string must follow
Model 204 string conventions. The argument can be either a quoted string or a string %variable.

Syntax

PRINT $SPIFY(DB2-command-string)

Example

EXEC DB2 CONNECT TO BOSTON END EXEC PRINT $SPIFY('SELECT * FROM DVPJB.S')

Output

The result of the $SPIFY function is a series of print lines. The last line is printed without an end-of-line; therefore, the recommended usage is PRINT $SPIFY(DB2-command-string). The output produced depends on whether DB2-command-string is a valid SELECT statement.

Valid SELECT statement

The output produced for a valid SELECT statement is:

• Printed copy of the argument, that is, the SELECT statement
• Column headers
• Selected rows
• Number of rows printed
• Line indicating successful execution and the final SQLCODE (100, indicating the end of the selected rows)

Valid statement other than SELECT

The output produced for a valid statement other than SELECT is:

• Printed copy of the argument, that is, the DB2 request
• Line indicating successful execution and the final SQLCODE (0)
• Line indicating that the call has finished

Invalid statement

The output produced for an invalid statement is:

• Printed copy of the argument, that is, the invalid statement, preceded by
  'SQL REQUEST:'
• Several lines containing the DB2 error message

Note: In $SPIFY, a dash (-) in the output indicates that the column has the NULL indicator set.

You can use the Model 204 USE PRINTER command to direct the output.