2028308 - Database Indexes to improve User Login performance

The following three queries were discovered to be performing poorly during the login procedure and bring up the main page.

Query 1:

SELECT C.DIRECTORY_ID
   FROM CUSTOMER C,
            CUSTOMER_DIRECTORY CD,
            BUSINESS B,
            BUSINESS_EMPLOYEE BE
        WHERE C.DIRECTORY_ID = BE.DIRECTORY_ID
            AND BE.BUSINESS_ID = B.BUSINESS_ID
            AND B.DIRECTORY_ID = CD.DIRECTORY_ID
            AND C.BANK_ID = ?
            AND C.USER_NAME = ?
            AND CD.CUST_ID = ?

Query 2:

SELECT C.*,
            CD.*,
            BE.*
   FROM CUSTOMER C,
            CUSTOMER_DIRECTORY CD,
            BUSINESS B,
            BUSINESS_EMPLOYEE BE
        WHERE C.DIRECTORY_ID = BE.DIRECTORY_ID
            AND BE.BUSINESS_ID = B.BUSINESS_ID
            AND B.DIRECTORY_ID = CD.DIRECTORY_ID
            AND USER_NAME = ?
            AND PASSWORD = ?
            AND C.BANK_ID = ?
            AND CUST_ID = ? 

Query 3:

SELECT C.*,
            CD.*,
            BE.*
   FROM CUSTOMER C,
            CUSTOMER_DIRECTORY CD,
            BUSINESS B,
            BUSINESS_EMPLOYEE BE
        WHERE C.DIRECTORY_ID = BE.DIRECTORY_ID
            AND BE.BUSINESS_ID = B.BUSINESS_ID
            AND B.DIRECTORY_ID = CD.DIRECTORY_ID
            AND USER_NAME = ?
            AND C.BANK_ID = ?
            AND CUST_ID = '?

Performance issues are more notable for larger numbers of Business and Bank users.

• DB2 9.7
• SAP Online Business Banking 8.3
• Sybase Corporate Online Banking 8.2
• Sybase Corporate Online Banking 7.0

OLB, BC, CB, 11802188, User Login, performance, corporate banking, business central, database, index, online banking , KBA , FS-OLB , SAP Online Banking , How To