www.krengeltech.com

DB2WSE SQL Statement Attributes

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 (...)

Note: Unless specified otherwise, the attributes for the STATEMENT element are valid only for SELECT statements, and are ignored where an INSERT, UPDATE or DELETE statement is specified.

STATEMENT attributes and attribute values are case-independent.


FIRSTROW

For SELECT statements, specifies the first matching row to return. If not specified or specified as <=0, defaults to 1

Example 

<STATEMENT FIRSTROW='2'>

LASTROW

For SELECT statements, specifies the last matching row to return. If not specified or specified as <=0, defaults to last matching row.

Limitations

Must be specified with FIRSTROW. Must be specified after FIRSTROW. Must not be specified with FORROWS.

Example 

<STATEMENT FIRSTROW='2' LASTROW='14'>

FORROWS

For SELECT statements, specifies the maximum number of matching rows to return. If not specified or specified as <=0, defaults to the value of the MAX_SELECT_ROWS configuration value.

Limitations

Must not be specified with LASTROW.

Example 

<STATEMENT FIRSTROW='2' FORROWS='10'>

NAMING

For all statements, specifies naming type. Must be either 'SYS' (library/file) or 'SQL' (library.table). If not specified, defaults to 'SYS'.

Example 

<STATEMENT NAMING='SQL'>

COLHDRS

For SELECT statements, specifies column headers. Must be either 'FLDNAM', 'SQLLABEL' or 'ANY'. If not specified, defaults to 'FLDNAM'. This attribute is ignored if RSPTYPE is specified.

Limitations

Must not be specified with RSPTYPE.

Example 

<STATEMENT COLHDRS='FLDNAM'>

RSPTYPE

For SELECT statements, specifies the response type. Must be one of the following values: 'BASIC', 'FULL', 'DATAONLY', 'COLDEFS'. If not specified, defaults to 'BASIC'. This attribute will override any value specified for the COLHDRS attribute.

See SELECT statement response format for details of the formats of the different response types, both for SELECT statements with different RSPTYPE values and also for non-SELECT statements.

Limitations

Must not be specified with COLHDRS.

Example 

<STATEMENT RSPTYPE='FULL'>

QUERYID

If a predefined query (which was previously created with the CRTDWEQRY command) should be run, the QUERYID attribute should be specified, with a value of the query identifier.

If QUERYID is specified, the STATEMENT element must be defined as a null element and no SQL statement should be specified.

Limitations

Must not be specified with an SQL statement. STATEMENT element must be specified as null.

Example 

<STATEMENT queryid='43'/>
Retrieved from "http://iwiki.krengeltech.com/wiki/DB2WSE_SQL_Statement_Attributes"