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: United Kingdom

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: 1567
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: United Kingdom

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: 2098
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: 1790
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: United Kingdom

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: 1790
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 Facing issue while executing multi ro... aagarwal88 DB2 6 Tue Oct 02, 2018 8:11 am
No new posts DB2 SQL Query to fetch all instances ... MallikarjunSM DB2 2 Thu Sep 27, 2018 6:46 pm
No new posts Generate SQL query dynamically using ... vnktrrd DB2 7 Tue Aug 28, 2018 8:11 pm
No new posts CLOB Column data to External File usi... lalitmehta12 DB2 9 Tue Aug 21, 2018 7:22 pm
No new posts Query for fetching matching data in t... Poha Eater DB2 10 Mon Jul 09, 2018 6:06 pm

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