SQL Query And Report Tool Gets The ProData Treatment
June 1, 2015 Dan Burger
SQL/Pro 5.0 is the latest in a long line of hard-working IBM midrange computing utilities produced and perfected by ProData Computer Services. Data access, data integration, and the DB2 relational database are ProData’s specialty. The key to the IBM i platform is its database and how developers create applications that ride on top of it. And when it comes to programmer productivity, the Omaha-based tech company gets it. Any database-related development work is going to involve SQL.
Years ago, when IBM i was known as OS/400, SQL was an unwelcomed stranger. It’s funny how things have changed for those who learned to take advantage of SQL’s ability to combine and extract records from databases. It’s true what the SQL advocates say: Programming with SQL is faster and easier than traditional methods. Taking advantage of SQL is one of ProData’s pursuits. SQL/Pro 5.0 is an example of what SQL can accomplish.
Anytime there’s a conversation about DB2 for i and SQL, performance is going to be a big part of the discussion. Information retrieval gets a lot of attention. A demo of SQL/Pro gives you a quick lesson in interactive performance. But SQL/Pro is not all about high performance information retrieval. In addition to more muscle, ProData built in useful features such as SQL statement prompting and the capability for statements to be executed in batch or interactively. Output can be directed to a display, printer file or database file.
There’s also an enhanced history log that keeps a record of every statement executed. The log allows users to quickly retrieve statements to rerun and to modify or create repeatable deployments. The browsing display feature shows the first 65 characters of the statement. The history log can also be used to create subsets with user defined filters for advanced searching.
And on the administration side–where security should always be a top concern–there are controls of SQL/Pro user and group authorities that limit the capabilities for SQL statements, services and commitment control.
The Save Queries feature allows users to name and save any SQL statement. Public access can be indicated for any saved queries making it easy to select the users that can use, change, or delete queries.
Queries can be saved directly from the interactive entry screen or indirectly through the history screen. Once saved, users can select and run a saved query from the Work With Saved Queries screen interactively or via a job queue. The screen shows all the saved queries alphabetically ordered in a subfile. A special command also permits users to run a saved query from a CL program.
The heart of SQL/PRO is the interactive SQL statement entry screen. This is where SQL statements are keyed into the system using insert, split, copy, delete and split functions. Statements can run interactively or in batch from this screen.
SQL statements require field names, which could become cumbersome except that SQL/Pro has included a function key that displays the field listing of any file on the user’s system.
Report formatting when output is directed to a printer is another SQL/Pro capability. Report formatting criteria is submitted through a series of prompt screens that are automatically linked to the current SQL SELECT statement. By the way, the objects created by SQL/Pro are the same objects used by IBM’s SQL/400 Query Manager. This comes in handy for users of SQL/400.
Product development at ProData follows a tried and true formula: Create a productivity tool that is useful in your own development work and then fine tune it with feedback from the IBM i community.
“We listen to our customers and give them the features they want,” says Allen Hartley, owner and CEO of ProData.
SQL/Pro 5.0 is designed to complement other ProData productivity utilities for the IBM i and can be packaged with ProData’s many other programmer utilities. One of those utilities, SQL Audit, is now built in to SQL/Pro. SQL/Audit tracks and monitors modifications–inserts, updates, and deletes–made to any IBM i database using SQL.
SQL/Pro 5.0 supports IBM V5R4 and newer versions of the operating system.
ProData began software development for the IBM i in 1981 and has exceeded 20,000 utility installations worldwide. The company is best known for its DBU database utility that provides access to data on DB2, SQL Server, Oracle, and other databases. DBU is a popular utility that makes it easy for programmers, administrators, and users to view and access any file in DB2/400, without programming. The software is useful for a variety of tasks that require full access to field-level data within a database. It has green-screen and graphical interfaces and a plug-in for IBM’s Rational Developer for IBM i development tool.