Photo by israel palacio
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?
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:
Photo by Tory Bishop
- 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.
Photo by Elevate
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?
VS Code extension, powered by Zowe CLI, that streamlines interaction with mainframe data sets, USS files, and jobs.
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’
Photo by Kevin Ku
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?