Data Studio Update Part 5
Query Building and Editing
27 June 2013
Data Studio makes it easy to create, run, organize and tune your DB2 queries. When developing a query, some times you just want to run a quick select to get a fast answer and sometimes you want to build a complex set of queries. Data Studio has tools to make all these tasks easy. It can help you with the syntax as well as giving you menu choices for table and column names so that you do not have to look them up. Further, it will validate your syntax as you are creating a script like a spell checker in a word processor, so that you don’t have to run the query to see if you have problems.
There are three main ways of starting the process of creating a query in Data Studio:
Quick and Dirty Single Table Select
This way is great when you just want do a quick select on a table and have Data Studio write the select for you listing all of the columns of the table. You then delete the columns that you don’t want and add a where clause if you like. You start by clicking the “Tables” or “Views” folder in the Administration Explorer to bring up the object list and then right-click the table or view that you want to query and choose “Data” from the first popup menu and then click ‘New “Select” Script’. If you are unfamiliar with how to navigate to the Administration Explorer, please see my article, Data Studio Update Part 2: Perspectives, Views and Database Connections. In Figure 1, I want to see the hit ratio for my buffer pools, so I start with the MON_BP_UTILIZATION database view to generate the select on the right.
Figure 1. Generate query from a view or table.
Once I generate the select I can edit it by clicking in the box with the select statement and then altering the query any way I like. If anything becomes underlined in red, then Data Studio has detected a syntax error in the query. I will discuss this in a later section. I only wanted to get a quick look at the hit ratios of the buffer pools, so in Figure 2, I deleted all columns except the buffer pool name and hit ratio columns and then ran the script by clicking the arrow in the green circle (step 1). To see the results, I then clicked on the SQL Results tab (step 2) then the SELECT statement shown under that tab (step 3) and finally the Results tab (Step 4) to see the output. Remember, you can double click on the “SQL Results” tab (the “Results” sub tab will not work) to make that part of the window bigger. While Data Studio seemed to let me save my script, I’ve been unable to find it anywhere when creating a script this way. So you want to only create queries with this method that you can throw away, or you can copy and paste the script into another file.
Figure 2. Run and Review query results.
Another way to get a quick look at the data in a table or view if you only need to see the first 500 rows is to use the built-in “Browse” or “Edit” capability. You can see how to do this in Figure 3. Starting in the Administration Explorer, click Tables or Views. In this case I wanted to get a quick look at my database’s table spaces, so I clicked “Views” and scrolled down to the SYSCAT.TABLESPACES view in the object list and right-clicked on it, then chose Data and then Browse Data. That brought up the results that I would see if I queried “SELECT * FROM SYSCAT.TABLESPACES”.
Figue 3. Browse the first 500 Rows of a table or view
SQL and XQUERY Editor and SQL Builder
To invoke the SQL Editor or SQL Builder you start with a project in the Data Project Explorer and either create a new project or use any project that has an “SQL Scripts” Folder. If you want to also manage stored procedures in an overall project for your database, then you should create a Data Development Project. For more information on how to do that and for a somewhat different perspective on creating scripts with the Editor and Builder, please see chapter 5 of the Getting Started with Data Studio for DB2 e-Book. You can also get more details about the editor and builder on the Working with SQL Statements page of the Data Studio Information Center. For the purposes of this article, I’ll use the GSDBTEST Change Plans Script that I created in my previous article Data Studio Part 4.
To invoke either the Editor or Builder you start in the Data Project Explorer view, and then click on the SQL Scripts folder of the project where you want to keep the SQL Script. In Figure 4 I am creating a script in the GSDBTEST Change Plans project.
Figure 4. Create a new script in the Editor or Builder
SQL and XQUERY Editor
Once you click the SQL or XQuery Script choice, you will get a pop-up window where you will name your scrip and choose the editor or builder and click the Finish Button. You will then be given a choice of databases on which you can run the query. Just select one and press Finish. If you need information on how to create database connections, please Part 2 of this series of articles. You can easily change which database you are using when you run the query. In Figure 5. I am naming the query dsPart5a and developing it against GSDBTEST.
Figure 5. Invoking the SQL and XQuery Editor
This will bring you into the same set of views as you saw in Figure 2 above, only now you can save the script and find it again as needed. In Figure 2 you can also see a set of tabs in the box above the view where the query appears including the Configuration, Validation, Special Register and Performance metrics tabs. Using these tabs you can do several things including changing the database on which you are running the script, whether you want Data Studio to validate (syntax check) your statements and which schema you want to use a default when you don’t specify one. For more information about what you can do in these tabs, please chapter 5 in the Getting Started with Data Studio for DB2 e-Book. Once you have the connections and other settings the way you want them you are ready to start writing SQL.
Two really nice things about the SQL Editor are:
1) It gives you menu choices for table names, column names just by clicking “ctrl+space bar”. This is called content assist.
2) It shows you syntax errors without running the script
In Figure 6, I am showing an example of both of these. In this example, I wanted to write the SQL from Figure 2 from scratch, but I didn’t remember the exact table name or the column names that I wanted so I started by typing “select from sysibmadm.”. Leaving my cursor at the end of the string I was typing, I pressed the CTRL key and space bar at the same time to get a list of tables. I then scrolled down in the list to find the table name. You can use the content assist nearly anywhere in the script to get appropriate choices for that position. In figure 6, you will also notice that the word “from” is underlined in red. This is because after the “select” key word you must have at least one column name or expression before the “FROM” keyword.
Figure 6. Press CTRL+SPACE to get a list of choices in context.
By figure 7 I have chosen the “MON_BP_UTILIZATION view by clicking on it in the drop down in Figure 6, then moved my cursor to a position between “select” and “from” and pressed CRTL+SPACE again to get a list of columns as shown in the left side of the figure. I did this twice to get both columns I wanted in my query as you can see in the right side of figure 7. I then move the cursor to the next line and pressed CTRL+SPACE because I did not want to type the WHERE key word, but wanted to choose it from a list instead. You should note that several other choices were presented in this box such as other table names. Remember that you can list multiple table names in the FROM clause. There were also other choices that could be put here. This capability extends beyond table names and key words, -- the content assist can be used to see all of the operators allowed for a where clause comparison and to get a list of functions such as SUM or SUBSTRING. Just put your cursor in the right place and press CTRL+SPACE and see what pops up! It should also be noted that this works for DDL as well so if you want to create scripts to create tables, indexes, sequences or any other object the content assist can help with those too.
Figure 7. Content Assist for column names and key words.
You can have multiple queries in the same script. You just need to separate them with the semicolon or what ever character you picked if you changed the delimiter. I show multiple queries in Figure 8. Also shown in figure 8 is how to view the status of the query and the results. You need to click the SQL Results tab if it is not already selected and then expand the most recent run of the script (dsPart5a.sql) to see the beginning of each statement. Then you click on the one that interests you. To see the SQL Code and messages click the Status tab, or to see the output of the query click the Result1 tab. In Figure 8 I have selected the first statement and am showing the Status on the right.
Figure 8 Viewing SQL Status and Results
An alternative to the SQL Editor is the SQL Builder. This technique for creating SQL is great if you don’t know a lot about the tables or want to build complex joins and want to graphically draw the join criteria. To see additional information about the SQL Builder please read chapter 5 of the Getting Started with Data Studio for DB2 e-Book. Like using the SQL Editor, you start a new SQL Builder script by opening a project, right-clicking the SQL Script folder, choosing “New” and then “SQL or XQuery Script as is shown in Figure 4 above. In the view you see in Figure 5, you choose the radio button for “SQL Query Builder” and below that the “Statement Type to choose SELECT, UPDATE, etc. and then press finish. Once you pick your database, you will be shown a window similar to left side of Figure 9. To begin creating your queries, you right click in the middle box of the builder as instructed (highlighted in red). This will pop up the “Add Table…” menu as shown on the right side of Figure 9 which gives you the Add Table box shown when you click the pop-up. You then expand the schema that you want and choose a table by double clicking on it. Keep repeating these steps to get all of the tables you want to join in the middle box. As you do this the FROM clause in the top box will be completed.
Figure 9. Starting Query Builder
Once you choose the tables you can see each of them in that middle box as shown in Figure 10 where I have chosen the SNAPDB, SNAPAPPL_INFO and SNAPAPPL tables. Notice that each box shows the columns in the table. You can move the tables around by dragging them and you can resize them too. You can join the tables by clicking each join column from one table and dragging it onto the corresponding column in the next table. You keep doing this until you have created all of the needed joins. As you do this the FROM clause is expanded for you.
Figure 10. Three Joined Tables
Next you will want to enumerate the columns you want to see in the query. To do this just check the box to the left of each column that you want to select in the query. As you can see in Figure 11 a number of columns have been checked such as the APPL_STATUS in the SNAPAPPL_INFO table. As you check them, they automatically show up as columns in the SELECT statement. Next you will want to add your WHARE clause condition. To do that you click the “Conditions” tab in the bottom box of the view (highlighted in red in Figure 11). You then choose the columns and operators from drop downs in each box and finally type the value to which you want to compare the column. This lower box is where you also specify GROUP BY conditions in the appropriate tabs. The query that you see built here shows the oldest transaction in the database and is useful when long running transactions are causing problems.
Figure 11. Check columns to select and create WHERE conditions
Finally to run the query, right-click anywhere in the top box where you have the query itself and choose “Run SQL” from the pop-up menu. You then close the script by clicking the “x” next to the script name in the tab and then choose “Save” to save your changes. After that you can open the query in the Query Editor to type changes and use content assist if you want to make free-form changes to the query. Do that by right clicking the scrip name in the SQL Scripts folder and choosing “Open With | SQL and XQuery Editor”.
Find Scripts on your Hard Drive
Once you have created and saved your scripts in either the editor or builder you can run them from any application that you like. You can copy and paste them. You can also just copy the files that Data Studio creates. To find the script file on your hard drive, you go to the directory that you specified in Data Studio as your workspace. In my case I use C:\IDA. Then in that directory you look for your project name (the project name in the Data Project Explorer view) and then in that directory you will see the files in from your SQL Scripts folder. In the above examples I created Scripts dsPart5a.sql and dsPart5b.sql under the GSDBTEST Change Plans project. In Figure 12 you can see that I am using Windows Explorer to view these files and opened dbPart5a that I created with the SQL Editor in Notepad. Now you can use or copy the files anywhere you like without opening Data Studio.
Figure 12. Where to find your SQL Scripts.
Once you have written your queries, it is a good idea to make sure that they will run efficiently if you are going to put them into a production application. With Data Studio you can run Visual Explain and other query tuning tools from Data Studio. For information on how to do this please chapter 7 in the Getting Started with Data Studio for DB2 e-Book.
This is the Forth in the series of Data Studio Articles that I am writing. Earlier editions that may interest you include:
Data Studio Update Part 1 – Links for installing, summary of my favorite features
Data Studio Update Part 2: Perspectives, Views and Database Connections – Navigating Data Studio, connecting to your databases.
Data Studio Update Part 3: View DB Objects and Attributes – View tables, columns, indexes and model diagram.
Data Studio Update Part 4: Changing Database Objects – Generate changes that preserve data and objects.
Data Studio Update Part 6: Query Tuning for DB2 LUW and z/OS – Tune individual Queries.
Data Studio Update Part 7: Find and Tune Bad Queries from the Package Cache – Find bad queries in the package
There are many more options that I show in these query editors that can make your life easier, and I suggest that you take some time to explore them. Please take a view minutes to click around and see what is available. Please add any features that you really like to my Facebook Page or my db2Dean and Friends Community and help the extended community.