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:

4 thoughts on “IBM MTM: Part Three – Challenge #07

  1. HEY MERRY CHRISTMAS!! AM A GREAT FAN OF YOUR WORK.
    I FACED A PROBLEM IN THIS CHALLENGE IF YOU CAN PLEASE HELP ME.
    WHEN I AM EXECUTING THE D2 AFTER REPLACING ALL ##### AM GETTING AN UNEXPECTED ERROR.
    I AM PASTING THE TOTAL FILE AS BELLOW

    ********************************* Top of Data **********************************
    ———+———+———+———+———+———+———+———+
    –DROP TABLESPACE ACCOUNTS.TSZ09606;
    –COMMIT;
    –*******************************************************
    CREATE TABLESPACE TSZ09606 IN ACCOUNTS
    USING STOGROUP ACCTDISK PRIQTY 20 SECQTY 20 ERASE NO
    LOCKSIZE PAGE LOCKMAX SYSTEM
    BUFFERPOOL BP0 CLOSE NO COMPRESS YES;
    ———+———+———+———+———+———+———+———+
    DSNT408I SQLCODE = -601, ERROR: THE NAME (VERSION OR VOLUME SERIAL NUMBER) OF
    THE OBJECT TO BE DEFINED OR THE TARGET OF A RENAME STATEMENT IS
    IDENTICAL TO THE EXISTING NAME (VERSION OR VOLUME SERIAL NUMBER)
    ACCOUNTS.TSZ09606 OF THE OBJECT TYPE TABLESPACE
    DSNT418I SQLSTATE = 42710 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXISB1 SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X’00000064′ X’00000000′ X’00000000′ X’FFFFFFFF’
    X’00000000′ X’00000000′ SQL DIAGNOSTIC INFORMATION
    ———+———+———+———+———+———+———+———+
    DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
    ———+———+———+———+———+———+———+———+
    DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS 1 AND 72
    DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1
    DSNE621I NUMBER OF INPUT RECORDS READ IS 7
    DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 26
    ******************************** Bottom of Data ********************************

    1. Hi Anonymous, you probably have already created the required tables and you’re trying to create them again. The error tells you you’re trying to create something which already exists.
      – Kevin

      1. then why when i am running the jcl it is giving the following error

        Display Filter View Print Options Search Help
        ——————————————————————————-
        SDSF OUTPUT DISPLAY DB2LOAD JOB07154 DSID 103 LINE NOT PAGE MODE DATA
        COMMAND INPUT ===> SCROLL ===> PAGE *
        ********************************* TOP OF DATA **********************************D
        DSNU000I 361 11:40:41.89 DSNUGUTC – OUTPUT START FOR UTILITY, UTILID = Z09606D
        DSNU1044I 361 11:40:41.93 DSNUGTIS – PROCESSING SYSIN AS EBCDIC D
        DSNU050I 361 11:40:41.93 DSNUGUTC – LOAD DATA INDDN(ACCOUNTS) D
        DSNU056I -DBCG 361 11:40:41.93 DSNUGMAP – TABLE ‘Z09606.TZ09606’ NOT FOUND D
        DSNU012I 361 11:40:41.93 DSNUGBAC – UTILITY EXECUTION TERMINATED, HIGHEST RET*
        ******************************** BOTTOM OF DATA ********************************

Leave a Reply

Your email address will not be published. Name, email and website not required.