DB2 and usernames with spaces
February 4th, 2009 by Leons PetrazickisDB2 v9.5 uses the Windows username as the default schema. Unfortunately, it does not support spaces in schema names. Accordingly, usernames - e.g. “Jane Smith” — that have a space can make DB2 unhappy.
I recently helped someone resolve an issue with this command:
Which brought up this error:
Creating DATABASE "SAMPLE"…
Connecting TO DATABASE "SAMPLE"…
Creating TABLES AND DATA IN schema "JANE SMITH"…
–ERROR—-
SQLSTATE = 42601
Native Error Code = -443
[IBM][CLI Driver][DB2/NT64] SQL0443N Routine "*L_SAMPLE" (specific name "") has returned an error SQLSTATE WITH diagnostic text "SQLSTATE 42601: A character, token, or clause is invalid or". SQLSTATE=42601
–
Creating TABLES WITH XML COLUMNS AND XML DATA IN schema "JANE SMITH"…
–ERROR—-
SQLSTATE = 42601
Native Error Code = -443
[IBM][CLI Driver][DB2/NT64] SQL0443N Routine "*L_SAMPLE" (specific name "") has returned an error SQLSTATE WITH diagnostic text "SQLSTATE 42601: A character, token, or clause is invalid or". SQLSTATE=42601
–
‘db2sampl’ processing complete.
The full routine name is CREATE_SQL_SAMPLE. It tries to create tables for Jane Smith, fails, and blazes bravely on. Interestingly, an empty database results.
The workaround is to log in as a user without a space, such as “db2admin”, run db2sampl, and then log back in as yourself.
For running future queries, you can specify a different schema:
And, of course, you can always explicitly specify a schema in your queries:
Posted in db2 |