Getting Started
Part Three - Challenge #01

Welcome!

The theme of Part 3 is to explore the roles and technical responsibilities in a z Systems z/OS environment. These environments are critical to the day to day operation of the world economy.

Have Fun!

Start the Part 3 Adventure

The only requirement is to allocate P3.OUTPUT data set.

From ISPF panel

  • Enter =6
  • Enter part3

Execution of part3 routine allocated CC#####.P3.OUTPUT and wrote member #01

You are ready for the next challenge

Next: Challenge #02

Operations Support
Part Three - Challenge #02

Background

Mainframe systems are designed to be used by large numbers of people. The vast majority of these people are unaware they are using a mainframe. For example: When you check your bank balance using your smartphone, it is not the smartphone that has your bank balance, it is a mainframe. When you make an airline reservation, it is not the browser or smartphone the completed the transaction, it is a mainframe. Many more examples exist.

The sophistication and complexity of these z Systems that serve the users require a variety of roles for 7x24x365 support. Additionally, separation of duties are implemented to insure adequate control and audit requirements. These systems should never depend upon one person.

The system operator monitors and controls the z Systems mainframe hardware and software. The operator monitors the system consoles for abnormnal conditions, and works with the system programming and production control staff to ensure the health and normal operation of the systems.

Actually, the majority of the z Systems mainframe data center operational tasks are automated. However, as an airplane has autopilot, it is best to have an experienced pilot in the plane in case something goes wrong to execute unplanned course corrections, etc.

This challenge includes use of SDSF to process z/OS commands.

While SDSF has commands, / at the SDSF command prompt will display of System Command Extension panel or / followed by a system command will execute the system command. All system commands and responses are written to the system log (SYSLOG)

SDSF has a user log for collecting command input and responses for the immediate session. Take the following actions to understand how it works:

  • =sd ; ulog
  • /d t
  • /d a,l
  • /d tcpip,,netstat,home
  • /d ts,l
  • ulog
  • ulog close
  • ulog
  • /d omvs,options
  • ulog
  • print odsn p3.output(#02)
  • print
  • print close
  • ulog close

The above resulted in P3.OUTPUT(#02) written with the content of ULOG which includes the system commands followed by the system command responses.

Take a moment to view the following link. Quickly scroll through the entire IBM Knowledge Center link page to observe all that is available related to z/OS MVS System Commands.

z/OS MVS System Commands Reference

Your Challenge

The system program linklist, aka LNKLST, is a list of libraries containing executable modules. When JCL is submitted to execute a program, this system program link list is searched by default to locate the JCL requested program.

z/OS MVS System Command Reference IBM Knowledge Center link is available to display information about the LNKLST set.

Locate and execute the z/OS MVS System Command to display the system program linklist. Enter this system command using SDSF ULOG, then print the system command and response to P3.OUTPUT(#02)

Next: Challenge #03

Data Scavenger Hunt
Part Three - Challenge #03

Background:

Part 2 challenges included exposure to:

  • Data Set Types
    1. Sequential Data Sets
    2. Partitioned Data Sets
    3. VSAM Data Sets
    4. Unix File Systems
  • Character Representation
    1. HEXADECIMAL
    2. ASCII
    3. EBCDIC
    4. Packed Decimal
  • z/OS Interactive Facilities
    1. SDSF Console Interface for system command processing
    2. SDSF for viewing JCL job output
    3. TSO OMVS for 3270 Unix Services command shell interface
    4. ISPF =3.4 Data Set List Utility panel to view Unix files
  • Viewing Data
    1. ISPF Editor - (HEX ON/OFF, HX)
    2. File Manager for VSAM view/edit - (HEX ON/OFF)
    3. Unix Services obrowse and oedit - (ISPF Editor for Unix files)
    4. JCL execution of system utility programs to print data from various data set types
    5. SPUFI for data in DB2 Tables

Given Part 2 was a race, you may need to review Part 2 challenges to help complete this challenge. Part 3 is not a race, so you can take your time to piece things together to complete this challenge.

Your challenge:

The objective is to find 8 separate numbers from 8 unique places in the z/OS environment, then total those 8 decimal numbers. Success is the correct decimal total of the 8 numbers.


=6, then enter ch3begin to get a minor hint about the final answer.

To determine the first number

Enter necessary z/OS system command to output the system link list. The previous challenge was to display the system link list.

Observe the system link list output has a respective list entry NUMBER associated with the DSNAME in the output.

The respective data set NUMBER associated with the LAST DSNAME of the system link list is the first number in the series of 8 numbers.


To determine the second number

CC#####.DATA member name Z1 column 65 and 66 is a Packed Decimal number. This Packed Decimal number is the second number.

*Note: Packed Decimal can be either a negative or positive number based upon last hexidecimal digit


To determine the third number

/u/public/num/number3 contains the third number


To determine the forth number

submit jcl(job09)

Successful JOB09 compile and execution of a COBOL program will write the forth number in the JCL JOB output.

The COBOL source program has a minor error that must be corrected to compile. The JCL DDNAME COBOL.SYSIN references the program source data set and member name.

COBOL Basic Syntax Tutorial

The above link has the information necessary to correct the COBOL syntax problems - see Coding Sheet heading information

Now that the compile problem is corrected, execution fails due to a JCL problem. The JCL must be corrected to successfully execute the compiled COBOL program.

JESMSGLG CSV003I message explains the program execution JCL problem

DDNAME LKED.SYSLMOD contains the executable module name from the previous JCL COBOL compile step.


To determine the fifth number

DB2 table WC.ALPHANUM row #1, field name NUMBER has fifth number. You learned how to use DB2 interactively in Part 2 to select rows from tables.


To determine the sixth number

CC#####.DATA member name NUM is an ASCII format number.

This number is 4 digits located columns 1, 2, 3, and 4.


To determine the seventh number

PUBLIC.VSAM is VSAM data set.

The forth record of PUBLIC.VSAM is the seventh number


To determine the eighth number

/u/public/num/packed has a Packed Decimal number that begins in column 1 of row 1.


Once you have the sum of all 8 numbers,

=6 and then enter ch3end

The ch3end routine will prompt you for the sum of the 8 numbers

Next: Challenge #04

Application Development Using COBOL
Part Three - Challenge #04

Background:

COBOL manages some of the most critical data and business logic in the world's economy.

As an example, if you are employed by a large financial organization and you eventually assume some of the critical day to day responsibilities for the operation of the financial organization, you will quickly learn that outages and crippled operations are totally unacceptable due to the amount of money lost and risk to the continued viability of the organization.

COBOL programming language, COBOL compiler optimization, and COBOL runtime performance has over 50 years of technology advancements contributing to the well being of world economies foundation. The core business logic of many large enterprises has decades of business improvement and tuning embedded in COBOL programs.

The point - whatever you read or hear about COBOL, be very scepticle. If you ever have the opportunity to work directly with someone involved in writing or maintaining critical business logic using COBOL, you will be learning about the operation of the core business. Business managers, business analysts, and decision makers come and go. The sum of all good business decisions can frequently be found in the decades of changes implemented in COBOL programs. The answer to "How does this business actually work?" can be found in COBOL programs.

Add the following to your awareness of COBOL. It is an absolute myth that you must be at least 50 years old to be good with COBOL. COBOL is incredible easy to learn and understand. One of the many reasons financial institutions like COBOL, is the fact that it is not necessary to be a programmer to read and understand the logic. This is important because critical business logic code is subject to audit. Auditors are not programmers. However, auditors are responsible for insuring the business financial statements are presented fairly. It is COBOL processing that frequently result in the business ledger updates and subsequent financial statements.

So, now a real world lesson, a comment recently made in an well known business journal by someone with a suspect agenda was quoted as saying, "COBOL is a computing language used in business and finance. It was first designed in 1959 and is pretty old and slow." A highly experienced business technology person knows the only true part of that last sentence was that COBOL was first designed in 1959.

It's no secret that lots of banks still run millions of lines of COBOL on mainframes. They probably want to replace that at some point. Why haven't they? Most banks have been around long enough to still feel the pain from the ~1960's Software Crisis. After spending enormous amounts of money, and time, on developing their computer systems, they finally ended up with a fully functional, well-tested, stable COBOL core system.

Speaking with people that have actually worked on such systems, nowadays they have Java frontends and wrappers which add functionality or more modern interfaces, they run the application on virtualized replicated servers, but in the end, everything runs through that single core. And that core is never touched or changed, unless absolutely necessary.

And from a software engineering perspective, that even makes sense. Rewrites are always more expensive than planned, and always take longer than planned (ok, probably not always. But often.). Never change a running system etc.., unless very good technical and business reasons exist.

That is real world!

While Part 2 exposed you to COBOL, this challenge will re-enforce awareness of COBOL that could prove valuable during your career.

COBOL that runs in z/OS Environment is Enterprise COBOL.

Your challenge:

Submit 10 COBOL compiles where the compiled COBOL source code starts out simple, then increases in functionality. These 10 COBOL compiles include 5 that successfully complete and 5 with a problem to correct.

Each successful compile is immediately followed by a compile with a problem. The successful compile can help resolve the problem with the subsequent job.

A few excellent URLs to help with this challenge

For convience, you are given JCL with in-stream COBOL source code in the same PDS member to edit the COBOL source code and immediately submit for compile and execution.



1st Pair of COBOL compile jobs, CBL1 and CBL2

  • Edit CC#####.SOURCE(CBL1)
  • Review CBL1 source, then submit
  • Review output =sd ; st
  • Edit CC#####.SOURCE(CBL2)
  • Review CBL2 source, then submit
  • Review output =sd ; st
  • Find problem in output
  • Edit CC#####.SOURCE(CBL2), correct problem, and submit
  • Success is compile and execution with return code (RC) 00


2nd Pair of COBOL compile jobs, CBL3 and CBL4

  • Edit CC#####.SOURCE(CBL3)
  • Review CBL3 source, then submit
  • Review output =sd ; st
  • Edit CC#####.SOURCE(CBL4)
  • Review CBL4 source, then submit
  • Review output =sd ; st
  • Find problem in output
  • Edit CC#####.SOURCE(CBL4), correct problem, and submit
  • Success is compile and execution with return code (RC) 00
  • Success verification is member CBL4 written into CC#####.CH4.OUTPUT


3rd Pair of COBOL compile jobs, CBL5 and CBL6

  • Edit CC#####.SOURCE(CBL5)
  • Review CBL5 source, then submit
  • Review output =sd ; st
  • Edit CC#####.SOURCE(CBL6)
  • Review CBL6 source, then submit
  • Review output =sd ; st
  • While CBL6 compile and execution completed with RC 00, a problem does exit.
  • Read CC#####.CH4.OUTPUT(CBL6) to recognize the problem.
  • This is a COBOL source problem. The truncation problem must be corrected.
  • Edit CC#####.SOURCE(CBL6), correct problem, and submit
  • Success is compile and execution with a totalled number that is not truncated
  • Success verification is a correct total number written into CC#####.CH4.OUTPUT(CBL6)


4th Pair of COBOL compile jobs, CBL7 and CBL8

  • Edit CC#####.SOURCE(CBL7)
  • Review CBL7 source, then submit
  • Review output =sd ; st
  • Edit CC#####.SOURCE(CBL8)
  • Review CBL8 source, then submit
  • Review output =sd ; st
  • CBL8 compile failed. Study the error message in the compile output and compare source to CBL7 source.
  • Edit CC#####.SOURCE(CBL8) to correct problem and submit
  • Once CBL8 compile is successful, then an execution problem occurs and must be corrected. The first sentence of the error message in SYSOUT DDNAME does explain the problem and correction needed in CBL8 source. Correct the problem and submit
  • Success verification is output written into CC#####.CH4.OUTPUT(CBL8)


5th Pair of COBOL compile jobs, CBL9 and CBL10

  • Edit CC#####.SOURCE(CBL9)
  • Review CBL9 source, then submit
  • Review output =sd ; st
  • Edit CC#####.SOURCE(CBL10)
  • Review CBL10 source, then submit
  • The problem is CBL10 failed to write any output. CC#####.CH4.OUTPUT(CBL10) is empty and it should have the same output as CBL9
  • Review output =sd ; st
  • This is a COBOL source code problem. Study CBL10 and CBL9 source code to find the problem.
  • Edit CC#####.SOURCE(CBL10) to correct problem and submit
  • Success verification is output written into CC#####.CH4.OUTPUT(CBL10)


Upon correction of the problems, =6 , then submit 'zos.public.jcl(ch4)'. This will write CC#####.P3.OUTPUT(#04)

Next: Challenge #05

Application Development Using Java
Part Three - Challenge #05

Background:

The z System hardware architecture hosting z/OS has capabilities that is perhaps the absolute best platform for Java and JVMs

These capabilities include

  • zIIP - dedicated processors for Java workload
  • SIMD - Single Instruction, Multiple Data
  • SIMT - Single Instruction, Multiple Threads
  • SMT - Simulatenous Multithreading
  • CPACF - CP Assist for Cryptographic Function

Java 8 exploits z Systems new SIMD vector hardware instructions for the Java libraries and numerous frequently used functions. Benchmarks exploiting SIMD vector hardware instructions improved by between 2X and 60X.

In the z/OS environment, the unix shell, JCL, and middleware such as CICS is available to execute Java where Java APIs exist to read from and write to any combination of z/OS data sources.

Your challenge:

While Java can read and write to any z/OS data source, a very powerful Java API is the ability to read and write to relational database tables. A Java driver, (JDBC - Java Database Connectivity), enables this capability.

You are given a Java program that successfully connects to DB2 for z/OS relational database table, executes a SQL statement to count the number unique world currencies, then prints the count. The challenge is to modify this Java program to also count the number of unique world universities, then print counts for both unique world currencies and unique world universities

The following tips apply to REXX and COBOL programming languages and equally apply to those with very little Java specific experience:

Programming languages have:

  1. well defined reserved words with documented behavior to accomplish a task
  2. rules for declaring variables to store characters, numbers, etc.
  3. well defined arithmetic operands and structures for formulas and boolean logic
  4. well defined string manipulation functions
  5. repeatable coding techniques where experience comes from recognizing and reusing specific coding techniques, then finally developing capability to recognize and reuse superior coding techniques

A few needed Java coding facts for accomplishing this challenge

  • Anything following // on a line is a comment. The comments are helpful.
  • Java curly braces '{' some_code '}' identifies a "section" of code with a limited scope. This applies to the 'while' statement that follows the SQL statement execution where that 'while' code is limited to each line returned from the associated SQL statement.
  • Because variables 'rs1' and 'sql1' are used to execute and store result set from the WC.CURRENCY table, use different variables, such as 'rs2' and 'sql2', to execute and store result set from the world universities table, WC.UNIV.

Review Java code that will need to be modified

  • Edit cc#####.source(world)
  • Review world Java source code considering tips above
  • Copy the Java code to your UNIX home directory using JCL by entering tso submit jcl(copyjava)on this source code edit primary command line.
  • SSH Login to z/OS UNIX System Services (USS) shell prompt. Note: Keep your TN3270 edit session active.
  • From USS shell prompt:
    1. javac worldFacts.java
    2. java worldFacts
  • Observe output written to USS session:
    1. Number of World Currencies = 181
    2. Number of World Universites =

The objective of this challenge is for the Java program to write a count for both Number of World Currencies and Number of World Universites. The DB2 for z/OS relational table with World Universites is WC.UNIV.

Modify Java code to include count for Number of World Universities =

  • Edit cc#####.source(world) using TN3270 session
  • Modify world Java source code considering tips above
  • save ,then tso submit jcl(copyjava)
  • From USS shell prompt:
    1. javac worldFacts.java
    2. java worldFacts
  • If unsuccessful, then correct cc#####.source(world) using TN3270 edit session
  • If Number of World Universites = has the count from the table, then
    1. java worldFacts >> world.output
    2. From TN3270 sessiontso submit jcl(copyfile)
  • Review P3.OUTPUT(#05) contains output with both counts

In the event, original copy of Java source is needed

  • Edit cc#####.source(world)
  • d99 on line 1 and copy 'zos.public.p3.source(world)'

Next: Challenge #06

Application Development Using C/C++
Part Three - Challenge #06

Background:

C and C++ can be compiled and executed using either JCL or Unix System Services shell. C and C++ in the z/OS environment can read, write, and update any of the z/OS data sources. Standards compliance with support for international C and C++ programming language standards and GNU C/C++ compatibility extensions for ease of application migration to z Systems.

The IBM z/OS XL C/C++ compiler exploits the latest z/Architecture, including the latest IBM z13 servers. It enables development of high-performing business applications and system programs on z/OS, while maximizing hardware use and improving application performance. It works in concert with z/OS problem determination tools.

Your challenge:

C language source code, CPGM, will require corrections to write to the respective JCL DDNAME. CPGM will read a record from a Unix file and write the record to P3.OUTPUT(#06).

Edit CC#####.JCL(CPGM). The JCL to compile the source is the first 3 lines. At the bottom are the JCL statement to execute the compiled C program.

Several corrections are required. If you submit and review the output, the first problem is a compile failure. The SYSOUT DDNAME from C StepName explains that a name in the program was either not found or declared in the C program. Review the output. Study the "name" in the program. You will determine the problem is related to a misspelled name.

Upon successful compile, the execution fails. Review the EXECCPGM StepName output. Review the JCL DDNAME read by the program. Once the program source is corrected and successful executes, then a record from /u/public/message is written to P3.OUTPUT(#06).

Next: Challenge #07

Application Development using Assembler
Part Three - Challenge #07

Background:

Assembler = Speed. There are a number of cases in large enterprises where speed is paramount over ease of programming.

Your challenge:

ASMPGM assembler program counts from 1 to 10 using a loop. The incremented number is in register 3. The assembler mnemonic (A) is the add instruction. The assembler program will PUT "Hi! I can count fast." before entering the loop. Modify the assembler program to PUT each newly incremented number. If done correctly, P3.OUTPUT(#07) will include:

Hi! I can count very fast.
00000001 
00000002 
00000003 
00000004 
00000005 
00000006 
00000007 
00000008 
00000009 
00000010 			


Several load statements in this assembler program have absolutely no impact on anything. The reason they are in the program is to show how assembler uses CPU registers for CPU operations in a later challenge. So, please do not remove them. The load instructions that have no impact are:

L     6,=C'abc ' 
L     7,=F'1234'
L     8,=C'1234'
LA    9,10


tso submit jcl(asmpgm)

The ASMPGM program output is written to P3.OUTPUT(#07)

edit jcl(asmpgm) and modify the in-stream source code to PUT each newly incremented number. submit , then review JCL job output and P3.OUTPUT(#07) for success.

Hint: A single line is needed in this program to PUT the count. A PUT exists in the program that can copied to the correct location in the assembler logic.

Next: Challenge #08

Systems Administration (Disk and Data Set Management)
Part Three - Challenge #08

Background:

Part 2 Challenge 9 used the ISMF, Interactive Storage Management Facility, panels which is a major disk administration tool. ISMF enables disk administrators to effectively manage the overall disk storage capacity for the enterprise.

Part 2 Challenge 10 explained "extents" where an "extent" is a contiguous area of disk space with an absolute beginning and ending location on disk. Each data set has 1 primary extent and optionally many secondary extents as needed. The disk VTOC, Volume Table of Content, keeps a record of all the extents on that disk volume.

A large enterprise z/OS environment can have thousands of disk volumes and thousands of tape volumes. Each disk volume and tape volume will have a unique volume label. These thousands of disk volumes and tape volumes are capable of storing millions of data sets.

While each disk and tape knows what data set names are contained on its own disk volume or tape volume, how can z/OS find a specific data set name among the thousands of volumes?

This challenge will provide that answer. This knowledge would begin to qualify you as a disk administrator candidate. If you are interviewed for large enterprise disk storage administrator position, the hiring authority would be impressed that you would have this knowledge.

Your challenge:

What is the Master Catalog and what are User Catalogs?

The Master Catalog and User Catalogs is the short answer to "how can z/OS find a requested data set name among the thousands of volumes?"

However, the next question is, "how does the Master Catalog and User Catalogs manage to keep track of these thousands of disk volumes, tape volumes, and millions of data set names?

  • From TSO/ISPF =6 panel , enter listc
  • Observe your CC##### data sets were displayed
  • Stating the obvious, which will become important later, is that all your data sets begin with CC#####

On your 3270 Emulator, locate the PA1 icon or keypad. PA1 is Attention, an interrupt, which is similar to cntl-c in Linux or Unix. If unable to locate PA1, then typically ESC or Alt-Ins will generate the PA1 signal. The next action you will take is going to create more output than you want to page through via the enter key to each '***'.

  • From TSO/ISPF =6 panel , enter profile
  • Observe the PREFIX value is set to your ID, CC#####
  • From that same ISPF Command Shell panel, enter profile noprefix
  • Now enter listc
  • Yikes! What is all that. I entered the same listc command before with completely different results.
  • PA1 icon, or ESC, or Alt-Ins to break out of the endless pages of stuff
  • From that same ISPF Command Shell panel, enter profile prefix(cc#####)
  • From that same ISPF Command Shell panel, enter profile and the prefix value should be your ID
  • Now enter listc
  • Back to normal

LISTC is short for List Catalog.

Things that z/OS technicians know about the Master Catalog and User Catalogs:

  1. Every z/OS has 1 and only 1 Master Catalog
  2. A single z/OS can have many User Catalogs
  3. Both the Master Catalog and the User Catalogs are VSAM data sets on disk volumes
  4. A Catalog has entries that associate a volume label with a data set name
  5. The Catalogs are seached for a requested data set name. A Catalog provides a volume label for the request to look in the VTOC of that volume label.
  6. When a new data set is created, a Catalog creates a new entry containing the data set name with the associated volume label where the data set was placed.

When LISTC was entered with PROFILE NOPREFIX, then the list catalog command started listing everything in the Master Catalog. The very first entry listed in the Master Catalog is the Master Catalog data set name.

When LISTC was entered with PROFILE PREFIX(CC#####), then the list catalog command started listing only catalog entries that begin with CC#####.


Best practices that are well known by experienced large enterprise Disk Administrators:

  • Master Catalog stores critical operating data set names with associated disk volume label such as SYS1 data set names
  • Master Catalog SHOULD NOT be used to keep track of business data set names
  • User Catalogs should keep track of business data set names and the associated volume labels
  • Master Catalog is searched first for business data set names
  • Master Catalog stores "alias" entries which is effectively the data set name "prefix"
  • Master Catalog "alias" entries include a related User Catalog name where the User Catalog has a catalog entry with the entire data set name with associated disk volume label

submit jcl(dsninfo) and review the output

This JCL that executes a series of system utility programs to gather CATALOG and VTOC details about a specific data set name might be something a disk administrator has in their personal JCL library toolbox.

A REXX routine was created to get specific data set name information displayed on the 3270 session immediately. A few errors exist in the REXX routine. Review of the JCL containing the system utility program control statements has the the challenge solution.

The REXX routine is taking only the most critical information to the you as a disk administrator. Specifically, only 2 of the 4 JCL steps are needed in the REXX routine, ALIAS and VTOCDSCB.

The objective is for the REXX routine to produce output from the DSNINFO JCL job ALIAS and VTOCDSCB steps. This is a total of 3 commands in the REXX routine that need minor alterations.

Edit CC#####.REXX.CLIST, then enter ex to left of member DSNINFO. This will execute the REXX routine that must be corrected to produce the same output as DSNINFO JCL job steps ALIAS and VTOCDSCB.

Use zos.public.hud.data.csv as the test case data set name input for the REXX routine.

Once satisfied with that REXX routine output, then submit 'zos.public.jcl(dsninfo)' . This JCL will write output from your REXX routine into P3.OUTPUT(#08)

Next: Challenge #09

Systems Administration (Security)
Part Three - Challenge #09

Background:

Security Administration roles and security best practices have always been critical. The internet has enabled public's indirect access to critical data sources. This has increased the intensity around security. Audit functions are also critical. The security administer will typically conduct audit functions in an attempt to find and address problems before auditors discover issues which must be reported.

Your challenge:

You (the new security administrator) were asked to provide "all audit records for a specific user", TEST003.

RACF data base activity and z/OS SMF system activity records are collected routinely to produce reports such as this one.

The senior security administrator gave you the instructions below to print the requested report. The senior security administrator told you that many reports exist and each unique report as a unique 4 character value. The senior security administrator does not remember which 4 character value is associated with the "all audit records for a specific user" report. You will need to identify that 4 character value.

  • Copy ZOS.PUBLIC.RACFICE.CNTL($$CNTL$$) to CC#####.JCL(RACFRPT)
  • The copied JCL(RACFRPT) member is a JOB card with lots of commented JCL statements
  • Review the CC#####.JCL(RACFRPT) comments to find the 4_char_value associated with the requested report.
  • Once the 4 character RACF report is identified, then:
    1. Copy ZOS.PUBLIC.RACFICE.CNTL(4_char_value) to CC#####.JCL(4_char_value)
    2. Copy ZOS.PUBLIC.RACFICE.CNTL(4_char_valueCNTL) to CC#####.JCL(4_char_valueCNTL)
    3. Edit CC#####.JCL(4_char_valueCNTL) and change IBMUSER to TEST003
  • Edit CC#####.JCL(RACFRPT) then:
    1. Delete the JCLLIB statement
    2. Change existing JCL statement to read SET ADUDATA=ZOS.PUBLIC.RACF.SMFUNLD
    3. Change existing JCL statement to read SET DBUDATA=ZOS.PUBLIC.RACF.UNLOAD
    4. Change existing JCL statement to read SET ICECNTL=CC#####.JCL
    5. Uncomment only one EXEC RACFICE,REPORT= statement (the one with the appropriate 4_char_value)
    6. Submit the JCL

If the instructions were followed accurately, then RACFPRT output will include the report in PRINT DDNAME.

Enter XDC to the left of the PRINT DDNAME output. An SDSF Open Print Data Set panel will appear. Type P3.OUTPUT(#09) in the Data set name field, then enter SHR in the Disposition field. This will write PRINT DDNAME report into P3.OUTPUT(#09).

Next: Challenge #10

Machine Instructions
Part Three - Challenge #10

Background:

Software program execution requires hardware. Computing hardware consist of complex electronics. Electronic transistors can be either 0V (Volt) or greater than 0V. The 0V is represented by a binary 0 and the greater than 0V is represented by a binary 1. Therefore, computers process binary bits (0V or +V) at the lowest level.

Hardware architectures differ. Every processor or processor family has its own machine code instruction set. Instructions are patterns of bits that by physical design correspond to different commands to the machine.

It is not practical to write a software program using machine code instructions just like it is not practical to enter data as binary strings. Each hardware architectue includes an Assembler with Assembler Language instruction mnemonics that translate into architecture dependent binary strings. Assembler mnemonics generate machine code. Most software programs are written in High-Level Languages which are compiled into assembler mnemonics which in turn generate the hardware dependent machine code.

Java is an exception. Java is compiled into byte code. A Java Virtual Machine, JVM, exists to execute the byte code. Each hardware architecture has a machine dependent JVM available to process the Java byte code. This is why Java executable code can be considered portable between hardware architectures without the need to compile into native machine code. However, the translation abstraction level adds some significant overhead.

Very large enterprises and software development companies write applications in Assembler because processing speed is paramount. Also, understanding assembler provides deep machine insight that can prove valuable to clearly identify root cause of complex problems.

Your challenge:

ASMPGM assembler program from challenge #07 will be used in this challenge to explore Assembler mnemonics, machine code instructions, and CPU registers role in processing the machine code instructions

Take the following actions to familiarize yourself with TSO TEST environment.

  • Exit ISPF to TSO Ready prompt, =x
  • If interim screen diplayed, enter 2 to delete log
  • Use the TSO TEST facility to load ASMPGM executable - TEST 'CC#####.LOAD(ASMPGM)'
  • List Program Status Word before executing program - listpsw
  • List registers before executing program - list 0r:15r
  • Set breakpoint at displacement offset +14 at +14 (list 0r:15r ; go)
  • Set breakpoint at displacement offset +56 at +56 (list 0r:15r ; go)
  • Set breakpoint at displacement offset +60 at +60 (list 0r:15r ; go)
  • Set breakpoint at displacement offset +A2 at +A2 (list 0r:15r ; go)
  • Start the executiongo then enter to continue at each breakpoint stop
  • Terminate TEST end , then return to ISPF ispf

ZOS.PUBLIC.ASMPGM.LISTING is the ASMPGM compile listing with nmemonics and respective machine code instruction. This same listing has the output of the TSO TEST environment you just explored following the compile listing.

Note: Please DO NOT Edit ZOS.PUBLIC.ASMPGM.LISTING. It cannot be changed and it could prohibit others from viewing it which would require your session to be cancelled to free the listing for others to view.

View (v) ZOS.PUBLIC.ASMPGM.LISTING to observe the following:

  • Listing line #2 and #3 - note the Address which is the ASMPGM Base Address and Length of program
  • Listing line #7 - column heading. The challenge instructions will reference these column headings.
  • Stmt column - decimal - "+" indicates statement inserted as a result of assembler MACRO or system symbol
  • LOC column - relative program location in hexadecimal
  • Object code column - machine code instructions in hexadecimal
  • Addr1 Addr2 columns - relative program locations in hexadecimal used by machine instruction
  • Listing line #154 to #174 - Program Literals used, when (stmt in decimal), and how.
  • Listing line #177 to #193 - Registers used, when (stmt in decimal, and how.
  • And yes, this listing has the challenge 7 code correction. Challenge 7 enable you to study this code in preparation for this challenge.
  • Starting at line 195 is the same TSO TEST results you manually explored

Following these questions with multiple choice answers, you will be instructed to execute a routine to collect your answers.

****************************************************
* Question #1                                      *
*                                                  *
* What is ASMPGM first machine operation code      *
* instruction?                                     *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) 00028DC0                                      *
* 2) USING                                         *
* 3) 47                                            *
* 4) CSECT                                         *
* 5) F0                                            *
* 6) B                                             *
*                                                  *
****************************************************
* Question #2                                      *
*                                                  *
* What is ASMPGM Assembler Mnemonic at +56?        *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) 00DA2618                                      *
* 2) 1B                                            *
* 3) 22                                            *
* 4) None                                          *
* 5) SR                                            *
* 6) R2                                            *
*                                                  *
****************************************************
* Question #3                                      *
*                                                  *
* What is ASMPGM machine operation code            *
* instruction +60?                                 *
*                                                  *
* Enter the number of the best answer              *
*                                                  *
* 1) 4E                                            *
* 2) CVD                                           *
* 3) LOOP                                          *
* 4) DS                                            *
* 5) 0H                                            *
* 6) 00DA2618                                      *
*                                                  *
****************************************************
* Question #4                                      *
*                                                  *
* What is ASMPGM Assembler Mnemonic at +A2?        *
*                                                  *
* Enter the number of the best answer              *
*                                                  *
* 1) 00028E4A                                      *
* 2) 0H                                            *
* 3) DS                                            *
* 4) EXIT                                          *
* 5) 58                                            *
* 6) L                                             *
*                                                  *
****************************************************
* Question #5                                      *
*                                                  *
* What is the first ASMPGM machine code            *
* instruction to be executed by ASMPGM?            *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) B       SETUP                                 *
* 2) CSECT                                         *
* 3) RO	     EQU 0                                 *
* 4) STM     RETREG,BASEREG,12(SAVEREG)            *
* 5) USING   ASMPGM,ENTRYREG                       *
* 6) 47F0F014                                      *
*                                                  *
****************************************************
* Question #6                                      *
*                                                  *
* Which ASMPGM register contains the result of     *
* the add instruction?                             *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) 1                                             *
* 2) 2                                             *
* 3) 3                                             *
* 4) 7                                             *
* 5) 15                                             *
* 6) 6                                             *
*                                                  *
****************************************************
* Question #7                                      *
*                                                  *
* What ASMPGM displacement offset is used to       *
* reference the character literal 'abc             *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) +14                                           *
* 2) +4E                                           *
* 3) +151                                          *
* 4) +228                                          *
* 5) +53                                           *
* 6) +60                                           *
*                                                  *
****************************************************
* Question #8                                      *
*                                                  *
* What ASMPGM displacement offset has the program  *
* compile date?                                    *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) +4                                            *
* 2) +20                                           *
* 3) +C                                            *
* 4) +21                                           *
* 5) +0                                            *
* 6) +14                                           *
*                                                  *
****************************************************
* Question #9                                      *
*                                                  *
* What is the ASMPGM hex displacement offset of    *
* the source code SETUP label containing STM       *
* assembler mnemonic?                              *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) +0                                            *
* 2) +C                                            *
* 3) +F                                            *
* 4) +14                                           *
* 5) +18                                           *
* 6) +32                                           *
*                                                  *
****************************************************
* Question #10                                     *
*                                                  *
* What Assembler Mnemonic places x'A' in register 9*
* as displayed at breakpoint +A2                   *
*                                                  *
* Enter the number of your answer                  *
*                                                  *
* 1) ST                                            *
* 2) 58                                            *
* 3) L                                             *
* 4) DS                                            *
* 5) LA                                            *
* 6) 41                                            *
*                                                  *
****************************************************

Enter =6 , then enter asmquiz from the ISPF Command Shell panel

Each execution of asmquiz will replace previous answers.

Next: Challenge #11

Systems Programming (SMP/E)
Part Three - Challenge #11

Background:

In a mainframe IT organization, the systems programmer plays a central role which requires diverse set of technology skills.

A systems programmer maintains both hardware and software systems and resolves system problems. Responsibilities of the job include: installation and maintenance of the systems as well as technical consultation, services, and support to the organization.

A highly experienced systems programmer:

  • develops operation procedures
  • develops systems administration procedures
  • develops application development procedures
  • assists systems administration staff with issues involving operating system services
  • assists development staff with issues involving operating system services
  • provides on-call support for any escalated issue beyond the technical capability of other groups

The system programmer installs, customizes, and maintains the operating system, and also installs or upgrades products that run on the system.

The system programmer might be presented with the latest version of the operating system to upgrade the existing systems. A software tool exists to install z/OS and z/OS software products. This software tool tracks all modifications to z/OS and z/OS software products. This software tool is Systems Modification Program/Extended (SMP/E)



Your challenge:

Use SMP/E to locate the modification level of a system module.

If you are a z/OS Systems Programmer, a responsibility includes debugging of a reported problem and providing corrective action. You isolated a problem that appears to be a defect in a specific IBM component. As a result, you contact IBM defect support to report the problem. IBM defect support works with you to drill deeper into the failing component. Ability to use SMP/E is critical to working with IBM to drill deeper into the failing component.

A systems programmer must have excellent understanding of SMP/E terminology and will as a result of repeated use of the SMP/E tool.



Overview of SMP/E Terminology

  1. Consolidate Software Inventory (CSI) - Data sets with information about installed software

    • Global Zone - Information about software "received"
    • Target Zone - Information about software "applied"
    • Distribution Zone - Information about software "accepted"
  2. Target Libraries - System component data sets for running system
  3. Distribution Libraries - System component data sets for initial installation and backout when necessary

Overview of SMP/E Software Installation and Maintenance Process

SYSMOD - System Modification

  • FUNCTION SYSMOD - Entire operating system or product consisting of many components
  • PTF SYSMOD - Fix for operating system component or product component
  • PUT - Program Update Tape, a collection of PTF SYSMODs (fixes) routinely sent to keep software maintenance current
  • RSU - Recommended Service Update, a collection of PTF SYSMODs (fixes) routinely sent to keep software maintenance current

SMP/E Commands

  1. RECEIVE - Store SYSMODs in Global Zone data sets
  2. APPLY - Install SYSMODs into Target Zone data sets and Target Libraries
  3. ACCEPT - Install SYSMODs into Distribution Zone data sets and Distribution Libraries
  4. RESTORE - Backout Target Zone and Target Library SYSMODs from previous update in Distribution Zone and Distribution Libraries

While ISPF panels for SMP/E exist, this challenge will not use the panels. The challenge will use JCL to execute the SMP/E tool.

Situation - You isolated a problem in ICHRCP00. ICH is the 3 digit unique identifier for the 'RACF Base' component. You contact IBM defect support. IBM defect support wants you to provide the last date of maintanence applied to ICHRCP00 and the PTF name applied on that date to ICHRCP00.

Step 1.
The objective is to find the 4 digit target zone name. submit jcl(listzone) . The SMPLIST DDNAME output include ZONEINDEX values. The 4 character ZONEINDEX value associated with TARGET MVS.MVST.CSI in the output. The 4 character string is the target zone name.

Step 2.
The objective is to find the 'RACF Base' FUNCTION SYSMOD. Edit jcl(listfunc) , Replace 'target_zone_name' with the TARGET zone name that was obtained from Step 1, then submit . Find the RACF Base in the SMPLIST DDNAME output using f 'RACF Base' . The FMID name value associated with the RACF Base is what is needed from the job output.

Step 3.
The objective is to find the RACF Base component, ICHRCP00, in the target zone. Edit jcl(listfmid) . Replace 'target_zone_name' with the TARGET zone name that was obtained from Step 1 and replace 'fmid_name' from Step 2. , then submit . Review job output SMPLIST DDNAME. The output has multiple entries for ICHRCP00, one entry for each time maintenance was applied to ICHRCP00. You are interested only in the last maintenance applied. Search from the bottom of the output as follows:

  • bottom
  • f 'ichrcp00' prev
  • up 8

The latest maintenance activity applied to ICHRCP00 target library component contains 'TYPE = PTF' line. To the left of 'TYPE = PTF' is the PTF name that has a UA prefix followed by a number.

Step 4.
The objective is to confirm the PTF name associated the current maintenance applied to ICHRCP00. Edit jcl(listmod) . Replace 'target_zone_name' with the correct TARGET zone name. Replace 'module_name' with module ICHRCP00, then submit . Review the SMPLIST DDNAME output and locate the last PTF that updated ICHRCP00. The PTF name has the form UAxxxxx. The RMID value in the output is the PTF name.

Step 5.
The objective is to determine the date of the last maintenance applied to ICHRCP00. Edit jcl(listptf) . Replace 'target_zone_name' with the correct TARGET zone name. Replace 'PTF_name' with the PTF name found in Step 4, then submit . The output is written to CC#####.P3.OUTPUT(#11) and the output contains the date maintenance was applied to ICHRCP00.

Next: Challenge #12

Systems Programming (Application Development Procedures)
Part Three - Challenge #12

Background:

A Systems Programmer does not know it all. However, they get very good at knowing which professional manuals to reference and figuring things out. The more they are required to figure out, the more effective they are in their Systems Programmer role.

When a Systems Programmer installs a new release of Enterprise COBOL for z/OS, they become familiar with "need to know" facts such as the COBOL target library data set names, the Enterprise COBOL for z/OS supplied compile JCL Procedures, etc.

DevOps is a popular clipped compound that emphasizes the collaboration and communication between business application developers and other IT professionals where Dev represents development and Ops represents the other IT professionals. Quite commonly the other IT professionals is the Systems Programmer in z/OS environments. This relationship has existed for many decades in the mainframe environment, long before the clipped compound, DevOps, was created as a result of implementing an "Agile Infrastructure".

Developers (Dev) are dependent upon procedures created and maintained by a Systems Programmer (Ops).

An example would be a JCL PROC (Procedure) used by developers to compile COBOL.

Your challenge:

You installed a new release of Enterprise COBOL for z/OS. You need to modify the IBM supplied JCL PROC (Procedure) that development will use to compile COBOL source code using the new release of the z/OS COBOL compiler.

The development staff requested assembler-language expansion produced in the new COBOL compiler source listings.

You are aware the COBOL compiler benefits from newer and faster machine instructions in current z System hardware.

The challenge is to modify the IBM supplied JCL PROC shipped with the new release of Enterprise COBOL for z/OS to include assembler-language expansion in the COBOL compiler source listing and specify the current z System hardware architecture for the new COBOL compiler to benefit from newer and faster machine instructions in you current z System hardware.

Things you currently know as the Systems Programmer to accomplish the challenge:

  • CC#####.JCL(COBCLG) is the new JCL PROC that business application developers will eventually use to compile COBOL source code with the new release of Enterprise COBOL for z/OS.
  • CC#####.JCL(COBTEST) is JCL that executes the COBCLG JCL PROC.
  • Enterprise COBOL for z/OS Customization Guide is SC14-7380-03
  • Chapter 2. in the Customization Guide lists the compiler options.
  • COBCLG PROC needs PARM=(options) to include assembler-language expansion in the COBOL compile source listing and to include the hardware architecture level to benefit for the current hardware faster machine instructions.
  • COBCLG PROC only has VBREF compiler option and needs two more options that can be found in Chapter 2. of the Enterprise COBOL for z/OS Customization Guide.
  • z/OS command D M=CPU will display the z System hardware number that can be used to specify the PARM=(options) architecture level in the new Enterprise COBOL for z/OS compile.

submit jcl(cobtest)

Review cobtest COMPILE SYSPRINT DDNAME

Edit CC#####.JCL(COBCLG) PARM=options to include 1) assembler-language in the COBOL COMPILE SYSPRINT and 2) z System hardware level of the contest z System.

Use Chapter 2. in the Customization Guide to find needed COBOL compiler options.

submit jcl(cobtest) to test added options

Once the COBOL COMPILE SYSPRINT includes the needed compile option changes, then tso submit jcl(cobtest#) to direct the compile sysprint output to P3.OUTPUT(#12)

Next: Challenge #13

z Systems Hardware has 5 Unique Operating Systems
Part Three - Challenge #13

Background:

The contest z/OS system is one of many operating systems running on the contest z Systems hardware. z Systems hardware can run 5 unique operating systems:

  1. z/OS - z Systems flagship operating system used for this contest
  2. z/VM - hipervisor operating system
  3. Linux - operates the same as other platforms
  4. z/TPF - Transaction Processing Facility
  5. z/VSE - similar to z/OS with less features and functions

Communication between the operating systems in a z Systems frame eliminates requirement for extra cables and the response is near zero because the communication path can be configured to remain within the z Systems frame

A z Systems can be hardware partitioned. This hardware partitioning is called LPAR. This LPAR hardware hipervisor is assigned CPU's, Memory, and Peripheral Devices such as disk, network, tape, etc, from the total available resource pool. A single z System is capable or running up to 85 LPARs.

Any of the 5 operating systems can run in an LPAR.

When z/VM is running in an LPAR, then 'guest' operating systems can run under the control of z/VM. z/VM is a software hipervisor. Any of the above 5 operating systems can be hosted as a virtual machine with the z/VM environment.

It is common to 100's and even 1000's of operating systems as 'guests' of z/VM. Running 8,000 virtual servers on a single system with near zero network delay between the operating systems is a fantastic industrial strength architecture.

This challenge will give you the opportunity to learn about the z/VM that is controlling the contest z/OS.

A routine exists to enter z/VM commands from your TSO/ISPF session.

Your challenge:

Enter =6 to display ISPF Command Shell panel.

Enter the following commands to display z/VM information related to this z/OS guest operating systems:

  • Q USERID
  • Q CPUID
  • Q CPUS
  • Q STORAGE
  • Q CPLEVEL
  • Q USERS
  • Q LAN ALL
  • Q NIC ALL
  • Q LAN NAME ####### DETAIL

where ####### is the VSWITCH name available from 'Q LAN ALL' and 'Q NIC ALL'

Note: Q is abbreviation for z/VM QUERY command

Checking your results:
While commands return output to your screen, it also writes output to P3.OUTPUT(#13)
Therefore, the output from the last z/VM Q command is stored in P3.OUTPUT(#13)
Success is when P3.OUTPUT(#13) contain LAN DETAILS from the VSWITCH name.

Next: Challenge #14

Encryption & Decryption Using a Pair of Keys
Part Three - Challenge #14

Background:

The importance of data encryption and decryption is increasing due to the amount of sensitive data flowing across public communication systems. Data can traverse layers of encryption and decryption. Cryptographic algorithms are used to manage public-private key pairs to accomplish data security.

Public-key cryptography

This challenge will demonstrate a very simple use of a key pair to encrypt and decrypt messages. However, the industry accepted mechanisms are significantly more complex.

Your challenge:

Completing the challenge requires decryption of an encrypted secret message. A REXX routine that was used to encrypt the 'SECRET' message is available to you. You create a REXX encryption routine from a copy of the REXX decryption routine, then modify the encryption REXX routine. The objective is to use your REXX decryption routine to decrypt the 'SECRET' and write the decrypted message to P3.OUTPUT(#14).

Challenge Details:
  • CC#####.REXX.CLIST(ENCRYPT) is the REXX routine used to encrypt the message
  • Copy CC#####.REXX.CLIST(ENCRYPT) into CC#####.REXX.CLIST(DECRYPT)
  • The public and private encryption key is NOT hard coded in the REXX routine
  • REXX routine reads the public and private key from partitioned data set member
  • REXX routine assumes first line of an encrypted message is a public key
  • The REXX 'say' statement can be used to display variables to help understand REXX routine syntax and logic
  • Internet has lots of REXX tutorials and information
  • REXX EXECIO statement is the API to read and write data
    1. EXECIO STEM operand establishes an array of data records
    2. Example 'STEM msg.' where 'msg.' is the STEM array, msg.1 is the first entry in the array, msg.2 is the second, and msg.i is used to increment array position by incrementing i.
ENCRYPT REXX Routine Helpful Facts
  • ZOS.PUBLIC.SECRET.MESSAGES(TESTMSG) is the ENCRYPT REXX routine test input
  • ENCRYPT REXX routine created encrypted text located in ZOS.PUBLIC.SECRET.MESSAGES(TESTMSGX).
  • ENCRYPT REXX routine was used to produce encrypted message in ZOS.PUBLIC.SECRET.MESSAGES(SECRET).
  • Enter ex to the left of member ENCRYPT in CC#####.REXX.CLIST to execute and observe encryption.
  • Remember that REXX say statement can be helpful by displaying variable values during execution.
  • DECRYPT REXX routine can use TESTMSGX as input with the objective to decrypt TESTMSGX writing text found in TESTMSG to the screen.

When DECRYPT routine is successfully decrypting TESTMSGX:
  • Modify DECRYPT routine to decrypt ZOS.PUBLIC.SECRET.MESSAGES(SECRET)
  • Modify DECRYPT routine to write the decrypted 'SECRET' message to P3.OUTPUT(#14)
  • Observe comments in REXX routine to comment 'ALLOC FI(msgout) DA(*)' and uncomment 'ALLOC FI(msgout) DA(P3.OUTPUT(#14)'

Next: Challenge #15

Data Analytics using DB2 Relational Database
Part Three - Challenge #15

Background:

Data Analytics will increase in importance during the next couple of years using technologies such as z/OS Apache Spark. SQL provides analytic functions today where z/OS Apache Spark will provide SQLContext capability for read-only data in memory, dataframes, coupled with machine learning algorithms in the near future. Therefore, your SQL skills will enable you to quickly become skilled with technologies such as z/OS Apache Spark.

Your challenge:

This challenge has neither a right or wrong answer. This challenge requires innovative business insights about data using SQL.

This challenge assumes your ability to use interactive DB2 SQL from Part 2 Challenge 11 and 12. You will not be given detailed "how to" instructions in this challenge.

The data for this challenge is two DB2 tables, a client profile table (CLIENT_INFO) with approximately 6000 clients and a payment table (SPPAYTB) with approximately 1.5 million card transactions from the 6000 clients over a 1 year period.

DB2 table CARDUSR.CLIENT_INFO

Column Names - ( Description )
  • CONT_ID - ( Client contact ID )
  • GENDER - ( Client gender where 0 or 1 only )
  • AGE_YEARS - ( Client age )
  • HIGHEST_EDU - ( Client education where 1 is lowest and 5 is highest )
  • ANNUAL_INVEST - ( Client savings )
  • ANNUAL_INCOME - ( Client income )
  • ACTIVITY_LEVEL - ( Client physical activity where 0 is lowest 5 is highest)
  • CHURN - ( Client retention where 0 is retained and 1 is cancelled card )

DB2 table CARDUSR.SPPAYTB

Column Names - ( Description )
  • ACAUREQ_HDR_CREDTT - ( Transaction data and time )
  • ACAUREQ_AUREQ_ENV_A_ID_ID - ( Card Issuer )
  • ACAUREQ_AUREQ_ENV_M_ID_ID - ( MDM + MC + # )
  • ACAUREQ_AUREQ_ENV_M_CMONNM - ( Common Merchant Name )
  • ACAUREQ_AUREQ_ENV_CPL_PAN - ( Card Primary Account Number)
  • ACAUREQ_AUREQ_ENV_C_CARDBRND - ( Card Brand )
  • ACAUREQ_AUREQ_TX_MRCHNTCTGYCD - ( Merchant Codes, MC )
  • ACAUREQ_AUREQ_TX_DT_TTLAMT - ( Amount of purchase )
  • CONT_ID - ( Client contact ID )
  • MDM_POSTAL_CODE_ID - ( Master Data Management, MDM )
  • AGE - ( Grace period in days to pay with zero interest )
  • AUTHORRESULT_RSPNT - ( Authorization Request/Response )
  • FRAUD_VER_RESULT - ( Transaction Fraud Y/N )

Notes:

  • Merchants Code - no additional information available
  • Master Data Management - no additional information available
  • Authorization Request/Response - all transactions are approved

Given:

  • CC####.SQL(TBLVIEWS) - SQL provided for reviewing table data
  • CC####.SQL(EXAMPLES) - Examples of gathering insights from data
  • CC####.SQL(ANALYSIS) - Initially contains comments
  • CC####.SQL(ANALYSIS) - Modify to include SQL SELECT statement per instructions

CC####.SQL(ANALYSIS) can contain at least 1 and no more than 5 SELECT SQL statements that provide business insights from the client and payment tables. Each SELECT statements may contain subselects, built-in functions, case expressions, search conditions, joins, etc., etc. A SELECT SQL statement is anything that begins with SELECT and ends with semicolom (;), the SQL statement terminator.

Suggested sources of information (hyperlinks) for this challenge:

Your SELECT statement(s) will be judged according to the business insights in the result set. This challenge will help judges select the top place finishers for all those that have successfully completed all the Part 3 challenges. Completing this challenge along with the other Part 3 challenges will most likely earn you at least an Honorable Mention on the Part 3 Wall of Fame.

Once done, then:

  • edit cc#####.p3.output(#15)
  • copy sql(analysis)
  • F3 - save and exit

Next: Challenge #16

Scoring & Selecting Winners
Part Three - Challenge #16

Congratulations!

Completing Part 3 makes it highly probable your name and school will be posted in Part 3 Wall of Fame. This is a distinguished and relatively short list. You will be in this list at least as an Honorable Mention and possibly as a top place winner.

How can I know if I have everything 100% accurate?

Bottom line: You cannot. No routine is provided to score the answers you submitted. Additionally, the last challenge is very open ended for the purpose of distinguishing yourself from other contestants by doing something innovative with the data to produce business insight reports that may influence business decisions.

The last challenge is scored by a judge panel. The judges independently score each of the contestants suggested business insights from the data. The judge scores are averaged to select the winners.