Emulate sp_Help In DB2 For i
April 30, 2014 Michael Sansoterra
|
Note: The code accompanying this article is available for download here. Every once in a while I get an email from an SQL developer asking something like “What tool do you use to develop SQL routines on IBM i?” Astonished is the perfect verb to describe the reaction when I reply that I normally use STRSQL or Run SQL Scripts (in System i Navigator). Equally apt descriptions are dumbfounded, perplexed, baffled, and flummoxed. If you’ve ever used SQL developer tools on other platforms, such as Microsoft’s SQL Server Management Studio, then the astonishment is quite understandable. Run SQL Scripts has many good features, but it’s still comparatively cumbersome to develop with. If I were to list the many productivity aids I have in SQL Server Management Studio that I don’t have on the IBM i, we’d be here for quite some time. However, instead of bellyaching about lacking features, I found it’s generally pretty easy to create some productivity utilities to aid in the development process. While they’re not full blown GUI features, they can nevertheless be helpful. One productivity tool available with SQL Server is a system stored procedure called sp_HELP. When you execute sp_HELP [table name] in SQL Server management studio, SQL Server spits back several result sets detailing information about the table, including column names, the indexes built on the table, constraints, etc. Just typing this simple command often beats navigating through the GUI object tree to find the same information, especially with large databases. So I built a DB2 for i stored procedure called sp_HELP that does much the same thing as its SQL Server counterpart. However, while sp_HELP in SQL Server returns information about most database objects, the version I’ve created only works with tables, views, and aliases. The code for the DB2 for i sp_HELP procedure can be downloadedhere. There are also two helper functions included in the source that are required to collect the dependency and key column information. Make sure all three objects are created! Note that the script assumes the functions and procedure will be built in QGPL, but you can modify it to be installed into any library you’d like. While the code was built on IBM i 7.1 with the latest database group PTF installed, the code for sp_HELP can be ported to an earlier version of IBM i as long as you’re willing to remove some features or make a few code tweaks. For example, the SYSTRIGGERS table has some new columns in IBM i 7.1 that are unavailable in prior versions of the OS. I tried to note where these require “6.1” or “7.1” in specific places in the source code wherever I encountered them so that changes can be made where appropriate. The DB2 for i sp_HELP procedure accepts two parameters: a schema name; and an object name, where the object can be an alias, view, table, physical file, logical file, or materialized query table. The schema and object names can be specified as either long or short (system) names. The procedure is used as follows:
CALL QGPL.sp_Help ('ADVENTUREWORKS','SALESHEADER')
Incidentally, the CALL keyword, the parenthesis and the quotes are required in DB2 for i, so it’s more typing than you’d do with SQL Server. The procedure returns up to six result sets with information about:
Take a look at this PDF that shows sample output for all six result sets. The constraint and dependency result sets are only shown when a table–or alias based on a table–is requested. When a view is requested, sp_HELP returns just three result sets: object information, columns, and triggers. The result sets returned show pretty much the same information you’d find with the System i Navigator GUI or by browsing through the DB2 catalog views. However, you can enhance sp_HELP to show you whatever is most important for your environment. sp_HELP only works with GUI-based tools such as Run SQL Scripts because the green screen tool STRSQL doesn’t support showing result sets. When the RunSQL Scripts utility returns multiple result sets, each result set gets placed in its own tab, which has advantages and disadvantages. The disadvantage is when sp_HELP returns six result sets, it’s difficult to know which tab has the trigger info result set, which tab has the column information, etc. IBM should remedy this by making each tab have an ordinal label (or something) so that a user has an idea of how to identify the desired result set without fumbling around. In contrast, in SQL Server management studio all of the results are shown in the same window so it’s easy to see all of the information. The sp_HELP procedure for DB2 for i is a handy utility for quickly viewing information about a database data object without having to maneuver through a GUI tree to find the required information. While it doesn’t have all of the features of the SQL Server version, it can be a real help for developers who need to keep on coding with access to relevant database object information at their fingertips. Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.
|
