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?
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?
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:
- Install Zowe CLI
- Install and configure IBM® Db2® Database Plug-in for Zowe CLI
- Optional: Install Zowe Explorer
Once you have installed and configured our pre-requisites, we’ll move to the next step.
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?
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.
Step#2: I created a COBOL stored procedure. Do not forget to define the fields in the Linkage Section.
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’
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.
- 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 😉
- 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.
- 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?