This function enables an IMOD to perform dynamically SQL SELECT statements and retrieve the selected rows.
Form 1:
result = DB2(OPEN,[plan])
Form 2:
result = DB2(SQL,stmt,[maxrow,[descsep],[OFFSET])
Form 3:
result = DB2(MORE,[maxrow])
Form 4:
result = DB2(CLOSE,[option])
Parameters
Result of an SQL request returns:
The null string, if no errors occurred.
The DB2 error code, followed by possible error text, if an error occurred that prevented execution of the request.
The text string MORE if a SELECT request successfully returned data but could not complete due to the maxrow limit.
Opens a plan.
Name of the DB2 plan to be used for this request. The default is GSSPLAN (which is distributed in the CA-GSS Example library and must be linked to the DB2 driver that services the requests).
Causes the execution of the specified SELECT statement.
DB2 SELECT statement.
Maximum table rows to be returned on the stack. More rows can be fetched with the MORE operation.
Default: 500
Single character to be used as a separator for the field descriptions. The first stack record that is returned is the Field Description Names. The descsep character is used to delimit the name.
Default: Field Description Names are not returned on the stack.
Generates output of a stack record consisting of pairs of 2-byte binary fields (immediately preceding the first row). The first field is the offset (0-based) to a field in each following row. The second field is the length of the data portion of the field.
Requests the return of more rows from the previously issued SELECT statement.
Closes the plan and commits or aborts changes.
How the close operation processes:
SYNC Normal
ABRT Abort previous processing
Neither option is applicable for SELECT processing.
When a SELECT is successful, the stack returns individual rows, up to the limit specified by maxrow. If descsep was specified, the first record that is returned is the field descriptor names. The descsep character separates the names. If OFFSET was specified, the next record consistd of pairs of 2-byte binary fields. Each pair describes one field in the following returned rows. The first pair is the offset in the record to the start of the field information. The second pair is the total length that is required for the field. You use these pairs to index into any data record to find a particular field.
Each of the remaining data records returns one selected row from the DB2 table. The first 2 bytes of each field is the 2-byte DB2 indicator field. The field contents immediately follow the indicator field.
ARG n MISSING OR INVALID
OPEN NOT FIRST OR NOT AFTER CLOSE
CLOSE NOT AFTER OPEN OR SQL
SQL AFTER CLOSE
MORE NOT AFTER SQL
DB2 error codes and messages
After OPEN or CLOSE:
rrrrrrrr cccccccc text
rrrrrrrr is the hexadecimal return code, cccccccc is the hexadecimal reason code, and text is the error text.
After SQL or MORE:
nnnn p text
nnnn is the signed numeric DB2 error code, p is the phase indicator (internal to CA-GSS), and text is the DB2-provided error text.
UNABLE TO PROVIDE SUBTASK
NO MORE ROWS AVAILABLE
Example
/* Select and retrieve rows. Locate the third column and print it.*/
result = db2('open') /* open the plan */
if rc ^= 0 then signal error1 /* -> if plan fails to open */
result = db2('SQL','SELECT askdfjakf',0,' ','OFFSET')
if rc ^= 0 then signal error2 /* -> if select fails */
parse pull . . header . /* Extract field name */
parse pull offset /* obtain offset record */
pos = c2d(substr(offset,8,2)) + 1 /* position of third field */
len = c2d(substr(offset,10,2)) - 2 /* length of data portion
of third field */
say header /* print the field header */
do while result = 'MORE'
limit = queued()
do i = 1 to limit
parse pull row
field3 = substr(row,pos,len)
say field3
end i
result = db2('MORE',50) /* get next 50 rows */
end
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|