Flours, Sugars, Oils Application Services
Part Three - Challenge #15

Background:

The challenge has neither a right or wrong answer. The challenge requires development of data processing services for the Flours, Sugars, and Oils Company.

COBOL source code and 2 DB2 tables are provided along with a minimum requirement to complete the challenge. The minimum requirement is a matter of following the instructions enabling interative execution of the COBOL program without any COBOL programming source code modifications.

Develop business helpful application services beyond the minimum requirement to distinguish your application from other contestants during the judging process. The quality and usefulness of your business application services will contribute to your chances of being a top place winner.

Judges will execute each contestant challenge 15 COBOL program checking for:
1) Successful program execution
2) Successful program execution of SQL

and essential to be selected as a top place winner:
3) Program enhancements that distinguish your application services from the others

You are at liberty to change any or all of the program.

Learning System IDs Only (AU0####), click on link below for special instructions.
Learning System ID special instructions for this challenge.
Recommendations:
Use your existing DB2 table, T#####, with the client profile data
If you decide to create one or more additional DB2 tables, each table needs a unique table name
  Each new DB2 table can be uniquely identified as follows:
A#####, B#####, C##### - where ##### is the 5 digits of your ID

If you decide to create a new DB2 table, DCLGEN instructions are provided to generate the COBOL source code variables declaring the new DB2 table in the program.

JCL is provided to precompile the COBOL source expanding the EXEC SQL statements, create the executable module, and bind the DBRM module into a required DB2 plan. The DB2 plan name and the COBOL executable program name is required to execute the program.

JCL Diagram

Summary of previous challenges that can help you complete the final challenge:

  1. COBOL programming language
  2. COBOL access to Flours, Sugars, Oils Company sequential data set
  3. COBOL DB2 API to Flours, Sugars, and Oils company DB2 table
  4. Your ID was GRANTed CREATETS, CREATETAB, LOAD ON DATABASE MTM2017
  5. You created a DB2 table and loaded FSO.CLIENT.PROFILE into your DB2 table
  6. A COBOL program, DB2FSO, was provided to interact with your DB2 table
  7. JCL procedure to build COBOL program executable and a DB2 Plan
  8. Interactive execution of COBOL program using DB2 table as a data source

Challenge:

Instructions to meet minimum requirements:

Copy the COBOL source code into your source data set as follows:
Figure 1.
Figure 2.
Figure 3.
Change all occurrences of ##### to 5 numeric digits of your ID for the COBOL source code to reference your personal DB2 table with client data as follows:
Figure 4.
Create executable module and DB2 plan as follows:
Figure 5.
Verify successful completion of compile and DB2 plan bind as follows:
Figure 6.
Figure 7.
Figure 8.
Figure 9.
Observe in the output is the name of DB2 plan which should be z#####b, where the ##### is 5 numeric digits of your ID. This DB2 plan name is required for execution step.
Figure 10.
Figure 11.
Jump to ISPF Command Shell (=6) and enter dsn system(dbbg) to start a DB2 command shell environment. The DSN prompt is the DB2 command shell. From DSN command shell, enter:
run prog(fso) lib(load) plan(z#####b)
Figure 12.
Try a Client Record Lookup and a Product Record Lookup to validate the program successfully executes SQL for each of the DB2 tables declared in the FSO COBOL source code.
Figure 13.
If your FSO COBOL program successfully executed SQL for each of the DB2 tables, then you successfully met the minimum requirement to consider this final challenge complete. Enter stop to terminate the program execution, then enter end to exit DB2 command shell and return to ISPF Command Shell.
Figure 14.
If you want to pursue top place status, continue with instructions that follow.

Upon review of the COBOL source code, hlq.SOURCE(FSO), you will see the code uses 2 DB2 tables. Your client table, T#####, is select/update/insert capable, The FSO.FSOSKU table, asis, is only select capable.

The FSO.FSOSKU table only has 3 product records with SKU identifiers, F1001, S1001, and Q1001. The column names with respective data types can be found in the FSO COBOL source code.

If you decide to create another data source for reading, writing, or updating, it can be any data set type, SEQ, PDS, VSAM, Unix file, or DB2 table.

If you decide to create another DB2 table, copy hlq.SQL member STEP2 to a new member name, then modify to create the new tablespace, table, and optional unique index. Careful here. Comment the drop S##### tablespace statement to avoid deleting your T##### client profile table unless you really mean to drop your existing tablespace and table as a part of a redesign of your new application services program.

Many SQL educational websites explain the SQL DML insert and update statements.
Figure 94. is a simple diagram of the insert statement syntax.

Figure 94.
What follows is an explanation of how to use the interactive DCLGEN, declarations generator, process. The DCLGEN example below inputs the DB2 table you created in a previous challenge. The same process can be used for any new DB2 table. The generated COBOL source can be copied into the FSO COBOL program source eliminating need to type the declarations into the source code.
Figure 95.
Figure 96.
Figure 97.
Figure 98.
Figure 99.
In the event you select to design a new DB2 table as a part of your application services program modification, the URL below can be helpful in designing the column data types.

DB2 for z/OS Data Types

Competing in the final challenge demonstated your ability to be a z/OS application developer. Your completion of Part 3 demonstrates your willingness and capability to be hired by employers needing those attributes to learn from senior IBM Z technicians supporting some of the most critical systems responsible for the day to day business transactions.

Next: Completing Part 3