SQL Table Join
Part Two - Challenge #12

Background:

You will work with 3 relational tables in the this challenge, then join 2 of the tables to produce a desired result set.

Your challenge:

Several DB2 for z/OS tables exist to be used in this exercise:

  • WC.CURRENCY
  • WC.CTYCODE
  • WC.UNIV

The prefix "WC" is the schema or owner of the table and the table names are CURRENCY, CTYCODE, and UNIV. Both the schema and table name are needed when writing SQL statements.

View the rows and columns in the above tables to prepare:

  • list all rows and columns from a table of universities
  • count number of rows in a table of universities
  • list result set based upon a university name pattern
  • list result set based upon a university country code
  • list result set of university country currency name

To accomplish the above, use DB2 Interactive functions panel =D2 and the SPUFI panels. Inside SPUFI, open SQL(SELECT).

Observe all the possible SQL select statements which can executed once uncommented.

ONLY in the event you want SQL to write more than 250 lines (the default), then change line 5 on the SPUFI panel to YES.

            
SPUFI SSID: DBBG ===> Enter the input data set name: (Can be sequential or partitioned) 1 DATA SET NAME ... ===> SQL(SELECT) 2 VOLUME SERIAL ... ===> (Enter if not cataloged) 3 DATA SET PASSWORD ===> (Enter if password protected) Enter the output data set name: (Must be a sequential data set) 4 DATA SET NAME ... ===> SPUFI.OUTPUT Specify processing options: 5 CHANGE DEFAULTS ===> YES (Y/N - Display SPUFI defaults panel?) 6 EDIT INPUT ...... ===> YES (Y/N - Enter SQL statements?) 7 EXECUTE ......... ===> YES (Y/N - Execute SQL statements?) 8 AUTOCOMMIT ...... ===> YES (Y/N - Commit after successful run?) 9 BROWSE OUTPUT ... ===> YES (Y/N - Browse output data set?)

Line 3 in the SPUFI DEFAULTS controls the maximum lines returned. In some cases, such as a request for a result set of all 9000+ universities in the world, you may want to increase this value. Additionally, the output data set SPUFI.OUTPUT may need to be manually deleted and changes made to the default SPACE UNIT and PRIMARY SPACE to allocate a larger SPUFI output data set. A recommendation would be SPACE UNIT of CYL and PRIMARY SPACE 3 and SECONDARY SPACE 1.

            
CURRENT SPUFI DEFAULTS SSID: DBBG ===> 1 SQL TERMINATOR .. ===> ; (SQL Statement Terminator) 2 ISOLATION LEVEL ===> CS (RR=Repeatable Read, CS=Cursor Stability, UR=Uncommitted Read) 3 MAX SELECT LINES ===> 10000 (Max lines to be return from SELECT) 4 ALLOW SQL WARNINGS===> NO (Continue fetching after sqlwarning) 5 CHANGE PLAN NAMES ===> NO (Change the plan names used by SPUFI) 6 SQL FORMAT....... ===> SQL (SQL, SQLCOMNT, or SQLPL) Output data set characteristics: 7 SPACE UNIT ...... ===> CYL (TRK or CYL) 8 PRIMARY SPACE ... ===> 3 (Primary space allocation 1-999) 9 SECONDARY SPACE . ===> 1 (Secondary space allocation 0-999) 10 RECORD LENGTH ... ===> 4092 (LRECL=Logical record length) 11 BLOCK SIZE ...... ===> 4096 (Size of one block) 12 RECORD FORMAT ... ===> VB (RECFM=F, FB, FBA, V, VB, or VBA) 13 DEVICE TYPE ..... ===> SYSDA (Must be DASD unit name) Output format characteristics: 14 MAX NUMERIC FIELD ===> 33 (Maximum width for numeric fields) 15 MAX CHAR FIELD .. ===> 80 (Maximum width for character fields) 16 COLUMN HEADING .. ===> NAMES (NAMES, LABELS, ANY or BOTH)

Use SPUFI to create a result set containing the full country name and university name of all the universities in the Czech Republic. The selection criteria is COUNTRY = 'Czech Republic'.

The result set must be the output of a single select statement joining both the WC.UNIV and the WC.CTYCODE tables. The result set will be 28 rows, one for each university in the Czech Republic.

The first line output line of the result set looks like:

 COUNTRY           UNIVERSITY
---------+-----   -------+---------+---------+---------+---------+-       
Czech Republic     Academy of Performing Arts, Film and TV Fakulty            
            
Note to our friends in the Czech Republic: If our table data is missing your school, please send us a nice email containing the SQL insert statement(s) to add the appropriate information to the database. Thanks!
A few hints to help you out:
  • Research DB2 SQL INNER JOIN syntax.
  • COUNTRY_CODE fields exist in both tables.
  • The COUNTRY field in WC.CTYCODE provides the 'country' full name in the result set.

Use CC#####.SQL(UNIV) to contain the single SQL select statement to produce the names of universities from the Czech Republic. Initially, UNIV has a few select statements to view the data from the 2 relevant tables. You will need to comment these out and create a new select statement that joins the results together.

After successful execution, edit the SPUFI.OUTPUT data set and confirm that it has the desired result set. This result set data must be copied into P2.OUTPUT(#12). Use the line command C99 on the first line and the primary line command REPLACE P2.OUTPUT(#12) to do this now.

When replacing P2.OUTPUT(#12) the following message may be safely ignored and bypassed by pressing the enter key:

Data set attributes are inconsistent. Truncation may result in the right-most positions of some records if replace is performed.

Successful completion of this challenge has data in CC#####.P2.OUTPUT(#12).

Next: Challenge #13