View previous topic :: View next topic
|
Author |
Message |
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
Hi,
When I unload the data and browse(view) the dataset in BROWSE mode I see non displayable characters. The columns are defined as VARCHAR however I see nothing in the QMF. This is happening only when I unload the data into dataset.
Do anyone have any idea why is this so..?. In my case
Code: |
SELECT S.KNID,
S.NVFO, S.NVEF,
S.ADLI01, S.ADLI02,
S.ADLI03, S.ADLI04,
S.ADLI05, S.ADLI06,
S.ADLI07, K.PDNR
FROM EB.TKUNDESTAM S,EB.TKONTOSTAM K
WHERE S.KNID = K.KNID
AND S.RSLD = 'DK'
AND K.PDNR IN
(SELECT PRODUKT_NR FROM EB.PRODUKT_RELATION_H
WHERE SCORETABEL_ID IN ('L','M','T')) WITH UR |
The columns S.NVFO thru S.ADLI07 are defined as VARCHAR in DB2. In few columns I see this characters after the data. If there is no data in the columns then it is full of junk characters.
Please help me in getting spaces if there is no data and only data if it contains some data. I will be sorting this data set later to eliminate duplicates and order the data.
Please see the attachment. Thanks..! |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Varchar columns in the unload have a two byte binary LENGTH field before the data. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
Order the data on extract with ORDER BY and write your WHERE clause so you don't have to post process the data. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
Well, Join is not on unique columns and cant use DISTINCT to remove duplicates as there are 12 million rows in each table.
Query is taking long long time.
This not for length, even data looks like ............................. though the HEX value of the two byte length says it is zero. I dont understand how to remove this. This happens everytime I unload.
I'm afraid to put ORDER by again becuase of performance. Explain results says one of the table is going for tablespace scan with Nested loop scan with the other one. The columns included in this join are part of index keys and not complete.
Requirement is such a way....Adding index doesn't make sense to me as this is just one time process. One time data extract..!
Please let me know your comments. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
If its a one time extract, performance should not be any concern. Put the ORDER BY on there.
If the hex length is 0, there is no data in that field for that row. |
|
Back to top |
|
|
|