Hey guys...


Just wondering about the reserved word PUBLIC used in DB2. The length
of that word is 8 and it screwing with some formatting work that I'm
doing. Does anyone know why? Is it because PUBLIC is a reserved word
and DB2 adds a newline to the end of them?

Any way to remove the newline? R/LTRIM doesn't work and I can't use a
SUBSTR because of other ids inside, say, the TABAUTH table.

On 08.03.11 19:05 , cbielins (AT) gmail (DOT) com wrote:
I'm sorry, I have no clue what you are talking about. Can you be a little bit
more specific? What do you mean by length(PUBLIC) = 8?

$ db2 "values length(PUBLIC)"
SQL0206N "PUBLIC" is not valid in the context where it is used.
SQLSTATE=42703
$ db2 "values length('PUBLIC')"

1
-----------
6

1 record(s) selected.


--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On Mar 8, 4:36*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
That's interesting Helmut! If you do a "select grantee,
length(grantee) from syscat.tabauth" you'll find that the length of
PUBLIC is 8, not 6. I thought that it may be due to it being a
reserved word.

Plus, when I feed a query into a bash variable (knowing it is PUBLIC)
and evaluate it, the data shows that there is a newline at the end of
the word, which, again, lead me to believe that DB2 designates their
reserved words with a newline.

Here is what I used:

db2 "select grantee, length(grantee) as length from syscat.tabauth
fetch first 5 rows only"

GRANTEE LENGTH
--------------------------------- -----------
PUBLIC 8
PUBLIC 8
PUBLIC 8
PUBLIC 8
PUBLIC 8

bash> var=`db2 -x "select grantee from syscat.tabauth fetch first 1
rows only"`
bash> echo $var
PUBLIC
bash> echo $var | wc -m
7 (this was also wierd)
bash> echo $var | xxd
0000000: 5055 424c 4943 0a PUBLIC.

The last '0a' translates to a newline. So I guess, I'm wondering I
can remove that newline because it is pushing some XML formatting over
too far.

Ok, now I understand what you mean.

The length for grantee in syscat.tabauth and sysibm.systabauth is always 8
characters minimum. You can try to grant access on a table to a user with 4
characters. You will see that the length is also 8. A detailed explanation
would probably take too long and would not really help you either.
But I try to make it short. Internally DB2 is padding the grantee with spaces
up to 8 characters. So the value which is in the catalog table is actually:
'PUBLIC '
or for a user called xyz:
'XYZ '
Therefore the length is 8.
You can get the correct length for grantee with:

db2 "select grantee, length(rtrim(grantee)) as length from syscat.tabauth
fetch first 5 rows only"

No, the newline is added by the echo command.

See what happens, if you do the following:

echo 'Test' |xxd

echo -n "Test' |xxd

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

The reason for this is that SCHEMA as well as USER used to be a CHAR(8)
rather than the VARCHAR it is today.
So to keep backwarsd compatibility the blank padding to 8 is performed.
Why did you say RTRIM doesn't work for you?

Cheers
Serge

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau