The Schema Creation Wizards - Page 2
June 22, 2005
The DB2 Create View wizard
The DB2 Create View wizard starts with essentially the same two pages that the DB2 Create Table wizard starts with: introducing the wizard and giving you the option to name the view, specify the schema, and add a comment. (These pages also allow you to specify an INSERT or UPDATE row conformance check for dynamic views.)
Now you are ready to define the SQL statement that forms the view. The DB2 UDB add-ins for VS.NET provide a specialized DB2 UDB SQL editor for writing DB2 UDB SQL statements:
Notice that when I typed in the SELECT * FROM PAULZ statement that forms the basis of the view, and specified a schema that exists in the database, followed by a period (.), the Intellisense feature of VS.NET was invoked. The VS.NET add-in for DB2 UDB enhances the VS.NET Intellisense feature to be "DB2 UDB-aware."
In the preceding figure, you can see that I am building a view on the STAFF table. In addition to this, note the syntax colorization of the keywords. The "DB2 UDB-aware" Intellisense feature is provided for all wizards that give you the option to enter SQL (for example, the DB2 SQL/PL Stored Procedure wizard also includes this feature), and the DB2 UDB SQL editor (which I will cover in the next article in this series). This greatly helps developers navigate their schema.
In addition to this, note the presence of the Validate SQL button. If you click this button, the SQL statements you enter are validated (before build or run time) and you can find out if you have mistyped a statement or selected an object that does not exist in the database. Think about the productivity enhancements of finding errors before build or run time - this neat little feature can save you a lot of time!
If you recall from Part 1 in this series, this feature is possible because the DB2 UDB add-in creates a cache of the schema underneath the IBM Explorer window; VS.NET leverages this cache to perform syntax checking, schema validation, enable offline operations, and more. The remaining options in this wizard are the same as in the DB2 Create Table wizard.
The DB2 SQL Procedure wizard
From the IBM Explorer, you can also create DB2 UDB stored procedures: both SQL/PL and common language runtime (CLR) procedures. I will cover CLR procedures in a future article in this series since it involves the creation of a class file and its subsequent compilation into an assembly.
The DB2 Create SQL Procedure wizard starts like the DB2 Create View wizard in that you can specify options such as the schema where the procedure should be created, the schema name, and more. There is a special window for you to enter the routine's SQL statement - it is hooked up to the DB2 UDB-aware Intellisense feature and the SQL validation engine too, as you saw in the DB2 Create View wizard.
The DB2 SQL Procedure wizard provides developers with the ability to browse parameters for the stored procedure they are creating (shown next) in the same manner as developers who browse columns when creating tables:
This helps minimize data type matching errors and promotes more productivity as developers can simply 'discover' the appropriate data type for their variables.
The remaining pages in this wizard are the same as those I have previously covered with the other wizards.
The DB2 SQL Function wizard
The DB2 SQL Function wizard is very similar to the DB2 SQL Procedure wizard and lets you create both scalar and table functions. There are some pages that are specific for functions. For example, you have to specify the return type of the value for a scalar function:
This wizard is smart enough to take a 'best guess' from the SQL statement it parses to suggest the return type required for the function you are creating. The remaining pages in this wizard are similar to those in the DB2 SQL Procedure wizard.
The DB2 Create Index wizard
The DB2 Create Index wizard lets you quickly create RID-based indexes on your tables. DB2 UDB also supports other types of indexes such as dynamic bitmap indexes (which are created automatically when needed), grid indexes, and block indexes.
You can see in the following figure that you can specify the schema, the name of the index, the table space in which it should reside, a comment that describes the index, whether the index will be used to enforce unique rows in the data, and the table on which you want to create the index:
This wizard also lets you select the column on which you want the index keys to be created, and the ordering of the RIDs for the index (ascending or descending):
The remaining pages in this wizard are the same as those you have seen in the other wizards.
The DB2 Create Trigger wizard
The DB2 Create Trigger wizard lets you create BEFORE, AFTER, and INSTEAD OF triggers. This wizard has all kinds of pages that accelerate the time it takes to build DB2 UDB triggers.
The remaining details in this wizard are part of your homework assignment! I want you to download a free copy of DB2 UDB (more on that in a bit) and try it out for yourself.
The DB2 Data Adapter wizard
As previously mentioned, I will cover data adapters in a follow-on article, but for completeness, I wanted to at least mention them here. Data adapters in DB2 UDB can be used to accelerate the time it takes to develop applications, enforce best practices, and even expose your routines as IIS Web methods or DB2 UDB Web services with a simple click of a button. Sounds interesting doesn't it. It is - but you will have to wait for those details.
Wrapping it all up
In this article, I hope you have seen that the DB2 UDB add-in for VS.NET goes beyond simple explorer features. It also includes a set of rich wizards that truly make working with a DB2 UDB environment transparent for .NET developers. These wizards are yet another unique and rich feature that assists .NET developers in DB2 UDB environments.
Are you ready to try it out for yourself? Get a free trial copy of a DB2 UDB for Windows server at: www.ibm.com/software/data/db2/udb/support/downloadv8.html. You can also get a free copy of DB2 UDB Personal Edition for development purposes at: http://www14.software.ibm.com/webapp/download/search.jsp?go=y&rs=db2udbpde.
About the Author
Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than nine years of experience with DB2 products and has written numerous magazine articles and books about it. Paul has co-authored the books: 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). Currently he is writing a book on the Apache Derby/IBM Derby database. You can reach him at: email@example.com.
IBM, Clear Case, DB2, DB2 Universal Database, Rational, and z/OS 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.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Linux is a trademark of Linus Torvalds in the United States, other countries, or both.
Other company, product, and service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2005. All rights reserved.
The opinions, solutions, and advice in this article are from the author's 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 author's knowledge at the time of writing.