Building efficiencies into a DBA's day

Using command templates in Optim Database Administrator

Optim™ Database Administrator (ODA), previously known as Data Studio Administrator, helps improve Database Administrator (DBA) productivity by providing advanced change management capabilities as well as support for common database administration tasks. The SQL and XQuery Editor in ODA (also available as part of Data Studio and other Optim products) provides contextual help that makes command editing quick and command execution less error-prone. This article shows you how to use templates and create customized templates to improve your productivity when using database commands. It also shows you how to test your customized templates and export them to share with other members of your team.

Share:

Salvador Ledezma, Staff Software Engineer, IBM

Salvador Ledezma photoSalvador has been working at IBM since 2002 at the Silicon Valley Lab, in San Jose, CA, initially developing Java-based workloads and applications for DB2 z/OS. Salvador currently spends his time working on runtime and tooling technologies for IBM's data server products using the Eclipse platform.



Swetha Patel (swepatel@us.ibm.com), Software Engineer, IBM  

Swetha Patel photoSwetha Patel is a Software Engineer working for Optim Database Administration tools. Swetha has previously worked on database drivers for languages such as Ruby, Python, Perl, and PHP for IBM databases.



Kathryn Zeidenstein (krzeide@us.ibm.com), InfoSphere Guardium Evangelist, IBM

Photo of Kathryn ZeidensteinKathy Zeidenstein has worked at IBM for a bazillion years. Currently, she is working as a technology evangelist for InfoSphere Guardium data activity monitoring, based out of the Silicon Valley Lab. Previously, she was an Information Development Manager for InfoSphere Optim data lifecycle tools. She has had roles in technical enablement, product management and product marketing within the Information Management and ECM organizations at IBM.



01 October 2009

Also available in Portuguese

Introduction

In addition to database change management, Optim Database Administrator Version 2.2 for DB2® for Linux®, UNIX®, and Windows® (ODA) provides DBAs with powerful and easy-to-use tools for performing day-to-day administrative tasks. For example, the Utility Task Assistants provide task-oriented, UI-driven steps for performing tasks on databases such as backup, restore, configure, create, and drop.

View a demo that illustrates the concepts in this article.

As DBAs become more proficient in performing these tasks, they may want to customize the command syntax or skip some of the steps provided by the Task Assistants. Those who prefer to issue commands themselves can use the SQL and XQuery Editor for editing and executing commands directly. Still, in this world of multi-tasking and information overload, it can be difficult to remember the full command syntax or the rich set of command options that are available. Administrative Command content assist templates, which are similar to SQL and XQuery templates already available in the editor, can help DBAs quickly and easily create reusable administrative command scripts.

In this article, we show you how to use these content assist templates in the context of administration commands. We include some useful templates you can start using right away. The article also discusses the creation and sharing of customized templates that may more readily adhere to your team's or company's data standards.

Prerequisites

The instructions in this article are based on using Optim Database Administrator Version 2 Release 2 with Fix Pack 1. The Resources section contains a link you can use to download a 30-day trial version that includes the required Fix Pack.

The instructions in this article assume that you have some experience using ODA 2.2 or the previous version of ODA, which was named Data Studio Administrator. It is also assumed that you are familiar with the Eclipse environment that is part of ODA. You will be using the Database Administration perspective (see Figure 1).

Figure 1. Database Administration perspective of ODA
Database Administration perspective is a perspective that is designed specifically for DBAs with the Data Source explorer being prominent.

For more information on getting started with ODA 2.2, see the Resources section.

Install sample database: To use our examples, you need to install the 2.2 version of the GSDB sample database. Go to the DB2 Information Center, for links to download the database and instructions on setting it up. Figure 2 shows the GSDB database in the Data Source Explorer (DSE).

Figure 2. GSDB database in the Data Source Explorer (DSE)
Shows DSE with GSDB connection to DB2 LUW database under database connections folder.

Review of the SQL and XQuery Editor

You use the SQL and XQuery Editor to write and execute SQL and XQuery statements and scripts. The editor is available in several products in the Optim portfolio. With Optim Database Administrator and IBM Data Studio, you can also use this editor to execute administration commands.

You can use the editor to develop scripts. Each script by default is saved in a file with a .sql extension. The editor provides features that are familiar to Java application developers, including syntax highlighting, multi-line comment toggling, and content assist. Figure 3 shows a sample SQL script in the editor.

Figure 3. SQL script in the SQL and XQuery Editor
The script appears in the main editor window on the right side of data source explorer.

When a script is in the editor, you can execute it in one of the following ways:

  • From the main menu, select Run > Run SQL as shown here:
    From main window toolbar, click Run and then RUN SQL
  • Right click in the editor and choose Run SQL as shown here:
    Select RUN SQL which is towards the bottom of the context menu.

Tip: To enhance productivity even further, you can use the accelerator keys: F5 or Ctrl+Alt+R.

Creating a script

When you create a script, you can choose to execute it immediately, or you can save it into an existing project.

Use the following instructions to create a Data Development project and save a script to it. Later you will modify the script while learning how to use command templates.

  1. Connect to the GSDB database.

    If you aren't already connected to the GSDB database, right-click the GSDB connection icon in the DSE and select Connect.

  2. Create a Data Development project.
    1. Right-click in the Data Project Explorer and select New > Data Development Project (see Figure 4).
      Figure 4. Creating a Data Development project
      Right-click in white space of the Data Project Explorer to bring up the menu then.
    2. In the New Data Development Project dialog, enter a Project name. In our example, shown in Figure 5, we use the name Working With Templates.
      Figure 5. Name the project
      Enter the name 'Working With Templates' in the project name field
    3. Select Next >.
    4. An SQL script is usually associated with the database that it will execute against. From the Select Connection page, select the connection to the GSDB database (see Figure 6).
      Figure 6. Select the GSDB connection for the script
      Select the connection for the GSDB database from the Select Connection page of the New Data Development project dialog.
    5. Select Finish.
    6. When asked whether you want to change to the Data perspective, select No.
  3. Create an SQL script.
    1. From the New SQL or XQuery Script dialog, enter myTemplate for the new script's name and leave the default selection to edit using the SQL and XQuery editor (see Figure 7).
      Figure 7. Creating a new script
      In the Script and Tool page, enter myTemplate for the Name field and leave the default selection for Edit using.
    2. Select Finish. The script is now ready to be edited.

Tip: You can also create a script outside of a project. However, you cannot save the script outside of a project, so when you try to save you are then required to create a project for it. Ways to create a script outside of a project include:

  • Click the New SQL Script icon on the Data Source Explorer toolbar (see Figure 8).
    Figure 8. Using the DSE to create a new script
    New SQL Script icon on the DSE toolbar is highlighted and moused over.
  • If already connected to a database, right-click the connection profile or the database icon and select New SQL Script (see Figure 9).
    Figure 9. Creating a new SQL script from the database
    New SQL Script selection is highlighted and moused over on context menu after right-clicking the GSDB database icon.

Content assist in action

You can use content assist to insert standard code patterns into scripts. As you type an expression or a clause, you can select from among various templates of a statement. For example, for a SELECT statement, your choices can range from among the most basic SELECT template to a SELECT template that uses XML functionality, such as XMLTABLE or XMLQUERY.

Let's demonstrate how to use content assist by editing the MyTemplate.sql script you created in the previous section.

Open the MyTemplate.sql script and position the cursor where you want to insert a particular SQL code pattern. To invoke content assist, type the first few letters or the full name of the action to be completed and press Ctrl+Space.

For example, for a SELECT statement, type SEL and press Ctrl+Space. A list of possible matches, called content assist proposals, is displayed as shown in Figure 10.

Figure 10. List of content assist proposals matching SEL
SQL editor after entering 'SEL' and pressing Ctrl+Space shows a list of proposed SELECT statements that can be selected.

The first proposal simply completes the SELECT keyword. More interesting is the second proposal (SELECT - Most basic SELECT statement), which selects all columns from a table.

Let's see how you can use this template to help view all countries served by the Great Outdoors Company. Select the second proposal by double-clicking it. The code in the template pattern is inserted (see Figure 11). You just saved yourself a lot of keystrokes!

Figure 11. Inserting the SELECT proposal list
The statement 'SELECT * FROM table' has been inserted as the first line of the script. 'table' has a box around it.

Boxes are used in the SQL syntax that is inserted to highlight variables that can be customized. The cursor is placed in the box around the table variable. Content assist can help further in case you don't quite remember the name of the table you want to select. In the outlined box, where the cursor has been placed, type the schema name GOSALESCT followed by a period, as shown here:

GOSALESCT.

Because content assist is context-aware, it now provides you a list of tables associated with the GOSALESCT schema. From the list, select CUST_COUNTRY (see Figure 12) and press the Enter key. This inserts GOSALESCT.CUST_COUNTRY into the SQL script in place of the table variable.

Figure 12. Using content assist to determine table name
After typing GOSALESCT, content assist shows a list of the tables in the schema.

To execute the SQL, right-click in the editor and select Run SQL. The command runs successfully and you can see the results of the query in the SQL Results view at the bottom right corner of the workspace (see Figure 13).

Figure 13. SQL Results View showing query results
The SQL Results views shows all the rows of the GOSALESCT.CUST_COUNTRY table.

Content assist can be especially useful for more complex SQL where the exact syntax or keywords may be difficult to remember. As you'll see in the next section, the concept of content assist is also applied to administration commands and utilities that often involve multiple options and can be quite complex.

Importing and using predefined command templates

The Download section contains a file named admin.xml that contains sample templates for common commands. You can use these templates along with content assist as a starting point for writing some of your own scripts.

The command templates provided in admin.xml are:

  • UPDATE DATABASE CONFIGURATION
  • UPDATE DATABASE MANAGER CONFIGURATION
  • DEACTIVATE
  • ACTIVATE
  • RESTART
  • FORCE APPLICATION
  • LIST APPLICATION
  • BACKUP
  • ROLLFORWARD
  • CREATE DATABASE
  • DROP DATABASE

For details on usage of these commands, including the full syntax diagrams, refer to the IBM DB2 Database for Linux, UNIX, and Windows Information Center for your release of DB2 for Linux, UNIX and Windows.

The admin.xml file defines how a proposal is presented in the editor when content assist is requested by the user. Each proposal is called a template and defined in a <template> tag. For example, the proposal for UPDATE DATABASE CONFIGURATION is shown in Listing 1:

Listing 1. Template for UPDATE DATABASE CONFIGURATION command
<template 
      name="UPDATE DB CFG" 
	 description="UPDATE database configuration parameter" 
      id="com.ibm.datatools.adm.db2.luw.templates.update_db_cfg" 
	 context="sql" enabled="true" 
	 autoinsert="false">UPDATE DATABASE CONFIGURATION 
	USING 
	${config_keyword} ${value};
</template>

The Try it yourself: Create a command template section provides more information about the attributes in the template tag.

To import the admin.xml file into your ODA workspace and make the commands listed above available in the SQL and XQuery Editor through content assist, do the following:

  1. Download the admin.xml file located in the Download section to your machine.
  2. From the main menu in ODA, select Window > Preferences.
  3. In the Preferences window, navigate to Data Management > SQL Development > SQL and XQuery Editor > Templates.
  4. On the Templates page, select Import... (see Figure 14).
    Figure 14. Adding the administration command template file
    After taking the navigation path described above, you get to a list of all current templates and click the Import button.
  5. From the Importing Templates dialog, navigate to the admin.xml file you downloaded and select Open.
  6. On the Templates page, select Apply and then OK.

The templates are now available in your workspace.

Using command templates: A scenario

To demonstrate how administration command templates can be used now that they are imported, let's follow along with a DBA at the fictitious Great Outdoors Company. To perform these steps yourself, you need to have first imported the command templates provided with this article as described in the previous section.

Business requirement: A Great Outdoors DBA, Thomas, has a business need to create a test database in order for application developers to prototype a new application. Thomas knows the commands to be executed and the proper order, but cannot always remember the exact syntax of the commands. The commands he will use are CREATE DATABASE, CONFIGURE DATABASE, CREATE TABLE, and BACKUP. These are typical administration commands that Thomas routinely needs to execute.

Setup: Thomas uses the Working with Templates project created earlier. He has created and opened a new script within the project. All the commands will be written in this script.

Steps: Following are the steps Thomas will perform:

  1. Create the database
  2. Configure the database
  3. Create table and backup
  4. Run the script and save it for later use

Step 1: CREATE DATABASE

  1. In the script editor, type CRE and press Ctrl+Space.
  2. The editor responds with a small content assist window that lists all possible commands matching CRE. These include CREATE TABLE, CREATE INDEX, and CREATE DATABASE (see Figure 15).
    Figure 15. Using the CREATE DATABASE template in the editor
    Content assist shows the list of possible matches for commands starting with 'CRE', which was typed into the script editor.
  3. To create a simple system-managed database, select CREATE DATABASE by double-clicking it.
  4. The CREATE DATABASE command is automatically inserted into the editor as it is defined by the template in admin.xml (see Figure 16).
    Figure 16. Customizing the CREATE DATABASE template
    The CREATE DATABASE command is shown in the script editor with the clauses defined in the template from admin.xml.
  5. As shown in Figure 16, the command template contains a variable for database and two variables for file_system_path. The cursor is placed in the box around the database variable.
  6. Replace the database variable by typing the name of the new database, which is TPAYROLL.
  7. Press the Tab key to move the cursor to the first file_system_path variable.
  8. Type the file path name: c:\data. Notice that what you type replaces both instances of the file_system_path variable.
  9. Press the Enter key. The CREATE DATABASE command is complete and the cursor at the beginning of a new line in the script editor.

Step 2. CONFIGURE DATABASE

  1. The next command Thomas wants to enter into the script is one to update a configuration parameter for the database. On the line below the CREATE DATABASE command, type the letter U and press Ctrl+Space. Content assist now shows you a list of commands that begin with the letter U.
  2. Once you display the list of proposals from content assist, you can continue to refine it by typing more letters in the editor. Type PD so that you now have the letters UPD in the editor. Each time you type another letter, the list becomes further refined.
  3. At this point, each proposal that appears in the list starts with the keyword UPDATE. Select the UPDATE DB CFG proposal with the description "Update Database Configuration Parameter" by double-clicking it.
  4. The UPDATE DATABASE CONFIGURATION command is automatically inserted into the editor as it is defined by the template in admin.xml (see Figure 17).
    Figure 17. UPDATE DATABASE CONFIGURATION proposal
    The UPDATE DATABASE CONFIGURATION command now appears in the editor. The template includes variables for the configuration keyword and its value.
  5. As shown in Figure 17, the command template contains variables for config_keyword and value. The cursor is placed in the box around the config_keyword variable.
  6. To change the application heap size, type APPLHEAPSZ for config_keyword, press the Tab key to move the cursor to the value variable, and type 1024.
  7. Press Enter to complete the command and move the cursor to a new line.

Step 3. CREATE TABLE and BACKUP

Thomas proceeds in a similar fashion using content assist to add CREATE TABLE and BACKUP commands to the script.

Step 4. Run and save the script for later use

When Thomas completes his script, he right-clicks in the editor and selects Run SQL. The commands run successfully. He can now save the script and, with some minor editing, he can use it whenever he needs to create a new test database for new development projects.

Try it yourself: Create a command template

We did not include all administration command or all permutations of commands in the admin.xml sample templates file. The nice thing about templates is that you can easily modify an existing one or create your own customized ones to suit your needs or to help other DBAs on your team.

Create a command template

The process described in this section can be used to create any number of new SQL and XQuery templates. For example, you may find that after you load data into a table, you often run a SET INTEGRITY command. By creating a template, you can more quickly and accurately repeat this action in the editor.

To create a new template, do the following:

  1. From the main menu in ODA, select Window > Preferences.
  2. In the Preferences window, navigate to Data Management > SQL Development > SQL and XQuery Editor > Templates.
  3. On the Templates page, select New… (see Figure 18).
    Figure 18. Creating a new template
    After taking the navigation path described above, you get to a list of all current templates and click the New... button.
  4. In the Name field of the New Template dialog, enter the name for your new template. For this example, enter SET INTEGRITY.
  5. For the Context selection, leave the default of sql. The other available selection is xquery, which is used to associate the template with an XQuery statement.
  6. Leave Automatically insert selected. This means that the template will be inserted into the editor automatically after you type its name and press Ctrl+Space. The template name must be unique for the automatic insert to work. If there is more than one template with the same name, they appear as proposals in the content assist.
  7. For Description, enter a short description of the template, for example: Check integrity for a table. This description is displayed in the list of content assist proposals in the editor.
  8. For Pattern, enter the command syntax that should be inserted automatically. The pattern can contain variables, which allow for parameterization of the template.

    You can use the Insert Variable… button to insert the common variables date, time, user name, and year. Otherwise, define variables with the notation: ${variable_name}. The variable name is a placeholder that the user replaces when the template is inserted in the editor. To help users who will be using your template, it may be useful to develop a standard naming convention (for example, table_name, column_name, database_name, expression, etc.).

    In this simple example for SET INTEGRITY, let's make only the table name a variable. Enter the following for the pattern:

    SET INTEGRITY FOR ${table_name} IMMEDIATE CHECKED ;

    When all the fields are filled in as described above, the New Template dialog should look similar to Figure 19.

    Figure 19. Creating a new template for SET INTEGRITY
    New template dialog contains name, description and pattern field.
  9. Select OK. The template is added to the list on the Templates page.
  10. In the Preferences window, select OK. Your template is now ready for use and testing.

Test the new command template

You should always test your command templates to ensure they work as intended. To test the new template you created in the previous section, do the following:

  1. In the SQL and XQuery Editor, type SE and press Ctrl+Space. This shows you the content assist with a list of proposals that match your input.
  2. Scroll to the bottom of the list and find your new SET INTEGRITY template (see Figure 20).
    Figure 20. Selecting the SET INTEGRITY proposal
    SET INTEGRITY is now at the bottom of the list of content assist proposals that match the 'SE' that was typed into the editor.
  3. Double-click the SET INTEGRITY proposal to insert its code template into the editor. table_name should be outlined to indicate that it is a variable that can be customized (see Figure 21).
    Figure 21. Customized SET INTEGRITY statement
    The SET INTEGRITY command is shown in the editor with table_name as a variable.

Export your new command template

After testing a new command template, you may want to make it available to other members of your team. You do this by exporting the template to an XML file. Not only does this help others be more productive, it also helps reduce errors. You can also begin using consistent and appropriate labeling of your templates to encourage use of options that are standard for your organization.

To export a command template to an XML file, do the following:

  1. Go to Window > Preferences.
  2. In the Preferences window, navigate to Data Management > SQL Development > SQL and XQuery Editor > Templates.
  3. On the Templates page, select the template you want to export. For this scenario, you would select your new SET INTEGRITY template (see Figure 22).
    Figure 22. Exporting a template
    statement is shown in the editor with the table name as a variableNote: To select multiple templates, press the Ctrl key and click the templates you want to select.
  4. Select Export….
  5. From the Export Templates dialog, choose the path where you want to save the template, enter a name for the template, and select Save.
  6. You can now send the exported xml file to your teammates. They can import the new templates into their own ODA workspaces and enjoy the productivity gains that templates provide.

Conclusion

The ability to use and customize templates for command and SQL editing can help you quickly and easily create reusable scripts that follow your organization's standards. In this article, we have described how to import existing command templates into your workspace, as well as how to create your own templates. Using the techniques described here, you are now able to create your own template file of commands. After proper review and test, you can now also export those commands and share them with other DBAs in your organization, thereby ensuring a level of consistency and accuracy across your team.


Download

DescriptionNameSize
Sample command template fileadmin.xml5KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=432607
ArticleTitle=Building efficiencies into a DBA's day
publish-date=10012009