DB2 and usernames with spaces

17:23 on Wed 2009-02-04 by Leons Petrazickis DB2

DB2 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:

db2sampl -force -name SAMPLE
 

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:

SET SCHEMA=db2admin;
 

And, of course, you can always explicitly specify a schema in your queries:

SELECT * FROM db2admin.tablename;