Improve SRC performance

The following changes to the Service Manager database can improve the performance of Web Service requests from Service Request Catalog. These steps should be completed by an experienced Service Manager administrator.

These changes remedy problems that occur when the Service Manager server cannot fully translate Service Manager queries to an SQL server when fields that appear in the query are mapped to large object (LOB) type fields. The changes prevent inefficient scans by the Service Manager server when it runs queries against tables that reference LOB type fields.

Create the ApprovalA1 table

To remap the current.pending.groups array from a CLOB/TEXT field to an Array table, create APPROVALA1 table. You should use the Dbdict utility, neither the System Definition utility nor the SQLMapping utility. To edit the dbdict for the Approval file, follow these steps:

1. In the Fields pane, scroll to and then double-click on the current.pending.groups array definition line.
2. Click the Edit field.
3. Set SQLTable to a1.
4. Click Next. You should be positioned on the dbdict entry for the current.pending.groups character field.
5. Set SQLTable to a1.
6. Change the SQL Type from CLOB to VARCHAR(120) and click OK.

7. Select the SQL Tables tab and add a new line with alias, name, and type of a1 APPROVALA1 oracle10.

8. Click OK. A pop-up dialog displays the DDL to create the new table.

   Note Do not modify the DDL.

9. Copy this DDL to the clipboard for future reference.
10. Click User Alters.

    Note Do not click SM Alters.

11. Click OK on the main dbdict window to update the dbdict.

    Note After you click User Alters, Service Manager displays a dialog warning that you must now alter the database by using the displayed DDL. However, Service Manager also tries to modify the database directly by using that DDL. If it succeeds, nothing further is required.

    The operation will succeed, provided the sqllogin account information in the Service Manager sm.ini file is for a database user with the rights to issue CREATE TABLE and CREATE INDEX commands. If the sqllogin account information in the Service Manager sm.ini file is for a database user who does not have these rights, the operation will fail, and an appropriately authorized user must manually run the DDL that you copied to the clipboard against the Service Manager database schema.

12. Verify that Service Manager succeeded in creating the new table and index by examining the Service Manager database in Oracle to confirm that the new APPROVALA1 table was created.
13. Investigate any SQL error messages that appear.
14. Use Oracle SQL Developer or another database management tool to verify that the APPROVALA1 table was created.

Remap the parent.tree field

The following task is to remap the parent.tree field in the capability file to an Array table. You should use the Dbdict utility, neither the System Definition utility nor the SQLMapping utility. To edit the capability file to an Array table, follow these steps:

1. Select the parent.tree type array.
2. Click the Edit field.
3. Set SQLTable to a1.
4. Click Next. You should be positioned on the parent.tree type character.
5. Change the SQL type to VARCHAR(50).
6. Change SQLTable to a1.
7. Click OK.
8. Go to SQLTables.
9. Add a1 CAPABILITYA1 oracle10.

10. Click OK. A pop-up dialog displays the DDL to create the new table.

    Note Do not modify the DDL

11. Copy this DDL to the clipboard for future reference.
12. Click User Alters.

    Note Do not click SM Alters.

13. Click OK on the main dbdict window to update the dbdict.

    Note After you click User Alters, Service Manager displays a dialog warning that you must now alter the database by using the displayed DDL. However, Service Manager also tries to modify the database directly by using that DDL. If it succeeds, nothing further is required.

    The operation will succeed, provided the sqllogin account information in the Service Manager sm.ini file is for a database user with the rights to issue CREATE TABLE and CREATE INDEX operations. If the sqllogin account information in the Service Manager sm.ini file is not for a database user who does not have these rights, the operation will fail, and an appropriately authorized user must manually run the DDL that you copied to the clipboard against the Service Manager database schema.

14. Verify that Service Manager succeeded in creating the new table and index by examining the Service Manager database in Oracle to see if the new CAPABILITYA1 table was created.
15. Investigte any SQL error messages that appear.
16. Use Oracle SQLDeveloper or another database management tool to verify that the CAPABILITYA1 table was created.

Remap the access.list Field

The following task is to remap the acces.list field in the svcCatalog file to an Array table. Use the Dbdict utility, not the System Definition utility or the SQLMapping utility. To edit the svcCatalog file to an Array table, follow these steps:

1. Select the access.list type array.
2. Click the edit field.
3. Set SQLTable to a2.
4. Click Next. You should be positioned on the access.list type character.
5. Change the SQL type to VARCHAR(50).
6. Change SQLTable to a2.
7. Click OK.
8. Go to SQLTables.
9. Add a2 SVCCATALOGA2 oracle10.

10. Click OK. A pop-up dialog displays the DDL to create the new table.

    Note Do not modify the DDL.

11. Copy this DDL to the clipboard for future reference.
12. Click User Alters.

    Note Do not click SM Alters.

13. Click OK on the main dbdict window to update the dbdict.

    Note After you click User Alters, Service Manager displays a dialog warning that you must now alter the database by using the displayed DDL. However, Service Manager also tries to modify the database directly by using that DDL. If it succeeds, nothing further is required.

    The operation will succeed, provided the sqllogin account information in the Service Manager sm.ini file is for a database user with the rights to issue CREATE TABLE and CREATE INDEX operations. If the sqllogin account information in the Service Manager sm.ini file is not for a database user who does not have these rights, the operation will fail, and an appropriately authorized user must manually run the DDL that you copied to the clipboard against the Service Manager database schema.

Remap the operators Field

The next task is to remap the operators field in the kmgroup file to an Array table. Use the Dbdict utility, not the System Definition utility or the SQLMapping utility. To edit the kmgroup file to an Array table, follow these steps:

1. Select the operators type array.
2. Click the edit field.
3. Set SQLTable to a1.
4. Click Next. You should be positioned on the operators type character.
5. Change the SQL type to VARCHAR(60).
6. Change SQLTable to a1.
7. Click OK.
8. Go to SQLTables.
9. Add a1 KMGROUPA1 oracle10.

10. Click OK. A pop-up dialog displays the DDL to create the new table.

    Note Do not modify the DDL.

11. Copy this DDL to the clipboard for future reference.
12. Click User Alters.

    Note Do not click SM Alters.

13. Click OK on the main dbdict window to update the dbdict.

    Note After you click User Alters, Service Manager displays a dialog warning that you must now alter the database by using the displayed DDL. However, Service Manager also tries to modify the database directly by using that DDL. If it succeeds, nothing further is required.

    The operation will succeed, provided the sqllogin account information in the Service Manager sm.ini file is for a database user with the rights to issue CREATE TABLE and CREATE INDEX operations. If the sqllogin account information in the Service Manager sm.ini file is not for a database user who does not have these rights, the operation will fail, and an appropriately authorized user must manually run the DDL that you copied to the clipboard against the Service Manager database schema.

14. Verify that Service Manager succeeded in creating the new table and index by examining the Service Manager database in Oracle to see if the new KMGROUPA1 table was created.
15. Investigte any SQL error messages that appear.
16. Use Oracle SQL Developer or another database management tool to verify that the KMGROUPA1 table was created.

Remap the members Field

Remap the members field in the cm3groups file to an Array table. To edit the cm3groups file to an Array table, follow these steps:

1. Select the members type array.
2. Click the editfield.
3. Set SQLTable to a1.
4. Click Next. You should be positioned on the members type character.
5. Change the SQL type to VARCHAR(60).
6. Change SQLTable to a1.
7. Click OK.
8. Go to SQLTables.
9. Add a1 CM3GROUPSA1 oracle10.

10. Click OK. A pop-up dialog displays the DDL to create the new table.

    Note Do not modify the DDL.

11. Copy this DDL to the clipboard for future reference.
12. Click User Alters.

    Note Do not click SM Alters.

13. Click OK on the main dbdict window to update the dbdict.

    Note After you click User Alters, Service Manager displays a dialog warning that you must now alter the database by using the displayed DDL. However, Service Manager also tries to modify the database directly by using that DDL. If it succeeds, nothing further is required.

    The operation will succeed, provided the sqllogin account information in the Service Manager sm.ini file is for a database user with the rights to issue CREATE TABLE and CREATE INDEX operations. If the sqllogin account information in the Service Manager sm.ini file is not for a database user who does not have these rights, the operation will fail, and an appropriately authorized user must manually run the DDL that you copied to the clipboard against the Service Manager database schema.

14. Verify that Service Manager succeeded in creating the new table and index by examining the Service Manager database in Oracle to see if the new CM3GROUPSA1 table was created.
15. Investigate any SQL error messages that appear.
16. Use Oracle SQL Developer or another database management tool to verify that the CM3GROUPSA1 table was created.

Additional performance tips

If the user receives a “max session exceed” error when running with large number of concurrent users, increase the Service Manager max threads per node. To do this, follow these steps:

1. Edit the sm.ini in the root Service Manager directory: $SM_install_dir$\server\RUN

2. Locate the Threadsperprocess parameter, and then increase the number. If the parameter is not present in the sm.ini file, add the parameter by using the following format:

   Threadsperprocess:{number}

3. Restart the Service Manager server.

For Oracle databases, you can improve the login and approval performance by adding an expression index. To do this, follow these steps:

1. Log in to Oracle Database under the sysop role.
2. Locate the APPROVALM1 table.

3. Create the following expression index on this table:

   FILE_NAME ||'-'|| COMPONENT ||'-'|| UNIQUE_KEY ||'-'|| NAME