IBM MTM 2018: Part Three – Challenge #09

In this challenge I’ll be working with a COBOL program to connect and communicate with a DB2 relational database table. Again, it’s not needed to know COBOL but I bet it would help.

I’ve read that COBOL is a relatively easy language to learn and I will take a shot at it together with Java after this mainframe series. I’ve tried some courses on Udemy but, with all due respect, they are horrible. I think I refunded 4 courses in total.

However, PluralSight has a very nice course brought by Tim Slate. I’ll definitely write a blog about that course! My backup is a course at GoGoTraining but that’s a bit too expensive right now. Of course, I won’t forget about Murach’s books 😉

View COBOL compile failure related to a misspelled internal file name

IBM Master the Mainframe – Part Three – Challenge #09

Let’s get right into it! We must navigate to Z30163.SOURCE and create a new empty member using the s db2cbl primary command. Then we’ll copy ‘zos.mtm2018.public.source(db2cbl)’ into it and execute it. Easy!

That’s done!  MAXRC code was 12 so that means the compilation fails. Now where does it go wrong and how do I find the error? Well, let me tell you. Enterprise COBOL messages begin with IGY, so we can issue the find command f igy.

The first message beginning with IGYPS2122-S is a severe error indicating the real problem. The first error starts the chain reaction of errors. We know it’s a severe error as the error code ends with –S. The other errors end with a W which means they are just mere warnings. So let’s first fix the first. Uh… haha.

Study the message the text identifies and correct the code. They also gave a big hint, only 1 character is involved that creates the problem. Let’s open the code and search for every word beginning with REC and see what we find. Then fix.

I found the mistake, I’m not saying the mistake is on the screenshot. “IBM: Remember the big hint – removal of a single character in the COBOL will fix the problem”. Submit the code and hope you get return code 4. JOB07842 submitted! My ouput is good, now let’s copy my output to P3.OUTPUT(#09). Let’s verify!

Ouch! It’s empty! See, it’s always nice to double check! The JCL executes the COBOL code so it would read from my DB2 relational database table. But I guess it has the name wrong?

Let’s edit the DB2CBL code inside SOURCE. Look what the table name is called inside the code and make it read from my table name. Starting from line 000066 we see that it reads ACCTNO, LIMIT, BALANCE, SURNAME, … seems familiar? The change command should do the trick.

I’ll compile, then run the tso submit command. Your output should be in P3.OUTPUT(#09).

My output is what it should be! Yay! Another victory for team basement coders!

My disk space

You know, I was the kind of guy that bought an 85 cents hosting package and expects it to be enought to host a blog with already over a hundred images.

What’s your disk space status?

IBM MTM 2018: Part Three – Challenge #08

Where is it? Hmm… I’m looking for some free time. Can’t seem to find any. It’s been a while. Rough week. I’ve changed the website a little bit. It’s also weird writing a blog on a monday, why didn’t I do it in the weekend? Don’t be confused, it’s not monday, I just publish late.

There’s a cookie notice now, and also some share buttons. The first happened because I want to be legal. The second happened because I didn’t really provide any convenient way of sharing a blogpost. Well, there’s that. Oh.. and I removed the ‘pat on the back button’. I didn’t really like it that much and it only worked half of the time.

And yes! I know! The cover photo of Java is really big. Am I even allowed to use their logo just like that? Hmm… I’ll see, if not… send me a mail at dmca@kevindurant.be.

Java and DB2 API, Application Program Interface

IBM Master the Mainframe – Part Three – Challenge #08

In this challenge we’re not supposed to know a thing about Java but the challenge includes a working example of a JAVA API to connect to a DB2 relation database table to read, write and update data.

Oh… you’re asking me if I know Java? Well… sort of! I’ve made some projects at college for Android class in Java.

Copy Java code from partitioned data set to unix file…

We jump to the ISPF Command Shell panel using =6 and enter the primary command “oput ‘zos.mtm2018.public.source(db2jdbc4)’ ‘db2jdbc4.java'”.

I pressed enter but nothing really happened. I think it just copied db2jdbc4 to db2jdbc4.java. Now let’s continue and jump to the Unix Services command shell prompt by entering =u. Then just execute javac db2jdbc4.java, javac actually is the compiler. Hence the ‘c’.

It seems there is not enough storage to load the DLL module we need. -Xms specifies how much memory we allocate to the pool. Maybe we should get that adjusted? Now exit and continue to TSO/ISPF and modify the SIZE value on the TSO/E LOGON display. The display where you, well, login.

Change value from 32768 to 262144 they say. Let’s just do that and login. Don’t forget to enter your password! Now let’s go back to the Unix Services Command Shell and compile again using javac db2jdbc4.java. Then execute the file using java db2jdbc4. Did you notice something? To compile it’s javac, to run it’s java 😉 

When you run the file you’ll see status ‘running’ in the bottom right corner of your screen. Let’s take a look at the output.

The output is beautiful but wrong. Connection authorization failure occurred. Reason: User ID revoked. That’s not good! We must fix it!

Let’s edit the file using oedit db2jdbc4.java, find and change z##### to your username and change wrongpwd to your real password. I’m not too comfortable hardcoding a password into a file but I’m not in a position to refuse.

I see two lines that I need to edit. However I’ll only do the first for the sake of this blog. I’ve noticed we’ve to run into another error because IBM wants us to. I’ve compiled successfully but ran into another error.

It looks like there’s a SQLERRMC. In normal human speech, that means it can’t find the correct table to select from. It also looks familiar doesn’t it? Let’s fix that.

It worked! However, there’s a string attached. Please look very close to the database name. It’s not as simple as just changing the hashtags.

Let’s exit the OMVS and tso submit ‘zos.public.jcl(db2jdbc4)’ to write the output to Z#####.P3.OUTPUT(#08). To be safe, just verify.

Looks good! All done!

Hmm… I want my own mainframe

Just like Sebastian did in Germany! When I was browsing mainframe.reddit.com I stumbled upon Sebastian’s mainframe video blog and I was baffled. Be sure to check him out!

When will you get your own mainframe?

IBM MTM 2018: Part Three – Challenge #07

No no I didn’t quit… I’m just late! Happy late Halloween!

Man! I was logging in on WordPress and it had to verify if I was a robot. Gotta say… I had my doubts too. This blog post will be long. Ah well, I treat this website more like a report than as a blog.

Also I have to apologise! I feel like my commitment to this blog and series is lacking. My posts are too much to the point. I hate it. I’ll try to do better this time. Let’s deviate as much as possible.

Anyway, let’s get our hands dirty and do some SQL!

DB2 for z/OS

IBM Master the Mainframe Part Three – Challenge #07

It is advised to know some basic SQL before attempting this challenge. It happens that I know some SQL, so I’ll skip the SQL introduction part. However I cannot skip the z/OS RDBMS part. There are 4 categories of SQL.

  1. DML — Data Manipulation Language
    • SELECT, UPDATE, INSERT and DELETE (CRUD applications)
  2. DDL — Data Definition Language
    • CREATE, ALTER and DROP data base structures (DB architecture)
  3. DCL — Data Control Language
    • GRANT and REVOKE privileges (security)
  4. TCL — Transaction Control Language
    • COMMIT and ROLLBACK work

Voila! Just to freshen your memory, CRUD stands for Create, Read, Update and Delete.

DB2I

Let’s first change the DB2I defaults. I get to that menu using =d2 primary command and then D primary command to select the global parameters menu.

I just changed DB2 NAME to DBCG in the first panel, nothing needs to be done inside the second panel.

SPUFI

We need to change the DATA SET NAME to sql(create), output data set is sql.out and set change defaults to no.

Done! Now I’m seeing an SQL file. Lines with — in front are commented lines. I need to change every occurence of ###### with my personal identifier (30163). This can be easily achieved using the change command.

Done!! Yes, I’ll add one more exclamation mark per done. Now we save the file using F3 and we get booted to the same menu we saw a minute ago. ENTER!

Done!!! We’ve created a tablespace, the table itself and its indexes. All in one go!

Load Data into your DB2 Table

We need to edit Z30163.JCL(DB2LOAD) and change all occurrences of ##### to the last 5 digits of my ID. Easy, same command as before!

Done!!!! Looks good, now submit it!

JOB DB2LOAD(JOB05355) SUBMITTED
JOB05355 $HASP165 DB2LOAD  ENDED AT SVSCJES2  MAXCC=0000 CN(INTERNAL)

Found DSNU1147I, shifted my screen to the right by 35 and verified that it loaded 45 records. Ok! I’m on a roll.

Select all rows and columns from your DB2 Table

I’ve navigated to SPUFI using =d2 and now I’ll set the DATA SET NAME to SQL(select). I know I’m not supposed to publish answers of part three but this is just too easy. If you’ve come this far then I guess it’s okay to post a screenshot. I like screenshots. Very much.

Save and execute!

Great! A row contains: ACCTNO, LIMIT, BALANCE, SURNAME, FIRSTN, ADDRESS1, ADDRESS2, ADDRESS3, RESERVED and COMMENTS. Knowing this will make the next exercise much easier.

Select results from your DB2 Table as instructed

Again, I can’t really show you the solution. But I can point you in the right direction! 🙂 The first thing we do is run the primary command caps off to prevent the system from capsing ‘Ohio’. I’m not sure if capsing is a word, it should be. Remember, SQL statements need to be terminated by a semicolon.

I wrote the first 4 DB2 SQL queries. These are pretty easy. The first needs to count all records. The second statement needs to count the amount of records where ADDRESS3  equals to ‘Ohio’. The third statement needs to SUM all values of column BALANCE and the last query needs to calculate the AVERAGE balance where ADDRESS3 equals to ‘Ohio’.

Let’s take a look at my results.

No errors! Again, I can’t show you my full queries, but it should give you an idea 🙂 One more thing!

Apply currency format to last 2 SQL select statements

We need to output the total balance and average balance from the last two SQL select statements in a currency format with a leading dollar sign and commas. Just like $###,###,##0.00. Documentation about the currency format can be found at IBM’s Knowledge Center.

I’ll go ahead and comment the last two statements in my Z30163.SQL(SELECT) file and copy the two needed statements again.

So that’s it! At first sight the documentation might seem confusing but it’s actually clear what you need to do. Let’s see if I did it right. Remember, the input of the currency statement must be a number. Please note, the first two statements are not commented. IBM only said modify, not comment/remove the other statements.

$21,547,464.89
$24,968.44

It works! The output now has those numbers displayed using the CURRENCY format.

Complete challenge by writing SQL result into P3.OUTPUT(#07)

Good! That’s it for challenge #07!

Halloween

Yes I did do something for Halloween. I did really scary stuff. I wrote some JavaScript that uses eval statements and I also made my PHP scripts prone to XSS. But only for one day. Nothing happened!

What scary thing did you do this week?