IBM Z and LinuxONE - Languages - Group home

Running SQLs with Db2 Developer Extension in Visual Studio Code

  

By Katherine Soohoo, Kendrick Ren

With IBM Db2 for z/OS Developer Extension in VS Code, users can create Db2 connection profiles and run SQL against a Db2 system. The SQL statements can exist locally in a file or on z/OS with the help of the Zowe Explorer extension.

The following instructions describe how to run SQL statements on your local machine or on z/OS.

Running SQL on your local machine

The Db2 Developer Extension is required to run SQL statements on your local machine.

1. Install and setup Db2 Developer Extension

  1. Install Db2 Developer Extension in VS Code.
  2. Complete the prerequisites for installing Db2 Developer Extension.
  3. In VS Code's user and workplace settings, specify the extension's settings such as the path to the db2jcc_license_cisuz.jar license file, path to the Java installation directory, and the port number used by the Db2 SQL Service.

setup-props

2. Add a Db2 connection

  1. Go to the Db2 Developer Extension view by clicking on the icon in the Activity Bar.
  2. In the CONNECTIONS view, click the plus sign (+) to add a new connection.
  3. Fill in the connection information and click the Finish button to save the connection profile.
  4. Upon finishing, an attempt to connect to the profile will be made. If prompted to access keychain, enter the password to allow access.

add-connection

3. Associate a Db2 connection to a SQL file

This step is required if there is more than one connection profile defined.

  1. Open a SQL file in VS Code.
  2. In the Status Bar, click on No Connection and select a connection to associate with the currently opened SQL file.

associate-connection

4. Run the SQL

  1. In the editor with the SQL file, right-click to open the context menu and choose Run Options.

  2. Specify custom run options, then click the Save button when finished.

  3. In the editor with the SQL file, right-click to open the context menu and choose Run All.

    Note: To run a subset of SQL statements within the file, highlight the SQL statements you wish to run then right-click and choose Run Selected.

  4. If host variables were present in the SQL statements, an additional tab will be revealed to specify the host variables values. After filling in the values, click the Finish button.

  5. After the SQL has finished running, the results of the execution will be revealed in another tab. The results of the execution will also be saved in the QUERY HISTORY view of the Db2 Developer Extension view.

sql-execution

Running SQL on z/OS

The features provided by Db2 Developer Extension can also work together with Zowe Explorer extension on the SQL statements which are stored in the data sets or the files in Unix System Service (USS). You can run the SQL statements in the data sets or the USS files in the same way. Zowe Explorer extension is used to access the data sets and the USS files on z/OS.

  1. Install Zowe Explorer in VS Code.

  2. Go to the Zowe view by clicking on the icon in the Activity Bar.

  3. Add a profile under DATA SETS or USS. Refer to the Zowe Explorer Profiles docs for more information.

  4. Open the sequential or PDS/PDSE data sets or USS files containing the SQL statements.

    Note: The lowest level qualifier of the data sets should be ending with "SQL". If you are using PDS or PDSE data sets, customize files.associations in settings.json with the content below.

    "files.associations": {
    "*.SQL(*).*": "sql"
}

  5. Follow the steps in the previous section to create a Db2 connection profile and run SQL with the Db2 Developer Extension.

zowe-explorer-settings