Hi Folks,
Thanks, good notes (placed at bottom).
Ok, in review, we start with some Query-style tools in the
inexpensive ballpark based on a more generic database history, than iSeries.
JasperReports
DbVisualizer
AquaData Studio
All have solid appearance, with Jasper having the greatest mention here.
A comparison of the three tools would be fascinating.
In each case I would be curious if there are any "gotchas" in trying
to access QS36F data, after you make up some IDDU or DDS or special
source representation. If the product wants the "true" DB2 database,
then I could compile and copy files over to DB2 for testing purposes.
Our Company setup
15-20 users, one or two users who would use query function, plus
possibly the programmer to design a pre-canned query if the product
supports this nicely. If the product is the best, that would be an
incentive to accelerate a bit of modernization (at least to the level
of bringing in the data, however I have little interest in OCL--->CL
conversion, perhaps sparking a different thread. btw, we are on 7.1
on the P05, again with only moderate interest of moving to a power I
right now (maybe in a year or so). Not in a rush to buy a twinax
adapter or lose my I-O terminals and Epsom DFX printers. A bit
skeptical about speed gains and pricing data. Very happy with IBM
service on our P05. "We will be in tomorrow, your
battery/workstation-controller/doohickey is beginning to make flakey
noises". Rather amazing. Oh, we are planning on upgrading our one
aged network server in the weeks ahead to a new Dell.
===================================
JasperReports - Jasper Server
http://www.jaspersoft.com/editions
JasperForge - with forums
http://jasperforge.org/
Pete Helgren
http://www.opensource4i.com/
http://www.petesworkshop.com/
===================================
DbVisualizer
http://www.dbvis.com/
Security and Connectivity to iSeries/AS400 DB2 UDB - from forum
http://www.dbvis.com/forum/thread.jspa?messageID=4739ኃ
Discover the DBVisualizer
November 17, 2004 - Kevin Vandever
http://www.itjungle.com/fhg/fhg111704-story01.html
===================================
Aqua Data Studio from Aquafold
http://www.aquafold.com/
AquaFold Adds DB2/400 Support to Database Tool
October 21, 2008 - Alex Woodie
http://www.itjungle.com/fhs/fhs102108-story02.html
2.7.5 DB2 iSeries JDBC Drivers
http://docs.aquafold.com/ads/7.0/AAD1EC92-9800-3780-7F9A-3506A944D47C.html
Aqua Data Studio - DB2 iSeries Client
http://www.aquafold.com/dbspecific/db2_iseries_client.html
Forums
https://www.aquaclusters.com/app/home/project/public/aquadatastudio/discussion
===================================
Then we have two tools that have a longer-range iSeries pedigree.
SEQUEL (owned by Help Systems)
NGS-IQ
SEQUEL - I simply do not know the price range tonight, which is
usually a sign of a fair expense.
NGS - got a note in email - and they say they are in the process of
addressing the simpler type of environment, like ours.
We will see if that makes it affordable in the mix in the weeks ahead.
====================================
SEQUEL - Viewpoint
http://www.sequel-software.com/products/sequel
Help Systems
http://www.helpsystems.com/
====================================
New Generation Software
NGS-IQ
http://www.ngsi.com/company/ngsiq.html
===================================
There is always the possibility of using the official IBM products.
Web Query from IBM
Query/400
===================================
Since IBM's Rational is based on Eclipse, the BIRT tool is in here somewhere.
This may be most appropriate for those using Eclipse.
Maybe an essay on the relationships of
Eclipse-BIRT-Actuate-Rational-IBM would be helpful.
Eclipse
http://www.eclipse.org/
Using BIRT on the iSeries (Formerly IBM AS/400)
http://birtworld.blogspot.com/2005/10/using-birt-on-iseries.html
Actuate
http://www.actuate.com/info/ppcgoogle-birtdemo/?gclid=CNapr_yw2a0CFYbe4AodzTXBkg
Although it looks like Actuate-BIRT might belong directly with
Jasper-DbVisualizer and Aquafold
===================================
There are a number of products that can work with an ODBC database
that simply do not have much
in the way of specific references and articles in the iSeries world.
Crystal Reports
Using Crystal Reports against iSeries databases
http://search400.techtarget.com/answer/Using-Crystal-Reports-against-iSeries-databases
Finding your Libraries and Tables with Crystal Reports
http://www.sqlthing.com/resources/FindTablesinCrystalReports.html
The server version is $$, there is a client package that may be in
the ballpark.
===================================
SQL Reporting Services - Microsoft
"Reporting Services is bundled with SQL Server"
http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/reporting-services.aspx
===================================
(add to list here - some may be on this page of interest)
Data Analyst Toolkit - Metadata
http://en.wikipedia.org/wiki/Comparison_of_database_tools
===================================
This next product is more about simply knowing the metadata structure
and statistics
SQLThing Enterprise Edition
http://www.sqlthing.com/
===================================
More Notes
mrc - m-power
http://www.mrc-productivity.com/
is out of the price range, as more of a development product
BCD has a program called Clover, personally I simply do not deal with
BCD products.
http://www.bcdsoftware.com/iseries400solutions/clover/
===================================
Development Tools that have Query Functions
various PC products like Access have experience communicating with the iSeries.
WinDev (uses EasyCom connection) and Magic (EasyCom) and Clarion also
come to mind,
These are designed more as development tools than inquiry.
In our shop we are beginning to work with Magic, however they are not
a super-Query oriented tool,
so that the need for a nice tool on the execs desk remains.
===================================
Your experiences, notes and sharing about the best query methods appreciated.
For my own roadmap, I am getting an idea about how to move ahead,
with queries planned to some vendors and users.
I will give an initial edge to any product that is QS36F happy.
Steven Spencer
Queens, NY
347 218-3306
========================================================================================
Peter Helgren
Depends on what you plan to do report writing-wize. I'd take a very
close look at Jasper Reports/Jasper Server. It installs easily on
IBM i 6.1 or 7.1. It has a great WYSIWYI report writing tool. It
will talk to DB2 on i and has a great report server component that
allows you to deploy and manage reports for your users using a web
interface. Best of all it is free (Community Edition) and has a
commercial version with support if you are so interested. If you
are kicking tires on report writing tools of IBM i, you should take
a look and give it a try (you might have to invest an hour of your
time to install the compoents and deploy your first test
report). Very easy. If you are interested I also wrote some RPG
wrappers that allow you to run Jasper reports from an RPG/5250 program.
-Tom Stieger
Pete, do you have experience running the JasperServer on the IBM
i? We have it running on another server using JDBC to connect to
the DB2 databases. I have to agree that it is an excellent report
writing tool and I have been using it (Community Edition)
successfully for over a year.
Pete Helgren
It's what I do for some of my customers. I also have presented at
Common and done a webinar. Working on a magazine article on it as well....
Rob Berendt
I have to say that Query/400 was a great tool. We had PC users
uploading their data to their AS400 just so they could use Query/400
against it. Executives usually had no trouble figuring it out. I've
looked at STRQM, which is part of 57##-ST1, but I am more used to
column sizing and reformatting in WRKQRY. I do prefer using the SQL of
STRQM though. Most of the GUI products have a long and complicated
setup process you have to run through. The hardest part for most
people using a query tool is knowing what to query. For example,
joining 5 files together, finding out there's no on hand balance
column and you have to calculate that, and cryptic column
names. All of which could be fixed by some decent views or join
logical files. I prefer a view over a join logical file because
I'm getting more used to the syntax and find it easier. Also, it
allows for expansion by being able to include various sql scalar
functions and even my own user defined functions. Many of the GUI
tools setup is to have you redefine
your user data into metadata. Which basically is like creating the
view under the covers.
Paul Nelson
> <<Most of the GUI products have a long and complicated setup
process you have to run through.>>
> Not SEQUEL, and its associated GUI tool called Viewpoint.
> Joe Pluta
> I'll chime in on JasperReports. We run the community edition all
day every day and we love it. We run the server on the i and created
a custom front end that will generate PDFs and Excel and we simply
add whatever else we need. RPG programmers make a few calls to an
API to set runtime values and away they go.Since it's Java, Jasper
runs best when you stick it in its own subsystem with its own
memory. Don't let it swap with normal ILE load. Other than that,
it works great. One thing I have noticed is that they are very
aggressive on their tooling - it sometimes changes pretty
substantially from major release to major release. They're nearly
always good changes, but it can take some retraining.
> Vern
> I'm curious about your comment on column sizing and reformatting -
I find it is all there in STRQM, too - and that there are even better
formatting options there - the one I like most is wrapping within a
column. I assume you know about using F13 to switch between the SQL
statement (in SQL mode) and form definition - the latter gives you
all the reformatting you could want, I think. I think QM is one of
the best-kept secrets on the i - and it goes beyond reporting to
doing almost anything you want with SQL. Query for i and Web Query
are both limited to reporting only, so far as I have heard.
=====================
Steven Spencer
So, what if you are using no tool so far ? And would like one,
preferably one that is visual and easy for an executive or two.
What are the alternatives ?. 5733-QU2 and WebSphere ? (I'll have to
check if we own this now, we are on 7.1, if not, then the licensing
fee). Programs like DbVisualizer, Aquafold ? (They were not originally
designed for the iSeries and DbVisualizer, at least does not
have official support.) More long-term iSeries utilities ? Which
tend to be more pricey.
What are good Query tools in the under-$2500-or-so arena ? (We are
running a P05, note that we also have the data in QS36F, although
it could be moved to the native database.)
midrange.com
