Use these libraries as a starting point:(Copy contents of these datasets to your location)

KC02677.STUD.TEST.DATAIN  -> data input

Using the data provided, write code to answer these questions:

1. What is the total amount of all transactions in the file?

2. Which shop is the most popular (most visited), and how much money was spent there in total?

3. What is the most common first and last name among the customers?

4. How much money did all customers spend each day (daily totals)?

5. What does each customer usually buy, and how many items did they buy?

Provide PDS name where you stored your Rexx code. Only files on the host will be evaluated

Create backup of recently loaded data. You can use sample JCL that have been provided during this course

• JOB NAME and JOBID 

 Delete data from new table and restore (RECOVER) it using created backup. You can use sample JCL that have been provided during this course

• JOB NAME and JOBID 

Use details provided bellow to upload data to the table.

Data file name: KC02677.STUD.DB2.TEST1.DATA

Punch card dataset: KC02677.STUD.DB2.TEST1.PUNC

• JOB NAME and JOBID 
• Punch card dataset name

• JOB NAME and JOBID 
• file where you have saved DDL 

What is the access path of the following query?  What was the value of  "Estimated Serv. Units" or PROCMS and PROCSU column value(copy/past the results)

SELECT *                   

FROM SYSIBM.SYSCOPY        

WHERE DBNAME = 'DVILMSTR'  

AND TSNAME = 'SKS05'       

AND ICTYPE = 'F'           

Which two type of files you need to supply to LOAD utility to be able successfully load data into table? 

What's the name of SQL language statements subgroup, which is used to control user authorization on the system? 

Which sample IBM Db2 program we use to execute SQL statements in batch? 

Mainframe technologies JCL part test

Task background:

Using transaction files sent from card terminals and ATMs located at one of local supermarkets, you need to produce aggregated transaction file. This file should include only those transactions that were made by our VUBA Bank client cards.

Files for your JCL input:

Transaction files:

·         KC02677.STUD.DATA.LEDOATM

·         KC02677.STUD.DATA.LEDOVISA

·         KC02677.STUD.DATA.LEDOMAST

·         KC02677.STUD.DATA.LEDOPDQ

VUBA Bank client card numbers:

·         KC02677.STUD.DATA.OURCARDS

Tasks (order in which you will implement these tasks is not important):

1.       Merge all transaction files into single file. Give a meaningfull name for it, something like: KC03%%%.DATA.TRAN.MERGED (2 points)

2.       Sort and aggregate transaction file. Note that transaction amount is written in packed decimal format, so you would need yo use PD option in your control statement. Transaction amount starts at column 64 (2 points)

3.       Produce two files which will contain aggregated transactions made by our VUBA Bank clients and others. Give files a meaningful name, something like: KC03%%%.DATA.TRAN.FINAL.OURCARDS, KC03%%%.DATA.TRAN.FINAL.OTHER (2 points)

4.       Create procedure to which you could pass needed input and output file names to get this final transaction file (2 points)

5.       If any of the steps in JCL returns RC > 0, then branch to ERROR step and produce RC 20 from it (2 points)

For every step number please provide executed JOBNAME and JOBID, Location of the OUTPUT Datasets and location of JCL