ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Looking for Query/400 Alternative?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Looking for Query/400 Alternative?

    I'm currently in a very "traditional" and small IBM i/IT shop (6 person crew). While its been bitter-sweet to have some of our IT staff for 30+ years, they will soon be leaving the workforce for retirement, etc; thus my responsibilities will be more focused on the IBM i. We still rely heavily on Query/400 for report generation. With a prior/strong SQL background, I find Query/400 to be painful and limiting; often avoiding it. Our company (as a whole) is slowly shifting to where employees are wanting data/reports beyond a green bar print out; but some of our staff have trouble seeing past a green screen/printed reports. I store my queries in .sql files on the IFS (via RDI). The majority of these manual queries require some sort of modifications when the data/report is updated. I will typically create an SQLRPGLE program, if scheduled runs or automation is needed.

    Due to issues, I have not pushed this question internally; but moving forward, I know there has to be a better way to manage queries/reports, etc. The two of us that use SQL share the knowledge/files, but even with a good file structure and naming conventions, there is sometimes struggle to find or recall the right file (similar problem to finding the right query with query/400). I've partly looked at Web Query for i, but there seems to be a lot of negative feedback regarding the product and pricing. There are some other products that I've used in the past, but require additional non-IBM servers; I would really like to keep everything on the IBM i. I have considered looking at a web-based reporting/dashboard engine that could run on top of Zend, that could design the reports or filter saved queries; allowing the users to run/export on demand. I've also thought it might even be possible to write a modular RPG program the individual queries are stored in unique modules, and one module would be used to puke the report or export the data to PDF/CSV/XLS, etc. I'm not sure if that's possible but about to explore that option.

    That being said, I know others have went down this route. If you have any recommendations or guidance on what to do or NOT do, I (and probably others) would like to listen.

    Thanks,
    JA




  • #2
    We are smaller yet... We currently use WebQuery (since about 2015). It's a love-hate relationship. It is a very robust product that, once setup, works pretty well. There are several people in IBM that are very intelligent and proficient with the product. However, technical support is well-below what you would expect from IBM. I have very complicated SQL views over our legacy database to provide the necessary metadata for users to author reports.

    We also have another product called Valence from CNX Corp. It allows you to create queries, applications, etc.

    I guess it depends upon who will be creating the reports and what type of output you want (traditional reports, Excel, dashboards, etc.)

    I would be glad to discuss our experiences offline - send me a PM with your number.

    Greg

    Comment


    • #3
      Greg,

      Thanks for the reply. You experiencing some of the things that concern me about WebQuery.

      I just wonder if instead of focusing on the reporting aspect, we take a step back and look at 30,000 ft view. Maybe the answer is ditching WRKQRY and WebQuery all together. Focus our efforts on building DB views and stored procedures for meta-data and logic. Then use an application like CNX/Valence, Fresche, ARCAD or Profound Logic for interface design and reporting/exporting; with less reliance on CL and RPG.

      I may be over thinking this and worrying about the long-term impact too much; given that I currently have little influence on such a decision. I just cannot help to think that the decisions being made by others today are going to be my problems tomorrow.

      JA

      Comment


      • #4
        Originally posted by jason.aleski View Post
        Greg,

        Thanks for the reply. You experiencing some of the things that concern me about WebQuery.

        I just wonder if instead of focusing on the reporting aspect, we take a step back and look at 30,000 ft view. Maybe the answer is ditching WRKQRY and WebQuery all together. Focus our efforts on building DB views and stored procedures for meta-data and logic. Then use an application like CNX/Valence, Fresche, ARCAD or Profound Logic for interface design and reporting/exporting; with less reliance on CL and RPG.

        I may be over thinking this and worrying about the long-term impact too much; given that I currently have little influence on such a decision. I just cannot help to think that the decisions being made by others today are going to be my problems tomorrow.

        JA
        I literally laughed out loud. Fortunately at my company, I answer to the owners - so i typically have the final say. I cannot say enough about the folks at CNX - they are top notch and are always available to help. With my data I had to "build up" SQL views before I could even begin with the metadata in WebQuery. The best thing about WQ approach is that once you have developed the metadata (great care should be taken here), your users can run with it - creating their own reports. Don't take my criticism wrong - WebQuery is not all bad.

        We have barely scratched the surface with it. We mainly use the BI Portal to access reports both internally and externally (our clients). We also use the REST API feature to show several KPI reports on TV screens in our warehouses. They simply open the browser and there's the report - it auto-refreshes every 15 minutes.

        Over the last 20+ years I've learned RPG (and ILE) on my own... since buying some of these new tools, I've learned quite a bit about SQL - so much so that I've incorporated it into most of RPG programs. Together, they are two very powerful tools. I would not completely abandon RPG for anything. SQL cannot do it all. If you're not exploring free-format RPG in RDi, you are missing out on some really powerful stuff - it should be relatively familiar with (younger) programmers that know other languages.

        No matter what direction you take, you'll likely start with the SQL views.

        Greg

        Comment

        Working...
        X