DB2 for z/OS
Part Three - Challenge #07

Background

DB2 Relational Database


Click here and review first two sections of Reference drop down - DB2 for z/OS

  • DB2 is IBM's Relational Database Technology
  • Understanding SQL is the foundation for learning any relational database management system

You will learn to create a DB2 Table, load data into the table, select rows from the table


Click here and review 3rd and 4th sections of Reference drop down - DB2 for z/OS

  • DB2 for z/OS can be executed a variety of ways
  • DB2 for z/OS Data Structures

Challenge:

Create DB2 Tablespace, Table, and Index

From the ISPF Primary Option Menu, enter d2 , the DB2 Interface Option

DB2I Primary Option Menu SSID must reference the DB2 Sub System ID, DBCG
 If SSID is DBCG, then skip to selecting (1) SPUFI, below
 If SSID is not DBCG, then enter d , the DB2I Defaults

Change DB2 NAME to DBCG on the DB2I DEFAULTS PANEL 1

DB2I DEFAULTS PANEL 2 is displayed - no changes needed - just enter to proceed

Observe SSID value is now DBCG
Select 1 , SPUFI, to process SQL statements

Type 3 changes on SPUFI Display
Input
  1 DATA SET NAME is sql(create)
Output
  4 DATA SET NAME is sql.out
Options
  5 CHANGE DEFAULTS is no

After entering the above information, the following display appears
The message is harmless - ignore - enter to proceed

Default Panel included YES to Edit Input
The SQL statements starting with -- are comment lines
The SQL without -- creates a DB2 tablespace, table, and index
Change all occurrences of ##### to the last five digits of your personal ID
  The display is an example of changing all ##### to 99999 (assuming the personal ID is 99999)
F3 to save and exit the edit session

Display below appears requesting enter to continue

SQL output - create tablespace successful

SQL output - create table successful

SQL output - create index successful


Load Data into your DB2 Table
  1. Edit Z#####.JCL(DB2LOAD)
  2. Change all occurrences of ##### to last 5 digits of your personal ID
  3. Submit ; =sd ; st ... to submit and jump to SDSF Status display
  4. Review DB2LOAD output DDNAME SYSPRINT associated with StepName LOADDB

Success is DSNU1147I ... TOTAL NUMBER OF RECORDS LOADED=45 in DB2LOAD output

If failure, then correct error


Select all rows and columns from your DB2 Table
Use DB2 Interactive panels and select SPUFI to process SQL statements
  • Input DATA SET NAME must be sql(select)
  • Output DATA SET NAME must be sql.out
  • Edit Input must be YES

Edit sql(select) and execute sql select statement

  1. Change all occurrences of ##### to last 5 digits of your personal ID
      SQL statement returns all rows and columns from your table
  2. F3 to exit and save
  3. Enter to proceed with execution of the SQL statement

Select results from your DB2 Table as instructed
Use DB2 Interactive panels and select SPUFI to process SQL statements
  • Input DATA SET NAME must be sql(select)
  • Output DATA SET NAME must be sql.out
  • Edit Input must be YES

Edit sql(select) and add 4 select statements as instructed below

  1. Replicate the select statement line 4 times with line command r4
  2. Comment line 1 select statement with -- starting in column 1
  3. Modify line 2 select statement to return:
      the total number of records in your table
      result is a number
        > investigate SQL COUNT function
  4. Modify line 3 select statement to return:
      the total number of records in your table where ADDRESS3 column is equal to 'Ohio'
      result is a number
        > investigate SQL WHERE clause
  5. Modify line 4 select statement to return:
      a result set with a totalled BALANCE from all records
      result is a number
        > investigate SQL SUM function
  6. Modify line 5 select statement to return:
      a result set with an average BALANCE for all records with 'Ohio' in ADDRESS3 column
      result is a number
        > investigate SQL AVG function
  7. F3 to save and exit
  8. Enter to proceed with execution of the SQL statement
NOTES:
  • Data in the table needs to be searched with the exact case as seen in the table unless SQL case function is applied to the data - such as 'Ohio' - ISPF primary command caps off may be required to retain upper and lower case
  • SQL statements are terminated by a semicolon ( ; )
  • SQL statement can continue on more than 1 line and is terminated by a semicolon ( ; )

Apply currency format to last 2 SQL select statements

The objective is to output the total balance and average balance from the last 2 SQL select statements
in a currency format with a leading dollar sign and commas, $###,###,##0.00

Documentation about the DB2 for z/OS Currency function will prove helpful
DB2 for z/OS Currency

Use DB2 Interactive panels and select SPUFI to process SQL statements
  • Input DATA SET NAME must be sql(select)
  • Output DATA SET NAME must be sql.out
  • Edit Input must be YES

Edit sql(select)

  1. Modify total balance from all records to output in a currency format
      When reading about DB2 for z/OS currency function, the reference to DSN8 is important to note
  2. Modify average balance from all 'Ohio' records to output in a currency format
      The last SQL statement with currency function needs to be continued on a second line
      The SQL processor will continue to read all lines until semi-colon (;) is reached
  3. Enter to proceed with execution of the SQL statement

Complete challenge by writing SQL result into P3.OUTPUT(#07)
  1. Edit sql.out
  2. Overtype line 000001 with c99 and enter primary command replace p3.output(#07)
      Ignore truncation message and enter to proceed

Next: Challenge #08