The Process Threading Model

SAP Sybase IQ uses operating system kernel threads for best performance. By default, SAP Sybase IQ allocates the number of threads based on the number of CPUs on the system.

Lightweight processes are underlying threads of control that are supported by the kernel. The operating system decides which lightweight processes (LWPs) should run on which processor and when. It has no knowledge about what the user threads are, but does know if they are waiting or able to run.

The operating system kernel schedules LWPs onto CPU resources. It uses their scheduling classes and priorities. Each LWP is independently dispatched by the kernel, performs independent system calls, incurs independent page faults, and runs in parallel on a multiprocessor system.

A single, highly threaded process serves all SAP Sybase IQ users. The database server assigns varying numbers of kernel threads to each user connection, based on the type of processing being done by that connection, the total number of threads available, and the various option settings.

Insufficient Threads Error

If there are insufficient threads for a query, SAP Sybase IQ generates this error:

Not enough server threads available for this query

This condition may well be temporary. When some other query finishes, threads are made available and the query may succeed the next time. If the condition persists, you may need to restart the server and specify more SAP Sybase IQ threads. It is also possible that -iqmt is set too low for the number of connections.

SAP Sybase IQ Options for Managing Thread Usage

Use the server start-up option -iqmt to set the maximum number of threads. The default value is calculated from the number of connections and the number of CPUs and is usually adequate.
Use the server start-up option -iqtss to set the stack size of the internal execution threads. The default value is generally sufficient, but may be increased if complex queries return an error indicating that the depth of the stack exceeded this limit.
Use the SET OPTION MAX_IQ_THREADS_PER_CONNECTION command to set the maximum number of threads for a single user. The SET OPTION MAX_IQ_THREADS_PER_TEAM command sets the number of threads available to a team of threads, enabling you to constrain the number of threads (and thereby the amount of system resources) allocated to a single operation.
Use these options to control the amount of resources a particular operation consumes. For example, you can set this option before issuing an INSERT, LOAD, BACKUP, or RESTORE command.
Setting this option requires the SET ANY PUBLIC OPTION system privilege.