DB2 Basics: Quotation Marks

This blog entry may be a little on the basic side, but some of my most basic entries are some of my most popular ones.

DB2 has a number of special characters, for which there are rules of use. Both the single quotation mark and the double quotation marks are special characters. The DB2 Knowledge Center lists what DB2 considers special characters.

Using the Single Quote

For DB2, the single quotation mark or apostrophe – ' – delimits a string. Note that is a straight quote and not the curly kind that some programs like MS Word may use. Most frequently, I use the single quote in ways like these:

select * from schema.table where column1='some_string'
select tabschema || '.' || tabname from syscat.tables

But apostrophes or single quotes can also be a part of the data we work with. In order to escape an apostrophe, you simply use another apostrophe. Thus if you want to update a value with an apostrophe, you would use something like this:

update table set lastname = 'O''Hara' where id=1234

Using the Double Quote – Linux and Windows

The primary purpose of using double quotes is to prevent the operating system from interpreting whatever is between the double quotes. I generally wrap any db2 statement that I issue at the Linux/UNIX command line in double quotes.

This will work:

$ db2 "select count(*) from syscat.tables where type='T' with ur"

1
-----------
       1909

  1 record(s) selected.

While this will fail:

$ db2 select count(*) from syscat.tables where type='T' with ur
-bash: syntax error near unexpected token `('

A similar error from windows looks like this:

C:\Program Files (x86)\IBM\sqllib\bnd> db2 bind @db2ubind.lst blocking all sqlerror continue messages bind.msg grant 
public ACTION ADD 
At line:1 char:10 
+ db2 bind @db2ubind.lst blocking all sqlerror continue messages bind.msg grant pu ... 
+          ~~~~~~~~~ 
The splatting operator '@' cannot be used to reference variables in an expression. '@db2ubind' can be used only as an 
argument to a command. To reference variables in an expression use '$db2ubind'. 
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException 
    + FullyQualifiedErrorId : SplattingNotPermitted 

Curly Quotation Marks vs. Straight Quotation Marks

Some programs, such as MS Word, convert regular quotation marks to fancier looking curly quotation marks as you type. This can be annoying if you or a developer have text in such a program or use such a program in an intermediate step or to document a process. There are several ways these can come across. Sometimes, particularly if you’re not using unicode in your terminal, the single or double quotation marks are converted to periods. Other times, it looks like this:

$ db2 "select * from emspot where name = 'Lens_Recommendation_Modal_Espot’  with ur"
SQL0010N  The string constant beginning with
"'Lens_Recommendation_Modal_Espot’  with ur" does not have an ending string

Once the curly quote at the end of that string is corrected, the same statement runs just fine:

$ db2 "select * from emspot where name = 'Lens_Recommendation_Modal_Espot'  with ur"
DB20000I  The SQL command completed successfully.

If you need to escape a double quote, you double it just like with the single quotes. However, remember that your OS or shell may interpret double quotes, and that can lead to some unexpected behavior.

Did anyone doubt I could write 500 words on how to use a quotation mark?