ASURXSQL external function

The following table describes the SQL statements and commands that the ASURXSQL external function provides.

In each example in the following table, the argument in parentheses is an SQL statement or command, such as CONNECT or DISCONNECT. When ASURXSQL runs, it sets the variable rc to the return code of the command that ran. The value in the rc variable determines subsequent processing. Using this external ASURXSQL function, you can perform any SQL statement.

ASURXSQL external function

SQL statement	Example	Description
CONNECT	rc = asurxsql('CONNECT' ssid plan)	Connect to the specified Db2 subsystem and open the specified plan. This is the plan to which the ASURXSQL plan DBRM is bound.
DISCONNECT	rc = asurxsql('DISCONNECT')	Commit, free all storage that ASURXSQL obtains, close the plan, disconnect from the Db2 subsystem, delete ASURXSQL from memory.
OPEN CURSOR	rc = asurxsql('OPEN cursor_name CURSOR FOR' sql_statement)	Prepare the SQL SELECT statement and open the specified cursor.
FETCH CURSOR	rc = asurxsql('FETCH cursor_name') rc = asurxsql('FETCH cursor_name INTO v1, v2, . . . vn')	Fetch the first or next row. Return SQLCODE = 100 when all rows have been fetched. The first form of the function puts the data into variables whose names are cursor_name.column_name. It also puts values into variables like cursor_name.n, where n is the column number of the query. The second form puts values into the specified variables. The maximum length of a variable name is 250 characters. Variable names can contain @, #, $, ¢, ., !, ?, _.
CLOSE CURSOR	rc = asurxsql('CLOSE cursor_name')	Close the specified cursor.
EXECUTE	rc = asurxsql(sql_statement)	Prepare and run the SQL statement. If the statement is SELECT, return no more than one row. The function sets variables as it does for FETCH. If the SELECT statement has no INTO clause, the stem name is always SELECT.
COMMIT	rc = asurxsql('COMMIT')	Commit uncommitted work, and close all open cursors.
ROLLBACK	rc = asurxsql('ROLLBACK')	Back out relational database changes made since the last commit.

ASURXSQL return codes

If ASURXSQL successfully asks Db2 for information, ASURXSQL produces return code 0.

If ASURXSQL encountered non-SQL errors, ASURXSQL returns one of the error messages that is shown in the following table:

Return code	Description
30	No command in the argument string
31	Already disconnected
32	Already connected
33	Cursor already open
34	Too many open cursors
35	Unknown cursor
36	Out of memory
37	SQL failure; the variable SQLCODE will be set and the variable SQLERRM will contain a printable error message (see next topic)
38	Argument string length greater than 32,767 characters
39	An ’INTO’ was expected but not found

Printing error messages

The following code formats and prints SQL error messages to the SYSTERM data set:

if sqlcode ¬= 0 then do i = 1 to 1000 by 80
m = substr(sqlerrm,i+1,79)
if m = ' ' then leave
say m
end

Call attachment return codes

If the CONNECT or DISCONNECT command fails, the DB2 Call Attachment Facility (CAF) sets other return codes, which are documented in the IBM Db2 Messages and Codes manual.

The CAFREASON variable contains the CAF reason code for CAF failures as follows:

Connect failed, rc = 8, cafreason = 00F30082

You can use the variable SQLSTATE in addition to SQLCODE. SQLSTATE indicates warnings as well as errors as follows:

ASURXSQL return code = 37 near line 137
DSNT408I SQLCODE = -991, ERROR: CALL ATTACH WAS UNABLE TO ESTABLISH AN
IMPLICIT CONNECT OR OPEN TO DB2. RC1=000C RC2=00F30006
DSNT418I SQLSTATE = 57015 SQLSTATE RETURN CODE

If the product issues a warning without an error, ASURXSQL will return rc=0 SQLCODE=0 so that the row values will be available.

SQL support

The current version of the DASD MANAGER PLUS ASURXSQL external function does not support the following items:

More than three open cursors at the same time
Parameter markers
The DESCRIBE command
Mixed case commands

Commands must be entirely lowercase or uppercase. For example, connect and CONNECT work, but coNNect does not work.