DB2 9.5 and IBM Data Studio: Part 8: The SQL Builder Development Accelerators - The Rest of the Story - Page 2
March 17, 2008
SQL templates are actually a favorite feature of mine that likely should have made it into one of my other articles that highlight the top features of the SQL Builder; however, I had to leave something of great interest for this last section too!
IBM Data Studio supports the concept of templates. Programmers are well familiar with these objects for extreme application development. Essentially, templates are structured descriptions of coding patterns that recur in source code. Java editors support the use of templates to fill in commonly used source patterns. For example, a common coding pattern is to iterate over the elements of an array using a FOR loop that indexes into the array. By using a template for this pattern, you can avoid typing in the complete code for the loop. Invoking Content Assist (Ctrl+Space) after typing a word will present you with a list of possible templates for a FOR loop in the Java perspective in IBM Data Studio. You subsequently choose the appropriate template by name (in this case, Iterate over Array). Selecting this template will insert the code into the editor and position your cursor so that you can edit the details.
IBM Data Studio extends the benefits of templates for SQL development as well, providing an enormous productivity boost for database administrators (DBAs) and programmers alike. Whats more, since IBM Data Studio can integrate with source control programs such as Microsoft Visual Source Safe, IBM Rational ClearCase, Concurrent Versions System (CVS), and more, youve got a great mechanism to ensure that best practices proliferate throughout the organization and into your business logic.
As of the DB2 9.5 release, SQL Templates are only available in the SQL Editor, so if you built your SQL statement using the SQL Builder, open it using the SQL Editor to access this feature. You can access the template list within the SQL Editor by invoking Content Assist (Ctrl+Space) from within SQL Editor.
To see how this works, create a new SQL statement and press Ctrl+Space below the default generated SELECT shell to see a list of all the available built-in templates in IBM Data Studio. In the following example, you can see that I selected the first occurrence of the XMLVALIDATE XMLVALIDATE scalar function template. (You select templates in the same manner that you select Content Assist options.)
As you may have noticed, a lot of templates are already shipped with IBM Data Studio. You can see all of the SQL-based templates that ship with IBM Data Studio by selecting Windows>Preferences>SQL Editor>Templates.
IBM Data Studio also provides the ability to generate your own SQL templates and subsequently retrieve them for use within the SQL Editor.
To create a new SQL template, perform the following steps in the previous window:
1. Click New. The New Template window opens.
2. Call this template DescribeATable using the Name field.
3. Select the type of statement you are creating using the Context drop-down list. For this example, the default sql is fine. (If you were defining an XQuery statement, you would select xquery instead.)
4. Leave the Automatically insert field selected; this will instruct IBM Data Studio to instantly add the template without prompting the SQL Editor after you select it from the context pop-up menu.
5. Enter a description of the template in the Description field.
6. Use the Pattern field to enter the SQL statement. For our example, use the following statement:
CALL ADMIN_CMD('describe select * from employee');
For this example, we want to create a template SQL statement that will describe a table without having to use an API or the command line processor (CLP). DB2 includes the ADMIN_CMD administrative routine that effectively enables you to flow SQL to the database to subsequently perform management operations and so on. This make the management of your data server via the SQL API much easier and broader scoped. Your New Template window should now look like this:
You can use the Insert Variable button to add special register variables into your SQL template as well. For example, you could augment the previous template to include information such as the current time when the statement was run, and so on:
7. Press OK.
8. The new SQL Template is added to the Templates window, as shown below. Click OK.
Once youve defined your custom template, you can refer to it in the SQL Editor: simply type in the name of the SQL template and press Ctrl+Space. IBM Data Studio will insert the template into the designer palette:
Note: You can also type in the first few letters of the SQL template name and press Ctrl+Space, and IBM Data Studio will insert the first SQL template it finds that matches that combination. For our example, you could type DES, and then Ctrl+Space, and IBM Data Studio would insert the DescribeATable SQL template you created in the previous step.
From here, you use your editor as you normally would. For example, run this SQL statement. In this example, you can see that the SQL statement describes the EMPLOYEE table that resides in the SAMPLE database.
Now change employee to staff to describe that table, and run the SQL statement again and you will see the structure of the STAFF table.
Wrapping it up...
In this article, I rounded out the features of the SQL Builder and finished off with a great feature that is part of the SQL Editor -- SQL templates. The SQL Builder is good for single data manipulation language (DML) statements, or to get you started. As you noticed, in order to leverage SQL templates, you have to open an SQL statement using the SQL Editor. (If you created your SQL statement using the SQL Builder, you can still open it in the SQL Editor.) This seems like a great segue into the next part of this series, where I will detail all the features of the SQL Editor. By the time you are finished reading the next article, youll have an excellent grasp of the SQL development features provided by IBM Data Studio, and know which facility to use for the task at hand.
IBM, DB2, ClearCase, and Rational are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Microsoft is a trademark of Microsoft Corporation in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2008. All rights reserved.
The opinions, solutions, and advice in this article are from the authors experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the authors knowledge at the time of writing.