<?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="reference" /> <meta name="DC.Title" content="Example 2: Dynamically join database files without DDS" /> <meta name="abstract" content="This example shows how to read only those records with secondary file records when dynamically joining database files without DDS." /> <meta name="description" content="This example shows how to read only those records with secondary file records when dynamically joining database files without DDS." /> <meta name="DC.Relation" scheme="URI" content="rbafodynjf.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="rbafodynjfex2" /> <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>Example 2: Dynamically join database files without DDS</title> </head> <body id="rbafodynjfex2"><a name="rbafodynjfex2"><!-- --></a> <!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script> <h1 class="topictitle1">Example 2: Dynamically join database files without DDS</h1> <div><p>This example shows how to read only those records with secondary file records when dynamically joining database files without DDS.</p> <div class="section"><p>Assume that you want to join files FILEAB, FILECD, and FILEEF to select only those records with matching records in secondary files. Define a file JOINF and describe the format that should be used. Assume that the record formats for the files contain the following fields:</p> </div> <table cellpadding="4" cellspacing="0" border="1" class="tableborder"><tr><td> <table cellpadding="4" cellspacing="0" summary="" width="100%" border="0"><thead align="left"><tr><th align="left" valign="bottom" width="25%" id="d0e23">FILEAB</th> <th align="left" valign="bottom" width="25%" id="d0e25">FILECD</th> <th align="left" valign="bottom" width="25%" id="d0e27">FILEEF</th> <th align="left" valign="bottom" width="25%" id="d0e29">JOINF</th> </tr> </thead> <tbody><tr><td align="left" valign="top" width="25%" headers="d0e23 ">Abitm</td> <td align="left" valign="top" width="25%" headers="d0e25 ">Cditm</td> <td align="left" valign="top" width="25%" headers="d0e27 ">Efitm</td> <td align="left" valign="top" width="25%" headers="d0e29 ">Abitm</td> </tr> <tr><td align="left" valign="top" width="25%" headers="d0e23 ">Abord</td> <td align="left" valign="top" width="25%" headers="d0e25 ">Cddscp</td> <td align="left" valign="top" width="25%" headers="d0e27 ">Efcolr</td> <td align="left" valign="top" width="25%" headers="d0e29 ">Abord</td> </tr> <tr><td align="left" valign="top" width="25%" headers="d0e23 ">Abdat</td> <td align="left" valign="top" width="25%" headers="d0e25 ">Cdcolr</td> <td align="left" valign="top" width="25%" headers="d0e27 ">Efqty</td> <td align="left" valign="top" width="25%" headers="d0e29 ">Cddscp</td> </tr> <tr><td align="left" valign="top" width="25%" headers="d0e23 "> </td> <td align="left" valign="top" width="25%" headers="d0e25 "> </td> <td align="left" valign="top" width="25%" headers="d0e27 "> </td> <td align="left" valign="top" width="25%" headers="d0e29 ">Cdcolr</td> </tr> <tr><td align="left" valign="top" width="25%" headers="d0e23 "> </td> <td align="left" valign="top" width="25%" headers="d0e25 "> </td> <td align="left" valign="top" width="25%" headers="d0e27 "> </td> <td align="left" valign="top" width="25%" headers="d0e29 ">Efqty</td> </tr> </tbody> </table> </td></tr></table> <div class="section"><p>In this case, all field names in the files that make up the join file begin with a 2-character prefix (identical for all fields in the file) and end with a suffix that is identical across all the files (for example, <em>xxitm</em>). This makes all field names unique and avoids having to qualify them.</p> </div> <div class="section"><div class="p">The <em>xxitm</em> field allows the join from FILEAB to FILECD. The two fields <em>xxitm</em> and <em>xxcolr</em> allow the join from FILECD to FILEEF. A keyed sequence access path does not have to exist for these files. However, if a keyed sequence access path does exist, performance might improve significantly because the system will attempt to use the existing access path to arrange and select records, where it can. If access paths do not exist, the system automatically creates and maintains them as long as the file is open. <pre>OVRDBF FILE(JOINF) TOFILE(FILEAB) SHARE(*YES) OPNQRYF FILE(FILEAB FILECD FILEEF) + FORMAT(JOINF) + JFLD((ABITM CDITM)(CDITM EFITM) + (CDCOLR EFCOLR)) CALL PGM(PGME) /* Created using file JOINF as input */ CLOF OPNID(FILEAB) DLTOVR FILE(JOINF)</pre> </div> </div> <div class="section"><div class="p">The join field pairs do not have to be specified in the preceding order. For example, the same result is achieved with a JFLD parameter value of: <pre>JFLD((CDCOLR EFCOLR)(ABITM CDITM) (CDITM EFITM))</pre> </div> </div> <div class="section"><p>The attributes of each pair of join fields do not have to be identical. Normal padding of character fields and decimal alignment for numeric fields occurs automatically.</p> </div> <div class="section"><p>The JDFTVAL parameter is not specified so *NO is assumed and no default values are used to construct join records. If you specified JDFTVAL(*YES) and there is no record in file FILECD that has the same join field value as a record in file FILEAB, defaults are used for the <em>Cddscp</em> and <em>Cdcolr</em> fields to join to file FILEEF. Using these defaults, a matching record can be found in file FILEEF (depending on if the default value matches a record in the secondary file). If not, a default value appears for these files and for the <em>Efqty</em> field.</p> </div> </div> <div> <div class="familylinks"> <div class="parentlink"><strong>Parent topic:</strong> <a href="rbafodynjf.htm" title="The dynamic join function allows you to join files without having to first specify data description specifications (DDS) and create a join logical file.">Dynamically join database files without DDS</a></div> </div> </div> </body> </html>