Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Hexadecimal representation of VARCHAR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming
View previous topic :: :: View next topic  
Author Message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Jan 30, 2012 8:54 pm    Post subject: Hexadecimal representation of VARCHAR
Reply with quote

Hello Friends,

I unloaded 2 fields of a table using BMC utility and data defination of these 2 fields in the DCL is geiven below:

Code:

01  DCLCCV.                                       
    10 EMP-ID        PIC S9(18)V USAGE COMP-3.   - 10 bytes 
 10 DEPT-ID.                                   
    49 DEPT-ID-LEN  PIC S9(4) USAGE COMP.        -  2bytes
    49 DEPT-ID-TEXT  PIC X(20).                          - 20 bytes



The data in the first row of the table is :

emp-id : 200000000000080200
DEPT-ID-LEN : 11
DEPT-ID-TEXT : 00000252905


The unload file browsed is shown below:

Code:


----+----1----+----2----+----3----
----+----F----+----F----+----F----
----+----1----+----2----+----3----
 ---------------------------------
*********************************
                                 
                                 
 ---------------------------------
.......Ø....00000252905..........
000000082000FFFFFFFFFFF0000000000
200000000C0B000002529050000000000


I want to understand. The first 10 bytes represent the Packed decimal right with C representing the + sign. The next 2 bytes 000B shows the value of the varchar length= 11

FFFFFFFFFFF
00000252905

This I am not very clear with how the conversion takes place from hex to char. these 11 bytes should be equal to 00000252905. Can anone help me here to understanding the encoding in detail??

I think the record length should be 32 (10 for PD + 2 bytes varchar length + 20 varchar data) then why in the hex we see 33 bytes and the file also shows reclength of 33. Where is that extra byte coming into picture I am not able to interpret.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


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

PostPosted: Mon Jan 30, 2012 9:21 pm    Post subject: Re: Hexadecimal representation of varchar
Reply with quote

Rijit wrote:
I unloaded 2 fields of a table using BMC utility and data defination of these 2 fields in the DCL is geiven below:

Code:

01  DCLCCV.                                       
    10 EMP-ID        PIC S9(18)V USAGE COMP-3.   - 10 bytes 
 10 DEPT-ID.                                   
    49 DEPT-ID-LEN  PIC S9(4) USAGE COMP.        -  2bytes
    49 DEPT-ID-TEXT  PIC X(20).                          - 20 bytes



The data in the first row of the table is :

emp-id : 200000000000080200
DEPT-ID-LEN : 11
DEPT-ID-TEXT : 00000252905


The unload file browsed is shown below:

Code:


----+----1----+----2----+----3----
----+----F----+----F----+----F----
----+----1----+----2----+----3----
 ---------------------------------
*********************************
                                 
                                 
 ---------------------------------
.......Ø....00000252905..........
000000082000FFFFFFFFFFF0000000000
200000000C0B000002529050000000000


I want to understand. The first 10 bytes represent the Packed decimal right with C representing the + sign. The next 2 bytes 000B shows the value of the varchar length= 11

FFFFFFFFFFF
00000252905

This I am not very clear with how the conversion takes place from hex to char. these 11 bytes should be equal to 00000252905. Can anone help me here to understanding the encoding in detail??

I think the record length should be 32 (10 for PD + 2 bytes varchar length + 20 varchar data) then why in the hex we see 33 bytes and the file also shows reclength of 33. Where is that extra byte coming into picture I am not able to interpret.

Why do you think that DEPT-ID-TEXT should undergo any conversion?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Mon Jan 30, 2012 11:37 pm    Post subject: Reply to: Hexadecimal representation of varchar
Reply with quote

For your "extra" byte, is it something like this?

I also don't know what "conversion" you'd be expecting. Maybe you should google for EBCDIC and see if that helps.

The value you show in hex is the hex representation of those characters.

How can you "save space" by packing a 20-digit employee id, and then use 20 bytes for an 11-digit department id?

Off-topic, but who the heck do you work for which employs so many people in so many departments?
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Tue Jan 31, 2012 2:42 pm    Post subject:
Reply with quote

I think 1 xtra byte is for the null indicator. the Question here is the field here below

DCLCCV.
10 EMP-ID PIC S9(18)V USAGE COMP-3. - 10 bytes
10 DEPT-ID.
49 DEPT-ID-LEN PIC S9(4) USAGE COMP. - 2bytes
49 DEPT-ID-TEXT PIC X(20).


sum up to 32 bytes but the unload file has 33 bytes in it.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Jan 31, 2012 3:30 pm    Post subject: Reply to: Hexadecimal representation of varchar
Reply with quote

since today is not psychic day ...
with the info You provided it is impossible to explain the extra byte.

You are the only one who can research why it is there

Quote:
I unloaded 2 fields of a table using BMC utility and data defination of these 2 fields in the DCL is geiven below:

what You should have told are not the COBOL declares, but the table DDL
( the statements used to create the table )
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Tue Jan 31, 2012 6:56 pm    Post subject:
Reply with quote

icon_wink.gif ..yes i mean the table declaration in the DCL..Now can you explain why the extra byte is there when we unload these fields from the table.?
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1219
Location: Israel

PostPosted: Tue Jan 31, 2012 7:25 pm    Post subject: Re: Hexadecimal representation of varchar
Reply with quote

Rijit wrote:
Code:
    10 EMP-ID        PIC S9(18)V USAGE COMP-3.   - 10 bytes 

The data in the first row of the table is :
emp-id : 200000000000080200
The unload file browsed is shown below:
Code:
----------
.......Ø..
0000000820
200000000C

The first 10 bytes represent the Packed decimal right with C representing the + sign.

This is correct. Did you notice how you have to read the number in the hexadecimal representation?
First top, first bottom, second top, second bottom and so on ?
This gives you X'0200000000000080200C' which is your emp-id in COMP-3 format.


Rijit wrote:
Code:
    49 DEPT-ID-LEN  PIC S9(4) USAGE COMP.        -  2bytes

The data in the first row of the table is :
DEPT-ID-LEN : 11
The unload file browsed is shown below:
Code:
--
..
00
0B

The next 2 bytes 000B shows the value of the varchar length= 11

Correct again. Although it is not obvious, but you have to read 0 from top-left, then 0 from bottom-left, then 0 from top-right and finally B from bottom-right.
This gives you X'000B' which is 11 in COMP format.


Rijit wrote:
Code:
    49 DEPT-ID-TEXT  PIC X(20).                          - 20 bytes

The data in the first row of the table is :
DEPT-ID-TEXT : 00000252905
The unload file browsed is shown below:
Code:
-----------
00000252905
FFFFFFFFFFF
00000252905

This I am not very clear with how the conversion takes place from hex to char. these 11 bytes should be equal to 00000252905. Can anone help me here to understanding the encoding in detail??

Assuming the same reading method used for the two previous values, you now have X'F0F0F0F0F0F2F5F2F9F0F5' which is you dept-id in display format!
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Tue Jan 31, 2012 8:35 pm    Post subject:
Reply with quote

Rijit wrote:
I think 1 xtra byte is for the null indicator. the Question here is the field here below

DCLCCV.
10 EMP-ID PIC S9(18)V USAGE COMP-3. - 10 bytes
10 DEPT-ID.
49 DEPT-ID-LEN PIC S9(4) USAGE COMP. - 2bytes
49 DEPT-ID-TEXT PIC X(20).


sum up to 32 bytes but the unload file has 33 bytes in it.


Did you read the link I provided? Can you confirm that it explains nothing to you?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Jan 31, 2012 8:52 pm    Post subject: Reply to: Hexadecimal representation of varchar
Reply with quote

frommy previous reply
Quote:
what You should have told are not the COBOL declares, but the table DDL


wiser for You to see an ophthalmologist icon_cool.gif
- You did not read correctly my comment, reread it please

reworded...
we do not care about the COBOL declaration

we need to see the DDL in simpler words the CREATE TABLE statements

and the statements used to unload the table

IT is pretty deterministic,
the reason for the appearance of some data can be found in the event chain that created it
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Wed Feb 01, 2012 12:33 am    Post subject:
Reply with quote

Bill Woodger wrote:
Rijit wrote:
I think 1 xtra byte is for the null indicator. the Question here is the field here below

DCLCCV.
10 EMP-ID PIC S9(18)V USAGE COMP-3. - 10 bytes
10 DEPT-ID.
49 DEPT-ID-LEN PIC S9(4) USAGE COMP. - 2bytes
49 DEPT-ID-TEXT PIC X(20).


sum up to 32 bytes but the unload file has 33 bytes in it.


Did you read the link I provided? Can you confirm that it explains nothing to you?


Bill I went through it. It was helpful..What I understood is the unload utility automatically allocates a single char byte for null indicator for each variable which is a not null right?
Pls correc me if I m wrong
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Wed Feb 01, 2012 12:40 am    Post subject: Re: Reply to: Hexadecimal representation of varchar
Reply with quote

enrico-sorichetti wrote:
frommy previous reply
Quote:
what You should have told are not the COBOL declares, but the table DDL


wiser for You to see an ophthalmologist icon_cool.gif
- You did not read correctly my comment, reread it please

reworded...
we do not care about the COBOL declaration

we need to see the DDL in simpler words the CREATE TABLE statements

and the statements used to unload the table

IT is pretty deterministic,
the reason for the appearance of some data can be found in the event chain that created it


I am sorry but I am not able to understand what you are trying to say icon_cry.gif icon_mad.gif
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Feb 01, 2012 12:52 am    Post subject: Reply to: Hexadecimal representation of varchar
Reply with quote

I give up...
36_2_18.gif 36_2_18.gif 36_2_18.gif
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Wed Feb 01, 2012 1:06 am    Post subject: Re: Reply to: Hexadecimal representation of varchar
Reply with quote

Rijit wrote:
enrico-sorichetti wrote:
we need to see the DDL in simpler words the CREATE TABLE statements

and the statements used to unload the table


I am sorry but I am not able to understand what you are trying to say icon_cry.gif icon_mad.gif

icon_rolleyes.gif
  1. Run, using QMF, SPUFI, DSNTEP*, or whatever tool is convenient for you, the following query:
    Code:
    SELECT COLNO, SUBSTR(NAME,1,18) AS NAME, COLTYPE, LENGTH, NULLS
    FROM SYSIBM.SYSCOLUMNS                                         
    WHERE TBNAME = 'my-table'                                         
    ORDER BY COLNO             

    Post the results, copied and pasted and enclosed in Code tags.
  2. Post the contents of the SYSIN data set, in-stream, PS or PDS member, of the unload step. Copy and paste them and enclose them in Code tags.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Wed Feb 01, 2012 8:28 pm    Post subject: Re: Reply to: Hexadecimal representation of varchar
Reply with quote

Akatsukami wrote:
Rijit wrote:
enrico-sorichetti wrote:
we need to see the DDL in simpler words the CREATE TABLE statements

and the statements used to unload the table


I am sorry but I am not able to understand what you are trying to say icon_cry.gif icon_mad.gif

icon_rolleyes.gif
  1. Run, using QMF, SPUFI, DSNTEP*, or whatever tool is convenient for you, the following query:
    Code:
    SELECT COLNO, SUBSTR(NAME,1,18) AS NAME, COLTYPE, LENGTH, NULLS
    FROM SYSIBM.SYSCOLUMNS                                         
    WHERE TBNAME = 'my-table'                                         
    ORDER BY COLNO             

    Post the results, copied and pasted and enclosed in Code tags.
  2. Post the contents of the SYSIN data set, in-stream, PS or PDS member, of the unload step. Copy and paste them and enclose them in Code tags.



Code:


 SELECT COLNO, SUBSTR(NAME,1,18) AS NAME, COLTYPE, LENGTH, NULLS     
 FROM SYSIBM.SYSCOLUMNS                                             
 WHERE TBNAME = 'CONTK'                                         
 AND TBCREATOR = 'WFG05'                                             
 ORDER BY COLNO                                                     
 WITH UR;

---------+---------+---------+---------+---------+---------+---------
 COLNO  NAME                COLTYPE   LENGTH  NULLS                 
---------+---------+---------+---------+---------+---------+---------
 1  *************       DECIMAL       19  N                     
  2  *************      DECIMAL       19  N                     
  3  *************     DECIMAL       19  N                     
  4  *************       VARCHAR       20  Y  ---this is field DEPT-ID                   


the table DDL shows this field DEPT-ID as VARCHAR(20) FOR MIXED DATA
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Feb 01, 2012 8:41 pm    Post subject: Reply to: Hexadecimal representation of varchar
Reply with quote

since the dept-id is defined as nullable then the extra byte is the NULL indicator
but You will have to read the BMC manuals for confirmation ...

You might try to insert a record with a NULL dept-id and see how the unload looks like
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Wed Feb 01, 2012 9:05 pm    Post subject: Re: Reply to: Hexadecimal representation of varchar
Reply with quote

Rijit wrote:
Code:


 SELECT COLNO, SUBSTR(NAME,1,18) AS NAME, COLTYPE, LENGTH, NULLS     
 FROM SYSIBM.SYSCOLUMNS                                             
 WHERE TBNAME = 'CONTK'                                         
 AND TBCREATOR = 'WFG05'                                             
 ORDER BY COLNO                                                     
 WITH UR;

---------+---------+---------+---------+---------+---------+---------
 COLNO  NAME                COLTYPE   LENGTH  NULLS                 
---------+---------+---------+---------+---------+---------+---------
 1  *************       DECIMAL       19  N                     
  2  *************      DECIMAL       19  N                     
  3  *************     DECIMAL       19  N                     
  4  *************       VARCHAR       20  Y  ---this is field DEPT-ID                   


the table DDL shows this field DEPT-ID as VARCHAR(20) FOR MIXED DATA

And it also shows that DEPT_ID can be NULL (see the 'Y' in column NULLS, unlike for all the other rows?)

Now, we senpai have probably written a few hundred kilobytes -- if not more -- on the subject of DB2 nulls over the years. Suffice to say, there is no value of a discrete byte - from zero to 255, X'00' to X'FF' -- that may not be a valid value for a given DB2 column. Nonetheless, DB2 really does have a NULL value, because DB2 is operating at a higher level of abstraction than MVS (you understand that there are no physical constructs directly corresponding to tables, triggers, referential constraints, etc., but that these are conceptual constructs, yes?)

If DB2 needs a NULL value, but no physical representation can be used, what is to be done? Simplicity itself: DB2 creates an indicator. If your unload JCL has -- as it should -- a SYSCNTL DD statement not directed to DUMMY, look at the load cards generated. They will look something like
Code:
(EMP_ID POSITION(1:10) DECIMAL,
DEPT_ID POSITION(11:*) VARCHAR
  NULLIF NULL1=X'6F'
    NULL1 POSITION(*:*) CHAR(1)
)

That NULL1 is the indicator variable showing DEPT_ID is in fact NULL, and takes up an extra byte in your output record.
Back to top
View user's profile Send private message
Rijit

Active User


Joined: 15 Apr 2010
Posts: 168
Location: Pune

PostPosted: Mon Feb 06, 2012 5:11 pm    Post subject: Re: Reply to: Hexadecimal representation of varchar
Reply with quote

enrico-sorichetti wrote:
since the dept-id is defined as nullable then the extra byte is the NULL indicator
but You will have to read the BMC manuals for confirmation ...

You might try to insert a record with a NULL dept-id and see how the unload looks like


I again did the unload exercise with a syscntl card. I got the exact record layout in this. The last byte after the varchar data was reserved for the null indicator. So in BMCUNLOAD if we specify the syscntl field it is easy to find the mapping of data in the unloaded extract file.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> COBOL Programming All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Convert Decimal to hexadecimal Steve Ironmonger DFSORT/ICETOOL 5 Fri May 27, 2016 4:19 pm
No new posts Need help on comparing hexadecimal va... ashok_uddaraju DFSORT/ICETOOL 4 Thu Apr 28, 2016 2:08 am
No new posts Only text from varchar Seppe DB2 8 Thu Apr 21, 2016 10:42 pm
No new posts Declaration of varchar in cobol s.saranya srinivasan COBOL Programming 6 Fri Sep 25, 2015 11:43 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us