DB2 SQL
Part Two - Challenge #14

Background:

Use SQL to select records from a DB2 table
The challenge demonstrates how to select rows and columns from a DB2 table,
then you will be instructed to select rows and columns from a different DB2 table.

Required Challenge Preparation - Configure DB2 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


Required Challenge Preparation - Learn to use DB2 Interactive Panels

Examples to interactively process SQL statements follow.

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

From the DB2I PRIMARY OPTION MENU, enter 1 to Process SQL statements.

Above action results in SPUFI display below following a harmless WARNING panel that might appear.

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.

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.

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.


Challenge:

Edit, modify, and execute a forth SQL statment 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 forth statement to return the following:
    • select all rows and all columns from table emp owned by ibmuser

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 with line command c99, then enter

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 z/OS relational database tables.

NEXT! The final Part 2 Challenge

Next: Challenge #15