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
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS
Request access to the private WebFOCUS User Group
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.
Go
Search
Notify
Admin
New PM!
Platinum Member posted June 01, 2009 02:21 PM
I have been scratching my head lately trying to figure out how to extract a D12.2 data element from an internal DDS table.
The field on our DDS is 144 bytes and consists of a recurring number of 12.2 elements that hold previous dollar amounts. Is there a simple solution in WF to extract this?
This message has been edited. Last edited by: T.Peters , June 09, 2009 10:44 AM
Posts: 127 | Location: San Antonio | Registered: May 29, 2009
IP
Virtuoso Yes there is
you should create a master with the field that holds the specific data with OCCURS=...
take a look in the documentation to find out the exaxt coding.
Frank prod: WF 7.6.10 platform Windows,
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006
IP
Virtuoso Suppose that, in your MASTER, your field is defined as:
FIELDNAME=PREVIOUS, ALIAS=PREV, USAGE=A144, ACTUAL=A144, $
If all your data elements are D fields, each takes up 8 bytes, hence you have 18 such fields.
Now 2 possibilities: either you want to access each field separately or you want to access them as occurences.
In the first case, you would change your MASTER to have 18 definitions of the type:
FIELDNAME=PREVIOUS1, ALIAS=PREV1, USAGE=D12.2, ACTUAL=D8, $
FIELDNAME=PREVIOUS2, ALIAS=PREV2, USAGE=D12.2, ACTUAL=D8, $
...
FIELDNAME=PREVIOUS18, ALIAS=PREV18, USAGE=D12.2, ACTUAL=D8, $
In the second case, you want to redefine the PREVIOUS field as follows, supposing that the PREVIOUS field is in segment SEG1:
SEGNAME=SEG1, ...
FIELDNAME=PREVIOUS, ALIAS=PREV, USAGE=A144, ACTUAL=A144, $
...
SEGNAME=PREVS, PARENT=SEG1, POSITION=PREVIOUS, OCCURS=18
FIELDNAME=PREVD, ALIAS=PREVD, USAGE=D12.2, ACTUAL=D8, $
When you use the field PREVD Focus will automatically loop through the occurences.
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006
IP
<JG> posted June 02, 2009 03:00 AM
In addition to Frank and Daniel's suggestion to coding an OCCURS into the master
Platinum Member Thanks for the input, guys, these sound like they might work. I'll see what I can do to get them to work. I think the SEGNAME solution will take some time to get done because I don't have the ability to manipulate the text version of the metadata through Dev Studio, but I'll see if I can copy the existing file to a new MASTER and define it as Danny suggested.
Posts: 127 | Location: San Antonio | Registered: May 29, 2009
IP
Platinum Member Understanding that I am completely green to the FOCUS language, I had to google what you guys were suggesting I do so that I could understand what is happening. With that in mind, can you help clear something up for me that I am confused about?
I read on
This site that the "POSITION" keyword indicates where the table is to start and it appears that it reads the next N fields in order until it reaches it's number of OCCURS? If that is the case, I think I explained my scenario wrong. I have 18 D12.2 values in the SAME A144 field in a packed format that I need to extract. Could I be mistaken in what I am reading?
Posts: 127 | Location: San Antonio | Registered: May 29, 2009
IP
Expert Can you post the COBOL description?
POSITION is used to re-describe an area in the main master. It is not necessary if the repeating fields are at the end of the record.
The best place to read about this stuff is in a mainframe FOCUS manual, the section on describing VSAM or fixed format files.
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006
IP
Virtuoso Can you post your master?
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006
IP
Platinum Member Here's my master file text view:
quote:
FILENAME=NCP01_FS008, SUFFIX=DB2 , $
My table are all the "Prev" fields, specifically ASPRVER. That is my 144 byte field that has the packed data in it.
The COBOL looks like this:
05 ACCUM-RANGE-HISTORICAL
OCCURS 14 TIMES.
* * 1-MTH1 2-MTH2 3-MTH3 4-MTH4 5-MTH5 6-MTH6 7-MTH7 8-MTH8 9-MTH9 10-MTH10 11-MTH11 12-MTH12 13-TOTAL (1 THRU 12 MTHS) 14-TOTAL (13 THRU 24 MTHS) **
07 H-NET-PREM PIC S9(09)V99.
07 H-NET-FEE PIC S9(09)V99.
07 H-EARNED-PREMIUM PIC S9(09)V99.
07 H-PAID-LOSSES PIC S9(09)V99.
07 H-LOSS-RATIO PIC S9(09)V99.
07 H-PENETRATION-PCT PIC S9(09)V99.
07 H-WAIVED-PCT PIC S9(09)V99.
07 H-FLAT-CANCELLED PIC S9(09)V99.
07 H-ITD-FUNDED-PREM PIC S9(09)V99.
07 H-ITD-REFUND-PREM PIC S9(09)V99.
Posts: 127 | Location: San Antonio | Registered: May 29, 2009
IP
Expert Here is a sample of what your master might look like for just the earned premium. You would have to repeat this for every group. You will need a segment for every group. After your regular part of the master, you add:
SEGNAME=ERNDPREM,SEGTYPE=S0,PARENT=NCP01_FS008,POSITION=AS_PREV_EARNED_PREMIUM,OCCURS=24,$
$ Not quite sure how many occurences you have.
FIELDNAME=EARNED_PREMIUM,ALIAS=ERND_PREM, FORMAT=P12.2,ACTUAL=P6,$
FIELDNAME=EARNED_ORDER, ALIAS = ORDER, USAGE = I2MT, ACTUAL = I4, $
$ Previous line from JG and optional.
Replicate this block in order for each group you need to redefine.
Hope this helps.
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006
IP
Platinum Member Well this was a very valuable learning lesson for us at SWBC. We learned that it is very important to realize that DB2 on the iSeries is very different than being on a SQL Server, Oracle, or some other file structure.
What we learned in regards to this problem is that a DB2 structure has no mechanism for storing values in an internal table, so an OCCURS clause will not work for us. IBI support in New York seemed to thing that being iSeries DB2 may provide us some mechanism for incorporating that ability, but we haven't been explained that solution yet.
So what did I do to get this data out? Ok, first I went into Data Migrator and opened the SQL statement that would pull from my MFD. I created a new define field using that source and used the HEX() function to first pull the data out of packed format. This effectively put the entire A144 field into a giant text string.
002820
00160F = 00002186200F
There were 24 of these in the giant text. I was then able to substring out the values I needed and in my SQL statement in DM, I moved the 24 substringed fields into their new locations in my newly created source.
My final command looked like:
SUBSTR(HEX(AS_PREV_EARNED_PREMIUM) FROM n FOR 11)
This is EXTREMELY cumbersome, but unless IBI can work out the simpler way to do this, I suppose it will just have to be accepted.
Posts: 127 | Location: San Antonio | Registered: May 29, 2009
IP
Gold member T.Peters,
I think in your case that the best solution would be for you to create a set of user written subroutines that can extract data from a given 144 byte cluster into a given field. As you know from this thread, FOCUS can probably read your data using an alternate master, but this requires a different master for every request you make. (This would be done in two steps: first, the DB2 request to get the raw data into a hold file, and then a second request from the hold file but using the alternate master).
The user functions would look something like this:
MYFLDA/P12.2 = GET144(ASPRVSP1,1,MYFLDA) ;
MYFLDB/P12.2 = GET144(ASPRVSP1,2,MYFLDB) ;
MYFLDC/P12.2 = GET144(ASPRVSP1,3,MYFLDC) ;
MYFLDD/P12.2 = GET144(ASPRVSP1,4,MYFLDD) ;
Here, e.g., you are obtaining the first 4 values from the field ASPRVSP1 (one of your 144 byte segments)
These functions should be very simple to write, but it will require 3GL expertise, and as I'm not an iSeries specialist, I can't guarantee success. But it should work, and once you've written one, all others will be very simple.
You can even write, for example, routines to do special processing on the 144 byte fields:
MYAVG/P12.2 = GET144AV(ASPRVSP1,MYAVG) ;
This is also the kind of thing you can have done using IBI's professional services. They can write a few of these for you, and using them as a template, you can write any others you may need.
Expert What about creating a DB2 view that would do all the subtringing in SQL?
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, 2005
IP
Please Wait. Your request is being processed...
Copyright © 1996-2020 Information Builders
Read-Only Topic
