Db2 SQL
Part Two - Challenge #14

Background:

Learn Once, Apply Everywhere

A relational database is similar to a spreadsheet. However, a relational database can be concurrently read and updated by many users and the relational database has many capabilities unavailable to personal spreadsheets. A spreadsheet is a relatively low volume, low frequency personal data source where a relational database is a high volume, high frequency industrial strength data source.

A major benefit of learning to use any relational database system is you can apply the knowledge to all available relational databases systems.


What is a Relational Database


What are the Most Popular Relational Databases


Learn Generic SQL from w3schools SQL Tutorial


**Use w3schools SQL Tutorial Try it yourself >> to learn about SELECT SQL command and learn about SELECT with WHERE clause. You will be expected to code a SQL SELECT with WHERE clause to be executed on an IBM Db2 for z/OS relational database in the challenge.


Challenge:

Configure Db2 Interactive ISPF Panel

Panels are available on the contest system to interactively process SQL statements.

From the ISPF Primary Option Menu, enter D2 for the Db2 Interactive functions.

The panel is used to interact with Db2 subsystem

Below - observe SSID: value is blank. Enter d to change the 'Defaults'.

Below - the Db2 Name value needs to be DBCG. Type DBCG, then enter to proceed to next panel

Below - DEFAULTS PANEL 2 is displayed.
No change is necessary.

Enter to proceed

Below - observe SSID: value is now DBCG
Your DB2 Interface panels are now ready to access the contest DB2 subsystem Enter 1 to Process SQL Statements

A harmless WARNING panel might appear. Enter to proceed. The next twisty starts with the SPUFI display.


SQL Processor Using File Input, SPUFI

The SPUFI panel should be displayed as a result of the previous action.

Type pds.data(sql) in the input data set name area as shown below,
then tab to output data set name area and enter sql.out as shown below

Note: When YES is present for CHANGE DEFAULTS and EDIT INPUT,
then result will be interim defaults panel followed by edit session

Above action may result in a harmless WARNING panel.
Enter to proceed.

Above actions result in the interim CURRENT SPUFI DEFAULTS panel.
No change is required.
Enter to proceed.

Above actions result in an ISPF edit session.
Z#####.PDS.DATA(SQL) is initially empty.
Enter the SQL select statement as shown below.

Cut and paste text -
select * from ibmuser.dept;

F3 to save and exit from the ISPF edit session

Enter to execute the SQL statement saved in Z#####.PDS.DATA(SQL).

Above actions result in DB2 returning the result set from the select statement.

The asterisk following the select returns all rows and all columns from ibmuser.dept where ibmuser is owner of table name dept.

Observe the table column names in the output.
The column names can be used for selective SQL output.

F3 to exit and return

Navigate your way back to the SQL processing ISPF edit session using the same input data set and output data set.

Type -- in column 1 and 2 preceding the first select statement.
The result is the first select statement is now a comment statement.

Insert a new line and enter the select statement as displayed below.

For copy and paste convenience select * from ibmuser.dept where deptname = 'OPERATIONS';

F3 to save and exit the ISPF edit session, then enter to execute the new SQL select statement.

The above action results in the output below where all rows and all columns are returned
from ibmuser.dept where the deptname = 'OPERATIONS'

Navigate your way back to the SQL processing ISPF edit session using the same input data set and output data set.

Type -- in column 1 and 2 preceding the second select statement.
The result is the first and second select statements are now comment statements.

Insert a new line and enter the select statement as displayed below.

For copy and paste convenience select deptname from ibmuser.dept where admrdept = 'A00';

F3 to save and exit the ISPF edit session, then enter to execute the new SQL select statement.

The result is all rows with only column deptname is returned where the admrdept column is equal to A00.

You are now ready for the simple challenge.

What you learned in Challenge 14 is needed to complete the final Part 2 challenge.


Edit, modify, and execute a fourth SQL statement in Z#####.PDS.DATA(SQL)
  1. All previous statements in Z#####.PDS.DATA(SQL) need to be comment statements
  2. Same as previous interactive SQL executions, write the output to sql.out
  3. Insert a fourth statement to return the following:
    • select only the phonenumber column from table vphone owned by ibmuser where employeenumber is '200220'

Actions to complete the challenge

The SQL output is written to Z#####.SQL.OUT

  1. Edit Z#####.SQL.OUT
  2. Enter the following ISPF editor commands:
    • Type primary command rep p2.output(#14)
    • Overtype line 000001 with line command c99, then enter
      c99 is to copy the next 99 lines - you could enter c19 to copy the next 19 lines
      c99 will get all the lines because the number of lines is less than 99

A display will appear reporting the 'data set attributes are inconsistent. Truncation may result..'

The message is expected. Press enter to proceed.


Successful completion includes member name #14 in P2.OUTPUT with output from the requested Db2 select.

You learned about interactive execution of SQL statements returning a result set from Db2 for z/OS relational database tables.

NEXT! The final Part 2 Challenge

Next: Challenge #15