Sunday, April 25, 2021

Everything about IEFBR14 utility

It takes one full blog post to list out (almost) everything that can be done using a utility which is widely known as a program that does nothing. Ironic! πŸ˜€

Yup! I'm talking about IBM's IEFBR14 utility. 



Table of Contents πŸ“š

Use the links given below to navigate to various sections of this post.

Introduction 

When IEFBR14 is invoked, it branches to the address in Register 14, which returns back control to the operating system. The Assembler instruction to do this is BR 14. That's how this utility got the name as IEFBR14.

When IEFBR14 is called, it immediately returns back the control to the operating system with a completion code of 0. By returning the control to z/OS, IEFBR14 allows the system to process the disposition parameter on any DD statements that are specified along with the EXEC statement. We're simply exploiting this functionality of IEFBR14 for the creation and deletion of datasets. 

IEFBR14 was created because while DD statements can create or delete files easily, they cannot do so without a program to be run due to a certain peculiarity of the Job Management system, which always requires that the Initiator actually execute a program, even if that program is effectively a null statement. The program used in the JCL does not actually need to use the files to cause their creation or deletion — the DD DISP=... specification does all the work. Thus a very simple do-nothing program was needed to fill that role.
- Quoted text from Wikipedia.

Using IEFBR14 utility to create and delete datasets 


IEFBR14 is typically used for creating and deleting datasets. A sample JCL is shown below. 
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //DEL01  DD DSN=Z01071.PS.A,                       
 000005 //     DISP=(MOD,DELETE,DELETE),                    
 000006 //     SPACE=(CYL,(1,0),RLSE)                      
 000007 //NEW01  DD DSN=Z01071.PS.B,                       
 000008 //     DISP=(NEW,CATLG,DELETE),                     
 000009 //     SPACE=(CYL,(1,0),RLSE),                     
 000010 //     DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)                                         
 ****** **************************** Bottom of Data ****************************  
IEFBR14 job step usually consists of an EXEC statement and DD statement for each dataset that we want to process.

As datasets are being referenced in each DD statement, a DISP parameter SHOULD be accompanied. 

Creation of datasets can also be done in foreground mode - 3.2 Data Set Utility panel - from ISPF. IEFBR14 utility is used when we want to create or delete the datasets as part of batch run.  

Creating a dataset using IEFBR14 utility


The JCL to create a new dataset using IEFBR14 utility is as follows: 
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //NEW01  DD DSN=Z01071.NEW.DATASET,                    
 000005 //     DISP=(NEW,CATLG,DELETE),                     
 000006 //     SPACE=(CYL,(1,0),RLSE),                     
 000007 //     DCB=(LRECL=80,RECFM=FB,BLKSIZE=800)                                          
 ****** **************************** Bottom of Data ****************************  

The DD statement named as NEW01 creates a new, empty dataset called Z01071.NEW.DATASET. All the information necessary to create the dataset has been provided in the JCL. 

DISP=(NEW,CATLG,DELETE) - creates a new dataset and catalogs it under normal termination. The dataset will be deleted if the job is terminated abnormally.

BLKSIZE of 800 is provided because the dataset created when providing BLKSIZE=0 may not be opened for view/edit as the system issues 'Invalid block size' message.

Click on the image for a larger version.

System issues 'Invalid block size' message when the dataset is created with BLKSIZE=0.

What happens when you try to create a dataset using IEFBR14 utility but without providing a DCB parameter in the DD statement πŸ€”?


The dataset gets created but it's not usable. Let's look at the dataset information.
 
Data Set Information shows the record format as ?, Record length and Block Size as 0.


Deleting a dataset using IEFBR14 utility


The JCL to delete an existing dataset before its creation is as follows: 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //DEL01  DD DSN=Z01071.PS.A,                       
 000005 //     DISP=(MOD,DELETE,DELETE),                    
 000006 //     SPACE=(CYL,(1,0),RLSE)                      
 ****** **************************** Bottom of Data ****************************  

The DD statement named as DEL01 deletes an existing dataset. If the dataset doesn't exist, MOD disposition creates the dataset and deletes it. Hence, SPACE parameter is provided. 

πŸ’£The job will fail with JCL error if the DD statement has got a DISP=(MOD,DELETE,DELETE) without SPACE= parameter and the dataset doesn't exist. 

SPACE= parameter may not be necessary if you're trying to delete an already existing dataset with DISP=(MOD,DELETE,DELETE).

DISP=(OLD,DELETE,DELETE) without a SPACE= parameter can also be coded if you're pretty sure about the existence of a dataset before running the utility job to delete and re-create the dataset.

What happens when you provide a GDG base in IEFBR14 utility with DISP=(MOD,DELETE,DELETE) πŸ€”?

Let's try this out!

I've created a GDG base named Z01071.TEST.GDG with 3 generations.  


The JCL is as follows:

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //DEL01  DD DSN=Z01071.TEST.GDG,                     
 000005 //     DISP=(MOD,DELETE,DELETE)                     
 ****** **************************** Bottom of Data ****************************  

The JESYSMSG listing after the completion of the job is shown below.



Just the generations that are part of the GDG base gets deleted; not the GDG base itself. 

πŸ’‘ If you want to delete the GDG base in batch run, IDCAMS utility with DELETE command can be used. 

What happens when you provide a PDS member in IEFBR14 utility with DISP=(MOD,DELETE,DELETE) πŸ€”?

Let's experiment!

I've created a PDS named Z01071.TEST.PDS with 3 members in it. 


Let's try to delete the first member (MEMBER1) using the following JCL. 
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //UNCAT01 DD DSN=Z01071.TEST.PDS(MEMBER1),                 
 000005 //     DISP=(MOD,DELETE,DELETE)                     
 ****** **************************** Bottom of Data ****************************  

The results are pretty surprising. Thank god, I didn't store anything important in the test PDS πŸ˜€.


The entire PDS is deleted. 

Uncataloging a dataset using IEFBR14 utility

There is a difference between uncataloging a dataset and deleting a dataset. 

When a dataset is uncataloged, it's removed from the catalog so that if you search for the dataset from the Data Set List Utility (=3.4) just by providing the dataset's name, you will not be able to find the dataset. You should also provide the Volume Serial number the dataset is residing upon.
 
Whereas, if a dataset is deleted, it's removed from VTOC (Volume Table of Contents) and the dataset may not be retrieved.   

Remember that both SMS and non-SMS data sets can be created and deleted using IEFBR14 utility. Only non-SMS data sets can be cataloged and uncataloged.

Let's try to uncatalog a dataset using IEFBR14 and the JCL is as follows: 

 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //UNCAT01 DD DSN=Z01071.INPUT.PS.A,                    
 000005 //     DISP=(OLD,UNCATLG)                        
 ****** **************************** Bottom of Data ****************************  
The JESYSMSG listing after the completion of the job is shown below.

Z01071.INPUT.PS.A dataset is uncataloged. 

Now that the dataset is uncataloged, let's try to search for the dataset just by providing the dataset's name. 
When we try to search for the dataset just by providing its name, No data set names found.

When we provide the Data set name as well as Volume Serial no., the dataset is listed. 



To catalog the dataset using IEFBR14 utility, the following JCL can be used. 
 ****** ***************************** Top of Data ******************************  
 000001 //Z01071A JOB 1,NOTIFY=&SYSUID                       
 000002 //*                                     
 000003 //STEP1  EXEC PGM=IEFBR14                         
 000004 //UNCAT01 DD DSN=Z01071.INPUT.PS.A,                    
 000005 //     DISP=(OLD,CATLG),                        
 000006 //     UNIT=3390,                            
 000007 //     VOL=SER=VPWRKB                          
 ****** **************************** Bottom of Data ****************************  
Note: The UNIT and VOL=SER= parameters must be provided to catalog a dataset.

Running an IEFBR14 JCL using Zowe CLI

Let's put everything (creating, deleting, cataloging and uncataloging) together in a JCL, store it as a local file (.txt file) in Desktop and try to invoke the local file using Zowe CLI (Command Line Interface).

Prerequisites:
  • Access to Zowe and z/OS MF (I've used the access obtained as part of Master the Mainframe 2020). 
  • Zowe CLI must be installed on the system (For additional Zowe CLI documentation, visit https://docs.zowe.org)
Let's create a text file with the following contents. 
 //Z01071A JOB 1,NOTIFY=&SYSUID        
 //*                      
 //STEP1  EXEC PGM=IEFBR14          
 //DEL01  DD DSN=Z01071.PS.A,         
 //     DISP=(MOD,DELETE,DELETE)      
 //UNCAT01 DD DSN=Z01071.INPUT.PS.A,      
 //     DISP=(OLD,UNCATLG)         
 //DEL02  DD DSN=Z01071.TEST.GDG,       
 //     DISP=(MOD,DELETE,DELETE)      
 //DEL03  DD DSN=Z01071.TEST.PDS(MEMBER1),  
 //     DISP=(MOD,DELETE,DELETE)   
The file is saved on my Desktop and it's named as IEFBR14.txt.
Zowe CLI is installed on your own computer, not on the mainframe. You'll use Zowe CLI to interface with Zowe and z/OSMF which is running on the mainframe. 


Steps to submit a JCL stored in a .txt file using Zowe CLI  are as follows: 

1. Open Command Prompt and type zowe. You'll get back a description, a listing of command groups, and options. 

Using the Zowe CLI. 

2. We have to use zos-jobs group to submit a JCL. Type zowe zos-jobs --help-examples  to view some examples. 

3. To submit a JCL from a local file, we should use the command, 

zowe zos-jobs submit local-file "IEFBR14.txt".

Upon the submission of the command, a status bar is shown submitting the local file to z/OS. 


Zowe CLI shows the JobID and the Jobname of the submitted job.

Let's use the JobID to locate the job from Zowe Explorer plug-in in VS Code. 

The JOBS Section on the left side bottom of the picture shows that the job with JobID as JOB04256 completed with return code 0. The JESYSMSG is opened on the right side using the Z Open Editor plug-in.  

Conclusion

Throughout this post, you have witnessed the uses of IEFBR14 utility. Most of the JCL's given in this post were commonly used on the sites that I've worked so far. If I had missed anything, please let me know through the Comments section below. Thx πŸ‘


Thursday, April 1, 2021

How to prevent SQLCODE -803 in IBM DB2?

Hi ! πŸ‘‹

On this April Fool's day πŸ˜€, we will be seeing how to prevent a specific SQL error code (-803) which is issued, when something isn't right when a user insert/update values in a Db2 table.



What's SQLCODE -803 πŸ€”?

When a user try to insert/update a value in column which is constrained to have unique values and if the inserted/updated value is already present in the table, SQLCODE -803 will be issued. Consequently, the insert/update statement will not be processed. 

More info about the error code can be found πŸ‘‰ here.

As a brief aside...

To better understand the stuff discussed in the remainder of this post, we need access to IBM DB2 database. At the time of writing this post, I just had Mainframe access to IBM's Master the Mainframe 2020 learning system and it didn't allow participants to try Db2 as the focus was put on Zowe, VS Code and plenty of other new stuff.

I started searching πŸ” on Google with phrases like 'how to practice db2 at home', 'practice db2 online' and that's how I found the Db2 Community edition Docker image. Docker wasn't new to me as I had already had prior experience by working on the last two challenges in the Part 3 of IBM's Master the Mainframe 2020 contest. 

Docker 🐬 is a technology which allows solutions that run in a standardized environment to be picked and made to run anywhere else. This means that every little file, library, piece of code, even full software pacakges and operating systems can be packaged up into a Docker Container πŸ“¦ and made available for distribution.
- Quoted text from Challenge instructions of ANSB1 - Master the Mainframe 2020.
 

IBM has made available, a Docker image (read image as snapshot) of Db2 11.5.4 Community Edition, so all that we have to do is to install this image on your local machine πŸ’». The procedure to install can be found πŸ‘‰ here

Note: The procedure assumes that you've already installed Docker Desktop on your system and you most probably do if you've finished the last 2 challenges in Part 3 of MTM2020. Else, you can follow the guidelines given in the procedure to install Docker Desktop first. Docker Desktop is nothing but a software which is used to run the Docker images as applications. 

Alternatively, IBM allows us to download and install IBM Db2 Community edition, a free to download, use and redistribute edition of the IBM Db2 data server, which has both XML database and relational database management system features. But, I recommend the Docker image method as I found it to be easy and simple. You can install the docker image and start working with sample tables just over a coffee 🍡 break. 

Alright! Let's get down to business. 

Kickstarting the Db2 Docker Image and creating a sample database...

After completing the procedure to install the IBM Db2 Community Edition Docker image on your machine, you should be able to see the container up and running in the Docker Desktop πŸ‘. 

This image shows the container named db2server in running state. 


Where do we issue SQL commands in Db2 Container πŸ€”?

Open the command prompt and type the following command to access the running Db2 instance within your Docker container:

docker exec -ti db2server bash -c "su – db2inst1"


If the command is successful, you'll see the last login details along with Db2 instance db2inst1 connected to the container named db2server with a $ symbol at the end.

Getting into the container is successful. We can start executing DB2 queries. 

We're all set to issue Db2 commands. Let's create the Db2 SAMPLE database, which will have a set of tables pre-loaded with data. We can then use these tables and their data in our queries. The procedure to create the sample database can be found πŸ‘‰ here.

Commands used to create DB2 SAMPLE database.


From the list of tables displayed after issuing db2 list tables command, let's use the EMPLOYEE table to reproduce SQLCODE -803. 
 
Running SQL statements in IBM Db2 Docker container is more or less similar to running statements in SPUFI in z/OS.  

The EMPLOYEE table has got 42 records. 

All the rows of EMPLOYEE table are displayed after the execution of SELECT SQL query.


In order to reproduce -803 SQLCODE, let's try to re-insert the last record to the EMPLOYEE table as I knew that the first column of the table i.e., EMPNO can only have unique values. 

Make note of the rectangle boxes. The first box shows the last row from the previously executed SELECT query. The second box shows the INSERT SQL query with values same as that of the last row. The third box shows the SQL error code, SQL0803N and the error description.

The error code after executing the INSERT SQL query is SQL0803N, where 0803N is in Zoned decimal format. The last character, N in '0803N', stores the negative sign as well as the value '3'.

Preventing SQLCODE -803...

Method 1: We can write an  SELECT SQL query in COBOL Db2 program before INSERTing a row. If the row is already present, SQLCODE will be 0. Else, SQLCODE will be +100 (no row(s) found). Evaluate the SQLCODE after the execution of the SELECT query and INSERT accordingly. 

Method 2: We can make use of the MERGE INTO statement. The INSERT and UPDATE statements can be incorporated into a MERGE statement by taking an input data source, comparing it to the contents of the existing table and performing one of the actions (INSERT or UPDATE) if the record exists or it does not. 

For example, let's try to update an existing row in the EMPLOYEE table and insert a new row to the table - all these in one statement.

The full statement is as follows. I've used different colors to describe the purpose of each part of the statement.  

MERGE INTO EMPLOYEE AS EM 
USING (VALUES (200340,'ROY','R','ALONZO','E21',5698,'07/05/1997','FIELDREP',16,'M','05/17/1956',31840,500,1907),(200341,'SRINIVASAN','','JV','D11',2882,'03/05/2021','DESIGNER',16,'M','02/03/1992',31840,500,1907)) 
AS ET (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) 
ON (EM.EMPNO = ET.EMPNO) 
WHEN MATCHED THEN UPDATE 
SET EM.BONUS = 1000 
WHEN NOT MATCHED THEN INSERT (EMPNO,FIRSTNME,MIDINIT,LASTNAME,WORKDEPT,PHONENO,HIREDATE,JOB,EDLEVEL,SEX,BIRTHDATE,SALARY,BONUS,COMM) VALUES (ET.EMPNO,ET.FIRSTNME,ET.MIDINIT,ET.LASTNAME,ET.WORKDEPT,ET.PHONENO,ET.HIREDATE,ET.JOB,ET.EDLEVEL,ET.SEX,ET.BIRTHDATE,ET.SALARY,ET.BONUS,ET.COMM)

The first statement, highlighted in blue color, is the MERGE INTO statement, which is used to identify the name of the table (in our case, it's EMPLOYEE) to which data will be inserted or updated. The AS EM code is used to specify the alias for the EMPLOYEE table that can be referenced later in the code.  

The second part of the statement, highlighted in green color, specifies the input data source i.e., two sets of values. The first set (with EMPNO as 200340) is already existing in the table and the second set (with EMPNO as 200341) is a new row that has to be inserted into the table. When you're coding the MERGE INTO statement in a COBOL Db2 program, then the input source can be specified as an host variable array. 

The third part of the statement, highlighted in purple color, is AS ET which is used to specify the alias for the input source. 

The fourth part of the statement, highlighted in orange color, are the column names specified to associate the input data with the target data during any insert or update operations that follow. 

The fifth part of the statement, highlighted in black color, is the search criteria upon which any action will be taken. In this case, if the EMPNO value in the EMPLOYEE table is equal to the input source's EMPNO value, then the actions mentioned in the next part of the statement will be invoked. 

The sixth and last part of the statement, highlighted in brown color, indicates that when the search criteria is matched, an update will occur in the EMPLOYEE table, updating the BONUS column to 1000. The WHEN NOT MATCHED code indicates that if a record doesn't exist, then an insert action will occur and add all the necessary column values from the input source into the EMPLOYEE table. 

Let's run the full statement in Db2 container. 

The SQL command completed successfully.


Let's check the EMPLOYEE table now. 


Note the last 2 rows. The last but one row's BONUS column is updated to 1000. The last row is newly inserted. There are now 43 rows in EMPLOYEE table as opposed to the 42 rows, before executing the MERGE INTO statement. 

We've hit the bottom of this post...

In this post, we discussed the following: 
  • How to install IBM Db2 Docker image on your machine?
  • How to create Db2 SAMPLE database containing a set of tables with pre loaded data after installing and configuring Db2 Community Edition Docker image on your machine?
  • How to issue SQL statements on Db2 Community Edition Docker image?
  • How to prevent SQLCODE -803 using MERGE INTO statement?
Hope this helps! Should you have any questions/feedback please post it in the Comments section below. Thx!