One of our developers is noticing an issue where his program runs fine for a small data set but dies when running against a larger data set. We have tried running it as Ad Hoc and as Deferred, both die with the message "Unknown error occurred. Agent on reporting server EDASERVE may have crashed. Please investigate reporting server log.". The Deferred Status page flips into a mode where it appears to be logged in as our service ID (we log into MRE as our personal AD account).

Logs entries (edaprint.log):

02/24/2012 09:43:31 connecting cmrpip041452 tscomid=798,sesid=33268
02/24/2012 09:43:31 processing cmrpip041452 u=e42479,g=E42479,l=USR

.
.
.

02/24/2012 09:58:29 request by edapmon to notify agent has crashed (tscomid=798)
02/24/2012 09:58:29 tscomid  connect  state    active   query time      last cmd         last class 
02/24/2012 09:58:29 >       pid      foc/ext 
02/24/2012 09:58:29          user              sesid                                                
02/24/2012 09:58:29 >                io      
02/24/2012 09:58:29 798      e42479   crashed  33268    Feb 24 09:43:31 TABLE            --         
02/24/2012 09:58:29 >       2240     1796024/3510430
02/24/2012 09:58:29   number of sessions:                       1
02/24/2012 09:58:29   last command:                             TABLE           
02/24/2012 09:58:29   last response time:                       0:00:00.000
02/24/2012 09:58:29   last errnum:                              0
02/24/2012 09:58:29   last executed class:                      --
02/24/2012 09:58:29   last procedure name:                      --                             
02/24/2012 09:58:29   last masterfile name:                     uhlidlist3                     
02/24/2012 09:58:29   focus i/o:                                1796024
02/24/2012 09:58:29   external database input:                  3510430
02/24/2012 09:58:29   number of transactions or hli commands:   3510429
02/24/2012 09:58:29 Agent statistics not available - process is gone
02/24/2012 09:58:29 Current list of connected sessions:

02/24/2012 09:58:29 tscomid sesid state  user     time of  last cmd         codep connected from    
02/24/2012 09:58:29 >       
02/24/2012 09:58:29                               last qry                                          
02/24/2012 09:58:29 >       
02/24/2012 09:58:29 798     33268 crashe e42479   09:43:31 TABLE            437  
02/24/2012 09:58:29 >  tcp=10.51.7.172:4134
02/24/2012 09:58:29 agent started (tscomid=799, pid=1536, svc=DEFAULT)

Here is the code that we think is being executed when the crash occurs.

DEFINE FILE HSX_UHC_ACCOUNT
NEWPROC/A7 = SUBSTR(20, CDM_CODE, 1, 20, 7, NEWPROC);
FEEDKEY/A35 = (COST_CENTER_CODE)||(NEWPROC);
-*
NEWSERVDATE/YYMD = HDATE(SERVICE_DATE, 'YYMD');
NEWSERVDATE2/A8YYMD = NEWSERVDATE;
SVCDT/A8  = EDIT(NEWSERVDATE2, '99999999');
END
-*
TABLE FILE HSX_UHC_ACCOUNT
PRINT
HSHSP_ACCOUNT.ADM_DATE_TIME
HSHSP_ACCOUNT.DISCH_DATE_TIME
HSHSP_TRANSACTIONS.TX_ID
HSHSP_TRANSACTIONS.ORIG_REV_TX_ID     AS 'ORIGREVTXID'
HSHSP_TRANSACTIONS.CHG_CRED_ORIG_ID   AS 'CHGCREDORGID'
HSHSP_TRANSACTIONS.TX_TYPE_HA_C
-*NEW_CODE
QUANTITY
PROCEDURE_DESC
-*SVCDT
TX_AMOUNT
BILLING_PROV_ID
PERFORMING_PROV_ID
UB_REV_CODE_ID
-*FEEDKEY
REVENUE_CODE
-*
CDM_CODE
COST_CENTER_CODE
-*
BY HSHSP_TRANSACTIONS.ORIG_REV_TX_ID
BY HSHSP_TRANSACTIONS.TX_ID
BY HSP_ACCOUNT_ID
BY SERVICE_DATE
-************
WHERE SERV_AREA_ID EQ '&HOSP';
-************
WHERE UB_REV_CODE_ID NE '';
WHERE UB_REV_CODE_ID LT '960' OR UB_REV_CODE_ID GE '990';
WHERE FEEDKEY NE '8480003030004';
-*WHERE RECORDLIMIT EQ '10';
ON TABLE HOLD AS UHLIDLIST3
END




-*!!!!! Seems to die here !!!!!

TABLE FILE UHLIDLIST3
PRINT *
ON TABLE HOLD AS UHLIDLIST4 FORMAT FOCUS INDEX ORIGREVTXID
END

Again, the code runs fine for a small set, but crashes on a larger set. Also, this is the midpoint of the program and the intention is to filedef and hold output to a netshare for an FTP process in another system to pick up and run. Flat file only, no formatting.

Any Ideas?

- ABT

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

Do you mean that the first HOLD UHLIDLIST3 actually gets created, even with a large data set?

If so, why not create the FOCUS hold and INDEX right there. No need to re-read the same data just to create another HOLD.

TABLE FILE HSX_UHC_ACCOUNT
...
ON TABLE HOLD AS UHLIDLIST3 FORMAT FOCUS INDEX ORIGREVTXID
END

Of course, that is not the answer you are expecting to why the whole request dies but at least *may* keep your process moving.

Things to check:

- Did you run out of disk space for the hold?
- Is the file too big for a FOCUS db? (use FORMAT XFOCUS instead)
- Did the downstream process try to pick up the file before it was ready?

It is my understanding that that hold is getting created and is dying on the next true usage of it (in a join statement).

Since there is no output, it is making it difficult to troubleshoot and see where it is getting to. The identified position is a guess since that's the name of the hold file referenced in the error log.

- ABT

quote:

Originally posted by njsden:
Do you mean that the first HOLD UHLIDLIST3 actually gets created, even with a large data set?

If so, why not create the FOCUS hold and INDEX right there. No need to re-read the same data just to create another HOLD.

TABLE FILE HSX_UHC_ACCOUNT
...
ON TABLE HOLD AS UHLIDLIST3 FORMAT FOCUS INDEX ORIGREVTXID
END

Of course, that is not the answer you are expecting to why the whole request dies but at least *may* keep your process moving.

02/24/2012 09:58:29 last masterfile name: uhlidlist3
02/24/2012 09:58:29 focus i/o: 1796024

Definitely indicates that uhlidlist3 was the *input* file, so the crash is occuring in the latter TABLE request.

First off, you don't need TABLE to generate UHLIDLIST4 -- TABLEF should do, and it will avoid a lot of disk activity.

Second, if ultimately you just want a flat file, why are you bothering with format Focus?

Third, if you really need a focus file (to access randomly on ORIGREVTAXID), I suggest you compose an appropriate hierarchical (multiple-segment) focus/xfocus master, and use MODIFY to load it, rather than HOLD FORMAT FOCUS. Keep only one of your two copies of HSHSP_TRANSACTIONS.ORIG_REV_TX_ID, as the key of the root segment; that should cut down on the indexing overhead.

Clarify,
small data set

Clarify,
larger data set

(Sorry been wathing old movies again.)

What data quantity,
Volume wise,
not row wise,
are we talking about?

? WF Release.
? Do you know the limitations of the release.

quote:

-*!!!!! Seems to die here !!!!!
TABLE FILE UHLIDLIST3
PRINT *
ON TABLE HOLD AS UHLIDLIST4 FORMAT FOCUS INDEX ORIGREVTXID
END

Does it still die if you take that index off?

You have:

HSHSP_TRANSACTIONS.ORIG_REV_TX_ID     AS 'ORIGREVTXID'
...
BY HSHSP_TRANSACTIONS.ORIG_REV_TX_ID

You might want to put that alias on the BY-field for the same column and leave the duplicate field out of the file. I'm suspecting you're running into an issue with field aliasing.

Also, did you SET ASNAMES = ON?
Otherwise the field you're trying to index isn't available. Perhaps the crash occurs in the error-path after a whole lot of data got loaded and there turns out to be an error in the table file definition (namely an incorrect index field reference).

We have a case open to something that might be related (hence my suggestions), although the scenario's are different enough that that's probably not the case.

I had it dying on me to. Turns out writing a FOCUS file is quiet complicated.

Problem here was solved by just not using the "FORMAT FOCUS" at all.

Only the very last hold we needed for reporting is a "FORMAT FOCUS" ( or XFOCUS ).

Try that? At least you know it might be the creation and filling the hold file that takes a lot a of time.

I'm surprised that no one suggested that you
-SET &ECHO='ALL'; right before the code you think is crashing and putting a -EXIT after the focus file is built.
I would also recommend that you define ORIGREVTXID and sort on it rather than alias it and get rid of the extra step (It is not what is causing the crash it is just running through the data an extra time). I would also recommend that you try XFOCUS since this works with a small dataset.