The Process Threading Model

Sybase IQ uses operating system kernel threads for best performance. By default, 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 Sybase IQ users. Sybase IQ 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, 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 Sybase IQ threads. It is also possible that -iqmt is set too low for the number of connections.

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 sets the number of threads available to a team of threads.

Use these options to control the amount of resources a particular operation consumes. For example, the DBA can set this option before issuing an INSERT, LOAD, BACKUP, or RESTORE command.