IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Unload : Resulting in wrong layout


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 6966
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 9:29 pm
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: 177
Location: Seattle, WA

PostPosted: Thu Aug 02, 2007 9:46 pm
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: 6966
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 10:32 pm
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: 6966
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 10:53 pm
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
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: 6966
Location: porcelain throne

PostPosted: Thu Aug 02, 2007 11:06 pm
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: 6966
Location: porcelain throne

PostPosted: Fri Aug 03, 2007 12:14 am
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: 177
Location: Seattle, WA

PostPosted: Fri Aug 03, 2007 5:15 am
Reply with quote

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

Global Moderator


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

PostPosted: Fri Aug 03, 2007 5:36 am
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
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
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
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

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Aug 03, 2007 6:10 pm
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: 6966
Location: porcelain throne

PostPosted: Fri Aug 03, 2007 7:06 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Multiple table unload using INZUTILB DB2 2
No new posts changing defaults in db2 admin - Unlo... DB2 0
No new posts DB2 Table - Image copy unload IBM Tools 2
Search our Forums:

Back to Top