RE: Unable to create keyed SQL files -- MIDRANGE-L

Hi Brent,

Based on: "The reason code is 7 - System Cross Reference files are damaged."
I'd say to look into RCLSTG SELECT(*DBXREF).
Found that here: http://archive.midrange.com/midrange-l/200907/msg00484.html

-Kurt

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx
Sent: Wednesday, May 15, 2013 3:20 PM
To: Midrange Systems Technical Discussion
Subject: Re: Unable to create keyed SQL files

Have you tried using:

Primary Key (RPT_FROMDATE, RPT_TODATE));

Also you can combine all of those label statements:
Label on column TXQTRRPT (
FROMDATE Text is 'FROM DATE',
TODATE Text is 'TO DATE',
CNFRDATE Text is 'CANCEL FROM DATE',
CNTODATE Text is 'CANCEL TO DATE',
VOLCNT Text is 'VOLUNTARY COUNT',
VOLAMT Text is 'VOLUNTARY AMOUNT',
PDCNT Text is 'PD COUNT',
PDAMT Text is 'PD AMOUNT',
PRCFLAG Text is 'IN PROCESS FLAG',
SBMDATE Text is 'SUBMIT DATE',
SBMUSER Text is 'SUBMIT USER',
SBMTIME Text is 'SUBMIT TIME',
SBMJOB Text is 'SUBMIT JOB',
SBMJOBNO Text is 'SUBMIT JOB NUMBER');

Thanks,
Tommy Holden

From: "Raymond B. Dunn" <Raymond.Dunn@xxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: 05/15/2013 03:12 PM
Subject: Unable to create keyed SQL files
Sent by: midrange-l-bounces@xxxxxxxxxxxx

All:

We are having an issue with our SQL files. If I use Implementor, or the STRSQL green screen entry, I get the same issue.

I get a run time dump of SQLCA, and diagnostic area,

On my compile listing, I get an error message SQL0901 - Record 1, Position 1 SQL system error.

In the joblog it lists:

CPD32B0 Diagnostic - 20 - Constraint is not valid. Constraint *N cannot be added for file xxxxx in library xxxx for TYPE value *PRIKEY.
The reason code is 7 - System Cross Reference files are damaged.

If I take the keys out of the file, it compiles just fine. I have been deleting the existing file before recreating the new one if it existed.

Does anyone know what file might be damaged? Has anyone else hit this before?

The file compiled fine yesterday, but this morning we are getting these issues. There have been no system updates since yesterday.

We're working with IBM, but it seems like this is taking them WAY too long to get any answers.

Here's the source:
----------------------
Create Table TXQTRRPT
(RPT_FROMDATE for column FROMDATE DATE not null,
RPT_TODATE for column TODATE DATE not null,
RPT_CNFRDATE for column CNFRDATE DATE not null,
RPT_CNTODATE for column CNTODATE DATE not null,
RPT_VOLCNT for column VOLCNT DEC(09, 3) not null,
RPT_VOLAMT for column VOLAMT DEC(11, 2) not null,
RPT_PDCNT for column PDCNT DEC(09, 3) not null,
RPT_PDAMT for column PDAMT DEC(11, 2) not null,
RPT_PRCFLAG for column PRCFLAG CHAR(01) not null,
RPT_SBMDATE for column SBMDATE DATE not null,
RPT_SBMUSER for column SBMUSER CHAR(10) not null,
RPT_SBMTIME for column SBMTIME TIME not null,
RPT_SBMJOB for column SBMJOB CHAR(10) not null,
RPT_SBMJOBNO for column SBMJOBNO CHAR(06) not null,

Primary Key (FROMDATE, TODATE));

Label on TXQTRRPT (FROMDATE Text is 'FROM DATE');
Label on TXQTRRPT (TODATE Text is 'TO DATE');
Label on TXQTRRPT (CNFRDATE Text is 'CANCEL FROM DATE');
Label on TXQTRRPT (CNTODATE Text is 'CANCEL TO DATE');
Label on TXQTRRPT (VOLCNT Text is 'VOLUNTARY COUNT');
Label on TXQTRRPT (VOLAMT Text is 'VOLUNTARY AMOUNT');
Label on TXQTRRPT (PDCNT Text is 'PD COUNT');
Label on TXQTRRPT (PDAMT Text is 'PD AMOUNT');
Label on TXQTRRPT (PRCFLAG Text is 'IN PROCESS FLAG');
Label on TXQTRRPT (SBMDATE Text is 'SUBMIT DATE');
Label on TXQTRRPT (SBMUSER Text is 'SUBMIT USER');
Label on TXQTRRPT (SBMTIME Text is 'SUBMIT TIME');
Label on TXQTRRPT (SBMJOB Text is 'SUBMIT JOB');
Label on TXQTRRPT (SBMJOBNO Text is 'SUBMIT JOB NUMBER');

Label on Table TXQTRRPT is 'TEXAS QUARTERLY REPORT FILE';

Thank you all for any assistance you can give!
-Brent Dunn

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.