Troubleshooting: Common database errors

The following list contains the most common Service Manager errors encountered. For information on solutions to these database errors, see the related topics.

• Error message: Cannot Find SQL Server, Error Connecting to servername.
• Error message: Corresponding join not defined.
• Error message: Error opening the Core.ir file in read mode.
• Power outages and hardware failure.
• Slow query execution.

Messages: Cannot Find SQL Server, Error Connecting to servername

Message: Cannot Find SQL Server and Error Connecting to servername

These generic connection failure messages indicate that the Run-time Environment (RTE) attempted to log in to Service Manager and either failed or received no reply. See the following list for common causes and possible solutions.

• The Service Manager server is not running.
  Ensure that the server is running and then attempt to connect using the Service Manager client.
• Your login information is incorrect.
  On failure, Service Manager prompts you to re-enter your login information. Ensure that you enter the correct case-sensitive login and password. For future login attempts, you can optionally modify program options to automatically set them to the correct information.
• The number of valid Service Manager licenses are already in use.
  Service Manager accepts a set number of concurrent users, depending on your license agreement. If all available licenses are in use, Service Manager rejects your login. Contact your Service Manager administrator to confirm the number of licenses available.

Message: Corresponding Join Not Defined

Message: Corresponding Join Not Defined

This message indicates that the report is attempting to join multiple tables that have not been properly joined in the database.

When designing reports, linking tables using the application is not sufficient to create a join. You must also configure Service Manager with the equivalent values.

To join the tables:

1. Inspect the joined tables in your application. Arrows should connect to only the fields that you want to join between the tables.
2. Make an unload of both tables before making modifications so that you can return to the original configuration if needed.

3. Open Service Manager and compare the settings in the JOINDEF and ERDDEF tables to this linking scheme. You may need assistance from your administrator to confirm this task. Ensure that the following criteria apply:

   • The joins match exactly.
   • The join in Service Manager is unique, no other join connects the same tables in a different manner.
   • Stop, and then restart, the Service Manager server after creating the join.

Messages: Error opening the Core.ir file in read mode

Error message text: Error opening the Core.ir file in read mode

To correct the error and regenerate the IR indexes, follow these steps:

1. Click Tailoring > Database Manager.
2. Select Administration mode.
3. Type core in the Table Name field, and then click Search.
4. Select a record to edit.

5. Open the More Actions menu and choose Regen.

   Note This option does not appear for files that contain no data records.

6. You are prompted to confirm this regen action.

   • Click OK to confirm this regen and erase all records in this file.
   • Click Cancel to cancel the regen.
   • Click Schedule (clock) to schedule the regen to perform at a designated time.

   Note If you clicked OK, the regen performs immediately in the foreground. When completed, Service Manager displays a message confirming the time/date of the file regen and the removal of all records.

   Note The asterisk (*) at the beginning of a message indicates there are additional messages related to this operation.

7. View all messages.
8. Read the messages and identify any errors that occurred during the operation.

Troubleshooting: Database debug

You can add the debugdbquery parameter to the sm.ini file to allow the server to run the database debug utility. You can then use Microsoft Excel to sort the query results.

Examples:

• To show all database access, use the following syntax: debugdbquery:999.
• To show all queries that exceed n number of seconds, use the following syntax: debugdbquery:n/.

Troubleshooting: Interpreting debugdbquery output

Output from the Database debug query parameter, debugdbquery, writes to the sm.log file. The messages written to the sm.log file contain several fields, each separated by a caret (^) character. The sm.log file is located in the following directory:

..\..\Micro Focus\ Service Manager\Server\logs

Sample debugdbquery 999 output to the sm.log file:

223 02/05/2007 17:39:51 DBFIND^F^scmessage(Oracle)^1^0.000000^F^0^0.000000^“syslanguage=“en”
and class=“us” and message.id=“1””^ ^0.000000^0.000000 ( [ 0] apm.get.inbox.by.name start )
223 02/05/2007 17:39:51 DBQUERY^F^probsummary(Oracle)^18^1.000000^F^0^0.020000^“hot.tic#true”^
{“category”}^0.000000^0.000000 ( [ 0] sc.manage select )

The following table describes the output fields for the database debug query (debugdbquery) parameter.

Field	Description
who	DBFIND or DBQUERY
where	F = foreground or B = background
file	The file name followed by database type: Oracle, SQL server or LDAP, or JOIN. If you add a letter I suffix, then the file name is case-insensitive.
key	The number of the selected key. If an asterisk character (*) follows the key number, Service Manager selected that key based on sort requirements, not query requirements. The system first uses a key that satisfies the sort criteria because a physical sort of the data is not required.
weight	The calculated weight for the key Service Manager selects for that specific query. For additional information, see the Key selection algorithms Help topic.
keytype	P = Partially keyed, I = IR expert search
record count	The number of records that satisfy the query. The system adds the DBQUERY entry to the log after processing the select panel and then returns the first 128 records that satisfy that query.
seconds till result came back	The amount of time required to satisfy the query.
query	The actual query from the user.
sortfields	The sort order in which the records are requested.
extracttime	The time required to read data records and extract the key values needed for sorting. This is only necessary if a key satisfying the requested sort order does not exist.
sorttime	The time required to sort all data records matching the query. This is only necessary if a key satisfying the requested sort order does not exist.

Troubleshooting: Power outages and hardware failure

Ensure that procedures are in place that provide continuous power in the event of a power outage or hardware failure. In the event of a power outage or hardware failure, transactions written to the log file are compromised and cannot be retrieved. If a backup process is running when the outage occurs, use your RDBMS tools to check the validity of the backup.

Troubleshooting: Slow execution of queries

Slow query execution time is usually related to hardware and network limitations, or report design. After eliminating hardware or network limitations as a source of the slow response time, you can apply the following suggested tips to your report designs to speed execution:

• Limit the number of records kept in a database table, such as avoid saving several years of incident records in a single database table. The more records that are kept in a file, the larger the index is. The larger the index is, the slower queries run.
• Limit sorting layers. If the raw SQL contains more than two ORDER BY statements, the processing time increases. Each ORDER BY statement significantly increases execution time.
• Use joins instead of subreports. Although subreports are universal and allow greater portability over different servers, they execute SQL queries against the database one time for every record. For example, a report with 100 records with a subreport in the Details section executes 101 SQL statements. A joined report of equivalent size executes only once.
• Use indexes where possible. Typically, the Unique key of any table is already indexed; however, in a join, placing a key on the field used to join the two tables dramatically increases the speed to execute. Add indexes only with the guidance of your Service Manager administrator. Adding too many indexes to a Service Manager table can affect performance.

Troubleshooting: lister

Use the following procedure to troubleshoot lister:

1. Verify lister status and configuration.
2. Regenerate obsolete lists.
3. Rebuild every list in the system.

Troubleshooting: ODBC error when running Crystal Reports

Crystal Reports cannot sort on fields that contain SQL reserved words such as group. If you receive an ODBC error when running a sort operation in Crystal Reports, then one of the fields in your query contains a SQL reserved word. Review the SQL query and identify any fields that contain a SQL reserved word. For example, you will receive an error if you attempt to sort on the field cm3rm1.group because it contains the SQL reserved word group.

To fix the error, you can create an alias field in the database dictionary to remove the SQL reserved word. For example, you can create the alias field cm3rm1.group_alias that is an alias of the original field cm3rm1.group.