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

Break1

Break2

Break3

Break4

Break5

Break6

Omit

Average

Count

First

Last

Maximum

Minimum

Sum

 

 

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.