Querying the database

With the DB Query Client, System Implementers (SI), Administrators, and IBM® Support personnel can query the remote database easily and securely on an on-demand basis for purposes such as testing, development, and troubleshooting.
The System Implementers (SI) and Administrators of IBM Sterling® Order Management System applications can examine the data that is populated in the database to troubleshoot the problems during the application development and testing phases and also after the applications are available to the user. In certain scenarios, they might want to test the queries that are run by the application APIs to ascertain whether the application works as expected or not. Similarly, the IBM Support personnel who look into the issues that are reported by SI might need database access to troubleshoot the issues effectively.
Remember: The DB Query Client is used for troubleshooting or development purpose only. It must not be used for real-time data reporting or any other requirement, which requires querying huge amounts of data.

In Sterling™ Order Management System, users from your site cannot directly access the database. The database would be in a remote location and available on a secure port with SSL enabled. The DevOps team have to set up read-only database users to access the database and share the information with SI. Moreover, it is not easy to establish a connection by using third-party database browsing tools, which involve network settings such as jump-host . These settings must be done manually by the DevOps team for each environment that SI requests. Additionally, because none of these configurations are portable, Ops team members must redo the specifications each time the database is upgraded to a newer version.

You can use the data extract service to retrieve data from your production environment backup database. For more information, see Data extract overview.

Databases to run the DB Query Client

  • The DB Query Client is run against the production backup database.
  • For higher environments (preproduction and production), the DB Query Client connects to the backup HADR read-only database instead of the primary database for querying tables. This helps in reducing the load on the primary database and thus ensures stability of the application.
  • If your primary database is unavailable, and the secondary database is used as the primary database, the DB Query Client cannot run. This is to prevent the impact on performance.
  • There may be a significant replay gap between primary DB and backup HADR read-only DB during database maintenance windows or when major reorgs are happening in the db. If this replay gap is very high, during such time, queries would be routed from backup DB to the primary db. Since this can impact performance of primary DB, it is advised that complicated time consuming queries are avoided during this period.
  • If the replay gap is not very high but moderate, queries would still happen on the backup HADR read-only DB. There is a chance that the output may be slightly stale, so it advised to use the DB query tool after some time.
  • For lower environments (Development, QA, Master-Configuration), the DB Query Client connects to the primary database for querying tables.
  • This client is designed for a single schema installation of Sterling Order Management System only.

Setting up user access and permission

The DB Query Client is permission-controlled. To use this utility, you must be a user in Sterling Order Management System and must belong to a user group that has permission for “Database Select Query Servlet”. By default, users of the system administrator user group have access to the application. To define users and user groups, see Security.
To assign the resource permission to a user group, complete the following steps:
  1. Launch Applications Manager and from the Applications menu, select Application Platform.
  2. From the tree in the side pane, choose Security > Groups and locate the user group for which you want to assign the resource permission.
  3. Double-click the user group and click the Permissions tab.
  4. Click Permissions for the Cross Application module and expand Applications > Application Console > Override Default Functionality Resources.
  5. Right-click Database Select Query Servlet and select Grant Permission.
  6. Click the Save icon.

To enable the resource permission for a user group, see Administering User Group Permissions.