Developing with Db2 for z/OS

Before you start

1. Verify that the version of Db2 for z/OS that you want to connect to, is supported. See the IBM® Software product compatibility reports. The catalog navigation function requires a connection to Db2 for z/OS version 12.1.1 or later.
2. Obtain the following information from your Db2 administrator:
   • The host name of the z/OS system where Db2 for z/OS is installed.
   • The location name and port of the Db2 for z/OS system.
   • To use the catalog navigation function your database logon ID must have read access to several Db2 system catalog tables. For more information about accessing system catalog tables, see Enabling catalog navigation.
   • Optional: The port for the SQL Tuning Services server. This server processes the Visual Explain, Statistics Advisor, and Capture Query Environment tuning features of Db2 for z/OS Development tooling.
3. If required by your site, obtain a license file for IBM Data Server Driver for JDBC and SQLJ, and import it into Developer for z/OS. If the license is activated on the Db2 for z/OS system you are connecting to, then you do not need to do this step. Your Db2 administrator can tell you if this step is necessary. For help with this step, see Importing a license file.

   For more information about the JDBC client license file, see Installing, updating, or upgrading the IBM Data Server Driver for JDBC and SQLJ on Db2 on Linux, UNIX, and Windows systems.

   For information about activating the JDBC license file on the Db2 for z/OS server, see V12R1Mnnn application compatibility levels for data server clients and drivers.

   Tip: If you attempt to connect to Db2 and a license file is required, the product prompts you to import it.
4. Create a connection to the remote system where Db2 for z/OS is installed.
   For instructions, see Creating a connection to a z/OS system or watch this demonstration.

   

Overview and cheat sheets

The Db2 for z/OS Development component provides tools for connecting to Db2 subsystems and SQL Tuning Services servers, running SQL, and tuning SQL statements, all tightly integrated with the COBOL and PL/I development tools in the z/OS Projects perspective. This component integrates the tooling for Db2 for z/OS into the Remote System Environment and the COBOL, PL/I, and z Systems® LPEX editors.

For a detailed introduction to the tasks you can do with Db2 for z/OS Development tooling, open the Develop Db2 Applications cheat sheet:

1. From the menu bar, click Help > Cheat Sheets.
2. On the Cheat Sheet Selection window, expand IBM Developer for z/OS, and then select Getting Started Catalog and click OK. The Cheat Sheets view opens in a new tab.
3. In the left pane of the Getting Started Catalog, expand Getting Started with IBM Developer for z/OS.
4. Click Develop Db2 Applications.
5. In the right pane of the Cheat Sheets view, click Go to Connect to a Db2 for z/OS server.

Connect to a Db2 location

1. In the Remote Systems view, expand a z/OS system connection, and then expand the Db2 for z/OS subsystem.
2. Right-click Db2 for z/OS Connections and select New > Db2 for z/OS Connection.
3. Log in to the z/OS system using your Db2 user ID and password.
4. In the Connection information area of the General page of the connection properties window, type the Location name and Port.
5. In the Authentication information area, choose a login method and enter your authentication information. For more information about the supported login methods, see Authenticating with the database.
6. To test the connection settings, click Test Connection.
7. Click Apply and Close.
   

Navigating catalogs and queries

After you connect to a Db2 database system, you can expand the connection to browse its catalog and query history. The Catalog folder also includes a wizard for generating table and view declarations for C, COBOL, and PL/I programs. To learn more about navigating catalogs, see Navigating catalogs. To learn more about the Declaration Generator, see Generating declarations.

Running SQL

You can run SQL from the COBOL, PL/I, SQL, or z Systems LPEX Editor.

1. Open the editor on a file that contains SQL statements.
2. In a COBOL, Java, SQLJ or PL/I file: Select the contents of an SQL statement, and then right-click and select Db2 for z/OS > Run Selected SQL (Ctrl+Alt+R or ⌥+⌘+R).
3. In an SQL file: You can run selected SQL or all SQL in the file:
   • Select one or more SQL statements and right-click and select Db2 for z/OS > Run Selected SQL.
   • Right-click in the editor and select Db2 for z/OS > Run All SQL.
   Tip: For more information about preferences for SQL content, see Defining SQL content types. To set options for the Run SQL commands, select the Run SQL Options menu item. The context-sensitive helps describe the options available on the Run SQL Options page.

If any SQL statement takes parameters or contains host variables, a window prompting you to enter the parameter or variable value opens. If the SQL statement has been run against the active database recently, the window can be populated with recent values for the Data Type, Null, and Value fields. The query results are displayed in the Execution Status window and the Remote System Details view. For more information about viewing and editing table data in this view, see Viewing and editing table data.

Creating and running SQL scripts

Db2 for z/OS Development tooling includes an SQL editor that you can use to create, edit, run and tune SQL statements. The editor provides tools for editing, formatting, and real-time syntax checking. You can run or tune all SQL statements in the editor or selected statements. You can use it not only to edit SQL files, but also to quickly generate a temporary file for running SQL. For more information about using the SQL editor, see Creating and running SQL scripts.

1. To open a file in the SQL editor, double-click a local or remote file with the .sql file extension.
2. To generate a temporary file:
   1. Select a Db2 for z/OS connection.
   2. Right-click and select New SQL Script.

Creating, deploying, and calling stored procedures

To create, deploy, and call a stored procedure, you must be connected to a Db2 for z/OS location.

1. Create a file or partitioned data set member with the extension .spsql.
2. Open this file in the SQL editor and add the SQL code for the stored procedure.
   Tip: Use the content assist templates createProcedureNative or createProcedureExternal to create sample procedure content. For more information about templates, see Defining SQL templates.
3. Right-click anywhere in the body of the stored procedure open in the SQL editor and select Db2 for z/OS > Deploy Stored Procedure. The Deploy Stored Procedure wizard opens.
4. Specify deployment and routine options on the wizard pages, and then click Deploy. The results of the deploy operation are shown in the Execution Status window, where you can see any warnings or errors and link to more information about SQL codes and status.
5. To run a deployed stored procedure routine, right-click anywhere in the body of the stored procedure and select Db2 for z/OS > Call Stored Procedure. If necessary, the Run Routine window opens to prompt you for variable or parameter values. The results of the call operation are shown in the Execution Status window, where you can see any warnings or errors and link to more information about SQL codes and status.

For more information about these steps, see Creating, deploying, and running stored procedures.

Debugging stored procedures

You can use the Debug As > Db2 for z/OS Routine menu action to debug a native SQL stored procedure. For more information about the debugging process, see Debugging stored procedures.

Connect to an SQL Tuning Services server

To use the Visual Explain, Statistics Advisor, and Capture Query Environment tuning features of Db2 for z/OS Development tooling, you must connect to an SQL Tuning Services server. Creating a connection is a two-step process. Both steps are done in the Db2 for z/OS subsystem of a remote system connection.

1. Connect to the SQL Tuning Services server:
   1. In the Remote Systems view, expand a z/OS system connection, and then expand the Db2 for z/OS subsystem.
   2. Right-click SQL Tuning Services Servers and select New > SQL Tuning Services Server.
   3. Log in to the remote system using your Db2 user ID and password.
   4. Enter the HTTP port into the Port text field.
   5. Optionally, you can customize the host, connection name, user, or password for the connection.
   6. Select the Test Connection to validate the settings enable connectivity.
   7. Select the Apply and Close button to save your connection settings.
2. Associate a database connection profile with a Db2 connection.
   1. In the Db2 for z/OS subsystem, expand Db2 for z/OS Connections, right-click a database connection, and select Properties.
   2. From the list of properties pages, click Tuning.
   3. From the SQL Tuning Services server list, select the tuning server to use when tuning statements in files associated with this Db2 for z/OS connection.
   4. Optionally, if you want to use a tuning profile, check the Use tuning connection profiles checkbox. Then select a pre-existing Database connection profile from the list or use the New button to create a profile.
   5. To use Multi-Factor Authentication (MFA) when performing tuning operations, make sure the Use tuning connection profiles checkbox is unchecked, and on the General tab of your Db2 for z/OS Connection set the Login Method to MFA Token. When these options are selected, you will be prompted to enter an MFA token each time you send a tuning request.
   6. Click Apply and Close.
   1. When tuning without a profile:
      1. If the Use tuning connection profiles checkbox is unchecked, the currentPackagePath field becomes available.
      2. You can leave it blank, or enter one or more collection IDs (separated by commas).
      3. If a package is not found in the first collection ID, the next one is checked in order.
   2. When tuning with a profile:
      1. If you are creating or editing a tuning profile, the currentPackagePath can be set in the Advanced Settings screen.
      2. The same rules apply. Use one or more collection IDs, separated by commas.

Tip: For more information about connecting with SSL/TSL, auto connect, and other connection options, see Connecting to Db2. If you want to import database connection profiles from IBM Data Studio or Eclipse Data tools, see Importing connection profiles from IBM Data Studio or Eclipse Data Tools.

For more information about SQL Tuning Services, see these topics:

• Tuning SQL statements
• IBM SQL Tuning Services

Tuning SQL

After you connect to a Db2 SQL Tuning Services server, you can select and tune SQL statements from the COBOL, PL/I, SQL, or z Systems LPEX Editor.

1. Open the editor on a file that contains SQL statements.
2. Select an SQL statement, and then right-click and select Db2 for z/OS > Tune Selected SQL (Ctrl+Alt+T or ⌥+⌘+T).
   Tip: To set options for the Tune SQL commands select the Tune SQL Options menu item. The context-sensitive helps contain descriptions of the options on the Tune SQL Options page.
3. On the Tuning Actions window, select one or more tuning actions, and then click OK.

   The tuning data is displayed in the Remote System Details view.

4. To see the output of each tuning action, select it and click Open Results.

Setting trace options for a Db2 for z/OS connection

Limitations and troubleshooting

• When connecting to Db2 over SSL, you might receive a NoSuchAlgorithmException that prevents the connection from succeeding. This situation is caused by an incompatibility between IBM JDK 11 and Db2 JDBC driver version v4.29.24. To work around this incompatibility, do one of these options:
  • Configure Db2 for z/OS Development to use a later version of the JDBC driver. To obtain a JDBC driver, go to DB2® JDBC Driver Versions and Downloads for Db2 z/OS. For more information about installing a JDBC driver into Developer for z/OS, see Configuring Db2 for z/OS tooling.
  • Add the JDBC property sslVersion with the value TLSv1.2 to the Optional page of the connection Properties window: