DB2 Viper II and the IBM Data Studio Developer Workbench - Page 3
October 19, 2007
4. Specify the filter that you want to apply to this database connection by clearing the Disable filter check box and building the filter for your database connection. For our example, select the Selection radio button; select the schema you used to create the SAMPLE database (in my case it was PAULZ because I was logged onto a Windows system as this user and issued the db2sampl xml sql command); select the Include selected items option from the corresponding drop-down list. Then click Finish.
Filtering is a great idea when you work with databases with large schemas. For example, some enterprise resource planning (ERP) applications such as SAP or PeopleSoft can have over 30,000 tables! Filtering provides a convenient way to quickly access only those schema objects you are interested in. Whats more, it provides a security meta-data layer as well. Generally, if youre working with a large database schema, I strongly recommend using a filter. Finally, your whole development environment will perform faster because the entire schema wont have to be fetched into the Database Explorer view. As you can see in the previous figure, there are essentially two methods you can use to build a filter: expressions and selections.
You can use the Expression filter to define all sorts of variations of filters that are generated by almost any imaginable sequence that relates to your schema names. The following figure shows the different stemming options for an expression-based schema filter:
For this example, I just want the tables in the PAULZ schema to show up in the Database Explorer view, so I simply enter that into this field. When you use this approach to filter the Database Explorer view you can end up with tables you didnt expect t see. For example, using the Starts with the characters option, the Database Explorer view would include those tables within the PAULZ schema, but also within any other schemas that start with these letters, such as PAULZ1 or PAULZ2.
If you want to filter on a specific schema (or schemas) and dont require the advanced filtering generation capabilities that the expression approach offers, you can simply select the Selection radio button and select all the schemas you want included in the connection object you are creating. For example, in the following figure, Im defining a filter on my database connection such that only those objects within the PAULZ schema will appear in the Database Explorer view:
Note that you can include or exclude objects on the basis of a schema name using the drop-down list within the Selection box, as shown below:
As you can imagine, if you select Exclude selected items from the drop-down list, all schema objects other than those in the PAULZ schema will be shown in the Database Explorer view.
You can further filter objects underneath a schema name once a connection is added to the Database Explorer view. For example, assuming the previous filter (which included the PAULZ schema) is applied to the database connection, you may want to filter those tables further within the PAULZ schema to start with PROD such that only tables like PRODUCT and PRODUCTSUPPLIER are shown within the PAULZ schema, as shown below:
After you complete the previous steps, the Database Explorer view should now look similar to the following figure:
You can see that a filter has been applied to the Schemas folder. The [Filtered] keyword is added to any folder that is filtered to give you design-time information in regards to why you may not see the objects you are looking for. Of course, if you created a filter using the Exclude selected items option detailed after Step 4, the Database Explorer view would look like:
You can dynamically change a filter at any time by right-clicking a filtered folder and selecting the Filter option.
Wrapping it up...
In this article, I introduced you to the Database Explorer view thats part of the new IBM DS DWB in the DB2 Viper II open beta. In addition, I showed you how to add a database connection to this view and apply filters to different schema objects to optimize the design-time experience for your database. In the next article, Ill introduce some of the tasks that you can perform from the Database Explorer view.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technology team. He has more than 13 years of experience with DB2 and has written more than 150 magazine articles and is currently working on book number 12. Paul has authored the books Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA class, and trying to figure out the world according to Chloë his daughter. You can reach him at: firstname.lastname@example.org.
IBM, DB2, DB2 Universal Database, pureXML, z/OS, Informix, Rational, i5/OS, Cloudscape are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. 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, 2007. 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.