We upgraded this weekend, and none of my "BIG" reports are running. My IT department is still trouble shooting the error. This is what they told me.

"Tracie,

There is a problem with running the attached query under 10g.

This query is doing “FULL TABLE SCANS” under 10g, where as it did “INDEX SCANS” under 9i.

I am still troubleshooting this problem."

Below is the "long query" that my IT department sent me. Mind you my code is in Webfocus.

QUERY #1 (Multiple sessions,
Heavy I/O, Likely cause of performance problems)


* Formatted on 2007/09/25 10:54 (Formatter Plus v4.8.0) */
SELECT t1."PIDM_KEY", t1."DONOR_ID", t1."DONOR_LAST_NAME",
t1."DONOR_FIRST_NAME", t1."DONOR_MIDDLE_NAME",
t1."DONOR_NAME_PREFIX", t1."DONOR_NAME_SUFFIX",
t1."DONOR_PREF_DONR_CODE", t1."SPOUSE_PIDM", t1."SPOUSE_LAST_NAME",
t1."SPOUSE_FIRST_NAME", t1."SPOUSE_NAME_PREFIX",
t1."SPOUSE_NAME_SUFFIX", t1."SPOUSE_DECEASED_IND",
t1."SPOUSE_PREF_DONR_CODE", t1."SPOUSE_ID", t1."TOTAL_GIVING_YEAR_2",
t1."TOTAL_GIVING_YEAR_3", t1."MOST_RECENT_GIFT_DATE",
t1."MOST_RECENT_GIFT_AMT", t2."GENDER", t2."DECEASED_IND",
t2."PREF_COLL", t2."PREF_CLASS", t2."OTHER_DONR_CODE_1",
t2."PREF_STREET_LINE1", t2."PREF_STREET_LINE2",
t2."PREF_STREET_LINE3", t2."PREF_CITY", t2."PREF_STAT_CODE",
t2."PREF_ZIP", t2."OTHER_ATYP_CODE", t2."OTHER_STREET_LINE1",
t2."OTHER_STREET_LINE2", t2."OTHER_STREET_LINE3", t2."OTHER_CITY",
t2."OTHER_STAT_CODE", t2."OTHER_ZIP", t2."TELE_CODE_1",
t2."PHONE_NUMBER_1", t2."PHONE_AREA_CODE_1", t2."TELE_CODE_2",
t2."PHONE_NUMBER_2", t2."PHONE_AREA_CODE_2", t2."TELE_CODE_3",
t2."PHONE_NUMBER_3", t2."PHONE_AREA_CODE_3", t3."ID_NUMBER",
t3."MIDDLE_NAME", t3."PREF_COLL", t3."PREF_CLASS",
t3."OTHER_ATYP_CODE", t3."OTHER_STREET_LINE1",
t3."OTHER_STREET_LINE2", t3."OTHER_STREET_LINE3", t3."OTHER_CITY",
t3."OTHER_STAT_CODE", t3."OTHER_ZIP", t3."TELE_CODE_1",
t3."PHONE_NUMBER_1", t3."PHONE_AREA_CODE_1", t3."PHONE_NUMBER_2",
t3."PHONE_AREA_CODE_2", t3."PHONE_NUMBER_3", t3."PHONE_AREA_CODE_3",
t4."PIDM_KEY", t4."COMPANY_NAME", t5."SPRTELE_TELE_CODE",
t5."SPRTELE_PHONE_AREA", t5."SPRTELE_PHONE_NUMBER",
t5."SPRTELE_STATUS_IND", t6."APRCSPS_PIDM", t6."APRCSPS_SPS_PIDM",
t7."PIDM_KEY", t7."TOTAL_GIVING_YEAR_2", t7."TOTAL_GIVING_YEAR_3",
t8."PIDM_KEY", t8."COMPANY_NAME"
FROM baninst1.aa_giving t1,
baninst1.aa_constituent_identification t2,
baninst1.aa_constituent_identification t3,
baninst1.aa_constituent_additional_info t4,
saturn.sprtele t5,
alumni.aprcsps t6,
baninst1.aa_giving t7,
baninst1.aa_constituent_additional_info t8
WHERE (t2."ID_NUMBER" = t1."DONOR_ID")
AND (t3."ID_NUMBER"(+) = t1."SPOUSE_ID")
AND (t4."PIDM_KEY"(+) = t1."PIDM_KEY")
AND (t5."SPRTELE_PIDM" = t1."PIDM_KEY")
AND (t6."APRCSPS_SPS_PIDM"(+) = t1."SPOUSE_PIDM")
AND (t7."PIDM_KEY"(+) = t6."APRCSPS_PIDM")
AND (t8."PIDM_KEY"(+) = t6."APRCSPS_PIDM")
AND (t1."DONOR_PREF_DONR_CODE" IN ('ALUM', 'ALND'))
AND t2."DECEASED_IND" IS NULL
AND t2."PREF_STREET_LINE1" IS NOT NULL
AND ( ((t2."GENDER" = 'M') AND t1."SPOUSE_ID" IS NOT NULL)
OR t1."SPOUSE_ID" IS NULL
)
AND ((SUBSTR (t2."PREF_CLASS", 4, 1)) IN ('3', '8'))
AND ((CASE (t2."OTHER_DONR_CODE_1")
WHEN 'FSCR'
THEN 'Y'
ELSE ' '
END) = ' ')
ORDER BY t1."PIDM_KEY", t1."DONOR_ID"


Did anyone have a similar problem when they upgraded? All my small reports are still running, but I have about 10 reports that all use this same query and they worked last week fine, but now they all go into the "UNKNOWN" area in my deferred status.

Thanks!

Tracie

HI Tracie,


Thats a tough one , since performance problems can be due to a wide array of things

Do you have a dba at hand ? and did he/she do some checks?

1. First thing i thought of was : did you gather statistics for your system ( or tables ) ?

if not check out info on dbms_stats.gather_table_stats , dbms_stats.gather_schema_stats,
dbms_stats.gather_system_stats.

this is quite crucial.

2. We are also on 10g and i know that in 10g , the optimizer is set default to cpu based costing , in 9i however this was io based as default , this change will have the effect of doing a lot more full table scans

set the parameter back to :

alter session set "_optimizer_cost_model"=io;

3. try to switch off 2 new features in 10g

alter session set "_optimizer_cost_based_transformation" =off;

alter session set "_gby_hash_aggregation_enabled" = FALSE;


Since i don't really know your level of expertise in oracle , you might go "duhhhh" when reading all of the above, if so can you give a little more info on which things you tried and checked

P.

As a rule of thumb I dont go beyond 3 Oracle managed joins because the Optimizer is so poor.
(The more joins the more exponential possibilities it has to consider)

Generally you can use MATCH FILE quite effectively to replace one to one left outers.

That said I would fire your dba and replace him with Pete. I never worked with Orrible 10g, but did a lot with 9.2. Looking at his advice though he is right on the money and you can either code those statements as ENGINE SQLORA for your particular session in your code or in EDASPROF


SQL SQLORA alter session set "_optimizer_cost_model"=io;
END

or you could even sort out your dba's problems and pass this info on to him!

I would turn XRETRIEVAL OFF and turn on SQL traces to see if the Joins are inefficient. Try this in both 9 and 10. Did indexes disappear when migrating? Were the WebFOCUS meta-data regenerated?

if you're using the cost based optimizer, the tables/indexes need to be analyzed.

I'm not on oracle anymore, but I wonder if you do it as sql passthru and do a "set explain plan on;" before your sql statement, maybe that would show you the explain plan.

Oracle's optimizer is just as robust as any other database optimizer I've seen, it's just a matter of making sure that if you're using it's cost-based optimizer that you're keeping your stats up.

You could mention 'materialized views' as an alternative solution in the new 10g world. As soon as you drop that term on the table, you're dba's will do anything they possibly can to keep that technology from coming into their database world. Materialized Views are a tremendous amount of work for your dba's, and many Oracle dba's know what they are and have read just enough about them to be scared. They can, however, change fact aggregated on low-cardinality by field queries into seriously powerful, useful datawarehousing. Again, it's a little human overhead to make it work, and maybe a little research to understand that they can help you.

I received an update from my DBA and he said he tried all the solutions and none worked so far.



The next thing he is trying now is getting all the tables behind the views that I am joining to see if the report will run if I do joins using the tables....

Any other suggestions?

You said your code is in WebFocus, so I take it that you are not doing a SQL passthru. Make sure that your indexes in Oracle are the same as they were in 9i. Check your synonyms, make a snapshot and then refresh them. Check your indexed fields in the synonym and make sure they are the same format and size as they were before. Also, with this many joins you might be wise to create a view in Oracle, create your synonym and access the view.

Tracie

If you haven't sorted it now.

1. Post your wf code.
2. Post the size of each of the tables (this can be obtained from stats in ALL_TABLEs)
3. Post a rough percentage of the records obtained by your selection criteria against each table.

I will have a crack at re-writing with MATCH file.


John

This message has been edited. Last edited by: hammo1j,

-HERE IS THE CODE

SET NODATA = ' '
JOIN
AA_GIVING.AA_GIVING.DONOR_ID IN AA_GIVING TO UNIQUE
AA_CONSTITUENT_IDENTIFICATION.AA_CONSTITUENT_IDENTIFICATION.ID_NUMBER
IN AA_CONSTITUENT_IDENTIFICATION AS J0
END
JOIN
LEFT_OUTER AA_GIVING.AA_GIVING.SPOUSE_ID IN AA_GIVING TO UNIQUE
AA_CONSTITUENT_IDENTIFICATION.AA_CONSTITUENT_IDENTIFICATION.ID_NUMBER
IN AA_CONSTITUENT_IDENTIFICATION AS J1
END
JOIN
LEFT_OUTER AA_GIVING.AA_GIVING.SPOUSE_PIDM IN AA_GIVING TO UNIQUE
APRCSPS.APRCSPS.APRCSPS_SPS_PIDM IN APRCSPS AS J3
END
JOIN
LEFT_OUTER APRCSPS.APRCSPS.APRCSPS_PIDM IN AA_GIVING TO UNIQUE
AA_GIVING.AA_GIVING.PIDM_KEY IN AA_GIVING AS J4
END
JOIN
LEFT_OUTER APRCSPS.APRCSPS.APRCSPS_PIDM IN AA_GIVING TO UNIQUE
PIDM_KEY IN AA_CONSTITUENT_ADDITIONAL_INFO AS J5
END
JOIN LEFT_OUTER PIDM_KEY IN AA_GIVING TO UNIQUE
PIDM_KEY IN AA_CONSTITUENT_ADDITIONAL_INFO AS J6
END
JOIN
LEFT_OUTER PIDM_KEY IN AA_GIVING TO
SPRTELE_PIDM IN SPRTELE AS J7
END
DEFINE FILE AA_GIVING
PHONE/A10=IF SPRTELE_TELE_CODE EQ 'MA' AND SPRTELE_STATUS_IND NE 'I' THEN SPRTELE_PHONE_AREA||EDIT(SPRTELE_PHONE_NUMBER, '9999999') ELSE ' ';
REUN_YR/A1=EDIT ( PREF_CLASS , '$$$9' );
RECENTDATE/MDYY=HDATE(MOST_RECENT_GIFT_DATE, 'MDYY');
BDATE/MDYY=HDATE(BIRTH_DATE, 'MDYY');
ADDRESS/A130=PREF_STREET_LINE1||(' '|PREF_STREET_LINE2)||(' '|PREF_STREET_LINE3);
JTNAME/A220=
(NAME_PREFIX)||
(' '|FIRST_NAME) ||
(' & ' |SPOUSE_FIRST_NAME)|
(' ' |LAST_NAME)||
(' ' |NAME_SUFFIX);
NAME/A123=NAME_PREFIX||(' '|FIRST_NAME)||(' '|MIDDLE_INITIAL)||(' '|LAST_NAME)||(' '|NAME_SUFFIX);
FINALNAME/A220=
IF (GENDER EQ 'M' AND SPOUSE_ID NE MISSING) THEN
JTNAME
ELSE
NAME;
FEM_PREFIX/A4=
IF (GENDER EQ 'M' AND SPOUSE_ID NE MISSING) THEN
'Mrs'
ELSE
' ';
CLASS1/A1=EDIT(PREF_CLASS, '$$$9');
CLASS/A4=PREF_CLASS;
CLASSYEAR/A4=IF J1PREF_CLASS EQ '0000' THEN ' ' ELSE J1PREF_CLASS;
COLL/A2=IF J1PREF_COLL EQ '00' OR '99' THEN ' ' ELSE J1PREF_COLL;
SP_ID/A9=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SPOUSE_ID ELSE ' ';
SP_PRF/A100=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SPOUSE_NAME_PREFIX ELSE ' ';
SP_FIRST/A100=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SPOUSE_FIRST_NAME ELSE ' ';
SP_MID/A100=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1MIDDLE_NAME ELSE ' ';
SP_LAST/A100=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SPOUSE_LAST_NAME ELSE ' ';
SP_SUFF/A20=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SPOUSE_NAME_SUFFIX ELSE ' ';
ADDRESS_TYPE/A2=IF OTHER_ATYP_CODE EQ 'E1' THEN 'E1' ELSE ' ';
WORKADDRESS1/A30=IF OTHER_ATYP_CODE EQ 'E1' THEN OTHER_STREET_LINE1 ELSE ' ';
WORKADDRESS2/A30=IF OTHER_ATYP_CODE EQ 'E1' THEN OTHER_STREET_LINE2 ELSE ' ';
WORKADDRESS3/A30=IF OTHER_ATYP_CODE EQ 'E1' THEN OTHER_STREET_LINE3 ELSE ' ';
WORKCITY/A20=IF OTHER_ATYP_CODE EQ 'E1' THEN OTHER_CITY ELSE ' ';
WORKSTATE/A3=IF OTHER_ATYP_CODE EQ 'E1' THEN OTHER_STAT_CODE ELSE ' ';
WORKZIP/A10=IF OTHER_ATYP_CODE EQ 'E1' THEN OTHER_ZIP ELSE ' ';
SP_WORKADDRESS1/A30=IF J1OTHER_ATYP_CODE EQ 'E1' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1OTHER_STREET_LINE1 ELSE ' ';
SP_WORKADDRESS2/A30=IF J1OTHER_ATYP_CODE EQ 'E1' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1OTHER_STREET_LINE2 ELSE ' ';
SP_WORKADDRESS3/A30=IF J1OTHER_ATYP_CODE EQ 'E1' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1OTHER_STREET_LINE3 ELSE ' ';
SP_WORKCITY/A20=IF J1OTHER_ATYP_CODE EQ 'E1' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1OTHER_CITY ELSE ' ';
SP_WORKSTATE/A3=IF J1OTHER_ATYP_CODE EQ 'E1' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1OTHER_STAT_CODE ELSE ' ';
SP_WORKZIP/A10=IF J1OTHER_ATYP_CODE EQ 'E1' AND SPOUSE_DECEASED_IND EQ MISSING THEN J1OTHER_ZIP ELSE ' ';
SP_EMPSTREET1/A30=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SP_WORKADDRESS1 ELSE ' ';
SP_EMPSTREET2/A30=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SP_WORKADDRESS2 ELSE ' ';
SP_EMPSTREET3/A30=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SP_WORKADDRESS3 ELSE ' ';
SP_EMPCITY/A20=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SP_WORKCITY ELSE ' ';
SP_EMPSTATE/A3=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SP_WORKSTATE ELSE ' ';
SP_EMPZIP/A10=IF SPOUSE_PREF_DONR_CODE EQ 'ALUM' OR 'ALND' AND SPOUSE_DECEASED_IND EQ MISSING THEN SP_WORKZIP ELSE ' ';
TOTALSYEAR2/D12.2=IF SP_ID NE MISSING THEN TOTAL_GIVING_YEAR_2 + J4TOTAL_GIVING_YEAR_2 ELSE TOTAL_GIVING_YEAR_2;
TOTALSYEAR3/D12.2=IF SP_ID NE MISSING THEN TOTAL_GIVING_YEAR_3 + J4TOTAL_GIVING_YEAR_3 ELSE TOTAL_GIVING_YEAR_3;
FSCR_IND/A1=IF OTHER_DONR_CODE_1 EQ 'FSCR' THEN 'Y' ELSE ' ';
WORKPHONE/A10=
IF TELE_CODE_1 EQ 'E1' THEN
PHONE_AREA_CODE_1 || EDIT(PHONE_NUMBER_1, '9999999')
ELSE IF TELE_CODE_2 EQ 'E1' THEN
PHONE_AREA_CODE_2 || EDIT(PHONE_NUMBER_2, '9999999')
ELSE IF TELE_CODE_3 EQ 'E1' THEN
PHONE_AREA_CODE_3 || EDIT(PHONE_NUMBER_3, '9999999')
ELSE ' ';
SP_WORKPHONE/A12=
IF J1TELE_CODE_1 EQ 'E1' THEN
J1PHONE_AREA_CODE_1 || EDIT(J1PHONE_NUMBER_1, '9999999')
ELSE IF TELE_CODE_2 EQ 'E1' THEN
J1PHONE_AREA_CODE_2 || EDIT(J1PHONE_NUMBER_2, '9999999')
ELSE IF TELE_CODE_3 EQ 'E1' THEN
J1PHONE_AREA_CODE_3 || EDIT(J1PHONE_NUMBER_3, '9999999')
ELSE ' ';
END
TABLE FILE AA_GIVING
PRINT
DONOR_ID
DONOR_NAME_PREFIX
DONOR_FIRST_NAME
DONOR_MIDDLE_NAME
DONOR_LAST_NAME
DONOR_NAME_SUFFIX
PREF_STREET_LINE1
PREF_STREET_LINE2
PREF_STREET_LINE3
PREF_CITY
PREF_STAT_CODE
PREF_ZIP
PHONE
WORKADDRESS1
WORKADDRESS2
WORKADDRESS3
WORKCITY
WORKSTATE
WORKZIP
ADDRESS_TYPE
PREF_CLASS
PREF_COLL
TOTALSYEAR2
TOTALSYEAR3
RECENTDATE
MOST_RECENT_GIFT_AMT
SPOUSE_PREF_DONR_CODE
APRCSPS_SPS_PIDM
SP_ID
SP_PRF
SP_FIRST
SP_MID
SP_LAST
SP_SUFF
SP_EMPSTREET1
SP_EMPSTREET2
SP_EMPSTREET3
SP_EMPCITY
SP_EMPSTATE
SP_EMPZIP
CLASSYEAR
CLASS
COLL
J6COMPANY_NAME
J5COMPANY_NAME
WORKPHONE
SP_WORKPHONE
SPOUSE_DECEASED_IND
BY PIDM_KEY
BY DONOR_ID
WHERE (GENDER EQ 'M' AND SPOUSE_ID NE MISSING) OR SPOUSE_ID EQ MISSING;
WHERE (PREF_STREET_LINE1 NE MISSING);
WHERE (DONOR_PREF_DONR_CODE EQ 'ALUM' OR 'ALND');
WHERE DECEASED_IND EQ MISSING;
WHERE CLASS1 NE &CLASSYEAR.(OR(<1,1>,<2,2>,<3,3>,<4,4>,<5,5>,<6,6>,<7,7>,<8,8>,<9,9>)).Select Last Digit of Reunion Years to EXCLUDE (Press Cntr for Multi).;
WHERE ( LIFE_TOTAL_GIVING EQ 0 ) AND ( TOTAL_PLEDGE_AMT_YEAR_1 LE 0 );
WHERE PHONE NE MISSING;
WHERE PHONE NE ' ';
WHERE PHONE NE '**********';
WHERE FSCR_IND EQ ' ';
WHERE RECORDLIMIT EQ 50
-*ON TABLE HOLD AS LYBNTHOLD
END
JOIN
LEFT_OUTER PIDM_KEY IN LYBNTHOLD TO UNIQUE
PIDM_KEY IN AA_CONSTITUENT_ADDITIONAL_INFO AS J1
END
JOIN
LEFT_OUTER APRCSPS_SPS_PIDM IN LYBNTHOLD TO UNIQUE
PIDM_KEY IN AA_CONSTITUENT_ADDITIONAL_INFO AS J3
END
JOIN
LEFT_OUTER APRCSPS_SPS_PIDM IN LYBNTHOLD TO MULTIPLE
PIDM_KEY IN V_NCAT_AA_EMAIL3 AS J4
END
JOIN
LEFT_OUTER PIDM_KEY IN LYBNTHOLD TO MULTIPLE
PIDM_KEY IN V_NCAT_AA_EMAIL3 AS J5
END
DEFINE FILE LYBNTHOLD
J5EMAIL/A90=
IF J5STATUS_IND1 EQ 'A' THEN
J5EMAIL_ADDRESS1
ELSE IF J5STATUS_IND2 EQ 'A' THEN
J5EMAIL_ADDRESS2
ELSE IF J5STATUS_IND3 EQ 'A' THEN
J5EMAIL_ADDRESS3
ELSE ' ';
J4EMAIL/A90=
IF J4STATUS_IND1 EQ 'A' AND SPOUSE_DECEASED_IND EQ MISSING THEN
J4EMAIL_ADDRESS1
ELSE IF J4STATUS_IND2 EQ 'A' AND SPOUSE_DECEASED_IND EQ MISSING THEN
J4EMAIL_ADDRESS2
ELSE IF J4STATUS_IND3 EQ 'A' AND SPOUSE_DECEASED_IND EQ MISSING THEN
J4EMAIL_ADDRESS3
ELSE ' ';
END
TABLE FILE LYBNTHOLD
PRINT
DONOR_ID
DONOR_NAME_PREFIX
DONOR_FIRST_NAME
DONOR_MIDDLE_NAME
DONOR_LAST_NAME
DONOR_NAME_SUFFIX
J0PREF_STREET_LINE1
J0PREF_STREET_LINE2
J0PREF_STREET_LINE3
J0PREF_CITY
J0PREF_STAT_CODE
J0PREF_ZIP
PHONE
WORKADDRESS1
WORKADDRESS2
WORKADDRESS3
WORKCITY
WORKSTATE
WORKZIP
ADDRESS_TYPE
CLASS
J1INST_DEGR_YEAR_1
J1INST_DEGR_YEAR_2
J1INST_DEGR_DEGC_CODE_1
J1INST_DEGR_COLL_CODE_1
J1INST_DEGR_COLL_CODE_2
J1INST_DEGR_DEGC_CODE_2
J1INST_DEGR_MAJR1_DESC_1
J1INST_DEGR_MAJR2_DESC_1
J5EMAIL
TOTALSYEAR2
TOTALSYEAR3
RECENTDATE
MOST_RECENT_GIFT_AMT
SPOUSE_PREF_DONR_CODE
APRCSPS_SPS_PIDM
SP_ID
SP_PRF
SP_FIRST
SP_MID
SP_LAST
SP_SUFF
SP_EMPSTREET1
SP_EMPSTREET2
SP_EMPSTREET3
SP_EMPCITY
SP_EMPSTATE
SP_EMPZIP
J3INST_DEGR_YEAR_1
J3INST_DEGR_YEAR_2
J3INST_DEGR_COLL_CODE_1
J3INST_DEGR_COLL_CODE_2
J3INST_DEGR_DEGC_CODE_1
J3INST_DEGR_DEGC_CODE_2
J3INST_DEGR_MAJR1_DESC_1
J3INST_DEGR_MAJR2_DESC_1
J4EMAIL
CLASSYEAR
COLL
J6COMPANY_NAME
J5COMPANY_NAME
WORKPHONE
SP_WORKPHONE
SPOUSE_DECEASED_IND
BY PIDM_KEY
BY DONOR_ID
ON TABLE NOTOTAL
ON TABLE HOLD AS FINAL
END
JOIN
LEFT_OUTER PIDM_KEY IN FINAL TO UNIQUE
PIDM_KEY IN AA_CONSTITUENT_ORGANIZ_SHARED AS J2
END
JOIN
LEFT_OUTER PIDM_KEY IN FINAL TO UNIQUE
PIDM_KEY IN AA_PROSPECT AS J3
END
TABLE FILE FINAL
PRINT
DONOR_ID
DONOR_NAME_PREFIX
DONOR_FIRST_NAME
DONOR_MIDDLE_NAME
DONOR_LAST_NAME
DONOR_NAME_SUFFIX
J0PREF_STREET_LINE1
J0PREF_STREET_LINE2
J0PREF_STREET_LINE3
J0PREF_CITY
J0PREF_STAT_CODE
J0PREF_ZIP
PHONE
WORKADDRESS1
WORKADDRESS2
WORKADDRESS3
WORKCITY
WORKSTATE
WORKZIP
ADDRESS_TYPE
CLASS
J1INST_DEGR_YEAR_1
J1INST_DEGR_YEAR_2
J1INST_DEGR_DEGC_CODE_1
J1INST_DEGR_COLL_CODE_1
J1INST_DEGR_COLL_CODE_2
J1INST_DEGR_DEGC_CODE_2
J1INST_DEGR_MAJR1_DESC_1
J1INST_DEGR_MAJR2_DESC_1
J5EMAIL
TOTALSYEAR2
TOTALSYEAR3
RECENTDATE
MOST_RECENT_GIFT_AMT
SPOUSE_PREF_DONR_CODE
APRCSPS_SPS_PIDM
SP_ID
SP_PRF
SP_FIRST
SP_MID
SP_LAST
SP_SUFF
SP_EMPSTREET1
SP_EMPSTREET2
SP_EMPSTREET3
SP_EMPCITY
SP_EMPSTATE
SP_EMPZIP
J3INST_DEGR_YEAR_1
J3INST_DEGR_YEAR_2
J3INST_DEGR_COLL_CODE_1
J3INST_DEGR_COLL_CODE_2
J3INST_DEGR_DEGC_CODE_1
J3INST_DEGR_DEGC_CODE_2
J3INST_DEGR_MAJR1_DESC_1
J3INST_DEGR_MAJR2_DESC_1
J4EMAIL
CLASSYEAR
COLL
J6COMPANY_NAME
J5COMPANY_NAME
WORKPHONE
SP_WORKPHONE
SPOUSE_DECEASED_IND
BY PIDM_KEY
BY DONOR_ID
WHERE EXCL_CODE_1 NE 'NOC' OR 'APL' OR 'DSP' OR 'S08';
WHERE PRST_CODE NE 'A';
WHERE PIDM_KEY NE LAST PIDM_KEY;
ON TABLE HOLD AS FINAL2
END
-INCLUDE last2act.fex
-INCLUDE last5gif.fex
JOIN
LEFT_OUTER PIDM_KEY IN FINAL2 TO UNIQUE
PIDM_KEY IN 5GIFTS AS J1
END
JOIN
LEFT_OUTER PIDM_KEY IN FINAL2 TO UNIQUE
PIDM_KEY IN ACTHOLD AS J2
END
DEFINE FILE FINAL2
LNAME/A25=EDIT ( DONOR_LAST_NAME , '9999999999999999999999999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$' );
FNAME/A25=EDIT ( DONOR_FIRST_NAME , '999999999999999' );
PRFX/A3=EDIT ( DONOR_NAME_PREFIX , '999$$$$$$$$$$$$$$$$$' );
MID/A15=EDIT ( DONOR_MIDDLE_NAME , '999999999999999' );
SP_MID/A15=EDIT ( SP_MID , '999999999999999' );
SP_FIRST/A25=EDIT ( SP_FIRST , '999999999999999' );
SFX/A10=EDIT ( DONOR_NAME_SUFFIX , '9999999999$$$$$$$$$$' );
SP_LAST/A25=EDIT ( SP_LAST , '9999999999999999999999999$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$' );
SP_PRF/A3=EDIT ( SP_PRF , '999$$$$$$$$$$$$$$$$$' );
SP_SUFF/A3=EDIT ( SP_SUFF , '999$$$$$$$$$$$$$$$$$' );
EMAIL/A30=EDIT(J5EMAIL, '999999999999999999999999999999');
SPEMAIL/A30=EDIT(J4EMAIL, '999999999999999999999999999999');
ACTIVITY1/A8=EDIT(ACTHOLD.COL1 , '99999999' );
ACTIVITY2/A8=EDIT(ACTHOLD.COL2 , '99999999' );
SPACTIVITY1/A8=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(ACTHOLD.COL3 , '99999999' ) ELSE ' ';
SPACTIVITY2/A8=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(ACTHOLD.COL4 , '99999999' ) ELSE ' ';
MAJOR1/A30=EDIT(J1INST_DEGR_MAJR1_DESC_1, '999999999999999999999999999999');
MAJOR2/A30=EDIT(J1INST_DEGR_MAJR2_DESC_1, '999999999999999999999999999999');
SPMAJOR1/A30= IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_MAJR1_DESC_1, '999999999999999999999999999999') ELSE ' ';
SPMAJOR2/A30= IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_MAJR2_DESC_1, '999999999999999999999999999999') ELSE ' ';
DEG1/A10=EDIT(J1INST_DEGR_DEGC_CODE_1,'9999999999' );
DEG2/A10=EDIT(J1INST_DEGR_DEGC_CODE_2,'9999999999' );
SPDEG1/A10=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_DEGC_CODE_1,'9999999999' ) ELSE ' ';
SPDEG2/A10=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_DEGC_CODE_2,'9999999999' ) ELSE ' ';
YEAR2/P9.2=TOTALSYEAR2;
YEAR3/P9.2=TOTALSYEAR3;
SPYEAR1/A4=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(CLASSYEAR, '9999') ELSE ' ';
SPYEAR2/A4=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_YEAR_2, '9999') ELSE ' ';
SPCOLL1/A2=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_COLL_CODE_1, '99') ELSE ' ';
SPCOLL2/A2=IF SPOUSE_DECEASED_IND EQ MISSING THEN EDIT(J3INST_DEGR_COLL_CODE_2, '99') ELSE ' ';
END
TABLE FILE FINAL2
PRINT
PHONE
DONOR_ID
PRFX AS 'PRF'
FNAME AS 'FIRST'
MID
LNAME AS 'LAST'
SFX
J0PREF_STREET_LINE1 AS 'STREET1'
J0PREF_STREET_LINE2 AS 'STREET2'
J0PREF_STREET_LINE3 AS 'STREET3'
J0PREF_CITY AS 'CITY'
J0PREF_STAT_CODE AS 'STATE'
J0PREF_ZIP AS 'ZIP'
WORKADDRESS1 AS 'WORKSTREET1'
WORKADDRESS2 AS 'WORKSTREET2'
WORKADDRESS3 AS 'WORKSTREET3'
WORKCITY
WORKSTATE
WORKZIP
ADDRESS_TYPE AS 'ATYPE'
CLASS AS 'CLASS 1'
J1INST_DEGR_YEAR_2 AS 'CLASS 2'
J1INST_DEGR_COLL_CODE_1 AS 'COLL'
J1INST_DEGR_COLL_CODE_2 AS 'COLL 2'
DEG1 AS 'DEG1'
DEG2 AS 'DEG2'
MAJOR1
MAJOR2
EMAIL
ACTIVITY1
ACTIVITY2
5GIFTS.COL1 AS 'GIFTAMT1'
5GIFTS.COL2 AS 'GIFTDATE1'
5GIFTS.COL3 AS 'DESGNAME1'
5GIFTS.COL4 AS 'GIFTAMT2'
5GIFTS.COL5 AS 'GIFTDATE2'
5GIFTS.COL6 AS 'DESGNAME2'
5GIFTS.COL7 AS 'GIFTAMT3'
5GIFTS.COL8 AS 'GIFTDATE3'
5GIFTS.COL9 AS 'DESGNAME3'
5GIFTS.COL10 AS 'GIFTAMT4'
5GIFTS.COL11 AS 'GIFTDATE4'
5GIFTS.COL12 AS 'DESGNAME4'
5GIFTS.COL13 AS 'GIFTAMT5'
5GIFTS.COL14 AS 'GIFTDATE5'
5GIFTS.COL15 AS 'DESGNAME5'
YEAR2 AS 'TOT GIVING YEAR2'
YEAR3 AS 'TOT GIVING YEAR3'
SP_ID
SP_PRF
SP_FIRST
SP_MID
SP_LAST
SP_SUFF
SP_EMPSTREET1
SP_EMPSTREET2
SP_EMPSTREET3
SP_EMPCITY
SP_EMPSTATE
SP_EMPZIP
SPYEAR1 AS 'SP CLASS 1'
SPYEAR2 AS 'SP CLASS 2'
SPCOLL1 AS 'SP COLL'
SPCOLL2 AS 'SP COLL2'
SPDEG1 AS 'SP DEG1'
SPDEG2 AS 'SP DEG2'
SPMAJOR1 AS 'SP MAJOR1'
SPMAJOR2 AS 'SP MAJOR2'
SPEMAIL AS 'SP EMAIL'
SPACTIVITY1 AS 'SP ACTIVITY1'
SPACTIVITY2 AS 'SP ACTIVITY2'
5GIFTS.COL16 AS 'SP GIFTAMT1'
5GIFTS.COL17 AS 'SP GIFTDATE1'
5GIFTS.COL18 AS 'SP DESGNAME1'
5GIFTS.COL19 AS 'SP GIFTAMT2'
5GIFTS.COL20 AS 'SP GIFTDATE2'
5GIFTS.COL21 AS 'SP DESGNAME2'
5GIFTS.COL22 AS 'SP GIFTAMT3'
5GIFTS.COL23 AS 'SP GIFTDATE3'
5GIFTS.COL24 AS 'SP DESGNAME3'
5GIFTS.COL25 AS 'SP GIFTAMT4'
5GIFTS.COL26 AS 'SP GIFTDATE4'
5GIFTS.COL27 AS 'SP DESGNAME4'
5GIFTS.COL28 AS 'SP GIFTAMT5'
5GIFTS.COL29 AS 'SP GIFTDATE5'
5GIFTS.COL30 AS 'SP DESGNAME5'
J6COMPANY_NAME
J5COMPANY_NAME
WORKPHONE
SP_WORKPHONE
BY PIDM_KEY
BY DONOR_ID
ON TABLE HOLD AS FINAL3
END
JOIN
LEFT_OUTER PIDM_KEY IN FINAL3 TO APBGHIS_PIDM IN APBGHIS AS J1
END
JOIN
LEFT_OUTER APBGHIS_PIDM AND APBGHIS_FISC_CODE_LAST_GIFT
IN FINAL3 TO UNIQUE AA_GIFT_DETAIL.AA_GIFT_DETAIL.PIDM_KEY
AND AA_GIFT_DETAIL.AA_GIFT_DETAIL.FISC_CODE IN AA_GIFT_DETAIL AS J2
END
DEFINE FILE FINAL3
LASTFY/A4=EDIT(FISC_CODE, '$9999$$$$$$$$$$');
LASTFYSUM/D15.2=GIFT_AMT;
END
TABLE FILE FINAL3
SUM
PHONE
DONOR_ID
PRFX AS 'PRF'
FNAME AS 'FIRST'
MID
LNAME AS 'LAST'
SFX
J0PREF_STREET_LINE1 AS 'STREET1'
J0PREF_STREET_LINE2 AS 'STREET2'
J0PREF_STREET_LINE3 AS 'STREET3'
J0PREF_CITY AS 'CITY'
J0PREF_STAT_CODE AS 'STATE'
J0PREF_ZIP AS 'ZIP'
WORKADDRESS1 AS 'WORKSTREET1'
WORKADDRESS2 AS 'WORKSTREET2'
WORKADDRESS3 AS 'WORKSTREET3'
WORKCITY
WORKSTATE
WORKZIP
ADDRESS_TYPE AS 'ATYPE'
CLASS AS 'CLASS 1'
J1INST_DEGR_YEAR_2 AS 'CLASS 2'
J1INST_DEGR_COLL_CODE_1 AS 'COLL'
J1INST_DEGR_COLL_CODE_2 AS 'COLL 2'
DEG1 AS 'DEG1'
DEG2 AS 'DEG2'
MAJOR1
MAJOR2
EMAIL
ACTIVITY1
ACTIVITY2
COL1 AS 'GIFTAMT1'
COL2 AS 'GIFTDATE1'
COL3 AS 'DESGNAME1'
COL4 AS 'GIFTAMT2'
COL5 AS 'GIFTDATE2'
COL6 AS 'DESGNAME2'
COL7 AS 'GIFTAMT3'
COL8 AS 'GIFTDATE3'
COL9 AS 'DESGNAME3'
COL10 AS 'GIFTAMT4'
COL11 AS 'GIFTDATE4'
COL12 AS 'DESGNAME4'
COL13 AS 'GIFTAMT5'
COL14 AS 'GIFTDATE5'
COL15 AS 'DESGNAME5'
LASTFY AS 'LASTFY'
LASTFYSUM AS 'LASTFYSUM'
SP_ID
SP_PRF
SP_FIRST
SP_MID
SP_LAST
SP_SUFF
SP_EMPSTREET1
SP_EMPSTREET2
SP_EMPSTREET3
SP_EMPCITY
SP_EMPSTATE
SP_EMPZIP
SPYEAR1 AS 'SP CLASS 1'
SPYEAR2 AS 'SP CLASS 2'
SPCOLL1 AS 'SP COLL'
SPCOLL2 AS 'SP COLL2'
SPDEG1 AS 'SP DEG1'
SPDEG2 AS 'SP DEG2'
SPMAJOR1 AS 'SP MAJOR1'
SPMAJOR2 AS 'SP MAJOR2'
SPEMAIL AS 'SP EMAIL'
SPACTIVITY1 AS 'SP ACTIVITY1'
SPACTIVITY2 AS 'SP ACTIVITY2'
COL16 AS 'SP GIFTAMT1'
COL17 AS 'SP GIFTDATE1'
COL18 AS 'SP DESGNAME1'
COL19 AS 'SP GIFTAMT2'
COL20 AS 'SP GIFTDATE2'
COL21 AS 'SP DESGNAME2'
COL22 AS 'SP GIFTAMT3'
COL23 AS 'SP GIFTDATE3'
COL24 AS 'SP DESGNAME3'
COL25 AS 'SP GIFTAMT4'
COL26 AS 'SP GIFTDATE4'
COL27 AS 'SP DESGNAME4'
COL28 AS 'SP GIFTAMT5'
COL29 AS 'SP GIFTDATE5'
COL30 AS 'SP DESGNAME5'
J6COMPANY_NAME
J5COMPANY_NAME
WORKPHONE
SP_WORKPHONE
BY 'DONOR_ID' NOPRINT
ON TABLE PCHOLD FORMAT ALPHA
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N8,
WRAP=6.000000,
$
ENDSTYLE
END

HERE IS A SAMPLE FILE OF DATA ( I used question marks to hide first names, paste in Text and the format will remain)
9545924555950000035Dr Nan????? Y Durnell-Uwechue PO Box 938641 Margate FL 33093 Dept of Communications 777 Glades Rd Boca Raton FL 33431 E11983ASBA Speech durnell@email.com 100.0012312005College of Arts & Sciences 50.0002182005General Scholarship Fund 50.0001302003Annual Fund 15.0004221996Alumni Association Operations Fund 2.0007101990Alumni Association Operations Fund 2006 100.00 .00 .00 .00 .00 .00
3362739280950000038Dr ????? S Adams 2109 Liberty Valley Road Greensboro NC 27406-5431Bennett Clg 900 E Washington St Greensboro NC 27401 E11968ASBA Art Education almaa@ncleg.net OCP10 OHORP 100.0005181998Alumni Association Operations Fund 25.0004101996Alumni Association Operations Fund .00 .00 .00 1998 100.00 .00 .00 .00 .00 .00
2527580522950000064Mrs???? Steele Mebane 2300 Ray Hardee Circle Greenville NC 27858 4101 Stantonsburg Rd Greenville NC 27834 E11968NUBS Nursing asmrn@aol.com 25.0006302004Annual Fund 20.0006221999Alumni Association Operations Fund 15.0007301997Alumni Association Operations Fund 10.0007071993Alumni Association Operations Fund .00 2004 25.00 .00 .00 .00 .00 .00
9102839727950000092Mr ?????? D Wright 19040 US Highway 421 Willard NC 28478 1958ENBS Architectural Engineering 500.0006202007General Scholarship Fund 500.0006062006General Scholarship Fund 500.0006032005General Scholarship Fund 500.0006282004Alumni Scholarship Fund 500.0005202003Annual Fund 2007 .00950000091MrsMaggie M Wright 1946BEBS Elementary Ed, Early Childhood 100.0008142006Annual Fund 100.0002082005Annual Fund 100.0008082003Alumni Association Operations Fund 100.0007262001Alumni Scholarship Fund 100.0003291998Alumni Association Operations Fund
9108757926950000133Col?????? McDougald Waller 347 Hackney Lane Raeford NC 28376 197399 joyce.waller@na.amedd.army.mil 150.0006012004Alumni Scholarship Fund 100.0005291998Alumni Association Operations Fund .00 .00 .00 2004 150.00 .00 .00 .00 .00 .00
9197512962950000172Mr Ra????? D Burrell 304 Smith Place Goldsboro NC 27534-7674PO Box 8002 Goldsboro NC 27533 E11973BEBS Business Administration rayb@wcc.wayne.cc.nc.us 500.0005292007School of Business & Economics 500.0006302006General Scholarship Fund 500.0006032005General Scholarship Fund 50.0005072004School of Business & Economics Scholarships 500.0005032004Annual Fund 2007 500.00 .00 .00 .00 .00 .00
6143378526950000177Mr C??? R Smith 1865 Richtree Road Columbus OH 43219 1973EDNDUG Health & Physical Education 25.0003092007General Scholarship Fund 25.0003172006Athletics Scholarship Fund 10.0008192004Annual Fund 10.0008132004Annual Fund 25.0012122003Annual Fund 2007 25.00 .00 .00 .00 .00 .00
3363799549950000296MrsR??? L Widemon 902 Ross Avenue Greensboro NC 27406-2414 1963ENCERT Secretarial Science 50.0012062004Annual Fund 25.0004012004Capital Campaign Unrestricted Fund 30.0011132003Annual Fund 35.0006012000Alumni Association Operations Fund 25.0006161999Emergency Assistance Fund 2005 50.00 .00 .00 .00 .00 .00
4106513948950000303Mr Al???? K White 30330 Bowland Hill Circle Princess Anne MD 21853-954430330 Bowland Hill Circle Princess Anne MD 21853 E11963ASNDUG Biology 20.0006211993Alumni Association Operations Fund 25.0006201991Alumni Association Operations Fund 25.0005021990Alumni Association Operations Fund .00 .00 1993 20.00 .00 .00 .00 .00 .00
2154724759950000322MrsMi????? Norman Washington 6132 Webster Street Philadelphia PA 19143-2218111 N 49th St Philadelphia PA 19139 E11953EDBS Commercial Education 50.0003092007Annual Fund 50.0005172006Annual Fund 50.0004142003Annual Fund 25.0006251996Alumni Association Operations Fund .00 2007 50.00 .00 .00 .00 .00 .00
2523215879950000377Ms Sh????? Bullock 3975 Sterling Point Drive Unit DDD1 Winterville NC 28590-9253 1993BEBA Marketing bullocks@ecu.edu 15.0006221999Alumni Association Operations Fund .00 .00 .00 .00 1999 15.00 .00 .00 .00 .00 .00
3367670057950000401Mr B???? Wofford 6029 Germanton Rd Winston Salem NC 27105 PO Box 1883 Asheboro NC 27204 E11973BEBS Business Administration 100.0003092007Annual Fund .00 .00 .00 .00 2007 100.00 .00 .00 .00 .00 .00
9107624240950000432Mr Car???? Williams 123 Mercer Avenue Wilmington NC 28403-0730 1968ENBS Industrial Engineering 10.0006241999National Alumni Scholarship Fund .00 .00 .00 .00 1999 10.00 .00 .00 .00 .00 .00
3013360525950000452Mr A???? B Williamson 7014 Valley Park Road Capitol Heights MD 20743-2633Howard University 6th & Girard Streets Northwest Washington DC 20059 E11968EDBS Health & Physical Education awilliamson@howard.edu 100.0004032007Annual Fund 100.0006062006Athletics Unrestricted 50.0011302000Athletics Unrestricted 50.0006302000Alumni Association Operations Fund 100.0006301999Alumni Association Operations Fund 2007 100.00 .00 .00 .00 .00 .00
3362301894950000476Mr Ral????? Bryant 3102 Winchester Drive Greensboro NC 27406 1978EDBS Recreation .00 .00 .00 .00 .00 0000 .00950013004MrsLeonora C Bryant 1601 E Market St Office of the Chancellor Greensboro NC 27411 1999BEBS MSM Management leonora@ncat.edu .00 .00 .00 .00 .00


HOW DO I FIND THE SIZE OF EACH TABLE? CAN I DO IT OR I HAVE TO ASK MY DBA?

If you can set up a synonym for ALL_TABLES which is the tables you can look at and print column NUM_ROWS for the tables you are looking at.

If you cant find the TABLEs underlying your view then set up ALL_DEPENDENCIES and look under NAME = your name and TYPE='VIEW'.

If this does not sort you out and its worth doing to learn a bit about Oracle then speak to your dba.

Regards

John

This message has been edited. Last edited by: hammo1j,

Tracie,

Did you ever try turning XRETRIEVAL OFF and turning SQL traces on to see if you get any WebFOCUS SQL translator error messages?

A cursory look through your code rang several alarm bells: you have five left outer joins. You have DEFINE fields with EDIT, date conversion, concatenation, if-then-else statements. You create a HOLD file and then join this to a database table. All or any of these are potentially inefficient - a WEBFOCUS SQL trace would provide you with error/warning messages, which I find invaluable in making a data extract efficient.

This code sets up a SQL trace. I would create a fex with the code and include it in any program that accesses a database table.

-*-- Set up SQL tracing --------------------------------------------------------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

-*-- Show SQL statements -----------------------------------
SET TRACEON = STMTRACE//CLIENT

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/CLIENT

-*-- Show SQL generated sub-statement trace ----------------
-*SET TRACEON = STMTRACE/2/CLIENT

-*-- Disable the trace stamp (Date/Time etc) ---------------
SET TRACESTAMP = OFF

-*-- Set trace line wrapping - # of characters -------------
SET TRACEWRAP = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

Don't forget to turn XRETRIEVAL OFF before executing the program by adding this just before your data extraction step:

SET XRETRIEVAL = OFF
-RUN

Everything is back to normal now! Our DBA did some fine tuning, now the reports are back running. Thanks for all your input. Our DBA thanks you too.


Tbj

Oracle 10G automatically was cost based instead of rules based, so my DBA had to take it back to rules based. The reports started running again based on this change!