DB2()

CA-GSS Statements and Commands › GSS Extended Functions › IO Functions › Number, String, and Variable Functions › System Information Functions › DB2()

DB2()

Use the DB2() function to enable an IMOD to dynamically perform SQL SELECT statements and retrieve the selected rows.

Syntax

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])

Arguments

The DB2() function takes these arguments:

result	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.
OPEN	Opens a plan.
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).
SQL	Causes execution of the specified SELECT statement.
stmt	DB2 SELECT statement.
maxrow	Maximum table rows to be returned on the stack. Additional rows may be fetched with the MORE operation. Default: 500
descsep	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.
OFFSET	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.
MORE	Requests the return of additional rows from the previously issued SELECT statement.
CLOSE	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.

Usage Notes

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.

Return Codes

The DB2() function produces these return codes:

101 - 105	ARG n MISSING OR INVALID
121	OPEN NOT FIRST OR NOT AFTER CLOSE
122	CLOSE NOT AFTER OPEN OR SQL
123	SQL AFTER CLOSE
124	MORE NOT AFTER SQL
125	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.
126	UNABLE TO PROVIDE SUBTASK
127	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

Tell Technical Publications how we can improve this information