MVSFORUMS.com

aquarian

Hi,
I have a situation like below

Table

X1 DECIMAL (31,2) WITH DEFAULT NULL
X2 DECIMAL(31,2) NOT NULL WITH DEFAULT

We have a unload job which reads this table and gives a flat file , unfortunately i have little control on it , but can suggest the other programmer if i have a solution , thats the main intention of posting

in the unload job at the end he has declared as

SELECT
CHAR(X1)
CHAR(X2)
FROM <table>

because of this in the flat file i am seeing some null( X'00)/space(X'40)and some weird (X'65') characters , also the length as X(33)- Alphanumeric

how can we modify the stmt to have the output directly as S9(16)V9(2)
_________________
cheers,
Aquarian

kolusu · Posted: Thu Aug 28, 2014 11:06 am Post subject:

aquarian,

I am confused here. A DB2 column defined as DECIMAL has the data stored as packed decimal field. If you use a CHAR function on that column it puts the data into readable format and the size of the output is doubled and some additional bytes are needed to account for the sign and the decimal point.

You can control the Null characters using IFNULL/VALUE/COALESCE and you can control the length of CHAR column too.

aquarian · Posted: Mon Sep 01, 2014 9:25 am Post subject:

Kolusu,
thanks a lot for the reply

I did suggested IFNULL and COALESCE from below link

but the output is not as i expected, My question is is it possible to have the output directly as S9(16)V9(6) or do i need to play with functions like NUMVAL/STRING/INSPECT or some redefines to get the proper decimals
_________________
cheers,
Aquarian

aquarian · Posted: Tue Sep 02, 2014 2:33 am Post subject: Alphanumeric to Display (PACKED)

It is finalized now, They are giving me data in a file and i need to fetch it, analyze it and display on report

1. Table declaration

X1 DECIMAL (31,2) WITH DEFAULT NULL

2. In the flat file i have received as (this is what i have declared in copybook)

X1-FILE PIC X(33) , and i could see the field value as below

' 000000000000000000000000000123.33'

3. I want this field to be moved to a report variable

HEADER-X1 PIC Z.ZZZ.ZZZ.ZZZ.ZZZ.ZZ9,99

Do i need to change the file declaration as well to directly pick the Packed decimals as PIC S9(15)V9(2) COMP-3 ? in that case will it pick the decimal point and the digits exactly ??

Can someone please guide me in this regard?
_________________
cheers,
Aquarian

Nic Clouston · Posted: Tue Sep 02, 2014 8:42 am Post subject:

And the help on another forum is not good enough?
_________________
Utility and Program control cards are NOT, repeat NOT, JCL.

aquarian · Posted: Tue Sep 02, 2014 8:59 am Post subject:

No it didnt but actually it gave me an idea !! Thanks for that

I understood sometimes we need to think simple rather than using functions/or any other fancy stuff

Splitting the variables did the trick !
_________________
cheers,
Aquarian

kolusu · Posted: Tue Sep 02, 2014 10:51 am Post subject:

aquarian · Posted: Wed Sep 03, 2014 4:37 am Post subject:

Kolusu
1. I just tried to post across forums assuming to get the solution, will try to keep in mind this in future

2. I am not loading data into Db2, but at receiving end , getting data from a db2 table, however after posting the forum and while trying myself i figured out the way, let me know if you want me delete the threads to avoid confusion, sorry if i have caused any inconvenience
_________________
cheers,
Aquarian