www.krengeltech.com

DB2WSE Response Format

From Wiki

User Guide

Contents

Key to Syntax Diagrams

  • {required-string} A required string (may be a single value or multiple options)
  • [optional-string] An optional string (may be a single value or multiple options)
  • option-1|option-2 Separate options are separated by a vertical bar (|)
  • repeated-string... Repeated strings are suffixed with an ellipsis (...)


When a DB2WSE request is made, DB2WSE will return data to the requestor as XML data. The returned data will generically consist of an outer DB2WSERSP section containing a HEADER section and a data section, e.g.: 

<DB2WSERSP>
  <HEADER>
    {header-information}
  </HEADER>
  {data}
</DB2WSERSP>

The HEADER section itself contains a copy of the original DB2WSE request, but without the USER or PASSWORD XML elements, e.g.: 


...
<HEADER>
  <DB2WSEREQ>
    <STATEMENT [attributes]>
      {sql-statement}
    </STATEMENT>
  </DB2WSEREQ>
<HEADER>
...

In the examples shown below, the DB2WSE request returned in the HEADER will simply be displayed as {db2wse-request}, e.g.: 

<DB2WSERSP>
  <HEADER>
    {db2wse-request}
  </HEADER>
...

If the DB2WSE request SQL statement is a SELECT statement, the HEADER section will also include the following elements (after {db2wse-request}), which contain further information about the response data:

RETURNEDROWS
The number of rows that were returned in the response.
MOREROWS
A YES/NO flag which indicates whether there are further records that match the specified criteria.

SELECT statement response format

The DB2WSE response to a SELECT statement depends on the value of the RSPTYPE attribute to the STATEMENT element in the DB2WSE request. The response will consist of either information about the columns (column attributes), the actual row and column data (column data) or both column attributes and column data.

BASIC response

A BASIC response (where RSPTYPE='basic' was specified) will return a HEADER section followed by row and column data as follows: 

<DB2WSERSP>
  <HEADER>
    {db2wse-request}
    <RETURNEDROWS>{number of rows returned}</RETURNEDROWS>
    <MOREROWS>{YES|NO}</MOREROWS>
  </HEADER>
  <ROW ROWNBR="{returned-row-number}">
    <{column-name}>{column-value}</{column-name}>
    ...
  </ROW>
  ...
</DB2WSERSP>

The ROWNBR value refers to the row number within the set of rows returned in the DB2WSE response. This may therefore be different to the relative record number (RRN) of the row in the underlying physical table.

For null-capable columns where the column has the null indicator set, the format of the column will be: 

<{column-name}/>

See the DB2WSE Examples for an example of such a response.

FULL response

A FULL response (where RSPTYPE='full' was specified) will return separate HEADER and COLDEFS sections containing header information and column definitions, followed by row and column data as follows: 

<DB2WSERSP>
  <HEADER>
    {db2wse-request}
    <RETURNEDROWS>{number of rows returned}</RETURNEDROWS>
    <MOREROWS>{YES|NO}</MOREROWS>
  </HEADER>
  <COLDEFS>
    <COLDEF {attributes}/>
    ...
  </COLDEFS>
  <ROW ROWNBR="{returned-row-number}">
    <C{column-sequence}>{column-value}</C{column-sequence}>
    ...
  </ROW>
  ...
</DB2WSERSP>

For null-capable columns where the column has the null indicator set, the format of the column will be: 

<C{column-sequence}/>

The COLDEF attributes string is made up of the following: 

COLSEQ='{column-sequence}' COLNAME='{column-name}' COLTEXT='{column-description}' COLTYPE='{column-type}' COLLENGTH='{column-length}'[COLDECPOS='{column-decimal-positions}']
  • The COLDECPOS attribute is only included with numeric columns.
  • The COLSEQ attribute is the order of the column in the table: the first column has column-sequence 1, the next column has column-sequence 2 and so on.
  • The COLTYPE attribute will have one of the following values:
  • Date
  • Time
  • Timestamp
  • Varying character
  • Fixed character
  • Long varying character
  • Varying graphic
  • Fixed graphic
  • Long varying graphic
  • Floating point
  • Packed decimal
  • Zoned decimal
  • Big integer
  • Large integer
  • Small integer
  • ROWID
  • Varying binary string
  • Fixed binary string

The value may also have 'with nulls' appended (e.g. 'Fixed character with nulls') if the column is null-capable.

DATAONLY response

A DATAONLY response (where RSPTYPE='dataonly' was specified) will return row and column data in the same format as would be returned for a FULL response, but without the COLDEFS section.

See the FULL response section above for details

COLDEFS response

A COLDEFS response (where RSPTYPE='flddefs' was specified) will return the Header and COLDEFS sections that would be returned with a FULL response, but without the ROW sections.

See the FULL response section above for details

Non-SELECT statement response format

For non-SELECT statements, the DB2WSE response will have the following format: 

<DB2WSERSP>
  <HEADER>
    {db2wse-request}
  </HEADER>
  <RSPCODE>
    {response-code}
  </RSPCODE>
  <RSPTEXT>
    {number-of-rows} {'row(s)'} {'inserted into'|'updated in'|'deleted from'} {qualified-file-name}
  </RSPTEXT>
</DB2WSERSP>

The RSPCODE value indicates the number of succsessful database updates (i.e. it will have the same value as number-of-rows).

ERROR response format

If an error occurs in the DB2WSE program, the DB2WSE response will have the following format: 

<DB2WSERSP>
  <HEADER>
    {db2wse-request}
  </HEADER>
  <ERRTEXT>
    <CODE>{error-code}</CODE>
    <SEVERITY>{error-severity}</SEVERITY>
    <PGM>{error-program}</PGM>
    <TEXT>{error-text}</TEXT>
  </ERRTEXT>
<DB2WSERSP>

The PGM value will always be 'DB2WSE'.

The SEVERITY value will be between 0 and 100.

The following is a list of the possible CODE values. A value in parentheses after the explanatory text for each error code specifies the message identifier of the actual text that will be displayed for the TEXT value. The message identifier begins with 'DWE' or 'RX', then the message is held in the KTIMSGF message file. If the message identifier has a value of 'CPF*', then one of several messages specified in the QCPFMSG message file may be used.

DB2WSE0001
No license found for product (RXE0001)
DB2WSE0002
Invalid or unknown DB2WSE query (DWE0002)
DB2WSE0003
No SQL statement specified (DWE0003)
DB2WSE0004
No SQL operation (SELECT, INSERT, UPDATE or DELETE) specified (DWE0004)
DB2WSE0005
Invalid SQL operation specified (DWE0005)
DB2WSE0006
More than one SQL operation specified (DWE0006)
DB2WSE0007
Insufficient authority to execute SQL statement (CPF* or DWE0007)
DB2WSE0008
SQL statement not allowed with specified options (DWE0008)
DB2WSE0009
Invalid SQL statement syntax (DWE0009)
DB2WSE0010
Unhandled exception in procedure-name(): error-details (DWE0010)
DB2WSE0011
API call error in procedure-name(): API-error-details (DWE0011)
DB2WSE0012
Invalid URL format (DWE0012)
DB2WSE0013
Error parsing XML (DWE0013)
DB2WSE0014
Invalid number of keys specified for table-name (DWE0014)
DB2WSE0015
Number of rows returned exceeds maximum allowed value of MAX_SELECT_ROWS-value (DWE0015)
DB2WSE0016
Unknown or unsupported column type: column-type (DWE0017)
DB2WSE0101
Authority processing error
Retrieved from "http://iwiki.krengeltech.com/wiki/DB2WSE_Response_Format"