Using Query Manager



Enter STRQM on the command line to start the Query Manager application






Select 10 to work with Query Manager profiles.





Query Manager profile allow the system administrator the ability to modify the users SQL ability and other features of Query Manager.


Query Manager profiles work are create when the user profile is created.


*SECADM authority or higher is needed to be able to modify Query Manager Profiles.





Default library for QM objects  - Define where the users query objects will reside.

Default Creation authority - Default authority assigned objects created by this user.


Run Query Mode Interactive – Allows the user to run SQL interactively

Batch – Require user to create the query and then execute it using the run command


Allow Change run query mode = Yes –Allows user to change between batch and interactive mode for their profile only.


User Access Level  -           Basic – Limits access to query and report form options

                                                All – Give access to all query manger options unless otherwise restricted.





QM Query Profiles – Continued


Confirmation messages = Y – Display confirmation message on database changes

                                                N – Don’t display confirmation message on database changes


Naming Convention =         *SYS – Uses AS/400 system naming conventions.  Restricts queries to AS/400 environment

                                                *SAA – SQL naming conventions are used


Relational Database Connection-      *None – Local database (AS/400 system database)

                                                                *Current – Existing connected database is used

                                                                Name – Name of connected database.


RDB Connection Method - Determines the connection management method used.

                                                                *RUW – Disconnects previous database connections before trying to connect to the new database

                                                                *DUW – Does not disconnect previous database connection.


Default library of QM Queries – Can assign where user queries are stored.





QM Query Profiles – Continued


File to be used for query output  - You can force query output and form files.


Commitment control levels -               1 – No rows are locked.

                                                                2 – All changed rows are locked until a commit or rollback statement is executed

                                                                3 – Cursor stability – All changed rows and the current cursor row are locked until the commit statement is executed.

                                                                4 –All rows are locked until the commit or roll back is executed


Default creation mode         1 – SQL Mode.

                                                2 – Prompted mode.

Allow Change to query creation mode             Y – Allows user to change their query creation mode

                                                                                N – Prevents user from changing their query creation mode.


Show query default sort sequence   Y – Specify sort sequence is available

                                                                N – Specify sort sequence




Allow access to QM tables                                Y  = Allows access to the Work with QM Tables

                                                                N  = Prevents access to the Work with QM Tables.


Default Column displays    1 – Column names -  Uses SQL column names

                                                2 – System Column Names AS/400 column headings


Select allowed SQL statements –      Y  provides list of SQL statement to give user authorization to

                                                                N  Does not display list of SQL statements    


















Allows you to select which option a user has



Allows you to select what options a user has.



Allows you to select what options a user has.




Select 1 to enter the Work with QM queries




Select 1 Create and enter the name of the query.




QM query prompt looks and feels very much like Query/400


Specify files = Allows you to select files to use

Define expression = Allows you to create fields and do calculations

Select and Sequence fields = Allow you to select the fields in your query

Select Records = Allows you to select records

Sort Records = Allows you to sort records.

Select Summary functions = Allow you to summarize data

Specify duplicate records = Allows you to decide how to handle duplicate records.

Specify sort sequence = Allows you to determine system sort sequence

Specify report formatting = Allows you to create reports






Enter in file names you want to use.   The system will assign a letter to specify file for SQL statements.



Allows you to join fields


Same types of joins as Query /400


1.        Matching fields

2.        All records in primary

3.        Drop records.




Allows you to select fields.




Allow you to select records based on field values.  Similar to Query/400


Sequence fields is similar to Query 400.




Allows you to keep duplicates or only first row of duplicated records.




Allows you to select the system sort sequence to use for the query.




Define how you want to run a query and what report format to use.



Sample of QM Query run to display.






Creating reports is a separate process for QM Query. 


Edit column formatting – Similar to defining break level and column formatting

Edit page heading – Allows multiple lines of page headings

Edit page footing – Allows multiple lines of page footings

Edit final text – Allows multiple lines of text at end of report

Edit break text – Allows edit at breaks

Specify formatting options -




Edit column formatting allows you to assign break points and column formatting


Usage= Assigns values to the fields

















Allows you to select the data type for the fields

Character data

Numeric data

Date and time data

Graphic data






Allows you to add up to five lines of text.



Allows you to add up to five lines of text.





Sample report displayed to screen










Specify how you want the report to look.




Display of sample QM query in SQL format.


F18 on most screens allows you to view the SQL version of QM query.





Create a Second report based on the same data.






Select 1 and enter report name.





QM Query remembers the last query you were working on.  It allows you to edit you new report layout.



Enter difference break sequence for report 2




Allows you to select 5 lines of page headings


Enter 1 line of page heading



Allows you to enter five lines of page footing


Enter 1 line of page footing






Allow user to select report formatting.




Sample of  Report 2 sent to display.



Select 3 – to Work with Query manager tables.


This allows you to create tables on the fly (without DDS)  similar to the way you would create a table in a database or with SQL. 


The Create table process creates a physical file on the system.



Select 1 and enter the table name




This screen allows you to create the fields in the file


The data types allowed are:


BIGINT      Big integer

BLOB        Binary large object

CHARACTER   Any character

CLOB        Character large object

DATALINK    Datalink

DATE        Date

DBCLOB      Double-byte large object

DECIMAL     Packed decimal

DOUBLE      Long floating point

FLOAT       Floating point

GRAPHIC     Any graphic

INTEGER     Large integer

NUMERIC     Zoned decimal

REAL        Short floating point

ROWID       Row identifier

SMALLINT    Small integer

TIME        Time

TIMESTAMP   Timestamp

VARCHAR     Variable character

VARGRAPHIC  Variable graphic




Add data to table.


This screen allows you to enter data into the table one record at a time. 


Note:  This will only work for tables with AS/400 acceptable data types.  If you have a table with other types of data not supported in the green screen world you will have to populate the table using the correct program (Java etc).


The QM application will send a warning when the files are not able to be modified in its environment.