|
|
|
Use the DB2() function to enable an IMOD to dynamically perform SQL SELECT statements and retrieve the selected rows.
The DB2() function has this syntax:
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])
The DB2() function takes these arguments:
Result of an SQL request will return:
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 which must be linked to the DB2 driver that should service the requests).
Causes execution of the specified SELECT statement.
DB2 SELECT statement.
Maximum table rows to be returned on the stack. Additional rows may be fetched with the MORE operation.
Default: 500
Single character to be used as a separator for the field descriptions. The first stack record returned will be the Field Description Names. The descsep character will be 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 additional rows from the previously issued SELECT statement.
Closes the plan and either commits or aborts changes.
option
How the close operation should process. Specify one of the following:
SYNC Normal
ABRT Abort previous processing
Neither option is applicable for SELECT processing.
When a SELECT is successful, individual rows will be returned on the stack, up to the limit specified by maxrow. If descsep was specified, the first record returned is the field descriptor names. The names are separated by the descsep character. If OFFSET was specified, the next record will consist 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 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 two bytes of each field is the 2-byte DB2 indicator field. The field contents immediately follow the indicator field.
The DB2() function produces these return codes:
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 © 2012 CA. All rights reserved. | Tell Technical Publications how we can improve this information |