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! 

Writing a REXX Program to create a Valid Luhn Number generator | Master the Mainframe 2020 | Level 3.3: REXX2: Nexxt Level Rexx

Welcome to my blog! This is my first post of this new year, 2021. Happy New Year to one and all 😄

In this post, we'll look at Level 3.3 of Master the Mainframe 2020. If you haven't registered for MTM2020 yet, then you're missing the fun 😐. Master the Mainframe has been a lot interesting this year (2020) as we are using Visual Studio Code (for the first time) to establish a connection with z/OS. Hit this link to register.

Level 3.3: REXX2: Nexxt Level Rexx is all about building complex logic and functionality using REXX. The task is to write a REXX Exec that acts as a generator, which when executed, will output Luhn-Algorithm compatible 16 digit numbers. The code shouldn't take any parameters though.

If you do not have prior experience in writing REXX exec's, do not worry at all. 

• MTM2020 is the right place to get your hands on REXX. There are couple of challenges in Level 3 which will improve your REXX skills in the best way possible.
• Advertisement alert ⚠: I also recommend you to read a blog post of mine 😀 which is intended for beginners in REXX. 
• Jim Barry's REXX tutorial is my personal favourite, so I recommend that as well. 

It's time ⏰ to go further... 

The first step in the challenge instructions will ask you to copy a member named CCVIEW from MTM2020.PUBLIC.SOURCE to your own SOURCE dataset. Let's take a look at the REXX Exec residing in CCVIEW member, section by section.

 In a nutshell, this REXX Exec reads an input file, MTM2020.PUBLIC.CUST16, line by line over a do while loop until end of the file and writes each line to an output file, if the read line is longer than 10 characters. We should be aware of few commands in REXX before venturing into a REXX Exec that is dealing with files (like this one 👆). 

First things first, the dataset(s) that you're going to use in a REXX Exec should be allocated to the address space that the REXX is running under. This is usually done using the ALLOC command as shown in the lines 11 and 12. The ALLOC command can be used by a REXX program to dynamically allocate necessary datasets. 

Defining ALLOC command for a dataset in REXX Exec is very much similar to defining a dataset in a JCL DD statement for I/O. 

Let's go thru the command given in line 11. 

"ALLOC FI(indd) DA('MTM2020.PUBLIC.CUST16') SHR REUSE" 

FI refers to the name that will be used by other commands in this REXX Exec, like EXECIO,  to refer to this file (Kinda shorthand 😉). DA refers to the real dataset name. The disposition SHR indicates that the file is intended for reading. It is imperative to note that any dataset allocated within a REXX Program using the ALLOC command should be released back to the system using the FREE command, after the file has been used. More about ALLOC can be found 👉 here.

After the dataset is allocated, we can read the file by the EXECIO command using the DISKR parameter. Let's look at line 23. 

"EXECIO 0 DISKR indd (OPEN"

The EXECIO command is used for processing files. 0 along with DISKR indicates that 0 records should be read from the file referenced by indd DD name.

R in the DISKR is for Read. Similarly, W in the DISKW is for write. X in the DISKX will issue an error because there is no such thing as DISKX 😅. Just DISKW and DISKR.

(OPEN indicates that the file should be opened for future use. Another example of EXECIO command can be found in line 31. 

"EXECIO 1 DISKR indd"

This EXECIO command resides within a DO loop ➿. Under each iteration, this EXECIO reads 1 record from indd. The records read will be written to the stack on a first-in, first-out basis . The records are then available by using the PULL or PARSE PULL instructions. 

Lines 30-45 are already explained in the challenge instructions. Shamelessly copying it here 😒. 

There are three nested DO statetments here. The outermost loop is just a check to make sure there are more lines to read, the middle loop iterates through those lines, and the innermost loop checks if the length of the line is greater than 10 characters, before writing it to the output file.

Make note of line 41 which is commented out. 

call INSPECT

The call instruction calls 📞 an internal routine named as INSPECT. Routines are made up of sequence of instructions that can receive data, process it, and return a value. INSPECT label with a colon can be found at line 80. When REXX executes the call instruction at line 41, the control passes to line 80. The instructions under the INSPECT routine are then executed, in this case, just a SAY instruction, which outputs a message to user. RETURN statement at line 82 returns back the control to the CALL instruction. 

Both the input and output files are closed in lines 46 and 57 respectively. FINIS in the command indicates that the dataset should be closed after use. If FINIS is omitted, the datset will remain open for future use. 

The datasets are freed by FREE instruction in lines 76 and 77 🆓. 

When you run this REXX Exec after replacing ZXXXXX with your user ID in line 12, you will notice that the output file looks the same as the input file. You now have a code that runs and it's time to build upon that foundation to make it do something else. 

What's the ask?

Your first task is to add a logic to this program that checks whether the credit card number being read in satisfies the Luhn algorithm. You may probably have the logic for validation coded inside the INSPECT routine. 

Once you have the logic for validating input for a Luhn number, the final task of this challenge is to build a generator that outputs Luhn-algorithm compatible 16 digit numbers. The final task should not read any input files or parameters. When the code is executed, it should write 500 unique numbers, one on each line, to a PDS member (ZXXXXX.OUTPUT(CUST16)). You have to use the ALLOC command to allocate the PDS member. 

Reading the challenge instructions carefully plays a vital role in finishing this challenge successfully. You have got several hints 💡 in there and let me list them out here. 

• REXX is a dynamically typed language. This means you are able to store several values of different types in a single variable during your code execution and no errors will occur. 
• For example, consider the following 2 lines.

       odd_digits = 0

       odd_digits = odd_digits + substr(cc_digits,5,1)

• In the second line, REXX actually understands the data by its usage. It automatically converts the type of the output from the second operand (substr() function) to perform the arithmetic operation. 
• Step 9 in the challenge instructions list out some built-in functions which you may find useful to accomplish the task.
• SUBSTR – Substring – returns just the characters at a specific location within a string. There’s an example of this in action at line 40. 
• LENGTH – Returns the total length, in characters, of a string. 
• MATH OPERATORS – In particular, the // symbol, which returns the remainder after dividing by a number.
• RANDOM – is a built-in function in REXX and it is used to generate a random non-negative whole number between the min and max range that are provided as arguments. 

What was my approach to finish this challenge 🤔? 

I came across the Credit card anatomy which goes like below:

Perceive lines 4 thru 16 as a Credit Card 💳. The first number is Major Industry Identifier (MII) which tells you what sort of institution issued the card. Lines 21 thru 27 shows the list of institutions. 

The first six digits are the Issuer Identification Number (IIN). These can be used to look up where the card originated from. Lines 31 thru 35 might ring some bells 🔔.

The 7th digit to second-to-last digit is the customer account number. Most companies use just 9 digits for the account number, thereby making the Credit card number as 16 digits. But, it's possible to use up to 12 digits for the account number. 

The last digit of a credit card is the check digit or checksum. 

With this info in hand, I generated the random credit card number in the following way: 

/* Major Industry Identifier */                               

random1 = random(1,9)                                         

                                                              

/* random1 to random3 together generates BIN */               

random2 = random(111,999)                                     

random3 = random(10,99)                                       

                                                              

/* random4 to random6 together generates account identifier */

random4 = random(1,9)                                         

random5 = random(1111,9999)                                   

random6 = random(1111,9999)                                   

part2 = random1||random2||random3||random4||random5||random6  

cc_digits = '000' || substr(part2,1,15)    

Note that I had generated only 15 digits of the credit card number using random built-in function (exclude the first 3 digits, 000). The last digit, checksum is calculated using a formula, 

c = (10 − (s mod 10) mod 10)      

where,

c is checksum,

s is  (sum of digits placed in odd positions) + (sum of each digit placed in even positions multiplied by 2) 

Note: if value of each digit placed in even positions multiplied by 2 is greater than 9, then subtract the value by 9. 

After the checksum is calcualted, the 16 digits of credit card number is formed by concatenating the 15 digits, generated out of the random function, with the checksum digit. 

cc_digits = strip(cc_digits||strip(checksum))

strip is a built-in function in REXX which is used to remove the leading and trailing spaces.

|| is the operator used for concatenating 2 strings. 

I had created a separate routine to calculate the checksum. Once the checksum is calculated, the 16 digit credit card number is passed on to another routine, which will validate the number to check if it is Luhn-compatible or not. If the number is valid, it will be written to a compound variable. 

Compound variables:

Variables are treated as compound variables if the variable name ends with a "." (period). All the 500 Luhn-compatible numbers are written to a compound variable named 'out.'. I had used do loops in combination with compound variables to write the numbers. After writing, if you refer to, out.1, it will fetch you the first Luhn-compatible number. Likewise, out.499 will fetch you the 499th Luhn-compatible number. out.0 will give you the count of total number of records present in the compound varaible, 'out.'. 

After storing 500 Luhn compatible numbers in the compound variable, I used the EXECIO command to write the compound variable to the output file. 

"EXECIO * DISKW outdd (STEM out."

The * and DISKW indicates that all the records from the STEM (i.e., compound variable), OUT. should be written to the output file outdd.

Some useful tips:

• Try this website to validate the 500 numbers you generate out of your code, to be Luhn-compatible or not. 
• I recommend you to use Vista TN3270 terminal for this challenge to logon to the MTM system (IP: 192.86.32.153 PORT: 623) rather than VS code. Coding and running your REXX exec will be a lot easier in TN3270 terminal. 
• Coding TRACE I in the 2nd line of your REXX Exec and executing the code will result in debugging mode. This might help you in understanding the program's flow, if things are not working out. More about TRACE can be found here.
• Bonus hint: While using DO loops to traverse across the even and odd positions of the 19 digit credit card number, you may have to step 2 times in each iteration. The syntax in such cases is as follows:

                    do i = 5 by 2 to 17

            (......)

         end

         

         

         do j = 4 by 2 to 18

            (......)

         end

About sharing my solution.. 

Nope😐, I respect the individuality and the fun element in coding these challenges and have opted not to share the complete code. Instead, I believe I've done my best in explaining several jargons used in REXX and those which, when understood, might be of help to you in finishing this challenge. If you still need clarity on any topics, please let me know. 

Hope this helps. Should you have any questions, please post it in the comments section below. Thx.  

Writing a REXX program to copy Mainframe tape dataset to DASD

Hiya! 👋 Welcome to my blog. In this post, let's look at a REXX (not T-REX🦖) code which will take a Mainframe tape dataset name as input and let you copy it to a DASD dataset.  

Mainframe tape datasets  can't be browsed like a PS dataset. To view the contents of the tape dataset, we need to copy the tape dataset to a DASD first. 

I hope this post will be of help to those who don't have prior experience in writing REXX programs. If you read till the bottom of this post, you'll get a fair idea on the following stuff:

• Usage of REXX in IBM z/OS. 
• Defining File-tailoring skeletons. 
• Addressing environments in REXX. 
• Built-in functions.
• IF/THEN/ELSE instructions in REXX. 
• Invoking REXX Exec.

A short intro about REXX:

• REXX is a programming language that was developed by IBM. 
• REXX is easy to learn and use. To prove this, let's write the traditional Hello World🌍 program in REXX.
• 
  

  

  Create a new member in your own PDS
  

  
  

  

  The first line of the REXX program SHOULD be a comment (delimited by /* and */) and it must contain characters  'REXX' in it. say in the second line is a REXX function that is used to print messages to the console.
  

  
  

  

  Save the member and press F3 to go back to PDS members list view. In the line command area, type ex to execute the member.
  

  
  

  

  No caption needed😎
  

  
  
• REXX is very readable, REXX instructions are based on English. 
• REXX has powerful set of built-in functions. 
• REXX is an interpreted language that does not require compilation. Each line of code is checked and interpreted into "machine understandable code" before being executed. 
• REXX runs in all MVS address spaces and on many platforms (there's one for Android too 👀). 

REXX is a program langauge swiss army knife for z/OS System Programmers and System Administrators.  

I guess it's enough talking about REXX. Let's begin writing ✍ the program itself. The entire program is shown below and we will go through the program section by section. 

Lines 1 thru 16: 

In REXX, comment is a sequence of characters delimited by /* and */. The first 16 lines are comments and they tell the modifications that have been made to this code right from the creation of this REXX Exec. Wait, What is a REXX Exec? (you may be asking yourself 🤔). 

Well, a REXX Exec contain REXX language instructions plus commands that are executed by the host environment. 

💡IBM recommends that all REXX execs start with a comment that includes the characters 'REXX' within the first line (line 1) of the exec. Failure to do so can lead to unexpected or unintended results in your REXX exec. 

Lines 17 thru 19: 

One of the strengths of REXX is that you can use it to invoke the functions of other products. ADDRESS command temporarily or permanently changes the destination of the commands that are followed next. Commands are strings sent to an external environment. 

💡ADDRESS statement is more similar to sudo (Switch User and DO this) command in Linux, which will make you the root user briefly to perform root user actions like installing a package. 

To use DB2 commands in REXX, you must first address that environment. Likewise, in Line #17, address ispexec is coded to execute the command in line #18 in ISPF environment, the full panel application that we all are addicted to😀. 

LIBDEF command is used to define the application-level libraries that will be in effect when the application is running. In Line #18, we use the LIBDEF command to define the Skeleton Library 💀. Gotcha! A Skeleton library is used to store skeleton files. A Skeleton file can be a JCL like the below: 

This is pretty much a simple job that has a SORT step in it to copy an input dataset, provided in SORTIN DD statement, to the output dataset, defined in SORTOUT DD statement. But, something here is so weird, isn't it?🤔 You see a lot of ampersands, don't you? Let me list out all the variable names preceded by an ampersand. 

• &JNUM
• &DSNME
• &USRID
• &MON
• &DY
• &HH
• &MM
• &SS 

There isn't an input dataset name defined in SORTIN. Instead, you've got &DSNME. You'll be issued with JCL errors when you manually submit this JCL. So, please don't do that ⚠. 

This JCL is meant for REXX. When our REXX exec is running, this skeleton file will be scanned record-by-record by the File-tailoring services in REXX (more about File Tailoring services will be covered later in this post). Each record will be scanned to find any dialog variable names, which are names preceded by an ampersand. When a variable name is found, it's current value is substituted from the REXX exec. 

💡A skeleton file can be assumed as a template that is non-functional on its own. File-tailoring services read skeleton files and write tailored output that can be used to drive other functions. 

Do you get the whole picture now? We'll use File-Tailoring services in Rexx to substitute values in all those variable names, preceded by an ampersand, in the skeleton file. We'll then have a functional JCL which upon submission will copy the input dataset to the output dataset. Simple! 😎  

Line #19 uses the ARG instruction. ARG retrieves the argument strings provided to a program or internal routine and assigns them to variables. For example, if you pass the string "IBM z/OS" to the statement, arg company product, then

company contains 'IBM'

product contains 'z/OS'

Line #19 is used to pull the input dataset name and assign it to the variable, dsn.

Lines 20 thru 26 in REXX exec: 

We need an input tape dataset name for the REXX exec to copy it to an output dataset. When the REXX exec is invoked without an argment (i.e., an input dataset name), we should tell the user to invoke the REXX exec with an argument and exit the exec. Line 20 thru 26 does that with the help of say instruction in REXX. 

IF/THEN/ELSE instruction in REXX is used to vaildate the dsn variable. When you have more than one statement under an IF condition, enclose them between a DO and END. 

Lines 27 thru 41 in REXX exec: 

The else part starting from line #27 is executed when the user has invoked the REXX exec with an argument. 

STRIP() is a built-in function in REXX and it is being used in line #29 to remove the leading spaces and trailing spaces as well as single quotes (if any) that surrounds the input argument. The stripped value is assigned to variable, a.

RANDOM() is a built-in function in REXX and it is being used in line #30 to generate a random non-negative whole number between the min and max range that are provided as arguments i.e., 001 and 999 respectively. The random number is assigned to a variable JNUM and this variable name, preceded by an ampersand, is present in the first line of the skeleton file. The usage of RANDOM()function in this exec ensures that the job name is unique every time when the output JCL is created by the file-tailoring services. 

USERID() is a built-in function in REXX and it is being used in line #31. This function returns the TSO User ID. Note that the return value of the function is being assigned to variable that is used in the Skeleton file. 

In line #32, the stripped input dataset name available in the variable a is being assigned to the Skeleton file's variable, DSNME. 

Pause! Let's do a status check to see where we are right now ✅. 

Till now, we only have a valid job name and an input dataset name to be assigned to the JCL in the skeleton file. We need an output dataset name. The output dataset name should be unique every time when the REXX exec is invoked. To do that, we will be adding the Date and Time values at the last 2 qualifiers of the dataset name. At the time of writing this line, I invoked the REXX exec against a dataset and got the output dataset name as 'Z01071.TAPE.COPY.Z01071.DDEC20.T053624'. The last but 1 qualifier has got the date as 20th December and  the last qualifier contains the time in Thhmmss format. Let's continue. 

In line #33, a variable upper is assigned with sequence of characters from a to z in upper case and in line #34, a variable lower is assigned with sequence of characters from a to z in lower case. We'll be using these variables as argumens to the TRANSLATE() built-in function in REXX.  

In line #35, two built-in functions are used. SUBSTR() and DATE(),

DATE() function returns the local date in dd mon yyyy format by default. When 'U' is passed as an argument to the DATE() function, the local date will be returned in MM/DD/YY format.   The return value from the DATE() function is being used as the string for SUBSTR() function. SUBSTR() function is used to extract a portion of the string. Therefore, line #35 is used to extract the MM from the date string (in MM/DD/YY format) and value is assigned to a variable named m. 

The CALL instruction in line #36 calls an internal routine, find_month. 

This routine uses the SELECT instruction in REXX to choose one of the 12 months based on the value in variable, m. return statement at the end of the routine, returns back the control to the line next to CALL instruction. 

Line #37, translates the month value from upper case to lower case using the TRANSLATE()built-in function.  

I hope you will be able to decode the lines 38 thru 41. They use the DATE(), TIME() and SUBSTR() functions to assign values to variables that are used in the Skeleton file.

At the end of line #41, we would have assigned values to all the dialog variable names, preceded by an ampersand, in the skeleton file. It's now time to use the File-tailoring services of REXX to write a tailored JCL that can be edited and submitted by the user. 

Lines 42 thru 47:

File-tailoring services:

To use the file-tailoring services, we must first address the ISPEXEC environment. After addressing the environment, the host environment's commands are passed as strings from the lines 43 thru 47. 

• FTOPEN TEMP - Prepares the file-tailoring process and specifies whether the temporary file is to be used for output. 
• FTINCL TCSKEL - Specifies the skeleton file named TCSKEL from the skeleton library to be used and starts the file tailoring process. The skeleton file is read record-by-record and the dialog variable names in the skeleton file are assigned with values from the variable pool created by the REXX exec.
• FTCLOSE -  Ends the file-tailoring process. 
• VGET ZTEMPF - The file tailoring output is directed to a temporary sequential file. The file name of the temporary file is available in the system variable ZTEMPF. Before editing or submitting the job from the temp. dataset, the VGET service should be invoked to initialize the ZTEMPF.  

In line #48, we have issued EDIT dataset command so that the temporary file will be opened in edit mode for the user. The user after checking the JCL can submit the job by issuing SUB command. 

Invoking the REXX exec:

There are several ways of invoking a REXX exec from foreground or background. 

1. Easiest of the lot is, after saving a REXX exec, you can issue an ex  command on the member to execute the exec in foreground mode. 
2. When you want to use the member name of the REXX exec as a line command, you must first allocate your private PDS that contain the REXX Exec(s) to the system libraries SYSEXEC/SYSPROC. Refer the first step in this link for more details on how to do that. You might have to copy paste the REXX exec to your PDS and provide you REXX PDS in the DATASET parameter of the ALLOC command. After the allocation, when you type the member name of a REXX exec, you're private PDS will be scanned by the system and the REXX exec will be executed if it's found in the PDS. 

Only option 2 works with the REXX exec that we've prepared. Invoking the exec will be lot easier when you allocate your private PDS to the system libraries. Let's see how our REXX exec fares out.

First, let's assign the private PDS to system library. 

Note that I've provided my PDS in the DATASET parameter of ALLOC command. 

Type ex on the member to execute the REXX exec. 

Allocation successful! Now, we can use the member name TC as a line command.

Type tc on a tape dataset or a DASD dataset that you want to make a copy. 

Tada! Here is the file tailored output. You may type SUB on the command line to submit this job. You may also type some SORT statements before submitting the job.  

Thx for reading. Please share your thoughts in the comments section. I would be glad to answer to your questions if any. Happy Holidays! 

My take-away on Interskill eLearning Course: Java on z/OS

I had enrolled for Java Programming curriculum in Interskill learning platform. The very first course was 'Java on z/OS for Java Programmers'. Despite having no prior experience with Java, I gave this course a shot and it was worth the time spent 😀

In this blog post, I wanted to write about my take-aways from the course; the very first take-away being a digital badge 🥇 issued by Interskill upon the successful completion of the module.

What I learnt? 🤔

The course starts with some intro about Java on z/OS. 

• Java source code can be compiled into Bytecode. 
• Bytecode is portable such that all it needs to run is Java Virtual Machine (JVM). 
• JVM is a container in which Java bytecode is executed. All Java programs must execute in a JVM.
• We do have a JVM on IBM z/OS so that Java can also be used on z/OS. 
• Java on z/OS uses features provided by z/OS Unix.
• The basic Java support on z/OS is provided by the Java Software Development Kit, or Java SDK. This is a package provided at no charge by IBM. It includes the basic Java standard edition (Java SE) features including compiler, JVM, and more. 
• There are several versions of the z/OS Java SDK and all the versions are equivalent to the same version provided by Oracle on other platforms. 
• More than one Java SDK can be installed at the same time on z/OS. 

How Java executes on z/OS?

Interskill provides an interactive window where you can type some commands and view the results. However, the commands that you can type are LIMITED and appropriate to an environment. It serves more of doing an hands-on rather than dwelling in theory. There was an interactive window that mimicked a z/OS Unix shell and there were several steps which guided me in compiling and executing a Java program from the z/OS Unix shell. I've tried and succeded in doing the same stuff on the system available via Master the Mainframe 2020. That means there is a JVM on z/OS for Unix system in MTM2020 which run Java programs.

 It's time for an hands-on!🙋  Are you ready? 

Compiling a Java program from z/OS Unix shell:

Note: In order to create/access files from your home directory /z/zxxxxx in MTM2020 system, you should first be signed up to 👉 MTM2020 and should've finished the challenges till Level 2.4.

We’re going to use the Unix System Services (USS) on z/OS. USS is a Unix Interface within z/OS which you can login through SSH. With VS Code, we’ve got a terminal where we can get into Unix for z/OS and start issuing Unix commands. I already have a root directory under my user ID.

Let's use the touch command to make a new file and name it as HelloWorld.java

Issuing touch HelloWorld.java creates a new file. ls command lists all the files under the directory /z/z01071.

Type vi HelloWorld.java and press Enter to edit the file that you had just created. Vi is an editor used to edit Java programs.

After writing the code, type :x and press Enter to save the file.

From the root directory, type cat HelloWorld.java to view the contents of the file. 

It's time to compile the program💥

From the root directory, type javac HelloWorld.java and press Enter. 

javac command reads source files written in Java Programming language and compiles them into class files that run on Java Virtual Machine (JVM). 

If the compilation is error free, you'll be prompted to type next set of commands.

From the root directory, type ls and press Enter to see the files under the directory. As a result of the compilation, we can now see a new file named HelloWorld.class (newly created Java Class).

To execute the newly created Java class, we type java HelloWorld and press Enter.

The program has printed a message 'Hello, World!'. We've just executed a Java program on z/OS system🙌

A Java program executing from a z/OS UNIX shell can use the same features that you would expect from Java on other platforms:

• java.io package can be used by the Java programs to access z/OS Unix files. Make a note that we can’t use this package to access the traditional z/OS datasets.

• The standard Java java.util package is available in z/OS for normal Java utilities and classes.

• The java.text package can be used for text, date, number and message manipulation.

• The java.net package can be used for TCP/IP communications. 

The course also provided info on executing Java programs in z/OS batch mode. A JCL with BPXBATCH utility is used to do this. I've tried and failed in executing a Java program in z/OS batch on the system available via Master the Mainframe 2020. 

More info about executing Java programs in z/OS batch can be found 👉 here 

Other methods in which we can execute Java programs in z/OS:

The course also hinted several other ways that a Java program can execute than simply from a z/OS UNIX console or batch. 

• JAVA applications can execute within CICS Transaction server.
• IMS provides message regions with JVMs that allow Java programs to execute. IMS also provided additional services to allow Java programs to access IMS databases.
• The most interesting method is that DB2 Stored procedures can be written in Java on z/OS. Java programs on z/OS can access database managers such as IMS or DB2 using normal Java JDBC or SQLJ calls. However, the course didn’t provide much info about JDBC/SQLJ calls.

z/OS Specific classes:

One of the most useful tools supplied in the z/OS Java SDK is jzos. This provides Java classes and methods for accessing mainframe resources and information from Java running in batch or z/OS UNIX. 

Some of the classes are listed below: 

• com.ibm.jzos.AccessMethodServices – This class provides a Java interface to IDCAMS. 
• com.ibm.jzos.DfSort – This class provides a Java Interface to sort things out using IBM’s DFSORT. 
• To allow Java programs to read/write to z/OS traditional datasets, com.ibm.jzos.RecordReader and com.ibm.jzos.RecordWriter classes can be used. 
• com.ibm.jzos.Zfile – This class can access any traditional z/OS dataset including sequential and VSAM datasets. 
• com.ibm.jzos.Mvs.JobSubmitter – Allow Java programs to submit z/OS batch jobs. 
• jzos also provides a Batch launcher which can act as an alternative to execute Java programs via batch.

Some of the issues 💣 faced when programming in Java on z/OS:

This section of the course focused on the issues that arise due to the usage of different encoding shemes 🔠. z/OS operate in EBCDIC (Extended Binary Coded Decimal Interchange Code) and other systems like Windows and Unix operate in ASCII (American Standard Code for Information Interchange). This can cause some confusion when working with Java programs on z/OS.

• All input source to the Java javac compiler is assumed to be encoded in the default character set, EBCDIC for z/OS. Otherwise, the encoding switch of the javac command can be leveraged to specify the character set of the source code (The ISO encoding format for ASCII is ISO8859-1).
• Java Profiles must be in ASCII on all platforms, including z/OS.
• When executing the program, every JVM stores strings and text internally in Unicode (UTF-16). This must be converted when performing any operations around input or output.
• By default, Java assumes that external data is encoded in the default platform encoding. This means, in z/OS, Java program will read input data assuming it is encoded in EBCDIC. Similarly, data written out will be in EBCDIC. If the external file’s encoding is in ASCII, then Dfile.encoding parameter of the Java JVM an be used to specify it as ASCII. 
• Java programs can convert between encodings using the getBytes method.

You may refer the 👉  Character Encoding Summary for more info on EBCDIC and ASCII character sets. 

That's all! We've hit the bottom of this post. Share your thoughts 💭 in the Comments section below. thx! 👍