How do you change or remove null character on BMC unload ?



IBM's flagship relational database management system

How do you change or remove null character on BMC unload ?

Postby snwskr » Tue Jun 29, 2010 8:57 pm

Hello,

I have an unload that I created that needs to be emailed , but one of the date fields is nullable causing a problem.
The unload automatically added a position for the null character into position 36.

The problem is the output character is low-values and needs to be spaces.

How do you override that ?

Here is the output diplayed from BMC..

LOAD DATA INDDN SYSREC
EBCDIC CCSID(37,65534,65534)
INTO TABLE
ZA$$PGS.$TABLE1
(FILL1 POSITION(1:5) CHAR (5)
,MRKR POSITION(6:11) CHAR (6)
,FILL2 POSITION(12:16) CHAR (5)
,ID POSITION(17:20) DECIMAL ZONED
,FILL3 POSITION(21:25) CHAR (5)
,END_DT POSITION(26:35) DATE EXTERNAL(10)
NULLIF BMC_NULL1=X'6F'
, BMC_NULL1 POSITION(36) CHAR(1)
,FILL5 POSITION(37:45) CHAR (9)
,CNT POSITION(46:54) DECIMAL ZONED
,UNUSE POSITION(55:80) CHAR (26)
)


Here is the Unload..
UNLOAD
FORMAT STANDARD
UNLOADDN SYSREC
SHRLEVEL CHANGE
DIRECT AUTO
ZONEDDECOVP(F,F)
FILL YES
SELECT ' '
,C.MRKR_GRP_ID
,' '
,C.MRKR_ID
,' '
,D.MRKR_END_DT
,' '
,COUNT(*)
,' '
INTO
FILL1 CHARACTER (05)
,MRKR CHARACTER (06)
,FILL2 CHARACTER (05)
,ID DECIMAL ZONED (04,0)
,FILL3 CHARACTER (05)
,END_DT
,FILL5 CHARACTER (09)
,CNT DECIMAL ZONED (09,0)
,UNUSE CHAR (26)
FROM DB2ADM.AMKVW003_ACCT_MRKR C
,DB2ADM.AMKVW002_MRKR_ID D
WHERE C.MRKR_GRP_ID = D.MRKR_GRP_ID AND
C.MRKR_ID = D.MRKR_ID AND
C.MRKR_GRP_ID IN
(SELECT DISTINCT A.MRKR_GRP_ID
FROM DB2ADM.AMKVW002_MRKR_ID A
,DB2ADM.AMKVW001_MRKR_GRP B
WHERE
A.MRKR_GRP_ID = B.MRKR_GRP_ID AND
B.MRKR_GRP_OPRTNL_FL = 'Y' AND
B.MRKR_GRP_WEB_FL = 'Y' AND
(A.MRKR_END_DT BETWEEN (CURRENT_DATE) AND
(CURRENT_DATE + 7 DAYS) )
)
AND ACCT_MRKR_ACTV_FL = 'Y'
GROUP BY C.MRKR_GRP_ID, C.MRKR_ID, D.MRKR_END_DT
;

Please help,

Thanks,
Paul
snwskr
 
Posts: 2
Joined: Tue Jun 29, 2010 8:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How do you change or remove null character on BMC unload ?

Postby GuyC » Wed Jun 30, 2010 1:25 pm

try coalesce(end_dt,' ')
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: How do you change or remove null character on BMC unload ?

Postby snwskr » Wed Jun 30, 2010 8:04 pm

I don't understand.

Where do I put this command ?

I tried it in the 'select' area and 'into' area and getting an error.
What is the syntax ?
snwskr
 
Posts: 2
Joined: Tue Jun 29, 2010 8:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How do you change or remove null character on BMC unload ?

Postby dick scherrer » Wed Jun 30, 2010 11:10 pm

Hello,

Post what you tried and the error this caused.

COALESCE is part of the SELECT. Use what GuyC posted instead of only the column name.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post