Escaping Single Quotes in Db2 SQL Statements

Every now and then I write about some things that may seem to be basic to old-timers, but can be very confusing to developers the first time they encounter them. Today's post is an example.

How do you handle a text string with a single quote in it? For example, something like this:

    Today is Craig's birthday (not really)

Well, the first thing to corroborate is that text in Db2 SQL is delimited by single quotes. So the first attempt people tend to make is something like this:

    'Today is Craig's birthday (not really)'

But they also quickly discover that this does not work. There are three single quotes here: one at the beginning, one between the "g" and the "s", and one at the end. So Db2 will choke on it and you will get an error.

The trick is knowing the escape character. An "escape" character is used to tell Db2 that the next character is actually part of the text string, not a delimiter. For Db2, the escape is to double up the single quote (note that other DBMSes may use a different technique). So, if we want to use our sample text phrase in Db2 SQL, we'd code it with the escape character, as follows:

    'Today is Craig''s birthday (not really)'

The single quote denoting the possessive for Craig's is doubled. This tells Db2 to treat the single quote as part of the text. So we could write SQL like this:

    SELECT 'Today is Craig''s birthday (not really)'
    FROM   SYSIBM.SYSDUMMY1;

This will return the text that we want:

    Today is Craig's birthday (not really)

This works even if there are multiple single quotes within the text. For example, consider if we want to use the following text in SQL:

    Today is not Craig's birthday but it is Beth's

We could write the SQL like this:

    SELECT 'Today is not Craig''s birthday but it is Beth''s'
    FROM   SYSIBM.SYSDUMMY1;

And it also works even at the end of the text string, even though it starts to look somewhat confusing. For example, what is we want to use the following text in SQL?

    I like the book 'A Confederacy of Dunces'

In that case, we still double up the single quotes, like so:

    SELECT 'I like ths book ''A Confederacy of Dunces'''
    FROM   SYSIBM.SYSDUMMY1;

And that will return our desired text.

I hope this short treatise on how to use the escape for single quotes in Db2 SQL has been helpful!

Seasons Greetings 2022!

Just a short blog entry today wishing all my regular readers a very happy holiday season! 

And to let everbody know that I will not be posting anything new between now and the end of the year...

But be sure to check back again next year (2023) as I continue to write about Db2, mainframes, and database issues that impact us all!

Db2 Education is a Constant Requirement

Ensuring access to proper Db2 educational materials should be one of the first issues to be addressed after your organization decides to implement Db2. But education sometimes falls through the cracks... 

Does your organization understand what Db2 is? How it works? For what purposes it is needed at your shop? How it will be used? Without a sound understanding of Db2, its components, and features, it is unlikely that you will be able to use Db2 to its best advantage. A basic level of Db2 knowledge can be acquired through a short Db2 fundamentals class for the IT personnel charged with making Db2 a success at your ­organization. But long-term success with Db2 requires ongoing education.

After addressing the basics of Db2 education, you must support a curriculum for on-going Db2 education for your organization. This support falls into four categories: 

The first category of training is a standard regimen of SQL and Db2 programming training to be used by application developers. Every programmer should receive basic training on SQL, education on how to embed SQL in the programming languages they will be using, and possibly additional courses on using Db2 with specific infrastructure software like MQ, WebSphere, CICS, and IMS. Also, with the importance of distributed access to Db2 these days, a course should be made available on that topic, with specific portions that address the technology used by your shop to provide distributed Db2 data access. If this basic level of Db2 education is not required for every Db2 programmer, then Db2 application performance will surely suffer as untrained coders write inefficient and incorrect SQL. This type of training can be delivered as instructor-led training by in-house SQL coding experts, as computer-based training, or as external classes. It should also be augmented with reference material such as books, articles, and blog posts.

The second category of education support is external training for special needs. This support includes education for database administrators, technical support personnel, and performance analysts. Additionally, your organization needs to plan for ongoing education to keep appropriate personnel up-to-date on new versions and releases of DB2. Although IBM offers great courses for new Db2 releases, several third-party vendors such as ProTech, Interskill Learning and KBCE regularly offer in-depth training and release-specific Db2 courses and lectures.

The third category of education is in-house, interactive training in the form of videos, computer-based training, and instructor-led courses. These courses should be used to augment and refresh the formal training given to your Db2 professional staff. 

The fourth, and final category of support, is reference material—for example, IBM’s Db2 manuals, Db2 books (such as DB2 Developer's Guide and A Guide to Db2 Performance for Application Developers), vendor-supplied white papers, and industry publications and periodicals. The current IBM manuals for Db2 are always available on the web, as well:

IBM Db2 12 for z/OS documentation

 

IBM Db2 13 for z/OS documentation

Of course, you should consider augmenting the standard IBM Db2 manuals with IBM redbooks. IBM redbooks provide in-depth, detailed coverage of a specific technology topic. IBM publishes redbooks on multiple subjects, including Db2, IMS, CICS, z/OS, and many other topics. IBM redbooks can greatly assist Db2 technicians working to understand a feature or nuance of DB2. You can download IBM red books for free in Adobe Acrobat format over the Web at http://www.redbooks.ibm.com/

IBM also offers many other useful sources of DB2 information on the Web including white papers, articles, and book excerpts from IBM Press publications. As does the International Db2 User Group (IDUG).

Independent Software Vendors (ISVs) are another rich source of Db2 information. The major vendors provide in-depth technical papers on features of Db2 that would be difficult for most shops to research in the same detail. Be sure to watch for and request the great Db2 Catalog posters, reference guides, white papers and eBooks available from BMC Software and Broadcom. There are other great Db2 ISVs who offer educational information, too, such as the newsletters, white papers, and webinars; these include InfoTel, SEG, and UBS-Hainer, among others.

All of these educational components—in-house education, external education, and industry publications—are useful for learning how you can use Db2 effectively. You would be wise to have a mix of material that supports more than one of the categories outlined previously. In this way, you provide a varied learning environment that meets the needs of all students. This varied learning environment allows each student to learn in the most conducive way for him or her. Plan to provide an on-site library of educational material addressing the following subjects:

• Introduction to relational databases
• Introduction to DB2 and SQL
• Advanced SQL
• Programming DB2 using (your languages of choice)
• Programming DB2 in batch
• Programming DB2 using TSO, CICS, and IMS
• Programming DB2 and the Web
• Creating DB2 stored procedures, triggers, and UDFs
• Programming DB2 in a Distributed Environment
• Debugging and Problem Analysis

You also might want to have an introductory Db2 database administration course to train new DBAs. In addition to this basic education library, plan to provide advanced education for technical Db2 users, such as DBAs, technical support personnel, and technical programmers and analysts. Advanced DBA topics (such as Sysplex Data Sharing, performance management, and backup/recovery) should be left to instructor-led training courses because of the complex nature of Db2 database administration. Additional advanced topics to consider include system administration (for systems programmers) and disaster recovery. Many vendors, including the ones metntioned above, offer these classes. Searching for smaller consulting firms and local resources is also prudent; these firms sometimes can provide courses tailored to your installation-specific needs.

The advanced education program should include allocating time to attend area user groups meetings, such as the International Db2 User Group (IDUG) conferences held every year in North America, EMEA, and Canada. 

The bottom line is simple, though. Plan for ongoing Db2 education for your DBAs, programmers, and analysts... or plan on failing.

See You In Scotland for IDUG?

Just a quick post to let folks know that I will be presenting at the IDUG EMEA Db2 Tech Conference the last week of October 2022. The conference will be held in Edinburgh, Scotland, UK at the Edinburgh International Conference Center (EICC), and even though the event runs from Saturday to Wednesday (instead of the customary Sunday through Thursday), you can still find a full slate of educational opportunities delivered by IBMers, vendors, users, and consultants from all over the world. In other worlds, there will be an impressive array of shared Db2 knowledge to consume.

So, where can you find me there?

First of all, on Monday, October 24th at 15:40 (for those of you who struggle with military time that is 3:40 PM) I will be presenting with Insoft-InfoTel on the topic "Intelligent Automation of Db2 Administration and Management." 

This session will discuss the importance of automation, particularly as it is applied to managing your Db2 for z/OS databases. I'll look at trends in automation and things you should consider automating. We'll also take a look at the various ways that traditional database management tasks are undertaken and the risks and cost associated with those tactics. And then InfoTel will share their iDBA-Online solution for automating your Db2 for z/OS management tasks and how it can mitigate risks and cost.

I will also be spending some time at the InfoTel booth in the exhibit hall on Monday evening, so be sure to stop by and discuss any Db2 topics with me!

I hope to see you at this year's IDUG EMEA conference the last week of October 2022. And if you are going to be there, be sure to attend my presentation and/or seek me out to say "Hello!"

The Importance of Data Masking for IBM Db2 z/OS

A three-pronged set of trends are conspiring to increase the need for organizations to protect and mask sensitive data stored in Db2 for z/OS databases:

• data privacy regulations place specific requirements on how data is to be protected, 
• data breaches continue to grow, thereby requiring improvements in data protection protocols,
• and the on-going requirement to copy production data to test in order to conduct realistic application testing 

Protecting your data while managing these three trends requires a systematic way to ensure that your sensitive data is not exposed or surreptitiously accessed. An ideal method to accomplish this is by masking sensitive data using a data masking tool.

If you want to learn all about the requirements for data masking, and how UBS-Hainer's BCV5 Masking Tool can protect your critical Db2 for z/OS data, be sure to register for and attend my upcoming webinar, Protect Your Sensitive Db2 for z/OS Data with the BCV5 Masking Tool on October 13, 2022 at 11am EST / 5 pm pm CEST.

I hope you will join me for this informative webinar.