IBM Support

Username/password with special characters need to be quoted when using them through CLP within db2prompt ie.db2=>

Technote (FAQ)


Question

SQL0104N is received when connecting to the database through CLP within db2prompt if the username/password contains a special character.

For example:

db2 => CONNECT TO sample USER admin USING !xyz

SQL0104N An unexpected token "!" was found following "<ID>".
Expected tokens may include: "NEW". SQLSTATE=42601

Cause

Special characters in username/password when working through CLP

Answer

It is recommended to quote the usernames/passwords which use any

special characters except from the following character set :

Character set :
-----------------------------------------------------------
'A'-'Z', 'a'-'z' ALPHABETIC CHARACTERS
'#','$','@' DIAMOND "LETTERS"
'0'-'9' NUMERIC CHARACTERS
'_' UNDERSCORE CHAR
'.' PERIOD CHAR
'/' FORWARD SLASH CHAR
'\' BACKWARD SLASH CHAR
':' COLON CHAR
'&','*','%','-','~','+',';','^?','<','=','>','[',']','`','|','^','{','}'
-----------------------------------------------------------

You can quote the username or password with special characters when using them through CLP inside db2prompt as in the examples below:

db2 => CONNECT TO sample USER admin USING '!xyz'

Another solution in a case where ONLY password contains special character/s, would be, to use the password prompt option.

For example:
db2 => CONNECT TO sample USER admin (Hit ENTER without writing password)

it will then prompt for the password, enter the password with special character there, This will not return SQL0104N.

These workarounds are not applicable on Windows environment

Also these workaround won't help outside db2prompt
for example:
db2 connect to sample USER admin USING '!xyz'
SQL0104N An unexpected token "!" was found following "<ID>".
Expected tokens may include: "NEW". SQLSTATE=42601

Document information

More support for: DB2 for Linux, UNIX and Windows
Programming Interface - CLP

Software version: 8.2

Operating system(s): AIX, HP-UX, Linux, Windows

Software edition: Enterprise Server

Reference #: 1303153

Modified date: 12 February 2015