October 2014

June 21, 2011

Query/400 Modernization

This week, Dawn planned to blog about the Print_Query_Definition stored procedure as a great way to help you manage your hundreds or possibly thousands of Query/400 objects. Now I can't do the topic any kind of justice like she can, but she recommends a great article we just posted by Gene Cobb.

"Sinking in a Query/400 Quagmire?" is a walkthrough of the database and query management problem and how this tool, introduced in V5R3, can help. It also provides some useful examples and offers up IBM Lab Services to help with your Query/400 modernization.

Dawn will be back next month but she's taking a short break for personal reasons.

Comments

IBM should be embarrased that the system has no simple, meaningful,workable Query Object Analysis tool and we have to get it from Lab Services (which I assume is chargeable).AND the SQL statement you offer is meaningless as it outputs to a spool file and can only do 1 query at a time.Pathetic. I love the platform but I sincerely believe that IBM is only marginally commited to the system i. Why can't it come with things that work SIMPLY and output NATIVELY to a usable and friendly format (Excel, PDF..). I can go on and on but it really is sad and painful to watch the slow demise of a beloved system.

Thanks for the feedback Harry. My apologies - I should have done a better job explaining and positioning this.

First off, the article was intended to inform readers about this stored procedure because most that I talk to are not even aware of it. Similar to some DSPxxxxx system commands (such as the DSPLIB command), there is no option to dump the data into a database file - only a spooled file. So like those commands if you wanted to be able to read that information using SQL or RPG, you would have to use something like the CPYSPLF command to get it there. Since the spooled file has a variety of information, you would also have to write some programs to extract, parse, and organize the data so that it can be easily harvested for some low level analysis. You can certainly do this, but obviously it takes time and the Lab Services group has already done this.

One of the many advantanges of having the Lab Services group around is that we (in the development lab) can and do sometimes bring their code into the OS to fill gaps. This might be one of those cases. In the meantime we are working with Lab Services to make this tool available at no charge. They are doing some additional enhancments and packaging work - so it should be avaiable soon. When its ready, I will post details here (as a new posted comment). It will likely be uploaded somewhere where it can be easily downloaded by you and others. But be warned, it will be "as is" code so there is no support nor guarantee that anything wrong will be fixed. But it should be a good tool to get you going.

However something I may not have made very clear in my article is that having this information in a db file is only a small step in the modernization process. As I mentioned in the article, our customers have many, many Query/400 objects out there (over 20,000 in one case). Many are still used and relevant - quite a few are not. Many are duplicates or very similar to other queries. Some were created in the 80's and no one has the foggiest idea what they are used for and if they still need to be kept around.

So let's say you had 3,000 Query/400 objects on your system and you use this tool to analyze things. What is your next step? You probably don't have time to write 3,000 equivalent Web Query reports - not would you want to as this would be quite unmanageable. You probably have neither the time nor inclination to really dig down and analyze and compare them to weed out the duplicates and non-essential ones. This is the real value of the Lab Services offering - to help you through this process and at the end of the day, produce a set of Web Query metadata, domains,folders, and reports that is organized and manageable. And maybe throw in a few fancy graphs and dashboards while they are at it!

I hope this helps. Again, I will post details here when the tool is ready to be downloaded.

Update to this discussion: The Query/400 Discovery tool is now available at no charge! More info below...

The DB2 Lab Services team has put together a utility based on the stored procedure Print_Query_Definition (described in the linked article) to extract information about Query/400 definitions into a set of tables that can then be analyzed to understand information about the ten's, or hundred's of query definitions built over time and present on your system. The Lab Services group use this utility as part of an overall "Query/400 modernization" services offering. Based on popular demand, we have decided to make this available to anyone.

The link returns an error: (I'm on Windows 7)
=====================================
Oops, that didn't work
Something went wrong - click the back button and try again. If this doesn't work report the problem to the support forum. If someone sent you this link, check to see if you've been granted permission.

IBM Systems Magazine is a trademark of International Business Machines Corporation. The editorial content of IBM Systems Magazine is placed on this website by MSP TechMedia under license from International Business Machines Corporation.