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: 1544
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: 1932
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: 1783
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: 1783
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 query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 4 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Query on IEFBR14 with GDG Ashishpanpaliya JCL & VSAM 4 Tue Nov 07, 2017 8:34 pm
No new posts Query on XMITIP abdulrafi All Other Mainframe Topics 1 Wed Oct 25, 2017 6:54 pm

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