Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] VSAM Join on partial key

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] VSAM Join on partial key
 Login/Join
 
Gold member
posted
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,


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
Expert
posted Hide Post
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
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Gold member
posted Hide Post
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


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
Master
posted Hide Post
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
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
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


WF 7.6.6, FOCUS 7.6.4, IBM MVS/TSO, Windows 2003 Server, DB2, MSSQL
 
Posts: 65 | Location: Chicago, IL | Registered: July 26, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved] VSAM Join on partial key

Copyright © 1996-2020 Information Builders