DB2 and usernames with spaces

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;