I'm joining a HOLD file that has partial keys to a VSAM file and using WHERE statemenmts to select the correct instance. The VSAM file has a Group Key defined as follows:

GROUP=TRANKEY,ALIAS=KEY,USAGE=A49,ACTUAL=A45,$
FIELD=DATA_FILE ,,A2 ,A2 ,$ 3-4
FIELD=DATA_USER ,,A2 ,A2 ,$ 5-6
FIELD=DATA_BANK ,,I4 ,I2 ,$ 7-8
FIELD=KEY_ACCT ,ACCT15 ,P15L ,P8 ,$ 9-16
FIELD=KEY_SCTY_CNTRY ,COUNTRY ,A1 ,A1 ,$ 17-17
FIELD=KEY_SCTY_CUSIP ,FSECNR ,A9 ,A9 ,$ 18-26
FIELD=KEY_SCTY_DATE ,SECDATE ,I8YYMD ,I4 ,$ 27-30
FIELD=KEY_SCTY_QUAL ,SECQUAL ,I4 ,I2 ,$ 31-32
FIELD=KEY_DATE ,ENTDT ,I8YYMD ,I4 ,$ 33-36
FIELD=KEY_APPL ,APPL ,A1 ,A1 ,$ 37-37
FIELD=KEY_ENTRY_DATE ,INPDT ,I8YYMD ,I4 ,$ 38-41
FIELD=KEY_ENTRY_TIME ,INPTIME ,I8 ,I4 ,$ 42-45
FIELD=KEY_TS_UNIQ ,INPUNIQ ,I4 ,I2 ,$ 46-47

The HOLD file is defined with a field called TSKEY that consist ofthe first 14 characters of TRANKEY above.

JOIN TSKEY IN HOLD TO ALL TRANKEY IN FND1TS1 AS J1 (Note the ALL option)

The HOLD file contains each of the individual elements of the key which I then use to select the correct instance of data.

TABLE FILE HOLD
SUM TRANS
BY TSKEY
WHERE FND1TS1.ACTL_SETL_DATE EQ ASETDATE ;
WHERE FND1TS1.KEY_SCTY_CUSIP EQ R1_FSECNR ;
WHERE FND1TS1.KEY_SCTY_DATE EQ R1_SECDATE ;
WHERE FND1TS1.KEY_DATE EQ R1_ENTDT ;
WHERE FND1TS1.KEY_ENTRY_DATE EQ R1_INPDT ;
WHERE FND1TS1.TRAD_ORDER EQ R1_ORDER ;
ON TABLE HOLD ....
END

When I run the request it works but if I have just 10 rows in the HOLD file I'm using 5 minutes CPU and 12million I/O's (the VSAM file has approximately 1.3million rows). It appears that FOCUS is starting from the beginning of the VSAM file and reading forward with each transaction.

Any ideas?

Alan

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

Boy, it has been a long time since I've done this! The documentation in the mainframe FOCUS manual that I have states that (and I'll paraphrase) with an initial subset join, the primary key is described by a key GROUP (which you have); the initial subset is the first field in that group--which you don't have.

Remember in joins, a big rule is that the formats have to match. You are joining an A14 to an A45 and that is a no-no. You will have to add another field, possibly a group to your VSAM MFD if that is possible.

The other problem that you have is that some of the fields in the initial subset have numeric formats, i.e. I and P. The constructed TSKEY will have to look exactly like that internally for the join to work.

I hope this was helpful.

Check out the join chapter in your MF FOCUS manual for more verbiage on this.

Ginny,
Thank you so much! It's working now and runs in no time. I coded a variation of what you suggested: TSKEY is 49 bytes instead of 45 in the crfile but I was able to used EDIT to substitute some values and to expand it so that I have the full key.

Alan

It's also been a while since I've done this, but you might consider building a master/vsam with alternate indexes. Unfortunately, I don't have the old mainframe manual.

Pat,
I was able to resolve the problem by using EDIT to recreate the key in the crfile. Turns out the key contains two I2 fields (Usage=I4, Actual=I2) that are actually I4, so I concatenated x'00' into those positions, followed by the remainder of the key, and it works perfectly -- and runs fast!

Thanks for responding.

Alan