SQLBUFSZ parameter

From m204wiki
Revision as of 10:19, 7 September 2017 by DCameron (talk | contribs) (→‎Handling BLOBs and CLOBs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Remote SQL server buffer size

Summary

Default value
0
Parameter type
User
Where set
By any user
Related products
All
Introduced
Model 204 V3.1

Description

The size of the Model 204 SQL Server buffer that assembles incoming SQL messages from a Horizon, CRAM SQL, or IUCV SQL buffer

The SQLBUFSZ value defines the maximum length of an incoming SQL message. The incoming SQL message contains the SQL request in addition to the data. Since the largest incoming SQL message is likely to be a DDL transaction, set SQLBUFSZ large enough to accommodate your largest DDL transaction plus 50 bytes of overhead. The maximum value you can specify is slightly over two billion. The recommended initial value for the SQLBUFSZ parameter is 100000.

For...Set SQLBUFSZ...
A General Data Stream (GDS) passed from the conversation buffer to CMIS (SQL communications interface) receiving buffer and moved to the Model 204 SQL bufferGreater than or equal to the maximum incoming SQL message length that can be received by the SQL server.

Specify SQLBUFSZ on the first IODEV 19, 17, or 13 line. To define SQL and RCL IODEV threads on IODEV 49, see SQL server threads (IODEV=19) and RCL thread (IODEV=49).

Handling SQL statements greater than 32k bytes

When handling an SQL statement that is greater than 32K, you must consider that 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. For example, set SQLBUFSZ=60000 to run a DDL statement that is 52K.

You can also monitor the Model 204 SQLI since-last statistic to determine the size of the largest SQL input request. For more information about the SQLI statistic, see Interpreting the SQLI and SQLO statistics.

Handling BLOBs and CLOBs

When handling large objects (BLOBs and CLOBs), for performance reasons, you might want to adjust the value of the SQLBUFSZ parameter to accommodate the INSERT or UPDATE of very large CLOB/BLOB columns. The SQLBUFSZ is expanded dynamically for large objects, based on the data that is sent. However, it is recommended that you set the value of the SQLBUFSZ parameter to the length of the average size CLOB/BLOB that is inserted or updated.