Running SQL from different types of files

A unique and extremely useful feature of Db2 Developer Extension is the ability to run your SQL code directly from any type of file without modifying it to accommodate the syntax rules of the language that you’re writing in. Whether you’re writing in a traditional mainframe language such as COBOL, PL/I, JCL or C or in a modern language such as Python, Node.js, or Rust, or even playing around by using a text file, you can embed your SQL in the file, select it, run it, and view the results.

This feature is useful for validating your SQL code from directly within the host language file, without the overhead of copying and pasting it into a .sql or, for stored procedures, into a .spsql file first.

Using this feature is pretty straightforward. The only prerequisite is that you have a database connection.  You can associate a specific connection to the file that you want to run your SQL from, but if you don’t, Db2 Developer Extension will try to establish a connection for you.

  1. Open a file and add a SQL statement.

  2. As an optional step, you can specify JDBC runtime options by right-clicking anywhere in the editor and selecting Run SQL Options from the context menu.

    For example, you can specify commit and rollback settings, and you can set the CURRENT SCHEMA and CURRENT PATH special registers. For information about the commit and rollback options, see Committing and rolling back changes.

  3. Highlight the statement, right-click it, and select Run Selected SQL from the context menu.

That’s it. Results are displayed in the SQL Results page that opens when the statement completes and stored in the Query History view.

SQL Results view

One other thing: if you’re coding in a .sql file, you can use Run Selected SQL to run just the specific statements that you’re working on, or you can run the entire file by selecting Run All.

If you’re new to SQL and want more information about embedding SQL in host languages, there’s a lot of information about this topic in the Db2 for z/OS documentation.

Examples

The following examples show this feature in action with several different programming languages.

C example

C example

COBOL example

COBOL example

Java example

java example

JCL example

JCL example

Node.js example

Node.js example

Python example

SPython example