Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
I'm not sure why you are substringing DB-RETRIEVAL-KEY when the individual fields are defined, but therein lies your problem. DB-SEG-SEQ-NUM is a COMP-3 field; when given a string as its argument, INT needs the equivalent of DISPLAY. Use INT(DB-SEG-SEQ-KEY), not INT(SUBSTR(DB-RETRIEVAL-KEY,21,4)).
People from the open systems are trying to access this column using Attunity Studio and they are not able to get the proper data, so we thought of creating a view.
Joined: 03 Oct 2009 Posts: 1787 Location: Bloomington, IL
sushanth bobby wrote:
Can you please elaborate on
Quote:
INT needs the equivalent of DISPLAY
.
Thanks,
Sushanth
If the argument to INT is a string expression, it must resolve to a valid string representation of a number, e.g., "-123" or X'60F1F2F3'; this is, I think, the equivalent of USAGE IS DISPLAY in COBOL (it has been about fifteen years since I last coded in COBOL). As DB-SEG-SEQ-NUM is actually COMP-3 (= packed decimal), attempting to substring it from DB-RETRIEVAL-KEY would give you X'0000123D' instead of X'60F1F2F3'. You have to use the actual COMP-3 field, or do some moderately sophisticated manipulation of the data to get what you want; I wouldn't do that last in SQL, but I don't know if you have a choice.
But this is not planned for batch, since we have implemented Change Data Capture feature to it, this will be read by Attunity CDC whenever a change is made.
And i unloaded the data from the table using DSNTIAUL and created & edited the DCLGEN using cobol specifications mentioned in the beginning of the post and checked the values in the file-aid got the following result,
Code:
DB-COMPANY-CODE DB-KEY-QUALIFIER DB-SEG-ID DB-SEG-SEQ-NUM FILLER DB-PARENT-ID
3/AN 15/AN 2/AN 4/PS 2/BI 2/AN
(27-29) (30-44) (45-46) (47-50) (51-52) (53-54)
6-------------- 7--------------- 8-------- 9------------- 10------ 11----------
********************************* TOP OF DATA **********************-CAPS OFF-*
NL 0002594 HQ 4735 27 HP
NL 0002595 HQ 4814 27 HP
NL 0003408 HQ 6359 27 HP
NL 0004240 HQ 5788 27 HP
NL 0004993 HQ 4180 27 HP
NL 0005343 HQ 3189 27 HP
NL 0006431 HQ 2637 27 HP
The values on the hex column SEG_SEQ_NUM and DB-SEG-SEQ-NUM COMP-3 field match-up with extra C value for being positive,
C - Positive
D - Negative
So, i think i can get the COMP-3 values using combination of CASE, SUBSTR on the HEX values with proper signs.
So far, i have not tested with a negative number, even in production all the values are positive. So, i am thinking of talking with the application people and definately test with a negative number also.
And data will be presented to Attunity using a -ve sign if data is negative.
