Runtime environment for SQL processing: Difference between revisions

From m204wiki
Jump to navigation Jump to search
No edit summary
 
(17 intermediate revisions by 2 users not shown)
Line 13: Line 13:
<p class="caption">SQL files for Online environment</p>
<p class="caption">SQL files for Online environment</p>
<table>
<table>
<tr class="head">
<th>File</th>
<th>File</th>
<th>Contents</th>
<th>Contents</th>
</tr>
<tr>
<tr>
<td>CATPROC</td>
<td>CATPROC</td>
<td>Procedures and data: SOUL procedures to run CCACATREPT subsystem and to create the CCACAT file for initial installation and reorganizations; and Help files for CCACATREPT.</td>
<td>Procedures and data: SOUL procedures to run [[SQL catalog reporting and querying|CCACATREPT]] subsystem and to create the CCACAT file for initial installation and reorganizations; and Help files for CCACATREPT.
<p class="note"><b>Note:</b> As of Model 204 version 7.5, these procedures and data are contained in M204PROC, and CATPROC is no longer a valid file.</p></td>
</tr>
</tr>
<tr>
<tr>
<td>CCACAT</td>
<td>CCACAT</td>
<td>Required file. Model 204 SQL catalog for storage of all SQL DDL definitions.
<td>Required file. Model 204 [[SQL catalog]] for storage of all SQL DDL definitions.
</td>
</td>
</tr>
</tr>
<tr>
<tr>
<td>TSFDATA</td>
<td>TSFDATA</td>
<td>Model 204 file for work records that permit you to retain TSF (CCATSF subsystem) working data across sessions.</td>
<td>Model 204 file for work records that permit you to retain TSF ([[SQL DDL from the Table Specification Facility|CCATSF]] subsystem) working data across sessions.</td>
</tr>
</tr>
<tr>
<tr>
<td>TSFPROC</td>
<td>TSFPROC</td>
<td>Procedures and data: SOUL procedures to run CCATSF subsystem, and Help files for the TSF.</td>
<td>Procedures and data: SOUL procedures to run the CCATSF subsystem, and Help files for the TSF.
<p class="note"><b>Note:</b> As of Model 204 version 7.5, these procedures and data are contained in M204PROC, and TSFPROC is no longer a valid file.</p></td>
</tr>
</tr>
</table>
</table>


==CCAIN parameters to support Model 204 SQL processing==
==CCAIN parameters to support Model 204 SQL processing==
The following table summarizes the recommended values for CCAIN parameters that support Model 204 SQL processing. For complete details on what these parameters support and how to adjust their values, see the [[List_of_Model_204_parameters|Model 204 parameter pages]].
The following table summarizes the recommended values for CCAIN parameters that support Model 204 SQL processing. For complete details on what these parameters support and how to adjust their values, see the [[List of Model 204 parameters|list of Model&nbsp;204 parameters]].


<p>When any of these parameters are required for RCL processing, the SQL settings are used as defaults.</p>
<p>
When any of these parameters are required for RCL processing, the SQL settings are used as defaults.</p>
<p class="caption">CCAIN parameters affected by SQL processing</p>
<p class="caption">CCAIN parameters affected by SQL processing</p>
<table>
<table>
<tr class="head">
<th>Parameter</th>
<th>Parameter</th>
<th>Recommendation</th>
<th>Recommendation</th></tr>
 
<tr>
<tr>
<td>CUSTOM </td>
<td><var>[[CUSTOM parameter|CUSTOM]]</var> </td>
<td>Set to 8 to allow RCL clients to issue the LOGIN command. This allows users to logout and login without closing the current connection and opening a new one.</td>
<td>Set to 8 to allow RCL clients to issue the <var>LOGIN</var> command. This allows users to logout and login without closing the current connection and opening a new one.</td></tr>
</tr>
 
<tr><td><var>[[LCPDLST parameter|LCPDLST]]</var></td>
<td>Use the default, 2176</td></tr>
 
<tr><td><var>[[LHEAP parameter|LHEAP]]</var></td>
<td>Set to 200000, then monitor and adjust</td></tr>
 
<tr><td><var>[[LIBUFF parameter|LIBUFF]]</var></td>
<td>Set to 5000; adjust to match <var>SQLIQBSZ</var></td></tr>
 
<tr><td><var>[[LNTBL parameter|LNTBL]]</var></td>
<td>Monitor NTBL and adjust</td></tr>
 
<tr><td><var>[[LOBUFF parameter|LOBUFF]]</var></td>
<td>Set to 5000</td></tr>
 
<tr>
<tr>
<td>LCPDLST</td>
<td><var>[[LPDLST parameter|LPDLST]]</var></td>
<td>Use the default, 2176</td>
<td>Set to 32760</td></tr>
</tr>
 
<tr><td><var>[[LQTBL parameter|LQTBL]]</var></td>
<td>Monitor QTBL and adjust</td></tr>
 
<tr>
<tr>
<td>LHEAP</td>
<td><var>[[LSTBL parameter|LSTBL]]</var></td>
<td>Set to 200000, then monitor and adjust</td>
<td>Monitor STBL and adjust</td></tr>
</tr>
 
<tr>
<tr>
<td>LIBUFF</td>
<td><var>[[LTTBL parameter|LTTBL]]</var></td>
<td>Set to 5000; adjust to match SQLBUFSZ</td>
<td>Set to 2000, then monitor TTBL, then adjust</td></tr>
</tr>
 
<tr>
<tr>
<td>LNTBL</td>
<td><var>[[LVTBL parameter|LVTBL]]</var> </td>
<td>Monitor NTBL and adjust</td>
<td>Monitor VTBL and adjust</td></tr>
</tr>
 
<tr>
<td>LOBUFF</td>
<td>Set to 5000</td>
</tr>
<tr>
<td>LPDLST</td>
<td>Set to 32760</td>
</tr>
<tr>
<td>LQTBL</td>
<td>Monitor QTBL and adjust</td>
</tr>
<tr>
<td>LSTBL</td>
<td>Monitor STBL and adjust</td>
</tr>
<tr>
<td>LTTBL</td>
<td>Set to 2000, then monitor TTBL, then adjust</td>
</tr>
<tr>
<tr>
<td>LVTBL </td>
<td><var>[[MAXBUF parameter|MAXBUF]]</var>  
<td>Monitor VTBL and adjust</td>
</tr>
<tr>
<td>MAXBUF
</td>
</td>
<td>Minimum value is 18</td>
<td>Minimum value is 18</td></tr>
</tr>
 
<tr>
<tr>
<td>MINBUF </td>
<td><var>[[MINBUF parameter|MINBUF]]</var> </td>
<td>Minimum value is 18. See the [[MINBUF parameter]] page for the formula used to calculate the appropriate value for MINBUF in SQL processing.</td>
<td>Minimum value is 18. See <var>[[MINBUF parameter|MINBUF]]</var> for the formula used to calculate the appropriate value for <var>MINBUF</var> in SQL processing.</td></tr>
</tr>
 
<tr>
<tr>
<td>NSUBTKS</td>
<td><var>[[NSUBTKS parameter|NSUBTKS]]</var></td>
<td>Increase by 3 for first open Model 204 TCP/IP (Horizon) link; by 2 for each subsequent link</td>
<td>Increase by 3 for first open Model&nbsp;204 TCP/IP (Horizon) link; by 2 for each subsequent link</td></tr>
</tr>
 
<tr>
<tr>
<td>SERVSIZE </td>
<td><var>[[SERVSIZE parameter|SERVSIZE]]</var> </td>
<td>Initially set this to at least 350K and adjust. For processing SQL statements that are unusually large, the recommended value is between 600K and 700K.</td>
<td>Initially set this to at least 350K and adjust. For processing SQL statements that are unusually large, the recommended value is between 600K and 700K.</td></tr>
</tr>
 
<tr>
<tr><td><var>[[SQLBSCAN parameter|SQLBSCAN]]</var> </td>
<td>SQLBSCAN </td>
<td>Use default, then adjust</td></tr>
<td>Use default, then adjust</td>
 
</tr>
<tr><td><var>[[SQLBUFSZ parameter|SQLBUFSZ]]</var></td>
<tr>
<td>SQLBUFSZ</td>
<td>Set to 100000 on first SQL IODEV line. RCL IODEV uses SQL IODEV setting (default).</td>
<td>Set to 100000 on first SQL IODEV line. RCL IODEV uses SQL IODEV setting (default).</td>
</tr>
</tr><tr>
 
<td><var>[[SQLCNVER parameter|SQLCNVER]]</var> </td>
<td>Review the <var>SQLCNVER</var> 0 and 1 settings to choose what is best for your application.</td></tr>
 
<tr>
<tr>
<td>SQLCNVER </td>
<td><var>[[SQLFILE parameter|SQLFILE]]</var></td>
<td>Review the 0 and 1 settings of the [[SQLCNVER parameter]] to choose what is best for your application.</td>
<td>Set to 1 to have Model 204 SQL processing generate a unique file name for the file to which your <var>CREATE TABLE</var> table is mapped in the SQL catalog</td></tr>
</tr>
 
<tr>
<tr><td><var>[[SQLIQBSZ parameter|SQLIQBSZ]]</var></td>
<td>SQLFILE</td>
<td>Set to 32752, then adjust</td></tr>
<td>Set to 1 to have Model 204 SQL processing generate a unique file name for the file to which your CREATE TABLE table is mapped in the SQL catalog</td>
 
</tr>
<tr><td><var>[[SQLLPLIM parameter|SQLLPLIM]]</var> </td>
<tr>
<td>Use default, then adjust</td></tr>
<td>SQLIQBSZ</td>
<td>Set to 32752, then adjust</td>
</tr>
<tr>
<td>SQLLPLIM </td>
<td>Use default, then adjust</td>
</tr>
</table>
</table>


Line 132: Line 128:
When handling an SQL statement that is greater than 32K, you must consider the following parameters:
When handling an SQL statement that is greater than 32K, you must consider the following parameters:
<ul>
<ul>
<li>
<li>The <var>[[LCPDLST parameter|LCPDLST]]</var> is the length of the C pushdown list. If its value is not large enough, the error message invoked includes the name of the relevant parameter.</li>
The LCPDLST parameter is the length of the C pushdown list. If its value is not large enough, the error message invoked includes the name of the relevant parameter.</li>
 
<li>
<li>The <var>[[SQLBUFSZ parameter|SQLBUFSZ]]</var> is the buffer to collect the entire SQL statement. It must be slightly greater than the maximum size of the largest SQL statement, which is the value of SQLI in the audit trail. For example, set SQLBUFSZ=100000 to run a DDL statement that is 52K.</li>
The SQLBUFSZ parameter is the buffer to collect the entire SQL statement. It must be slightly greater than the maximum size of the largest SQL statement, which is the value of SQLI in the audit trail. For example, set SQLBUFSZ=100000 to run a DDL statement that is 52K.</li>
 
<li>
<li>The <var>[[SQLIQBSZ parameter|SQLIQBSZ]]</var> is the buffer for the interface with IFAM. For example, set SQLIQBSZ=32752 to run an IFGET result that requires a buffer greater than 31K.</li>
The SQLIQBSZ parameter is the buffer for the interface with IFAM. For example, set SQLIQBSZ=32752 to run an IFGET result that requires a buffer greater than 31K.</li>
 
<li>
<li>Other pertinent parameters to review are <var>[[LVTBL parameter|LVTBL]]</var>, <var>[[LHEAP parameter|LHEAP]]</var>, and <var>[[SERVSIZE parameter|SERVSIZE]]</var>. If their values are not large enough, the error message invoked includes the name of the relevant parameter.
Other pertinent parameters to review are VTBL, LHEAP, and SERVSIZE. If their values are not large enough, the error message invoked includes the name of the relevant parameter.
<p>
<p>
To allow LHEAP and VTBL to be as large as possible, conserve address space by setting the buffer size parameters as low as possible. Alternatively, you can place CCASERVR in memory to eliminate size restrictions on SERVSIZE.</p>
To allow <var>LHEAP</var> and <var>LVTBL</var> to be as large as possible, conserve address space by setting the buffer size parameters as low as possible. Alternatively, you can place CCASERVR in memory to eliminate size restrictions on <var>SERVSIZE</var>.</p></li>
</li>
</ul>
</ul>


==CCATEMP file size==
==CCATEMP file size==
<p>SQL query processing uses CCATEMP space for sorting rows and for storing subquery results.   </p>
<p>
<p>Depending on the amount of data being accessed, your typical CCATEMP requirements might increase. You can set CCATEMP as high as 16 million pages. Overallocating CCATEMP wastes disk space and uses excessive memory, so only allocate additional pages if necessary.  </p>
SQL query processing uses CCATEMP space for sorting rows and for storing subquery results. </p>
<p>
Depending on the amount of data being accessed, your typical CCATEMP requirements might increase. You can set CCATEMP as high as 16 million pages. Overallocating CCATEMP wastes disk space and uses excessive memory, so only allocate additional pages if necessary.  </p>


==Defining SQL and RCL IODEV threads==
==Defining SQL and RCL IODEV threads==
Line 156: Line 152:


<p>
<p>
You must define IODEV threads for all Connect<span class="superstar">&#9733;</span> configurations:</p>
You must define <var>IODEV</var> threads for all Connect<span class="superstar">&#9733;</span> configurations:</p>
<ul>
<ul>
<li><i>IODEV=19</i> defines a Model 204 TCP/IP SQL thread for TCP/IP.   </li>
<li><code>IODEV=19</code> defines a Model&nbsp;204 TCP/IP SQL thread for TCP/IP. </li>
<li><i>IODEV=49</i> defines a Model 204 TCP/IP RCL thread for TCP/IP.   </li>
 
<li><code>IODEV=49</code> defines a Model&nbsp;204 TCP/IP RCL thread for TCP/IP. </li>
</ul>
</ul>


===Coding the IODEV line===
===Coding the IODEV line===
Your definition of an IODEV 19 or 49 thread involves the specification or adjustment of the NOTHREAD, SQLBUFSZ, and LHEAP parameters described in this section. For examples, see [[#Runtime examples|Runtime examples]].
Your definition of an IODEV 19 or 49 thread involves the specification or adjustment of the <var>NOTHREAD</var>, <var>SQLBUFSZ</var>, and <var>LHEAP</var> parameters described in this section. For examples, see [[#Runtime examples|Runtime examples]].


====RCL and SQL multithreaded environment====
====RCL and SQL multithreaded environment====
When defining IODEV=49 (RCL) and IODEV=19 (SQL) threads:
When defining IODEV=49 (RCL) and IODEV=19 (SQL) threads:
<ul>
<ul>
<li>
<li>In the IODEV=49 line and in the IODEV=19, set the total number of concurrent threads (<var>NOTHREAD</var>) and identify the number of each thread.</li>
In the IODEV=49 line and in the IODEV=19, set the total number of concurrent threads (NOTHREAD) and identify the number of each thread.</li>
 
<li>
<li>In the IODEV=19, set other IODEV parameters such as <var>SQLBUFSZ</var>, <var>LHEAP</var>, <var>LIBUFF</var>, and <var>SQLIQBSZ</var>. The RCL thread uses these SQL thread settings as defaults. </li>
In the IODEV=19, set other IODEV parameters such as SQLBUFSZ, LHEAP, LIBUFF, and SQLIQBSZ. The RCL thread uses these SQL thread settings as defaults. </li>
</ul>
</ul>


====NOTHREAD====
====NOTHREAD====
You can insert one or more IODEV=19 or 49 lines in the CCAIN stream. On the first line defining the SQL or RCL thread, include the NOTHREAD parameter to indicate the number of threads to be allocated. The number of threads of types 19 and 49 is controlled by the CPUID zap and NOTHREAD must be less than the number of threads defined against the CPUID zap for your site.
You can insert one or more IODEV=19 or 49 lines in the CCAIN stream. On the first line defining the SQL or RCL thread, include the <var>NOTHREAD</var> parameter to indicate the number of threads to be allocated. The number of threads of types 19 and 49 is controlled by the CPUID zap, and <var>NOTHREAD</var> must be less than the number of threads defined against the CPUID zap for your site.
<p>
<p>
Set NOTHREAD to the maximum number of concurrent Connect<span class="superstar">&#9733;</span> conversations to be supported. Make sure that your Model 204 NUSERS parameter setting accommodates these additional SQL and RCL threads.</p>
Set <var>NOTHREAD</var> to the maximum number of concurrent Connect<span class="superstar">&#9733;</span> conversations to be supported. Make sure that your Model&nbsp;204 <var>NUSERS</var> parameter setting accommodates these additional SQL and RCL threads.</p>


====SQLBUFSZ====
====SQLBUFSZ====
SQLBUFSZ is a required user parameter that defines the maximum incoming SQL message length that can be presented to the SQL Server. The recommended initial value for this parameter is 100000. Specify it on the first IODEV 19 line.
<var>SQLBUFSZ</var> is a required user parameter that defines the maximum incoming SQL message length that can be presented to the SQL Server. The recommended initial value for this parameter is 100000. Specify it on the first IODEV 19 line.
<p>For more information about sizing SQL buffers, see the [[SQLBUFSZ_parameter]] topic.</p>
<p>
For more information about sizing SQL buffers, see <var>[[SQLBUFSZ parameter|SQLBUFSZ]]</var>.</p>


====LHEAP====
====LHEAP====
LHEAP specifies the size of HEAP, a dynamic storage area required for the processing of SQL C routines. HEAP is in the variable portion of the server area allocated to each user, and its size is added to the variable size calculation of the server area. An overall working space of 200,000 bytes is usually sufficient for SQL operations.
<var>LHEAP</var> specifies the size of HEAP, a dynamic storage area required for the processing of SQL C routines. HEAP is in the variable portion of the server area allocated to each user, and its size is added to the variable size calculation of the server area. An overall working space of 200,000 bytes is usually sufficient for SQL operations.
<p>
<p>
The recommended initial value is 200000 for SQL threads. Specify it on the first IODEV 19 line. Do not specify it with the User 0 parameters, because this wastes space. For non-SQL threads, which do not require HEAP space, you can set or reset LHEAP to zero, the default. </p>
The recommended initial value is 200000 for SQL threads. Specify it on the first IODEV 19 line. Do not specify it with the User 0 parameters, because this wastes space. For non-SQL threads, which do not require HEAP space, you can set or reset <var>LHEAP</var> to zero, the default. </p>
<p>
<p>
You can monitor the high-water mark for use of this storage area with the HEAP statistic, described in [[#HEAP and PDL high-water marks|HEAP and PDL high-water marks]].</p>
You can monitor the high-water mark for use of this storage area with the HEAP statistic, described in [[#HEAP and PDL high-water marks|HEAP and PDL high-water marks]].</p>
<p>
<p>
As the query complexity increases, you must increase the LHEAP value accordingly, as you encounter SQL error messages. Many SQL error messages offer specific advice. However, if you continue to get SQL error messages after increasing the LHEAP value, your query is too complex for RSQL to handle. You should rewrite your query into smaller, less complex parts.</p>
As the query complexity increases, you must increase the <var>LHEAP</var> value accordingly, as you encounter SQL error messages. Many SQL error messages offer specific advice. However, if you continue to get SQL error messages after increasing the <var>LHEAP</var> value, your query is too complex for RSQL to handle. You should rewrite your query into smaller, less complex parts.</p>


==Runtime examples==
==Runtime examples==
Line 236: Line 233:
Server areas are the internal work areas allocated to each user. The server area includes a fixed space portion and a variable space portion. This section lists the fixed and variable portions of the server area that are affected significantly by SQL Server processing.
Server areas are the internal work areas allocated to each user. The server area includes a fixed space portion and a variable space portion. This section lists the fixed and variable portions of the server area that are affected significantly by SQL Server processing.
<p>
<p>
The SERVSIZE system parameter specifies the size of the server area available for all users for the Online run or per IODEV thread. SERVSIZE is the sum of the fixed and variable portions of the server area.  </p>
The <var>SERVSIZE</var> system parameter specifies the size of the server area available for all users for the Online run or per IODEV thread. <var>SERVSIZE</var> is the sum of the fixed and variable portions of the server area.  </p>
<p>
<p>
You can calculate SERVSIZE according to formulas provided in the [[Defining the runtime environment (CCAIN)#Server_areas|server areas]] topic.</p>
You can calculate <var>SERVSIZE</var> according to formulas provided in the [[Defining the runtime environment (CCAIN)#Server areas|server areas]] topic.</p>


<p>
<p>
The fixed and variable portions of the server area are calculated separately. To help you estimate SERVSIZE for Model 204 SQL processing, the following sections list the server area parameters and tables that are affected by SQL processing. </p>
The fixed and variable portions of the server area are calculated separately. To help you estimate <var>SERVSIZE</var> for Model&nbsp;204 SQL processing, the following sections list the server area parameters and tables that are affected by SQL processing. </p>
<p>
<p>
Adjust your typical estimates of these parameters and tables as recommended, and include them in your usual SERVSIZE calculation along with the parameters and tables not affected by SQL processing. </p>
Adjust your typical estimates of these parameters and tables as recommended, and include them in your usual <var>SERVSIZE</var> calculation along with the parameters and tables not affected by SQL processing. </p>
<p>
<p>
The recommended minimum value of SERVSIZE for SQL processing is 350K.  For processing SQL statements that are unusually large, the recommended value is between 600K and 700K.</p>
The recommended minimum value of <var>SERVSIZE</var> for SQL processing is 350K.  For processing SQL statements that are unusually large, the recommended value is between 600K and 700K.</p>


===Fixed server area===
===Fixed server area===
LIBUFF and LOBUFF are parameters that contribute to the fixed portion of the server area calculation. Their typical settings must be increased to accommodate SQL processing of certain kinds of long input statements or returned data. The maximum for both is 32K. Technical Support recommends that you set LIBUFF close to the value of SQLBUFSZ.
<var>LIBUFF</var> and <var>LOBUFF</var> are parameters that contribute to the fixed portion of the server area calculation. Their typical settings must be increased to accommodate SQL processing of certain kinds of long input statements or returned data. The maximum for both is 32K. Technical Support recommends that you set <var>LIBUFF</var> close to the value of <var>SQLBUFSZ</var>.
<p>
<p>
In addition to these parameters, the fixed portion of the server area includes an area (called C PRV in the audit trail) that is used internally by the SQL Engine for storage of C global variables. SQL IODEVs must allow 6K bytes of fixed server size for the C PRV; non-SQL IODEVs do not need to allocate server space for the C PRV.   </p>
In addition to these parameters, the fixed portion of the server area includes an area (called <code>C PRV</code> in the audit trail) that is used internally by the SQL Engine for storage of C global variables. SQL IODEVs must allow 6K bytes of fixed server size for the C PRV; non-SQL IODEVs do not need to allocate server space for the C PRV. </p>


===Variable server area===
===Variable server area===
Line 259: Line 256:
[[#Variable server area sections affected by SQL|Variable server area sections affected by SQL]] lists the sections of the server area used for SQL processing. You might need to resize the controlling parameters to conform to SQL application requirements. Estimate as usual the sizes of the server tables not affected by SQL processing. </p>
[[#Variable server area sections affected by SQL|Variable server area sections affected by SQL]] lists the sections of the server area used for SQL processing. You might need to resize the controlling parameters to conform to SQL application requirements. Estimate as usual the sizes of the server tables not affected by SQL processing. </p>
<p>
<p>
For more information about monitoring server tables and the parameters with which you control their size, see the [[Defining the runtime environment (CCAIN)#Server_areas|server areas]] topic. </p>
For more information about monitoring server tables and the parameters with which you control their size, see the [[Defining the runtime environment (CCAIN)#Server areas|server areas]] topic. </p>


<p>
<p>
Line 265: Line 262:
<p class="caption">Variable server area sections affected by SQL</p>
<p class="caption">Variable server area sections affected by SQL</p>
<table>
<table>
<tr class="head">
<th>Section</th>
<th>Section</th>
<th>Contents</th>
<th>Contents</th>
<th>Controlling parameter</th>
<th>Controlling parameter</th>
</tr>
<tr>
<tr>
<td>HEAP
<td>HEAP</td>
</td>
<td>C routine processing</td>
<td>C routine processing
<td><var>[[LHEAP parameter|LHEAP]]</var></td>
</td>
<td>LHEAP
</td>
</tr>
</tr>
<tr>
<tr>
<td>LPDLST
<td>LPDLST</td>
</td>
<td>User pushdown list</td>
<td>User pushdown list
<td><var>[[LPDLST parameter|LPDLST]]</var></td></tr>
</td>
 
<td>LPDLST
</td>
</tr>
<tr>
<tr>
<td>NTBL
<td>NTBL</td>
</td>
<td>Statement labels, list names, and variables</td>
<td>Statement labels, list names, and variables
<td><var>[[LNTBL parameter|LNTBL]]</var></td></tr>
</td>
 
<td>LNTBL
</td>
</tr>
<tr>
<tr>
<td>QTBL
<td>QTBL</td>
</td>
<td>Statements in internal form (quadruples) </td>
<td>Statements in internal form (quadruples)  
<td><var>[[LQTBL parameter|LQTBL]]</var></td></tr>
</td>
 
<td>LQTBL
</td>
</tr>
<tr>
<tr>
<td>STBL
<td>STBL</td>
</td>
<td>Character strings</td>
<td>Character strings
<td><var>[[LSTBL parameter|LSTBL]]</var></td></tr>
</td>
 
<td>LSTBL
</td>
</tr>
<tr>
<tr>
<td>TTBL
<td>TTBL</td>
</td>
<td>Temporary work pages</td>
<td>Temporary work pages
<td><var>[[LTTBL parameter|LTTBL]]</var></td></tr>
</td>
 
<td>LTTBL
</td>
</tr>
<tr>
<tr>
<td>VTBL
<td>VTBL</td>
</td>
<td>Compiler variables</td>
<td>
<td><var>[[LVTBL parameter|LVTBL]]</var></td></tr>
Compiler variables</td>
<td>LVTBL</td>
</tr>
</table>
</table>


==Setting initial sizes for SQL buffers==
==Setting initial sizes for SQL buffers==
The system manager must set three SQL buffer parameters for Connect<span class="superstar">&#9733;</span>, which are described in detail in the following wiki topics:
The system manager must set three SQL buffer parameters for Connect<span class="superstar">&#9733;</span>, which are described in detail in the following topics:
<ul>
<ul>
<li>INBUFSIZE parameter of [[DEFINE LINK command: Horizon for TCP/IP|DEFINE LINK]]</li>
<li><var>INBUFSIZE</var> parameter of <var>[[DEFINE LINK command: Horizon for TCP/IP|DEFINE LINK]]</var></li>
<li>DATALEN parameter of [[DEFINE PROCESS command: Horizon for TCP/IP and VTAM|DEFINE PROCESS]]</li>
 
<li>[[SQLBUFSZ parameter|SQLBUFSZ]] user parameter set in the IODEV line</li>
<li><var>DATALEN</var> parameter of <var>[[DEFINE PROCESS command: Horizon for TCP/IP and VTAM|DEFINE PROCESS]]</var></li>
 
<li><var>[[SQLBUFSZ parameter|SQLBUFSZ]]</var> user parameter set in the IODEV line</li>
</ul>
</ul>


===INBUFSIZE===
===INBUFSIZE===
<p>At the mainframe, Model 204 TCP/IP receives the TCP/IP request unit (TU) into a buffer whose size is set by the DEFINE LINK parameter INBUFSIZE. The buffer receives and passes one TU at a time. For Model 204 TCP/IP set INBUFSIZE to 4096 or the TCP/IP TU size in your network.</p>
<p>
At the mainframe, Model 204 TCP/IP receives the TCP/IP request unit (TU) into a buffer whose size is set by the <var>DEFINE LINK</var> parameter <var>INBUFSIZE</var>. The buffer receives and passes one TU at a time. For Model 204 TCP/IP, set <var>INBUFSIZE</var> to 4096 or to the TCP/IP TU size in your network.</p>


===DATALEN===
===DATALEN===
<p>In Model 204 DATALEN has a maximum setting of 32K. It should be set to the average amount of data in any request in your network.</p>
<p>
In Model 204, <var>DATALEN</var> has a maximum setting of 32K. It should be set to the average amount of data in any request in your network.</p>
 
==See also==
{{Template:SQL connectivity topic list}}


[[Category: SQL connectivity]]
[[Category: SQL connectivity]]

Latest revision as of 19:17, 9 November 2017

Overview

This topic discusses changes to the job or EXEC that brings up your Model 204 Online. These changes are required or recommended to support Model 204 SQL and Remote Command Line (RCL) processing. This topic also provides information about sizing SQL buffers.

You also need to specify DEFINE commands for Model 204 TCP/IP (Horizon). For more information, see:

DEFINE LINK command: Horizon for TCP/IP

DEFINE PROCESS command: Horizon for TCP/IP and VTAM

DEFINE PROCESSGROUP command: Horizon for TCP/IP

SQL processing files

The following table lists SQL-specific files you need to include in your Online environment.

Add DD statements or FILEDEFs for these files to the job that brings up your Model 204 Online. Model 204 installation or reinstallation allocates the files.

SQL files for Online environment

File Contents
CATPROC Procedures and data: SOUL procedures to run CCACATREPT subsystem and to create the CCACAT file for initial installation and reorganizations; and Help files for CCACATREPT.

Note: As of Model 204 version 7.5, these procedures and data are contained in M204PROC, and CATPROC is no longer a valid file.
CCACAT Required file. Model 204 SQL catalog for storage of all SQL DDL definitions.
TSFDATA Model 204 file for work records that permit you to retain TSF (CCATSF subsystem) working data across sessions.
TSFPROC Procedures and data: SOUL procedures to run the CCATSF subsystem, and Help files for the TSF.

Note: As of Model 204 version 7.5, these procedures and data are contained in M204PROC, and TSFPROC is no longer a valid file.

CCAIN parameters to support Model 204 SQL processing

The following table summarizes the recommended values for CCAIN parameters that support Model 204 SQL processing. For complete details on what these parameters support and how to adjust their values, see the list of Model 204 parameters.

When any of these parameters are required for RCL processing, the SQL settings are used as defaults.

CCAIN parameters affected by SQL processing

Parameter Recommendation
CUSTOM Set to 8 to allow RCL clients to issue the LOGIN command. This allows users to logout and login without closing the current connection and opening a new one.
LCPDLST Use the default, 2176
LHEAP Set to 200000, then monitor and adjust
LIBUFF Set to 5000; adjust to match SQLIQBSZ
LNTBL Monitor NTBL and adjust
LOBUFF Set to 5000
LPDLST Set to 32760
LQTBL Monitor QTBL and adjust
LSTBL Monitor STBL and adjust
LTTBL Set to 2000, then monitor TTBL, then adjust
LVTBL Monitor VTBL and adjust
MAXBUF Minimum value is 18
MINBUF Minimum value is 18. See MINBUF for the formula used to calculate the appropriate value for MINBUF in SQL processing.
NSUBTKS Increase by 3 for first open Model 204 TCP/IP (Horizon) link; by 2 for each subsequent link
SERVSIZE Initially set this to at least 350K and adjust. For processing SQL statements that are unusually large, the recommended value is between 600K and 700K.
SQLBSCAN Use default, then adjust
SQLBUFSZ Set to 100000 on first SQL IODEV line. RCL IODEV uses SQL IODEV setting (default).
SQLCNVER Review the SQLCNVER 0 and 1 settings to choose what is best for your application.
SQLFILE Set to 1 to have Model 204 SQL processing generate a unique file name for the file to which your CREATE TABLE table is mapped in the SQL catalog
SQLIQBSZ Set to 32752, then adjust
SQLLPLIM Use default, then adjust

Handling SQL statements greater than 32K bytes

When handling an SQL statement that is greater than 32K, you must consider the following parameters:

  • The LCPDLST is the length of the C pushdown list. If its value is not large enough, the error message invoked includes the name of the relevant parameter.
  • The SQLBUFSZ is the buffer to collect the entire SQL statement. It must be slightly greater than the maximum size of the largest SQL statement, which is the value of SQLI in the audit trail. For example, set SQLBUFSZ=100000 to run a DDL statement that is 52K.
  • The SQLIQBSZ is the buffer for the interface with IFAM. For example, set SQLIQBSZ=32752 to run an IFGET result that requires a buffer greater than 31K.
  • Other pertinent parameters to review are LVTBL, LHEAP, and SERVSIZE. If their values are not large enough, the error message invoked includes the name of the relevant parameter.

    To allow LHEAP and LVTBL to be as large as possible, conserve address space by setting the buffer size parameters as low as possible. Alternatively, you can place CCASERVR in memory to eliminate size restrictions on SERVSIZE.

CCATEMP file size

SQL query processing uses CCATEMP space for sorting rows and for storing subquery results.

Depending on the amount of data being accessed, your typical CCATEMP requirements might increase. You can set CCATEMP as high as 16 million pages. Overallocating CCATEMP wastes disk space and uses excessive memory, so only allocate additional pages if necessary.

Defining SQL and RCL IODEV threads

Model 204 requires a pool of threads to support server control of SQL connections. The system manager must define these threads in the CCAIN stream as part of the Online startup configuration.

For more information about CCAIN and IODEV threads, see defining the user environment.

You must define IODEV threads for all Connect★ configurations:

  • IODEV=19 defines a Model 204 TCP/IP SQL thread for TCP/IP.
  • IODEV=49 defines a Model 204 TCP/IP RCL thread for TCP/IP.

Coding the IODEV line

Your definition of an IODEV 19 or 49 thread involves the specification or adjustment of the NOTHREAD, SQLBUFSZ, and LHEAP parameters described in this section. For examples, see Runtime examples.

RCL and SQL multithreaded environment

When defining IODEV=49 (RCL) and IODEV=19 (SQL) threads:

  • In the IODEV=49 line and in the IODEV=19, set the total number of concurrent threads (NOTHREAD) and identify the number of each thread.
  • In the IODEV=19, set other IODEV parameters such as SQLBUFSZ, LHEAP, LIBUFF, and SQLIQBSZ. The RCL thread uses these SQL thread settings as defaults.

NOTHREAD

You can insert one or more IODEV=19 or 49 lines in the CCAIN stream. On the first line defining the SQL or RCL thread, include the NOTHREAD parameter to indicate the number of threads to be allocated. The number of threads of types 19 and 49 is controlled by the CPUID zap, and NOTHREAD must be less than the number of threads defined against the CPUID zap for your site.

Set NOTHREAD to the maximum number of concurrent Connect★ conversations to be supported. Make sure that your Model 204 NUSERS parameter setting accommodates these additional SQL and RCL threads.

SQLBUFSZ

SQLBUFSZ is a required user parameter that defines the maximum incoming SQL message length that can be presented to the SQL Server. The recommended initial value for this parameter is 100000. Specify it on the first IODEV 19 line.

For more information about sizing SQL buffers, see SQLBUFSZ.

LHEAP

LHEAP specifies the size of HEAP, a dynamic storage area required for the processing of SQL C routines. HEAP is in the variable portion of the server area allocated to each user, and its size is added to the variable size calculation of the server area. An overall working space of 200,000 bytes is usually sufficient for SQL operations.

The recommended initial value is 200000 for SQL threads. Specify it on the first IODEV 19 line. Do not specify it with the User 0 parameters, because this wastes space. For non-SQL threads, which do not require HEAP space, you can set or reset LHEAP to zero, the default.

You can monitor the high-water mark for use of this storage area with the HEAP statistic, described in HEAP and PDL high-water marks.

As the query complexity increases, you must increase the LHEAP value accordingly, as you encounter SQL error messages. Many SQL error messages offer specific advice. However, if you continue to get SQL error messages after increasing the LHEAP value, your query is too complex for RSQL to handle. You should rewrite your query into smaller, less complex parts.

Runtime examples

Shown in these examples are sample settings of some of the CCAIN parameters described earlier in this topic, the first two IODEV lines for each SQL IODEV, and Model 204 SQL DEFINE commands.

For all operating systems using TCP/IP RCL and SQL threads

The following example is an excerpt from a job that brings up a Model 204 Online for SQL processing, using a TCP/IP connection.

...LIBUFF=3000,LNTBL=600,LOBUFF=5000,LPDLST=32760, LQTBL=2000,LSTBL=12000,LTTBL=150,LVTBL=300, MINBUF=18,MAXBUF=200,NSUBTKS=15,... SERVSIZE=700000,... . . . IODEV=49,POLLNO=1,NOTHREAD=4 IODEV=49,POLLNO=2 IODEV=49,POLLNO=3 IODEV=49,POLLNO=4 IODEV=19,POLLNO=1,NOTHREAD=4,SQLBUFSZ=100000,LHEAP=200000, - LIBUFF=6048,SQLIQBSZ=32752 IODEV=19,POLLNO=2 IODEV=19,POLLNO=3 IODEV=19,POLLNO=4 * above IODEV=49 is for RCL connections * above IODEV=19 is for SQL CONNECT* connections *-------------------------------------------------------* / * DEFINE CONNECT* SQL AND RCL LINK ETC... * / *-------------------------------------------------------* / ********************************************** DEFINE LINK TCPSQL WITH SCOPE=SYSTEM TRANSPORT=TCPSE - PROTOCOL=IP LOCALID=ANY INBUFSIZE=4096 CONNECTIONS=8 - SERVPORT=2132 DEFINE PROCESSGROUP ANY192 WITH SCOPE=SYSTEM LINK=TCPSQL - INLIMIT=8 OUTLIMIT=8 REMOTEID=192.0.0.0 LOGIN=NOTRUST - GUESTUSER=REJECT MASK=255.0.0.0 DEFINE PROCESSGROUP ANY204 WITH SCOPE=SYSTEM LINK=TCPSQL - INLIMIT=8 OUTLIMIT=8 REMOTEID=204.0.0.0 LOGIN=NOTRUST - GUESTUDEFINE PROCESS CCARSQL WITH SCOPE = SYSTEM - DATALEN = 32763 FROM = (ANY192, ANY204) SER=REJECT MASK=255.0.0.0 ********************************************************

Server area sizing

Server areas are the internal work areas allocated to each user. The server area includes a fixed space portion and a variable space portion. This section lists the fixed and variable portions of the server area that are affected significantly by SQL Server processing.

The SERVSIZE system parameter specifies the size of the server area available for all users for the Online run or per IODEV thread. SERVSIZE is the sum of the fixed and variable portions of the server area.

You can calculate SERVSIZE according to formulas provided in the server areas topic.

The fixed and variable portions of the server area are calculated separately. To help you estimate SERVSIZE for Model 204 SQL processing, the following sections list the server area parameters and tables that are affected by SQL processing.

Adjust your typical estimates of these parameters and tables as recommended, and include them in your usual SERVSIZE calculation along with the parameters and tables not affected by SQL processing.

The recommended minimum value of SERVSIZE for SQL processing is 350K. For processing SQL statements that are unusually large, the recommended value is between 600K and 700K.

Fixed server area

LIBUFF and LOBUFF are parameters that contribute to the fixed portion of the server area calculation. Their typical settings must be increased to accommodate SQL processing of certain kinds of long input statements or returned data. The maximum for both is 32K. Technical Support recommends that you set LIBUFF close to the value of SQLBUFSZ.

In addition to these parameters, the fixed portion of the server area includes an area (called C PRV in the audit trail) that is used internally by the SQL Engine for storage of C global variables. SQL IODEVs must allow 6K bytes of fixed server size for the C PRV; non-SQL IODEVs do not need to allocate server space for the C PRV.

Variable server area

The variable portion of the server area includes server tables and a dynamic storage area for the processing of SQL C routines.

Server tables are sections of the server area used by the compiler and evaluator to store all the information necessary to run either SOUL or SQL requests. Some server tables are also used by the editor and Host Language Interface (HLI) functions.

Variable server area sections affected by SQL lists the sections of the server area used for SQL processing. You might need to resize the controlling parameters to conform to SQL application requirements. Estimate as usual the sizes of the server tables not affected by SQL processing.

For more information about monitoring server tables and the parameters with which you control their size, see the server areas topic.

For more information about HEAP, see the discussion of SQL IODEV threads in Defining SQL and RCL IODEV threads.

Variable server area sections affected by SQL

Section Contents Controlling parameter
HEAP C routine processing LHEAP
LPDLST User pushdown list LPDLST
NTBL Statement labels, list names, and variables LNTBL
QTBL Statements in internal form (quadruples) LQTBL
STBL Character strings LSTBL
TTBL Temporary work pages LTTBL
VTBL Compiler variables LVTBL

Setting initial sizes for SQL buffers

The system manager must set three SQL buffer parameters for Connect★, which are described in detail in the following topics:

INBUFSIZE

At the mainframe, Model 204 TCP/IP receives the TCP/IP request unit (TU) into a buffer whose size is set by the DEFINE LINK parameter INBUFSIZE. The buffer receives and passes one TU at a time. For Model 204 TCP/IP, set INBUFSIZE to 4096 or to the TCP/IP TU size in your network.

DATALEN

In Model 204, DATALEN has a maximum setting of 32K. It should be set to the average amount of data in any request in your network.

See also

Retrieved from "https://m204wiki.rocketsoftware.com/index.php?title=Runtime_environment_for_SQL_processing&oldid=104535"