IBM MTM: 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?

Share this:

IBM MTM: Part Three – Challenge #06

I’ve noticed some other Belgians have reached the wall of fame on mybluemix, nice! All from the same school too! Where are the New Media and Communication students at? Hello? *echo*

Come on Kevin! I know, I know, I’m slacking… I had time to make a blogpost yesterday but decided to just watch some Netflix and shut down my brain for a few hours. It was awesome! Well, time to put my brain Billie Branchboots back to work for a few weeks. No breaks allowed!

This is the last small and short challenge of IBM’s Master The Mainframe Part three series. The following blogposts will be significantly longer and more complex hereafter.

Load Client Records in VSAM

IBM Master the Mainframe Part Three – Challenge #06

VSAM, Virtual Storage Access Method, is used as an access method to manage verious user data types. VSAM is a high performance data set type and popular for storing critical data. This challenge is about defining a VSAM KSDS and load client data from a sequential data set.

If you’re interested, then you can either read IBM’s redbook or read this VSAM tutorial to know more about VSAM. BUT! It is required to read about KSDS before attempting this challenge.

We need to create the data structure above. Let’s go! Let’s edit Z30163.JCL(defvsam).

I’m in, now I need to do the following:

  1. Replace @TYPE with VSAM DEFINE parameter to define VSAM KSDS type
  2. Replace @LENGTH,@OFFSET to specify VSAM KSDS key
  3. Replace @MIN,@MAX to specify VSAM KSDS minimum and maximum record length

Again, no solution this time! Sorry! I might be able to help a bit though! Tutorialpoint gives us this:

RECSZ(average, maximum)
KEYS(length, offset)

Regarding RECORDSIZE, keep in mind that each record is 170 characters long. Regarding KEYS, specifies the length and offset of primary key from first column. Think about it 🙂

JOB DEFVSAM(JOB02748) SUBMITTED
JOB02748 $HASP165 DEFVSAM  ENDED AT SVSCJES2  MAXCC=0000 CN(INTERNAL)

SYSTSPRT looks okay, now just copy to P3.OUTPUT(#06) and I’m done!

Took me longer than expected

I thought this would only take me 15 minutes or so. Sadly, I was wrong. It took me about an hour and a half.

Next challenge is very long, seriously! Next up, DB2 relational databases! I’ll probably do challenge 07 and 08 this weekend. Expect the longest blogpost ever! I’ll be sure to contact guinsess worlds records.

What took you longer than expected?

Share this:

IBM MTM: Part Three – Challenge #05

I just noticed that I’ve released the blog about challenge #04 at 12pm instead of 10am. Honest mistake! I have to admit, I kind of rushed that blog. Sorry about the lack of quality lately. I’ve been very busy IRL.

I’ve picked up some games again after a year long break of serious gaming. Besides that I run a Dungeons and Dragons the Curse of Strahd party as DM every two weeks. So now you know what I do besides programming 8 hours at my job, writing blogs, Master the Mainframe and going out with friends. I have to admit, I really enjoy the 15 minutes of true free time I have per day.

The badge

I’m sure you’ve noticed the featured image 🙂 I got it! The badge! IBM contacted me and informed that I’m able to accept my IBM Master the Mainframe badge at Acclaim! I’m so happy! I’m not sure if it means much for any employer but it sure is something I’ll brag with at my local nerd cafe!

Not the best picture, but hey!

Visit my Acclaim profile!

Application Development Support

IBM Master the Mainframe Part Three – Challenge #05

A developer at mycompany contacted me and said something is wrong with the BBRI client data. The developer wrote a program to read the BBRI client data. The result was a program execution abnormal end S0C7. Abend S0C7 is a well known failure related to invalid data in a numeric field.

I will recreate the problem, use SORT to remove the invalid packed decimal fields and write the output to a temporary data set. This looks like an easy assignment.

To recreate the problem I’ll create a new member called cbl0002 inside Z30163.source and copy ‘zos.mtm2018.public.source(cbl0002)’ into cbl0002. Then All that’s left is to edit Z30163.JCL(cbl0002) and run it.

Now let’s head to Z30163.JCL(CBL0002J)!

Let’s submit!

 07.35.52 JOB06624 $HASP165 CBL0002J ENDED AT SVSCJES2 - ABENDED S0C7 U0000 CN(INTERNAL) 

The system detected a data exception (System Completion Code=0C7).     
From compile unit CBL0002 at entry point CBL0002 at compile unit offset +000003DC at entry offset +000003DC
at address 1AC003DC.

Not good! I’ll need to use SORT to remove the invalid packed decimal fields and write the output to a temporary data set. Then pass that sorted data to the application developed by my fellow colleague.

Let’s edit Z30163.JCL(cbl0002j) and first of all delete lines 2 up to and including 8. This way we won’t recompile the program again. Deletion happens via the DD line command. Then we need to copy sort004 into cbl0002 after ‘a’. A is a label, if you don’t now then you’ll need to read more about labels here to know how to define a label.

After copying the file I need to do the following:

  1. Delete line beginning with //SORT004 JOB
  2. Delete line beginning with //BAD
  3. Delete line beginning with OUTFILE FNAMES=BAD
  4. Change //GOOD DD SYSOUT=*
    to //GOOD DD DSN=&&TMP,DISP=(NEW,PASS,DELETE),SPACE=(CYL,1)
  5. Change //PRTLINE DD SYSOUT=*,OUTLIM=15000
    to //PRTLINE DD DSN=&SYSUID..P3.OUTPUT(#05),DISP=SHR
  6. Change //BBRI DD DSN=&MASTER,DISP=SNR
    to //BBRI DD DSN=&&TMP,DISP=(MOD,DELETE,DELETE)

This is what I’ve got.

I’ve left out some lines on the screenshot above, I don’t want to post the exact solution. I promised Mr. Paul Newton from IBM!

JOB CBL0002J(JOB06654) SUBMITTED 
07.56.15 JOB06654 $HASP165 CBL0002J ENDED AT SVSCJES2 - JCL ERROR CN(INTERNAL)

Oops I guess I did something wrong! Let me check!

17 IEFC019I MISPLACED ELSE STATEMENT 
18 IEFC019I MISPLACED ENDIF STATEMENT

Hmm… Oh! I didn’t read step 3, it said ‘delete the last 3 lines’. Oopsie! JOB CBL0002J(JOB06657) SUBMITTED!

That’s the output! The challenge is complete! Ofcourse, if I was at my real job I wouldn’t just post a screenshot of acountbalances and account identifiers 😇

Wait this wasn’t about ICETOOL at all?

In my last challenge I said that the next challenge would be about ICETOOL and not COBOL… What? I’m not sure where I got that from. Next challenge is about VSAM KSDS, I double checked this time!

This challenge was REALLY easy and I believe I understood everything I did. The first thing I’ll do after this blog is read the COBOL source code from last two blogs and try to understand what’s going on.

We’ve completed one-third of #MTM part 3! I just checked some other challenges and it seems that challenge #06 will be the last small/short challenge from this series. L

Also, I have to thank all my e-mail subscribers for not unsubscribing as soon as the feature was available! Thanks for your loyalty! I’m also surprised about the amount of traffic the site gets whenever I publish a new blogpost! This is great!

What’s been great for you lately? Let me know! If not in the comments below, you can always mail at great@kevindurant.be!

Share this: