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

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

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

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

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

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

• 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 ( ; )

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

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

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