Did anyone exceed the limit (2 Gbytes) of focsort file ?
I use WebFocus 5.2.1 on AIX with no limits on dimension of unix files.
Some querys of mine exceed the limit of focsort.
I can't use EXTSORT ON because it is allowed only on MVS environment.
Any hits ?
Thanks in advance
Paolo

I do not know what you are trying to do, but you might try doing a TABLEF and holding/saving the result set format alpha and then call the unix sort procedure via the ! . I have not tryed this but it might work for what you are trying to do. Please post how it turns out. Good Luck

quote:

Originally posted by Busca:
[qb] Did anyone exceed the limit (2 Gbytes) of focsort file ?
I use WebFocus 5.2.1 on AIX with no limits on dimension of unix files.
Some querys of mine exceed the limit of focsort.
I can't use EXTSORT ON because it is allowed only on MVS environment.
Any hits ?
Thanks in advance
Paolo [/qb]

We use Focus for Unix on Solaris, and have run into the 2gb limit. We "chunk" the data by repeatedly selecting subsets of the months of data we're interested in and then appending to the target file using:

cat chunk | gzip >> target

We are having similar issue with older version of FOCUS with 1gig file limit.

Please explain your cat chunk process.

We are trying to use external SYNCSORT but Info Builders tells us that this only works with TABLE FILE and not with MERGE

We are doing a MERGE with a sum so what would you recommend as a work around so we can get the same results?

Thanks

Sorry meant we are using MATCH FILE which causes a merge.

Well, here's the focexec we use to chunk the data based on a date range specified by the &MYY and &MYYE inputs. The include file specified by the &INCLUDE argument is the program that creates a HOLD file that is smaller than the 2Gb limit.

-* CHUNKS - Repeatedly execute extract in periodic chunks.
-*
-* Inputs: &MYY - Month/year start (mmyyyy).
-* &MYYE - Month/year end (mmyyyy).
-* &INCLUDE - The include file to execute.
-* &TARGET - The target file name to create.
-* &LIMIT - The record limit.
-* &PERIOD - The number of months to extract (Default = 12).
-*
-* ----------------------------------------------------------------------
-* 07/08/02 James Muir Initial coding completed.
-* 07/17/02 James Muir If only one chunk to extract, don't append
-* the sequence number to the file name.
-* 07/25/02 James Muir Pass &SEQNO to extract instead of appending
-* in this routine. This approach is more
-* flexible, and will work for hpa as well as
-* the other extracts.
-* ----------------------------------------------------------------------
-*SET &ECHO=ALL;
SET MSG=OFF
-RUN
-*
-* -------------------------- Default period, target, record limit.
-DEFAULTS &TARGET = ' ';
-DEFAULTS &LIMIT = ' ';
-DEFAULTS &PERIOD = '12';
-*
-* -------------------------- Initial sequence number.
-SET &SEQNO = 0;
-*
-* -------------------------- Get starting and ending months and years.
-SET &MM = EDIT(&MYY,'99$$$$');
-SET &YY = EDIT(&MYY,'$$9999');
-SET &MME = EDIT(&MYYE,'99$$$$');
-SET &YYE = EDIT(&MYYE,'$$9999');
-*
-* -------------------------- Nr months to extract.
-SET &MO = ((&YYE - &YY) * 12) - &MM + &MME + 1;
-*
-* -------------------------- Just one chunk, or many?
-IF &MO GT &PERIOD THEN GOTO CHUNKS.200;
-*
-* -------------------------- Only one period to extract. Just do it.
-CHUNKS.100
-TYPE EX &INCLUDE MYY=&MYY, MYYE=&MYYE, TARGET=&TARGET, LIMIT=&LIMIT
EX &INCLUDE MYY=&MYY, MYYE=&MYYE, TARGET=&TARGET, LIMIT=&LIMIT
-RUN
-GOTO CHUNKS.DONE
-*
-* -------------------------- More than one period to extract. Get
-* -------------------------- the starting date in YYM format.
-CHUNKS.200
-SET &YYM = CHGDAT('MYY', 'YYM', &MYY, 'A17');
-*
-* -------------------------- Initialize starting month.
-SET &STARTMO = -.
-*
-* -------------------------- Increment starting month, sequence nr.
-CHUNKS.210
-SET &SEQNO = &SEQNO + 1;
-SET &STARTMO = &STARTMO + .
-*
-* -------------------------- Compute starting date.
-SET &YYM1 = AYM(&YYM , &STARTMO, 'I6');
-SET &MYY1 = CHGDAT('YYM', 'MYY', &YYM1, 'A17');
-SET &MYY = SUBSTR(17,&MYY1,1,6,6,'A6');
-*
-* -------------------------- Have more than &PERIOD months?
-IF &MO GT &PERIOD THEN GOTO CHUNKS.230;
-*
-* -------------------------- Have &PERIOD or less months.
-CHUNKS.220
-SET &MYYE = &MME || &YYE;
-GOTO CHUNKS.240
-*
-* -------------------------- Have more than &PERIOD months.
-CHUNKS.230
-SET &YYME1 = AYM(&YYM, &STARTMO + &PERIOD - 1, 'I6');
-SET &MYYE1 = CHGDAT('YYM', 'MYY', &YYME1, 'A17');
-SET &MYYE = SUBSTR(17,&MYYE1,1,6,6,'A6');
-GOTO CHUNKS.240
-*
-* -------------------------- Run the extract.
-CHUNKS.240
-TYPE EX &INCLUDE MYY=&MYY, MYYE=&MYYE, TARGET=&TARGET, LIMIT=&LIMIT
EX &INCLUDE MYY=&MYY, MYYE=&MYYE, TARGET=&TARGET, LIMIT=&LIMIT, SEQNO=&SEQNO
-RUN
-*
-* -------------------------- Decrement month counter.
-SET &MO = &MO - .
-IF &MO GT 0 THEN GOTO CHUNKS.210;
-GOTO CHUNKS.DONE
-*
-* -------------------------- Cleanup. Reset dates and sequence number so
-* -------------------------- that subsequent extracts are not affected.
-CHUNKS.DONE
-SET &SEQNO = 0;
-SET &MYY = &MM || &YY;
-SET &MYYE = &MME || &YYE;


Each focexec referenced by &INCLUDE calls a routine called RENAMETF that puts the chunks together. Here it is:

-* RENAMETF - Rename files for loading, compress file as necessary.
-*
-* Inputs: &SOURCE - The source file name (without suffix).
-* &TARGET - The target file name (without suffix).
-* &SEQNO - The target file sequence number.
-* &COMPRESS - If 'Y' then compress the data.
-*
-* ----------------------------------------------------------------------
-* 07/17/02 James Muir Initial coding completed.
-* 08/05/02 James Muir Incrementally compress/concatenate.
-* 08/12/02 James Muir Bug fix; force gzip in some cases.
-* ----------------------------------------------------------------------
-*SET &ECHO=ALL;
SET MSG=OFF
-RUN
-*
-* --------------------- Default seqno, compress flag.
-DEFAULT &SEQNO = 0;
-DEFAULT &COMPRESS = 'N';
-*
-* --------------------- No chunks, first chunk or subsequent chunks?
-IF &SEQNO EQ 0 THEN GOTO RENAMET.100;
-IF &SEQNO EQ 1 THEN GOTO RENAMET.200;
-GOTO RENAMET.300
-*
-* ----------------------------------------------------------------------
-* ---------------------- No chunking being done ------------------------
-* ----------------------------------------------------------------------
-*
-* --------------------- Rename .ctl
-RENAMET.100
-SET &S01 = &SOURCE || '.ctl';
-SET &T01 = &TARGET || '.ctl';
-*
-UNIX mv &S01 &T01 2>/dev/null
-*
-* --------------------- Rename .mas
-RENAMET.110
-SET &S02 = &SOURCE || '.mas';
-SET &T02 = &TARGET || '.mas';
-*
-UNIX mv &S02 &T02 2>/dev/null
-*
-* --------------------- Rename .lyo
-RENAMET.120
-SET &S03 = &SOURCE || '.lyo';
-SET &T03 = &TARGET || '.lyo';
-*
-UNIX mv &S03 &T03 2>/dev/null
-*
-* --------------------- Rename .ftm
-RENAMET.130
-SET &S04 = &SOURCE || '.ftm';
-*
-UNIX ls &S04 1>/dev/null 2>/dev/null
-IF &RETCODE NE 0 THEN GOTO RENAMET.140;
-*
-SET &T04 = &TARGET || '.ftm';
-*
-UNIX mv &S04 &T04 2>/dev/null
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.140;
-UNIX gzip -f &T04 2>/dev/null
-*
-* --------------------- Rename .txt
-RENAMET.140
-SET &S05 = &SOURCE || '.txt';
-*
-UNIX ls &S05 1>/dev/null 2>/dev/null
-IF &RETCODE NE 0 THEN GOTO RENAMET.DONE;
-*
-SET &T05 = &TARGET || '.txt';
-*
-UNIX mv &S05 &T05 2>/dev/null
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.DONE;
-UNIX gzip -f &T05 2>/dev/null
-GOTO RENAMET.DONE
-*
-* ----------------------------------------------------------------------
-* ------------------------ First chunk being done ----------------------
-* ----------------------------------------------------------------------
-*
-* --------------------- Rename .ctl
-RENAMET.200
-SET &S01 = &SOURCE || '.ctl';
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.205;
-*
-SET &T01 = &TARGET || '.ctl';
-UNIX mv &S01 &T01 2>/dev/null
-GOTO RENAMET.210
-*
-RENAMET.205
-SET &T01 = &TARGET || '_1.ctl';
-UNIX mv &S01 &T01 2>/dev/null
-GOTO RENAMET.210
-*
-* --------------------- Rename .mas
-RENAMET.210
-SET &S02 = &SOURCE || '.mas';
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.215;
-*
-SET &T02 = &TARGET || '.mas';
-UNIX mv &S02 &T02 2>/dev/null
-GOTO RENAMET.220
-*
-RENAMET.215
-SET &T02 = &TARGET || '_1.mas';
-UNIX mv &S02 &T02 2>/dev/null
-GOTO RENAMET.220
-*
-* --------------------- Rename .lyo
-RENAMET.220
-SET &S03 = &SOURCE || '.lyo';
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.225;
-*
-SET &T03 = &TARGET || '.lyo';
-UNIX mv &S03 &T03 2>/dev/null
-GOTO RENAMET.230
-*
-RENAMET.225
-SET &T03 = &TARGET || '_1.lyo';
-UNIX mv &S03 &T03 2>/dev/null
-GOTO RENAMET.230
-*
-* --------------------- Rename .ftm
-RENAMET.230
-SET &S04 = &SOURCE || '.ftm';
-*
-UNIX ls &S04 1>/dev/null 2>/dev/null
-IF &RETCODE NE 0 THEN GOTO RENAMET.240;
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.235;
-*
-SET &T04 = &TARGET || '.ftm';
-UNIX mv &S04 &T04 2>/dev/null
-UNIX gzip -f &T04 2>/dev/null
-GOTO RENAMET.240
-*
-RENAMET.235
-SET &T04 = &TARGET || '_1.ftm';
-UNIX mv &S04 &T04 2>/dev/null
-GOTO RENAMET.240
-*
-* --------------------- Rename .txt
-RENAMET.240
-SET &S05 = &SOURCE || '.txt';
-*
-UNIX ls &S05 1>/dev/null 2>/dev/null
-IF &RETCODE NE 0 THEN GOTO RENAMET.DONE;
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.245;
-*
-SET &T05 = &TARGET || '.txt';
-UNIX mv &S05 &T05 2>/dev/null
-UNIX gzip -f &T05 2>/dev/null
-GOTO RENAMET.DONE
-*
-RENAMET.245
-SET &T05 = &TARGET || '_1.txt';
-UNIX mv &S05 &T05 2>/dev/null
-GOTO RENAMET.DONE
-*
-* ----------------------------------------------------------------------
-* --------------------- Handle subsequent chunks -----------------------
-* ----------------------------------------------------------------------
-*
-* --------------------- Rename .ctl
-RENAMET.300
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.310;
-*
-SET &S01 = &SOURCE || '.ctl';
-*
-UNIX rm &S01 2>/dev/null
-*
-* --------------------- Rename .mas
-RENAMET.310
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.320;
-*
-SET &S02 = &SOURCE || '.mas';
-*
-UNIX rm &S02 2>/dev/null
-*
-* --------------------- Rename .lyo
-RENAMET.320
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.330;
-*
-SET &S03 = &SOURCE || '.lyo';
-*
-UNIX rm &S03 2>/dev/null
-*
-* --------------------- Rename .ftm
-RENAMET.330
-SET &S04 = &SOURCE || '.ftm';
-*
-UNIX ls &S04 1>/dev/null 2>/dev/null
-IF &RETCODE NE 0 THEN GOTO RENAMET.340;
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.335;
-*
-SET &T04 = &TARGET || '.ftm.gz';
-UNIX cat &S04 | gzip >> &T04 2>/dev/null
-UNIX rm &S04
-GOTO RENAMET.340
-*
-RENAMET.335
-SET &T04 = &TARGET || '_' || &SEQNO || '.ftm';
-UNIX mv &S04 &T04 2>/dev/null
-GOTO RENAMET.340
-*
-* --------------------- Rename .txt
-RENAMET.340
-SET &S05 = &SOURCE || '.txt';
-*
-UNIX ls &S05 1>/dev/null 2>/dev/null
-IF &RETCODE NE 0 THEN GOTO RENAMET.DONE;
-*
-IF &COMPRESS NE 'Y' THEN GOTO RENAMET.345;
-*
-SET &T05 = &TARGET || '.txt.gz';
-UNIX cat &S05 | gzip >> &T05 2>/dev/null
-UNIX rm &S05
-GOTO RENAMET.DONE
-*
-RENAMET.345
-SET &T05 = &TARGET || '_' || &SEQNO || '.txt';
-UNIX mv &S05 &T05 2>/dev/null
-GOTO RENAMET.DONE
-*
-* --------------------- Cleanup.
-RENAMET.DONE


A program that wants to use the chunking code would have the following code in it that specifies the target file to create, and the name of the focexec to run. The &PERIOD arg specifies the number of months of data to chunk.

-*
-* -------------------------- Create charges extract data.

-SET &TARGET = 'MYTARGET';
-SET &INCLUDE = 'MYRPT';
-SET &PERIOD = 12;
-*
-INCLUDE CHUNKS


And your focexec, myrpt.fex, would have to call RENAMETF as follows:


-* myrpt.fex
.....
extract data whatever
.....
-*
-* -------------------------- Rename target file for loading.
EX RENAMETF SOURCE='mydata', TARGET=&TGTNAME, COMPRESS='Y', SEQNO=&SEQNO
-RUN


This is complicated, but it's how we worked around the 2Gb limit.