Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Recommendations for SQL Tools for IBM i
has no ratings.
Published: 29 Jan 2015
Revised: 04 Feb 2015 - 777 days ago
Last viewed on: 22 Mar 2017 (2221 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Recommendations for SQL Tools for IBM i Published by: Bob Cozzi on 29 Jan 2015 view comments(8)

I'm wondering what SQL tool people are using now on IBM i?

Are you using STRSQL or that thing that comes bundled in whatever Ops Nav/Navigator/iNav is called this week, or something else?

I find myself using our own in-house WRKSQLF/WRKQRYF and RUNSQLF/RUNQRYF for 80 percent of SQL needs, and STRSQL for quick and dirty things like one-off updating a file or similar task.

What are you using?

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 22 days 3 hours 59 minutes ago

I'm using HelpSystems SEQUEL and have been for over 25 years for both production code and ad hoc queries and updates.  It has saved me a ton of time, the most recent example being that I had to keep some tables on our system in sync with a MS SQL Server database.  All it required was a bit of setup and a couple of SQL statements to get the job done.

Posted by: starbuck5250
Premium member *
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 22 days 3 hours 8 minutes ago

SQL tool to do what?  I use a variety of things with SQL, primarily RPG.  For running !--script--s like CREATE TABLE, or for one-off queries, I use IBM System i Navigator for Windows' Run SQL !--script--s application.  I keep the !--script--s on the IFS so that all of us can use and reuse them.  Very handy when developing in the test environment because I just change the set schema command and can apply the !--script-- to the production side. 

iNav for Windows is at end of life at version 7.1 and is being superceded by Navigator for Web, which is shaping up to be a very nice tool to explore how the database is put together.  Executing SQL... meh, not ready yet. 

There are open source tools like SQuirreL which I use from time to time.

I've installed IBM's Data Studio into my RDi environment, and I'm still learning the ins and outs, but it's free and it's IBM, so there's that.

I also have a command I modified from a trade magazine decades ago that allows me to run any SQL statement (including SELECT) from the command line.  Handy to do quick looks into a table, but I also built it into several command line tools like DSPFLDVAL CUSTMAST CREDITCODE - this will give a list of credit code values and how many customers are assigned to each one.  Handy to work out what a table looks like from the inside.

Finally, I've been using Java on and off IBM i via JDBC / ODBC to connect across machines to SQL Server and MySQL.  The neat thing about that is that you can wrap the Java calls and use them in RPG programs.  See Scott Klement's JDBCR4 service program for an example.

I haven't used STRSQL in many years.

Posted by: Viking
Premium member *
CA
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 22 days 54 minutes ago

I'll sometimes use STRSQL for a quick SQL lookup.  But mainly I use Linoma's Surveyor tool.  Free for one user.  It replaces DBU, you can run SQLs, you can work with the IFS, etc.  Very useful for me.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 21 days 23 hours 26 minutes ago
Edited: Fri, 30 Jan, 2015 at 11:27:25 (782 days ago)

Thanks Viking. I didn't know the Surveyor tool has SQL/query ability. Typically Linoma stuff is slow particularly their Java-based stuff, so I tend to avoid it.

Buck, I think I wrote that "article from a magazine decades ago".  But your "to do what?" is a good question.

I'm using Query File SQL to create reports and embed SQL in CL. I also use a basic RPG EXECUTE IMMEDIATE wrapped in a CL command (like that one I published on this site recently and in Midrange magainze 25 years ago) to do pre-V7R1 RUNSQLSTMT stuff.

To grab data from a remote server, I also use Query File SQL's RDB parameter and it just works.

Since QF SQL already does so much, I'm really only looking for a GUI result set presentation. Not a Windows solution, but a GUI/Web resultset that can be viewed on virtually anything.

I'm going to have to look at the Web-based SQL thingy in Nav to see if that'll work for end-user GUI/Web result sets. Otherwise I may have to write my own and I really don't want to do that.

 

Posted by: starbuck5250
Premium member *
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 21 days 7 hours 50 minutes ago

Bob, it could very well be your original idea!  I had read the article and a few years later had a need to run dynamic SQL in a CLP so I recalled the general idea (QMQRY with generic replacement variables) and off I went.  Very clever and very useful, thanks for sharing with the community.

Navigator for Web is at http://my.system.ip:2060/webaccess/iWAHome  Check out the Database option; there's a lot of stuff in there.  Also, a security officer may need to adjust the permissions for specific users (Customize, Policies) but for power users this might be nice.  You can download as CSV, Excel or PDF.

Posted by: clbirk
Premium member *
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 21 days 25 minutes ago

I use strsql and occasionally wrkdbf. If I am after reports, etc. then we do it either in webquery or old query/400.

I do use occasionally the sql feature within inav. Unlike someone who mentioned they "save" their sql definitions, I don't, I can regenerate when I need them. And I don't do things like create table, etc. I simply right click in inav and do the option to create a new table.

do a few things with the ibm runsql command also.

 

chris

 

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 17 days 3 hours 38 minutes ago

I use AQT - Advanced Query Tool.  It is a nearly universal SQL client that comes with many pre-difined server definitions, including DB2/400.  It uses the Client Access ODBC driver nicely.  http://www.querytool.com/

It's pretty cheap and the support is great.  I use it also to connect to SQL Server and Oracle inside our firewall.  Written and supported by former IBMers.   Makes RDi and iNav SQL interfaces look childish.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Recommendations for SQL Tools for IBM i
Posted: 2 years 1 months 17 days 4 minutes ago
Edited: Wed, 04 Feb, 2015 at 09:46:02 (777 days ago)

Steve, when we were naming Query File, we originally came up with "Query Tool" but that was taking by AQT. Theirs looks like a client-only UI, but pretty cool. My Query File does effectively the same thing, but is IBM i (host) based only, hence green screen. I may start using AQT for GUI-based stuff.

Thanks.