Taming SQL Queries with a CozTool

October 14, 2015 Alex Woodie

Bob Cozzi has done a lot with the IBM midrange server platform–probably more than most, including founding the platforms’ first publication and presiding over its largest user group at one point. But Cozzi may have top it all with his latest creation: A handy little database utility called SQL Query File that he developed and sells through his software company, CozTools.

SQL Query File is a green-screen tool designed to let IBM i professionals quickly and easily query their DB2 for i database using standard SQL, right from the command line. You can cut the SQL you previously used with Query/400 or third-party tools, and paste it right in Cozzi’s tool, where it might run faster.

But just issuing SQL queries from the command line for the database’s SQL Query Engine (SQE) itself isn’t enough to write home about–there are lots of ways to do that. What sets SQL Query File apart is its output capability. Once users have run their query, they can have the output of that query appear in an variety of formats, including PDF, HTML, text, CSV, Excel, JSON, and XML–not to mention putting the results in a database file or spilling it out to the screen or a spool file.

CozTools’ SQL Query File outputs the results of DB2 for i queries in a variety of handy formats.

Cozzi offers the following explanation for the significance of this: “What good is a tool if every time you need an alternate output format, you have to buy another tool, purchase and optional plug-in, or rewrite the request so it works with some other unrelated component?” the company writes. “With SQL Query File, you simply specify the output format on the OUTPUT parameter and your existing industry standard SQL statement is used to produce the data in the format you request.”

Nothing is easier, says CozTool. While others require the user to learn HTML, JavaScript, or “a technology that’ll be gone next week,” SQL Query File does the work for you. What’s more, users can get “cool scrolling HTML tables” via the WEBSQL CGI program that’s included with SQL Query File. According to the company, this “allows users to run the same SQL statements from the Web, and automatically create an HTML scrolling table,” which some people call “subfiles for the Web.”

The SQL Query File tool can be used to generate ad hoc reports, perform data mining on the DB2 for i database, or execute data conversions, the vendor says. And the capability it provides the user to adjust the queries and tweak the parameters enable it to fill a number of other needs. User will no longer write a program to list the contents of a database file.

The software also includes the WRKQRYF (Work with File using SQL Query File) command, which lists the fields in the file and allows users to selectively build their SQL statements and view the results on the green screen or as printed output. When the user is done fiddling around, he can save the SQL statement and refer to it as often as needed.

The product is free for customers on i5/OS V5R4 and IBM i 6.1, but requires payment for IBM i 7.1 and IBM i 7.2. Pricing starts at $1,995 for up to a six-core machine, or $3,995 for an unlimited number of cores. It can also be licensed on a monthly basis, starting at $35 per month for one or two cores. For more information see www.sqlqueryfile.com/.

Tags:

Sponsored By
UNITED COMPUTER GROUP, INC.

UCG VAULT400 BaaS Clients have it ALL!

You can't say your data protection is complete until you have a disaster recovery plan that is itself complete and tested.

        • Secure cloud backup and disaster recovery
        • Hardware DR
        • Cyber security training
        • 19 data centers across the U.S. and Canada
        • 24 x 7 x 365 monitoring
        • Supports all platforms and databases
        • The latest in IBM Power Systems
        • An excellent client experience each and every time

HOW MUCH CONFIDENCE DO YOU HAVE IN YOUR CURRENT DATA BACKUP,
DR AND CYBER SECURITY PROGRAMS?

Consider these best practices when evaluating data backup, DR, and cyber security options:

1. RELIABILITY. Up to 71% of restores from tape contain failures. Best Practice: Use disk-to-disk technology for backups. With disk-to-disk technology, your backup data resides on disk drives, proven to be far more reliable than tapes. When your backup completes, you know the data is secure and accessible on the disk drive. With tapes you never really know if your data is usable until you try to restore it, at which point it's too late.

2. BREADTH OF OFFERING. Choice in product and service offerings meet your business' needs. Best Practice: Don't settle for less than what you need. Vendor offerings vary widely. Some are designed primarily for consumers and others for enterprise data centers. Choose a solution that scales (see scalability below), and offers the features you need to provide the level of service you expect. De-duplication and delta-block technologies will improve performance, reduce your data footprint and save you money. Find out if their de-duplication offering is at the file level or the block level. Make sure the solution can back up servers, PCs, and laptops as well your applications.

3. SECURITY. 60% of organizations using tapes don't encrypt their backups. Best Practice: End-to-end encryption with no "back door." Using encryption with tape makes backups run slowly and often takes too long to fit within a backup window. As a result, most people simply turn encryption off, creating a security risk. Even with the physical safety of disk-to-disk backup, encryption is essential. Look for 256-bit AES. Find a solution that encrypts your data during transmission and storage. Make certain there isn't a "back door" that would let someone else view your data.

4. CYBER SECURITY. More than 80% of U.S. companies have been successfully hacked, according to a recent Duke University/CFO Magazine Global Business Outlook Survey. Best Practice: Regular cyber security training and phishing tests for all employees using company email are essential to your organization. Your end-users are the weak link in your network security. Today, your employees are frequently exposed to advanced phishing attacks. Trend Micro reported that 91% of successful data breaches started with a spear-phishing attack. Be sure your vendor of choice includes cyber security training as part of their backup and DR package.

5. Accessibility. Companies waste thousands of hours waiting on tapes. Best Practice: Ensure that you can get your data back with minimal delay. You should have direct access to your backups, with no time spent on physical transport (no trucks, no warehouses). Your restores should take minutes, not hours or days. Set yourself up to work with your data, not wait for it. Make sure your solution provider can meet your Return-to-Operations (RTO) and Recovery Point Objectives (RPO) which determine how quickly you can recover your data and maintain business continuity. Inquire about onsite and offsite replication that provide both improved performance and a solid disaster recovery strategy.

6. Scalability. Some backup systems can't scale readily. Best Practice: Invest in a data protection architecture that can grow with your business. You should be able to back up your data no matter how large it grows. Starting small? Look for an option that handles your backups automatically. Then, as you grow, gives you tools to manage complex environments. Look for "changes-only" and compression technologies to speed backups and save space. And insist on bandwidth throttling to balance traffic and ensure network availability for your other business applications. Make sure that their solution offerings rely on common technology to scale easily as your business--and data--grow.

7. Cost-effectiveness. Companies lose an average of $84,000 for every hour of lost activity. Best Practice: Calculate the true total cost of tape-based back up. When you do the math, the dollars make sense: Go with disk-to-disk. Unlike tape, there are close to zero handling costs-no rush deliveries, loading, accessing, locating, or repeated steps. And there's one benefit you can't factor directly: Reputation. Reliability and security can make an incalculable difference with just one avoided breach or failure.

8. Compliance. Most companies have problems satisfying privacy, security, and data retention regulations. Best Practice: Choose a data protection partner who has deep know-how about compliance, and the technology to ensure it.

9. Disaster Recovery. Most companies lack a comprehensive, tested plan for disasters. Best Practice: Find a vendor that delivers a complete DR solution. You can't say your data protection is complete until you have a disaster recovery plan that is itself complete and tested. Your backup vendor should have both the product mix and professional services team to help you prepare for a worst-case scenario. Make sure they can help configure your backups so you rebound quickly. Best bet: A vendor who can train you to deal with disasters confidently, based on your company's actual configuration.

10. Ease-of-Use. Some companies don't--or can't--manage their backups from one place. Best Practice: Get control and reporting you can use anywhere, with ease. Managing your backup environment should be simple, and the software you use should eliminate any guesswork that could lead to lost data. You should know at all times if your data is protected across your entire network-including remote offices-by simply looking at a dashboard. The software should be simple to configure using wizards, yet powerful enough to meet your specific needs with customizable views, job propagation, and roles-based security.

11. Operating System and Platform Support. Most backup vendors support a limited range of OS, server types, and applications. Best Practice: Look for broad and deep technology that supports your complete environment. Your backup solution should accommodate your environment, not vice versa. Demand a single solution to protect your laptops, desktops, and servers regardless of the platform and applications they're running. Beyond the broad claims, check the fine print, and the level of protection offered for applications such as Exchange. For example, can they restore individual mail messages or contacts, and can they support Exchange running on a Microsoft Cluster?

12. Customer Support. Backup vendors' product support varies widely. Best Practice: Find a vendor whose support is passionate, maybe even slightly obsessed. Customer support should be one of your vendor's main selling points. You shouldn't have to wonder if they'll be there to help when you need them most. Do they offer phone support or email only, and who exactly are you talking to when you call that 800 number? Find a vendor that will treat your data as if it were their own.

13. Reputation. Does your backup vendor have a quality reputation and the financial resources to stay in business for the long haul? Best Practice: Find a vendor with strong financial backing and customer references. There are a lot of vendors that have come and gone. When you consider a service provider, look for one that has strong financial backing, a solid business plan and the ability to be in business as long as your data needs to be stored. Ask for customer references and case studies as their customers are the best validation you can get.

DOWNLOAD A PDF OF THIS ARTICLE:
BEST PRACTICES FOR BACKUP AND RECOVERY.

Connect with us on our social networks!

You can find us on Google+, LinkedIn, Twitter, Facebook, and YouTube.

For more information or to schedule a demonstration on UCG's products and services,
please visit us at ucgrp.com/Talk2Us or call 800.211.8798.

Browse our Resource Libraries for important information and downloads!
UCG VAULT400 BaaS Resource Library
UCG S2K Enterprise Management Software (EMS) Resource Library

ucgrp.com • vault400.com

Technology customized to your business, today and in the future.