Information Systems:From Query/400 to SQL: A discussion on queries
Overview
This page follows the theme of commenting on the state of the system. In particular, queries are discussed here. Refer to the Database Query category for a more encompassing discussion of queries. This article merely pertains to observations on current practices and musings on modernization.
Notes
One of the most confusing things when exploring the topic of IBM i queries is that there seem to be 3 different (pre-installed) software packages involved that are related to queries: Query/400, Query Manager, and SQL for DB2. The inclination would be to treat them all as mutually exclusive, but they are very much related. Here are some points that might help explain the relationship between them:
- Query/400 (RUNQRY,WRKQRY) is a query design program - it is a means of creating and running queries. The backend is SQL - that is, it runs queries using SQL - but it does not require knowledge of the SQL language to design the query. The intent was presumably to make query design easier for traditional programmers or system admins by avoiding the requirement to learn another language (SQL). Sheila (and probably Pat) were power-users of Query/400, which is why most of the queries run are based on Query/400 query definitions. Fun fact, ASW Query Manager (not the same as Query Manager) is pretty much just a customized interface for running Query/400 queries.
- Query Manager (STRQM,STRQMQRY) is a weird product by IBM. Like Query/400 and as indicated by its name, it is software to manage (create, run etc.) queries. However, it appears to have been designed as a hybrid solution for designing, running and managing queries that accommodates the Query/400 and SQL methods of query creation (the area that Query/400 masks from its users). It is possible to start creating a query using a Query/400-like interface and convert it to SQL, and vice versa. Needless to say, unlike Query/400, Query Manager also displays and allows you to edit the behind-the-scene SQL statement of the query (if designed using the Query/400-like interface). Despite its advanced capabilities, none of our queries are QM queries. In fact, QM probably only seems weird because its lack of use in our system makes it appear redundant as a software.
- SQL for DB2 is not a query manager, but rather its the platform that allows SQL to function on the i. Everything that runs SQL (Query/400,Query Manager,SQLRPGLE programs etc.) can run because of this software package. The confusion here comes from the many meanings of SQL (it is both a DDL and DCL), but that topic is beyond the scope of this article.
Many menu options within Extensions (OMS) actually just run Query/400 queries - some display results directly on the screen, while other queries produce spool files for printing. For example, Accounting runs a query through the following menu option in extensions:
* 21 EDI Menu * 9 Supplier Invoices
The backend process triggered by this menu option (the basic flow of which exemplifies a practice widely used throughout the system where queries are called) can be described as follows:
- Menu option 9 calls EDINVSUPC (CLLE program).
- EDINVSUPC runs Query/400 query EDINVSUP1, outputting a temporary file in QTEMP called EDINVSUP1
- EDINVSUPC creates physical file EDINVSUP2 and copies results of EDINVSUP1 to EDINVSUP2, running object checks prior to operating on these files
- EDINVSUPC calls EDINVSUP (RPGLE program)
- EDINVSUP performs logic to set values on one of the fields.
- EDINVSUPC runs a second query, EDINVSUP2, that inputs EDINVSUP2 and outputs to a spool file for the user to view.
The necessary query logic (what the first query and RPG program do) can actually be achieved through a single SQL statement:
select doc#,supp#,po#,suppinv#,invdate,duedate,cshdiscduedate1 from (
select t03.ctidno as Doc#,t01.i1sean as Supp#,t01.i1porf as PO#,t01.i1vein as SuppInv#,t01.i1idat as InvDate,t01.i1dudt as DueDate,t01.i1dud1 as CshDiscDueDate1,
case
when t01.i1dud1 <= t01.i1dudt and i1dud1 <> '0' then t01.i1dud1
else t01.i1dudt
end as SortDate
from uwdaswprdd.edoinh1 as t01 join up1480bfva.sroltai as t02 on i1vein=ctvein join up1480bfva.srolta as t03 on t02.ctrefx=t03.ctrefx
where ctdoty='EIN'
order by t01.i1dud1,t01.i1dudt
) as result order by SortDate
There are many instances of these types of queries (Query/400 queries within CL), and while there is little benefit to rewriting them for the sake of "modernizing", it is important to know that these queries can be converted and programmed to be run in other ways (Infonet, Query Manager, SQLRPGLE), as the logic is SQL-based.