|
View previous topic :: View next topic
|
| Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi,
I have a COBOL copy book specification like below for a DB2 Column DB_RETRIEVAL_KEY CHAR(87)
| Code: |
10 DB-RETRIEVAL-KEY.
15 DB-COMPANY-CODE PIC X(03)
15 DB-KEY-QUALIFIER PIC X(15).
15 DB-SEG-ID PIC X(2).
15 DB-SEG-SEQ-NUM PIC S9(7) COMP-3.
15 FILLER PIC S9(4) COMP.
15 DB-PARENT-ID PIC X(2).
15 DB-PARENT-SEQ-NUM PIC S9(7) COMP-3.
15 FILLER PIC S9(7) COMP-3.
15 FILLER PIC S9(7) COMP-3.
15 DB-PROD-CODE PIC X(2).
15 FILLER PIC X(6).
15 FILLER PIC S9(3) COMP-3.
15 FILLER PIC X(37).
|
So, right now i am splitting up the data as per the copybook specification and i am facing a problem while converting the COMP-3 data.
I am getting below error.
| Code: |
SELECT DB_RETRIEVAL_KEY
, SUBSTR ( DB_RETRIEVAL_KEY , 1 , 3 ) AS COMPANY_CODE
, SUBSTR ( DB_RETRIEVAL_KEY , 4 , 15 ) AS KEY_QUALIFIER
, SUBSTR ( DB_RETRIEVAL_KEY , 19 , 2 ) AS SEG_ID
, INT(SUBSTR ( DB_RETRIEVAL_KEY , 21 , 4 ))
FROM VTGDATAX.GIXXE
;
DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT
ACCEPTABLE TO THE INT FUNCTION
DSNT418I SQLSTATE = 22018 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXRINT SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -135 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF79' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION
BPA0012E: DB2 SQL/DDL ERROR HAS OCCURRED - ROLLBACK ISSUED.
RETCODE = 8
|
Output using HEX
| Code: |
SELECT DB_RETRIEVAL_KEY
, SUBSTR ( DB_RETRIEVAL_KEY , 1 , 3 ) AS COMPANY_CODE
, SUBSTR ( DB_RETRIEVAL_KEY , 4 , 15 ) AS KEY_QUALIFIER
, SUBSTR ( DB_RETRIEVAL_KEY , 19 , 2 ) AS SEG_ID
, HEX(SUBSTR ( DB_RETRIEVAL_KEY , 21 , 4 ))
FROM VTGDATAP.GIXXE
fetch first 50 rows only;
DB_RETRIEVAL_KEY COMPANY_CODE KEY_QUALIFIER SEG_ID
NL 0002594HQ °* HP U1 NL 0002594 HQ 0004735C
NL 0002595HQ a< HP U1 NL 0002595 HQ 0004814C
NL 0003408HQ Î HP U1 NL 0003408 HQ 0006359C
NL 0004993HQ HP U1 NL 0004993 HQ 0004180C
NL 0005343HQ Î HP U1 NL 0005343 HQ 0003189C
NL 0006431HQ Ü@ HP U1 NL 0006431 HQ 0002637C
NL CL0002370HQ ×* HP U1 NL CL0002370 HQ 0002705C
NL CL0002370HQ ×% HP U1 NL CL0002370 HQ 0002706C
NL CL0002377HQ ¨ HP U1 NL CL0002377 HQ 0003581C
NL CL0002377HQ ¨ HP U1 NL CL0002377 HQ 0003582C
NL CL0002378HQ «% HP U1 NL CL0002378 HQ 0004486C
NL CL0002378HQ «@ HP U1 NL CL0002378 HQ 0004487C
NL CL0002960HQ HP U1 NL CL0002960 HQ 0003082C
NL CL0003118HQ HP U1 NL CL0003118 HQ 0003080C
NL CL0003229HQ q HP U1 NL CL0003229 HQ 0002981C
NL CL0004283HQ ó HP U1 NL CL0004283 HQ 0003188C
NL CL0005258HQ Î HP U1 NL CL0005258 HQ 0003099C
NL CL0006099HQ HP U1 NL CL0006099 HQ 0002273C
NL CL0006393HQ Ý HP U1 NL CL0006393 HQ 0001652C
NL CL0006666HQ l HP U1 NL CL0006666 HQ 0001933C
NL CL0007069HQ ×< HP U1 NL CL0007069 HQ 0002704C
NL CL0007268HQ « HP U1 NL CL0007268 HQ 0001483C
NL CL0007406HQ HP U1 NL CL0007406 HQ 0002081C
NL LS0002972HQ * HP U1 NL LS0002972 HQ 0000005C
NL LS0004697HQ HP <U1 U1 NL LS0004697 HQ 0000130C
NL LS0004697HQ HP <U1 U1 NL LS0004697 HQ 0000130C
NL LS0018407HQ * HP U1 NL LS0018407 HQ 0000005C
NL LS0018407HQ * HP U1 NL LS0018407 HQ 0000005C
NL LS0018407HQ * HP U1 NL LS0018407 HQ 0000005C
NL LS0019733HQ HP <U1 U1 NL LS0019733 HQ 0000020C
NL LS0019898HQ * HP <U1 %U1 NL LS0019898 HQ 0000065C
NL LS0030797HQ * HP U1 NL LS0030797 HQ 0000005C
NL LS0030983HQ * HP U1 NL LS0030983 HQ 0000005C
NL LS0031186HQ * HP U1 NL LS0031186 HQ 0000005C
|
Can you please suggest me how to proceed with this conversion.
Thank You,
Sushanth |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
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)). |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Akatsukami,
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.
Can you please elaborate on
| Quote: |
| INT needs the equivalent of DISPLAY |
.
Thanks,
Sushanth |
|
| Back to top |
|
|
Akatsukami
Global Moderator
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. |
|
| Back to top |
|
|
John Kondrick
New User
Joined: 15 Jun 2010
Posts: 2
Location: USA
|
|
|
|
| I suggest you use a second step in your job. Unload the table and then use DFSORT to reformat the packed decimal to character data. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi John,
Thanks for the suggestion.
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.
Sushanth |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi,
Using the below query, i got the following result,
| Code: |
SELECT SUBSTR ( DB_RETRIEVAL_KEY , 1 , 3 ) AS COMPANY_CODE
, SUBSTR ( DB_RETRIEVAL_KEY , 4 , 15 ) AS KEY_QUALIFIER
, SUBSTR ( DB_RETRIEVAL_KEY , 19 , 2 ) AS SEG_ID
, HEX ( SUBSTR ( DB_RETRIEVAL_KEY , 21 , 4 ) ) AS SEG_SEQ_NUM
FROM VTGDATAP.GIXXE
;
|
| Code: |
COMPANY_CODE KEY_QUALIFIER SEG_ID SEG_SEQ_NUM
NL 0002594 HQ 0004735C
NL 0002595 HQ 0004814C
NL 0003408 HQ 0006359C
NL 0004240 HQ 0005788C
NL 0004993 HQ 0004180C
NL 0005343 HQ 0003189C
NL 0006431 HQ 0002637C
|
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.
Please let me know if my thinking is wrong.
Thanks,
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Sushanth,
Have you run a test with a negative number? How should this be presented to the Attunity CDC? With a minus '-' or simply the D sign? |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Dick,
| Quote: |
| Have you run a test with a negative number? |
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.
Thanks,
Sushanth |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
int(substr(HEX ( SUBSTR ( DB_RETRIEVAL_KEY , 21 , 4 ) ),1,7))
*
case when substr(HEX ( SUBSTR ( DB_RETRIEVAL_KEY , 21 , 4 ) ),8,1)) = 'D' then -1 else +1 end |
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Thank You Very Much GuyC, that was a time-saver.
d.sch, Negative number testing is done. It Worked.
Thanks Again,
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
| Quote: |
| Negative number testing is done. It Worked. |
Cool - thanks for the feedback,
d |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
