SAP Sybase IQ Stops Processing or Stops Responding

You can detect the cause of server unresponsiveness by looking in the SAP Sybase IQ message file.

Possible Causes

The most common causes of server unresponsiveness include:

Insufficient disk space
Insufficient room in main or temp buffer cache

Action

If your server seems to be prone to unresponsiveness, either while processing or during shutdown, use the start_iq command line option -z and the SAP Sybase IQ database option QUERY_PLAN = 'ON' to log useful information in the SAP Sybase IQ message (.iqmsg) and server log (.srvlog) files.

In addition to logging this information, there are other steps you can take to determine the cause of the problem:

Check both the SAP Sybase IQ message file and the server log file for You have run out of space... messages. If you have run out of IQ main store or IQ temporary store, add the appropriate dbspace with the CREATE DBSPACE command.

Setting the database options MAIN_RESERVED_DBSPACE_MB and TEMP_RESERVED_DB_SPACE_MB to large enough values to handle running out of space during a DDL COMMIT or CHECKPOINT is also important. A few hundred MB should be enough, but you can set these options higher for a large database.
Determine if the SAP Sybase IQ server process (iqsrv16) is consuming CPU cycles by monitoring the CPU usage for a few minutes at the operating system level. Record this information. If the CPU usage changes, then the SAP Sybase IQ server process should be processing normally.

If the SAP Sybase IQ server CPU usage is normal, you can examine what the server is doing, that is, what statement the server is currently executing.

If there are no out of space indications, use Interactive SQL on a new or existing connection to gather the following information, in the specified order.

Information to Gather for Server Unresponsiveness
Command	Informational purpose
SELECT db_name()	Database name
CHECKPOINT	Checkpoint can succeed
sa_conn_properties ># sa_conn_properties.out	Connection information
sa_conn_info ># sa_conn_info.out	Connection information
sa_db_properties ># sa_db_properties.out	Database property information
sa_eng_properties ># sa_eng_properties.out	Server property information
sp_iqstatus ># sp_iqstatus.out	Database status information
sp_iqconnection ># sp_iqconnection.out	Connection information
sp_iqtransaction ># sp_iqtransaction.out	Transaction information

If you cannot resolve the issue, contact SAP Sybase Technical Support for assistance. They can use the information you have just gathered to help diagnose the problem.

When the server is unresponsive, generate a stack trace for each SAP Sybase IQ thread by creating a file named DumpAllThreads or dumpallthreads in the $IQDIR16/logfiles directory (the %ALLUSERSPROFILE\%\SybaseIQ\logfiles folder on Windows 64 platforms, C:\ProgramData\SybaseIQ\logfiles for Vista 64).

Starting SAP Sybase IQ as recommended, using the Program Manager or start_iq command, sets the IQDIR16 variable automatically. If the IQDIR16 variable is not set, create the DumpAllThreads file in the directory in which iqsrv16 was started.

The SAP Sybase IQ server detects the presence of the DumpAllThreads file and writes a stack trace for each IQ thread in the stack trace file stktrc-YYYYMMDD-HHNNSS_#.iq. After the stack traces are written to the stack trace file, the DumpAllThreads file is deleted.

This stack trace information can be used by SAP Sybase Technical Support to help diagnose the problem.
If you can connect to the database, run the IQ UTILITIES buffer cache monitor on the main and temp (private) buffer caches for ten minutes with a ten-second interval:
1. Connect to the database or use the existing connection.
2. CREATE TABLE #dummy_monitor(c1 INT);
3. IQ UTILITIES MAIN INTO #dummy_monitor START MONITOR '-append -debug -interval 10 -file_suffix iqdbgmon';
4. IQ UTILITIES PRIVATE INTO #dummy_monitor START MONITOR '-append -debug -interval 10 -file_suffix iqdbgmon';
  
  Let the process run for 10 minutes, then stop the buffer cache monitor:
5. IQ UTILITIES MAIN INTO #dummy_monitor STOP MONITOR;
6. IQ UTILITIES PRIVATE INTO #dummy_monitor STOP MONITOR;
Check near the end of the SAP Sybase IQ message file for the message Resource count 0, which may be followed by an Open Cursor message. These messages indicate a resource depletion, which can cause a deadlock. The immediate solution is to reduce the number of active connections using Ctrl+C or the DROP CONNECTION command.
The long-term solution for avoiding deadlocks due to resource depletion is one or a combination of:
- Restricting the number of users on the server by reducing the value of the -gm server startup option
- Adding another secondary server to a multiplex
- Increasing the processing capacity of the hardware by adding CPUs