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;