187 lines
17 KiB
HTML
187 lines
17 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="concept" />
|
||
|
|
<meta name="DC.Title" content="Use Open Query File (OPNQRYF) command" />
|
||
|
|
<meta name="abstract" content="The Open Query File (OPNQRYF) command is a control language (CL) command that allows you to perform many data processing functions on database files. These topics discuss how to create a query using the OPNQRYF command, how to specify parameters for its major functions, and how to use it with your high-level language program." />
|
||
|
|
<meta name="description" content="The Open Query File (OPNQRYF) command is a control language (CL) command that allows you to perform many data processing functions on database files. These topics discuss how to create a query using the OPNQRYF command, how to specify parameters for its major functions, and how to use it with your high-level language program." />
|
||
|
|
<meta name="DC.subject" content="command, CL, OPNQRYF (Open Query File)" />
|
||
|
|
<meta name="keywords" content="command, CL, OPNQRYF (Open Query File)" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoopen.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafocrtqry.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoufinf.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoufdrf.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoopncl.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafozlt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoopnex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafodynrs.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoccfufmt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafocarr.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafocddm.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafochllp.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafomsgopn.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoopenm.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafodttc.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafodttaopq.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoqryrp.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoqryprf.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafooqfsst.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafooqfpcoth.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafooqfpcfld.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafocosof.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoconchkrcd.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoothrtcons.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoopnerr.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoodpc.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafofn.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafoexpressions.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafobif.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbaforbif.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../sqlp/rbafykickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../rbam6/rbam6clmain.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../cl/opnqryf.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafoopnqf" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Use Open Query File (OPNQRYF) command</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafoopnqf"><a name="rbafoopnqf"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Use Open Query File (OPNQRYF) command</h1>
|
||
|
|
<div><p>The Open Query File (OPNQRYF) command is a control language (CL)
|
||
|
|
command that allows you to perform many data processing functions on database
|
||
|
|
files. These topics discuss how to create a query using the OPNQRYF command,
|
||
|
|
how to specify parameters for its major functions, and how to use it with
|
||
|
|
your high-level language program.</p>
|
||
|
|
<p>Essentially, the OPNQRYF command acts as a filter between the processing
|
||
|
|
program and the database records. The database file can be a
|
||
|
|
physical or logical file. Unlike the Create Physical File (CRTPF) or Create
|
||
|
|
Logical File (CRTLF) command, the OPNQRYF command creates only a temporary
|
||
|
|
file for processing the data; it does not create a permanent file.</p>
|
||
|
|
<p>The OPNQRYF command has functions similar to those in data description
|
||
|
|
specifications (DDS) and the CRTPF and CRTLF commands. DDS requires source
|
||
|
|
statements and a separate step to create the file. The OPNQRYF command allows
|
||
|
|
a dynamic definition without using DDS. The OPNQRYF command does not support
|
||
|
|
all of the DDS functions, but it supports significant functions that go beyond
|
||
|
|
the capabilities of DDS. In addition, Query for iSeries™ can be used to perform some of
|
||
|
|
the function the OPNQRYF command performs. However, the OPNQRYF command is
|
||
|
|
more useful as a programmer's tool.</p>
|
||
|
|
<p>The OPNQRYF command parameters also have many functions similar to the
|
||
|
|
SQL SELECT statements. For example, the FILE parameter is similar to the SQL
|
||
|
|
FROM statement, the QRYSLT parameter is similar to the SQL WHERE statement,
|
||
|
|
the GRPFLD parameter is similar to the SQL GROUP BY statement, and the GRPSLT
|
||
|
|
parameter is similar to the SQL HAVING statement. </p>
|
||
|
|
<div class="p">The following list shows the major functions supplied by the OPNQRYF command:<ul><li>Dynamic record selection</li>
|
||
|
|
<li>Dynamic keyed sequence access path</li>
|
||
|
|
<li>Dynamic keyed sequence access path over a join</li>
|
||
|
|
<li>Dynamic join</li>
|
||
|
|
<li>Handling missing records in secondary join files</li>
|
||
|
|
<li>Unique-key processing</li>
|
||
|
|
<li>Mapped field definitions</li>
|
||
|
|
<li>Group processing</li>
|
||
|
|
<li>Final total-only processing</li>
|
||
|
|
<li>Improving performance</li>
|
||
|
|
<li>Open query identifier (ID)</li>
|
||
|
|
<li>Sort sequence processing</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<p>To understand the OPNQRYF command, you must be familiar with its two processing
|
||
|
|
approaches: using a format in the file, and using a file with a different
|
||
|
|
format. The typical use of the OPNQRYF command is to select, arrange, and
|
||
|
|
format the data so it can be read sequentially by your high-level language
|
||
|
|
program. </p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafocrtqry.htm">Create a query with the Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
To create a query, you can use the Open Query File (OPNQRYF) command.
|
||
|
|
Alternatively, you can create a query using the Run SQL Scripts window in iSeries Navigator. </li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoufinf.htm">Use an existing record format in the file</a></strong><br />
|
||
|
|
The Open Query File (OPNQRYF) command does the record selection and your program processes only the records that meet the selection values. You can use this approach to select a set of records, return records in a different sequence than they are stored, or both.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoufdrf.htm">Use a file with a different record format</a></strong><br />
|
||
|
|
For more advanced functions of the Open Query File (OPNQRYF) command (such as dynamically joining records from different files), you must define a new file that contains a different record format.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoopncl.htm">CL program coding with the Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
The Open Query File (OPNQRYF) command has these basic rules that can prevent coding errors.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafozlt.htm">The zero-length literal and the contains (*CT) function</a></strong><br />
|
||
|
|
In the Open Query File (OPNQRYF) command, a zero-length literal is denoted as a quoted string with nothing, not even a blank, between the quotation marks (""). Zero-length literal support changes the results of a comparison when used as the compare argument of the contains (*CT) function.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoopnex.htm">Usage notes for Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
These usage notes pertain to the examples in the topics that describe how to specify the parameters for the major functions of the Open Query File (OPNQRYF) command and how to use the OPNQRYF command with your high-level language program.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafodynrs.htm">Select records without using DDS</a></strong><br />
|
||
|
|
Dynamic record selection allows you to request a subset of the records in a file without using data description specifications (DDS).</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoccfufmt.htm">Considerations for creating a file and using the FORMAT parameter</a></strong><br />
|
||
|
|
This topic discusses the considerations for creating a file and using the FORMAT parameter.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafocarr.htm">Considerations for arranging records</a></strong><br />
|
||
|
|
This topic discusses the considerations for using the Open Query File (OPNQRYF) command to arrange records.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafocddm.htm">Considerations for distributed data management files</a></strong><br />
|
||
|
|
The Open Query File (OPNQRYF) command can process distributed data management (DDM) files, however, there are certain considerations.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafochllp.htm">Considerations for writing a high-level language program</a></strong><br />
|
||
|
|
There are certain considerations for writing a high-level language program.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafomsgopn.htm">Messages sent when the Open Query File (OPNQRYF) command is run</a></strong><br />
|
||
|
|
When the Open Query File (OPNQRYF) command is run, messages are sent informing the interactive user of the status of the OPNQRYF request. For example, if a keyed access path is built by the OPNQRYF command to satisfy the request, a message is sent to the user.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoopenm.htm">Use the Open Query File (OPNQRYF) command for more than just input</a></strong><br />
|
||
|
|
The Open Query File (OPNQRYF) command supports the OPTION parameter to determine the type of processing. The default is OPTION(*INP), so the file is opened for input only. You can also use other OPTION values on the OPNQRYF command and a high-level language program to add, update, or delete records through the open query file.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafodttc.htm">Compare date, time, and timestamp using the Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
A date, time, or timestamp value can be compared either with another value of the same data type or with a string representation of that data type.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafodttaopq.htm">Perform date, time, and timestamp arithmetic using the Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
Date, time, and timestamp values can be incremented, decremented,
|
||
|
|
and subtracted. These operations might involve decimal numbers called <em>durations</em>.
|
||
|
|
These topics include a definition of durations and a specification
|
||
|
|
of the rules for performing arithmetic operations on date, time, and timestamp
|
||
|
|
values.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoqryrp.htm">Use the Open Query File (OPNQRYF) command for random processing</a></strong><br />
|
||
|
|
You can use the Open Query File (OPNQRYF) command for sequential processing. You can also use the OPNQRYF command for random processing.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoqryprf.htm">Open Query File (OPNQRYF) command: Performance considerations</a></strong><br />
|
||
|
|
Here are tips and techniques for optimizing the performance when you use the Open Query File (OPNQRYF) command.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafooqfsst.htm">Open Query File (OPNQRYF) command: Performance considerations for sort sequence tables</a></strong><br />
|
||
|
|
Here are tips and techniques for optimizing the performance of sort sequence tables.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafooqfpcoth.htm">Performance comparisons with other database functions</a></strong><br />
|
||
|
|
The Open Query File (OPNQRYF) command uses the same database support as logical files and join logical files. Therefore, the performance of functions like building a keyed access path or doing a join operation is the same.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafooqfpcfld.htm">Field use</a></strong><br />
|
||
|
|
When the grouping function is used, all fields in the record format for the open query file (FORMAT parameter) and all key fields (KEYFLD parameter) must be either grouping fields (specified on the GRPFLD parameter) or mapped fields (specified on the MAPFLD parameter).</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafocosof.htm">Files shared in a job</a></strong><br />
|
||
|
|
In order for your application program to use the open data path built by the Open Query File (OPNQRYF) command, your program must share the query file. If your program does not open the query file as shared, then it actually does a full open of the file it was originally compiled to use (not the query open data path built by the OPNQRYF command).</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoconchkrcd.htm">Checking if the record format description changed</a></strong><br />
|
||
|
|
If record format level checking is indicated, the format level number of the open query file record format (identified on the FORMAT parameter) is checked against the record format your program was compiled against. This occurs when your program shares the previously opened query file.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoothrtcons.htm">Other runtime considerations for the Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
Read other runtime considerations for the Open Query File (OPNQRYF) command, including overrides and copying from an open query file.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoopnerr.htm">Typical errors when using the Open Query File (OPNQRYF) command</a></strong><br />
|
||
|
|
You must specify several functions correctly for the Open Query File (OPNQRYF) command and your program to get the correct results.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoodpc.htm">Open data path considerations</a></strong><br />
|
||
|
|
This topic outlines the considerations for the open data path (ODP).</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafofn.htm">Field names</a></strong><br />
|
||
|
|
Listed here are the requirements for specifying field names on the Open Query File (OPNQRYF) command parameters.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafoexpressions.htm">Expressions</a></strong><br />
|
||
|
|
This topic discusses the conventions for the expressions specified on the Open Query File (OPNQRYF) command.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbafobif.htm">Built-in functions</a></strong><br />
|
||
|
|
The built-in functions listed here are supported for the expression used to define a derived field on the MAPFLD parameter or a complex selection operand specified on the QRYSLT or GRPSLT parameter.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rbaforbif.htm">Restricted built-in functions</a></strong><br />
|
||
|
|
Listed here are several restricted built-in functions.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafoopen.htm" title="These topics describe how to use the Open Query File (OPNQRYF) and Open Database File (OPNDBF) commands to open database file members in a program. Examples, performance considerations, and guidelines for writing a high-level language program are included. Typical errors that might occur are also discussed.">Open a database file</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="../sqlp/rbafykickoff.htm">SQL programming</a></div>
|
||
|
|
<div><a href="../rbam6/rbam6clmain.htm">Control language (CL)</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="../cl/opnqryf.htm">Open Query File (OPNQRYF) command</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|