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
 

 

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: 1529
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: 1713
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: 1738
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: 1738
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


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