EMPPAY Sample

The EMPAY view accesses the EMPINFO and PAYINFO tables.

In this section: EMPPAY MASTER EMPPAY FOCSQL EMPPAY Diagram

EMPPAY MASTER

For DB2 or DB2 for VM:

FILENAME=EMPPAY  ,SUFFIX=SQLDS,$
 
SEGNAME=EMPINFO  ,SEGTYPE=S0,$
 FIELD=EMP_ID        ,ALIAS=EID           ,USAGE=A9       ,ACTUAL=A9,$
 FIELD=LAST_NAME     ,ALIAS=LN            ,USAGE=A15      ,ACTUAL=A15,$
 FIELD=FIRST_NAME    ,ALIAS=FN            ,USAGE=A10      ,ACTUAL=A10,$
 FIELD=HIRE_DATE     ,ALIAS=HDT           ,USAGE=YMD      ,ACTUAL=DATE,$
 FIELD=DEPARTMENT    ,ALIAS=DPT           ,USAGE=A10      ,ACTUAL=A10,
   MISSING=ON,$
 FIELD=CURRENT_SALARY,ALIAS=CSAL          ,USAGE=P9.2     ,ACTUAL=P4,$
 FIELD=CURR_JOBCODE  ,ALIAS=CJC           ,USAGE=A3       ,ACTUAL=A3,$
 FIELD=ED_HRS        ,ALIAS=OJT           ,USAGE=F6.2     ,ACTUAL=F4,
   MISSING=ON,$
 FIELD=BONUS_PLAN    ,ALIAS=BONUS_PLAN    ,USAGE=I4       ,ACTUAL=I4,$
SEGNAME=PAYINFO  ,SEGTYPE=S0, PARENT=EMPINFO, $
  FIELDNAME=PAYEID  ,ALIAS=EID ,USAGE=A9        ,ACTUAL=A9,$
  FIELDNAME=DAT_INC ,ALIAS=DI  ,USAGE=YMD       ,ACTUAL=DATE,$
  FIELDNAME=PCT_INC ,ALIAS=PI  ,USAGE=F6.2      ,ACTUAL=F4,$
  FIELDNAME=SALARY  ,ALIAS=SAL ,USAGE=D12.2M    ,ACTUAL=D8,$
  FIELDNAME=JOBCODE  ,ALIAS=JBC ,USAGE=A3        ,ACTUAL=A3,$

Note:

• For Teradata:
• Change the suffix value to SQLDBC.
• Change the ALIAS for LAST_NAME to LNAME.
• Change the ACTUAL format for CURRENT_SALARY to P8.
• Change the USAGE format for ED_HRS to D6.2 and the ACTUAL to D8.
• Change the USAGE format for PCT_INC to D6.2 and the ACTUAL to D8.
• Change the USAGE format for SALARY to P9.2 and the ACTUAL to P8.

• For CA-IDMS, change the suffix value to SQLIDMS.
• For Oracle, change the suffix value to SQLORA.

EMPPAY FOCSQL

For DB2 or DB2 for VM:

SEGNAME = EMPINFO, TABLENAME = "USER1"."EMPINFO", KEYS = 1, WRITE = YES,
     DBSPACE = PUBLIC.SPACE0,$
SEGNAME = PAYINFO, TABLENAME = "USER1"."PAYINFO", KEYS = 2, WRITE = YES,
     DBSPACE = PUBLIC.SPACE0,
     KEYFLD = EMP_ID, IXFLD = PAYEID, $

For Teradata:

SEGNAME=EMPINFO, TABLENAME=USER1.EMPLOYEE, KEYS=1, WRITE=YES,
    FALLBACK=YES,$
SEGNAME=PAYINFO, TABLENAME=USER1.PAYINFO,  KEYS=2, WRITE=YES,
    FALLBACK=YES,
    KEYFLD=EMP_ID, IXFLD=PAYEID,$

For IDMS SQL:

SEGNAME = EMPINFO, TABLENAME = EMPSCHEM.EMPINFO, KEYS = 1, WRITE = YES,
     DBSPACE = EMPSEG.EMPAREA.SPACE0,$
SEGNAME = PAYINFO, TABLENAME = PAYSCHEM.PAYINFO, KEYS = 2, WRITE = YES,
     DBSPACE = PAYSEG.PAYAREA,
     KEYFLD = EMP_ID, IXFLD = PAYEID, $

For Oracle:

SEGNAME=EMPINFO, TABLENAME=USER1.EMPINFO, KEYS=1, WRITE=YES,
    DBSPACE=SPACE1,
SEGNAME = PAYINFO, TABLENAME = USER1.PAYINFO, KEYS = 2, WRITE=YES,
    DBSPACE=SPACE1,
     KEYFLD = EMP_ID, IXFLD = PAYEID,$

EMPPAY Diagram

check file emppay pict
  NUMBER OF ERRORS=     0
  NUMBER OF SEGMENTS=   2  ( REAL=    2  VIRTUAL=   0 )
  NUMBER OF FIELDS=    14  INDEXES=   0  FILES=     1
  TOTAL LENGTH OF ALL FIELDS=   91
 SECTION 01
              STRUCTURE OF SQLDS    FILE EMPPAY   ON 06/16/93 AT 09.44.14
 
          EMPINFO
  01      S0
 **************
 *EMP_ID      **
 *LAST_NAME   **
 *FIRST_NAME  **
 *HIRE_DATE   **
 *            **
 ***************
  **************
        I
        I
        I
        I PAYINFO
  02    I S0
 **************
 *PAYEID      **
 *DAT_INC     **
 *PCT_INC     **
 *SALARY      **
 *            **
 ***************
  **************