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.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to migrate Focus dbs to SQL tables ?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How to migrate Focus dbs to SQL tables ?
 Login/Join
 
Platinum Member
posted
I have searched Focal Point and Advanced Search (and just what is so 'Advanced' about this generally useless tool ?) long and hard to find pretty much nothing on the subject of converting Focus databases to SQL Server tables.
This was frustrating, bit I tried to figure it out on my own...

As an example question on this subject, suppose we have a simple 2 segment Focus file:

FILENAME=EXAMPLE , SUFFIX=FOC ,$
SEGNAME=SEG1 ,SEGTYPE=S1 ,$
FIELD=KEY_1 ,ALIAS=K1 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE1 ,ALIAS=ATT1 ,FORMAT=A10 ,$
...
SEGMENT=SEG2 ,PARENT=SEG1 ,SEGTYPE=S1 ,$
FIELD=KEY_2 ,ALIAS=K2 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE21 ,ALIAS=ATT21 ,FORMAT=A10 ,$
...

I created two tables in SQL Server:

Table SEG1 with one primary key KEY_1,
Table SEG2 with a primary key consisting of KEY_2 and KEY_1 (as a foreign key).

So far so good, but I then tried to test a Maintain program written for the Focus file, after adjusting the Path Spec statements,
on my new tables and synonyms, and it keeps failing with a 'stack contains multiple paths' error on one of the SEG2 table stacks.

I don't know quite what is going on here and would like to get some idea of just what are the best practices (or ordinary ones for that matter) for converting Focus databases to SQL tables.

Thanks,


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Master
posted Hide Post
I don't know if it is best practice, but I would use WebFocus to pull the data into a flat file and then use SQL insert to load the SQL database.


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
Expert
posted Hide Post
I think Dave is looking for a solution to a more complex problem - how to convert a Maintain program to use relational database tables instead of a hierarchical database table.

At first I thought creating a view that joins the two SQL Server tables would be enough, but I don't think that would do the job, probably because (I know nothing of Maintain) the program traverses the two segments of the hierarchical database table differently than it would for relational database tables.

The job entails more than just replacing FOCUS databases by relational tables.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
quote:
The job entails more than just replacing FOCUS databases by relational tables


I haven't used Maintain for 5 or 6 years so I may be completely off base on this one, but I think Francis hit it right on. It seems like there was a big difference between loading from a stack into a multi-segment FOCUS table and loading into two relational database tables. I'll defer to the Maintain gurus for additional direction on exactly how it should be accomplished, but I believe, as Francis said, that there are a few more steps involved than just moving the tables.

This message has been edited. Last edited by: Darin Lee,


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Master
posted Hide Post
Dave
If you are getting the "Stack contains multiple paths" error, make sure that you are prefacing the field names with the Master name. You have to do this in NEXT statements, WHERE statements, Update, Include, Delete and Revise. Since the Masters have fields with the same names in them, you must do this.

So, while in FOCUS you may have had:
FOR ALL NEXT KEY_1 KEY_2 INTO STACK
Now you would have to have:
FOR ALL NEXT SEG1.KEY_1 INTO STACK1
and
FOR ALL NEXT SEG2.KEY_1 SEG2.KEY_2 INTO STACK2

You can use criteria from STACK1 to load up STACK2, but you can't load the data from both Masters into one stack.

Mark
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
Expert
posted Hide Post
Darin, "Tony"? "Francis" - a collision of wise-guys.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
While others have adressed converting an APPLICTION let me address the first question posed which was about how "converting Focus databases to SQL Server tables" which can most easily done using our ETL (Extract-Transform-Load) tool called DataMigrator.

The think to keep in mind here is that FOCUS as a hierarchical database handles the relationships between the segments automatically, but for an RDBMS the relationships are *explicit* in the synonym.

You can do this easily with Data Management Console modeling view, but here's a MASTER file for your MS SQL Server tables.

FILENAME=EXAMPLE , SUFFIX=SQLMSS ,$
SEGNAME=SEG1 ,SEGTYPE=S0 ,$
FIELD=KEY_1 ,ALIAS=K1 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE1 ,ALIAS=ATT1 ,FORMAT=A10 ,$
...
SEGMENT=SEG2 ,PARENT=SEG1 ,SEGTYPE=S0 ,  CRJOINTYPE=LEFT_OUTER, $
FIELD=KEY_1S ,ALIAS=K1 ,FORMAT=A8 ,INDEX=I,$
FIELD=KEY_2 ,ALIAS=K2 ,FORMAT=A8 ,INDEX=I,$
FIELD=ATTRIBUTE21 ,ALIAS=ATT21 ,FORMAT=A10 ,$
  


And here's an ACCESS file:

SEGNAME=SEG1, TABLENAME=example_seg1, KEYS=1, WRITE=YES, $
SEGNAME=SEG2, TABLENAME=example_seg2, 
   KEYFLD=KEY_1, IXFLD=KEY_1S ,  KEYS=2,  WRITE=YES, $


Now from the Data Management Console you can select your synonym and "Recreate DBMS Table" and it will create TWO tables for you.

Then a simple data flow with your FOCUS file as the source and the synonym above as a target is all that's needed to copy the data from FOCUS to MS SQL Server.


N/A
 
Posts: 397 | Location: New York City | Registered: May 03, 2007Report This Post
Virtuoso
posted Hide Post
Sorry Francis - I had just been reading a post by Tony and got my wise guys mixed up. Credit to you.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Mark,

Thanks for the advice. I fully qualified the WHERE fields and my .mnt finally ran ! You da man !

Note: my maintains always treat segments separately so they *should* work with tables easily.

Clif,

Thanks for the DataMigrator advice. I'd really like to work with it, but don't think the customer is going to pop for the extra expense for a one time thing...


All,

While the maintain conversion was my immediate problem, I'd really like some good practical advice on how to structure relational tables from Focus database. I know the general idea, but not real world stuff - strategies, rules-of thumb, gotchas, etc.

Thanks,


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Silver Member
posted Hide Post
Hi. I know that this is months late, but the way that I did most of this was through MODIFY using CASE LOGIC and then write to file using TYPE ON Statement. This extracted the data. I then use SSIS to load the data into SQL Server.
Any one that would like a copy of the code that I use, let me know.
regards steve njm870t@hotmail.com


WebFOCUS 7.7.01 Windows, TSO 7.0
 
Posts: 43 | Registered: April 18, 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     How to migrate Focus dbs to SQL tables ?

Copyright © 1996-2020 Information Builders