Query (QQQQRY) API


  Required Parameter Group:

1 Query option requested Input Char(10)
2 User file control block I/O Char(*)
3 Query definition template I/O Char(*)
4 Literal values I/O Char(*)
5 Access plan control block I/O Char(48)
6 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:

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:


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

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
0 0   BIN(4) qdbqfilo Offset to file, library, format, and member specifications.
4 4   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.
8 8   BIN(4) qdbqjoio Offset to join specifications. 0 indicates that this is not a join query.
12 C   BIN(4) qdbqselo Offset to the record selection specifications. 0 indicates that no record selection is to occur.
16 10   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.
20 14   BIN(4) qdbqgrpo Offset to the group-by-selection specifications. 0 indicates that no group by is to occur.
24 18   CHAR(4) qdbqdt_1 Reserved.
28 1C   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.
32 20   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.
33 21   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.
34 22   CHAR(2) qdbqattr Query attributes.
34 22 0 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.
34 22 1 BIT(1) qdbqdist Distinct records.
0
Do not produce distinct records.
1
Eliminate duplicate records from the query result.
34 22 2 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.
34 22 3 BIT(1) qdbqterr Tolerate decimal data errors.
0
Decimal data errors result in an exception being issued.
1
Decimal data errors are ignored.
34 22 4 BIT(1) qdbqdt_2 Reserved.
34 22 5 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.
34 22 6 BIT(1) qdbqdt_3 Reserved.
34 22 7 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.
35 23 0 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.
35 23 1 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.
35 23 2 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.
35 23 3 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.
35 23 4 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 (+, -, *, /).
35 23 5 BIT(1) qdbqsubq The query definition template contains at least one subquery. This does not span across unions.
35 23 6 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.
35 23 7 BIT(1) qdbqdt_4 Reserved.
36 24   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.
38 26   BIN(4) qdbqnumrcd This field is no longer supported. Its value will be ignored.
42 2A   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.
46 2E   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.
50 32   CHAR(4) qdbqdt_5 Reserved.
54 36   BIN(4) qdbqqdto Offset to the query definition template table containing offsets to all query definition templates between unions.
58 3A   CHAR(8) qdbqdt_6 Reserved. (Ref #2.)
66 42   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.)
67 43   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.)
68 44   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.)
69 45   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.
70 46   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.
72 48   CHAR(2) qdbqdt_7 More flags.
72 48 0 BIT(1) qdbqvlit Variable length literal.
0
No
1
Yes
72 48 1 BIT(5) qdbqdt_8 Reserved. (Ref #6.)
72 48 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.
72 48 7 BIT(1) qdbqmapbd Reserved.
73 49 0 BIT(7) qdbqdt_9 Reserved.
73 49 7 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.
74 4A   CHAR(2) QDBQDT_10 Reserved.
76 4C   qdbqfbk Query feedback. The following information is returned on successful completion of the query.
76 4C   CHAR(2) qdbqqtyp_t Query status indicators.
76 4C 0 BIT(1) qdbqtemp Temporary result.
0
No temporary result.
1
Temporary result created.
76 4C 1 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.
76 4C 2 BIT(1) qdbqdt_11 Reserved.
76 4C 3 BIT(1) qdbqacpi Access plan indicator.
0
No access plan present.
1
Query definition template is or is part of an access plan.
76 4C 4 BIT(1) qdbqsreg This field is set on if the query definition template contains special registers CURRENT USER, CURRENT SERVER, or CURRENT TIMEZONE.
76 4C 5 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.
76 4C 6 BIT(1) qdbqsqlt SQL tables.
0
Not all SQL tables.
1
All SQL tables.
76 4C 7 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.
77 4D 0 BIT(1) qdbqcurdt The query definition template contains CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP.
77 4D 1 BIT(7) qdbqdt_12 Reserved.
78 4E   CHAR(28) qdbqdt_13 Length of query definition structure.
78 4E   BIN(4) qdbqdtln Length of query definition.
82 52   CHAR(24) qdbqdt_14 Reserved.
106 6A   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 6B   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 6C   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 6D   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 6E   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 6F   CHAR(1) qdbqdt_15 Reserved.
112 70   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 74   CHAR(12) qdbqdt_16 Reserved.
128 80   BIN(4) qdbq_jrefo Offset to JREF Join specification.
132 84   CHAR(44) qdbqdt_65 Reserved.
176 B0   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 B0 0 BIT(1) 
qdbq_posnopts
_prior
Scrolling to previous record is used.
176 B0 1 BIT(1) 
qdbq_posnopts
_first
Scrolling to first record is used.
176 B0 2 BIT(1) 
qdbq_posnopts
_last
Scrolling to last record is used.
176 B0 3 BIT(1) 
qdbq_posnopts
_before
Scrolling to before the first record is used.
176 B0 4 BIT(1) 
qdbq_posnopts
_after
Scrolling to after the last record is used.
176 B0 5 BIT(1) 
qdbq_posnopts
_current
Retrieval of the current record is used.
176 B0 6 BIT(1) 
qdbq_posnopts
_relative
Scrolling to a record relative to the current record is used.
176 B0 7 BIT(9) qdbqdt_17 Reserved.
178 B2   CHAR(1) qdbq_ext_bits Miscellaneous bits in the query definition header.
178 B2 0 BIT(1) qdbq_ctlblk An indicator that the caller will be control record blocking; therefore, ignore SEQONLY() overrides.
178 B2 1 BIT(1) qdbq_norolb Rollback HOLD can leave the position of this cursor as unknown.
178 B2 2 BIT(1) 
qdbq_stream
_cursor
The user of this query attempts to read records from this query as fast as possible.
178 B2 3 BIT(4) qdbqdt_54 Reserved.
178 B2 7 BIT(1) qdbqdt_18 Reserved.
179 B3   CHAR(1) qdbq_ext_bits2 Miscellaneous bits in the query definition header.
179 B3 0 BIT(2) qdbqdt_57 Reserved.
179 B3 2 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 B3 3 BIT(5) qdbqdt_58 Reserved.
180 B4   CHAR(1) qdbq_ext_bits3 Miscellaneous bits in the query definition header.
180 B4 0 BIT(1) qdbqdt_62 Reserved.
180 B4 1 BIT(1) qdbq_searched_ update Indicator if this is a searched UPDATE QDT.
180 B4 2 BIT(1) qdbq_searched_ delete Indicator if this is a searched DELETE QDT.
180 B4 3 BIT(2) qdbqdt_63 Reserved.
180 B4 5 BIT(1) qdbq_drvtbl Indicates that this QDT is part of a derived table QDT.
180 B4 6 BIT(2) qdbqdt_64 Reserved.
181 B5   CHAR(15) qdbqdt_19 Reserved.
196 C4   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 CE   BIN(4) qdbspcsize API users specify size for space containing all API query definition templates. (Ref #7.)
210 D2   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 D6   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 D8   CHAR(5) qdbqdt_53 Reserved.
221 DD   CHAR(1) qdbqic Whether query allows index creation.
N
Index creation is not allowed.
Y or X'00'
Index creation is allowed.
222 DE   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
0 0   CHAR(42) qqqstb1 Sequence table name, library name, and language identifier.
0 0   CHAR(10) qqqresv2 Reserved.
10 A   CHAR(20) qqsrtseq Sort sequence.
10 A   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.
20 14   CHAR(10) qqqstbnl Library name. Possible special values are:
*LIBL
The library list.
*CURLIB
The job's current library.
30 1E   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.
40 28   CHAR(2) qqqresv3 Reserved.
42 2A   CHAR(38) qqqstb2 Reserved.
42 2A   CHAR(2) qqqresv4 Reserved.
44 2C   CHAR(10) qqqtbnm Reserved.
54 36   CHAR(10) qqqlbnm Reserved.
64 40   CHAR(14) qqqresv5 Reserved.
78 4E   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.
80 50   CHAR(10) qqqstbe1 User-specified DBCS sort sequence information.
80 50   CHAR(1) qqqstbtyp Type of DBCS sort sequence table.
X'00'
UCS-2 sort sequence table
81 51   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).
82 52   BIN(4) qqqstblen Length of DBCS sort sequence table. If an SBCS sort sequence table is specified, qqqstblen must be zero.
86 56   BIN(4) qqqstboff Offset to the DBCS sort sequence table from qqqstb1.
90 5A   CHAR(22) qqqresv1 Reserved.
112 70   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
0 0   qdbqfhdr File specification header.
0 0   BIN(2) qdbqfilnum The number of files, libraries, formats, and members. (Ref #8.)
2 2   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.
3 3   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.
4 4   CHAR(1) qdbqdt_21 Flags.
4 4   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.
4 4 1 BIT(1) qdbqmfjn Join clause exists. An SQL JOIN clause syntax exists in this query.
4 4 2 BIT(6) qdbqdt_22 Reserved.
5 5   CHAR(11) qdbqdt_23 Reserved.
16 10   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
0 0   CHAR(40) qdbqflmf File, library, member, and record format names.
0 0   CHAR(10) qdbqfile File name. If an override is in effect to another file, the actual file name is returned in this field.
10 A   CHAR(10) qdbqlib Library name. If the special value *LIBL is used, the actual library name is resolved and returned in this field.
20 14   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.
30 1E   CHAR(10) qdbqfmt Format name. If the special value *ONLY is used, the actual format name is resolved and returned in this field.
40 28   CHAR(1) qdbqfflg File specification flags.
40 28 0 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.
40 28 1 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.
40 28 2 BIT(1) qdbqfgna Reserved.
40 28 3 BIT(1) qdbqfngn Reserved.
40 28 4 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.
40 28 5 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.
40 28 6 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
40 28 7 BIT(1) qdbqdt_24 Reserved.
41 29   CHAR(1) qdbqmfvw Reserved.
42 2A   CHAR(1) qdbqmfvw_spc Reserved.
43 2B   BIN(2) qdbqf_qdtnum Index into the array of subquery offsets (QDBQQDT_T) for the derived table QDT.
45 2D   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
0 0   qdbqjhdr Join specifications header.
0 0   BIN(4) qdbqjln Length of this join specification.
4 4   BIN(2) qdbqjknum Number of from-join and to-join field specifications.
6 6   CHAR(10) qdbqdt_26 Reserved.
16 10   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 70   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
0 0   CHAR(30) qdbqjfnm Join from field name.
30 1E   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.
32 20   CHAR(2) qdbqdt_27 Reserved.
34 22   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
36 24   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.
66 42   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.
68 44   CHAR(1) qdbqjpfmt Reserved.
69 45   CHAR(1) qdbqjpsep Reserved.
70 46   BIT(1) qdbqjfprf Reserved.
70 46 1 BIT(1) qdbqjvw Reserved
70 46 2 BIT(1) qdbqj_type_sup Join type specified. The type of join is specified in field qdbqj_type.
70 46 3 BIT(5) qdbqdt_oj Reserved.
71 47   CHAR(1) qdbqj_type Type of join.
J
Inner join
C
Partial outer join
E
Exception join
72 48   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
0 0   qdbq_jref_hdr JREF Join specifications header.
0 0   BIN(4) qdbq_jref_len Length of this JREF join specification.
4 4   BIN(2) qdbq_jref# Number of JREF Join entries.
6 6   CHAR(10) qdbqdt_66 Reserved.
16 10   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
0 0   BIN(2) qdbq_jref_ entry_type JREF Join entry type.
0
Join operand
2
Join operator
2 2   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
0 0   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.
2 2   CHAR(8) qdbqdt_67 Reserved.


JREF Join Operator (QDBQ_JREF_OPERATOR_T)

JREF Join entry operator.

Offset Bit Type Variable Name Field
Dec Hex
0 0   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).
4 4   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
5 5   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
0 0   BIN(4) qdbqsl Selection specifications length. This is the total length of all selection specifications.
4 4   BIN(2) qdbqsnum Number of selection specifications.
6 6   CHAR(10) qdbqdt_29 Reserved. (Ref #9.)
16 10   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
0 0   BIN(4) qdbqslen Selection item length. This length includes the length (QDBQSIT_T) plus the length of the selection item structure.
4 4   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.)
6 6   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
0 0   CHAR(30) qdbqsofn Field name. The field name must be an external name.
30 1E   BIN(2) qdbqsofj 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. 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 to be found in the file format referenced by using this value as an index into the file list.
32 20   BIN(2) qdbqsoqt Index into the query-definition-template table for the correlated field's associated query definition template. Use zero for noncorrelated fields.
34 22   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
0 0   BIN(2) qdbqssub Index into the query-definition-template offset table for the subquery's query definition template.
2 2   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
3 3   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.
0 0   BIN(4) qdbqsocl Constant operand byte length. This only includes the length of the constant in field qdbqsovl, including apostrophes.
4 4   CHAR(1) qdbqdt_32 Constant attributes.
4 4 0 BIT(1) qdbqsoci DBCS open constant.
0
This constant is not a DBCS-open literal.
1
This constant is a DBCS-open literal.
4 4 1 BIT(1) qdbqdt_33 Reserved.
4 4 2 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.
4 4 3 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.
4 4 4 BIT(1) qdbqsoco DBCS-only constant.
0
This constant is not DBCS-only.
1
This constant is DBCS-only.
4 4 5 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.
4 4 6 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.
4 4 7 BIT(1) qdbqdt_34 Reserved.
5 5   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
6 6   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.
7 7   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.
8 8   CHAR(2) qdbqdt_35 Reserved.
10 A   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.
12 C   CHAR(1) qdbqdt_36 Reserved.
12 C 0 BIT(2) qdbqdt_37 Reserved.
12 C 2 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.
12 C 3 BIT(5) qdbqdt_38 Reserved.
13 D   CHAR(29) QDBQDT_39 Reserved. (Ref #11.)
42 2A   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
0 0   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.
2 2   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.
3 3   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.
4 4   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.
6 6   CHAR(1) qdbqdt_55 Selection operator flags.
6 6 0 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.
6 6 1 BIT(1) qdbqdt_56 Reserved.
6 6 2 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.
6 6 3 BIT(5) qdbqdt_60 Reserved.
7 7   CHAR(3) qdbqdt_40 Reserved.
Note: The following fields are not present in a query definition restored from a System/38™.
10 A   CHAR(14) qdbqsop2 Wildcard value for double-byte characters
10 A   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.
12 C   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.
14 E   CHAR(3) qdbqdt_41 Reserved.
17 11   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.
19 13   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.
21 15   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.
23 17   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.
25 19   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
0 0   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.
2 2   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.
4 4   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
0 0   CHAR(16) qdbqkh Order by header.
0 0   BIN(2) qdbqknum The number of key positions in the order by specifications.
2 2   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.
3 3   CHAR(13) qdbqdt_42 Reserved.
16 10   ARRAY(1) OF CHAR(64) qdbqkf Key specifications of 10 000.
16 10   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.
36 24   CHAR(1) qdbqksq Key field sequencing attributes.
36 24 0 BIT(1) qdbqksad Ascending or descending sequencing indicator.
0
Ascending sequence
1
Descending sequence
36 24 1 BIT(1) qdbqdt_43 Reserved.
36 24 2 BIT(1) qdbqkabs Absolute value sequence indicator. This bit is ignored for character key fields.
0
Numeric sequence
1
Absolute value sequence
36 24 3 BIT(5) qdbqdt_44 Reserved.
37 25   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
0 0   CHAR(16) qdbqgh Group by header.
0 0   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.
2 2   CHAR(14) qdbqdt_46 Reserved.
16 10   ARRAY(120) OF CHAR(64) qdbqgf Group field specification. Up to 120 fields are allowed.
16 10   CHAR(30) qdbqgfld Group field name.
46 2E   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.
48 30   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
0 0   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
0 0   BIN(4) qdbqtl Set specifications length. This is the total length of all set specifications.
4 4   BIN(2) qdbqtnum Number of set specifications.
6 6   CHAR(10) qdbqdt_48 Reserved. (Ref #13.)
16 10   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
0 0   BIN(4) qdbqtlen Set item length. This length includes the length (QDBQTIT_T) plus the length of the set item structure.
4 4   BIN(2) qdbqtitt Set item type.
1
Constant operand
2
Operator
(Ref #14.)
6 6   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
0 0   CHAR(16) qdbqqhdr Header.
0 0   BIN(2) qdbqqdtnum Number of subqueries defined with offsets.
2 2   CHAR(14) qdbqdt_51 Reserved.
16 10   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
0 0   BIN(4) qdbqo Offset to QDT from start of first QDT in the union.
4 4   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

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:

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
0 0   CHAR (1962) qufcb Query base UFCB. A character view of the entire user file control block.
0 0   CHAR(174) reserved1 Reserved.
174 AE   CHAR(1) shr_secure Share and secure flags.
174 AE 0 BIT(3) flglrsva Reserved.
174 AE 3 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 AE 4 BIT(1) flglshsw Share value.
0
Not share
1
Share
174 AE 5 BIT(1) flglsecr Secure specified.
174 AE 6 BIT(1) flglud Secure value.
0
Not secure
1
Secure
174 AE 7 BIT(1) flglsvb Reserved.
175 AF   CHAR(1) open Open flags.
175 AF 0 BIT(2) flagrsva Reserved.
175 AF 2 BIT(1) flagui Open input.
175 AF 3 BIT(1) flaguo Open output.
175 AF 4 BIT(1) flaguu Open update.
175 AF 5 BIT(1) flagud Delete.
175 AF 6 BIT(2) flagsvb Reserved.
176 B0   CHAR(4) relver Release and version.
176 B0   CHAR(2) release Release number. This value must be set to 01.
178 B2   CHAR(2) version Version number. This value must be set to 00.
180 B4   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 B8   CHAR(1) markcnt Mark count and blocked record.
184 B8 0 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 B8 1 BIT(1) flg2rsvl Reserved.
184 B8 2 BIT(1) flg2brcd Blocked records.
0
There are no blocked records.
1
There are blocked records.
184 B8 3 BIT(5) flg2rsv2 Reserved.
185 B9   CHAR(1) reserved2 Reserved.
186 BA   CHAR(1) invact Mark count usage.
186 BA 0 BIT(1) flg4rsvl Reserved.
186 BA 1 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 BA 2 BIT(6) flg4rsv2 Reserved.
187 BB   CHAR(1) reserved2a Reserved.
188 BC   CHAR(1) native System environment and process NULLS.
188 BC 0 BIT(2) flg3rsvl Reserved.
188 BC 2 BIT(1) flg3ntve This field must be set to 1.
188 BC 3 BIT(3) flg3rsv2 Reserved.
188 BC 6 BIT(1) flg3null Process null-capable fields.
188 BC 7 BIT(1) flg3rsv3 Reserved.
189 BD   CHAR(2) reserved3a Reserved.
191 BF   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 C0   CHAR(5) reserved3b Reserved.
197 C5   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 C9   CHAR(7) reserved3 Reserved.
Note: The parameter field through the ufcbend field are repeated in the variable-length data area for each parameter.
208 D0   CHAR(73) parameter Variable parameters.
208 D0   BIN(2) primrlnl Primary record length. Initialize to -1 to deactivate.
210 D2   BIN(2) primrlnv The user-specified record length.
212 D4   BIN(2) filedep File-dependent I/O. Initialize to -3 to deactivate.
214 D6 0 BIT(1) fildonoff File-dependent option.
On
This is file dependent.
Off
This is not file dependent.
214 D6 1 BIT(7) fldrsvl Reserved.
215 D7   BIN(2) lvlchk Level-check option. Initialize to -6 to deactivate.
217 D9 0 BIT(1) lvlonoff Level-check option.
On
Perform level checking
Off
Do not perform level checking
217 D9 1 BIT(7) lvlrsvl Reserved.
218 DA   BIN(2) recfmts Record format sequence numbers for level checking.
220 DC   BIN(2) maximum The maximum number of formats.
222 DE   BIN(2) curnum The current number of formats.
224 E0   ARRAY(75) of CHAR(23) formats Array of format names and sequence numbers
224 E0   CHAR(10) name The format name.
234 EA   CHAR(13) number The format sequence number.
1949 79D   BIN(2) keyfdbk Key feedback. Initialize to -53 to deactivate.
1951 79F 0 BIT(1) keyonoff Key feedback option.
On
Provide feedback
Off
Do not provide feedback
1951 79F 1 BIT(7) keyrsvl Reserved.
1952 7A0   BIN(2) seqonly Sequential processing. Initialize to -58 to deactivate.
1954 7A2 0 BIT(1) seqonoff Sequential processing option.
On
Use Fast sequence processing
Off
Use standard sequence processing
1954 7A2 1 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 2 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
0 0   BIN(2) qqqvvalnum Number of values in list.
2 2   CHAR(30) qqqvals_l Reserved.
32 20   ARRAY(1) OF CHAR(48) qqqvlst List of variable field values referenced by field Qddfvarx. See field Qddfvarx for the Qddfvarx field. (Ref #15.)
32 20   Pointer qqqvsptr Space pointer to the host variable value. The value must be in internal form.
48 30   CHAR(7) qqqvattr Attributes of value.
48 30 0 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
49 31   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).
49 31   CHAR(1) qqqvadec Fractional digits for zoned or packed.
50 32   CHAR(1) qqqvatot Total digits for zoned or packed.
51 33   CHAR(4) qqqvaary Container for precision and digits for binary values.
51 33   CHAR(1) qqqvbind Fractional digits for binary value.
52 34   CHAR(1) qqqvbint Total digits for binary value.
53 35   CHAR(2) qqqvals_2 Reserved.
55 37   CHAR(1) qqqvals_3 Field attributes.
55 37 0 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.
55 37 1 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.
55 37 2 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.
55 37 3 BIT(5) qqqvals_4 Reserved.
56 38   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.
57 39   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'.
58 3A   BIN(2) qqqvcsid CCSID of value.
59 3C   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 ]