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:

The object itself (object type, label, etc.) with info supplied from the SYSTABLES catalog view.
The object’s columns based on info drawn from the SYSCOLUMNS2 catalog view.
Other objects that depend on the selected object. This only includes database references that can be found in the system catalogs. Code dependencies are not listed here. This result set is based primarily on the QADBFDEP system cross reference file and the SYSINDEXES catalog view. Key column information is also shown.
Check, unique, and primary key constraints on the object based on the SYSCST and SYSCHKCST catalog views.
Foreign key constraints defined on the object based on the SYSCST and SYSREFCST catalog views. Only the child-parent or “outgoing” relationships from the requested table are shown. “Incoming” relationships from other tables to the requested table are not shown.
The triggers defined on the object based on the SYSTRIGGERS catalog view.

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.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot

Tags:

CYBER-ATTACKS ON THE RISE. PROTECT WITH THE TRIPLE PLAY.

COVID-19 has not only caused a global pandemic, but has sparked a “cyber pandemic” as well.

“Cybersecurity experts predict that in 2021, there will be a cyber-attack incident every 11 seconds. This is nearly twice what it was in 2019 (every 19 seconds), and four times the rate five years ago (every 40 seconds in 2016). It is expected that cybercrime will cost the global economy $6.1 trillion annually, making it the third-largest economy in the world, right behind those of the United States and China.”¹

Protecting an organization’s data is not a single-faceted approach, and companies need to do everything they can to both proactively prevent an attempted attack and reactively respond to a successful attack.

UCG Technologies’ VAULT400 subscription defends IBM i and Intel systems against cyber-attacks through comprehensive protection with the Triple Play Protection – Cloud Backup, DRaaS, & Enterprise Cybersecurity Training.

Cyber-attacks become more sophisticated every day. The dramatic rise of the remote workforce has accelerated this trend as cyber criminals aggressively target company employees with online social engineering attacks. It is crucial that employees have proper training on what NOT to click on. Cyber threats and social engineering are constantly evolving and UCG’s Enterprise Cybersecurity Training (powered by KnowBe4) is designed to educate employees on the current cutting-edge cyber-attacks and how to reduce and eliminate them.

A company is only as strong as its weakest link and prevention is just part of the story. Organizations need to have a quick response and actionable plan to implement should their data become compromised. This is the role of cloud backup and disaster-recovery-as-a-service (DRaaS).

Data is a company’s most valuable asset. UCG’s VAULT400 Cloud Backup provides 256-bit encrypted backups to two (2) remote locations for safe retrieval should a cyber-attack occur. This is a necessary component of any protection strategy. Whether a single click on a malicious link brings down the Windows environment or an infected SQL server feeds the IBM i, once the data is compromised, there is no going back unless you have your data readily available.

Recovery is not a trivial task, especially when you factor in the time sensitive nature of restoring from an active attack. This leads to the third play of the Triple Play Protection – DRaaS. Companies have myriad concerns once an attack is realized and a managed service disaster recovery allows employees to keep focus on running the business in a crisis state.

The combination of training employees with secure backup and disaster recovery offers companies the best chance at avoiding financial disruption in an age of stronger, more frequent cyber-attacks.

Reach out to UCG Technologies to discuss your company’s security needs and develop a data protection plan that fits you best.

ucgtechnologies.com/triple-play

800.211.8798 | info@ucgtechnologies.com

^{1. https://theconversation.com/cyberattacks-are-on-the-rise-amid-work-from-home-how-to-protect-your-business-151268}

Unions Criticize IBM’s Earning Per Share Focus IBM i 7.2 Available May 2

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search