Retrieving data from Db2 tables in REXX programs

All output data in REXX programs is string data. Although, you can determine the data type that the data represents from its format and from the data type of the column from which the data was retrieved.

About this task

The following table gives the format for each type of output data.

Table 1. SQL output data types and REXX data formats
SQL data type REXX output data format
SMALLINT
INTEGER
BIGINT
 A string of numerics that does not contain leading zeroes, a decimal point, or an exponent identifier. If the string represents a negative number, it begins with a minus (-) sign. The numeric value is between -9223372036854775808 and 9223372036854775807, inclusive.
DECIMAL(p,s) A string of numerics with one of the following formats:
  • Contains a decimal point but not an exponent identifier. The string is padded with zeroes to match the scale of the corresponding table column. If the value represents a negative number, it begins with a minus (-) sign.
  • Does not contain a decimal point or an exponent identifier. The numeric value is less than -9223372036854775808 or greater than 9223372036854775807. If the value is negative, it begins with a minus (-) sign.
FLOAT(n)
REAL
DOUBLE
 A string that represents a number in scientific notation. The string consists of a numeric, a decimal point, a series of numerics, and an exponent identifier. The exponent identifier is an E followed by a minus (-) sign and a series of numerics if the number is between -1 and 1. Otherwise, the exponent identifier is an E followed by a series of numerics. If the string represents a negative number, it begins with a minus (-) sign.
DECFLOAT REXX emulates the DECFLOAT data type with DOUBLE, so support for DECFLOAT is limited to the REXX support for DOUBLE. The following special values are not supported:
  • INFINITY
  • SNAN
  • NAN
Start of change
CHAR(n)
VARCHAR(n)
CLOB(n)
BLOB(n)
End of change		 Start of changeA character string or LOB value of length n bytes. The string is not enclosed in single or double quotation marks.End of change
Start of change
GRAPHIC(n)
VARGRAPHIC(n)
DBCLOB(n)
End of change		 Start of changeA string of length 2*n bytes. Each pair of bytes represents a double-byte character. This string does not contain a leading G, is not enclosed in quotation marks, and does not contain shift-out or shift-in characters.End of change
Because you cannot use the SELECT INTO statement in a REXX procedure, to retrieve data from a Db2 table you must prepare a SELECT statement, open a cursor for the prepared statement, and then fetch rows into host variables or an SQLDA using the cursor. The following example demonstrates how you can retrieve data from a Db2 table using an SQLDA: 
SQLSTMT= ,
'SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,' ,
'  WORKDEPT, PHONENO, HIREDATE, JOB,' ,
'  EDLEVEL, SEX, BIRTHDATE, SALARY,' ,
'  BONUS, COMM' ,
'  FROM EMP'
EXECSQL DECLARE C1 CURSOR FOR S1
EXECSQL PREPARE S1 INTO :OUTSQLDA FROM :SQLSTMT
EXECSQL OPEN C1
Do Until(SQLCODE ¬= 0)
  EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA
  If SQLCODE = 0 Then Do
    Line = ''
    Do I = 1 To OUTSQLDA.SQLD
      Line = Line OUTSQLDA.I.SQLDATA
    End I
    Say Line
   End
End