TechTip: Struggling with Those DB2 Web Query Functions? Print
Tips & Techniques - Database
Written by Gene Cobb   
Friday, 22 April 2011 00:00

Support MC Press - Visit Our Sponsors

 

Forums Sponsor

POPULAR FORUMS

Forums

 

 

Search Sponsor

 

POPULAR SEARCHES

Search

Help is available! Learn about the handy Function Assist feature available in the DB2 Web Query Developer Workbench tool.

 

DB2 Web Query is loaded with built-in functions to help you transform your raw DB2 for i data into something more meaningful and useful for use in your reports, graphs, dashboards, and compound documents. Some of these functions have multiple parameters and a limited number of acceptable values, so unless you have been working with the product for some time, it may not be obvious how to implement the correct format, syntax, and values to make them work. Wouldn't it be nice if there was an interface complete with help text and examples to guide you through the process of defining these functions?

 

Well, hot diggity, there actually is! If you have the DB2 Web Query Developer Workbench tool installed on your PC, help is right at your fingertips in the Synonym Editor tool! The Synonym Editor has many nice help-related features, perhaps the most useful of which is a little gem called Function Assist.

 

To use Function Assist, open DB2 Web Query Developer Workbench, find your synonym and open it in the Synonym Editor. An example is shown below.

 

042211CobbFig1

Figure 1: Open the Synonym Editor. (Click images to enlarge.)

 

In order to use DB2 Web Query built-in functions, you must create either a Define or Computed field in your synonym. These are virtual columns that are not actual fields in your database file but can be based on a field from the file (or even another virtual column). While they are similar, there is a difference between a Define and a Computed column: the expression in a define column is executed for every row in the result set returned by the DB2 Web Query report, while a computed column's expression is executed for only each row from the grouped/aggregated result set after the aggregation has been performed. For more information on this, see Tyler Even's TechTip: "Exploit DB2 Web Query's Defined and Computed Fields."

 

For this article, we are going to create a Define column that uses the DATEADD function to calculate the date two weeks before the value of the ORDERDATE field in our database. To do this, click on the Add Define smart icon. As shown in the example below, it looks like a lowercase "f" with a thick blue underscore.

 

042211CobbFig2

Figure 2: Click on the Add Define icon.

 

In the name text box, type the name of your new define field (call it TwoWeeksBeforeOrder), and click on the Functions tab:

 

042211CobbFig3

Figure 3: Enter your define field and click on Functions.

 

You can either try to locate the desired function by expanding the function categories (provided you know which category it falls under) or—an easier way—use the Find icon as shown below.

 

042211CobbFig4

Figure 4: Locate the desired function with the Find icon.

 

Simply type the name of the function (or even the first few letters of it) and click Find Next. If a match is found, that function will be highlighted in the expanded category it is under. This is shown in Figure 5 below.

 

042211CobbFig5 

Figure 5: Your match is highlighted.

 

If you want help on the selected function, select it and click Help from the right-mouse-click context menu. As example is shown below.

 

042211CobbFig6

Figure 6: Right-mouse-click for help.

 

As you can see in Figure 7, this action opens up a new window titled Function References with information about the specific function you selected.

 

042211CobbFig7

Figure 7: Get information about the function you selected.

 

From here, a wealth of information is available about that function, including details about the syntax, valid parameter values, useful tips, and some examples to help guide you. Other functions can be researched by utilizing the Search and Index features in the left pane of this window. You can even build and save a list of Favorite searches!

 

Once you close the Function References window, you will return to the Define Calculator window. Simply double-click on the function to present it in the Function Assist window.

 

Function Assist is an extremely useful interface that will help you decide what values to specify for each of the function's parameters. If the parameter value is limited to a set of values, a drop-down list interface is presented for that parameter. In our example, the first parameter for DATEADD is the date field that you want to increment. If you click on the drop-down list twisty icon as shown in Figure 8, it will display all of the date columns from the synonym. Simply select the one that you want.

 

042211CobbFig8

Figure 8: Function Assist will help you choose values for the function's parameters.

 

The next parameter for DATEADD is the 'component_code'. Again, click on the drop-down twisty to see the valid parameter values. In the bottom pane, more information is displayed about the parameter.

 

042211CobbFig9

Figure 9: A drop-down list shows valid parameter values.

 

In our example, we want to add 14 days (two weeks) to ORDERDATE, so select 'D' for day units.

 

Finally, specify the number of days you want to increment ORDERDATE by. In our example, we specified 14, as shown below.

 

042211CobbFig10

Figure 10: Choose the number of days to increment by.

 

If you wanted to see some examples that use the function, simply click on the Example button, as shown in Figure 11.

 

042211CobbFig11

Figure 11: See examples that use the function.

 

The Example dialog is displayed. This gives you one or two examples, often with some other useful information specific to this function.

 

042211CobbFig12v2

Figure 12: Here's your example, along with other helpful information.

 

Click OK to close this window and return to the Function Assist Window. Once you are satisfied with the parameter values, click OK to return to the Define Calculator window.

 

042211CobbFig13

Figure 13: Return to the Define Calculator menu.

 

As you can see, all of the values specified from Function Assist are plugged into the Define Calculator.

 

You can bring up the Function Assist window directly from the Define Calculator field as well. This is often more efficient than going through the above Search process (particularly if you already know the name of the function you want to use). Simply type the function in the Expression pane (be sure to include both the left and right parenthesis after the function name) and click on the Function Assist button as shown in the example below using the SUBSTR function.

 

042211CobbFig14

Figure 14: Bring up Function Assist from the Define Calculator window.

 

The (hopefully) now-familiar Function Assist dialog window is displayed for the SUBSTR function:

 

042211CobbFig15

Figure 15: Your Function Assist window shows the chosen function.

 

Unfortunately, not all of the Function Assist and help features described above are available in the report development tools (such as Report Assistant and InfoAssist). However, you can use the Developer Workbench Synonym Editor and the techniques described to help you formulate the correct function syntax and values. Once you have them, simply copy/paste the expression into the Define Fields in Report Assistant and InfoAssist. Easy, pleasy, lemon-squeezy! If you currently do not have DB2 Web Query Developer Workbench installed, you can install a free 70-day trial version and take it for a test drive  The trial version can be downloaded here:

http://www.ibm.com/services/forms/preLogin.do?lang=en_US&source=swg-idwq

 

Hopefully, this tip has shown you the wide variety of helpful options that are available to lend you a hand while adding DB2 Web Query functions to your synonyms and reports. If you have other questions on the product or are looking for other tips and techniques, visit IBM's online forum, where you can post your questions to the experts. Just use the Forum link on the IBM DB2 Web Query developerWorks Web site.

 

Once registered, you can post your questions! You can even subscribe to the forum and get email alerts whenever there is a response to your question. Or if you just can't get enough DB2 Web Query information, you can subscribe to all the threads!

as/400, os/400, iseries, system i, i5/os, ibm i, power systems, 6.1, 7.1, V7, V6R1


Gene Cobb
About the Author:

Gene Cobb is a DB2 for i5/OS Technology Specialist in IBM's ISV Business Strategy & Enablement for System i group. He has worked on IBM midrange systems since 1988, including over 10 years in the IBM Client Technology Center (now known as IBM Systems and Technology Group Lab Services). While in Lab Services, he assisted customers with application design and development using RPG, DB2 for i5/OS, CallPath/400, and Lotus Domino. His current responsibilities include providing consulting services to System i developers, with a special emphasis in application and database modernization. He can be reached at cobbg@us.ibm.com.

Read More >>
Last Updated on Friday, 22 April 2011 09:52
 
User Rating: / 3
PoorBest 

Related Articles: