| View previous topic :: View next topic |
| Author |
Message |
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
 Posted: Thu Aug 10, 2006 3:13 pm Post subject: DSNTIAUL and formatting of output |
 |
|
Folks, a quick question. When I run an unload of a table using DSNTIAUL with the SQL parameter each coulmn unloaded has two .. at the end of the data. I want to load the file created into an excel spreadsheet and these two .. results in strange characters. Is there a way to prevent these from appearing and what do they actually represent.
ex
my sql would look something like
| Code: |
SELECT CHAR(' '),
'|',
per.title,
'|',
CHAR(PER.SOCSECNO)
from mytable
|
and the output is
| Code: |
..|..MR..|..111111111..
|
where the .. is 00 when veiwed as hex
01
thanks
Tim |
|
| Back to top |
|
 |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Thu Aug 10, 2006 4:46 pm Post subject: |
|
|
timfoster,
Both these will work
| Code: |
SELECT RTRIM(FNAME) || '|' ||
SUBSTR(LNAME,1,14) AS NAME
FROM HOURS.HRSDTL
WHERE WRKPERIOD = '2004-05-01' AND
EMPRNO = 1100 AND
SSNO = 123456789;
|
| Code: |
NAME
KEITH|SMITH JR
|
or
| Code: |
SELECT FNAME || '|' ||
LNAME
FROM HOURS.HRSDTL
WHERE WRKPERIOD = '2004-05-01' AND
EMPRNO = 1100 AND
SSNO = 123456789;
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Thu Aug 10, 2006 5:14 pm Post subject: |
|
|
Tim,
The example you have shown '..' both at the beginning as well as the end.
The other option for you to unload and replace the '..' with a 'comma' and make it as a CSV.
NASCAR9,
Why for the SSN field is he getting '..'. It should be defined as X(9) and should always have the 9 digits. Why is he getting the '..'s? It's a standard utility and haven't seen that happen before.
________
easy vape review
Last edited by coolman on Sat Feb 05, 2011 1:47 am; edited 1 time in total |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 10, 2006 5:19 pm Post subject: |
|
|
timfoster,
You need to use the CHAR function on every character string you use in the select statement as the unload utility treates the constants as Varchar fields and add the extra 2 bytes before each field.
try this
| Code: |
SELECT CHAR(' ')
,CHAR('|')
,per.title
,CHAR('|')
,CHAR(PER.SOCSECNO)
from mytable
|
Hope this helps....
Cheers
Kolusu
Nascar9,
The output of RTRIM is also a varchar , so it will still add the extra 2 bytes which consists of the length of the field. So your solution will not work in this case
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Thu Aug 10, 2006 5:32 pm Post subject: |
|
|
| kolusu wrote: | timfoster,
Nascar9,
The output of RTRIM is also a varchar , so it will still add the extra 2 bytes which consists of the length of the field. So your solution will not work in this case |
Here is what is in my SPUFI data set
| Code: |
KEITH|SMITH JR
DCCEC4EDCEC4DD4444444444444444444444444444444444444444444444444444444444
25938F249380190000000000000000000000000000000000000000000000000000000000
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Thu Aug 10, 2006 5:36 pm Post subject: |
|
|
| NASCAR9 wrote: | Here is what is in my SPUFI data set
|
Nascar9,
Spufi formats the data for you. Try running the sql using DSNTIAUL in batch mode and see the output
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
| Posted: Fri Aug 11, 2006 8:03 am Post subject: |
|
|
folks,
thanks for all the responses. I have been able to get rid of the two bytes in question. Just for my own understanding. Kolusu - you mention
the constants are treated as Varchar fields and add the extra 2 bytes. Does the two bytes represent field length.
I still have one issue. I am getting two bytes at the very beginning of the output
| Code: |
SELECT CHAR(' ') ||
CHAR('|') ||
CHAR(' ') ||
|
any idea why the first two bytes are there - My assumption is that it is actaully because of the dataset format and not something from the unload itself
dataset definition
Code:
| Code: |
//SYSREC00 DD DSN=TFOSTER.PUBLIC.UNLOAD.IOASSET.PROP,
// UNIT=DISK,SPACE=(CYL,(10,10),RLSE),
// DISP=(,CATLG,CATLG)
|
thanks again for the help |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12394
Topics: 75
Location: San Jose
|
| Posted: Fri Aug 11, 2006 11:37 am Post subject: |
|
|
| Quote: |
the constants are treated as Varchar fields and add the extra 2 bytes. Does the two bytes represent field length.
|
Yes the 2 bytes reprenst the length.
| Quote: |
any idea why the first two bytes are there - My assumption is that it is actaully because of the dataset format and not something from the unload itself
|
The output you show does not match the SELECT statement. If your db2 table has a varchar column then when you unload it , it will 2 extra bytes which will have the actual length of the varchar column. If you don't want the extra 2 bytes then you need to use the CHAR function
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
timfoster
Beginner
Joined: 08 Jul 2004
Posts: 12
Topics: 5
|
| Posted: Mon Aug 14, 2006 8:25 am Post subject: |
|
|
folks,
thanks for all the help. The download is working as I need it to now. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
