Beginning PRF for Payroll Data

1
Beginning PRFfor Payroll Data

• Session 2

2
Objectives

• Learn about relational tables
• Learn about Payroll data tables
• How to join tables in a query

3
Relational Tables Store Info about Business
Entities

• In a relational data model, each table stores
  information about one kind of business entity
• The MSPAY_ID table stores information about each
  employee
• The MSPAY_JOB table stores information about each
  job an employee has
• The MSPAY_DEDUCTIONS table stores information
  about each payroll deduction an employee has
• The POS_CODE table stores the code and an English
  description of each type of job position
• The attributes of the business entity are columns
  in the table
• An employee has first name, last name, address,
  birthdate, marital status and number of
  deductions for federal taxes, etc
• A job has a start date, eval code and date,
  number of hours/day
• A code has a description of what the code means
  and salary schedule range
• Entities have a unique identifier, called a
  primary key, that distinguishes them from others
  of the same kind.
• Employee social security number
• Job social security number and job number
  (1,2,3 etc.)
• Position code code and employee group

4
Business Entities have Relationships with one
another

• Because we are looking at Payroll data, most of
  our entities are related to the employee,
  represented by the mspay_id table
• An employee performs 1 or more jobs,
• has 1 or more types of earnings,
• has several payroll deductions
• Each type of job has 1 position code
• Relationships reflect the business rules of the
  organization

5
A Partial Model of Payroll Data
1 employee has 0 to several types of deductions
Employee
Deduction
1 employee has 1 to several jobs
1job type has 1 position code and description
Job
Position Code
Entity-Relationship Diagram shows the business
entities and the relationships between them
6
Foreign Keys Link Data from Table to Table
MSPAY_ID table
MSPAY_JOB table
POS_CODE table
7
Indexes Help DB2 Find Data Faster
50
Index pages
76, 100
30, 50
1 1,1 2 1,2 . . 302,10
77 8,13 . . 100 10,4
51 5,5 . . 76 8,12
31 2,6 32 2,7 . . 50 5,4
Index leaf pages point to page , row of data
Employee 1 Employee 2 . . Employee 25
Employee 26 Employee 27 . . Employee 41
Employee 100
Data pages
. . .
8
Database Reports You can Run

• PRFM.01 R04.01 -------- PLATINUM REPORT
  FACILITY -------- 09/30 1353
• COMMANDgt
  
• 
  
• 
  
• OPT NAME
  
• QUERY ......................
• l FORM .......................
  sysad2.dev
• PROCEDURE ..................
  
• 
  
• New object name for C/N options...
• FMLB.02 R04.01 --------------- FORM LIBRARY
  ---------------- 10/10 1333
• COMMANDgt
  USER ID SYSAD2
• 
  
• NAMEgt DEV CREATORgt SYSAD2
  TYPEgt SELgt N
• 
  

9
Creating Select Statements

• SQL (Structured Query Language) uses English-like
  phrases to access data
• SELECT names table columns or expressions that
  you want in the result
• FROM names the database tables where the data
  is located
• WHERE names criteria for which data rows you
  want
• ORDER BY tells what sort order you want the
  data returned in
• GROUP BY tells how to combine rows of data for
  a summary report when you dont need rows of
  detail
• INNER JOIN ON tells how to relate tables to
  each other when you need data from more than one
  at a time
• PRF helps make these clauses more user-friendly,
  but you still need to understand what data you
  are going after and how it is stored.

10
Payroll Data

• Payroll is run every other week for some
  employees and bimonthly for others.
• At the completion of each run, payroll data is
  loaded into the payroll DB2 database so refreshed
  data is available the day following the day
  payroll is run.
• The amounts are stored as current, month-to-date,
  quarter-to-date, and year-to-date.
• Below are part of several rows from
  MSPAY_ACCUMULATOR
• NET_CUR_WAGE NET_MTD_WAGE NET_QTD_WAGE
  NET_YTD_WAGE
• ---------------------------------------------
  ------
• 1634.68 1634.68 4343.92
  26828.77
• 1647.20 1647.20 4941.60
  35003.87
• 735.01 735.01 3017.54
  12404.66
• 1390.81 1390.81 4773.70
  31589.79
• 1196.32 1196.32 3588.96
  24599.04
• 918.81 918.81 2756.43
  17283.01
• 997.81 .00 .00
  .00

11
Payroll Data cont.

• An employee may have more than one job.
• In MSPAY_ID, num_of_jobs shows the number of
  positions each employee has
• There is a row in MSPAY_JOB for each position.
• When these two tables are joined, some employees
  will have more than one row, as below
• FNAME NUM_OF_JOBS
  POS_CODE START_DATE
• --------------------------------------------------
  --------------------
• MARY 00002
  5000 08/18/1994
• MARY 00002
  5000 06/08/2001
• JOHN 00003
  8130 08/13/1992
• JOHN 00003
  E047 10/01/2001
• JOHN 00003
  8100 06/04/1999
• SUSAN 00002
  2890 08/15/1991
• SUSAN 00002
  E047 09/27/1999
• MIKE 00002
  3260 08/11/1999
• MIKE 00002
  8100 08/23/2002

12
Payroll Data cont.

• To create a report with only 1 row per employee,
  select each employees primary job
• Join on both columns
• soc_sec_num of MSPAY_ID soc_sec_num of
  MSPAY_JOB
• And pri_job_num of MSPAY_ID job_num of
  MSPAY_JOB
• FNAME NUM_OF_JOBS
  POS_CODE START_DATE
• --------------------------------------------------
  --------------------
• MARY 00002
  5000 08/18/1994
• JOHN 00003
  8130 08/13/1992
• SUSAN 00002
  2890 08/15/1991
• MIKE 00002
  8100 08/23/2002
• ROBERT 00003
  6050 08/13/1997
• TONYA 00002
  1020 08/12/1998

13
Payroll Data cont.

• To select active employees
• Look for a termination/leave of absence code of
  blank
• Or, termination/leave of absence date in the
  future
• A particular job can be active or inactive, too
• Job_status of MSPAY_JOB is A Active
• N Inactive
• S Summer (This means the employee is not
  working due to summer vacation, but the position
  is still active.)

14
Joining Two Tables in a Query

• To add a second table to the query, enter ATTACH
  on the command line
• On the JOIN CONDITIONS screen, choose the columns
  for joining the tables.
• Once this is done, build your query in the same
  way you do for a single table query.
• JCON.03 R04.01 -------------- JOIN CONDITIONS
  -------------- 11/14 1326
• COMMANDgt
  SYSAD2.PAYDEMO_JOBS2
• 
  
• ----CONDITIONS------DESCRIPTIONS------------------
  ----------------------------
• 1. 154 A.SOC_SEC_NUM B.SOC_SEC_NUM
  
• 2.
  
• 3.
  
• 4.
  
• 5.
  
• 6.
  
• 7.
  
• --------------------------------------------------
  ----------------------------
• A TPPRFDB1.MSPAY_ID 57.
  B.WORK_LOC .........CHAR 3
• 47. A.DUAL_EMP_IND .....CHAR 1 58.
  B.VAR_LOC ..........CHAR 3

15
Lab Joining Two Tables in a Query

• Sign on CICS and initiate PRF
• Create a query to print job information for
  active employees who have the position of school
  bus driver (pos code 6100). This may not be
  their primary position.
• Include first name of employee, position title,
  start date, and hours per day.
• Run the query, limiting rows to 50.
• If you have time, do the extra queries on the
  next page.

16
Lab If you have time

• Create a query to print information about active
  employees who have earned so far this year, less
  than half their gross from last year.
• (Hint Notice that mspay_accumulator has a column
  for last years gross.)
• (Hint Remember how we used a colon in front of
  a columns number to designate the column when
  testing TLOA code OR TLOA date? The same
  technique can be used to compare this years
  gross ytd page with last years..)
• Create a query to select some pay information you
  might use in your job. Write out first what
  information you will select, and what criteria
  you will select on. Then build the query.

17
Summary

• Each table in a multi-table join MUST have join
  criteria to avoid getting multiple rows
• For good performance, use index columns for
  joining and WHERE conditions whenever possible
• If you have a runaway query or time out in
  CICS, you often cannot get out of it yourself.
  Call Pam or Kathy, below, to stop your PRF
  session. Whenever you leave your desk, exit your
  PRF session!
• For help, contact
• Pam Odden 799-5040 ext. 333
• Kathy Jones, DBA 799-5040 ext. 366
• Marcia Munoz 799-5040 ext. 377