DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

phantom column


Hello there,
Could anyone help me with the following question?
A table column named TEXT (data type:character, length:200, null:yes) gives me some trouble.
- if I select the column from the table the result is blank
- if I select trim(length(text)) the result is 73
- if I do the following select :
select text || 'asd' from table
=> the result is blank again (and not 73 spaces followed by 'asd' as I expected)
- the value in the column is not NULL ( tested using "is null")
Could anyone tell me what does this column contain?
I have no idea.
What is the binary or hex value in the column ? i.e. select hex(text) from ....
Does the column allow NULL ? are all the values NULL ? select coalesce(text,'blank') from ...
Also, what is the database codepage, and your application-codepage, and are you using a GUI or a command-line to view the result of the select ?
Always give you db2 server version + fixpack, and operating system name + version.
Hi,
- hex value: all space 202020...
- the column allows null
- the select coalesce... returns the same result as the simple select
- database codepage: 819
- db2 version : v9.5.0.10
- o.s.: Windows xp but i use a unix server
So your datatype for TEXT might be CHAR(73) and that is the expected behaviour.
If the datatype was VARCHAR(73) you may get a different result, as regards the length()
TEXT is CHAR(200)
- if I select length(text) the result is 200
Maybe this could help:
If i compare the row with the strange value in TEXT with another row in the table (with i am sure is empty):
- select length(text) from table => result is 200 for both rows
- select length(trim(text)) from table => result is 73 for the strange row and 0 for the empty row
- select hex(text) from table => result is the same for both rows (2020202020...)
- select coalesce(text, 'blank') from table => result is the same for both rows (blank)
What is in that column that has length 73?
If I select concat(text, 'string') from table => for the empty row the result is 200 spaces + 'string'(what i expect) but for the strange row the result is blank
I don't think there is NULL value in the row beacouse if I select text from table where text is null the row is not returned.
Thanks.

Related Links

Error Building DB2 SQL Procedure
DB2 Command Centre
One or more values in the INSERT statement, UPDATE statement, or foreign key update c
The cursor specified in a FETCH or CLOSE statement is not open
test
db2 security
online incremental backups in DB2
DB2 stored procedures filesystem full errors
COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E Function sequence error. SQ
problems with DB2 Administration server
st
Adding space to SMS tablespaces - alternatives?
db2inidb through API
How to obtain a lock on the table
Activate not logged initially issue
Page cleaners Tuning

Categories

DataBase Dev
MongoDB
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
Crystal Reports
Delphi, C etc
JAVA
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
Brilliant Database
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
New Members & Introduc...
Applications & Tools
Database Concepts & De...
Chit Chat
Marketplace
Job Opportunities
Suggestions & Feedback

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App