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
 

 

DB2 Unload using Utility

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Mon Mar 28, 2011 7:36 pm    Post subject: DB2 Unload using Utility
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Mar 28, 2011 7:56 pm    Post subject:
Reply with quote

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

is col4 char or varchar?
what is the length of col4? 15 perchance?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 647
Location: Whitby, ON, Canada

PostPosted: Mon Mar 28, 2011 8:01 pm    Post subject:
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Mar 28, 2011 8:12 pm    Post subject:
Reply with quote

without "else " in the case the output field will be nullable and thus an extra null-indicator will be added to the output file
Back to top
View user's profile Send private message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Tue Mar 29, 2011 1:56 pm    Post subject: DB2 Unload using Utility
Reply with quote

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.
Back to top
View user's profile Send private message
Elixir

Active User


Joined: 08 Feb 2009
Posts: 116
Location: CHENNAI/NEW JERSEY - INDIA/USA

PostPosted: Tue Mar 29, 2011 3:30 pm    Post subject:
Reply with quote

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.
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts TAPE VERIFY utility?? MrExtraordinare JCL & VSAM 3 Fri Dec 09, 2016 10:57 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts Any docs / links for isub- cobol comp... SRICOBSAS COBOL Programming 4 Fri Aug 26, 2016 2:35 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm


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