Improving performance after deploying database services artifacts

After the deployment of data services artifacts, several indices are created that cause DB2® to choose poor access plans. You must perform some steps immediately after the deployment and before you start any of the data-services component servers to ensure that good access plans are created.

One symptom of poor access plans is that data stops appearing in the Runtime and Historical databases because of very long queries that perform Target Life Cycle component duties. Normally this occurrence is also associated with a rise in CPU allocation by the DB2 processes.

Complete the following steps to solve this problem:

For the Runtime database

  1. Connect to the Runtime database using the query tool of your choice.
    1. If running locally with the deployment user

      db2 connect to <Runtime_Database>

    2. If running remotely

      db2 connect to <Runtime_Database> user <User_Name>

  2. Create a file named createCCDindexesDrops.sql containing the following text:

    SELECT 'DROP INDEX ' || CREATOR || '.' || NAME || ';'

    FROM

    SYSIBM.SYSINDEXES, WBIRMADM.RMMETADATA RM

    WHERE

    REPLACE(LTRIM(TBCREATOR) || '.' || LTRIM(TBNAME),' ','') = RM.TGT_RM_APP_STG_TAB_NAME

    AND CREATOR='APP'

    AND NAME LIKE 'CCD%'

    ;

  3. Run the SQL statement just created, and redirect the output to a file.

    db2 -txf createCCDindexesDrops.sql > dropRuntimeCCDIndexes.sql

  4. Examine the file. You should see statements like:

    DROP INDEX APP .CCDIX_CCD_45;

    DROP INDEX APP .CCDIX_CCD_6;

    DROP INDEX APP .CCDIX_CCD_7;

  5. Run the generated file as follows:

    db2 -tvf dropRuntimeCCDIndexes

  6. Create a file named createrebindDSRuntimeStoredProcedures.sql and add the following contents:

    SELECT ' CALL SYSPROC.REBIND_ROUTINE_PACKAGE(''P'', ''' || TGT_RM_SPETL_NAME || ''', ''ANY'');'

    FROM

    WBIRMADM.RMMETADATA

    WHERE

    Service_Name='State to Runtime';

    SELECT ' CALL SYSPROC.REBIND_ROUTINE_PACKAGE(''P'', ''' || TGT_RM_APP_PRUNE_SP_NAME || ''', ''ANY'');'

    FROM

    WBIRMADM.RMMETADATA

    WHERE

    Service_Name='State to Runtime';

  7. Run the file.

    db2 -txf createrebindDSRuntimeStoredProcedures.sql > rebindDSRuntimeStoredProcedures.sql

  8. Examine the generated file. You should see the following:

    CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'WBIRMADM.WBIRMSP_10', 'ANY');

    CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'WBIRMADM.WBIRMSP_14', 'ANY');

    CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'WBIRMADM.WBIRMSP_47', 'ANY');

  9. Run the generated file to rebind the DS stored procedures once the indexes are removed.

    db2 -tvf rebindDSRuntimeStoredProcedures.sql

For the Historical database

  1. Connect to the Runtime database using the query tool of your choice.
    1. If running locally with the deployment user

      db2 connect to <Historical_Database>

    2. If running remotely

      db2 connect to <Historical_Database> user <User_Name>

  2. Create a file named createCCDindexesDrops.sql containing the following text. You can reuse the file created for the Runtime database:

    SELECT 'DROP INDEX ' || CREATOR || '.' || NAME || ';'

    FROM

    SYSIBM.SYSINDEXES, WBIRMADM.RMMETADATA RM

    WHERE

    REPLACE(LTRIM(TBCREATOR) || '.' || LTRIM(TBNAME),' ','') = RM.TGT_RM_APP_STG_TAB_NAME

    AND CREATOR='APP'

    AND NAME LIKE 'CCD%'

    ;

  3. Run the SQL statement just created, and redirect the output to a file.

    db2 -txf createCCDindexesDrops.sql > dropRuntimeCCDIndexes.sql

  4. Examine the file. You should see statements like:

    DROP INDEX APP .CCDIX_CCD_45;

    DROP INDEX APP .CCDIX_CCD_6;

    DROP INDEX APP .CCDIX_CCD_7;

  5. Run the generated file as follows:

    db2 -tvf dropRuntimeCCDIndexes

  6. Create a file named createrebindDSRuntimeStoredProcedures.sql, and add the following contents:

    SELECT ' CALL SYSPROC.REBIND_ROUTINE_PACKAGE(''P'', ''' || TGT_RM_SPETL_NAME || ''', ''ANY'');'

    FROM

    WBIRMADM.RMMETADATA

    WHERE

    Service_Name='Runtime to Historical';

    SELECT ' CALL SYSPROC.REBIND_ROUTINE_PACKAGE(''P'', ''' || TGT_RM_APP_PRUNE_SP_NAME || ''', ''ANY'');'

    FROM

    WBIRMADM.RMMETADATA

    WHERE

    Service_Name='Runtime to Historical';

  7. Run the file.

    db2 -txf createrebindDSRuntimeStoredProcedures.sql > rebindDSHistoryStoredProcedures.sql

  8. Examine the generated file. You should see the following:

    CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'WBIRMADM.WBIRMSP_10', 'ANY');

    CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'WBIRMADM.WBIRMSP_14', 'ANY');

    CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P', 'WBIRMADM.WBIRMSP_47', 'ANY');

  9. Run the generated file to rebind the DS stored procedures once the indexes are removed.

    db2 -tvf rebindDSHistoryStoredProcedures.sql


Copyright IBM Corporation 2005, 2006. All Rights Reserved.