Are you still running IBM i green screen queries? Managing all of those queries can be difficult, particularly if you’re dependent on a tool like Query/400.
Luckily, Sequel Data Access offers a better way to run your IBM i green screen queries. Creating queries is fast and you’ll even be able to convert existing queries from Query/400.
Compare Query/400 to Sequel Data Access >
Not sure how to convert queries with Sequel? There are two methods for importing and converting queries from Query/400. Use this as your step-by-step guide and get started converting your queries today.
1. The Green Screen Method for Converting Queries
The green screen method is the best way for technical users to convert multiple queries at once.
Step 1: use Sequel’s CVTQRY command from the IBM i command line. This is the only step.
Example: CVTQRY QRY(MYQRYLIB/*ALL) REPORT(*NONE) VIEW(MYSEQLIB/*QRY) SERVER(*LOCALSYS) SYNTAX(*SEQUEL)
CVTQRY QRY (MYQRYLIB/*ALL) is how you select which existing queries to convert. By specifying a location where all of your queries are housed, you’ll be able to convert them all simultaneously.
REPORT(*NONE) is where you specify whether or not you want a report to run on the queries you are importing. By using (*NONE), a Sequel report will not be created right off the bat, but you can easily create one later on.
Use the REPORT option to create a Sequel report, which is a spooled file report. The report will allow calculations, totals, subtotals, and page breaks. Your SQL statement will be embedded but still accessible within the report.
VIEW(MYSEQLIB/*QRY) creates one view—which is the Sequel term for query—for each query you are converting. The view will be named the same as your existing query. A Sequel view contains the definition to access your data. It is the basis for all other Sequel objects, like reports or tables.
You can also use this command to specify the library name when creating the new Sequel views. If you prefer, this library can even be the same as your query library.
SERVER(*LOCALSYS) is required for converting queries. This is how you define which server to keep the queries on. This is also how you define which query engine will be used—the Classic Query Engine or SQL Query Engine. There are two options for defining the server:
- Choose *SEQUEL to use the CQE
- Choose *LOCALSYS to use the SQE. Using the SQE is recommended for faster performance.
SYNTAX (*SEQUEL) is required for converting queries. This is how you define how the SQL syntax will be written. Again, you have two options for defining the syntax:
- *SEQUEL means that the SQL statement will be written and saved in the *SEQUEL syntax. Selecting this option allows greater functions within the view (query). For instance, you’ll be able to use the CVTDATE function to create a date field from a numeric or character field.
- *SERVER means that the SQL statement is written in native SQL.
Note: If you define a server as *SEQUEL, the *SEQUEL syntax will also be used.
2. The GUI Method for Converting Queries
The GUI method is the best way for less technical users to select and import queries on an ad hoc basis.
Step 1: Open Sequel Viewpoint—Sequel’s graphical user interface—and navigate to a library where you would like to place your converted queries.
Step 2: Use the menu at the top of the screen to select your import. Simply click File > Import > Query Definition.
Step 3: Navigate to the library that contains the existing query definition. You should see all of the queries in the folder.
Step 4: Select which query you’d like to convert. Use Ctrl-click to individually select multiple queries. Or use Shift-click to select a range of queries.
Step 5: Set the database/server and syntax parameters using the values in a drop-down menu. In our example, this will create a view in the Sequel syntax and use the SQE.
Step 6: Select whether to Import Report(s) or Import View(s). In our example, we’ve selected Import Report.
Step 7: Click the Import button to convert the selected queries into the library defined in step 1.
Step 8: The query conversion is now complete! You should see the query you’ve just converted to a Sequel view in the Viewpoint library.
Converted Queries, Modern Results
Sequel makes it simple to convert your Query/400 queries in whichever way makes most sense for you. You won’t lose any of your existing work, and you’ll be able to build on those queries with modern Sequel functions.
For instance, make it easy for business users to explore the query with run-time prompts. Build an executive dashboard so your executive team can make decisions. Or create a report that makes the data easier to digest.
Once your queries are converted, you’ll have endless options for utilizing them and a simple way to manage them.
If you're still thinking about making the move from Query/400, check out our content hub for resources on how to get moving.
Get More Out of Your Queries
Already using Sequel? Fortra offers robust Sequel training throughout the year to help users like you do more with Sequel, from converting queries to building dashboards.
