DB2 Example
Supplement to Part 3 Challenge 10

The purpose of the example is a guide "how to":

  1. Create a tablespace using an existing DB2 storage group name (MTM2017), database name (MTM2017), and VCAT name (MTM2017)
      While unnecessary, for convenience MTM2017 was used for all 3 names as seen in example
  2. Create a table in previously created tablespace
  3. Create an indexspace and index for the table previously created
  4. Use a DB2 utility to load data into the new DB2 table and index
  5. Use DML SELECT statements to verify data in the new table and index
  6. Locate the z/OS VSAM LDS cluster names associated with the newly created tablespace and indexspace
  7. Execute LISTC on the tablespace z/OS VSAM cluster name and indexspace z/OS VSAM cluster name

NOTE: The example uses Z99999 as the HLQ. You will use your ID as the HLQ.

ISPF panel d2 is available to interact with DB2 for z/OS

Figure 3.


The DB2I PRIMARY OPTION MENU value for SSID: must be DBBG
  If SSID: value is blank, then enter d to change the default SSID value

Figure 4.


DB2I DEFAULTS value for SSID: must be DBBG
  Once changed, enter and F3 to return

Figure 5.


Process SQL statements by entering 1 SPUFI

Figure 6.


Figure 7 is a SQL processing preparation panel
  Line 1 is the SQL input which is your SQL data set with member STEP1
  Line 4 is the SQL output which is your SQL.STEP1 data set name
  Line 5 can be changed to NO to skip the change SQL processing defaults panel
  Line 6 with YES is to proceed to edit of the SQL statements before executing
  Line 7 with YES is to execute the statements following the edit session

Figure 7.
Ignore the DSNE345I WARNING message. It is harmless. Enter to continue.

The SQL member STEP1 is in edit session in Figure 8. below

Several observations:

  1. A dash dash (--) in column 1 and 2 is an RDMS SQL standard for a comment line
  2. Line 3 comment DDL is the one time DBA action necessary to CREATE the STOGROUP named MTM2017 assigning a single disk volume (VPWRKX) with a VCAT of MTM2017
    Therefore, any DB2 objects allocated using DB2 STOGROUP MTM2017 is placed on disk volume VPWRKX with an HLQ of MTM2017
  3. Line 8 comment DDL created a database name of MTM2017 and any MTM2017 database object is directed to use MTM2017 DB2 storage group by default
  4. Line 14-16 comments are DCL, Data Control Language, statements granting specific DB2 privileges to PUBLIC
  5. Line 20-23 are DML, Data Manipulation Language, statements to select specific information from the DB2 Catalog (metadata)
No changes are needed.
F3 to return after reviewing the SQL statements

Figure 8.


Figure 9. below is back at the SPUFI panel following edit session
Observe message on the panel to PRESS ENTER TO CONTINUE with processing of the SQL

Figure 9.


Figure 10. is a harmless warning message that will periodically appear prior to executing SQL from ISPF interactive session.
Ignore the message and enter to proceed.

Figure 10.


Figure 11. below is the output from the SQL processing which shows only comments.
F8 will scroll forward to view more output from the SQL processing.

Figure 11.


Figure 12. below is more output from the SQL which displays result set from SELECT statements writing information from the DB2 Catalog.
F3 to return to SPUFI panel.

Figure 12.


Figure 13. below is preparation for executing DDL
  Line 1 is the SQL input which is your SQL data set with member STEP2
  Line 4 is the SQL output which is your SQL.STEP2 data set name
  Line 5 can be changed to NO to skip the change SQL processing defaults panel
  Line 6 with YES is to proceed to edit of the SQL statements before executing
  Line 7 with YES is to execute the statements following the edit session

Figure 13.


The SQL member STEP2 is in edit session in Figure 14. below
Several observations:

  1. Line 4-7 DDL will create a tablespace associated with database name MTM2017
  2. Line 10-22 DDL will create a table in the tablespace
  3. Line 25-28 DDL will create an indexspace for the table
  4. change all occurrences of ##### to the 5 numeric digits of your ID to create a unique table name

Figure 14.


F3 to save and return to SPUFI panel

Figure 15.


Figure 16. below is back at the SPUFI panel following edit session
Observe message on the panel to PRESS ENTER TO CONTINUE with processing of the SQL
Remember to ignore the warning panel that will appear next

Figure 16.


Figure 17. below is output from the SQL execution
F8 to scroll forward

Figure 17.


Figure 18. below is more output from the SQL execution
The SQLCODE IS 0 messages mean an empty tablespace, table, and indexspace was successfully allocated
F3 several times to return to ISPF Primary Option Panel

Figure 18.


Jump to data set list panel =3.4

Figure 19.


Enter yourid.JCL in the Dsname Level field

Figure 20.


Edit e yourid.JCL

Figure 21.


Select s member name DB2LOAD

Figure 22.


Figure 23. is JCL to execute a DB2 utility which will load data into your new tablespace/table and indexspace/index
Observe line 1200 with DDNAME of CLIENTS referencing data set name with client records
Observe line 1700 with INDDN(CLIENTS) to reference input data set name with client records on line 1200
Observe line 1700 INTO TABLE as the target DB2 table for the CLIENT data

Figure 23.


Change all occurrences of ##### to 5 numeric digits of your ID

Figure 24.


Submit the JCL to load data into the table and jump to SDSF to review the output

Figure 25.


After reviewing output, then jump back to DB2 Interactive panels

Figure 26.


Select 1 SPUFI

Figure 27.


Change line 1 input to SQL(STEP4)

Figure 28.


Figure 29. is a series of SELECT statements to write result sets from the data loaded into your table
Change all occurrences of ##### to 5 numeric digits of your ID to process against your table

Figure 29.


Following the change, F3 to save changes and return to SPUFI

Figure 30.


Enter to execute the SQL

Figure 31.


Observe the result sets from the SELECT statements
Once done reviewing the SQL output, then F3 several times until at the ISPF Primary Option Menu

Figure 32.


Jump to the data set list panel 3.4

Figure 33.


Enter the VCAT name of MTM2017, dsndbc, the database name of MTM2017, and ** as a wildcard for remaining data set name qualifiers in the Dsname Level field, as shown in Figure 34.

Figure 34.


Observe all VSAM data set names which are DB2 tablespaces and indexspaces that have VCAT of MTM2017 as the HLQ and database name of MTM2017 in 3rd data set name qualifier position in the displayed list

Figure 35.


Observe LISTC of the VSAM cluster name to get VSAM information about the tablespace name, S99999, where 99999 should be your 5 numeric digits

Figure 36.

Summary:

  • MTM2017 is the DB2 Storage Group name
  • MTM2017 is the DB2 VCAT name and VSAM data set name HLQ
  • MTM2017 is the DB2 DATABASE name and the VSAM data set name 3rd position qualifier
  • S##### is the naming pattern for your tablespace name
  • T##### is the naming pattern for your table name
  • X##### is the naming pattern for your index space name
  • DSNDBC is the DB2 assigned 2nd level data set name qualifier for the VSAM cluster name
  • DSNDBD is the DATA component of the DSNDBC cluster name
  • STEP1 was a one time execution of DDL statements for all to use those DB2 created resources
  • STEP2 created your personal tablespace, table, and indexspace
  • DB2LOAD JCL executed a DB2 utility to populate the table with client data
  • STEP4 extracted result sets from your newly populated DB2 table using SQL SELECT statements