OPNQRY command (X'200C')

The distributed data management (DDM) architecture OPNQRY command opens a query to a database for a read request.

Format

DLI Flow:

SQL Flow:

Parameters

DSSHDR

The 6-byte header that contains information about the data stream structure (DSS).

LL

The length specified as a 2-byte binary integer. This length includes LL and CP.

CP

X'200C', the 2-byte code point of the OPNQRY command.

MAXBLKEXT

An optional parameter that specifies the maximum number of extra blocks per result set that the requester is capable of receiving as reply data in the response to an OPNQRY or CNTQRY command. The number is specified as a 2-byte binary number. A value of 0 indicates that the requester is not capable of receiving extra query blocks of answer set data. A value of -1 indicates that the requester is capable of receiving the entire result set. The code point for MAXBLKEXT is X'2141'.

MONITOR(X'1900')

FLAGS: A 4-byte flag value.

PCBNAME

A required parameter that specifies the PCB name that uniquely identifies the query made by a DL/I call. The PCB name is specified as a character string. The value is initially sent with the original OPNQRY command. The same value must subsequently be sent in commands such as CNTQRY, CLSQRY, and RLSE for proper correlation with the original OPNQRY call. The codepoint for the PCBNAME parameter is X'C907'.

PKGNAMCSN(X'2113')

Specifies the fully qualified package name, consistency token, and section number within the package that is used to execute the SQL. Mutually exclusive with PKGSN.

PKGSN

A 2-byte short field that represents the Section Number. Mutually exclusive with PKGCNSTKN.

QRYBLKCTL

An optional parameter that specifies the type of query block protocol that is used when a query is opened. IMS supports only the limited block query protocol of the DDM architecture. If the QRYBLKCTL parameter is specified on the OPNQRY command, the 2-byte data portion of the QRYBLKCTL parameter must specify the hexadecimal value of X'2417', the code point for the limited block query protocol (LMTBLKPRC). If the QRYBLKCTL parameter is omitted from the OPNQRY command, the IMS target server still uses the limited block query protocol. The code point for the QRYBLKCTL parameter is X'2132'.

QRYBLKSZ

A required parameter that specifies the size of query blocks that is ideal for the source application program. Query blocks are used by the target server to return answer set data. The target server can override this parameter as needed. The query block size is specified as a 4-byte unsigned binary number. The minimum size for a query block is 0.5 KB. The maximum size is 10 MB. The code point for the QRYBLKSIZ parameter is X'2114'.

QRYROWSET

An optional parameter that specifies the number of rows of data to return in one network reply. The number of rows is specified as a 4-byte binary number. The minimum value for QRYROWSET is 0. The maximum value is 32 767. The code point for the QRYROWSET parameter is X'2156'.

The JDBC driver uses the following calculation to determine how many records can be placed into the buffer that ODBM returns from the IMS DB through the DRDA protocol. Here is a brief description of the variables used in the formula:

IOAREALENGTH: By leveraging the database metadata available in the catalog, the length for a given path (record) call can be determined. This is the sum of the maximum possible length for all segments in the path call. For example, if you specify SELECT * FROM SEGMA, SEGMB where SEGMB is a child of SEGMA, then the length of the path call (record) being returned is the length of SEGMA + the length of SEGMB. This length is referred to as IOAREALENGTH.

MAXRETURNDATA: ODBM has its own buffer that is used to collect data prior to sending it back to the user. This buffer is referred to as MAXRETURNDATA and is set it to 1MB.

MAXROWDATA: For each record that gets returned, there is some additional overhead that uses the amount of usable buffer space for actual record data. The formula accounts for this additional overhead. 44 bytes will be used for the keyfeedback of each row as well as bytes used to describe the SSAList (equivalent of the WHERE clause from the SQL statement). The amount of space required per row of data is referred to as MAXROWDATA.

Formula used to calculate QRYROWSET:


IOAREALENGTH = LENGTH_OF_YOUR_PATHCALL 
MAXRETURNDATA = 1MB 
MAXROWDATA = 44 + (NUM_OF_SEGMENT_LEVELS_IN_SSALIST * 256) + IOAREALENGTH 
QRYROWSET = MAXRETURNDATA / MAXROWDATA

Usage

If no errors occur during processing of the OPNQRY, the IMS target server returns the OPNQRYRM reply message to indicate that the query was successfully opened.

Command objects

The following command objects can be chained to the OPNQRY command:

INAIB (X'CC01'): A required command object that contains AIB data.
Note: The INAIB object in OPNQRY is not used in DRDA DDM command support for native SQL implementations.
DLIFUNC (X'CC05'): A required command object that specifies the action to take on the database. The data field of DLIFUNC is a database function that is specified as a character string. The valid values for DLIFUNC, when it is chained to the OPNQRY command, are: RETRIEVE, GHU, GU, GHN, GN, GNP, or GHNP.
Note: The DLIFUNC object in OPNQRY is not used in DRDA DDM command support for native SQL implementations.
RTRVLFD (X'CC04'): An optional scalar data object representing a field that the client wants to retrieve. Multiple RTRVFLD objects can be chained to the OPNQRY command. If an RTRVFLD object is not included on the OPNQRY command, all fields in the retrieved segment are returned.
Note: The RTRVLFD object in OPNQRY is not used in DRDA DDM command support for native SQL implementations.
SSALIST (X'CC06'): An optional chained object that lists the segment search arguments. If the SSALIST is not included on the OPNQRY command, the IMS target server ignores any RTRVFLD chained objects and the query results in an unqualified step through the IMS database.
Note: The SSALIST object in OPNQRY is not used in DRDA DDM command support for native SQL implementations.

Positive reply messages

In response to the OPNQRY command, the IMS target DDM server returns to the source server the following positive reply messages:

OPNQRYRM (X'2205'): Open query reply message.

Reply data objects

The following reply data objects can be returned in response to the CNTQRY command:

QRYDSC (X'241A'): Query answer set description.
QRYDTA (X'241B'): Query answer set data.

Error reply messages

In response to the OPNQRY command, the IMS target DDM server can return to the source DDM server the following error reply messages:

Table 1. Possible reply messages for the OPNQRY command
Code point of reply message	Name of reply message	Meaning of reply message
X'121C'	CMDATHRM	Not authorized to command
X'1232'	AGNPRMRM	Permanent agent error
X'1233'	RSCLMTRM	Resource limits reached
X'1245'	PRCCNVRM	Conversational protocol error
X'124C'	SYNTAXRM	Data stream syntax error
X'1250'	CMDNSPRM	Command not supported
X'1251'	PRMNSPRM	Parameter not supported
X'1252'	VALNSPRM	Parameter value not supported
X'1253'	OBJNSPRM	Object not supported
X'1254'	CMDCHKRM	Command check reply message
X'125F'	TRGNSPRM	Target not supported
X'2204'	RDBNACRM	Database not accessed
X'220A'	DSCINVRM	Invalid description
X'220B'	ENDQRYRM	End of query
X'220D'	ABNUOWRM	Abnormal end of unit of work condition
X'220E'	DTAMCHRM	Data descriptor mismatch
X'220F'	QRYPOPRM	Query previously opened
X'2212'	OPNQFLRM	Open query failure
X'2218'	RDBUPDRM	Database update reply message

OPNQRY examples

OPNQRY only example:
[ibm][ims][drda][t4]        SEND BUFFER: OPNQRY                 (ASCII)           (EBCDIC)
[ibm][ims][drda][t4] 0000   005BD00100030055  200C004421134BC9  .[.....U ..D!.K.  .$}............I
[ibm][ims][drda][t4] 0010   D4E2F14040404040  4040404040404040  ...@@@@@@@@@@@@@  MS1             
[ibm][ims][drda][t4] 0020   D5E4D3D3C9C44040  4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][ims][drda][t4] 0030   4040E2E8E2E2D5F2  F0F0404040404040  @@........@@@@@@    SYSSN200      
[ibm][ims][drda][t4] 0040   404040405359534C  564C303100010008  @@@@SYSLVL01....      ...<.<......
[ibm][ims][drda][t4] 0050   2114000000000005  215D01            !.......!].       .........).

OPNQRY complete chained request example for SQL SELECT:
[ibm][ims][drda][t4]        SEND BUFFER: EXCSQLSET              (ASCII)           (EBCDIC)
[ibm][ims][drda][t4]        0 1 2 3 4 5 6 7   8 9 A B C D E F   0123456789ABCDEF  0123456789ABCDEF
[ibm][ims][drda][t4] 0000   004ED05100010048  2014004421134BC9  .N.Q...H ..D!.K.  .+}............I
[ibm][ims][drda][t4] 0010   D4E2F14040404040  4040404040404040  ...@@@@@@@@@@@@@  MS1             
[ibm][ims][drda][t4] 0020   D5E4D3D3C9C44040  4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][ims][drda][t4] 0030   4040E2E8E2E2D5F2  F0F0404040404040  @@........@@@@@@    SYSSN200      
[ibm][ims][drda][t4] 0040   404040405359534C  564C30310041      @@@@SYSLVL01.A        ...<.<....  
[ibm][ims][drda][t4] 
[ibm][ims][drda][t4]        SEND BUFFER: SQLSTT                 (ASCII)           (EBCDIC)
[ibm][ims][drda][t4] 0000   0031D0430001002B  2414002353455420  .1.C...+$..#SET   ..}.............
[ibm][ims][drda][t4] 0010   434C49454E542057  524B53544E4E414D  CLIENT WRKSTNNAM  .<..+.......++.(
[ibm][ims][drda][t4] 0020   452027392E36352E  3137342E32352700  E '9.65.174.25'.  ................
[ibm][ims][drda][t4] 0030   00                                  .                 .               
[ibm][ims][drda][t4] 
[ibm][ims][drda][t4]        SEND BUFFER: PRPSQLSTT              (ASCII)           (EBCDIC)
[ibm][ims][drda][t4] 0000   0058D05100020052  200D004421134BC9  .X.Q...R ..D!.K.  ..}............I
[ibm][ims][drda][t4] 0010   D4E2F14040404040  4040404040404040  ...@@@@@@@@@@@@@  MS1             
[ibm][ims][drda][t4] 0020   D5E4D3D3C9C44040  4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][ims][drda][t4] 0030   4040E2E8E2E2D5F2  F0F0404040404040  @@........@@@@@@    SYSSN200      
[ibm][ims][drda][t4] 0040   404040405359534C  564C303100010005  @@@@SYSLVL01....      ...<.<......
[ibm][ims][drda][t4] 0050   2116F10005214604                    !....!F.          ..1.....        
[ibm][ims][drda][t4] 
[ibm][ims][drda][t4]        SEND BUFFER: SQLATTR                (ASCII)           (EBCDIC)
[ibm][ims][drda][t4] 0000   001CD05300020016  2450000E464F5220  ...S....$P..FOR   ..}......&...|..
[ibm][ims][drda][t4] 0010   52454144204F4E4C  59200000          READ ONLY ..      .....|+<....    
[ibm][ims][drda][t4] 
[ibm][ims][drda][t4]        SEND BUFFER: SQLSTT                 (ASCII)           (EBCDIC)
[ibm][ims][drda][t4] 0000   0029D04300020023  2414001B53656C65  .).C...#$...Sele  ..}...........%.
[ibm][ims][drda][t4] 0010   6374202A2066726F  6D20504844414D56  ct * from PHDAMV  .......?_.&...(.
[ibm][ims][drda][t4] 0020   41522E7761726400  00                AR.ward..         ..../....       
[ibm][ims][drda][t4] 
[ibm][ims][drda][t4]        SEND BUFFER: OPNQRY                 (ASCII)           (EBCDIC)
[ibm][ims][drda][t4] 0000   005BD00100030055  200C004421134BC9  .[.....U ..D!.K.  .$}............I
[ibm][ims][drda][t4] 0010   D4E2F14040404040  4040404040404040  ...@@@@@@@@@@@@@  MS1             
[ibm][ims][drda][t4] 0020   D5E4D3D3C9C44040  4040404040404040  ......@@@@@@@@@@  NULLID          
[ibm][ims][drda][t4] 0030   4040E2E8E2E2D5F2  F0F0404040404040  @@........@@@@@@    SYSSN200      
[ibm][ims][drda][t4] 0040   404040405359534C  564C303100010008  @@@@SYSLVL01....      ...<.<......
[ibm][ims][drda][t4] 0050   2114000000000005  215D01            !.......!].       .........).