I'm currently trying to upgrade to 7.6.5 from 7.1.1. We're currently trying out a new Hub/Sub server set up. Our Hub is an NT server and our sub is an OS400 server. Our current configuration has both servers considered a hub/sub.

Anyways, I finally have the meta data working, however, I receive this error whenever I run a specific report :

(FOC1400) SQLCODE IS -302 (HEX: FFFFFED2)
L (FOC1406) SQL OPEN CURSOR ERROR. : F00092
(FOC1400) SQLCODE IS 1400 (HEX: 00000578)
: SQLCODE IS -302 (HEX: FFFFFED2)
(FOC1407) SQL FETCH CURSOR ERROR. : F00092


The content of my FEX is as follows:

JOIN
F00092.F00092.NUM_KEY_1 IN F00092 TO MULTIPLE
F060116V1.F060116V1.ADDRESS_NUMBER IN F060116V1 AS J1
END
JOIN
F060116V1.F060116V1.ADDRESS_NUMBER IN F00092 TO MULTIPLE
F060117V1.F060117V1.ADDRESS_NUMBER IN F060117V1 AS J2
END
DEFINE FILE F00092
ERCOST/D12.2=65.00;
EECOST/D12.2=F00092.USER_DEFINED_AMOUNT - ERCOST - F00092.USER_DEFINED_AMOUNT__2;
END
TABLE FILE F00092
PRINT
'F00092.F00092.SUPPLEMENTAL_DATABASE_CODE' NOPRINT
'F00092.F00092.QUANTITY' NOPRINT AS 'Priority,Record'
'F00092.F00092.USER_DEFINED_DAYS' NOPRINT AS 'Purchasing ,Frequency'
'F00092.F00092.ENDING_EFFECTIVE_DATE' AS 'Next Eligible,Order Date'
'F00092.F00092.EFFECTIVE_DATE' AS 'Last Ordered,Date'
'F00092.F00092.TYPE_DATA' NOPRINT
'F00092.F00092.NUM_KEY_1' NOPRINT
'F060117V1.F060117V1.ADDITIONAL_NAME' AS 'Last,Name'
'F060117V1.F060117V1.PREFERRED_NAME'
'F060116V1.F060116V1.CATEGORY_CODE_02_CODE_DESC_1' NOPRINT AS 'Emp,Type'
'F00092.F00092.USER_DEFINED_CODE' NOPRINT AS 'PO,Type'
'F00092.F00092.REMARK' NOPRINT AS 'Style'
'F00092.F00092.REMARKS___LINE_2' NOPRINT AS 'Size'
'F00092.F00092.USER_DEFINED_AMOUNT/D12.2' NOPRINT AS 'Total Cost'
BY 'F060116V1.F060116V1.DATE_TERMINATED'
BY 'F060116V1.F060116V1.ALPHA_NAME' NOPRINT
BY 'F00092.F00092.ENDING_EFFECTIVE_DATE' NOPRINT
HEADING
"MASTER LISTING"
"SHOES"
"By Priority Code"
WHERE ( F00092.F00092.SUPPLEMENTAL_DATABASE_CODE EQ 'E' )
AND ( F00092.F00092.TYPE_DATA EQ 'SH' )
AND ( F00092.F00092.QUANTITY EQ 1 )
AND ( F060116V1.F060116V1.PAY_STATUS_CODE GE '0' )
AND ( F060116V1.F060116V1.DATE_TERMINATED LE '01/01/1923' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=ON,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
$
TYPE=HEADING,
LINE=1,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=14,
$
TYPE=HEADING,
LINE=2,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=14,
$
TYPE=HEADING,
LINE=3,
JUSTIFY=CENTER,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
SIZE=14,
$
ENDSTYLE
END


After messing with the report and trying to narrow down the problem I came to the where clause and found that if I removed the "AND ( F060116V1.F060116V1.DATE_TERMINATED LE '01/01/1923' )" portion, the report ran. Thus, I thought that there must be a problem with the metadata for this field.

I looked on the OS400 side and viewed the converted (using the JDE Enterprise One adapter) metadata as :

FIELDNAME=DATE_TERMINATED, ALIAS=YADT, USAGE=MDY, ACTUAL=P6JUL,
TITLE='Date,Term.', DESCRIPTION='Date Terminated', $

follwed by a define as :

DEFINE YADT/MDY=YADT; ACCESS_PROPERTY=(INTERNAL),
TITLE='Date,Term.', DESCRIPTION='Date Terminated', $



I looked on the NT side and viewed the metadata for this field as :

FIELDNAME=DATE_TERMINATED, ALIAS=DATE_TERMINATED, USAGE=MDY, ACTUAL=DATE,
TITLE='Date,Term.', DESCRIPTION='Date Terminated', $


On our current 7.1.1 server the metadata for this field is :
ALIAS='YADT', USAGE=MDY, ACTUAL=P6JUL,
TITLE='Date,Term.',
DESCRIPTION='Date Terminated',$


Upon reviewing these three spots I really do not see anything wrong with the metadata, yet I get this error code. Would anyone be willing to shed some light on this problem?

For testing purposes only, I would put a trace in the HUB program and trap the SQL. Then I would log onto the SUB console, go to procedures and paste the SQL and try running it against the native adapter. If it does not run, you will have to figure out what date format the native adapter requires and adjust your metadata on the hub side so that the proper format is generated.

Trace commands:

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF
  

USAGE needs to be MDYY I think for F060116V1.F060116V1.DATE_TERMINATED LE '01/01/1923'

We had this kind of problem with the date (hub Unix /sub AS400) PRIOR to 7.6.5 (We opened a case on it - we had to DEOPTIMIZE our SQL to get date criteria to work). It appears to me to be fixed in 7.6.5 (AS400 7.6.5). It is interesting that you are still getting it. I may have to run more tests.

Ginny,

I tried your tests and I got back the SQL that was being ran on the HUB side. I tried to run the sql in the proceedures side of the sub server(AS400) but it would not run because it did not recognize any of the text.

I then tried to run the whole fex through the proceedures on the SUB side and it ran fine.

I tried the same thing on the HUB side and I got the error.

Hammo,

I tried changing the usage to MDYY on both servers but I received the same error message. I tried one side and not the other, then vice versa and still received the error message. I doubt this is the problem since I can get the fex to run on the sub side.


N.Selph,
I'm not sure what you mean you had to deoptimize the sql

Try removing the slashes from the date. Make sure the MDYY is the usage on the date and the Define. If that doesn't work, try defining a field with usage=P6 that is equal to DATE_TERMINATED and test it for zeroes.

Pat,

I tried the removeal of slashes as well as the MDYY usage with no results. I created a defined field and made it a P6 with a result as 0 in the field like you suggested may appear.

I added the set all = on and I'm able to run the report.

To deoptimize the SQL, you make a date defined field from your real date field and test in the WHERE clause on the defined (not real) field. If you look at the sql generated (on the hub), you see that the WHERE clause on the date is not present.
Normally you don't want this to happen. This is just a bug workaround with SEVERE performance penalties.
However, we have had to do this to avoid the type of problem reported by PHawk, where a valid sql statement containing a date criterion on the hub, errors out at the sub-server (AS400) layer.
On our sub-server we have APT=OFF (Automatic pass through turned off) in the edasprof.prf because we are using a JDE adapter. If you don't have this configuration, chances are you will not see this problem, because the SQL does not get retranslated at the sub-server level, but passes through.