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.
- DML — Data Manipulation Language
- SELECT, UPDATE, INSERT and DELETE (CRUD applications)
- DDL — Data Definition Language
- CREATE, ALTER and DROP data base structures (DB architecture)
- DCL — Data Control Language
- GRANT and REVOKE privileges (security)
- TCL — Transaction Control Language
- COMMIT and ROLLBACK work
Voila! Just to freshen your memory, CRUD stands for Create, Read, Update and Delete.
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.
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.
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!
What scary thing did you do this week?