Query (QQQQRY) API

Required Parameter Group:

Query option requested

Input

Char(10)

User file control block

I/O

Char(*)

Query definition template

I/O

Char(*)

Literal values

I/O

Char(*)

Access plan control block

I/O

Char(48)

Error code

I/O

Char(*)

Default Public Authority: *EXCLUDE

Threadsafe: Conditional; see Usage Notes.

The Query (QQQQRY) API gets a set of database records that satisfies a database query request. Using this API you can do all the things you could do with the Open Query File (OPNQRYF) command. You can also perform subqueries, perform unions, and use SQL host variables.

The QQQQRY API can be used to do any combination of the following database functions:

Join records from more than one file, member, and record format. The join operation that is performed may be equal or nonequal in nature.
Calculate new field values by using numeric and character operations on field values and constants.
Group records by like values of one or more fields, and calculate aggregate functions, such as minimum field value and average field value, for each group.
Select a subset of the available records. Selection can be done both before and after grouping the records.
Arrange result records by the value of one or more key fields.

You can use this API to run a query, create an access plan, or get information from the query definition template (QDT). When you run the query, the API uses the information you provide with the query definition template to extract information and data from the database. Creating an access plan makes it possible to run the query with better performance. Checking the query definition template allows you to validate the values in this query definition template.

The format definition is part of the query definition template and can be created and saved with extracted information by the Retrieve Database File Description (QDBRTVFD) API. When you are finished using the QQQQRY API, you should close the file (using the Close File (CLOF) command) to free up resources.

Another part of the query definition template is the access plan for the query. Using this API with the Create Query Access Plan (CRTQAP) value of the query option requested parameter, you can build an access plan to more efficiently run a query more than once. You can then use the access plan control block parameter to point to the access plan. This greatly improves the time it takes to perform subsequent runs of this query using this API and the RUNQRY option. Every time a query is run, the system first checks to see if an access plan has been specified. If one has, that is what is used to get the data requested by the query. If no access plan has been specified, a new one is built dynamically.

Authorities and Locks

User Space Authority: *CHANGE
Library Authority: *EXECUTE
File Authority: *OBJOPR
User Space Lock: *SHRRD

Required Parameter Group

Query option requested

INPUT; CHAR(10)

One of three options to be used:

RUNQRY
Run query
CRTQAP
Create query access plan
CHKQDT
Check query definition templates

User file control block

I/O; CHAR(*)

One or more selected options for input and output of the specified query. This parameter need only be used along with the RUNQRY query option. See User File Control Block (QDBUFCB_T) Structure for a list of available options.

Query definition template

I/O; CHAR(*)

The information required to create objects that are used to query a database. It contains feedback information from the creation of objects. If a pointer to the access plan is specified, the corresponding query definition templates must also be specified.

Literal values

I/O; CHAR(*)

This parameter is used to put into effect SQL host variables. When SQL host variables are used, this is a list of constant values used to run a query. If this parameter is to be ignored, a null pointer can be specified for the parameter. Once the literal value is specified on a call, it must always be specified.

Access plan control block

I/O; CHAR(48)

A string of bytes that point to the access plan control block and give the size the access plan requires. This parameter must be specified for the RUNQRY query option when you want to specify an access plan and for the CRTQAP query option. The format for this parameter is:

PTR(SPP)

A space pointer that indicates the area of storage that contains the access plan. This area must begin on a 16-byte boundary and be all zeros.

Bin(4)

The size of storage needed to contain the access plan.

Char(28)

Reserved.

Error code

I/O; CHAR(*)

The structure in which to return error information. For the format of the structure, see Error code parameter.

Data Structures

The QQQQRY API uses information in four structures to carry out a query. All structures are used together to perform the function you have selected using the query option requested parameter. The names of these structures are:

QDBQH_T

Query definition template

Qdb_Qddfmt_t

Format definition template

QDBUFCB_T

User file control block

QQQVALS_T

Values for query variable fields

The following sections show you in a general way how this information is structured.

Query Definition Template (QDBQH_T)

The query definition template provides information about the query that is to be performed. QDBQH_T Format shows the general layout of this format.

Notice the box marked with a (1) in QDBQH_T Format. The topic Format Definition Template (Qdb_Qddfmt_t) provides the layout of the entire record format specification.

The offsets and descriptions of all the fields contained in this structure are shown in the following tables. You can see this source in member QQQQRY in the QSYSINC library.

QDBQH_T Format

Query Definition Header (QDBQH_T)

This is the first structure and is located at offset zero. (Ref #1.)

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbqfilo

Offset to file, library, format, and member specifications.

BIN(4)

qdbqfldo

Offset to the record specifications. 0 indicates that the record specifications should be taken from the file format. 0 is not valid if there are multiple files in the file specification and this is not a group-by query.

BIN(4)

qdbqjoio

Offset to join specifications. 0 indicates that this is not a join query.

BIN(4)

qdbqselo

Offset to the record selection specifications. 0 indicates that no record selection is to occur.

BIN(4)

qdbqkeyo

Offset to the order by specifications. 0 indicates that the records are returned in the file's access path order. If this is a join, the access path order of the first file in the file array is used. -1 indicates that the result records are returned in no guaranteed order; running the same query twice may produce a different result order.

BIN(4)

qdbqgrpo

Offset to the group-by-selection specifications. 0 indicates that no group by is to occur.

CHAR(4)

qdbqdt_1

Reserved.

BIN(4)

qdbqgpso

Offset to the group by selection specifications. 0 indicates that no group by selection is to occur. If field qdbqgrpo is 0, this offset is ignored.

CHAR(1)

qdbqfin

Query completion indicator.

A: Query need not be completed before returning. The database attempts to minimize the entire query and retrieval time. Selection may be done at I/O time.
F: Query need not be complete before returning. The database attempts to minimize the time to get the first buffer of results. Selection may be done at I/O time.
M: Query need not be complete before returning; however, selection at I/0 time should be minimized so that long waits for the next selected records are minimized.
C: Query must be completed before returning. If this is a join, the records must be put in a temporary file.

CHAR(1)

qdbqtem

Query temporary result indicator.

N: Temporary results should be prohibited.
O: Temporary results are allowed but should be used only if necessary to do the query. If a read previous operation can be requested, then O must be used.
T: Temporary results are allowed but should be used only if necessary to do the query. However, if temporary results are used, then use the last TSORT method, which reads directly from its sort. This option cannot be specified if a read previous operation is to be used.
A: Temporary results are allowed and should be used if better performance can be achieved by using a temporary result. Use A when the user does not request previous records to be read.

CHAR(2)

qdbqattr

Query attributes.

BIT(1)

qdbqnst

Status messages. Status messages are never sent for batch jobs.

0: Do not send status messages.
1: Send status messages during query and I/O processing.

BIT(1)

qdbqdist

Distinct records.

0: Do not produce distinct records.
1: Eliminate duplicate records from the query result.

BIT(1)

qdbqpgmd

Program-defined files.

0: Ignore interactive data definition utility (IDDU) data definitions for program-described files.
1: Use IDDU data definitions for program-described files.

BIT(1)

qdbqterr

Tolerate decimal data errors.

0: Decimal data errors result in an exception being issued.
1: Decimal data errors are ignored.

BIT(1)

qdbqdt_2

Reserved.

BIT(1)

qdbqintd

Integer division.

0: Do not perform integer division.
1: Perform integer division. Division of two integer (binary) numbers produces a zero precision result.

BIT(1)

qdbqdt_3

Reserved.

BIT(1)

qdbqchgx

Changed files exception.

0: No exception requested.
1: Send an exception when a queried file has changed since creation of the input access plan.

BIT(1)

qdbqsaap

Precision reduction (SAA^®)

0: Allow precision reduction.
1: Disallow reduction of the precision of a derived result. Instead, reduce significant digits when necessary.

BIT(1)

qdbqddmx

Distributed data management (DDM) files exception.

0: No exception requested.
1: Send an exception when a queried file is a DDM file.

BIT(1)

qdbqraut

Resolve authority.

0: Normal authority checking. User must have corresponding data authority for each open option.
1: Check for at least one data authority (read, add, update, or delete) regardless of the open options.

BIT(1)

qdbqsqlb

SQL definition of binary.

0: Binary fields have digits as known by the database.
1: Binary fields in SQL tables and views have 11 digits if the binary is large and 5 digits if it is small.

BIT(1)

qdbqaltc

Alternate computation.

0: Do not use alternate computation.
1: Use alternate computation. Some derivations do not overflow as fast when no precision reduction (SAA) is allowed (qdbqsaap=1). Also, use the user-defined result field size for one-operation derivations (+, -, *, /).

BIT(1)

qdbqsubq

The query definition template contains at least one subquery. This does not span across unions.

BIT(1)

qdbqsubx

Subcharacters exception. This field specifies what to do if, during CCSID compatibility processing, a conversion occurs on the data such that information may be lost or misinterpreted.

0

Allow the query to finish. Information messages are returned if this condition occurs.

1

Send an exception.

For literals and host variable values, the exception is sent during the open operation; check the query definition template or create an access plan of the query if subcharacters were used during the conversion of the value.

For fields and conversion tables, the error occurs during I/O operations on the query if subcharacters are used.

BIT(1)

qdbqdt_4

Reserved.

BIN(2)

qdbqkunum

The NODUPKEY number of key fields. The database does not return any records with duplicate keys and determines this by using this number of key fields as a comparison length. -1 indicates that all the key fields are used as a comparison length. This field is not applicable if field qdbqkeyo is -1.

BIN(4)

qdbqnumrcd

This field is no longer supported. Its value will be ignored.

BIN(4)

qdbqnxqo

Offset to next query definition template. This value is 0 if this is the only query definition template or if this is the last query definition template.

BIN(4)

qdbqtoso

Offset to the set operation specifications. The set operation specifications can only be specified on the last query definition template. 0 indicates that no set operation is to occur. If only one query definition query template is specified, this offset is ignored.

CHAR(4)

qdbqdt_5

Reserved.

BIN(4)

qdbqqdto

Offset to the query definition template table containing offsets to all query definition templates between unions.

CHAR(8)

qdbqdt_6

Reserved. (Ref #2.)

CHAR(1)

qdbqdfmt

Date format used as the preferred format for validity checking a date string or when mapping a character field to a date.

X'FE': Job default format
X'01': USA format
X'03': ISO format
X'05': EUR format
X'07': JIS format
X'17': MDY format
X'18': DMY format
X'19': YMD format
X'1A': JUL format

When the value of this field is X'FE', the preferred format is obtained from the job attributes, which have the value X'17', X'18', X'19', or X'1A'. (Ref #3.)

CHAR(1)

qdbqdsep

Date separator used as the preferred format for validity checking a date string or when mapping a character field to a date. It is only set when field qdbqdfmt is X'FE', X'17', X'18', X'19', or X'1A'.

X'00': Job default separator
X'EE': Implied separator
/: Slash separator
-: Dash separator
.: Period separator
,: Comma separator
Blank: Blank separator

When the value of this field is X'00', the preferred separator is obtained from the job attributes, which are one of the previously defined values except X'00' and X'EE'. When the value of this field is X'EE', the implied separator for the format is used.

(Ref #4.)

CHAR(1)

qdbqtfmt

The time format used as the preferred format when validity checking a time string or when mapping a character field to a time.

X'FE': Job default format
X'01': USA format
X'03': ISO format
X'05': EUR format
X'07': JIS format
X'1B': HMS format

When the value of this field is X'FE', the preferred format is obtained from the job attributes, which will have the value X'1B'. (Ref #5.)

CHAR(1)

qdbqtsep

The time separator used as the preferred separator when validity checking a time string or when mapping a character field to a time. It is only set when field qdbqtfmt is X'FE' or X'1B'.

X'00': Job default separator
X'EE': Implied separator
.: Period separator
,: Comma separator
Blank: Blank separator
:: Colon separator

When the value of this field is X'00', the preferred separator is obtained from the job attributes, which are one of the values previously defined except X'00' and X'EE'. When the value of this field is X'EE', the implied separator for the format is used.

BIN(2)

qdbqcsdc

The coded character set identifier (CCSID) constant tag. If nonzero, this specifies the CCSID with which the literals in the query definition template should be tagged. If this field specifies a single-byte character set (SBCS) CCSID and a literal has double-byte character set (DBCS) data, the associated mixed CCSID of the SBCS CCSID is used for the literal. Conversely, if the literal is SBCS and this field specifies a mixed or DBCS CCSID, the associated SBCS CCSID is used for the literal.

CHAR(2)

qdbqdt_7

More flags.

BIT(1)

qdbqvlit

Variable length literal.

0: No
1: Yes

BIT(5)

qdbqdt_8

Reserved. (Ref #6.)

BIT(1)

qdbqopta

Optimize all indexes over the query files.

0: The optimizer determines how many indexes to consider when optimizing the query. The optimizer times out if the time spent optimizing becomes significant when compared to the time it takes for the query to run.
1: Optimize all indexes built over the query files. This may increase the time it takes for the optimization of the query to occur.

BIT(1)

qdbqmapbd

Reserved.

BIT(7)

qdbqdt_9

Reserved.

BIT(1)

qdbq_force
_temp

Force query records to a temporary result. Note that this is only honored if set on for the last, outermost (that is, non-subquery) QDT of a union. It is ignored for all other QDTs of the query.

0: Normal processing.
1: Force results of entire query into a temporary result.

CHAR(2)

QDBQDT_10

Reserved.

qdbqfbk

Query feedback. The following information is returned on successful completion of the query.

CHAR(2)

qdbqqtyp_t

Query status indicators.

BIT(1)

qdbqtemp

Temporary result.

0: No temporary result.
1: Temporary result created.

BIT(1)

qdbqcomp

Selection completion.

0: Selection is not complete.
1: Selection is complete.

If selection is complete, the open feedback area contains the number of selected records. If selection is not complete, record selection may be performed while reading the records and the open feedback may indicate more records than are ultimately selected.

BIT(1)

qdbqdt_11

Reserved.

BIT(1)

qdbqacpi

Access plan indicator.

0: No access plan present.
1: Query definition template is or is part of an access plan.

BIT(1)

qdbqsreg

This field is set on if the query definition template contains special registers CURRENT USER, CURRENT SERVER, or CURRENT TIMEZONE.

BIT(1)

qdbqsubw

Subcharacters warning. This field specifies whether during CCSID compatibility processing, subcharacters are used in the query.

0: No subcharacters are used.
1: Subcharacters are used in the query.

BIT(1)

qdbqsqlt

SQL tables.

0: Not all SQL tables.
1: All SQL tables.

BIT(1)

qdbqlblst

The library list was used to determine the referenced table. This is possible due to the use of the Override with Database File (OVRDBF) command.

BIT(1)

qdbqcurdt

The query definition template contains CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP.

BIT(7)

qdbqdt_12

Reserved.

CHAR(28)

qdbqdt_13

Length of query definition structure.

BIN(4)

qdbqdtln

Length of query definition.

CHAR(24)

qdbqdt_14

Reserved.

106

CHAR(1)

qdbqdofmt

Date format for output date fields.

X'FE': Job default format
X'FF': Format specified with based-on field.
X'01': USA format
X'03': ISO format
X'05': EUR format
X'07': JIS format
X'17': MDY format
X'18': DMY format
X'19': YMD format
X'1A': JUL format

If the data type Qddfftyp is unknown (X'FFFF') and this field is X'FF', the format and separator are taken from those specified with the based-on field. If the data type Qddfftyp is date (X'000B'), the format and separator are taken from the extension of record formats Qddfdttf and Qddfdtts. However, if Qddfdttf is X'FF', the format and separator are taken from qdbqdofmt and qdbqdosep. If either of these fields are not valid, it is an error. When the value of qdbqdofmt is X'FE', the format is obtained from the job attributes, which will have the value X'17', X'18', X'19', or X'1A'.

Qddfftyp, Qddfdttf, and Qddfdtts are part of the QDBRTVFD include.

107

CHAR(1)

qdbqdosep

Date separator used as the output separator for fields. It is only set when qdbqdofmt is X'FE', X'17', X'18', X'19', or X'1A'.

X'00': Job default separator
X'EE': Implied separator
/: Slash separator
-: Dash separator
.: Period separator
,: Comma separator
Blank: Blank separator

When the value of this field is X'00', the separator is obtained from the job attributes (any of the preceding values except X'00' and X'EE'). When the value of this field is X'EE', the implied separator for the format is used.

108

CHAR(1)

qdbqtofmt

Time format for output time fields.

X'FE': Job default format
X'FF': Format specified with based-on field.
X'01': USA format
X'03': ISO format
X'05': EUR format
X'07': JIS format
X'1B': HMS format

If the data type Qddfftyp is unknown (X'FFFF') and this field is X'FF', the format and separator are taken from those specified with the based-on field. If the data type Qddfftyp is time (X'000C'), the format and separator are taken from the extension of record formats Qddfdttf and Qddfdtts. However, if Qddfdttf is X'FF', the format and separator are taken from qdbqtofmt and qdbqtosep. If either of these fields are not valid, it is an error. When the value of qdbqtofmt is X'FE', the format is obtained from the job attributes, which have the value X'1B'.

Qddfftyp, Qddfdttf, and Qddfdtts are part of the QDBRTVFD include.

109

CHAR(1)

qdbqtosep

Time separator used as the output separator for fields. It is only set when qdbqtofmt is X'FE' or X'1B'.

X'00': Job default separator
X'EE': Implied separator
.: Period separator
,: Comma separator
:: Colon separator

When the value of this field is X'00', the separator is obtained from the job attributes (any of the above values except X'00' and X'EE'). When the value of this field is X'EE', the implied separator for the format is used.

110

CHAR(1)

qdbqtsofmt

Timestamp format for output timestamp fields.

X'FE': Job default format
X'FF': Format specified with based-on field
X'09': SAA timestamp format

If the data type Qddfftyp is unknown (X'FFFF') and this field is X'FF', the format and separator are taken from those specified with the based-on field. If the data type Qddfftyp is timestamp (X'000D'), the format and separator are taken from the extension of record formats Qddfdttf and Qddfdtts. However, if Qddfdttf is X'FF', the format and separator are taken from qdbqtsofmt and qdbqtsosep. If qdbqtsofmt contains a format that is not valid, it is an error.

Qddfftyp, Qddfdttf, and Qddfdtts are part of the QDBRTVFD include.

111

CHAR(1)

qdbqdt_15

Reserved.

112

BIN(4)

qdbq_optmrows

Optimization option. This field tells the optimizer that the user does not intend to retrieve more than n records from the query result. n can be any integer as long as it fits in a BIN(4) type. If the optimizer optimizes for n records, this could improve performance. Specifying a number does not mean the user cannot retrieve more than n records. It just tells the optimizer to optimize for only n records. For more information about the OPTIMIZE clause, see the DB2^® for IBM^® i SQL reference topic collection.

116

CHAR(12)

qdbqdt_16

Reserved.

128

BIN(4)

qdbq_jrefo

Offset to JREF Join specification.

132

CHAR(44)

qdbqdt_65

Reserved.

176

CHAR(2)

qdbq_posnopts
_t

The ORed byte containing an indicator for every scrolling option that is used for this query. Scrolling option of next is always assumed.

176

BIT(1)

qdbq_posnopts
_prior

Scrolling to previous record is used.

176

BIT(1)

qdbq_posnopts
_first

Scrolling to first record is used.

176

BIT(1)

qdbq_posnopts
_last

Scrolling to last record is used.

176

BIT(1)

qdbq_posnopts
_before

Scrolling to before the first record is used.

176

BIT(1)

qdbq_posnopts
_after

Scrolling to after the last record is used.

176

BIT(1)

qdbq_posnopts
_current

Retrieval of the current record is used.

176

BIT(1)

qdbq_posnopts
_relative

Scrolling to a record relative to the current record is used.

176

BIT(9)

qdbqdt_17

Reserved.

178

CHAR(1)

qdbq_ext_bits

Miscellaneous bits in the query definition header.

178

BIT(1)

qdbq_ctlblk

An indicator that the caller will be control record blocking; therefore, ignore SEQONLY() overrides.

178

BIT(1)

qdbq_norolb

Rollback HOLD can leave the position of this cursor as unknown.

178

BIT(1)

qdbq_stream
_cursor

The user of this query attempts to read records from this query as fast as possible.

178

BIT(4)

qdbqdt_54

Reserved.

178

BIT(1)

qdbqdt_18

Reserved.

179

CHAR(1)

qdbq_ext_bits2

Miscellaneous bits in the query definition header.

179

BIT(2)

qdbqdt_57

Reserved.

179

BIT(1)

qdbq_trust
_posn

Trust scrolling option

0: Query optimizer assumes that any type of cursor positioning may be done.
1: Settings of qdbq_posnopts can be trusted. The user that built this QDT has knowingly set the options and could experience problems if cursor positioning not indicated is attempted. This bit should be set on with qdbq_posnopts set to '0000'X to give the query optimizer more flexibility in choosing the best data access method and also to enable symmetric multiprocessing (SMP) methods such as parallel table scan and hash join.

179

BIT(5)

qdbqdt_58

Reserved.

180

CHAR(1)

qdbq_ext_bits3

Miscellaneous bits in the query definition header.

180

BIT(1)

qdbqdt_62

Reserved.

180

BIT(1)

qdbq_searched_ update

Indicator if this is a searched UPDATE QDT.

180

BIT(1)

qdbq_searched_ delete

Indicator if this is a searched DELETE QDT.

180

BIT(2)

qdbqdt_63

Reserved.

180

BIT(1)

qdbq_drvtbl

Indicates that this QDT is part of a derived table QDT.

180

BIT(2)

qdbqdt_64

Reserved.

181

CHAR(15)

qdbqdt_19

Reserved.

196

CHAR(10)

qdbpopnid

Optional OPENID to identify this query. *FILE indicates the name of first or only file specified in the file specification. You can also specify a name to associate with opened query file.

206

BIN(4)

qdbspcsize

API users specify size for space containing all API query definition templates. (Ref #7.)

210

BIN(4)

qdbqnlss

Displacement to QQQNLSS_T structure (Sequence, Tables, Names, and Parameters (QQQNLSS_T)) used for sort sequence information. This is an offset from the beginning of the query definition template. 0 indicates no QQQNLSS_T structure was passed. For nonviews, if this is a union or subquery, this field is ignored unless it is the first query definition template (for unions), or the outermost query definition template (for subqueries).

214

BIN(2)

qdbqsrts

Sort sequence indicator. Possible values for this field follow. When you use value 2 or 3, you must include the QQQNLSS_T structure (Sequence, Tables, Names, and Parameters (QQQNLSS_T)) at offset qdbqnlss.

0: *HEX
2: The table or CCSID passed in structure QQQNLSS_T.
3: The sort sequence and language identifier passed in structure QQQNLSS_T.

216

CHAR(5)

qdbqdt_53

Reserved.

221

CHAR(1)

qdbqic

Whether query allows index creation.

N: Index creation is not allowed.
Y or X'00': Index creation is allowed.

222

CHAR(40)

qdbqdt_69

Reserved.

262

106

CHAR(1)

qdbqdfpround

Rounding mode to use for decimal floating point conversions and computations fields.

X'00': Round half even
X'01': Round half up
X'02': Round down
X'03': Round ceiling
X'04': Round floor
X'05': Round half down
X'06': Round up

263

107

CHAR(137)

qdbqdt_20

Reserved.

Sequence, Tables, Names, and Parameters (QQQNLSS_T)

Sequence, tables, names, and parameters structure. The displacement to this structure from the beginning of structure QDBQH_T is an entry in the table at variable qdbqnlss.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(42)

qqqstb1

Sequence table name, library name, and language identifier.

CHAR(10)

qqqresv2

Reserved.

CHAR(20)

qqsrtseq

Sort sequence.

CHAR(10)

qqqstbnm

Table name. Possible special values are:

*JOB: The sort sequence of the job.
*LANGIDUNQ: The unique-weight sort sequence table that is associated with the language identifier requested parameter.
*LANGIDSHR: The shared-weight sort sequence table that is associated with the language identifier requested parameter.
*HEX: The sort sequence according to the hexadecimal value of the characters.

CHAR(10)

qqqstbnl

Library name. Possible special values are:

*LIBL: The library list.
*CURLIB: The job's current library.

CHAR(10)

qqqlangid

Language identifier. Possible values are:

*JOB: The language identifier of the job.
xxx: 3-character language identifier. See Language identifiers and associated default CCSIDs for a complete list of language identifiers supported.
Blank: If blank, field qqqsrtseq cannot be *JOB, *LANGIDUNQ, or *LANGIDSHR.

CHAR(2)

qqqresv3

Reserved.

CHAR(38)

qqqstb2

Reserved.

CHAR(2)

qqqresv4

Reserved.

CHAR(10)

qqqtbnm

Reserved.

CHAR(10)

qqqlbnm

Reserved.

CHAR(14)

qqqresv5

Reserved.

BIN(2)

qqqtbl_ccsid

Sequence table CCSID. This field is only used when either qqqtbl is specified or qqqstboff is set for a DBCS sort sequence table.

CHAR(10)

qqqstbe1

User-specified DBCS sort sequence information.

CHAR(1)

qqqstbtyp

Type of DBCS sort sequence table.

X'00': UCS-2 sort sequence table

CHAR(1)

qqqstbloc

Location of DBCS sort sequence table.

X'00': Table is stored at qqqtbl.
X'01': Table is stored at the DBCS sort sequence table offset (qqqstboff).

BIN(4)

qqqstblen

Length of DBCS sort sequence table. If an SBCS sort sequence table is specified, qqqstblen must be zero.

BIN(4)

qqqstboff

Offset to the DBCS sort sequence table from qqqstb1.

CHAR(22)

qqqresv1

Reserved.

112

CHAR(256)

qqqtbl

User-specified sort sequence table.

File Name Specification (QDBQF_T)

File name specification. This structure defines the files, member, and formats that are used in the query. This structure is required.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

qdbqfhdr

File specification header.

BIN(2)

qdbqfilnum

The number of files, libraries, formats, and members. (Ref #8.)

CHAR(1)

qdbqmfop

Multiple file option. This field is only applicable if the qdbqfilnum field is greater than one.

J: Inner join. No default values are supplied if a join value does not exist and no record is returned.
C: Partial outer join (file chaining). Default values are supplied if a join value does not exist.
E: Exception join. Default values are supplied if a join value does not exist. Only records with default values are returned.

CHAR(1)

qdbqmfor

Multiple file order option. This field is only applicable if field qdbqfilnum is greater than one, field qdbqmfop equals J, and there is no file-distinct processing. For each file specified in the file specifications, qdbqfdst equals 1. Partial-outer join, exception join, and file-distinct processing implies no join reordering.

A: Join the files in any order. The result order may vary even when rerunning the same query.
N: Join the files in the order they are specified.

CHAR(1)

qdbqdt_21

Flags.

BIT(1)

qdbqmfio

Multiple file I/O options allowed through this query.

0: Only allow read operations against the first file in the array (always read-only for secondary files).
1: Allow insert, update, or delete operations against the first file.

BIT(1)

qdbqmfjn

Join clause exists. An SQL JOIN clause syntax exists in this query.

BIT(6)

qdbqdt_22

Reserved.

CHAR(11)

qdbqdt_23

Reserved.

ARRAY(32) OF CHAR(64)

qdbqn

File, library, member, and format array. This structure is defined at QDBQN_T on File, Library, Member, and Format Array (QDBQN_T).

File, Library, Member, and Format Array (QDBQN_T)

File, library, member and format names array. This structure defines the file, library, member, and format names that are used in the query. This structure is required.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(40)

qdbqflmf

File, library, member, and record format names.

CHAR(10)

qdbqfile

File name. If an override is in effect to another file, the actual file name is returned in this field.

CHAR(10)

qdbqlib

Library name. If the special value *LIBL is used, the actual library name is resolved and returned in this field.

CHAR(10)

qdbqmbr

Member name. If the special values *FIRST or *LAST are used, the actual member name is resolved and returned in this field.

CHAR(10)

qdbqfmt

Format name. If the special value *ONLY is used, the actual format name is resolved and returned in this field.

CHAR(1)

qdbqfflg

File specification flags.

BIT(1)

qdbqfdst

File-distinct flag. This field specifies, for the records that make up the join secondaries for a join query, whether only the first record or all records that satisfy the join conditions should participate in the join. This flag only applies to join secondary files (files 2 through n, where n equals the number of files in the join).

0: All records participate.
1: Only the first record participates.

BIT(1)

qdbqfujn

Unique join fanout. This field specifies whether the number of join records found can exceed 1. This field only applies to join secondary files (files 2 through n, where n equals the number of files in the join).

0: Multiple join records are allowed.
1: Only one join-to record may be found for this file.

BIT(1)

qdbqfgna

Reserved.

BIT(1)

qdbqfngn

Reserved.

BIT(1)

qdbqnmch

Name change indicator

0: The library, file, or member name in the specified query definition template (at offset qdbqfilo in structure qdbqh_t, Query Definition Header (QDBQH_T)) did not change as a result of an override.
1: The library, file, or member name in the specified query definition template (at field qdbqfilo) changed due to an override.

BIT(1)

qdbqflbo

Library name overridden.

0: The library name in the specified query definition template (at offset qdbqfilo in structure qdbqh_t, Query Definition Header (QDBQH_T)) did not change as a result of an override.
1: The library name in the specified query definition template (at offset qdbqfilo) changed to *LIBL due to an override, and the file was found using *LIBL as the library name.
This bit is a feedback bit. The user of the query definition template should not set it.

BIT(1)

qdbqf_nldft

Null or default. The type of values to be returned for unmatched records of a partial outer or exception join.

0: Return default values
1: Return NULL values

BIT(1)

qdbqdt_24

Reserved.

CHAR(1)

qdbqmfvw

Reserved.

CHAR(1)

qdbqmfvw_spc

Reserved.

BIN(2)

qdbqf_qdtnum

Index into the array of subquery offsets (QDBQQDT_T) for the derived table QDT.

CHAR(19)

QDBQDT_25

Reserved.

Record Format Specification (QDBQR_T)

Record format specification. This structure defines the fields that are used in the query. The structure Qdb_Qddfmt_t is mapped by member QDBRTVFD in the QSYSINC library. If join is specified, this specification is required.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(*)

QDBQR

Record specifications.

Join Specification (QDBQJ_T)

Join specification. This structure defines how the files are joined by the query. One join specification exists for the entire query definition. A join specification entry consists of a from-field, a join operator, and a to-field. The join specification entries can be inserted in any order with respect to the file specifications.

If this is an inner join (the qdbqmfop field equals J, and no join specifications are given for a particular to-file, the system searches the record selection specifications for any possible implied join specifications. If no join specifications can be derived from the record selection specifications, Cartesian product is used to do the join.

All join specifications can be given in the record selection specifications. In this case, it is not necessary to provide a join specification.

If this is a partial-outer or exception join (qdbqmfop equals C or E) and no join specifications are given for a particular to-file, the system uses Cartesian product to do the join. In addition, only one join operator can be specified for a particular to-file.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

qdbqjhdr

Join specifications header.

BIN(4)

qdbqjln

Length of this join specification.

BIN(2)

qdbqjknum

Number of from-join and to-join field specifications.

CHAR(10)

qdbqdt_26

Reserved.

ARRAY(1) OF CHAR(96)

qdbqjfld

Join specification array. Array of fields that define the from and to fields to use when joining. The structure is defined at QDBQJFLD_T on Join Specification Array (QDBQJFLD_T).

112

CHAR(*)

QDBQJNXT

Join field pair arrays. Displacement to join specifications array from structure QDBQJ_T (see structure QDBQJFLD_T on Join Specification Array (QDBQJFLD_T)).

Join Specification Array (QDBQJFLD_T)

Join specification array. This structure is an array of fields that define the from and to fields to use when joining.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(30)

qdbqjfnm

Join from field name.

BIN(2)

qdbqjfnum

Field join reference number. 0 indicates that the QDBQR_T format (see Record Format Specification (QDBQR_T)) is searched for the external field name. If the field is not found, the formats of the files in the file specification are searched. A value in this field indicates that the external field name is to be found in the file format referenced by using this value as an index into the file name specification structure, qdbqf_t, (see File Name Specification (QDBQF_T)). In any case, the field found must exist in a file joined prior to this file.

CHAR(2)

qdbqdt_27

Reserved.

CHAR(2)

qdbqjop

Join option.

EQ: Equal
GT: Greater than
LT: Less than
NE: Not equal
GE: Greater than or equal
LE: Less than or equal

CHAR(30)

qdbqjtnm

Join to field name. Note that only character and any DBCS fields may be joined to character and any DBCS fields, and only numeric fields may be joined to numeric fields. The lengths of the two fields need not be the same. However, if they are different, a warning is sent to the user indicating that padding occurred.

BIN(2)

qdbqjtnum

Field join reference number. 0 indicates that the QDBQR_T format (see Record Format Specification (QDBQR_T)) is searched for the external field name.

If the field is found, it must have been completely derived from the file associated with this join specification. If the field is not found, the format of the file associated with this join specification is searched.

A value in this field indicates that the external field name is to be found in the file format referenced by using this value as an index into the file list. This value must reference the file associated with this join specification.

CHAR(1)

qdbqjpfmt

Reserved.

CHAR(1)

qdbqjpsep

Reserved.

BIT(1)

qdbqjfprf

Reserved.

BIT(1)

qdbqjvw

Reserved

BIT(1)

qdbqj_type_sup

Join type specified. The type of join is specified in field qdbqj_type.

BIT(5)

qdbqdt_oj

Reserved.

CHAR(1)

qdbqj_type

Type of join.

J: Inner join
C: Partial outer join
E: Exception join

CHAR(24)

qdbqdt_28

Reserved.

JREF Join Specification (QDBQ_JREF_T)

JREF Join specification. This structure can be used to define the order in which the files are to be joined. It can also be used to specify any join selection needed to implement the join. Two files (or join results) are specified along with the appropriate join type to be used to join together the two operands. An offset can also be specified to the Selection Specifications (QDBQS) that will define the join criteria that applies to the operands.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

qdbq_jref_hdr

JREF Join specifications header.

BIN(4)

qdbq_jref_len

Length of this JREF join specification.

BIN(2)

qdbq_jref#

Number of JREF Join entries.

CHAR(10)

qdbqdt_66

Reserved.

CHAR(*)

qdbq_jref_spec

Start of the JREF Join entries. The structure is defined at QDBQ_JREF_ENTRY_T on JREF Join Entry (QDBQ_JREF_ENTRY_T).

JREF Join Entry (QDBQ_JREF_ENTRY_T)

JREF Join Entry.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(2)

qdbq_jref_ entry_type

JREF Join entry type.

0: Join operand
2: Join operator

CHAR(*)

qdbq_jref_item

JREF Join items. The structure is defined at QDBQ_JREF_OPERAND_T and QDBQ_JREF_OPERATOR_T on JREF Join Specification (QDBQ_JREF_T).

JREF Join Operand (QDBQ_JREF_OPERAND_T)

JREF Join entry operand.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(2)

qdbq_jref_file#

JREF Join reference number. The value in this field is used to identify the entry in the QDBQN array associated with this file.

CHAR(8)

qdbqdt_67

Reserved.

JREF Join Operator (QDBQ_JREF_OPERATOR_T)

JREF Join entry operator.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbq_jref_jselo

JREF Join entry offset. Offset to the join selection for this JREF Join predicate. The join selection is defined by the Selection Specifications (QDBQS).

CHAR(1)

qdbq_jref_jtype

JREF Join type. Type of the join specified for this JREF Join predicate.

J: Inner join
C: left partial outer join
E: Exception join

CHAR(5)

qdbqdt_68

Reserved.

Record Selection Specification (QDBQS_T)

Record selection specification. This structure defines the selection specifications for the files being queried. Selection on the file is done before grouping. If selection is desired on group by results, see structure QDBQGS_T on Group-by-Selection Specification (QDBQGS_T).

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbqsl

Selection specifications length. This is the total length of all selection specifications.

BIN(2)

qdbqsnum

Number of selection specifications.

CHAR(10)

qdbqdt_29

Reserved. (Ref #9.)

CHAR(*)

qdbqspec

Start of selection specifications. Displacement to selection item specifications array from structure QDBQS_T (see structure QDBQSIT_T on Selection Item Specifications (QDBQSIT_T)).

Selection Item Specifications (QDBQSIT_T)

Selection item specifications. This structure is defined at field qdbqspec in structure QDBQS_T.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbqslen

Selection item length. This length includes the length (QDBQSIT_T) plus the length of the selection item structure.

BIN(2)

qdbqsitt

Selection item type.

0: Field operand
1: Constant operand
2: Operator
3: Subquery operand
4: Null operand (SAA). This operand is used for is null and is not null functions. Only equal and not equal operators are allowed.

(Ref #10.)

CHAR(*)

qdbqsitm

Selection item. This field is overlaid by the sequence of selection field structures.

Selection Field Operand (QDBQSOPF_T)

Selection field operand. This structure overlays field qdbqsitm in structure QDBQSIT_T.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(30)

qdbqsofn

Field name. The field name must be an external name.

BIN(2)

qdbqsofj

A value in this field indicates that the external field name is to be found in the file format referenced by using this value as an index into the file list.

BIN(2)

qdbqsoqt

Index into the query-definition-template table for the correlated field's associated query definition template. Use zero for noncorrelated fields.

CHAR(24)

qdbqdt_30

Reserved.

Selection Field Subquery Operand (QDBQSOPS_T)

Selection field subquery operand. This structure overlays field qdbqsitm in structure QDBQSIT_T).

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(2)

qdbqssub

Index into the query-definition-template offset table for the subquery's query definition template.

CHAR(1)

qdbqstyp

Subquery operator qualifier.

X'00': Use the qdbqsop field only
X'01': ALL
X'02': ANY or SOME

Valid values for qdbqsop when qdbqstyp equals 00 are:

Basic predicate: 0001-0006
Exists: 0045
In: 0046

Valid values for qdbqsop when qdbqstyp is not equal to 00 are:

Operator: 0001-0006

CHAR(23)

qdbqdt_31

Reserved.

Selection Constant Operand (QDBQSOPC_T)

Selection Constant Operand. This structure overlays field qdbqsitm in structure QDBQSIT_T.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR (32793)

qdbqsoch

Constant operand header.

BIN(4)

qdbqsocl

Constant operand byte length. This only includes the length of the constant in field qdbqsovl, including apostrophes.

CHAR(1)

qdbqdt_32

Constant attributes.

BIT(1)

qdbqsoci

DBCS open constant.

0: This constant is not a DBCS-open literal.
1: This constant is a DBCS-open literal.

BIT(1)

qdbqdt_33

Reserved.

BIT(1)

qdbqsocc

Character constant type.

0: Character string in apostrophes. The character constant is bracketed by apostrophes and any imbedded apostrophes must be represented by two apostrophes.
1: Character string not in apostrophes. The character constant is not bracketed by apostrophes.

If it is determined during query processing that the constant should be numeric and if field qdbqsoac in this table is 0, this bit is ignored.

BIT(1)

qdbqsoac

Character constant.

0: Do not assume that this is a character constant. Determination of the type of constant is made during query processing.
1: Assume that this is a character constant.

BIT(1)

qdbqsoco

DBCS-only constant.

0: This constant is not DBCS-only.
1: This constant is DBCS-only.

BIT(1)

qdbqsosr

Special register.

0: This constant operand is not a special register.
1: This constant operand is a special register, defined by the qdbqsorc field.

BIT(1)

qdbqsonl

SAA NULL indicator.

0: This constant operand is not a NULL literal.
1: This constant operand is a NULL literal.

The query definition template is synchronized with the format description.

BIT(1)

qdbqdt_34

Reserved.

CHAR(1)

qdbqsorc

Special register constant. This field is defined by special register constants declared in the record format definition. This field can only be specified if field qdbqsosr is on.

X'01': User
X'02': Current date
X'03': Current time
X'04': Current timestamp
X'05': Current time zone
X'06': Current server

CHAR(1)

qdbqsoft

Date, time, timestamp, format attribute. This field applies only to date, time, or timestamp literals.

X'FE': Job default
X'FF': Determine format
X'01': USA format
X'03': ISO format
X'05': EUR format
X'07': JIS format
X'09': SAA timestamp
X'17': MDY format
X'18': DMY format
X'19': YMD format
X'1A': JUL format
X'1B': HMS format
X'1D': YYYYNNN format
X'1E': YYYYMMDDHHMMSS format

When the value of this field is X'FF', the format and separator specified in the query-definition-template header (either the qdbqdfmt field or the qdbqtfmt field, and either the qdbqdsep field or the qdbqtsep field, for a date or time literal is used first in determining the format and separator of the literal.

When the value of this field is X'FE' for a date or time literal, the format and separator are determined using the job attributes. The format value may be X'17', X'18', X'19', X'1A', or X'1B'. The separator specified for qddfdvsp is used first in determining the format and separator.

When the value of this field is X'FE' for a timestamp literal, the SAA timestamp format is used as the format of the literal.

CHAR(1)

qdbqsosp

Date and time separator. This field applies only to date or time literals. It should only be set when the qdbqsoft field is X'FE', X'17', X'18', X'19', X'1A', or X'1B'.

X'00': Job default separator
X'EE': Implied separator
/: Slash separator
-: Dash separator
.: Period separator
,: Comma separator
Blank: Blank separator
:: Colon separator

When the value of this field is X'00', the separator is obtained from the job attributes, which will be one of the preceding values except X'00' or X'EE'. When the value of this field is X'EE', the implied separator for the format is used.

CHAR(2)

qdbqdt_35

Reserved.

BIN(2)

qdbqsocd

CCSID value for this literal. If not set to zero, the literal will be tagged with this CCSID. Otherwise, the literal will be tagged with the CCSID specified in the query-definition-template header (see Query Definition Header (QDBQH_T)) or the job default, in that order. This field is only valid for character, DBCS-open, DBCS-only, DBCS-graphic, and UCS-2 literals.

CHAR(1)

qdbqdt_36

Reserved.

BIT(2)

qdbqdt_37

Reserved.

BIT(1)

qdbqglit

An indicator that the constant is a DBCS-graphic or UCS-2 literal. If this field is a UCS-2 literal, qdbqsocd must be set to a valid UCS-2 CCSID, or qdbqsocd must be zero and qdbqcsdc (see Query Definition Header (QDBQH_T)) must be set to a valid UCS-2 CCSID.

BIT(5)

qdbqdt_38

Reserved.

CHAR(29)

QDBQDT_39

Reserved. (Ref #11.)

CHAR (32751)

qdbqsovl

Operand value. The operand value must be in external form.

Selection Operator Item (QDBQSOPR_T)

Selection Operator Item. This structure overlays field qdbqsitm in structure QDBQSIT_T.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(2)

qdbqsop

Operators. Relational operators:

X'0001': Equal
X'0002': Not equal
X'0003': Greater than or equal
X'0004': Less than or equal
X'0005': Greater than
X'0006': Less than
X'0007': Range (inclusive)
X'0041': Scan
X'0042': Wildcard scan
X'0043': Values
X'0045': Exists
X'0046': In

Boolean operators:

X'000B': OR
X'000C': XOR
X'000D': AND
X'000E': NOT

Case selection operators:

X'0018': WHEN
X'001B': ELSE

Case operators are only valid when specified as part of a case selection specification.

CHAR(1)

qdbqswc1

Wildcard value for any single character. This character indicates the value in the character string operand that should be interpreted as matching any single character. This field is only applicable if the qdbqsop field is a wildcard scan.

CHAR(1)

qdbqswc2

Wildcard value for any number of characters. This character indicates the value in the character string operand that should be interpreted as matching any number of characters. This field is only applicable if the qdbqsop field is a wildcard scan.

BIN(2)

qdbqvalcnt

Values operand count. This count reflects the number of selection constant operands (values) associated with the values operator. This count must be set if the operator is values and is ignored for all other operators.

CHAR(1)

qdbqdt_55

Selection operator flags.

BIT(1)

qdbqescp

Wildcard escape character indicator. This field is valid only for wildcard scan.

0: There is no escape character.
1: There is an escape character specified for the wildcard scan operator by using the third operand.

BIT(1)

qdbqdt_56

Reserved.

BIT(1)

qdbqsopr_ext

Selection operator extension area indicator.

0: Operator extension area (QDBQSOP3_T) does not exist.
1: Operator extension area (QDBQSOP3_T) exists.

BIT(5)

qdbqdt_60

Reserved.

CHAR(3)

qdbqdt_40

Reserved.

Note: The following fields are not present in a query definition restored from a System/38™.

CHAR(14)

qdbqsop2

Wildcard value for double-byte characters

CHAR(2)

qdbqsdbl

Wildcard value for any one double-byte character. This value indicates the value in the DBCS string operand that should be interpreted as matching any one double-byte character. This field is only applicable if field qdbqsop is a wildcard scan and string operand is a DBCS or graphic pattern.

CHAR(2)

qdbqsdb2

Wildcard value for any number of double-byte characters. This value indicates the value in the double-byte string operand that should be interpreted as matching any number of double-byte or single-byte characters. This field is only applicable if field qdbqsop is a wildcard scan and the string operand is a DBCS or graphic pattern.

CHAR(3)

qdbqdt_41

Reserved.

CHAR(2)

qdbqsuo1

Half-width wildcard value for any one double-byte UCS-2 character. This value indicates what value in the UCS-2 operand matches any one double-byte UCS-2 character. This field is only applicable if qdbqsop is a wildcard scan and the pattern is a UCS-2 parameter marker, host variable value, or constant.

CHAR(2)

qdbqsuo2

Full-width wildcard value for any one double-byte UCS-2 character. This value indicates what value in the UCS-2 operand matches any one double-byte UCS-2 character. This field is only applicable if qdbqsop is a wildcard scan and the pattern is a UCS-2 parameter marker, host variable value, or constant.

CHAR(2)

qdbqsum1

Half-width wildcard value for any number of double-byte UCS-2 characters. This value indicates what value in the UCS-2 operand matches any number of double-byte UCS-2 characters. This field is only applicable if qdbqsop is a wildcard scan and the pattern is a UCS-2 parameter marker, host variable value, or constant.

CHAR(2)

qdbqsum2

Full-width wildcard value for any number of double-byte UCS-2 characters. This value indicates what value in the UCS-2 operand matches any number of double-byte UCS-2 characters. This field is only applicable if qdbqsop is a wildcard scan and the pattern is a UCS-2 parameter marker, host variable value, or constant.

CHAR(1)

qdbqdt_59

Reserved.

Note: For the wildcard scan operator (qdbqsop=X'0042'), UTF-8 wildcard values should not be specified. If either the match operand, the pattern operand, or the escape character are UTF-8, specify both the EBCDIC equivalents (qdbqswc1, qdbqswc2, qdbqsdb1, and qdbqsdb2) and the UCS-2 equivalents (qdbqsuo1, qdbqsuo2, qdbqsum1, and qdbqsum2).

Selection Operator Item Extension (QDBQSOP3_T)

Selection Operator Item Extension. This structure overlays field qdbqsitm in structure QDBQSIT_T by following QDBQSOPR_T and is only present if qdbqsopr_ext is set to '1'.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(2)

qdbqswc_ccsid

CCSID of wildcard character values that are specified in qdbqswc1, qdbqswc2, qdbqsdb1, and qdbqsdb2. The appropriate associated CCSID is determined depending on the CCSID of the pattern. If needed, this CCSID is used to convert the relevant wildcard characters to the CCSID of the pattern. If set to zero, it is assumed that the wildcard values are in the same CCSID as that of the pattern.

BIN(2)

qdbqswc_ ccsid_ucs2

CCSID of wildcard character values that are specified in qdbqsuo1, qdbqsuo2, qdbqsum1, and qdbqsum2. If needed, this CCSID is used to convert the relevant wildcard characters to the CCSID of the pattern. If this field is set to 0, it is assumed that the wildcard values are in the same CCSID as the pattern. If this field is specified, it must be a valid UCS-2 CCSID.

CHAR(28)

qdbqdt_61

Reserved.

Order by Specification (QDBQK_T)

Order by specification. This structure contains a description of how the results of the query should be ordered. Up to 10 000 bytes may be used in ordering.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(16)

qdbqkh

Order by header.

BIN(2)

qdbqknum

The number of key positions in the order by specifications.

CHAR(1)

qdbqkt

Key field ordering type.

U: Unique key fields
D: Duplicate key fields
F: FIFO duplicate key fields
L: LIFO duplicate key fields
C: FCFO duplicate key fields
A, X'00': Any key field is considered.

This field is only used as a guide when considering indexes. Field qdbqopta) should be set to on to consider that all indexes build over the query files.

CHAR(13)

qdbqdt_42

Reserved.

ARRAY(1) OF CHAR(64)

qdbqkf

Key specifications of 10 000.

CHAR(30)

qdbqkfld

Key field name. The field name must be an external field name from the QDBQR_T format (unless QDBQR_T is not specified, in which case the field is an external field name from the file format). For the QDBQR_T structure, see Record Format Specification (QDBQR_T). Field Qddffiob must not be X'04' (neither input nor output) for a key field.

CHAR(1)

qdbqksq

Key field sequencing attributes.

BIT(1)

qdbqksad

Ascending or descending sequencing indicator.

0: Ascending sequence
1: Descending sequence

BIT(1)

qdbqdt_43

Reserved.

BIT(1)

qdbqkabs

Absolute value sequence indicator. This bit is ignored for character key fields.

0: Numeric sequence
1: Absolute value sequence

BIT(5)

qdbqdt_44

Reserved.

CHAR(33)

qdbqdt_45

Reserved.

Group by Specification (QDBQG_T)

Group by specification. This structure contains a description of how the record results of the query should be grouped. All records for which equal values exist in the defined fields are grouped together. Up to 2000 bytes may be used.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(16)

qdbqgh

Group by header.

BIN(2)

qdbqgfnum

The number of group by fields. If the number of group fields is 0, all the records are processed as one group.

CHAR(14)

qdbqdt_46

Reserved.

ARRAY(120) OF CHAR(64)

qdbqgf

Group field specification. Up to 120 fields are allowed.

CHAR(30)

qdbqgfld

Group field name.

BIN(2)

qdbqgflj

Field-join reference number. 0 indicates that the QDBQR_T format (Record Format Specification (QDBQR_T)) is searched for the external field name. If the field is not found, the formats of the files in the file specification are searched. If the field name is found in more than one file format, an error is signaled. A value in this field indicates that the external field name is found in the file format referred to by using this value as an index into the file list.

CHAR(32)

qdbqdt_47

Reserved.

Group-by-Selection Specification (QDBQGS_T)

Group-by-selection specification. This structure defines the selection specifications for the group by results. Selection on the group results is performed after the selection on the record is complete and the grouping has been completed.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(*)

QDBQGS_T

Group-by-selection specification structure. See Record Selection Specification (QDBQS_T).

CHAR(*)

QDBQGSIT_T

The group by selection item specification structure (see Selection Item Specifications (QDBQSIT_T)).

Set Operation Specification (QDBQT_T)

Set operation specification. This structure defines the operation specifications being performed for each set of results generated from each query definition template. These specifications are only valid when more than one query definition template is specified. The set operation specifications must only be specified on the last query definition template.

The specification structure is a stack of operands and operators in reverse notation. Operands are constant literals that identify the relative position of a query definition template among others in the query-definition-template chain. Operators are set operators such as union. For example, given the following query definition templates:

Query definition templates

The following operations can be performed:

         (1st QDT) UNION (2nd QDT) UNION ALL (3rd QDT)

The above can be specified in the set operation specification (in reverse notation) as:

         1 2 UNION 3 UNION ALL

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbqtl

Set specifications length. This is the total length of all set specifications.

BIN(2)

qdbqtnum

Number of set specifications.

CHAR(10)

qdbqdt_48

Reserved. (Ref #13.)

CHAR(*)

qdbqtspc

Start of set specifications.

Set Item Specifications (QDBQTIT_T)

Set item specifications. This structure overlays field qdbqtspc in structure QDBQT_T.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbqtlen

Set item length. This length includes the length (QDBQTIT_T) plus the length of the set item structure.

BIN(2)

qdbqtitt

Set item type.

1: Constant operand
2: Operator

(Ref #14.)

CHAR(10)

qdbqtitm

Set item. Use either table QDBQtopC_T or QDBQtopR_T.

Relative Number of Query Definition Template (QDBQtopC_T)

Relative number of query definition template. This structure overlays field qdbqtitm in structure QDBQTIT_T .

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(2)

qdbqtqdt

Relative number of query definition template.

CHAR(8)

qdbqdt_49

Reserved.

Set Operators (QDBQtopR_T)

Set operators. This structure overlays field qdbqtitm in structure QDBQTIT_T .

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(2)

qdbqtop

Set operators.

X'0001': Union
X'0002': Union all

CHAR(8)

qdbqdt_50

Reserved.

Query Definition Template Offset Table (QDBQQDTS_T)

Query definition template offset table. This structure is set for each unioned outermost query definition template that contains subqueries. This offset table contains offsets for addressability to each query definition template within a union.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR(16)

qdbqqhdr

Header.

BIN(2)

qdbqqdtnum

Number of subqueries defined with offsets.

CHAR(14)

qdbqdt_51

Reserved.

ARRAY(32) OF CHAR(16)

qdbqqdt

Array of subquery offsets. See structure QDBQQDT_T (Array of Subquery Offsets (QDBQQDT_T)) for the layout.

Array of Subquery Offsets (QDBQQDT_T)

Array of subquery offsets.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(4)

qdbqo

Offset to QDT from start of first QDT in the union.

CHAR(12)

qdbqdt_52

Reserved.

Format Definition Template (Qdb_Qddfmt_t)

The format definition (Qdb_Qddfmt_t) for the QQQQRY API is the same structure that is used by the Retrieve Database File Description (QDBRTVFD) API called FILD0200. Qdb_Qddfmt_t Format shows how this information is organized. When more than one entry can appear, the figure indicates this as in (2). For a description of the fields in Qdb_Qddfmt_t and their respective offsets, see FILD0200 Format (Qdb_Qddfmt Structure) in Retrieve Database File Description (QDBRTVFD) API.

The description and offsets are also in the include source supplied with IBM i. You can see this source in member QDBRTVFD in the QSYSINC library.

The QQQQRY API builds the format definition if it was not created prior to the query.

Qdb_Qddfmt_t Format

User File Control Block (QDBUFCB_T) Structure

User file control block. This structure holds information from the user file control block (UFCB). It contains selected options for the input and output of the specified query.

The options available include:

Sequence only
Commitment control
Block records
Keyed feedback
Record length
Open options
Release number
Version number
Invocation mark count or activation group number
System environment
Null-capable fields
File dependency
Level check
Record format specifications
Secure
Shared
Open scope

In addition, some validity checking is done for this UFCB. CPF4297 is issued if any reserved space in the header of the QDBUFCB_T format is not zero.

The offsets and a description of all the fields contained in this structure are shown in the following table. You can see this source in member QQQQRY in the QSYSINC library.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

CHAR (1962)

qufcb

Query base UFCB. A character view of the entire user file control block.

CHAR(174)

reserved1

Reserved.

174

CHAR(1)

shr_secure

Share and secure flags.

174

BIT(3)

flglrsva

Reserved.

174

BIT(1)

flglshr

Share specified.

0: No type of share was specified on the UFCB.
1: SHARE(YES) or SHARE(NO) was specified on the UFCB.

174

BIT(1)

flglshsw

Share value.

0: Not share
1: Share

174

BIT(1)

flglsecr

Secure specified.

174

BIT(1)

flglud

Secure value.

0: Not secure
1: Secure

174

BIT(1)

flglsvb

Reserved.

175

CHAR(1)

open

Open flags.

175

BIT(2)

flagrsva

Reserved.

175

BIT(1)

flagui

Open input.

175

BIT(1)

flaguo

Open output.

175

BIT(1)

flaguu

Open update.

175

BIT(1)

flagud

Delete.

175

BIT(2)

flagsvb

Reserved.

176

CHAR(4)

relver

Release and version.

176

CHAR(2)

release

Release number. This value must be set to 01.

178

CHAR(2)

version

Version number. This value must be set to 00.

180

BIN(4)

invmkcnt

Mark counter of call or activation group. Set this field to the call mark count when scoping the open to the default activation group. For this case, a 0 indicates a permanent open, and any value greater than 0 indicates a temporary open. Set this field to the activation group mark count when scoping the open to an activation group.

Note: Setting this field to the default activation group is the same as specifying a call mark of 0 for a permanent open. This is the low-order four bytes of the mark count value. This field needs to be concatenated with mumkctbh to retrieve the actual invocation mark.

184

CHAR(1)

markcnt

Mark count and blocked record.

184

BIT(1)

flg2mkcp

Mark counter option.

0: The mark counter specified by the invmkcnt field is not used.
1: The mark counter specified by the invmkcnt field is used.

184

BIT(1)

flg2rsvl

Reserved.

184

BIT(1)

flg2brcd

Blocked records.

0: There are no blocked records.
1: There are blocked records.

184

BIT(5)

flg2rsv2

Reserved.

185

CHAR(1)

reserved2

Reserved.

186

CHAR(1)

invact

Mark count usage.

186

BIT(1)

flg4rsvl

Reserved.

186

BIT(1)

flg4iact

Mark counter usage.

0: The mark counter specified by the invmkcnt field contains a call mark.
1: The mark counter specified by the invmkcnt field contains an activation group number.

186

BIT(6)

flg4rsv2

Reserved.

187

CHAR(1)

reserved2a

Reserved.

188

CHAR(1)

native

System environment and process NULLS.

188

BIT(2)

flg3rsvl

Reserved.

188

BIT(1)

flg3ntve

This field must be set to 1.

188

BIT(3)

flg3rsv2

Reserved.

188

BIT(1)

flg3null

Process null-capable fields.

188

BIT(1)

flg3rsv3

Reserved.

189

CHAR(2)

reserved3a

Reserved.

191

CHAR(1)

opnscp

Open scope.

A: Open is scoped to the specified activation group, or if this is the default activation group and a call mark is specified, the open is scoped to the program at the call mark specified.
J: Open is scoped to the job.
X'00': Not specified. The value A is assumed.

192

CHAR(5)

reserved3b

Reserved.

197

BIN(4)

mumkctbh

High-order four bytes of the mark count value making a total of eight bytes. This needs to be concatenated with invmkcnt to obtain the actual invocation mark.

201

CHAR(7)

reserved3

Reserved.

Note: The parameter field through the ufcbend field are repeated in the variable-length data area for each parameter.

208

CHAR(73)

parameter

Variable parameters.

208

BIN(2)

primrlnl

Primary record length. Initialize to -1 to deactivate.

210

BIN(2)

primrlnv

The user-specified record length.

212

BIN(2)

filedep

File-dependent I/O. Initialize to -3 to deactivate.

214

BIT(1)

fildonoff

File-dependent option.

On: This is file dependent.
Off: This is not file dependent.

214

BIT(7)

fldrsvl

Reserved.

215

BIN(2)

lvlchk

Level-check option. Initialize to -6 to deactivate.

217

BIT(1)

lvlonoff

Level-check option.

On: Perform level checking
Off: Do not perform level checking

217

BIT(7)

lvlrsvl

Reserved.

218

BIN(2)

recfmts

Record format sequence numbers for level checking.

220

BIN(2)

maximum

The maximum number of formats.

222

BIN(2)

curnum

The current number of formats.

224

ARRAY(75) of CHAR(23)

formats

Array of format names and sequence numbers

224

CHAR(10)

name

The format name.

234

CHAR(13)

number

The format sequence number.

1949

79D

BIN(2)

keyfdbk

Key feedback. Initialize to -53 to deactivate.

1951

79F

BIT(1)

keyonoff

Key feedback option.

On: Provide feedback
Off: Do not provide feedback

1951

79F

BIT(7)

keyrsvl

Reserved.

1952

7A0

BIN(2)

seqonly

Sequential processing. Initialize to -58 to deactivate.

1954

7A2

BIT(1)

seqonoff

Sequential processing option.

On: Use Fast sequence processing
Off: Use standard sequence processing

1954

7A2

BIT(1)

numonoff

Fast sequential processing option.

On: Number of records to transfer to or from the I/O buffers for fast sequential processing is specified.
Off: The number of records to transfer to or from the I/O buffers is not specified.

1954

7A2

BIT(6)

seqrsvl

Reserved.

1955

7A3

BIN(2)

numrecs

The number of records to transfer to or from the I/O buffers for fast sequential processing.

1957

7A5

BIN(2)

commitc

Commitment control. Initialize to -59 to deactivate.

1959

7A7

CHAR(1)

control

Commitment control and optional record-locking level. Possible values are:

X'00': Do not place the member under commitment control when it is opened. This would be the same as specifying the Start Commitment Control command as STRCMTCLT COMMIT(*NO).
X'80': Place the member under commitment control when it is opened, and use the record-locking level default used on the Start Commitment Control command, that is, STRCMTCTL COMMIT (*YES).
X'82': Place the member under commitment control when it is opened and use record-locking level *CHG, that is, STRCMTCTL COMMIT (*YES,*CHG).
X'86': Place the member under commitment control when it is opened and use record-locking level *CS, that is, COMMIT *YES,*CS).
X'87': Place the member under commitment control when it is opened and use record-locking level *ALL, that is, COMMIT (*YES,*ALL).

1960

7A8

BIN(2)

ufcbend

This field must be set to 32767, the end of the variable area parameters. Set this field to ENDLIST.

1962

7AA

BIN(4)

dummy

Dummy pointer to force boundary alignment for the user file control block structure.

Value for Query Variable Fields (QQQVALS_T) Structure

The structure is used to supply the values for the variable fields used by the QQQQRY API. The offsets and a description of all the fields contained in this structure are shown in the following table. You can see this source in member QQQQRY in the QSYSINC library.

Offset

Bit

Type

Variable Name

Field

Dec

Hex

BIN(2)

qqqvvalnum

Number of values in list.

CHAR(30)

qqqvals_l

Reserved.

ARRAY(1) OF CHAR(48)

qqqvlst

List of variable field values referenced by field Qddfvarx. See field Qddfvarx for the Qddfvarx field. (Ref #15.)

Pointer

qqqvsptr

Space pointer to the host variable value. The value must be in internal form.

CHAR(7)

qqqvattr

Attributes of value.

CHAR(1)

qqqvatyp

Scalar type

X'00': Binary
X'01': Floating point
X'02': Zoned decimal
X'03': Packed
X'04': Character
X'06': Graphic
X'07': DBCS-only
X'08': DBCS-either
X'09': DBCS-open
X'0B': Date
X'0C': Time
X'0D': Timestamp
X'0F': Decimal floating point

CHAR(2)

qqqvalen

Scalar length for character, binary, floating point, only, either, or open. For graphic, this is also the number of bytes (not characters).

CHAR(1)

qqqvadec

Fractional digits for zoned or packed.

CHAR(1)

qqqvatot

Total digits for zoned or packed.

CHAR(4)

qqqvaary

Container for precision and digits for binary values.

CHAR(1)

qqqvbind

Fractional digits for binary value.

CHAR(1)

qqqvbint

Total digits for binary value.

CHAR(2)

qqqvals_2

Reserved.

CHAR(1)

qqqvals_3

Field attributes.

BIT(1)

qqqvvlen

Variable length host variable field.

0: The host variable field is not variable length.
1: The host variable field is variable length.

BIT(1)

qqqvnulll

The form of field qqqvsptr. If on, qqqvsptr is ignored and the literal is the null value. If off, the literal pointed to by qqqvsptr is used.

BIT(1)

qqqvzerol

The length of field qqqvsptr. If on, qqqvsptr is ignored and the literal is zero length. If off, the literal pointed to by qqqvsptr is used.

BIT(5)

qqqvals_4

Reserved.

CHAR(1)

qqqvdvft

Date, time, and timestamp format attribute. This field applies to date, time, or timestamp values only, where the field qqqvatyp in this structure is date, time, or timestamp.

X'00': Job default
X'FF': Determine format
X'01': USA format
X'03': ISO format
X'05': EUR format
X'07': JIS format
X'09': SAA timestamp
X'17': MDY format
X'18': DMY format
X'19': YMD format
X'1A': JUL format
X'1B': HMS format
X'1D': YYYY NNN format
X'1E': YYYY MM DDDD HH MM SS format

These formats are optional. If the value is X'FF', the format is in the query definition template header and that format is used first in determining the format. See field qdbqdfmt or field qdbqtfmt if the format is in the query definition template header.

CHAR(1)

qqqvdvsp

Date, time, and timestamp separator. This field is only set when field qqqvdvft in this structure is X'17', X'18', X'19', X'1A', or X'1B'.

BIN(2)

qqqvcsid

CCSID of value.

CHAR(20)

qqqvals_5

Reserved.

Usage Notes

In multithreaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA. This command also is not threadsafe and fails for Distributred Data Management (DDM) files of type *SNA.

Error Messages

Message ID

Error Message Text

CPF2114 E

Cannot allocate object &1 in &2 type *&3.

CPF2115 E

Object &1 in &2 type *&3 damaged.

CPF2169 E

Job's sort sequence information not available.

CPF24B4 E

Severe error while addressing parameter list.

CPF2619 E

Table &1 not found.

CPF3BCC E

Language identifier &1 not valid.

CPF3BC6 E

Sort sequence &1 not valid.

CPF3BC7 E

CCSID &1 outside of valid range.

CPF3BC8 E

Conversion from CCSID &1 to CCISID &2 is not supported.

CPF3BC9 E

Conversion from CCSID &1 to CCISID &2 is not defined.

CPF3C90 E

Literal value cannot be changed.

CPF3CF1 E

Error code parameter is not valid.

CPF3FC0 E

Language identifier is not valid.

CPF4000 E

All CPF40xx messages could be returned. xx is from 01 to FF.

CPF4100 E

All CPF41xx messages could be returned. xx is from 01 to FF.

CPF4200 E

All CPF42xx messages could be returned. xx is from 01 to FF.

CPF4300 E

All CPF43xx messages could be returned. xx is from 01 to FF.

CPF5000 E

All CPF50xx messages could be returned. xx is from 01 to FF.

CPF5100 E

All CPF51xx messages could be returned. xx is from 01 to FF.

CPF5200 E

All CPF52xx messages could be returned. xx is from 01 to FF.

CPF5300 E

All CPF53xx messages could be returned. xx is from 01 to FF.

CPF8133 E

Table &4 in &9 damaged.

CPF9800 E

All CPF98xx messages could be signaled. xx is from 01 to FF.

Example

For examples that use the QQQQRY API, see Example: Defining queries in the Examples: APIs and exit programs topic.

API introduced: V2R2

[ Back to top | Database and File APIs | APIs by category ]