Running an SQL script from your administration client

To modify connected databases or create objects in these databases, you can run SQL scripts directly from your administration client. This is useful because you need not change the system or console to execute SQL statements.

Before you begin

Make sure that the user running the administration client has sufficient privileges on the selected databases to execute the SQL code.

About this task

If you run an accelerated query from the SQL Script Editor of IBM Data Studio, the SQL Script Editor invokes the JDBC method Statement.setNumRows(), which causes the JDBC driver to convert the query into a read-only query (FOR READ ONLY). This happens even if the FOR READ ONLY clause is not part of the query statement. If the clause is not part of the statement, the query might end with an error message and reason code 4 (The query is not read-only). The error occurs with actual queries in a production environment and EXPLAIN-only queries alike. With EXPLAIN-only queries, the error does not matter. Actual queries, however, will not return results when the error occurs. Therefore, add the FOR READ ONLY clause explicitly to the SQL statement text if you submit actual queries from the SQL Script Editor.
Tip: Queries that return large result sets might benefit from asynchronous result set fetching. For more information, see Asynchronous result fetching.

Procedure

  1. From your administration client, connect to the Db2 subsystem or data sharing group the query is supposed to run against.
    For more information, see Connecting to a database server.
  2. Open the SQL editor:
       
    IBM Db2 Analytics Accelerator Studio
    1. On the toolbar of the Administration Explorer, click the downward-pointing arrow next to the New button.
    2. Select New SQL Script from the menu.
    IBM Unified Experience for z/OS
    1. Expand (click) the Run submenu on the sidebar on the sidebar.
    2. Select SQL.
    3. From the Connections drop-down list in the upper right of the window, select a Db2 subsystem or data sharing group.
    IBM Db2 Analytics Accelerator Administration Services N/A
  3. Type or paste your SQL statement in the text area:
    IBM Db2 Analytics Accelerator Studio Type or paste the SQL statement in the blank space of the Script<x>.sql workspace that opens on the upper right.
    Note: <x> stands for a counting number (integer). This means that your first SQL script is named Script1.sql, the second Script2.sql, and so on.
    IBM Unified Experience for z/OS Type or paste the SQL statement in the text area that opens on the New statements tab in front of the SQL window.
    IBM Db2 Analytics Accelerator Administration Services N/A
  4. Run your SQL script:
    IBM Db2 Analytics Accelerator Studio
    1. Click "Run SQL" button in SQL script window of IBM Db2 Analytics Accelerator Studio

      If no connection profile is selected in the Administration Explorer, the Select Connection Profilewindow comes up. If so, select the proper database connection profile (database server) in the Connections list.

    2. Click Finish.
    IBM Unified Experience for z/OS Click Run.
    IBM Db2 Analytics Accelerator Administration Services N/A

What to do next

You might want to save your SQL script for reuse:

IBM Db2 Analytics Accelerator Studio SQL scripts can be saved in your local workspace if you created an appropriate project before, for example a Data Development project. For information on how to create and work with Data Development projects, refer to Creating a data development project in the online help of IBM Data Studio.
IBM Unified Experience for z/OS You can save your SQL script to a comma-separated value (csv) file by using the export function:
  1. Click the SQL export icon icon on the very right, on top of the results table.
  2. In the Export in CSV format window, expand the Select CSV separators drop-down list.
  3. Select a suitable value separator.
  4. Click Export to save the csv file.
IBM Db2 Analytics Accelerator Administration Services N/A