August 24, 2016, 12:04 PM
eric.woerleHere are the master files that I am using in my clustered master join. I've cut a lot out. Taken things out put them back in etc... I can't find a reason. At the bottom of this is the SQL output. Table (RF_CITIZEN_TYP) is added to the join, but its not a part of the parent path. I'm requesting Banner ID which sits in the Profile Table. In this case RF_CITIZEN_TYP is a child table of Profile. But its the only child table that is being called. I kind find any attributes in my masterfile that sticks out as different. The joins aren't created any differently in the clustered master join. I'm very befuddled. And the other crazy part is, that all of the tables that are added to the query which aren't needed do not have the row level security included that they should.
*** Clustered Master Join
FILENAME=test_security, MFD_PROFILE=apolloglobal/apgglb9805,
REMARKS='Term Based view of Student Information. You can find such information about a student related to Curriculum, Cohort, Profile, Attributes, Grades, Attendance etc.', $
VARIABLE NAME=&&VARACTIND, PROMPT='Active Ind (1/0)', USAGE=I9, DEFAULT='1', $ This is why you see the (-1 = 1 ). Its a toggle to allow inactive records for audit purposes
SEGMENT=TERM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/TERM, CRINCLUDE=ALL, $
SEGMENT=V_TERM_STUD_CURRICULUM, SEGTYPE=KU, PARENT=TERM, CRFILE=APOLLOGLOBAL/IDR/SIS/VIEWS/V_TERM_STUD_CURRICULUM, CRINCLUDE=ALL, CRJOINTYPE=INNER,
JOIN_WHERE=TERM.TENANT_ID EQ V_TERM_STUD_CURRICULUM.TENANT_ID
AND TERM.START_DT GE V_TERM_STUD_CURRICULUM.START_DT
AND TERM.START_DT LE V_TERM_STUD_CURRICULUM.END_DT
AND LRNR_MOD EQ 'LEARNER'
AND (V_TERM_STUD_CURRICULUM.ACTIVE_IND EQ 1) OR (ACTIND EQ &&VARACTIND)
AND V_TERM_STUD_CURRICULUM.PROFILE_KEY EQ IDR_BASE_TABLES_PROFILE.PROFILE_KEY;, $
DEFINE ACTIND/I4 WITH V_TERM_STUD_CURRICULUM.PROFILE_KEY=-1; $
SEGMENT=IDR_BASE_TABLES_STUD_CURRICULUM, SEGTYPE=KU, PARENT=V_TERM_STUD_CURRICULUM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/STUD_CURRICULUM, CRSEGMENT=STUD_CURRICULUM, CRINCLUDE=ALL,
JOIN_WHERE=V_TERM_STUD_CURRICULUM.STUD_CURRICULUM_KEY EQ IDR_BASE_TABLES_STUD_CURRICULUM.STUD_CURRICULUM_KEY;, $
SEGMENT=IDR_BASE_TABLES_PROFILE, SEGTYPE=KU, PARENT=V_TERM_STUD_CURRICULUM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PROFILE, CRSEGMENT=PROFILE, CRINCLUDE=ALL,
JOIN_WHERE=V_TERM_STUD_CURRICULUM.PROFILE_KEY EQ IDR_BASE_TABLES_PROFILE.PROFILE_KEY;,
DESCRIPTION='IDR_BASE_TABLES_PROFILE', $
DEFINE STUDENT_AGE/I4=DATEDIF(BIRTH_DT, '&DATEYYMD', 'Y'); $
SEGMENT=IDR_BASE_TABLES_PF_FRGN_PERSON, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_FRGN_PERSON, CRSEGMENT=PF_FRGN_PERSON, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_PF_FRGN_PERSON.PROFILE_KEY;, $
SEGMENT=IDR_BASE_TABLES_PF_NAME, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_NAME, CRSEGMENT=PF_NAME, CRINCLUDE=ALL, CRJOINTYPE=INNER,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_PF_NAME.PROFILE_KEY AND IDR_BASE_TABLES_PF_NAME.PRIMARY_IND EQ 1 AND RF_MSTR_NM_TYP_KEY EQ -1;, $
DEFINE STUDENT_NAME/A511=FIRST_NM | (' ' | LAST_NM); $
SEGMENT=IDR_BASE_TABLES_PF_DISABILITY, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_DISABILITY, CRSEGMENT=PF_DISABILITY, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_PF_DISABILITY.PROFILE_KEY;, $
DEFINE DISAB_IND/A20=IF IDR_BASE_TABLES_PF_DISABILITY.PROFILE_KEY IS MISSING THEN 'NULL' ELSE 'Y'; $
SEGMENT=PF_MEDICAL_INFO, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_MEDICAL_INFO, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ PF_MEDICAL_INFO.PROFILE_KEY;, $
DEFINE MEDICAL_IND/A20=IF PF_MEDICAL_INFO.PROFILE_KEY IS MISSING THEN 'NULL' ELSE 'Y'; $
SEGMENT=IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/STUD_DEGREE_MAJ_MIN, CRSEGMENT=STUD_DEGREE_MAJ_MIN, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN.PROFILE_KEY;, $
SEGMENT=IDR_BASE_TABLES_STUD_DEG_RF_ACAD_LVL, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/RF_ACAD_LVL, CRSEGMENT=RF_ACAD_LVL, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN.RF_ACAD_LVL_KEY EQ IDR_BASE_TABLES_STUD_DEG_RF_ACAD_LVL.RF_ACAD_LVL_KEY;,
DESCRIPTION='STUD_DEG_RF_ACAD_LVL', $
SEGMENT=RF_ETHNIC_TYP, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/RF_ETHNIC_TYP, CRINCLUDE=ALL,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.RF_ETHNIC_TYP_KEY EQ RF_ETHNIC_TYP.RF_ETHNIC_TYP_KEY;, $
SEGMENT=RF_CITIZEN_TYP, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/RF_CITIZEN_TYP, CRINCLUDE=ALL,
JOIN_WHERE=IDR_BASE_TABLES_PROFILE.RF_CITIZEN_TYP_KEY EQ RF_CITIZEN_TYP.RF_CITIZEN_TYP_KEY;, $
SEGMENT=IDR_BASE_TABLES_PROGRAM, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_STUD_CURRICULUM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PROGRAM, CRSEGMENT=PROGRAM, CRINCLUDE=ALL,
JOIN_WHERE=IDR_BASE_TABLES_STUD_CURRICULUM.PROGRAM_KEY EQ IDR_BASE_TABLES_PROGRAM.PROGRAM_KEY;,
DESCRIPTION='PROGRAM', $
SEGMENT=PROGRAM_CUST_ULA, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROGRAM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PROGRAM_CUST_ULA, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=IDR_BASE_TABLES_PROGRAM.PROGRAM_KEY EQ PROGRAM_CUST_ULA.PROGRAM_KEY;, $
FOLDER=TEST_SECURITY,
DESCRIPTION='Students', $
FOLDER=PROFILE, PARENT=TEST_SECURITY, $
FIELDNAME=BANNER_ID, ALIAS=BANNER_ID, BELONGS_TO_SEGMENT=IDR_BASE_TABLES_PROFILE,
TITLE='Banner Id', DESCRIPTION='Student id Banner',
TITLE_EN='Banner ID',
TITLE_UK='Banner Id',
TITLE_ES='ID Banner',
DESC_EN='Student ID Banner',
DESC_UK='Student id Banner',
DESC_ES='Identificador del estudiante en Banner', $
FIELDNAME=STUDENT_NAME, ALIAS=FIRST_NM_LAST_NM_VRTL, BELONGS_TO_SEGMENT=IDR_BASE_TABLES_PF_NAME,
TITLE='Student Name', DESCRIPTION='Student Name',
TITLE_EN='Student Name',
TITLE_UK='Student Name',
TITLE_ES='Nombre del estudiante',
DESC_EN='Student Name',
DESC_UK='Student Name',
DESC_ES='Nombre del estudiante', $
FOLDER=CURRICULUM, PARENT=TEST_SECURITY, $
FIELDNAME=SOR_MODIFIED_TS, ALIAS=SOR_MODIFIED_TS, BELONGS_TO_SEGMENT=IDR_BASE_TABLES_STUD_CURRICULUM,
TITLE='Activity Date', DESCRIPTION='Program (Curriculum) Student Activity Date',
TITLE_EN='Activity Date',
TITLE_UK='Activity Date',
TITLE_ES='Fecha de actividad',
DESC_EN='Program (Curriculum) Student Activity Date',
DESC_UK='Program (Curriculum) Student Activity Date',
DESC_ES='Programa (Curriculo) fecha de actividad del estudiante', $
END
DBA=xxxxx, DBAFILE=aaaaa, $
Term .acx / .mas
/////.acx
SEGNAME=TERM, TABLENAME=IDR.TERM, CONNECTION=IDR,
KEYS=1, $
/////.mas
FILENAME=term, SUFFIX=SQLORA , MFD_PROFILE=apolloglobal/apgglb9805,$
SEGMENT=TERM, SEGTYPE=S0, $
FIELDNAME=TERM_KEY, ALIAS=TERM_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=TERM_CD, ALIAS=TERM_CD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=TERM_DESC, ALIAS=TERM_DESC, USAGE=A255, ACTUAL=A255,
MISSING=ON, $
FIELDNAME=START_DT, ALIAS=START_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=END_DT, ALIAS=END_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=FA_PROC_YR, ALIAS=FA_PROC_YR, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=FA_TERM, ALIAS=FA_TERM, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=FA_BEG_PERIOD, ALIAS=FA_BEG_PERIOD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=FA_END_PERIOD, ALIAS=FA_END_PERIOD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=HOUSING_START_DT, ALIAS=HOUSING_START_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=HOUSING_END_DT, ALIAS=HOUSING_END_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=SUMMER_IND, ALIAS=SUMMER_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8, $
FIELDNAME=REF_MSTR_ACAD_YR_KEY, ALIAS=REF_MSTR_ACAD_YR_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=REF_MSTR_TRM_TYP_KEY, ALIAS=REF_MSTR_TRM_TYP_KEY, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxx, DBAFILE=bbbbbb, $
all of the acx files are basically the same so I'm not going to add all of them.
v_term_stud_curriculum .mas
FILENAME=v_term_stud_curriculum, SUFFIX=SQLORA , MFD_PROFILE=apolloglobal/apgglb9805, $
SEGMENT=V_TERM_STUD_CURRICULUM, SEGTYPE=S0, $
FIELDNAME=PROFILE_KEY, ALIAS=PROFILE_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=STUD_CURRICULUM_KEY, ALIAS=STUD_CURRICULUM_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=PREV_STUD_CURRICULUM_KEY, ALIAS=PREV_STUD_CURRICULUM_KEY, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=LRNR_MOD, ALIAS=LRNR_MOD, USAGE=A30V, ACTUAL=A30V,
MISSING=ON, $
FIELDNAME=STATUS, ALIAS=STATUS, USAGE=A30V, ACTUAL=A30V,
MISSING=ON, $
FIELDNAME=PREV_STATUS, ALIAS=PREV_STATUS, USAGE=A30V, ACTUAL=A30V,
MISSING=ON, $
FIELDNAME=START_DT, ALIAS=START_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=END_DT, ALIAS=END_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=ACTIVE_IND, ALIAS=ACTIVE_IND, USAGE=I2, ACTUAL=I8,
MISSING=ON, $
FIELDNAME=PRIORITY_NBR, ALIAS=PRIORITY_NBR, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=LAST_UPD_ACTIVITY_DT, ALIAS=LAST_UPD_ACTIVITY_DT, USAGE=HYYMDS, ACTUAL=HYYMDS,
MISSING=ON, $
FIELDNAME=RF_MSTR_CURR_ACT_STS_KEY, ALIAS=RF_MSTR_CURR_ACT_STS_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_MSTR_PREV_ACT_STS_KEY, ALIAS=RF_MSTR_PREV_ACT_STS_KEY, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=PROGRAM_KEY, ALIAS=PROGRAM_KEY, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=PREV_PROGRAM_KEY, ALIAS=PREV_PROGRAM_KEY, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=PREV_PROGRAM_CD, ALIAS=PREV_PROGRAM_CD, USAGE=A64V, ACTUAL=A64V,
MISSING=ON, $
FIELDNAME=PREV_PROGRAM_DESC, ALIAS=PREV_PROGRAM_DESC, USAGE=A255V, ACTUAL=A255V,
MISSING=ON, $
FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxxx, DBAFILE=eeeeeeeeee, $
profile.mas
FILENAME=profile, SUFFIX=SQLORA , MFD_PROFILE=apolloglobal/apgglb9805, $
SEGMENT=PROFILE, SEGTYPE=S0, $
FIELDNAME=PROFILE_KEY, ALIAS=PROFILE_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=GENDER_CD, ALIAS=GENDER_CD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=HAIR_CD, ALIAS=HAIR_CD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=EYE_CD, ALIAS=EYE_CD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=BIRTH_DT, ALIAS=BIRTH_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=BIRTH_CITY_NM, ALIAS=BIRTH_CITY_NM, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=DRVR_LIC_NBR, ALIAS=DRVR_LIC_NBR, USAGE=A64, ACTUAL=A64,
MISSING=ON, $
FIELDNAME=DRVR_LIC_DT, ALIAS=DRVR_LIC_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=DECEASED_DT, ALIAS=DECEASED_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=DECEASED_IND, ALIAS=DECEASED_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=HEIGHT, ALIAS=HEIGHT, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=WEIGHT, ALIAS=WEIGHT, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=SPEC_DISABLE_VET_IND, ALIAS=SPEC_DISABLE_VET_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=INCARCERATION_IND, ALIAS=INCARCERATION_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=INTL_TAX_NBR, ALIAS=INTL_TAX_NBR, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=ACTV_DUTY_SEPR_DT, ALIAS=ACTV_DUTY_SEPR_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=RACE_ETHN_CONF_IND, ALIAS=RACE_ETHN_CONF_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=RACE_ETHN_CONF_DT, ALIAS=RACE_ETHN_CONF_DT, USAGE=YYMD, ACTUAL=DATE,
MISSING=ON, $
FIELDNAME=AF_SER_MEDAL_IND, ALIAS=AF_SER_MEDAL_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=CONFIDENTIAL_IND, ALIAS=CONFIDENTIAL_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=VET_NBR, ALIAS=VET_NBR, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=SSN, ALIAS=SSN, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=RELT_PRIORITY_NBR, ALIAS=RELT_PRIORITY_NBR, USAGE=D20.2, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=SOR_ID, ALIAS=SOR_ID, USAGE=A64, ACTUAL=A64,
MISSING=ON, $
FIELDNAME=SOR_FILE_ID, ALIAS=SOR_FILE_ID, USAGE=D10, ACTUAL=D8,
MISSING=ON, $
FIELDNAME=BANNER_ID, ALIAS=BANNER_ID, USAGE=A64, ACTUAL=A64,
MISSING=ON, $
FIELDNAME=RF_MSTR_MRTL_KEY, ALIAS=RF_MSTR_MRTL_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_MSTR_BIRTH_STAT_KEY, ALIAS=RF_MSTR_BIRTH_STAT_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_MSTR_DRVR_STAT_KEY, ALIAS=RF_MSTR_DRVR_STAT_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_MSTR_RELG_KEY, ALIAS=RF_MSTR_RELG_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_DRVR_LIC_CNTRY_KEY, ALIAS=RF_DRVR_LIC_CNTRY_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_ETHNIC_TYP_KEY, ALIAS=RF_ETHNIC_TYP_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=RF_CITIZEN_TYP_KEY, ALIAS=RF_CITIZEN_TYP_KEY, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxx, DBAFILE=ccccccccc, $
rf_citizen)typ.mas
FILENAME=rf_citizen_typ, SUFFIX=SQLORA , MFD_PROFILE=apolloglobal/apgglb9805,$
SEGMENT=RF_CITIZEN_TYP, SEGTYPE=S0, $
FIELDNAME=RF_CITIZEN_TYP_KEY, ALIAS=RF_CITIZEN_TYP_KEY, USAGE=D10, ACTUAL=D8, $
FIELDNAME=CITIZEN_TYPE_CD, ALIAS=CITIZEN_TYPE_CD, USAGE=A64, ACTUAL=A64,
MISSING=ON, $
FIELDNAME=CITIZEN_TYPE_DESC, ALIAS=CITIZEN_TYPE_DESC, USAGE=A255, ACTUAL=A255,
MISSING=ON, $
FIELDNAME=EDI_EQUIV_CD, ALIAS=EDI_EQUIV_CD, USAGE=A30, ACTUAL=A30,
MISSING=ON, $
FIELDNAME=CITIZEN_IND, ALIAS=CITIZEN_IND, USAGE=P2, ACTUAL=P8,
MISSING=ON, $
FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxx, DBAFILE=ddddddddddd, $
procedure
-*SET DBAJOIN = ON
SET TRACEOFF=ALL
SET TRACEON=SQLDI
SET TRACEON=SQLCALL
SET TRACEON=SQLAGGR//CLIENT
SET TRACEON=STMTRACE//CLIENT
SET TRACEON=STMTRACE/2/CLIENT
SET TRACEUSER=ON
SET TRACESTAMP=OFF
TABLE FILE TEST_SECURITY
SUM BANNER_ID
BY BANNER_ID
WHERE RECORDLIMIT EQ 5
END
SQL Output
SELECT
T6."BANNER_ID",
MAX(T6."BANNER_ID")
FROM
IDR.TERM T1,
BI_SUMMARY.V_TERM_STUD_CURRICULUM T2,
IDR.PROFILE T6,
IDR.RF_CITIZEN_TYP T8
WHERE
(((T1."TENANT_ID" = T2."TENANT_ID") AND (T1."START_DT" >=
T2."START_DT") AND (T1."START_DT" <= T2."END_DT") AND
(T2."LRNR_MOD" = 'LEARNER') AND (T2."ACTIVE_IND" = 1)) OR ((-1
= 1) AND (T2."PROFILE_KEY" = T6."PROFILE_KEY"))) AND
(T6."PROFILE_KEY" = T2."PROFILE_KEY") AND
(T8."RF_CITIZEN_TYP_KEY" = T6."RF_CITIZEN_TYP_KEY") AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T2."TENANT_ID" IN(-9, 8, 9)) AND
(T6."TENANT_ID" IN(-9, 8, 9))
GROUP BY
T6."BANNER_ID"
ORDER BY
T6."BANNER_ID";
All of the dba security is basically this. its generated at run time according to a procedure I created in Oracle. Each master file gets its own DBA file. This is where the "WHERE Tx.TENANT_ID = ..." comes from. DBA Security injects row level security as where statements. When you turn on DBAJOIN, then it injects it as part of the join clause, but only for left outer joins. Inner Joins are still a part of the where clause.
DBA Security
FILENAME=apgglb9805, SUFFIX=FIX , $
SEGMENT=DUMMY, SEGTYPE=S0, $
FIELDNAME=DUMMY, USAGE=A1, ACTUAL=A1, $
END
USER={userpassword], ACCESS=R, $
RESTRICT=VALUE_WHERE, NAME= [segment name], VALUE=TENANT_ID IN (-9,8,9); ,$