Sorting and Reporting
This project is intended to emulate how IT management of data needs to integrate with business requirements for viewing and interpreting that data.
In this (highly simplified) environment, you work for the IT department of LRC, a large retail sector company which has just acquired a small company called IC&U. (Neither of these names represent real companies of course.)
This new acquisition sells ice cream and umbrellas and had its own IT systems which now need to be integrated into your own organisation. As is almost always the case in real life, their IT environment is very different from your own.
Sales data from IC&U has now been made available to your own system and is updated daily. As the next step in integration, you are asked to arrange for some standard sales reports to be generated from the new data. In order to do this you need to familiarise yourself with IC&U’s datasets and the software available to manipulate it (see Details below) and then:
1. Produce some simple reports in a manner which can be automated and managed by your batch environment.
2. Document the production of those reports so that your colleagues can implement and automate the report generation in production.
The IC&U data referred to above is the UNIUWS.TRDATA dataset and the software is DFSORT and ICETOOL. The columns of the dataset are as follows:
Field Column Range Data Type
Shop Location 1 17 CH
Year 19 4 ZD
Month 24 2 ZD
Ice cream sales 27 7 ZD
Umbrella sales 35 7 ZD
1. The “Column” and “Range” numbers above show the starting column (1 is the first column of the dataset) and the length of the field. This fits well with the syntax that DFSORT uses.
2. You can use ordinary ISPF “Browse” to view the contents of UNIUWS.TRDATA. For example, you can use ISPF 3.4, put UNIUWS in the “Dsname Level” field, hit Enter then scroll down and put “b” in the Action field against UNIUWS.TRDATA. It is sometimes useful to use the fact that your 3270 emulator will always display the row/column position of your cursor in the bottom right of the screen whenever you are using a full-screen application. For example, a display of “013/004” means that your cursor is at row 13 column 4 of the screen.
3. The Location field uses character data (referred to by DFSORT as “EBCDIC Character Data“, type CH) and the other fields are positive numeric fields that DFSORT can manipulate using type “Zoned Decimal” (ZD).
4. The column ranges listed above for the numeric fields include enough leading blanks (treated as numeric leading zeroes) that the column sums you need to do will fit without needing to resize them.
5. The dataset is FB (Fixed-blocked) with a logical record length (LRECL) of 41 characters and a blocksize (BLKSIZE) of 27962.
6. None of the reports take a huge number of lines and you can simply send the output to sysout instead of specially created datasets. For example
//SORTOUT DD SYSOUT=*
will send the sorted output to where you can conveniently select it in SDSF by using “?” against your jobname and then “s” against the resulting SORTOUT DD to see what it contains. The SYSOUT DD from a DFSORT step tells you all about what it did to sort your data.
The reports you are asked to generate are these:
1. For each (year, month) pair (e.g. January 1980, July 2005), sum up ice cream sales.
2. For each (location,month) pair, produce an overall sum of ice cream sales (sum over all years).
3. For each month, report the best performing location for ice cream sales.
4. Repeat 1-3 above with the umbrella column instead of ice-cream.
It is suggested you do each in three stages.
1. Do a simple DFSORT producing the columns, sums and data you need for the ice-cream report.
2. Repeat for umbrellas by changing column ranges as appropriate.
3. Use ICETOOL to produce something in a report format rather than just raw data.
For report number (3) above (the best performing location), it is recommended you use two sort steps to produce the result. If you want to do this, you can write the results of the first sort step to a temporary dataset (which will have the same fixed record length as UNIUWS.TRDATA itself) by using the following JCL
//SORTA EXEC PGM=SORT
//SORTOUT DD DISP=(NEW,PASS),DSN=&&TEMP,
and then use that temporary data as input for sorting in the following step by using JCL
//SORTIN DD DISP=(OLD,DELETE),DSN=*.SORTA.SORTOUT
The SORTA in “DSN=*.SORTA.SORTOUT” highlighted above refers back to the step name you choose that contains the DD for the temporary dataset you want to reference.
Library and Manuals
Manuals are available from the z/OS Internet library at http://publibz.boulder.ibm.com/bookmgr_OS390/libraryserver/zosv1r9/ .
You are most likely to want the DFSORT Getting Started book and the DFSORT Application Programming Guide.
All information and message error codes for DFSORT are documented in the DFSORT Messages Codes and Diagnosis Manual. If you have JCL problems that you can’t resolve by looking at existing examples (unlikely), the MVS JCL User’s Guide and MVS JCL Reference are available.
Given any message code (of the form ABC1234I for an informational message, ABC1234W for a warning or ABC1234E for an error), you can go to http://www-03.ibm.com/systems/z/os/zos/bkserv/lookat/ and type in the message code, choose z/OS V1R9 and click “Go” to find the documentation for that message.
The deliverables for this part of the project correspond to the two numbered parts of the Requirements section above
1. For the sales reports batch jobs (as detailed in the Reports section), you need to have six separate pieces of JCL. These could be, for example, members REP1A, REP1B, REP1C, REP2A, REP2B, REP2C of your UWSnnnn.JCL PDS or of a separately created PDS such as UWSnnnn.LRP.JCL. Doing a “SUBMIT” on each member needs to run your report generation job successfully (with return code 0) and produce the required report in a sysout (…DD SYSOUT=* is fine) that can be browsed with SDSF.
2. The documentation for the report generation should describe briefly how your jobs work, the steps involved, and how and why the DFSORT data manipulation options and ICETOOL report options you chose to use produce the required results.
The working reports must be demonstrated during class on the last week of the module. Marks will be awarded for the successful demonstration of the reports as well as their contents. There is no other opportunity for this demonstration.
The documents should be in PDF or Microsoft “.doc” format. The report should be professional in appearance including using proper formatting, headers, etc. In addition each page should be numbered and marked with your Banner ID.