You can detect the cause of server unresponsiveness by looking in the
SAP Sybase IQ message
file.
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 UnresponsivenessCommand |
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:
- Connect to the database or use the existing
connection.
- CREATE TABLE #dummy_monitor(c1
INT);
- IQ UTILITIES MAIN INTO
#dummy_monitor START MONITOR '-append -debug -interval 10
-file_suffix iqdbgmon';
- 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:
- IQ UTILITIES MAIN INTO
#dummy_monitor STOP MONITOR;
- 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