Runtime environment for SQL processing
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=19defines a Model 204 TCP/IP SQL thread for TCP/IP.IODEV=49defines 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 parameter of DEFINE LINK
- DATALEN parameter of DEFINE PROCESS
- SQLBUFSZ user parameter set in the IODEV line
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.