IBM Data Server Drivers (Db2 Connect Client)

VS Code extension for Db2 Connect

By Sai Ram Akhil Ravuri posted Wed November 11, 2020 05:06 AM

  

Db2 Connect Extension for VS Code

** This usage document is based on Db2 Connect Extension Version 2.3.0**

Prerequisites: -

  • Visual studio code - Download
  • If clidriver is already present in your system, please set IBM_DB_HOME = /path/to/clidriver to avoid the download of clidriver.

 

How to install this VS Code extension:-

  • Click on the Extension icon in the activity bar.
  • Type Db2 Connect and click on the install button.
  • Once the extension is installed you will see the IBM BEE icon in the activity bar.
  • If you face any issues while installing, please check whether your system can access the Github page or not. If not please give permission to access the Github page.
  • In case if you still have issues, please have a look at the log as mentioned below.

How to see the logs: -

  • Please click on the terminal and select a new terminal.
  • In the terminal, go to the Output tab, and from the dropdown list select Db2 Connect.

 

How to install a license for Db2 I Series and Db2 z/OS:-

  • To add a license, click on the Db2 Connect(BEE) icon and click on Add license icon as shown in the below image.
  • When you click for the first time on the license button after installation you can see an information message at the right side bottom saying that building the native modules.
  • Once the setup is done you will see the below message.
  • Now select the license file and click on the install button.
  • After successful installation user can see the below message.


How to Connect to Db2 Server: -

  • To connect, click on the Db2 Connect(BEE) icon and click the Add connection button(+) at the top.
  • When you click for the first time on the Add button after installation you can see an information message at the right side bottom saying that building the native modules.
  • Once the setup is done you will see the below message.
  • For z/OS and iSeries Connectivity: -
    • For connectivity against Db2 for LUW using the Db2 Connect VS Code extension, a Db2 Connect license is not required. However, if you want to use Db2 Connect VS Code extension against Db2 for z/OS or Db2 for i(AS400) Servers, you must have Db2 Connect 11.5 license if the Db2 Server is not already activated with an unlimited Db2 Connect license. Db2 Connect license can be enabled either on the server using Db2 Connect activate utility or on the client using the client-side license file. If you have client-side license file, copy license file to this folder `$USERPROFILE/.vscode/extensions/db2connect/node_modules/ibm_db/installer/clidriver/license` .
    • In absence of a valid Db2 Connect license file, `Db2 Connect` will throw SQL1598N error. Client-side license file name should be `db2con*.lic`.
    • To know more about license and purchasing cost, please contact IBM Customer Support.
  • Please click the add button(+) again after the success message.
  • Choose one of these options.
  • If the user selects the first option, an input box where a user can enter the connection string will be prompted.
  • If the user selects the second option, a Connection Parameters window will be shown.
  • For the SSL Server Certificate please enter the full path (Ex:  C:\WORK_Go\sslCertificate.crt).
  • Enter details such as Database, Hostname, port, user id and password.
  • You can also add ODBC properties in the optional tab and click on "Connect" for making a connection.
  • If the user chooses the third option, a list of profiles created already will be displayed. (To see profiles users should create a profile which is shown in the next step).
  • Users can choose any profile and make a connection.

How to Create Profiles: -

Db2: Manage Connection Profiles

  • Please click on the Manage Connection Profiles icon to create, edit or delete the profiles.
  • Please choose one of the options:-
  • Users can create a connection profile by entering the following details
    • Profile Name: Enter the profile name.
    • Database: Enter the database name.
    • Hostname: The Db2 Server/IP address that the user wants to connect to.
    • Port: Enter the Port number.
    • Userid: Enter the name of a user with access to a Db2 server.
    • Password: Enter the password for the specified user.
  • Click on “Save and Close” and Profile will be saved.
  • User can Edit Connection profile by selecting a profile that user want to edit:
    • Database: Enter the database name.
    • Hostname: The Db2 Server that the user wants to connect to
    • Port: Enter the Port number
    • Userid: Enter the name of a user with access to a Db2 server.
  • Click on “Update and Close” and the profile will be saved.
  • Users can delete the connection profile by selecting a profile that the user wants to delete.

Intellisense:-
  • Before Database Connection (Shows only Db2 keywords).
  • After Database Connection (Shows tables, stored procedures, functions... present in the connected database).
  • The below image shows how functions are displayed using IntelliSense.
  • The below image shows how data types are displayed when creating a table.
  • Objects that can be created are only shown after CREATE keyword.
 

     

    Tree View: -

    • After the successful connection, a tree view is created in the Db2 Connect view.
    • Database: -
      • Expand the database to see the list of schemas.
      • Right-click on the database to create a new schema, table, alias, view, function, stored procedure, trigger and indexes.
      • Alternatively, click on Add(+)s button to create a schema.
    • Schema: -
      • Expand schema list tables, views, alias, stored procedures, functions, triggers and indexes.
        • Right-click on the schema to refresh schema and create a new table, view, alias, stored procedure, function, trigger and index in that schema.
      • Tables: -
        • Expand the table to list the column names with datatype and length.
        • The primary key and Foreign key are identified using the column icon.
        • Indexes on a column are shown in the tooltip.
        • Type of Index like Non-Unique Index (NUI) and Unique Index (UI) is shown in the column label.
        • Delete a table using a trash icon as shown in the above image.
        • Create a new table.
        • Describe table.
        • Insert row to the table.
        • Select data from the table.

      • Views: -
        • Delete view using a trash icon.
        • Expand view to get the column names with the datatype.
        • Get Body of a view in a file if you want to modify you can modify the view and submit it using execute single sql option (Right-click on the file).
        • Select data from the view.
        • Get Body will open a new sql file with a view body.

      • Alias: -
        • Expand alias to get the base table name.
        • Delete view using a trash icon.
        • Select data from the alias.

      • Stored Procedures: -
        • Expand stored procedure to get the column names with parameter type and datatype.
        • Delete the stored procedure using the trash icon.
        • Get Body to get the body of a stored procedure in a file if you want to modify you can modify the stored procedure and submit it using the execute single sql option (Right-click on the file).
        • Execute the stored procedure.
        • Users need to enter the input values for IN and INOUT variables in the stored procedure.
        • The result is displayed in the below format.

      • Functions: -
        • Expand the functions folder to list the functions.
        • Delete the function using the trash icon.
        • Get Body to get the body of a function stored at the server in a file if you want to modify you can modify the function and submit it using the execute single sql option (Right-click on the file).

       

      • Triggers: -
        • Get column names for the trigger.
        • Delete the trigger using the trash icon.
        • Get Body to get the body of a trigger in a file if you want to modify you can modify the trigger and submit it using the execute single sql option (Right-click on the file).

       

      • Indexes: -
        • Get the column names for indexes.
        • Delete the index using the trash icon.
        • This files can be saved as .sql,.spsql,.udfsql,.db2,.spdb2 and .udfdb2 formats.

      How to Execute Query:-

      Db2: Execute Query(Go to Command Palette from View Menu or by pressing Ctrl + Shift + P and Type Db2)

      • when the user selects Db2: Execute Query user will be prompted to enter the query.
      • Enter the Query and press ‘Enter’ to confirm it.
      • The result will be displayed in a grid view.

       

      Db2: Execute Non-Query(Go to Command Palette from View Menu or by pressing Ctrl + Shift + P and Type Db2)

      • when the user selects Db2: Execute Non-Query user will be prompted to enter the sql.
      • Enter the sql and press ‘Enter’ to confirm it.

       

      Db2: Execute SQL File

      •  Click on the execute file icon.
      • Select a file and click execute.
      • Multiple Queries in the file will be executed and results will be displayed in the view tab.

         

         

        How to execute sql in an opened file: -

        • Select the query which you want to execute. Right-click and select the Execute Single Query option.
        • If your selection has multiple queries, please select the Execute Multiple Query option.
        • If you want to execute the whole file just right-click and select Execute Current SQL File.

        How to View History: -

                

        • Click on the view command history icon in the tree.
        • When the user selects Db2: View Command History untitled file will be opened where a list of already executed commands are displayed with time.
        • Users can select the sql, right-click and select Execute Current Query to execute.
        • While closing the untiled file click on don’t save.

        How to Disconnect from the current Db2Server: -

        • Click on the cross icon to disconnect.

        (OR)

         

        Db2: Disconnect

        •  Select View > Command Palette > Db2: Disconnect

         

        How to Export Data (Result set): -

        • Click on the export icon at the top right corner.
        • When the export button is clicked the user will be prompted to select the format.


        How to report an issue: -

        Db2: Report an Issue

        • Select View > Command Palette > Db2: Report an Issue
        • This option opens the GitHub repository in the browser where the user can raise any issues related to this extension.

        OR

         

         

        Keyboard Shortcuts: -

        •  All the shortcuts will work only when sql file is opened. (extension should be .sql)
          • Connect: - ctrl + shift + C
          • Disconnect: - ctrl + shift + D
          • Execute Current File: - ctrl + shift + F
          • Execute Single Query: - ctrl + shift + E
          • Execute Multiple Query: - ctrl + shift + M
        • * For MAC replace ctrl with cmd.

         

        1 comment
        89 views

        Permalink

        Comments

        Tue January 26, 2021 06:43 AM

        @Sai Ram This is a brilliant extension - very nice and lightweight compared to using other java based query analysers. I just started playing with it and really like it.

        I was wondering if there was any way to save the connection profile as part of the workspace (in .vscode folder) - similar to how SQLTools manages it? this way profiles can be checked in as part of the project.

        And ideally passwords would be externalised  with the ability to use environment variables or as first time inputs which are saved locally as a secret but not as part of the project so that this secure?