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.
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?
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.
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.
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.
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.
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.
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
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.
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.