IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Converting COMP-3 data stored in CHAR column


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Sep 28, 2010 9:26 pm
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Tue Sep 28, 2010 9:48 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Sep 28, 2010 10:10 pm
Reply with quote

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
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL

PostPosted: Tue Sep 28, 2010 10:30 pm
Reply with quote

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
View user's profile Send private message
John Kondrick

New User


Joined: 15 Jun 2010
Posts: 2
Location: USA

PostPosted: Tue Sep 28, 2010 10:40 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Tue Sep 28, 2010 11:04 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Sep 29, 2010 8:44 am
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 29, 2010 8:56 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Sep 29, 2010 9:18 am
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Sep 29, 2010 3:36 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Sep 29, 2010 9:27 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Sep 30, 2010 12:09 am
Reply with quote

Quote:
Negative number testing is done. It Worked.
Cool - thanks for the feedback,

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts SCOPE PENDING option -check data DB2 2
No new posts RC query -Time column CA Products 3
Search our Forums:

Back to Top