Beginning PRF for Student Data

1
Beginning PRFfor Student Data

• Session 1

2
Objectives

• Learn how to access PRF
• How to navigate and list queries and forms
• How to add a query of our own

3
Accessing PRF in CICS

• On the Nevada Screen enter A for User
  Applications
• Enter your user id and password to log onto CICS
• Enter PRF (Platinum Report Facility) at the CICS
  Primary Option menu and press Enter
• ----------------------- CICS/MVS PRIMARY OPTION
  MENU -------------------------
• OPTION gt prf
  
• 
  USERID - PJO
• 1 STUDENT APPLICATIONS
  DATE - 10/09/02
• 2 PERSONNEL APPLICATIONS
  TERMID - 0070
• 3 FINANCIAL APPLICATIONS
  APPLID - DBDCCICS
• 4 PURCHASING/INVENTORY APPLICATIONS
  
• 5 COOPERATIVE OCCUPATIONAL EDUCATION (COE)
  
• 6 MANAGEMENT INFORMATION SERVICES
  APPLICATIONS
• 7 FACILITIES APPLICATIONS
  
• 8 CCSD ELECTRONIC MAIL SYSTEM (SYSM)
  
• 9 SCHOOL/FACILITY PROFILE APPLICATIONS
• At the next screen enter 1 for PRF and press
  Enter
• To exit, press F3 back to Platinum Report
  Facility System Services, F3 again to a blank
  screen, and F3 again back to CICS Primary Option
  Menu

4
Three PRF Object Types

• Query a statement sent to DB2 asking it to
  retrieve data. You define the data you need on
  the PRF Build Query screen, and it is translated
  into an SQL (Structured Query Language) SELECT
  statement.
• Form a format for how you want the results of
  your query presented to you. You define the form
  on the Build screen for the type of form you
  want, such as Column form, Letter, Mailing
  Labels, or Extract File. Every form must have an
  underlying query.
• Procedure a series of commands to run queries
  and/or forms, combined to complete an entire task.

5
Listing Queries, Forms, and Procedures

• Enter L under OPT on the appropriate line.
• If you leave a blank under NAME, PRF will show a
  list of the queries, forms or procedures
  associated with your user ID on the Library
  Screen. If you dont have any, you can enter the
  user ID of a colleague who uses PRF, followed by
  ., or you can enter an asterisk and press enter
  to see all the PRFs that are designated as
  Shared. Press Enter.
• PRFM.01 R04.01 -------- PLATINUM REPORT
  FACILITY -------- 09/30 1353
• COMMANDgt
  
• 
  
• 
  
• OPT NAME
  
• l QUERY ...................... pjo.
  
• FORM .......................
  
• PROCEDURE ..................
  
• 
  
• New object name for C/N options...
  
• 
  
• 
  DB2 subsystem DSN
• --------------------------------------------------
  ----------------------------
• Enter one of the following options
  
• A - Add B - Batch C -
  Copy D - Delete
• E - Edit F - Freeform L -
  Library N - reName

6
Library Screen

• QYLB.02 R04.01 --------------- QUERY LIBRARY
  --------------- 09/30 1357
• COMMANDgt
  USER ID SYSAD2
• 
  
• NAMEgt CREATORgt PJO
  TYPEgt SELgt N
• 
  
• Options-gt S -Select R -Run D -Delete U
  -Undelete B -Batch I -Information
• QUERY NAME SHR AREA
  DESCR
• PJO.BLANK_REGIONS Y
  ...............................
• PJO.NYR_ENROLL Y
  ACTIVES WITH ENTERDATE OTHER TH
• PJO.NYR_ENROLL17 Y
  ENTERDATE OTHER THAN 1ST DAY AF
• PJO.NYR_WITHDRAW Y
  ACTIVES WITH ENTERDATE OTHER TH
• Name can be used to select a particular query or
  queries Note the asterisk by itself is a wild
  card, but with part of the name, you need to use
  the SQL wild cards, _ or . NYR in Name could
  be used to select only the queries beginning with
  NYR, above.
• Creator is the user ID of the person who owns the
  PRF
• Type, used for forms only, selects a particular
  type of form, such as Labels.
• Typing Y in SEL accesses an advanced search
  screen

7
Using Commands on the Library Screen

• SHR designates an object as available for others
  to use. When SHR is U others can run and update
  the object, when Y, others can run but not
  update. When N, only the creator can run or
  update.
• Description is optional, but recommended
• To see more information, on the Library Screen,
  press F10. You will see the dates created and
  modified and by whom, and whether parameters are
  required at run time.
• To Select an object for edit, enter S.
• To Run, enter R to run interactively, or B to set
  up a batch job.
• To Delete or Undelete, enter the appropriate
  letter under OPT next to the desired object.
  (Warning about Undelete it only works while you
  are still on the Library Screen and have just
  Deleted the object. Once you leave the screen,
  the object is gone)
• To view Information about the object, enter I.

8
Getting Help in PRF

• Press F1 for help anywhere in PRF. Place the
  cursor on the entry field for which you need
  help.
• Follow directions on help screens to drill into
  specific topics or areas of the screen.
• Press F1 twice for menu of help topics.
• Press F12 for list of function keys active on the
  current screen.

9
Viewing Comments for an Object

• To see or enter comments for an object, enter I
  (Info) next to the desired object on the library
  screen. A numbered list of comment lines
  appears, with the instructions to enter EDIT on
  the command line to make changes. Comments can
  be entered here and saved with your PRF object.
• INFO 03 R04.01 ------------ PRF Object
  Information ------------ 09/30 1521
• COMMANDgt
  
• PRF1155I Use the EDIT command for updates.
  
• Object PJO.NYR_ENROLL Type
  QERY Last Update 2002-09-30
• 1. List of counts of new enrolls for each
  school, track,
• 2. grade, and gender before the first day of
  school, 2002,
• 3.
  
• 4.

10
Running a Query or Form

• To run an object, enter R next to the desired
  object on the library screen. The Run Query or
  Run Report screen is displayed.
• Set row limit if you want just a sample of the
  report
• Change replaceable parameters if necessary
• Press Enter to run the report
• RUNR.02 R04.01 ---------------- RUN REPORT
  ----------------- 10/09 1234
• COMMANDgt
  SYSAD2.DEV_TABLE_COLS
• 
  
• press ENTER to run
  report
• 
  
• QUERY USED BY THIS FORM SYSAD2.DEV_TABLE_COLS
  ROW LIMIT NONE
• 
  
• 
  
• 
  
  
  
• DO YOU WISH TO VIEW OR UPDATE THE
  REPLACEABLE PARMS? NO

11
Lab Navigating in PRF

• Sign onto CICS and initiate PRF
• List your queries or those of someone who has
  some, if you do not. (Try user PJO or SYSAD2)
• Copy query SYSAD2.STU_DEMO_A to yourID.somename
  (Hint PRF puts your ID in front of your query
  name automatically.)
• Add a comment so you remember where you got it
• Look at the help screen for the PRF Query Library
  screen and the menu of help topics
• Run the new query you just created by copying
  STU_DEMO_A . Use a row limit of 20.

12
Adding a New Query

• To create a new query, enter A(dd) on the
  Platinum Report Facility screen under OPT next to
  QUERY
• Enter a name for the query under NAME
• Pressing enter brings you to the Select Tables
  screen
• PRFM.01 R04.01 -------- PLATINUM REPORT
  FACILITY -------- 09/30 1617
• COMMANDgt
  
• 
  
• 
  
• OPT NAME
  
• a QUERY ...................... demo1
  
• FORM .......................
  
• PROCEDURE ..................
  
• 
  
• New object name for C/N options...
  

13
Selecting a Table

• SLTB.03 R04.01 ---------------- SELECT TABLES
  --------------- 09/30 1606
• COMMANDgt
  PJO.DEMO1
• 
  
• NAMEgt IDgt SSASIDB1
  LOCgt LOCAL SELgt N
• TYPEgt
  
• 1 (T) SSASIDB1.AADD_DISCODES LABEL
  Discipline Description
• 2 (T) SSASIDB1.AATC_CALENDAR LABEL
  School year calendar
• 3 (T) SSASIDB1.AATD_DAILY_ATTEND LABEL
  Daily Attendance
• 4 (T) SSASIDB1.AATP_PERIOD_ATTEND LABEL
  Period Attendance
• 5 (T) SSASIDB1.AATR_ATT_REASONS LABEL
  Attendance Reasons
• Enter the database name for the tables you need
  next to ID. For example SSASIDB1 for the SASI
  tables. Press enter and you will see a list of
  tables in that database.
• The NAME field may be used to refine your search
  through a long list of tables. It defaults to ,
  for all the tables. You may enter part of a
  table name and the _ or sign, as used in SQL.
• The FIND command may be used on the command line
  to further refine your search, or enter Y in the
  SEL field to do a more advanced search.
• When you have found the table you need, enter S
  and press enter to select it. The BUILD QUERY
  screen will appear.

14
Select Columns for the Query

• .
• BLDQ.02 R04.01 ---------------- BUILD QUERY
  ----------------- 10/09 1423
• COMMANDgt
  SYSAD2.DEMO1
• 
  
• Distinct N Shrink N Location LOCAL
  
• SEL SSASIDB1.ASTU_STUDENT ORD GRP FUNC
  WHERE ..... using AND
• 1 13. FIRSTNAME ............ C
• 2 14. MIDDLENAME ........... C
• 15. OTHERNAME ............ C
  
• 16. NICKNAME ............. C
  
• 17. BIRTHDATE ............ D
  
• 5 18. GENDER ............... C
  
• 4 19. GRADE ................ C
  
• 20. TRK .................. C
  
• 21. ETHNICCODE ........... C
  
• 22. PRNTGUARD ............ C
  
• 23. MAILADDR ............. C
  
• 24. CITY ................. C
  

15
Define a WHERE Clause

• BLDQ.02 R04.01 ---------------- BUILD QUERY
  ----------------- 10/09 1438
• COMMANDgt
  SYSAD2.DEMO1
• 
  
• Distinct N Shrink N Location LOCAL
  
• SEL SSASIDB1.ASTU_STUDENT ORD GRP FUNC
  WHERE ..... using AND
• 1. CCSDSTATUS ........... C
  ' '
• 2. SCHOOLNUM ............ C
  '945'
• 3. STULINK .............. N
  
• 4. ENTERDATE ............ D
  
• 5. ENTERDATE ............ D
  
• 6. LEAVEDATE ............ D
  
• 7. ENTERCODE ............ C
  
• 8. LEAVECODE ............ C
  
• 9. PERMNUM .............. C
  
• 10. SOCSECNUM ............ C
  
• 11. FAMILYNUM ............ N
  
• 3 12. LASTNAME ............. C
• --------------------------------------------------
  ----------------------------
• SELECT FIRSTNAME, MIDDLENAME, LASTNAME, GRADE,
  GENDER, SPEDELIG,

16
Some Query Hints

• Change using AND to using OR as needed
• Use the ORD column to indicate sort order
• Enter WHERE on the command line for more room in
  the WHERE area. If there is still not enough
  room, place the cursor on the WHERE clause you
  need to enter and press F1. The Where Condition
  screen is displayed.
• To remove a table from your query, enter DETACH
  on the command line, followed by the letter
  designation of the table you want to remove. To
  add a different one, use ATTACH to get back to
  the table selection screen.
• If you have a very complicated query or
  something PRF cant handle, such as a UNION,
  enter EDIT QUERY on the command line for a
  freeform area to type your query manually.

17
Test the Query

• Enter RUN on the command line
• The RUN QUERY screen appears, offering a choice
  of ROW LIMIT and FORM. Since we havent created
  a form for our query yet, our only choice is the
  DEFAULT FORM, which displays the data just as it
  is returned from DB2.
• Use ROW LIMIT as a means of testing the query on
  just a few rows, without getting back all the
  results.
• Press Enter to run the query.
• RUNQ.03 R04.01 ------------------ RUN QUERY
  ----------------- 10/01 1135
• COMMANDgt
  SYSAD2.DEMO1
• 
  
• 
  
  
  
• ROW LIMIT 30
  
• 
  
• USING FORM ..... DEFAULT FORM
  
• 
  
• Forms that can be used by this Query NO FORMS
  BASED ON THIS QUERY

18
Add a Description

• Enter PROFILE on the command line to access the
  Query Profile screen
• Description is very handy once you accumulate
  several queries and forms
• OPRO.03 R04.01 -------------- QUERY PROFILE
  -------------- 10/01 1400
• COMMANDgt
  SYSAD2.DEMO1
• PRF0102I Enter short description
  
• 
  
• DESCRgt a demo query for class..............
  .......
• 
  
• SHAREgt Y
  
• "Y" - others can use - but not update - your
  QUERY
• "N" - your QUERY cannot be referenced or
  copied by others
• "U" - others can use - and update - your QUERY

19
Using Groups and Functions

• Use the GROUP BY clause in SQL to select summary
  data instead of a row for every detail line
• For example what if we want to find the number
  of students with each type of special ed
  eligibility and placement? Instead of printing a
  line for every student, we print one line per
  special ed code and placement showing the total
  number of students in that group.
• Use Functions to perform operations on grouped
  data
• For example we will use the count function.
  Examples of other functions are max, min, avg,
  and sum.

20
Using Groups and Functions

• BLDQ.02 R04.01 ---------------- BUILD QUERY
  ----------------- 10/09 1508
• COMMANDgt
  SYSAD2.DEMO2
• 
  
• Distinct N Shrink N Location LOCAL
  
• SEL SSASIDB1.ASTU_STUDENT ORD GRP FUNC
  WHERE ..... using AND
• 1. CCSDSTATUS ........... C
  ' '
• 2. SCHOOLNUM ............ C
  '945'
• 3. STULINK .............. N
  
• 4. ENTERDATE ............ D
  
• 5. ENTERDATE ............ D
  
• 6. LEAVEDATE ............ D
  
• 7. ENTERCODE ............ C
  
• 8. LEAVECODE ............ C
  
• 3 9. PERMNUM .............. C COUNT
  
• 10. SOCSECNUM ............ C
• ---------
• ---------
• 97. BUSINFO .............. C
• 98. ORIGYRGRAD ........... C

21
Lab Building a Query

• Build a query called LATE_ENROLLMENTS to list
  active students at Martin Elementary School (232)
  whose enterdate is later than 08/25/2003.
• List each students first, last and middle names
  and enterdate
• Order by enterdate
• Place the cursor in an entry field under the ORD
  column and press F1 for help to sort the
  enterdates in descending order instead of
  ascending.
• Optional Build a query to select from a single
  student table information you might use in your
  daily tasks.

22
Summary

• So far, we have learned to navigate in PRF and
  create a simple query.
• Context-sensitive help is available by pressing
  F1 with the cursor on the entry field of
  interest.
• Be aware of the amounts of data in your tables
  and be sure you have defined a WHERE clause to
  limit the data you will select.
• In the next sessions we will learn about the
  Student data tables and create a form to enhance
  the appearance of our results.