SQLIQBSZ parameter

From m204wiki
Jump to navigation Jump to search

SQL intrnl query buff size

Summary

Default value
8176
Parameter type
User
Where set
On User 0's or on user's parameter line
Related products
All
Introduced
Model 204 V3.1

Description

SQLIQBSZ sets the size in bytes of a Model 204 SQL Engine internal buffer

This buffer is used for compilation and evaluation of SQL requests. It must be large enough to accommodate the greatest of the following quantities per request:

  • The total length of a row fetched from Model 204
  • The total length of the data values of Model 204 indexed fields that are referenced in the WHERE clause of an SQL query
  • The total data length of an insert or update query
  • The numerical product of:
    • The number of columns in your SQL table
    • The average length per column of the SQL catalog column description (that is, including names and attributes of the SQL column and Model 204 field)
    For example, the SQLIQBSZ default value typically accommodates a 250-column table whose average data length per column is 30 bytes.

If your SQLIQBSZ value is insufficient for your SQL data, you receive an SQL error code or Model 204 error message stating that your request is too long or your buffer is full.

The SQLIQBSZ minimum is 2032 and its maximum is 32752. If your SQLIQBSZ setting is below the minimum or above the maximum, Model 204 forces the setting to either the minimum or maximum value.

The SQLIQBSZ default is sufficient for most SQL applications.

Handling SQL statements greater than 32K bytes

When handling an SQL statement that is greater than 32K, you must consider that 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.