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
 
Unload : Resulting in wrong layout

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
murali922

New User


Joined: 25 Jul 2005
Posts: 92
Location: India

PostPosted: Thu Aug 02, 2007 9:08 pm    Post subject: Unload : Resulting in wrong layout
Reply with quote

Code:
SELECT 'H' AS RECORD_TYPE                                     
     , CHAR(CURRENT DATE, ISO) || ' ' AS CARD_ACCT_PLSTC_NO   
FROM SYSIBM.SYSDUMMY1     


When I use the same query in SPUFI, it comes up fine. But when I put the record into a FILE, it comes up wrong.

In SPUFI the output looks like this :
---------+---------+---------+---------+---------+-
RECORD_TYPE CARD_ACCT_PLSTC_NO
---------+---------+---------+---------+---------+-
H 2007-08-02

When I unload that into a file, it results in something like this :-
..H..2007-08-02

I expect the output to be like this :
H2007-08-02

Thanks in advance for the help. icon_biggrin.gif
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 9:29 pm    Post subject: Re: Unload : Resulting in wrong layout
Reply with quote

Code:
SELECT 'H' ||
       CHAR(CURRENT DATE, ISO) || ' ' AS RECORD_TYPE_CARD_ACCT_PLSTC_NO   
FROM SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Thu Aug 02, 2007 9:46 pm    Post subject: Re: Unload : Resulting in wrong layout
Reply with quote

Interesting.

I tried dbz's code and I still get hex values (X'000C') in columns 1 & 2, although the remainder looks good.

I wonder why that is. icon_confused.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 10:32 pm    Post subject:
Reply with quote

tis interesting that (X'000C') = 12 base 10, which is the length of the data.
probably unloading to variable length file, or you are not turning off the VLI generator during the output.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 10:53 pm    Post subject:
Reply with quote

actually 0c is 13, which is the length of the generated output.

just kidding - it really is 12. . .
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Aug 02, 2007 10:57 pm    Post subject:
Reply with quote

When you use a literal like 'H' it is treated as a varchar field, if you cast it as a char(1) field then you won't get the 2 byte length field in front of it.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 11:06 pm    Post subject:
Reply with quote

thanks Craq,

we know why the vli, which is = 12. (despite my previous post!)

1 for H
10 for the date
1 for space after the date.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 03, 2007 12:14 am    Post subject:
Reply with quote

Code:

SELECT CAST('H' ||CHAR(CURRENT DATE, ISO) || ' '  AS CHAR(12))
FROM SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
socker_dad

Active User


Joined: 05 Dec 2006
Posts: 147
Location: Columbia, MO

PostPosted: Fri Aug 03, 2007 5:15 am    Post subject: Re: Unload : Resulting in wrong layout
Reply with quote

Sweet!
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 03, 2007 5:36 am    Post subject:
Reply with quote

thx to whomever edited my post, and i am not kidding. sehr peinlich.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Aug 03, 2007 6:36 am    Post subject:
Reply with quote

My understanding was the unload utility unloads it to match the DCLGEN copybook for the table for use in COBOL. It preservers the comp-3 etc.
Back to top
View user's profile Send private message
murali922

New User


Joined: 25 Jul 2005
Posts: 92
Location: India

PostPosted: Fri Aug 03, 2007 10:10 am    Post subject: Re: Unload : Resulting in wrong layout
Reply with quote

My original SQL looks like this :-

Code:
SELECT 'H' AS RECORD_TYPE
     , CHAR(CURRENT DATE, ISO) || ' ' AS CARD_ACCT_PLSTC_NO
  FROM SYSIBM.SYSDUMMY1

 UNION ALL

SELECT CASE WHEN AMOUNT_NO = '00'
            THEN 'B'
            ELSE 'S'
        END AS RECORD_TYPE
     , CHAR(CURRENT DATE, ISO) || ' ' AS CARD_ACCT_PLSTC_NO
   FROM SYSIBM.SYSDUMMY1B

 UNION ALL

SELECT 'T' AS RECORD_TYPE
     , ' ' AS CARD_ACCT_PLSTC_NO
  FROM SYSIBM.SYSDUMMY1
;


I would need to use the same layout in all of the 3 sections, as I use a UNION ALL. In that case, I would not be able to use the solution you all have suggested, as if I use yours I am not having the variable RECORD_TYPE at all.

Is there some other way I can remove the .. in front of all the variables.

Thanks
Back to top
View user's profile Send private message
murali922

New User


Joined: 25 Jul 2005
Posts: 92
Location: India

PostPosted: Fri Aug 03, 2007 1:32 pm    Post subject:
Reply with quote

SELECT CASE WHEN STU_NAME = '00'
THEN SCHOOL_NAME
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;

SCHOOL NAME is defined in the table as CHAR(35).

The first record has
ALPHA MATRICULATION SCHOOL OF EDUCA
INDIAN SCHOOL OF BUSSINESS

The first record comes out fine. But the next record comes up with 2 blank spaces in the starting.
Since the value in the field would always not be CHAR(35), and sometimes might be less than 35 bytes,
I think there needs to be a solution to remove the spaces first.
I have used CAST, and it doesnt work. I have also tried modifying the SQL to be like this :

SELECT CASE WHEN STU_NAME = '00'
THEN SCHOOL_NAME
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;

SELECT CASE WHEN STU_NAME = '00'
THEN SUBSTR(CAST(COMPANY_NM AS CHAR(35)),1,35)
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;

SELECT CASE WHEN STU_NAME = '00'
THEN SUBSTR(CAST(COMPANY_NM AS CHAR(35)),1,
LENGTH(RTRIM(COMPANY_NM)))
ELSE ' '
END AS SCHOOL_NAME FROM TABLE_A;

Nothing works. Let me know if you have any other way I can solve this problem.
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 Aug 03, 2007 6:10 pm    Post subject:
Reply with quote

Hello,

Why not modify the receiving process to deal with the data the way your process creates it? As long as the data is consistent, it should not be a problem. . .?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 03, 2007 7:06 pm    Post subject:
Reply with quote

you could break it up into different steps. It appears that you are creating a header, detail and trailer records. You could create the header in step 1, the details in step 2 and the trailer in step 3 and then merge the three files. Thus negating the need for a union in your detail record extraction
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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
This topic is locked: you cannot edit posts or make replies. Need help in estimating space of unlo... ashek15 IMS DB/DC 12 Fri Apr 07, 2017 5:11 am
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm

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