|
View previous topic :: View next topic
|
| Author |
Message |
Vamshi Veludandi
New User
Joined: 17 Mar 2009
Posts: 27
Location: Bangalore
|
|
|
|
Hi,
We have a table for which the DDL is as follows:
| Code: |
CREATE TABLE
EMP_TAB
( Emp_No INTEGER NOT NULL
GENERATED ALWAYS
AS IDENTITY
( START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 20000
NO CYCLE
NO CACHE
NO ORDER ),
E_code CHAR(3)
)
|
I tried to retrieve data from 2 columns of the table EMP_TAB.
| Code: |
SELECT Emp_no,
'|',
E_code
FROM EMP_TAB |
and expected the output file to be of length 4+1+3 = 8 and the character '|' would be positioned at column no 5. But it didn't.
| Code: |
COMMAND ===>
EMP-ID D E-CODE
4/BI 1/AN 3/AN
(1-4) (5-5) (6-8)
1---------- 2------- 3-------
****** ***************************** TOP OF DATA ******************-CAPS OFF-*
000001 1 |
000002 2 |
000003 3 |
000004 4 |
****** **************************** BOTTOM OF DATA ****************-CAPS OFF-* |
Only when I changed the layout, I was able to see the data correctly.
| Code: |
COMMAND ===> SCROLL ===> CSR
EMP-ID A B C D E-CODE
4/BI 1/AN 1/AN 1/AN 1/AN 3/AN
(1-4) (5-5) (6-6) (7-7) (8-8) (9-11)
1---------- 2------- 3------- 4------- 5------- 6-------
****** ***************************** TOP OF DATA ******************-CAPS OFF-*
000001 1 | 111
000002 2 | 222
000003 3 | 333
000004 4 | 444
****** **************************** BOTTOM OF DATA ****************-CAPS OFF-* |
Just wondering why 3 bytes of data has appeared adjacent to the IDENTITY column ?
Do we ever have to handle that data ?
Thanks,
Vamshi. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| Only when I changed the layout, |
 |
|
| Back to top |
|
|
Vamshi Veludandi
New User
Joined: 17 Mar 2009
Posts: 27
Location: Bangalore
|
|
|
|
Hi,
With the assumption that the output file created by the unload job would of length 8, I created a copybook with 3 fields (as mentioned in the select) and tried to view the file with the copybook layout.
But the data in the file didn't match the copybook layout correctly.
Therefore I inserted a field with 3 bytes after the first field making the whole copybook length as 11 bytes.
Only then was I able to map the data correctly.
Thanks,
Vamshi |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
did this '|' not come as varchar?
view the unload file with hex and let us see it
(no, do not use the utillity with the copybook
just a straight view of the unload file)
and use the following sql:
| Code: |
SELECT Emp_no,
'|',
E_code
FROM EMP_TAB
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
'|' is probaly unloaded as a nullable varchar .
Record layout of the unload file would show up in SYSPUNCH if it wasn't set to dummy. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
for info:
SELECT CHAR('|',1)
FROM ...
would result in a 1 byte fixed length char string.
you could also use SUBSTR('|',1,1) |
|
| Back to top |
|
|
Vamshi Veludandi
New User
Joined: 17 Mar 2009
Posts: 27
Location: Bangalore
|
|
|
|
Dick / Guyc / Enrico,
Thanks a lot.
I really had no idea that whenever a hard coded value is given in the select statement, DB2 considers value within quotes as a VARCHAR field and a numeric value as INTEGER.
I even tried SELECT CHAR('|') FROM ... but no joy.
Only when I mentioned the length i.e. SELECT CHAR('|',1) FROM ...
was i able to get the expected result.
Thankyou all once again.
Regards,
Vamshi. |
|
| Back to top |
|
|
|
|
