Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
query in unloading a column defined as smallint

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

New User


Joined: 08 Jul 2010
Posts: 12
Location: Coimbatore

PostPosted: Fri Sep 20, 2013 4:53 pm    Post subject: query in unloading a column defined as smallint
Reply with quote

Hi,

I am working on unloading a table where a column is defined as smallint and I would like to have a unloaded value leading zero.

Example:

Column "Division" is defined as smallint and it has got the value as "1" While unloading I would like to have "01" in the unload file. I have tried with CAST but did not help.

Any help on this is much appreciated.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1542
Location: Andromeda Galaxy

PostPosted: Fri Sep 20, 2013 5:13 pm    Post subject:
Reply with quote

Can you try DIGITS?
Back to top
View user's profile Send private message
kmk2mani

New User


Joined: 08 Jul 2010
Posts: 12
Location: Coimbatore

PostPosted: Fri Sep 20, 2013 5:52 pm    Post subject:
Reply with quote

Hi Pandora-Box,

I have just tried DIGITS but did give an error.

SQLCODE = -104, ERROR: ILLEGAL SYMBOL "DIGITS".
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Sep 20, 2013 6:54 pm    Post subject:
Reply with quote

Hello,

What happens if you unload this column preceded by a literal zero (assuming the value always has a length of one).
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1894
Location: UK

PostPosted: Fri Sep 20, 2013 6:56 pm    Post subject:
Reply with quote

How about posting your code to let people see if you coded it correctly? Well, obviously you didn't or it would not have failed but another pair of eyes may spot your mistake.
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Fri Sep 20, 2013 8:50 pm    Post subject:
Reply with quote

Nic Clouston wrote:
How about posting your code to let people see if you coded it correctly? Well, obviously you didn't or it would not have failed but another pair of eyes may spot your mistake.

And as much if not more to the point, what is the TS using to unload the table: IBM utility, BMC utility, DSNTIAD, some RYO program?
Back to top
View user's profile Send private message
kmk2mani

New User


Joined: 08 Jul 2010
Posts: 12
Location: Coimbatore

PostPosted: Mon Sep 23, 2013 12:20 pm    Post subject:
Reply with quote

Dick,

The column will have 2 digits also.

Nic and Akatsukami,

I am using DB2UNLD to unload the table. Please find below the unload query.

UNLOAD TABLESPACE
LOCK NO
QUIESCE NO
OPTIONS NULLID NO NULL OFF
SELECT CAST(CHAR(' ') AS CHAR(01)),
CAST(STORE_NBR AS CHAR(04)),
CAST(BASE_DIV_NBR AS CHAR(02)) ------> Column BASE_DIV_NBR is defined as smallint.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Mon Sep 23, 2013 5:22 pm    Post subject:
Reply with quote

Can you show us how did you used digits function in the unload card?
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


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

PostPosted: Mon Sep 23, 2013 8:48 pm    Post subject:
Reply with quote

kmk2mani wrote:
I am using DB2UNLD to unload the table. Please find below the unload query.

UNLOAD TABLESPACE
LOCK NO
QUIESCE NO
OPTIONS NULLID NO NULL OFF
SELECT CAST(CHAR(' ') AS CHAR(01)),
CAST(STORE_NBR AS CHAR(04)),
CAST(BASE_DIV_NBR AS CHAR(02)) ------> Column BASE_DIV_NBR is defined as smallint.

"DB2UNLD" is probably the name of a proc executing the IBM DB2 utility. Unfortunately, I don't know of a way to get what you want. Specifying the field EXTERNAL (n) FILL YES would give you a sign prior to leading zeroes. You may have to do some post-processing with *Sort.
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us