DB2 Unload using Utility

Elixir · Posted: Mon Mar 28, 2011 7:36 pm

Hi,

My aim is to unload a DB2 table onto a file,

The Sysin
------------------------------------
Select Col1,---> 20 bytes
col2,---->20 bytes
col3,-->2 bytes
case col4
when '*' then 'star'
end
from table1, table2, table 3
------------------------------------

On unloading im am getting a Lowvalue(X'00') and a X'0F' between col3 and col4 in the output unload file.

Please let me know if any addional statement need to be used if we use CASE statement while unloading data from table.

dbzTHEdinosauer · Posted: Mon Mar 28, 2011 7:56 pm

how about case col4 when '*' then 'STAR' otherwise col4 end

is col4 char or varchar?
what is the length of col4? 15 perchance?

don.leahy · Posted: Mon Mar 28, 2011 8:01 pm

DSNTIAUL treats a literal as a VARCHAR. The extra byte you are seeing represents the length of the literal value. (Though the value you cite, x'000F' does not make any sense for the given example).

One way around this is to use the CHAR function. CHAR('STAR',4) should do the trick.

GuyC · Posted: Mon Mar 28, 2011 8:12 pm

without "else " in the case the output field will be nullable and thus an extra null-indicator will be added to the output file

Elixir · Posted: Tue Mar 29, 2011 1:56 pm

dbzTHEdinosauer,

All the Columns are Char.
Col4 is CHAR(1).

Between col3 and output of Case I get the below,
43,44
00
0F

The maximum length of Case output is 15.

Elixir · Posted: Tue Mar 29, 2011 3:30 pm

Don,

I tried using your suggestion. There is non prepaded values now. Thanks.

But I am confused with the with prepaded X'000F' in case I do not use CHAR in the SYSIN.