View source for UL/DB2 and SQL statements


==Overview==
<p>
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.</p>
<p>
The topic also describes the $SPIFY interface, which lets a User Language programmer test an SQL statement before it is used in a procedure.</p>

===Prerequisite knowledge of SQL===
<p>
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.</p>

==Requirements for writing SQL statements==
<p>
This section describes the general requirements that apply when you code an SQL statement in a User Language procedure.</p>

===Components of an SQL statement in a User Language procedure===
<p>
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.</p>

===EXEC DB2... END EXEC delimiters===
<p>
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".</p>
<p>
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.</p>
<p>
Two valid SQL statements are shown below (the indentation has been added for legibility):</p>
<p class="code">EXEC DB2
   COMMIT WORK
END EXEC</p>
<p class="code">EXEC DB2 FETCH P INTO %POLN END EXEC</p>

===Continuing SQL statements===
<p>
You do not need to use a continuation character to continue an SQL statement onto the following line. </p>

====Examples====
<p class="code">EXEC DB2
   DECLARE FOO CURSOR FOR SELECT
   S#,SNAME,STATUS,CITY FROM S
END EXEC</p>
<p>
You can continue a literal by placing a hyphen at the end of the line:</p>
<p class="code">EXEC DB2
   DELETE FROM DVPJB.S
   WHERE NAME = 'ALFRED E. - 
      NEWMAN'
END EXEC</p>

===Using quotation marks===
<p>
Use single quotation marks (') around literal text in an SQL statement. </p>

====Example====
<p class="code">EXEC DB2
   DELETE FROM DVPJB.S
   WHERE NAME = 'ALFRED E. -
     NEWMAN'
END EXEC</p>

<p class="note"><b>Note:</b> 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.</p>

===SQL comments not supported===
<p>
The UL/DB2 Interface does not support SQL comments.</p>

===Using multiple SQL statements in a procedure===
<p>
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. </p>

====Example====
<p class="code">EXEC DB2
   SQL statement
END EXEC
...
EXEC DB2
   SQL statement
   statement continued
END EXEC
...
EXEC DB2
   SQL statement
END EXEC</p>

===Checking $STATUS===
<p>
Check $STATUS after each EXEC DB2... END EXEC block to make sure that the processing completed correctly. See [[UL/DB2 error codes and messages#$STATUS and $STATUSD|$STATUS and $STATUSD]] for more information on checking $STATUS in a User Language procedure.</p>

==Connecting a User Language procedure to DB2==
<p>
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.</p>
<p>
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|Requirements for writing SQL statements]].</p>
<p>
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]].</p>

====CONNECT TO statement====
<p>
Use the CONNECT TO statement to create a thread between a User Language procedure and DB2.</p>
<p>
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.</p>

=====Syntax=====
<p class="syntax">EXEC DB2
   CONNECT TO <span class="term">symbolic_name</span>
END EXEC</p>
<p>
where:</p>
<p>
<span class="term">symbolic_name</span> is the symbolic name from the DESTINATION parameter of the DEFINE PROCESS command.</p>

====Errors connecting to DB2====
<p>
If the CONNECT TO statement within a User Language procedure fails, Model 204 displays an error message. See [[UL/DB2 error codes and messages#Errors involving Call Attach Facility calls|Errors involving Call Attach Facility calls]] for more information.</p>

===DISCONNECT FROM statement===
<p>
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.</p>

====Syntax====
<p class="syntax">EXEC DB2
   DISCONNECT FROM <span class="term">symbolic_name</span>
END EXEC</p>
<p>
where:</p>
<p>
<var class="term">symbolic_name</var> is the symbolic name from the DESTINATION parameter of the DEFINE PROCESS command.</p>
<p>
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.</p>
<p>
See [[UL/DB2 internals]] for more information.</p>

==Defining and using cursors==
<p>
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.</p>

===Supported cursor statements===
<p>
The SQL cursor operation statements supported by UL/DB2 are:</p>
<ul>
<li>DECLARE</li>
<li>OPEN</li>
<li>FETCH</li>
<li>CLOSE</li>
</ul>
<p>
In addition, two other supported statements use cursors:</p>
<ul>
<li>
UPDATE ... CURRENT (positioned UPDATE)</li>
<li>
DELETE ... CURRENT (positioned DELETE)</li>
</ul>
<p>
These statements are described in [[#Modifying DB2 tables|Modifying DB2 tables]].</p>

===Managing cursors===
<p>
You must place statements that declare a cursor before any statement that references that cursor.</p>
<p>
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.</p>

===DECLARE statement===
<p>
Use the DECLARE statement to declare a cursor and its associated query. </p>

====Syntax====
<p class="syntax">EXEC DB2
   DECLARE <span class="term">cursor_name</span> CURSOR
   FOR SELECT <span class="term">query_expression</span>
END EXEC</p>
<p>
where:</p>
<p>
<var class="term">cursor_name</var> is from 1-18 characters.</p>
<p>
See [[#Using %variables in SQL statements|Using %variables in SQL statements]] for more information about the use of %variables in a query expression.</p>

====Example====
<p class="code">EXEC DB2
   DECLARE FOO CURSOR FOR
   SELECT P#,PNAME,CITY
   FROM DVPJB.P
END EXEC</p>
<p>
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".</p>

===OPEN statement===
<p>
Use the OPEN statement to prepare a cursor for processing. </p>

====Syntax====
<p class="syntax">EXEC DB2
   OPEN <span class="term">cursor_name</span>
END EXEC</p>
<p>
where:</p>
<p>
<var class="term">cursor_name</var> 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.</p>

====Example====
<p class="code">EXEC DB2
   OPEN FOO
END EXEC</p>
<p>
This block opens the cursor FOO, declared in the previous example.</p>

====Multiple cursor support====
<p>
You can open and use more than one cursor simultaneously. The maximum number of cursors that you can have open simultaneously is 10.</p>
<p>
A request for more than 10 cursors results in a compile-time error.</p>

===FETCH statement===
<p>
Use the FETCH statement to place a single row of returned values into a %variable. </p>

====Syntax ====
<p class="syntax">EXEC DB2
   FETCH <span class="term">cursor_name</span> INTO
      <span class="term">%variable </span>{{{INDICATOR} <span class="term">%variable</span>},...}
END EXEC</p>
<p>
where:</p>
<ul>
<li>
<var class="term">cursor_name</var> is the name of a cursor previously declared and opened.</li>
<li>
INDICATOR  keyword is optional when the indicator <var class="term">%variable</var>
 is present. </li>
<li>
<var class="term">%variable</var> is a User Language %variable that has been previously declared. The number of %variables must match number of SQL columns named in the query. <p>See [[#Using %variables in SQL statements|Using %variables in SQL statements]] for more information.</p>
</li>
</ul>

====Example====
<p>
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:</p>
<p class="code">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</p>
<p>
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.</p>
<p>
Also, test for $STATUS NE 0 and $STATUS NE 100 for a real error condition (an exceptional case).</p>

===CLOSE statement===
<p>
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.</p>

====Syntax====
<p class="syntax">EXEC DB2
   CLOSE <span class="term">cursor_name</span>
END EXEC</p>

====Example====
<p>
The following example closes the cursor FOO:</p>
<p class="code">EXEC DB2
   CLOSE FOO
END EXEC</p>

==Modifying DB2 tables==
<p>
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.</p>
<p>
The supported SQL statements that modify a DB2 table are:</p>
<ul>
<li>INSERT</li>
<li>UPDATE (searched UPDATE)</li>
<li>DELETE (searched DELETE)</li>
<li>UPDATE ... CURRENT (positioned UPDATE)</li>
<li>DELETE ... CURRENT (positioned DELETE)</li>
</ul>

===INSERT statement===
<p>
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.</p>

====Inserting Large Object fields====
<p>In this release the INSERT statement is not supported for Large Object fields.</p>

====Inserting a single row====
<p>Use the following syntax of the INSERT statement to process a single row at a time.</p>

=====Syntax=====
<p class="syntax">EXEC DB2
   INSERT INTO <span class="term">table_name</span> VALUES ([<span class="term">%variable</span>|<span class="term">literal</span>|NULL],...)
END EXEC</p>
<p>
where:</p>
<p>
%<span class="term">variable</span> is a User Language %variable.</p>

=====Example=====
<p>
The following code inserts the values "LONDON",  "BUDDHA", "S9", and  "3" into the table DVPJB.S:</p>
<p class="code">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</p>

====Inserting multiple rows====
<p>
The following syntax of the INSERT statement that processes multiple rows.</p>

=====Syntax=====
<p class="syntax">EXEC DB2
   INSERT INTO <span class="term">table_name column_list</span>
   SELECT <span class="term">query_specification</span>
END EXEC</p>
<p>
See [[#Using %variables in SQL statements|Using %variables in SQL statements]] for more information about the use of %variables in a query specification.</p>
<p>
You can use this construct to copy data from one DB2 table to another.</p>

=====Example=====
<p>
The following code selects the SNAME, STATUS, and CITY fields from the S table and copies them into the table DVPJB.S:</p>
<p class="code">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</p>

===Searched UPDATE statement===
<p>
Use the searched UPDATE statement to update one row of a table at a time.</p>

====Syntax====
<p class="syntax">EXEC DB2
   UPDATE <span class="term">table_name</span>
   SET {<span class="term">column</span> = [<span class="term">%variable</span>|<span class="term">literal</span>|NULL]},...
   WHERE <span class="term">search_condition</span>
END EXEC</p>
<p>
where:</p>
<p>
%<var class="term">variable</var> is a previously declared User Language %variable. </p>
<p>
See [[#Using %variables in SQL statements|Using %variables in SQL statements]] for more information about the use of %variables in a search condition.</p>

====Example====
<p class="code">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</p>

===Searched DELETE statement===
<p>
Use the searched DELETE statement to delete one row or a table at a time.</p>

====Syntax====
<p class="syntax">EXEC DB2
   DELETE FROM <span class="term">table_name</span>
   WHERE <span class="term">column</span> = <span class="term">search_condition
</span>
END EXEC</p>
<p>
where:</p>
<p>
<var class="term">%variable</var> is a previously declared User Language %variable. </p>
<p>
See [[#Using %variables in SQL statements|Using %variables in SQL statements]] for more information about the use of %variables in a search condition.</p>

====Example====
<p class="code">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</p>

===UPDATE... CURRENT statement===
<p>
Use the UPDATE... CURRENT statement as follows.</p>

====Syntax====
<p class="syntax">EXEC DB2
   UPDATE <span class="term">table_name</span>
   SET {<span class="term">column</span> = [<span class="term">%variable</span>|<span class="term">literal</span>|NULL]},...
   WHERE CURRENT OF <span class="term">cursor_name</span>
END EXEC</p>
<p>
where:</p>
<p>
<var class="term">%variable</var> is a previously declared User Language %variable.</p>

====Example====
<p class="code"><nowiki>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</nowiki></p>

===DELETE... CURRENT statement===
<p>
Use the DELETE... CURRENT statement as follows.</p>

====Syntax====
<p class="syntax">EXEC DB2
   DELETE FROM <span class="term">table_name</span>
   WHERE CURRENT OF <span class="term">cursor_name</span>
END EXEC</p>

====Example====
<p class="code">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</p>

==Using %variables in SQL statements==
<p>
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.</p>

===%variables===
<p>
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.</p>
<p>
Do not place a colon before a %variable.</p>

===Using %variables for Input===
<p>
The SQL statements that use a %variable to provide a value to DB2 are:</p>
<ul>
<li>UPDATE... SET... CURRENT</li>
<li>UPDATE... SET ... WHERE</li>
<li>INSERT... VALUES</li>
<li>DELETE ... WHERE</li>
<li>DECLARE ... CURSOR </li>
</ul>
<p>
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.</p>

====Example====
<p>
The %CITY variable adds the value "LONDON" to the DVPJB.S table:</p>
<p class="code">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</p>

===Using %variables for output (FETCH statement)===
<p>
The SQL FETCH statement uses a %variable to receive output.</p>
<p>
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.</p> 

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

====Example====
<p>
%A, %B, and %C in the following code receive the values of the P#, PNAME, and CITY columns from the table PVBJB.P:</p>
<p class="code">
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</p>

====Stripping blanks from string values returned by DB2====
<p>
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.</p>

=====Example=====
<p>
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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"):</p>
<p class="code">DECLARE %BAR STRING LEN 20
FETCH CUR1 INTO %BAR
$LEN(%BAR) = 20</p>
<p>
A User Language string with a length of 20 that is assigned a 3-character value ("Joe") has a length of three:</p>
<p class="code">DECLARE %FOO STRING LEN 20
%FOO =' JOE'
$LEN (%FOO) = 3</p>
<p>
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:</p>
<p class="code">$DEBLANK(%BAR)</p>

====Data types====
<p>
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 <var class="book">IBM DATABASE 2 SQL Reference</var>.</p>
<p>
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.</p>

====Specifying the INDICATOR type with the FETCH command====
<p>
You can specify the INDICATOR type on FETCH commands.</p>

=====Syntax=====
<p class="syntax">EXEC DB2
   FETCH <span class="term">cursor_name</span>
   INTO <span class="term">%variablea</span> INDICATOR <span class="term">%indica, %variableb</span>
      INDICATOR <span class="term">%indicb</span>,...
END EXEC</p>
<p>
where:</p>
<p>
<var class="term">%indica</var> in this statement is the indicator variable for  <var class="term">%variablea</var>.  </p>
<p>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.</p>

=====Example=====
<p class="syntax">EXEC DB2
  FETCH <span class="term">cursor_name</span>
  INTO <span class="term">%variablea %indica, %variableb</span>
END EXEC</p>
<p>
Here, <var class="term">%indica</var> is the indicator variable for <var class="term">%variablea</var>.</p>

====Querying the INDICATOR variable====
<p>
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.</p>

==Terminating transactions==
<p>
UL/DB2 supports the COMMIT and ROLLBACK statements to terminate transactions.</p>

===COMMIT statement===
<p>
A commit point occurs when you issue the COMMIT statement or when the request ends normally.</p>

====Syntax====
<p class="syntax">EXEC DB2
   COMMIT WORK
END EXEC</p>
<p>
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.</p>

===ROLLBACK statement===
<p>
UL/DB2 issues a ROLLBACK statement if either Model 204 or DB2 terminates abnormally.</p>

====Syntax====
<p class="syntax">EXEC DB2
   ROLLBACK WORK
END EXEC</p>

==$SPIFY interface==
<p>
$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.</p>
<p>
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.</p>
<p>
$SPIFY takes any valid SQL statement as an argument; the string must follow <BR>
Model 204 string conventions. The argument can be either a quoted string or a string %variable.</p>

====Syntax====
<p class="syntax">PRINT $SPIFY(<span class="term">DB2-command-string</span>)</p>

====Example====
<p class="code">EXEC DB2
  CONNECT TO BOSTON
END EXEC
PRINT $SPIFY('SELECT * FROM DVPJB.S')</p>

====Output====
<p>
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(<var class="term">DB2-command-string</var>). The output produced depends on whether <var class="term">DB2-command-string</var> is a valid SELECT statement.</p>

====Valid SELECT statement====
<p>
The output produced for a valid SELECT statement is:</p>
<ul>
<li>Printed copy of the argument, that is, the SELECT statement</li>
<li>Column headers</li>
<li>Selected rows</li>
<li>Number of rows printed</li>
<li>Line indicating successful execution and the final SQLCODE (100, indicating the end of the selected rows)</li>
</ul>

====Valid statement other than SELECT====
<p>
The output produced for a valid statement other than SELECT is:</p>
<ul>
<li>Printed copy of the argument, that is, the DB2 request</li>
<li>Line indicating successful execution and the final SQLCODE (0)</li>
<li>Line indicating that the call has finished</li>
</ul>

====Invalid statement====
<p>
The output produced for an invalid statement is:</p>
<ul>
<li>Printed copy of the argument, that is, the invalid statement, preceded by <BR>
'SQL REQUEST:'</li>
<li>Several lines containing the DB2 error message</li>
</ul>

<p class="note"><b>Note:</b> In $SPIFY, a dash (-) in the output indicates that the column has the NULL indicator set.</p>
<p>
You can use the Model 204 <var>[[USE PRINTER / PUNCH command|USE PRINTER]]</var> command to direct the output.</p>

==See also==
{{Template:UL/DB2 topic list}}

[[Category: UL/DB2]]