Running SQL queries with the DB Query Client

A System Implementer (SI) can use the IBM DB Query Client to query a remote database securely on an on-demand basis for testing, development, and troubleshooting. The SI can run an SQL query, export a query, view last run queries, and export query result.

If the database contains any compressed data, the DB Query Client decompresses the compressed data and displays in the result or exports the data.

Before you begin

  • Use a browser that fully supports HTML5.
  • For Microsoft Internet Explorer, the DB Query Client is supported on Internet Explorer, version 11. Therefore, ensure to set up the emulation to 11 or any value higher than 10.
    To set up the emulation, complete the following steps:
    1. In Internet Explorer, press the F12 key, log in to the DB Query Client, and then open Developer tools.
    2. Click Emulation and select the document mode as 11 or higher than 10.
    3. Return to the DB Query Client. You can now use pagination for the query result.
    Do not close the Developer tools, if already open. Let it run in the background.

Procedure

To run SQL queries with the DB Query Client, complete the following steps:

  1. Start the DB Query Client by using the following URL:
    https://<hostname:port>/smcfs/yfshttpdbi/sterlingdbqueryclient.jsp
  2. Enter the User ID and Password. The user authentication is valid until the current session expires or until the current session is closed. If the current browser session expires, you can enter your login credentials and run queries on the same page.
  3. Type an SQL query in Enter SQL query, and click Run query.

    In the Number of rows field, specify the number of rows to be displayed per page. By default, the value is set to 100. If you enter a greater value, pagination is automatically enabled. You can specify a maximum value of 10000 rows to be displayed per page.

    • Order the query result by column whenever DISTINCT is used with the SELECT clause.
    • DISTINCT allows you to run the following query:
      select distinct <organization_code> from yfs_item where 1=1;
      Note: For the DISTINCT query with primary key column not selected, pagination is not supported. By default, the value is set to 100. However, if you want to view more records increase the page size. A maximum of 10000 records can be displayed.
    • Queries that contain aggregation functions such as UNION, INTERSECT, or WITH clauses are not supported by pagination. Such queries run in a non-paginated mode. However, if you want to view more records, increase the page size to a maximum limit of 10000 records.
    Note:
    • You cannot query tables that contain sensitive or irrelevant data or do not contain business data.
    • You cannot run queries that modify the tables.
    Some sample queries are as follows:
    
    select * from yfs_resource fetch first 10 rows only;
    select * from yfs_resource_permission fetch first 10 rows only;
    select * from yfs_user fetch first 10 rows only;
    • Query tables to order the result by non-primary column. For example,
      select * from yfs_user order by loginid fetch first 10 rows only;
    • Create joins by using subqueries. For example,
      
      select u.user_key, ug.USERGROUP_KEY, u.loginid, ug. USERGROUP_NAME from (SELECT * FROM
      omdb.YFS_USER)  as u RIGHT OUTER JOIN (SELECT * FROM omdb.YFS_USER_GROUP) as ug ON
      u.USERGROUP_KEY = ug.USERGROUP_KEY fetch first 10 rows only;
    • Using column aliases query. For example, select order_no as Docket from yfs_order_header;

    • Retrieve index information from tables by using describe statements. For example, describe table YFS_USER;. This query returns only the basic table schema details such as data type, null or not null, and not the index information. However, you can run describe indexes for table YFS_USER to retrieve the index details of the table.

    • Query tables from SYSCAT schema. You can query the following tables from the SYSCAT schema:
      • SCHEMATA
      • TABLES - While querying this table, it is recommended that you add a WHERE clause with tabschema='OMDB'
      • VIEWS - While querying this table, it is recommended that you add a WHERE clause with viewschema='OMDB'
      • COLUMNS - While querying this table, it is recommended that you add a WHERE clause with tabschema='OMDB'
      • INDEXES - While querying this table, it is recommended that you add a WHERE clause with tabschema='OMDB'
      • TABCONST - While querying this table, it is recommended that you add a WHERE clause with tabschema='OMDB'

      By default, in non-paginated mode only the first 100 records are displayed. If you want to display more records, in the DB Query Client, specify the total number of records to display.

    Alternative methods to run queries
    • Click Last run queries to view all the queries that you ran previously. The last 100 previously run queries are saved and 10 queries are displayed per page. You can run a query from the list.
    • In the Search for queries field, enter appropriate terms such as query keywords or table names to find your query, and click Run query. For example, search terms can be YFS_USER, SELECT. Alternatively, you can scroll the list and click Next or Previous to find your query.
    • Import and run a query.
    Note:
    • If you do not end a query with a semicolon, it is internally appended.
    • By default, pagination is enabled. If a problem occurs for certain queries, pagination is automatically disabled, and the query result is displayed based on the number of rows entered.
    • If there are momentary database connectivity issues, the tool retries three times to connect to the database. If unsuccessful, an appropriate message is displayed.
    • Query timeout is 2 minutes.
    Results:
    • To ensure optimal performance in the production environment, only the first 100 rows are displayed for queries with CLOB columns.
    • For SQL queries with CLOB columns, a maximum of 100 records are displayed and can be exported.
    • BLOB columns are shown in the output as --BLOB Data-- instead of showing a string value of the binary object on the result page.
    • The tool returns the exact SQL error codes so that the users can understand what is wrong with a query. For example, when you run select * from YFS_USER_DETAILS, the tool returns the following error message:
      Exception while executing query. Please check the query syntax/server logs.
      "OMDB.YFS_USER_DETAILS" is an undefined name.. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.69.49
  4. Optional: By default, the Export numbers as text (Only for viewing in Microsoft Excel) checkbox is selected. To export your query result to CSV file, clear the checkbox and then click Export. The query results are exported to a .csv. For example, queryResult--2020-8-5-13-19-19.csv.

    In the CSV file, long numbers are displayed in the exponential format. For example, 12345678901234567890 is represented as 1.23457E+19.

    To export your query results to a read-only CSV file, select the Export numbers as text (Only for viewing in Microsoft Excel) checkbox. For example, queryResult--2020-8-5-13-19-19_view-only-on-ms-excel.csv.

    The CSV file can be opened only in Microsoft Excel. In the exported CSV file, an apostrophe (') is prefixed for numbers that are greater than 11 digits so that the whole numbers are displayed as text in Microsoft Excel. However, numbers that are less than 11 digits are displayed as integers and do not contain apostrophe. For numbers that contain leading zeros, Microsoft Excel truncates the zeros and displays only numbers. For example, "0001" is displayed as "1".

What to do next

Open the read-only CSV file in Microsoft Excel by completing the following steps:
  1. Open Microsoft Excel.
  2. From the Data menu, click From Text > Import.
  3. Choose the Data Type as Delimited.
  4. Select Unicode (UTF-8) from the File origin list.
  5. Choose the delimiter as Comma.
  6. Click Finish.