Runtime environment for SQL processing: Difference between revisions

From m204wiki
Jump to navigation Jump to search
Line 162: Line 162:
You must define IODEV threads for all Connect<span class="superstar">&#9733;</span> configurations:</p>
You must define IODEV 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 <var>[[SQLBUFSZ parameter|SQLBUFSZ]]</var>.</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==

Revision as of 19:20, 10 August 2016

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.
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 CCATSF subsystem, and Help files for the TSF.

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 SQLBUFSZ
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 VTBL, 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 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.

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<.var> 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 wiki 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 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.