|
View previous topic :: View next topic
|
| Author |
Message |
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
 ..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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006
Posts: 1353
Location: Israel
|
|
|
|
| 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:
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 
- 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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| 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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| 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
- 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   |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
- 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.
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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| 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 |
- 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.
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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
Rijit
Active User
Joined: 15 Apr 2010
Posts: 168
Location: Pune
|
|
|
|
| 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 |
|
|
|
|
