| View previous topic :: View next topic |
| Author |
Message |
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
 Posted: Tue Jun 06, 2006 6:16 pm Post subject: VARCHAR Length moving target |
 |
|
I'm having trouble getting the length for a VARCHAR column. The results are not consistent. I ran the following queries in SPUFI with the result below.
| Code: |
SELECT
LENGTH(BILL_TYPE_VALUE)
FROM FDBMD.BILL_TYPE_VALUES
WHERE BILL_TYPE_VALUE = '111';
---------+---------+---------+---------+---------+---------
10
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
SELECT
LENGTH(BILL_TYPE_VALUE), BILL_TYPE_VALUE
FROM FDBMD.BILL_TYPE_VALUES
WHERE BILL_TYPE_VALUE = '111';
BILL_TYPE_VALUE
---------+---------+---------+---------+---------+---------
10 111
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
SELECT
LENGTH(BILL_TYPE_VALUE), LENGTH(FULL_DESC)
FROM FDBMD.BILL_TYPE_VALUES
WHERE BILL_TYPE_VALUE = '111';
---------+---------+---------+---------+---------+---------
3 8
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
|
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Tue Jun 06, 2006 9:25 pm Post subject: |
|
|
NASCAR9,
Are sure that no one has modified the results in between? Did you try running the same query once again for a different where clause condition?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jun 07, 2006 9:52 am Post subject: |
|
|
| kolusu wrote: | NASCAR9,
Are sure that no one has modified the results in between? Did you try running the same query once again for a different where clause condition?
Kolusu |
Kolusu, this is a new table (loaded yesterday). I ran the query on the complete table and the results were the same. I added a WHERE condition to see it the results would change, they didn't. Maybe a bug?
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 07, 2006 10:31 am Post subject: |
|
|
NASCAR9,
I wouldn't term it a bug, but did you check the load file if they have same values for these columns?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jun 07, 2006 10:50 am Post subject: |
|
|
| kolusu wrote: | NASCAR9,
I wouldn't term it a bug, but did you check the load file if they have same values for these columns?
Kolusu |
The load was from a client application. The values for the columns are correct. I also have another table giving the same kind of results. The common denominator is the first columns of both tables are VARCHAR.
BTW, the rest of the VARCHAR colums show the correct LENGTH, just the first column is the 'moving target'.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 07, 2006 10:59 am Post subject: |
|
|
NASCAR9,
Try this.
Delete all the rows from the table and load it with your own data and re-run the spufi and check the results. Alternatively insert a row via spufi and check the results. If you still get mismatching results, then may be you need to contact IBM.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Wed Jun 07, 2006 11:08 am Post subject: |
|
|
| kolusu wrote: | NASCAR9,
Try this.
Delete all the rows from the table and load it with your own data and re-run the spufi and check the results. Alternatively insert a row via spufi and check the results. If you still get mismatching results, then may be you need to contact IBM.
Kolusu |
Before I do that, tell me what you think about this. Both tables have the PRIMARY Key defined as the first column + a DATE column. Could it be the Select is just going to the Index and not the data for the length when the only column is in the index? Then when another Non-indexed column is included in the Select, it goes to the data and retrieves the length there?
My thinking is The Index needs to maintain the maximum length for uniqueness, where as the data is just data.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 07, 2006 11:36 am Post subject: |
|
|
| Quote: |
Could it be the Select is just going to the Index and not the data for the length when the only column is in the index?
|
Nascar9,
I don't think LENGTH scalar function has any thing to do with the Index. It just returns the length of the string.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Thu Jun 08, 2006 8:09 pm Post subject: |
|
|
Here's an update. The default in our shop for indexes is PADDED. This is the reason the length field came back 10, the select was index only. I have since added NOT padded clause to the Index, now the query comes back with 3 always. This information was found in the SQL Reference under CREATE INDEX.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jun 09, 2006 8:42 am Post subject: |
|
|
NASCAR9,
I am confused .Even before you changed the index your last query in the first post showed the length to be 3 . How did that happen?
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 09, 2006 9:34 am Post subject: |
|
|
| kolusu wrote: | NASCAR9,
I am confused .Even before you changed the index your last query in the first post showed the length to be 3 . How did that happen?
Kolusu |
You're correct! I worked with our contract DBA last night on this opportunity. We used EXPLAIN (Thanks Kolusu) to see the path taken.
The first two queries never went to the data itself. They used index only. The last query went to the data since FULL_DESC was not part of the INDEX.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
NASCAR9
Intermediate
Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California
|
| Posted: Fri Jun 09, 2006 9:48 am Post subject: |
|
|
More information.
NOT PADDED or PADDED Specifies how varying-length string columns are to be stored in the index. If the index contains no varying-length columns, this option is ignored, and a warning message is returned. Indexes that do not have varying-length string columns are always created as physically padded indexes. NOT PADDED Specifies that varying-length string columns are not to be padded to their maximum length in the index. The length information for a varying-length column is stored with the key. NOT PADDED is ignored and has no effect if the index is being created on an auxiliary table. Indexes on auxiliary tables are always padded.
Chapter 5. Statements 721
PADDED Specifies that varying-length string columns within the index are always padded with the default pad character to their maximum length. When the index contains at least one varying-length column, the default for the option depends on the value of field PAD INDEXES BY DEFAULT on installation panel DSNTIPE: v When the value of this field is NO, which is the default for sites that newly installed Version 8, new indexes are not padded unless PADDED is specified. v When the value of this field is YES, which is the default value for sites that migrated to Version 8, new indexes are padded unless NOT PADDED is specifed.
_________________
Thanks,
NASCAR9 |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Fri Jun 09, 2006 9:54 am Post subject: |
|
|
| NASCAR9 wrote: | | kolusu wrote: | NASCAR9,
I am confused .Even before you changed the index your last query in the first post showed the length to be 3 . How did that happen?
Kolusu |
You're correct! I worked with our contract DBA last night on this opportunity. We used EXPLAIN (Thanks Kolusu) to see the path taken.
The first two queries never went to the data itself. They used index only. The last query went to the data since FULL_DESC was not part of the INDEX. |
Thanks for the explanation Nascar. That explains everything and I learnt something new.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
