DB2 Relational Database and SQL
Part Two - Challenge #11

Background:

Relational Database Management Systems (RDBMS) are used to manage a massive amount of data being simultaneously accessed by thousands of people, internet browser initiated tasks, network devices and other software applications.

Structured Query Language (SQL) is a common and easy to learn language used by programmers to access the data that is stored in any RDBMS. Once you have learned to use SQL, this knowledge can be used to communicate with any other RDBMS with minimal changes to the language syntax.

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

DB2 for z/OS SQL can be executed a variety of ways, including through ISPF panel, JCL, GUI tools, FTP, and programming language APIs like Scala.

By far, the most popular RDBMS in z/OS is called DB2. Don't ask what happened to DB1... Just kidding! DB2 has been around since 1983. You can read more about it.

Here are some excellent sources to learn the capabilities of SQL:

Your challenge:

Use JCL to insert a row into an existing DB2 Table, then select that row from the table and write it out to a PDS member.

A few needed facts about SQL:

  • A dash dash '--' in columns 1 and 2 indicate that the line is a comment.
    It can be useful to keep multiple SQL statements in a single data set, then comment and uncomment specific statements as needed.
  • A semi-colon ';' is used as SQL statement terminator in DB2 for z/OS

Press F3 until you are at the ISPF Primary Option Menu. Look closely for an option titled D2 DB2I Perform DB2 Interactive functions

Use the ISPF primary command =D2 to jump to the interactive DB2 Panels.
The following should be displayed:

DB2I PRIMARY OPTION MENU SSID: DBBG COMMAND ===> Select one of the following DB2 functions and press ENTER. 1 SPUFI (Process SQL statements) 2 DCLGEN (Generate SQL and source language declarations) 3 PROGRAM PREPARATION (Prepare a DB2 application program to run) 4 PRECOMPILE (Invoke DB2 precompiler) 5 BIND/REBIND/FREE (BIND, REBIND, or FREE plans or packages) 6 RUN (RUN an SQL program) 7 DB2 COMMANDS (Issue DB2 commands) 8 UTILITIES (Invoke DB2 utilities) D DB2I DEFAULTS (Set global parameters) X EXIT (Leave DB2I)

If DBBG is not present in the top-right, then take the following actions:

  1. Enter option D to open DB2I DEFAULTS.
  2. Enter DBBG in the DB2 NAME field.
  3. When DB2I DEFAULTS PANEL 2 is displayed, press enter.
  4. DBBG should now be present in the top-right corner.

Next, from DB2I PRIMARY OPTION MENU enter 1 to open SPUFI (Process SQL statements)

Note: SPUFI is an acronym for SQL Processing Using File Input, and yes, everybody calls it "Spoofy".

Modify the panel fields as follows:

1 DATA SET NAME.... ===> SQL(INSERT)
4 DATA SET NAME.... ===> SPUFI.OUTPUT
5 CHANGE DEFAULTS...===> NO

Then press enter. You may encounter the following dialog, which can be ignored:

DSNE345I  WARNING: DB2 DATA CORRUPTION CAN RESULT       
                   FROM THIS SPUFI SESSION BECAUSE THE  
                   CCSID USED BY THE TERMINAL IS NOT THE
                   SAME AS THE CCSID USED BY SPUFI      
                                                        
                   - TERMINAL CCSID: 37                 
                   - SPUFI CCSID   : 1047               
                   NOTIFY THE DB2 SYSTEM ADMINISTRATOR. 
                                                        
PRESS:  ENTER to continue                               
        END to return                                   		
		

Press enter to proceed, and the SQL(INSERT) data set member will open in the ISPF editor. Currently only line 2 will execute and will return a result set that contains all the rows from the WC.FAVORITE DB2 table.

Press F3 to exit edit session and return to the SPUFI panel, and then press enter to process changed SQL. Note that you didn't actually change any of the SQL, just that the option to do was was made available to you prior to execution.

Following execution, the result set is displayed and you can use the standard F7, F8, F10 and F11 keys to browse around. Once satisifed, press F3 to close the output, returning once again to the SPUFI panel. Press enter now, and you will be back in the editor for SQL(INSERT).

Look closely at lines 7, 12, 18, 23, 27 and 29. Each of these need to change to include your personal favorites. When you are ready to execute the statements, uncomment the particular statement. Be sure a comma exists after each favorite, and you may want to comment out line 2 to reduce the amount of output in your result set.

Your task is to use SPUFI to insert your favorite color, month, time of day (dawn or dusk) and your user ID. Use the statements provided in SQL(INSERT) as a model to insert your own favorites into the table, using F3 and enter to execute.

Once the insert is successful, take a look at line 31 in SQL(INSERT). Execution of this statement returns a result set that includes only your entry. Comment out all the other lines and change cc##### to your user ID before attempting to execute line 31.

The last step in this challenge is to edit CC#####.JCL(SQLJCL), edit it to change the hard-coded "cc#####" to your user ID, and then submit the job.

Submitting this JCL will select the record with your ID from the WC.FAVORITE DB2 table and write the result set to P2.OUTPUT(#11).

Browse or view P2.OUTPUT(#11) and verify that it contains your inserted row, then move on to the next challenge!

Next: Challenge #12