Quick start: Integrating Db2 stored procedures and Zowe CLI

Quick start: Integrating Db2 stored procedures and Zowe CLI

Richelle Anne Craw

Db2 stored procedures by themselves have the benefit of improving programming productivity. What if you combine that with the power of Zowe CLI?

1_djS4ER8u9SUHCJtFo71rmQ.jpeg
Photo by israel palacio on Unsplash

In this blog, I will show you how to reap the benefits of Zowe by writing our own Db2 Stored Procedure in Zowe Explorer and creating Zowe CLI commands that call this procedure

What is Db2 Stored Procedure?

Before I begin, let’s have a review. 📚

A Db2 stored procedure is a compiled program that can execute SQL statements and is stored at a local or remote Db2 server. You can invoke a stored procedure from an application program or from the command line processor. A single call to a stored procedure from a client application can access the database at the server several times.

What is Zowe?

1_3w2sBoeg5rRDVdQCDXmZ1A.png

Zowe is an open source project hosted by the Open Mainframe Project that offers modern interfaces to interact with z/OS and allows you to work with z/OS in a way that is similar to what you experience on cloud platforms today.

What is Zowe CLI?

Zowe CLI is the Command Line Interface for Zowe. It allows you to write scripts and it also allows you create a plugin that can access different mainframe applications such as CICS, MQ and most specially Db2.

Before we begin…

It is important to note that before we can integrate Db2 and Zowe CLI we need to install our pre-requisites:

1_P_68Eoz6WPshojgNvkO-hA.jpeg
Photo by Tory Bishop on Unsplash

Pre-requisites:

  1. Install Zowe CLI
  2. Install and configure IBM® Db2® Database Plug-in for Zowe CLI
  3. Optional: Install Zowe Explorer

Once you have installed and configured our pre-requisites, we’ll move to the next step.

Stored Procedures

1_YVaGhyHjnWKaMMII23Eucg.jpeg
Photo by Elevate on Unsplash

Today, we are going to create an external stored procedure in COBOL. On top of this, we will develop it using Zowe Explorer.

What is the Zowe Explorer?

1_nu8XyinhjPu4hzUq2XQRxg.png

Going back…

I would assume that you have enough knowledge about Db2 and Stored Procedures. If not, I have written mine this way:

Step#1: I created a definition for the stored procedure.

1_CIbctVNVkEaFzPiZ5mytFw.png

Step#2: I created a COBOL stored procedure. Do not forget to define the fields in the Linkage Section.

1_Ntk6FnAb3ruF46ZY1SJWkg.png

Step#3: I executed the JCL that will compile my COBOL code and bind it to the Db2. This step can be performed within the Zowe Explorer. Simply right click on the data set member containing the compile JCL and select ‘Submit Job’

Zowe CLI

1_ctRjv5OAkdAFtLCHLhblVw.jpeg
Photo by Kevin Ku on Unsplash

Once your compilation is complete and you have successfully created your Db2 stored procedures, the next steps will be as easy as 🍕 pie. 😊

Here are some of the ways you can call the stored procedure you created:

  • Via COBOL Program: If you created a Main COBOL program to call your stored procedure, you can then trigger that using the Zowe submit jobs command. The submit command offers 3 options in submitting a job. It can be via data sets, via local file or stdin.
1_jignpTbu8lrFOxTZ7vSRyA.png
  • Via Zowe CLI: You can also trigger your stored procedure using the CLI. The Db2 plugin offers that option. Just do not forget to create your Db2 profile 😉
1_cSXpBUMwKL_dGvJIOCyJ6A.png
  • Via scripts that make use of Zowe CLI: Once you have the CLI commands ready, it is now straightforward to create scripts on top of it. This will reduce any number of repetitive tasks involving calls to stored procedures. Any scripting language will work. Here are some sample scripts that utilize various components of the Zowe CLI.

Summary:

  • Today we learned how to seamlessly work with the Zowe Explorer, Zowe CLI, and the Zowe Db2 plugin.
  • We created a COBOL Db2 stored procedure and called it via the Submit command and the Zowe Db2 plugin.
  • We explored the use of scripting with the Zowe Db2 plugin

We barely scratched the surface of the possibilities with our ‘SELECT’ stored procedure. How would you use an ‘UPDATE’ or ‘DELETE’ stored procedure in your scripts?


Find more Zowe blog content here.

Learn more about Zowe here.

2 Likes
Recent Stories
The Importance of Db2 for z/OS Accounting Traces and Reports

Experiences with SQL PL for redesigning a GUI application

Complex SQL tuning in Db2 for z/OS