iseries sql agent - helpsystems

iSeries SQL AgentiSeries SQL Interactive Monitor

iSeries SQL Monitor

6.0VMC-SQL

VISUAL Message Center iSeries SQL Agent

The software described in this book is furnished under a license agreement and may be used only in

accordance with the terms of the agreement.

Copyright Notice

Copyright © 2016 Tango/04 All rights reserved.

Document date: September 2016

Document version: 1.5

Product version: 6.0

No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any language or computer language, in any form or by any means, electronic mechani-cal, magnetic, optical, chemical, manual, or otherwise, without the prior written permission of Tango/04.

Trademarks

Any references to trademarked product names are owned by their respective companies.

Technical Support

For technical support visit our web site at www.tango04.com.

Tango/04 Computing Group S.L. Avda. Meridiana 358, 12 Barcelona, 08027 Spain

Tel: +34 93 274 0051

http:\\www.tango04.com

http:\\www.tango04.com

Table of Contents

Table of Contents

Table of Contents .............................................................................. iii

How to Use this Guide........................................................................vi

Chapter 1

Introducing the iSeries SQL Agent........................................................ 11.1. Enhancing iSeries Database Security.............................................................1

1.2. Interactive SQL Monitor Major Features .........................................................2

1.3. SQL Monitor Major Features...........................................................................3

1.4. Benefits of the iSeries SQL Agent ..................................................................3

1.4.1. Real Time alerting of Suspicious SQL/STRSQL Activity...........................3

1.4.2. Flexible Reporting ....................................................................................4

1.4.3. Simplifying (and Reducing the Cost of) Operations .................................4

1.4.4. Complementing a Corporate BSM / RTI Initiative .....................................4

1.4.5. Speeding Up Ad Hoc Changes After an Application Failure .....................4

1.4.6. Easier Audits, No App Changes Required ...............................................4

1.4.7. Increasing Application Availability, Saving Downtime Costs.....................4

1.4.8. Being In Compliance with Security Laws and Avoiding Fines...................4

1.4.9. Controlling External Consultants, Offshore Personnel, Etc.......................5

1.4.10. Greatly Enhancing Security ....................................................................5

1.4.11. Preventing and Investigating Fraud ........................................................5

1.5. Differences Between the SQL Monitor and the Interactive SQL Monitor........5

Chapter 2

VISUAL Message Center iSeries SQL Agent Reports ................................ 62.1. Predefined SQL Monitor Agent Reports .........................................................7

2.1.1. Creating Customized Subreports ..............................................................7

© 2016 Tango/04 Computing Group Page iii

Table of Contents

2.2. Available Report Export Formats ....................................................................7

2.2.1. Available Report Destinations ...................................................................8

Chapter 3

Interactive SQL Monitor (SQI) ............................................................. 93.1. Monitoring Interactive SQL Statements in Your System.................................9

3.2. Installing the iSeries SQI Monitor..................................................................10

3.2.1. Configuring SQI Monitor: Main Menu......................................................10

3.3. Configuring and Starting the SQI Monitor .....................................................11

3.3.1. Configuration...........................................................................................11

3.3.2. Stop Logging Interactive SQL .................................................................12

3.3.3. Check Logging of Interactive SQL ..........................................................12

3.3.4. Starting the Monitor.................................................................................12

3.3.5. Set Autostart Defaults (Start at IPL Recommended) ..............................13

3.3.6. Check Activity of Kernel Supervisor ........................................................14

3.3.7. Starting the SQL Monitor.........................................................................14

3.4. Monitoring your SQL Statement Events with VISUAL Message Center .......14

3.4.1. Example – SmartConsole Message for Interactive SQL Statement Events16

3.5. Interactive SQL Monitor Database Cleanup .................................................17

3.5.1. SQI Database Cleanup Using Interactive SQL .......................................17

Chapter 4

SQL Monitor .................................................................................... 194.1. Monitoring SQL Statements in Your System ................................................19

4.2. Installing the iSeries SQL Monitor.................................................................20

4.3. Before You Begin..........................................................................................20

4.4. Configuring SQL Monitor: Main Menu...........................................................21

4.5. Starting the SQL Monitor ..............................................................................22

4.6. Configure SQL Monitor ................................................................................22

4.6.1. Automatic Monitor Starting......................................................................22

4.6.2. Send To SmartConsole ..........................................................................22

4.6.3. Retrieve Events From ............................................................................23

4.6.4. Check Interval ........................................................................................23

4.6.5. Retrieve Variables ..................................................................................23

4.6.6. Number of Hours to Keep ......................................................................23

4.6.7. Cycles Number to Delete Operation ......................................................23

4.6.8. Execute STRDBMON for All Jobs...........................................................23

4.6.9. Filter by Job, Job User, Job Number.......................................................24

4.6.10. Filter by User Profile .............................................................................24

4.6.11. Filter by Database .................................................................................24

4.6.12. Include System SQL .............................................................................25

4.6.13. Output Library .......................................................................................25

4.6.14. Filter by Query Governor.......................................................................25

© 2016 Tango/04 Computing Group Page iv

Table of Contents

4.7. Checking SQL Monitor Jobs .........................................................................25

4.8. Filtering Jobs Menu.......................................................................................25

4.9. Configuring With SQL Monitor Maintenance.................................................26

4.9.1. Subsystem Name....................................................................................26

4.9.2. Job Name ...............................................................................................27

4.9.3. Job User .................................................................................................27

4.9.4. Job Real User ........................................................................................27

4.9.5. Subsystem Name: (SBSNAME) .............................................................28

4.9.6. Job Name: (JOBNAME) .........................................................................28

4.9.7. Job User: (JOBUSER) ...........................................................................28

4.9.8. Job Real User: (REALUSER) .................................................................28

4.9.9. Job Type: (JOBTYPE) ............................................................................28

4.9.10. Group Profile: (USERGROUP) ............................................................29

4.9.11. User Class: (USERCLASS) ................................................................29

4.9.12. Accounting Code: (ACGCODE) ............................................................29

4.9.13. Inclusion / Exclusion..............................................................................30

4.10. Monitoring Your SQL Statement Events with VISUAL Message Center ....30

4.10.1. Example – SmartConsole Message for SQL Statement Events ...........32

4.10.2. Operation Code Valid Values................................................................33

4.10.3. Commit/Rollback Type Valid Values .....................................................35

4.11. SQL Monitor Database Cleanup .................................................................35

4.11.1. Database Cleanup Using Interactive SQL ............................................35

4.11.2. SQL Database Cleanup Using Interactive SQL ....................................36

Appendix A: SQL Monitor for ODBC_JDBC .......................................... 38A.1. Installing SQL Monitor for ODBC/JDBC .......................................................39

A.2. Configuring and Starting the monitor............................................................39

A.2.1. Configure SQL Monitor for iSeries..........................................................39

A.2.2. Configure SQL Monitor Customized for ODBC/JDBC............................41

Appendix B: Performance Considerations............................................ 45B.1. PTFs ...........................................................................................................45

B.2. CPU Usage and Rapid Occupation ..............................................................46

B.2.1. Resolving Large Overhead.....................................................................46

B.3. Processing Speed .......................................................................................47

Appendix C: Contacting Tango/04...................................................... 49

About Tango/04 Computing Group .................................................... 51

Legal Notice .................................................................................... 52

© 2016 Tango/04 Computing Group Page v

How to Use this Guide

© 2016 Tango/04 Computing Group Page vi

How to Use this Guide

This chapter explains how to use Tango/04 User Guides and understand the typographical conventions

used in all Tango/04 documentation.

Typographical Conventions

The following conventional terms, text formats, and symbols are used throughout Tango/04 printed

documentation:

Convention Description

Boldface Commands, on-screen buttons and menu options.

Blue Italic References and links to other sections in the manual or further documentation containing relevant information.

Italic Text displayed on screen, or variables where the user must substitute their own details.

Monospace Input commands such as System i commands or code, or text that users must type in.

UPPERCASEKeyboard keys, such as CTRL for the Control key and F5 for the function key that is labeled F5.

Notes and useful additional information.

Tips and hints that will improve the users experience of working with this product.

Important additional information that the user is strongly advised to note.

Warning information. Failure to take note of this information could potentially lead to serious problems.

Introducing the iSeries SQL Agent

Chapter 11 Introducing the iSeries SQL Agent

1.1 Enhancing iSeries Database SecurityThe VISUAL Message Center Interactive SQL Monitor and the SQL Monitor Agents provide a means to

make the security of your relational database residing on iSeries/i5 systems much tighter, by capturing

all the SQL statements executed on your systems and partitions.

They will let your operators, system administrators, auditors and security officers know both historically

and in real time of every single SQL statement run against the database engine, or to highlight only the

most suspicious events according to your own dynamic rules. In conjunction with the SmartConsole,

they can execute unattended actions for maximum security. For instance, they can alert you immediately

of an UPDATE executed against a sensitive database on a weekend night, or better, automatically hold

the offending job and disable the suspicious user. VISUAL Message Center Reports allows you to then

make the most out of the collected information, such as creating periodic reports for the corporate Web

site in a completely automated manner.

Additionally, the SQL Monitor can measure database response time and help you to understand, profile,

and optimize native, Client/Server or Web applications.

Figure 1 – Real time alerts can be obtained and automated; self-protecting actions can be executed according to flexible, dynamic, customizable rules.

The Interactive SQL Monitor Agent covers only the SQL statements executed from interactive jobs,

while the SQL Monitor Agent may capture all the SQL statements, regardless of their source (that is, it

will also capture SQL transactions coming from embedded SQL statements on RPG and COBOL

programs, ODBC/JDBC clients, Client/Server applications, and any kind of program using SQL

statements to access the native database).

iSeries data can be also accessed using native technology, for example, native access from

applications in any programming language (including COBOL and RPG), DFU, UPDDTA and WRKQRY.

© 2016 Tango/04 Computing Group Page 1


SQL Monitor only monitors access executed using SQL. It does not monitor accesses performed using

native technology.

SQL activity and suspicious actions can be sent to a corporate dashboard, be assigned a color-coded

severity and status, and used as another sensor of operational health for auditing purposes, monitoring

projects, or major Business Service Management (BSM) initiatives.

Figure 2 – Customizable Audit Reports can be obtained in a variety of formats, either on demand or automatically.

1.2 Interactive SQL Monitor Major FeaturesWith the Interactive SQL Monitor, you can:

• Keep a record of every single SQL statement executed from an interactive job, using STRSQL

or embedded SQL on RPG, COBOL, or any other language.

• Be alerted in real time of SQL statements being executed from interactive jobs, by SMS, email,

popup window, etc.

• Use complex escalation rules to guarantee notification of suspicious activity

• Filter out undesired SQL statements selecting by user, table name, date, time, calendar,

system, job, subsystem, SQL completion code, etc.

• Automatically execute self-protecting actions when a suspicious activity is detected (such as to

hold a job, disable a user profile, remove authorization to tables, etc.)

• Schedule different unattended actions based on any event data, calendar, system, etc.

• Easily navigate audit trails by using the SmartConsole dynamic Event List, Business Views and

Event Navigator

• Color-code suspicious activity

• Create web-based reports of SQL activity

• Automatically feed the corporate portal with SQL activity reports periodically

• Create comprehensive audit reports for law and auditing compliance

• Monitor SQL activity with no need to use journal files or triggers or any other application

changes and minimal CPU overhead



• Generate graphical Enterprise Views with customizable state information (such as green for

normal and red for a fraud intent detection)

• Access real time information from the Internet using a browser

• Export audit data into Excel and many other formats

• Off-load historical audit data to other systems (such as Windows or Linux) for saving on line

iSeries storage or for security reasons (the reporting module supports any ODBC-compliant

database)

• Selectively grant or deny access to the data retrieved by the agent to different operators

through the usage of real time dynamic subscriptions

• Integrate SQL activity events into practically any other application in real time

• And more

1.3 SQL Monitor Major FeaturesThe iSeries SQL Monitor Agent extends the capabilities of the Interactive SQL Monitor to any kind of

SQL statement, effectively recording any type of SQL access to the iSeries database. Major features

are:

• All the mentioned features of the Interactive SQL Monitor listed above, plus

• Comprehensive auditing coverage of any SQL statement, interactive or not

• Full capturing of (and protection from) suspicious network database accesses, including Client/

Server programs, Java JDBC-based programs, and any kind of remote SQL statement

• Full capturing (and protection from) internal SQL accesses, in batch or interactive programs,

embedded SQL on RPG, COBOL, or any other language

• Easy to analyze information about application behavior that can be useful to understand,

profile, and fine-tune Client/Server or Web applications

• Discovery of CPU-abusive SQL statements (since every SQL statement indicates both elapsed

time in execution and estimated process time, estimated number of rows selected, etc.)

• SLA measurement and bottleneck detection (by using the elapsed time in execution and

estimated process time)

• Selectively mask constant values on WHERE, SET and other clauses to preserve data

confidentiality

• Selectively control, filter or execute actions based on the remote IP address

• Customize the capture of information by setting capturing rules (filtering by job name, job user,

real user, job type, group profile, user class, and/or accounting code)

• And more

1.4 Benefits of the iSeries SQL Agent

1.4.1 Real Time alerting of Suspicious SQL/STRSQL ActivityBy sending events (SQL statements executed) to the VISUAL Message Center SmartConsole, this

agent provides an early alert of suspicious activity which can be automatically stopped (for instance, by

holding a job executing SQL updates on a production table during weekends).



1.4.2 Flexible Reporting With VISUAL Message Center Reports, you can generate different detailed or summary reports, either

on screen, on a variety of formats including Adobe Acrobat PDF, or even feed a web or intranet site

automatically (generating HTML reports).

1.4.3 Simplifying (and Reducing the Cost of) Operations By using the power of the SmartConsole, undesired events can be easily filtered out (for instance, all

statements that do not alter the database can be ignored; or only SQL statements accessing sensitive

files can be shown in real time), saving valuable Operators time. Moreover, automated actions can be

specified and the full power of calendaring, event escalation, duplicate suppression, coloring,

enrichment, transformation/formatting, and more is available at any time.

1.4.4 Complementing a Corporate BSM / RTI InitiativeBusiness Service Management and Real Time Infrastructure projects require comprehensive sensors

and monitors on all the functions that may potentially affect business services. Protecting the database

accesses is a fundamental part of these efforts.

1.4.5 Speeding Up Ad Hoc Changes After an Application FailureSome security officers prohibit the usage of SQL to patch production databases, forcing developers and

operators to write ad hoc RPG or COBOL programs for correcting tables after a failure or bug on

applications, since interactive SQL statements are not recordable (nor auditable). However, by using the

Interactive SQL Agent, this restriction makes no sense anymore. Indeed, SQL statements are much

easier to audit (and require less technical knowledge) than RPG or COBOL programs. For developers

and operators, there is also a clear advantage, since executing the change through a single SQL

statement is easier and much faster than writing and compiling a program.

1.4.6 Easier Audits, No App Changes Required This agent simplifies the auditing task since only relevant SQL statements need to be read. Most

irrelevant statements can be quickly filtered out. Compared to record-level auditing, SQL statement

auditing requires less effort since a single statement can generate millions of record changes, which

would generate a huge record changes printout. Record-level changes also require the files to be

journalled or a trigger mechanism to be implemented. The VISUAL Message Center iSeries SQL Agent

require no journal or database instrumentation.

1.4.7 Increasing Application Availability, Saving Downtime CostsBy automatically inspecting every SQL statement executed against the production tables, generating

both audit trails and real time alerts, and starting self-protection mechanisms in a unattended manner

(such as a user profile disablement), the SQL Monitor agents can contribute enormously to the safety,

integrity, confidentiality and availability of your sensitive files. By keeping applications working your

company will save the money of costly downtime.

1.4.8 Being In Compliance with Security Laws and Avoiding FinesThese agents help you be in compliance with security laws that mandate recording of file accesses,

indicating date, time, user, real user (which can be different from the job user), and the full SQL

statement which identifies the record (or record set) accessed or modified. European privacy laws, for

instance, require accesses of sensitive data to be recorded and stored for several years. American laws

such as Sarbanes Oxley and others also mandate strong controls over the integrity and/or confidentiality



of the data. Vertical markets also have specific requirements, such as Basel II for the financial sector or

HIPAA and CFR 21-11 for the health industry, which can be satisfied with the use of this agent. 1

1.4.9 Controlling External Consultants, Offshore Personnel, Etc.These agents will help you enormously in controlling all the interactive and batch SQL activity issued not

only by your internal staff, but also by external contractors, consultants, etc. that may work temporarily

for your company or access your files remotely.

1.4.10 Greatly Enhancing Security By using the features described above your company will be able to enhance security controls and enjoy

a better security for your sensitive data, either in an attended or unattended manner.

1.4.11 Preventing and Investigating FraudBy monitoring SQL activity, accesses, changes, deletions and inserts to the production databases and

sensitive files can enormously help you preventing and investigating fraud cases.

1.5 Differences Between the SQL Monitor and the Interactive SQL MonitorAs mentioned, the SQL Monitor can record every SQL statement executed on the system. The

Interactive SQL Monitor can only capture SQL statements executed from interactive jobs. These can be

entered by using STRSQL or embedded SQL on RPG, COBOL, or any other language.

SQL Monitor and Interactive SQL Monitor retrieve the same set of data from SQL statements.

1. Comprehensive legal compliance usually requiresadditional agents and monitors. A Tango/04 Computing Group

Authorized Business Partner representative can assist you in determining your needs.


VISUAL Message Center iSeries SQL Agent Reports

Chapter 22 VISUAL Message Center iSeries SQL Agent Reports

You can use either VISUAL Message Center Reports or create your own internal database queries.

Reports is a very flexible means of obtaining information, since it allows you to define your selection in a

intuitive manner, using an easy to use GUI, choosing from a wide range of selection parameters. For

advanced users, there is even more flexibility, since the selection criteria can be expanded to any SQL

selection clause. There are more than 150 different predefined reports that cover Operations, Service

Level Management, Job Accounting, Security and other areas, in detailed, summary, and graphical

formats; and that number is continuously growing.

Reports can be exported into a variety of formats, including PDF (Adobe Acrobat), plain text, HTML,

Windows Word document format, etc. A complete list of available formats is included at the end of this

chapter. Reports can be sent by email right from Reports, too.

Reports can also be scheduled. For instance, you can automatically feed your corporate portal with

auditing reports every night (or at any interval).

Figure 3 – Reports contains over 150 reports to analyze the operational health and security of your IT infrastructure. Customized reports can be created easily.



2.1 Predefined SQL Monitor Agent ReportsThere are several predefined reports that you can use immediately from VISUAL Message Center

Reports to audit and analyze SQL statements executed. There are various report templates, for

performance, for example.

Among the reports you will find there are: SQL Statement Executed – Grouped by Job, SQL Statements

Executed – Grouped by Real User, SQL Statements Executed Ranking – Grouped by Job, and

Interactive SQL Statements Executed – Grouped by Real User.

Figure 4 – You can view, print, email, or transform into HTML listings of executed SQL statements easily by using Reports.

2.1.1 Creating Customized SubreportsYou can also create subreports, which are a variation of the existing reports. For instance, you may

create a report called “Interactive SQL Statements executed this month by QSECOFR”, by selecting

This Month as the report date and QSECOFR as the Real User, and saving the parameter file with the

name you want.

You can also change the Tango/04 Computing Group logo by your company’s logo easily, and perform

other customizations. For more information on Reportins, see the VISUAL Message Center Reports

User Guide.

2.2 Available Report Export FormatsReports includes a wide range of export formats for your convenience, as demonstrated in the following

list. You can see a list of available formats when exporting a report.

• Acrobat (PDF)

• Comma Separated Values (CSV)

• Crystal Reports (RPT)

• Crystal Reports 7.0 (RPT)

• Excel (XLS, data-only and editable)


http://customers.tango04.com/content/695

http://customers.tango04.com/content/695


• DOC (Word document, data-only and editable)

• Rich Text Format (RTF)

• XML

• HTML

2.2.1 Available Report DestinationsYou can send the generated report to several destinations. The most common is disk file (save the

report to disk), which is the default, but the following destinations are available:

• Application

• Disk File

• Lotus Domino Database

• Microsoft Mail (MAPI)

For instance, if you want to send a report by email, use the Microsoft Mail destination. You will be

prompted for the recipient’s email address.


Interactive SQL Monitor (SQI)

Chapter 33 Interactive SQL Monitor (SQI)

In this section, you will learn how to work with the VISUAL Message Center iSeries SQI (SQL

interactive) monitor.

First, we will define the scope of the product and its uses, and then you will learn how to configure and

work with the product.

3.1 Monitoring Interactive SQL Statements in Your System.This agent’s practical application is to audit the interactive SQL statement events in your system. These

are the SQL statements that run from an SQL session started with STRSQL command or embedded

SQL on RPG, COBOL, or any other language.

SQI monitor can store these events in a specific database for SQI monitor and/or in VMC historical

database. You will be able to query the specific database with VISUAL Message Center Reports. Events

sent to VMC historical database will be received in real time at the SmartConsole.

In order for SQI monitor to work correctly, it is necessary to start a job called Kernel Supervisor. For

more information see section 3.3 - Configuring and Starting the SQI Monitor on page 11. It is also

necessary to start the SQL Monitor job.

For more information, see section 4.5 - Starting the SQL Monitor on page 22.

Every SQL statement event can also store additional information that you will be able to see in the

SmartConsole and also in the reports generated by Reports. This information includes the following

fields:

• Initial timestamp of statement run

• Ending timestamp of statement run

• Elapsed time in execution (milliseconds)

• SQL code

• SQL statement length

.



3.2 Installing the iSeries SQI MonitorThe iSeries SQI monitor is installed as part of the VISUAL Message Center suite product. For

information on installation and activation, please see the installation instructions of the product.

3.2.1 Configuring SQI Monitor: Main MenuAfter you have installed the product in the B_DETECTOR library, you can access the main menu using the

following command:

GO MENU(B_DETECTOR/BD_MENU) and then …

Warning Some IBM PTFs cannot be applied (either temporarily or permanently) from an interactive

session where interactive SQL monitor (SQI) is active. Please note that this is only relevant

to VISUAL Message Center versions previous to version 6.00.

Important To safely install any PTF, please make sure that you are applying it from a session that is not

being controlled by SQI. To do this, you may install PTFs from a job that is not being

controlled by SQI, or terminate it by issuing the command B_DETECTOR/ENDKRNSUP.

Next, start a new 5250 interactive session that will not be controlled by SQI. Alternatively,

you can safely install any IBM PTFs or cumulative packages when the system is restricted,

or install them in delayed mode (at IPL time).

Applying certain PTFs from a session controlled by SQI may produce unpredictable results

in system’s behavior (not necessarily related to SQI), including the message "mch3402: tried

to refer to all or part of an object that no longer exists". If you think that an IBM PTF has been

applied from a session controlled by SQI, you may contact Tango/04 Computing Group

Support for help. Our technicians may determine if it’s the case and assist you in correcting

the problem.

Note B_DETECTOR/ENDKRNSUP (End Kernel Supervisor) exists since Kernel Supervisor is a

requirement for SQI. Kernel Supervisor is also a requirement for Crash Preventer (CPR), so

ending Kernel Supervisor will also end Crash Preventer.

Please note that this is only relevant to VISUAL Message Center versions previous to

version 6.00.

Note Tango/04 or our partners can provide professional installation assistance and expert advice.

We can also demonstrate the most important operating concepts of the product so you don’t

even need to read this manual at all!



Figure 5 – VISUAL Message Center Menu Option 61

Select Option 61 (Go to Interactive SQL Monitor Menu).

Figure 6 – Interactive SQL Monitor menu

This screen gives you the main options for working with the product. We will see all product options.

3.3 Configuring and Starting the SQI MonitorIn order to receive interactive SQL statement events from the iSeries SQI monitor in the SmartConsole,

you must follow these steps:

3.3.1 ConfigurationUse option 11 “Start logging Interactive SQL”, if you want the SQI monitor to store events in its specific

database. Use option 14 “Change smart console real time alert settings” to decide if you want SQI

monitor to send events to VISUAL Message Center’s historical database (SmartConsole).



Figure 7 – Send to SmartConsole option

You can also change this configuration like this:

3.3.2 Stop Logging Interactive SQLENDSQIMON allows you to end the logging STRSQL activity in your system. You can also select option 12

“Stop logging Interactive SQL”.

3.3.3 Check Logging of Interactive SQLCHKSQIMON allows you to check the SQI monitor activity. You can select option 13 “Check that logging of

interactive SQL activity is active”.

These options are dynamic, so any change is immediately noted.

3.3.4 Starting the MonitorYou must enter Interactive SQL monitor license key from monitor menu (select option 32).

Start Kernel Supervisor (use option 21 to go to Kernel Supervisor menu). If no valid key for SQI exists, a

message will be sent to QSYSOPR when starting Kernel Supervisor. To correct that, you must enter a

valid key and restart (end and start) Kernel Supervisor.

SQL statements running in interactive jobs started while Kernel Supervisor was active (and with a valid

license key) so they will be monitored from now. To verify if Kernel Supervisor is active, you must select

option 24 “Check activity of Kernel Supervisor” in Kernel Supervisor menu.



Figure 8 – Kernel Supervisor Menu

If this is your first time installing VISUAL Message Center (which includes SQI), you will not receive SQI

events until subsystems in which interactive jobs run have been started at least once after the VISUAL

Message Center installation. So you can either restart your interactive subsystems or wait until next IPL.

3.3.5 Set Autostart Defaults (Start at IPL Recommended)With the option 22 you can specify whether Kernel Supervisor will start after an IPL or not. The possible

values are:

• *YES: The Kernel Supervisor will start automatically after an IPL.

• *NO: The Kernel Supervisor will not start automatically after an IPL.

To start it manually use command STRKRNSUP.

Note As kernel supervisor is a requirement for SQI Monitor it is recommended to set it as

autostart *yes. You can do that from option 22 in the kernel supervisor menu



Figure 9 – Change kernel supervisor

3.3.6 Check Activity of Kernel SupervisorYou can check the Kernel Supervisor at any time by entering option 24: ”Check activity of Kernel

Supervisor.” in the iSeries Kernel Supervisor monitor menu.

3.3.7 Starting the SQL MonitorIn order for Interactive SQL Monitor to monitor SQL activity from interactive jobs, it is also necessary to

start the SQL Monitor job. For more information, see section 4.5 - Starting the SQL Monitor on page 22.

3.4 Monitoring your SQL Statement Events with VISUAL Message CenterWhile Kernel Supervisor is active and “Smart console real time alert settings” is selected, you will start

receiving these events in the SmartConsole.

The SmartConsole is shown below:



Figure 10 – Receiving events in the SmartConsole

Business Views allow you to group messages and events for specific “business” or technological areas.

The first pane contains your Business Network, a business impact analyzer where you can create and

organize your Business Views into folders that reflect the structure of your enterprise. The second pane

is dedicated to the Message Grid, where you can see the messages of the currently selected Business

View.

In this case, it is great for grouping security messages – regardless of which user, subsystem, job etc.

they come from.

Note All events received from the iSeries SQL Agent have the agent code “SQI”



3.4.1 Example – SmartConsole Message for Interactive SQL Statement Events

Figure 11 – Message in the SmartConsole

The screenshot above shows us the message as received by the SmartConsole. This specific event is

generated by an STRSQL interactive session in the AS/400 iSeries. It is important to realize that you are

able to see valuable information as initial timestamp of statement run, ending timestamp of statement

run, elapsed time in execution (in milliseconds), SQL code and SQL statement length.

The following message variables are shown:

Figure 12 – Message variables

For reference of “SQL Code” possible values, look for:



• DB2 Universal Database for iSeries SQL Messages and Codes book.

3.5 Interactive SQL Monitor Database CleanupThe command Delete Interactive SQL Monitor Messages (DLTSQIMSG) deletes message data collected

previously by Interactive SQL Monitor. This is also accessible from the interactive SQL menu option 41,

“Delete interactive SQL monitor messages”. It is advisable to control the size of the database and

periodically perform a clean up operation once every day.

For the REORGANIZE parameter, specify whether you want to reorganize the file or not.

Reorganizing allows deleted records space to be released. By specifying REORGANIZE *YES the process

will last longer, and jobs locking the file will be ended immediately. All users must be signed off VISUAL

Message Center.

It would make sense to include this command as an IPL instruction. Database registers can be deleted

in two ways:

• *DATE: By date. With this option, database registers with a Date field value between the user-

defined dates will be deleted. Specify a start and end date for the time span where you want to

delete records. Records between these two dates will be deleted from the database. Data must

be specified in job date format. If you specify the special value *ALL for this parameter, all

database registers will be deleted.

• *KEEP: By number of days. With this option, database registers with a Date field value before N

number of days from today will be deleted. Simply specify a number of days to keep messages.

You can use this option to automate database cleanup by scheduling the B_DETECTOR/DLTSQIMSG command, specifying the number of days you want to keep them.

The command Delete interactive SQL Monitor Messages (DLTSQIMSG) deletes message data collected

previously by interactive SQL monitor. This is also accessible from the interactive SQL menu option 41,

“Delete interactive SQL monitor messages”.

The VISUAL Message Center Interactive SQL Monitor collects data and stores it in the file

called SILOG01X.

It is advisable to control the size of the database and periodically perform a clean up operation once

every day.

3.5.1 SQI Database Cleanup Using Interactive SQLYou can clean the database and remove unwanted messages using database commands.

This method does not require all users / clients to be disconnected. The field LIQSTI of the database

B_DETECTOR/ SILOG01X contains the date that the message entered the database. To delete message

between two specific dates, you can use Interactive SQL.

To do so, follow these steps:

• Sign on to your iSeries with a user profile that is in the B_DETECTOR authorization list, or which

has special *ALLOBJ authority.

• Start a SQL session with the command STRSQL.

• Delete the desired registers with the following SQL statement (replacing date values in the

format YYYYMMDD):

DELETE FROM B_DETECTOR/SILOG01X



WHERE LIQSTI >= ‘start date’ and LIQSTI <= ‘end date’

Example

For example, to delete all registers with dates between 1st July 2003 and 15 July 2003 inclusive,

enter:


WHERE LIQSTI >= ‘20030701’ and LIQSTI <= ‘20030721’

It is not necessary to introduce complete to and from dates. You could delete all registers up to a

certain date, for example, to delete all messages up to July 15 2003:


WHERE LIQSTI >= ‘20030715’

You can use the command SELECT to see the resulting contents of the file.


SQL Monitor

Chapter 44 SQL Monitor

In this section, you will learn how to work with the VISUAL Message Center iSeries SQL monitor.

First, we will define the scope of the product and its uses, and then you will learn how to configure and

work with the product.

4.1 Monitoring SQL Statements in Your SystemThis agent’s practical application is to audit the SQL statement events in your system. When these

events have occurred in your system, SQL monitor send a message to VISUAL Message Center

SmartConsole.

This agent can filter the SQL statements based on the following fields:

• Subsystem name and subsystem library name

• Job name

• Job user

• Job real user

• Job type

• Group profile

• User class

• Accounting code

Every SQL statement event can also store additional information that you will be able to see in the

SmartConsole. This information includes:

• Initial timestamp of statement run

• Ending timestamp of statement run

• Elapsed time in execution (milliseconds)

• Estimated process time (seconds)

• IP remote address

• Real user


SQL Monitor

• Operation code.

• SQL code.

• Commit/rollback type.

• Thread identifier.

• Table name.

• Table library.

• Table member.

• Total number of rows in table.

• Estimated number of selected rows.

• Estimated number of rows with joins.

• Estimated number of fetched rows.

• Reason code.

• Remote port.

• SQL statement length.

4.2 Installing the iSeries SQL MonitorThe iSeries SQL monitor is installed as part of the VISUAL Message Center suite product. For

information on installation and activation, please see the installation instructions of the product.

4.3 Before You BeginThe VISUAL Message Center SQL monitor for iSeries uses internal IBM DB2 technology intensively.

Make sure you understand the following requirements before activating VISUAL Message Center SQL

monitor for iSeries for the first time in a Production environment:

• This product requires that the latest PTFs are properly installed in your system.

• This product may cause high CPU usage and rapid storage occupation growth rates in

environments with applications that produce a very large number of SQL statements per

second.

Note If you are upgrading your operating system from V5R1 or earlier to V5R4 or later, you must

also reinstall VISUAL Message Center, even if the version is V5R4 compatible. When you

start the SQL Monitor a message appears reminding you to reinstall VISUAL Message

Center.

Note Although you are free to install the product in any ASP, the output file configured in

CHGSQLMON must be stored in a library located in ASP1.

Tip Tango/04 or our partners can provide professional installation assistance and expert advice.

We can also demonstrate the most important operating concepts of the product so you don’t

even need to read this manual at all!


SQL Monitor

• SQL monitor requires OS/400 V4R5 or higher on the AS/400 / iSeries

The first time you try to start the monitor a warning message will appear reminding you of these

requirements. Once you accept the message, it will not appear again.

Figure 13 – Warning message

4.4 Configuring SQL Monitor: Main MenuAfter you have installed the product in the B_DETECTOR library, you can access the main menu using the

following command:

GO MENU (B_DETECTOR/SQL_MENU)

Figure 14 – Main menu

This screen gives you the main options for working with the product. We will look at the main SQL

Monitor options in the following sections.


SQL Monitor

4.5 Starting the SQL MonitorIn order to receive SQL statement events from the iSeries SQL monitor in the VISUAL Message Center

console, you must start the SQL monitor job. Enter the command:

B_DETECTOR/STRSQLMON

You can also enter option 2 to start the job SQLMONITOR running in the T4NICELINK subsystem.

When the user starts the job SQLMONITOR, two other jobs are running. These jobs are called

USRMONITOR and HSTMONITOR. They also run in the T4NICELINK subsystem.

The USRMONITOR job works simultaneously with the SQLMONITOR job. When the SQL monitor captures

an SQL statement event from a specific job, the USRMONITOR retrieves the real user, the remote IP

address and the remote port for this specific job. Actually, the USRMONITOR stores this information in a

historical real user database when a job starts or ends, or when a user profile change has been

performed in a job.

The HSTMONITOR retrieves the job type, the subsystem name and the subsystem library name.

In this way, the SQL monitor can obtain the user real, remote IP address and remote port information

through the USRMONITOR and the job type, the subsystem name and the subsystem library name through

the HSTMONITOR.

4.6 Configure SQL Monitor Option 3 – Configure SQL Monitor refers to the SQL monitor settings, such as when to start or stop the

monitor, what recollection interval to use, whether to display or hide values, and more. SQL Monitor

settings are described individually in the following sections.

To specify what data to monitor see section 4.9 - Configuring With SQL Monitor Maintenance on

page 26

4.6.1 Automatic Monitor StartingHere you can specify whether you want SQL monitor to start up automatically in the specified

subsystem. The possible values are:

• *YES: SQL Monitor will start automatically after an IPL.

• *NO: SQL Monitor will not start automatically after an IPL. You can start SQL Monitor manually

using the command STRSQLMON.

4.6.2 Send To SmartConsole By default SQL Monitor sends data to the SmartConsole. From version V5R4 onward you can choose

not to send the SQL Monitor data to the SmartConsole, potentially improving performance of the SQL

Monitor. The possible values are:

Tip Another way to start the job SQLMONITOR is to enter the command:

GO T4NICELINK/T4NICELINK

and go to the VISUAL Message Center menu. There you can select the corresponding

option.


SQL Monitor

• *YES: the data will be sent to the SmartConsole in real time.

• *NO: no events are sent to the SmartConsole

4.6.3 Retrieve Events From You can also specify from when to start retrieving data. Available options are

• *NOW: retrieve SQL events from the moment the SQL monitor is started.

• *LASTTIME: include all SQL events since the last time SQL Monitor was run.

4.6.4 Check Interval Specify an interval in seconds between executions of SQL monitor. This means, the waiting time in

seconds between successive measurements.

4.6.5 Retrieve Variables You can specify whether to retrieve or hide the values of the variables in the SQL statements. Possible

values are:

• *YES: The values of the variables will be shown in the complete SQL statement.

• *NO: The values will be hidden in the complete SQL statement. An interrogation mark (“?”) will

be displayed instead of the actual value.

4.6.6 Number of Hours to Keep This value specifies the number of hours that SQL statement events are maintained in the system

monitor database.

4.6.7 Cycles Number to Delete Operation You can specify the number of cycles between a dynamic delete and the next delete of records in the

system monitor database.

4.6.8 Execute STRDBMON for All JobsThis value indicates whether to start SQL monitor for all jobs in your system or only a few jobs. Possible

values are:

• *YES: The System SQL Monitor will be started for all the jobs in your system specified in the

parameter JOB of this command. IMPORTANT: if the monitor is not stopped correctly by using the B_DETECTOR/ENDSQLMON command or menu option 4. End Monitor, then the system will continue to monitor SQL Activity

for all jobs, which in turn inserts records in the output database file B_DETECTOR/SLDBM01X. If

the monitor has not been stopped correctly, then the only way to stop it later, is by using one of

the following:

− iSeries Navigator tool

− IPL (Initial Program Load)

Tip In order for the monitor to retrieve all SQL statements, this value must be greater than the

maximum execution time of the SQL statements in the system.


SQL Monitor

• *NO: The System SQL Monitor will only start for those jobs that were introduced with the

command ADDDBMJOB.

4.6.9 Filter by Job, Job User, Job NumberFrom version V5R4 onward you can also filter by Job, Job User and Job Number

When you filter by job, you can use the following variables:

• *ALL: monitor SQL Statements for all jobs

• Name: SQL Monitor starts for those jobs with the name specified here. If there are duplicates

you must also specify either the job user or the job number.

When you filter by job user, you can use the following variables:

• *ALL: monitor SQL Statements for all job users

• *Name: SQL Monitor starts for those jobs users with the name specified here

• *Generic: monitor SQL statements for jobs whose job user name corresponds to the generic

name. Jobs in WAIT and in queues that correspond to the generic user name will also be

included

When you filter by job user, enter any job number between 000000 and 999999.

4.6.10 Filter by User Profile New in version V5R4 is the possibility to filter by real user (filter by user profile). Available variables are:

• *Name: enter the real name. Only SQL statements created or run by this real user will be

monitored

• Generic: Only SQL statements from users whose names start with the specified prefix will be

monitored.

• *None: SQL statements from all users are monitored.

• *Current: Only SQL statements for the user who invokes the STRDBMON command are

monitored.

Press <PgDn> to see more options

4.6.11 Filter by DatabaseFrom version V5R4 onward you can filter by database. In this field you can enter the absolute path of the

database, or simply enter the filename and library. Available options are:

• *NONE

• *ALL

In addition is also possible to filter files using the command SETSQLFLT. Can be used simultaneously, in

which case it is important that – files defined in both must overlap in order to monitor SQL statements for

these files.

Note Filtering by job, job user and job number only makes sense if the values entered here are a

subset of those configured in SQL Monitor Maintenance.


SQL Monitor

Note that filtering by database only makes sense if the values entered here are a subset of those

configured in the SETSQLFLT command.

4.6.12 Include System SQLChoose whether to include SQL Statements generated and run by the operating system. Available

options are

• *YES: Include SQL Statements from the operating system

• *NO: Only check SQL statements from users (default setting)

• *INI: Monitor SQL statements according to the SQL_DBMON_OUTPUT parameter specified in the

current INI file.

4.6.13 Output LibraryThe monitored SQL statements are stored in a file called SLDBM01X. By default this file is stored in the

library B_DETECTOR.

From V5R4 onward you can choose to store this file in any other library.

Output Library is where you specify the library that will store the file SLDBM01X. To use the default library

use the default setting *NONE.

4.6.14 Filter by Query GovernorFrom version V6R1M0 onward you can filter by Query Governor (FTRQRYGOVR). Query Governor exit

programs are called when a job’s estimated time to finish or use of storage has exceeded user-defined

limits. With the command STRDBMON (Start Database Monitor) it is possible to retrieve the records

generated by the Query Governor. You can then filter for desired records.

Available options are:

• *NONE: filtering by this parameter does not yield any records about exited jobs

• *ALL: with this parameter you will receive Database Monitor records whenever the Query

Governor limit is exceeded, no matter what value (0, 1, 2, 3) the existing exit program(s) are

returning. For example, you will receive records even if offending queries are cancelled

• *COND: this parameter lets you collect Database Monitor records whenever the Query Governor

limit is exceeded and the existing exit program(s) return value 2, 3, or 0 (in one special case).

Please find the return code value explanations at the following IBM Web site:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/apis/xqrygovr.htm

4.7 Checking SQL Monitor JobsYou can check the SQL monitor job at any time by entering option 5 – Check Monitor Activity in the

iSeries SQL monitor menu.

4.8 Filtering Jobs MenuWith a huge volume of data this filter helps you narrow your focus, for example, 2% of 20,000 jobs. If you

filter for this info before you process the data it will help the performance of your system.


http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/apis/xqrygovr.htm

SQL Monitor

Beginning with VISUAL Message Center (iSeries Modules) Service Pack 2, you can fine-tune the

information that the SQL Monitor (for both batch and interactive jobs) recollects by adjusting a new filter.

The following sections explain how to locate and use the filter.

SQL_MENU

If you want to work with interactive job filters you can skip ahead to the next section.

To configure a job filter for the SQL Monitor:

Step 1. Enter the following command:

GO B_DETECTOR/SQL_MENU

or

GO B_DETECTOR/BD_MENU

and choose option 60.

Step 2. Choose option 7.


You can filter by subsystem name, subsystem library, job name, job user, and real user

here (see beginning section 4.9.1 - Subsystem Name on page 26 for values to filter by).

SQL for Interactive Jobs

You can filter by subsystem name, subsystem library, job name, job user, and real user here.

To configure job filters for interactive monitor:

Step 1. Enter the following command:

GO B_DETECTOR/IS_MENU

or

GO B_DETECTOR/BD_MENU

and choose option 61.


4.9 Configuring With SQL Monitor MaintenanceYou can select option 1 in SQL monitor menu. With this option (SQL monitor maintenance) the user

specifies what historical and reporting data to monitor. Here you can specify which conditions an SQL

statement event must meet in order to store this event in SQL monitor database. These conditions are

defined for some fields that are retrieved for every SQL statement. More than one configuration entry

can be specified, each of them with its own conditions. You can select the option for SQL monitor

configurations. These operations are the following: copy, remove, display and add.

The SQL configuration is based in the following parameters:

4.9.1 Subsystem NameThe user can select the subsystem name for SQL monitor configuration.

Possible values are: *ALL, a generic value or a specific name.

This field value sometimes cannot be retrieved for current "run SQL statement" event, due one of the

following reasons:


SQL Monitor

• History log monitor was not active when job running the SQL statement was started.

• The job running the SQL statement was not already active.

In this case the event will be considered to accomplish a condition for this field and therefore, it will be

stored in the SQL monitor database.

4.9.2 Job Name The user can select the job name for SQL monitor configuration.


4.9.3 Job User The user can select the job user for SQL monitor configuration.


4.9.4 Job Real User The user can select the real user for SQL monitor configuration.



following reasons:

• Real user history monitor was not active when job running the SQL statement was started or

when the real user of this job was changed.


In this case, the event will be considered to accomplish condition for this field and therefore, it will be

stored in SQL monitor database.

Figure 15 – Configuring SQL monitor configuration

Example

Command SQL monitor configuration --- Add (ADDSQLCFG) add a new configuration entry for SQL

monitor


SQL Monitor

4.9.5 Subsystem Name: (SBSNAME) The user can select the subsystem name for SQL monitor configuration.



following reasons:



In this case the event will be considered to accomplish condition for this field and therefore, it will be


4.9.6 Job Name: (JOBNAME) The user can select the job name for SQL monitor configuration.


4.9.7 Job User: (JOBUSER) The user can select the job user for SQL monitor configuration.


4.9.8 Job Real User: (REALUSER) The user can select the real user for SQL monitor configuration.



following reasons:






4.9.9 Job Type: (JOBTYPE) The user can select the job type for SQL monitor configuration.

Type of active job. Possible values are:

• ASJ: Autostart

• BCH: Batch

• BCI: Immediate batch

• EVK: Started by a procedure start request

• INT: Interactive

• M36: Advanced 36 server job


SQL Monitor

• MRT: Multiple requester terminal

• PJ: Pre-start job

• PDJ: Print driver job

• RDR: Reader

• SBS: Subsystem monitor

• SYS: System

• WTR: Writer


following reasons:



In this case, the event will be considered to accomplish conditions for this field and therefore, it will be


4.9.10 Group Profile: (USERGROUP) The user can select the group profile of real user for SQL monitor configuration.



following reasons:






4.9.11 User Class: (USERCLASS) The user can select the user class of real user for SQL monitor configuration.



following reasons:






4.9.12 Accounting Code: (ACGCODE)The user can select the accounting code of real user for SQL monitor configuration.



SQL Monitor

This field value sometimes cannot be retrieved for current "run SQL statement" event due one of the

following reasons:






4.9.13 Inclusion / ExclusionThe Inclusion / Exclusion option allows you to set up job filters that include or exclude certain criteria

from the parameters on this display. This allows you to create very specific filters to give precise

meaning to the messages sent to the VISUAL Message Center SmartConsole.

Specify Include to include all matching selection criteria in the filter.

Specify Exclude to exclude the matching selection criteria from the filter.

Examples of Inclusion / Exclusion filtering

• Select *ALL in all filter parameters and *INCLUDE to create a filter that monitors all SQL job

criteria.

• Select *ALL in all job filter parameters and *EXCLUDE to create a filter that doesn’t monitor

any SQL job criteria. Note that any subsequent filters that are listed after this filter will not

function as this filter has already excluded all selection criteria.

• Select *ALL in all job filter parameters but specify an individual user profile name in the Job

User parameter and *INCLUDE to create a filter that includes all SQL job criteria for a specific

user.

• Enter a specific Subsystem and Library name with *ALL in the remaining parameters and

*EXCLUDE to create a filter that monitors for all SQL jobs outside of the specified Subsystem.

4.10 Monitoring Your SQL Statement Events with VISUAL Message CenterOnce you have defined the events that you would like to monitor on the iSeries, and started the SQL

monitor, you will start receiving those events at the SmartConsole.

The SmartConsole is shown below:

Note By default, if you do not set up any filtering Inclusion / Exclusion rules, then no SQL jobs are

monitored.

Warning Any filter that is created to Exclude selection criteria is run before any filter using the Include

option. Take care when specifying these options as you may obtain unexpected results.


SQL Monitor

Figure 16 – VISUAL Message Center SmartConsole

The new Business Views allows you to group messages and events for specific “business” or

technological areas. The first pane contains your Business Network, a business impact analyzer where

you can create and organize your Business Views into folders that reflect the structure of your

enterprise. The second pane is dedicated to the Message Grid, where you can see the messages of the

currently selected Business View.

In this case, it is great for grouping security messages – regardless of which user, subsystem, job etc.

they come from.

Note All events received from the iSeries SQL Agent have the agent code “SQL”.


SQL Monitor

4.10.1 Example – SmartConsole Message for SQL Statement Events

Figure 17 – Messages received by SmartConsole

The screenshot above shows the message as received by the SmartConsole. This specific event is

generated by an ODBC request to the AS/400 iSeries. It is important to realize that you are able to see

valuable information as real user, IP remote address, remote port and performance data, besides the

SQL statement.

The following message variables are shown:

Figure 18 – Message variables

For reference of “SQL Code” possible values, look for:

• DB2 Universal Database for iSeries SQL Messages and book codes.


SQL Monitor

4.10.2 Operation Code Valid Values• 'AL' THEN 'ALTER TABLE'

• 'CA' THEN 'CALL'

• 'CD' THEN 'CREATE DISTINCT TYPE'

• 'CF' THEN 'CREATE FUNCTION'

• 'CL' THEN 'CLOSE'

• 'CO' THEN 'COMMENT ON'

• 'CM' THEN 'COMMIT'

• 'CN' THEN 'CONNECT'

• 'CC' THEN 'CREATE SCHEMA'

• 'CG' THEN 'CREATE TRIGGER'

• 'CI' THEN 'CREATE INDEX'

• 'CP' THEN 'CREATE PROCEDURE'

• 'CS' THEN 'CREATE ALIAS'

• 'CT' THEN 'CREATE TABLE'

• 'CV' THEN 'CREATE VIEW'

• 'DC' THEN 'DECLARE CURSOR'

• 'DD' THEN 'DELETE...DELETE'

• 'DE' THEN 'DESCRIBE'

• 'DF' THEN 'DELETE...FETCH'

• 'DI' THEN 'DISCONNECT'

• 'DK' THEN 'DELETE...CLOSE'

• 'DL' THEN 'DELETE'

• 'DM' THEN 'DESCRIBE INPUT'

• 'DP' THEN 'DECLARE PROCEDURE'

• 'DR' THEN 'DROP'

• 'DT' THEN 'DESCRIBE TABLE'

• 'DU' THEN 'DELETE...UPDATE'

• 'EX' THEN 'EXECUTE'

• 'EI' THEN 'EXECUTE IMMEDIATE'

• 'FC' THEN 'FETCH...CLOSE'

• 'FD' THEN 'FETCH...DELETE'

• 'FE' THEN 'FETCH'

• 'FF' THEN 'FETCH...FETCH'


SQL Monitor

• 'FL' THEN 'FREE LOCATOR'

• 'FU' THEN 'FETCH...UPDATE'

• 'GR' THEN 'GRANT'

• 'HC' THEN 'CLOSE (Hard)'

• 'IC' THEN 'INSERT'

• 'IN' THEN 'INSERT'

• 'JR' THEN 'Recycle Prestart Job'

• 'LO' THEN 'LABEL ON'

• 'LK' THEN 'LOCK'

• 'OC' THEN 'OPEN...CLOSE'

• 'OD' THEN 'OPEN...DELETE'

• 'OF' THEN 'OPEN...FETCH'

• 'OO' THEN 'OPEN'

• 'OP' THEN 'OPEN'

• 'OU' THEN 'OPEN...UPDATE'

• 'PD' THEN 'PREPARE...DESCRIBE'

• 'PR' THEN 'PREPARE'

• 'RE' THEN 'RELEASE'

• 'RT' THEN 'RENAME'

• 'RV' THEN 'REVOKE'

• 'RO' THEN 'ROLLBACK'

• 'SI' THEN 'SELECT INTO'

• 'SC' THEN 'SET CONNECTION'

• 'SK' THEN 'SELECT INTO'

• 'SP' THEN 'SET PATH'

• 'SR' THEN 'SET RESULTS'

• 'ST' THEN 'SET TRANSACTION'

• 'SV' THEN 'SET VARIABLE'

• 'UC' THEN 'UPDATE...CLOSE'

• 'UD' THEN 'UPDATE...DELETE'

• 'UF' THEN 'UPDATE...FETCH'

• 'UP' THEN 'UPDATE'

• 'UU' THEN 'UPDATE...UPDATE'

• 'VI' THEN 'VALUES INTO'


SQL Monitor

4.10.3 Commit/Rollback Type Valid Values• 1=NO COMMIT (NC, NONE)

• 2=READ UNCOMMITTED (UR, CHG)

• 3=READ COMMITTED (CS)

• 4=REPEATABLE READ (RS, ALL)

• 5=SERIALIZABLE (RR)

4.11 SQL Monitor Database CleanupThere are two commands for SQL monitor database cleanup. The command Delete System DB Monitor

Messages (DLTDBMMSG) and Delete SQL monitor messages.

The command Delete System DB Monitor Messages (DLTDBMMSG) deletes message data collected

previously by System DB Monitor. This is also accessible from the SQL menu option 31, Delete system

DB monitor messages.


every day.

For the REORGANIZE parameter, specify whether you want to reorganize the file or not.

Reorganizing allows deleted records space to be released. By specifying REORGANIZE *YES the process

will last longer, and jobs locking the file will be ended immediately. All users must be signed off VISUAL

Message Center.

It would make sense to include this command as an IPL instruction. Database registers can

be deleted in two ways:

• *DATE: By date. With this option, database registers with a Date field value between the user-

defined dates will be deleted. Specify a start and end date for the time span where you want to

delete records. Records between these two dates will be deleted from the database. Data must

be specified in job date format. If you specify the special value *ALL for this parameter, all

database registers will be deleted.

• *KEEP: By number of days. With this option, database registers with a Date field value before N

number of days from today will be deleted. Simply specify a number of days to keep messages.

You can use this option to automate database cleanup by scheduling the B_DETECTOR/DLTDBMMSG command, specifying the number of days you want to keep messages.

The command Delete SQL Monitor Messages (DLTSQLMSG) deletes message data collected previously

by SQL monitor. This is also accessible from the SQL menu option 32, Delete SQL monitor messages.

The VISUAL Message Center SQL monitor collects data and stores it in the file called SLLOG01X.


every day.

4.11.1 Database Cleanup Using Interactive SQLYou can clean the database and remove unwanted messages using database commands.

This method does not require all users / clients to be disconnected. The field QQTIME of the database

B_DETECTOR/ SLDBM01X contains the date that the message entered the database. To delete message



SQL Monitor

To clean the database and remove unwanted messages:

Step 1. Sign on to your iSeries with a user profile that is in the B_DETECTOR authorization list, or

which has special *ALLOBJ authority.

Step 2. Start a SQL session with the command STRSQL.

Step 3. Delete the desired registers with the following SQL statement (replacing date values in

the format YYYYMMDD): DELETE FROM B_DETECTOR/SLDBM01X

WHERE QQTIME >= ‘start date’ and QQTIME <= ‘end date’

4.11.2 SQL Database Cleanup Using Interactive SQLYou can clean the database and remove unwanted messages using database commands.

This method does not require all users / clients to be disconnected. The field LQQSTIM of the database

B_DETECTOR/ SLLOG01X contains the date that the message entered the database. To delete message


To do so, follow these steps:

• Sign on to your iSeries with a user profile that is in the B_DETECTOR authorization list, or which

has special *ALLOBJ authority.

• Start a SQL session with the command STRSQL.

• Delete the desired registers with the following SQL statement (replacing date values in the

format YYYYMMDD): DELETE FROM B_DETECTOR/SLLOG01X WHERE LQQSTIM >= ‘start date’ and LQQSTIM <= ‘end date’

Example

To delete all registers with dates between 1st July 2003 and 15 July 2003 inclusive, enter: DELETE FROM B_DETECTOR/SLDBM01X WHERE QQTIME >= ‘20030701’ and QQTIME <= ‘20030721’ It is not necessary to introduce complete to and from dates. You could delete all registers up to a

certain date, for example, to delete all messages up to July 15 2003: DELETE FROM B_DETECTOR/SLDBM01X WHERE QQTIME >= ‘20030715’ You can use the command SELECT to see the resulting contents of the file

Example

To delete all registers with dates between 1st July 2003 and 15 July 2003 inclusive, enter:

DELETE FROM B_DETECTOR/SLLOG01X

WHERE LQQSTIM >= ‘20030701’ and LQQSTIM <= ‘20030721’

Continued on next page...


SQL Monitor

...continued from previous page

It is not necessary to introduce complete to and from dates. You could delete all registers up to a

certain date, for example, to delete all messages up to July 15 2003:

DELETE FROM B_DETECTOR/SLLOG01X

WHERE LQQSTIM >= ‘20030715’

You can use the command SELECT to see the resulting contents of the file.


Appendix A : SQL Monitor for ODBC_JDBC

Appendix AAppendix A: SQL Monitor for ODBC_JDBC

Under certain circumstances you may find that an iSeries system running SQL Monitor for iSeries does

not have enough CPU resources to monitor all SQL statements run by a specific list of users, because

OS/400 System Database Monitor needs to be active for a group of jobs. This group may include the

jobs of the list of users, in addition to jobs of many other users.

The System Database Monitor creates a trace which has to be filtered by SQL Monitor for iSeries to

select only SQL statements from specific lists of users. It is the creation of this trace that requires a high

amount of CPU.

To resolve this issue we recommend

• Selecting the most relevant applications or jobs where SQL statements of the specific list of

users run: interactive jobs and ODBC/JDBC service.

• Adding all the users of the list, and only these users, to the same group profile to make it easier

and more efficient to identify the jobs to monitor. We will assume in the rest of the document

that this group profile is called SUPERUSER.

The SQI monitor will monitor green screen SQL updates, deletes and inserts for the specific SUPERUSERgroup on the iSeries. SQI monitor is already installed on the iSeries and can be configured and activated

to monitor these users.

The newly redesigned ODBC/JDBC monitor (SQL Monitor for ODBC/JDBC) will monitor ODBC/JDBC.

The following sections cover the installation, configuration and activation of SQL Monitor for ODBC/

JDBC.

SQL Monitor for ODBC_JDBC includes four common ways of updating production data:

• MS Access – ODBC

• WinSQL – ODBC

• Green screen SQL on the AS400 (SQI monitor)

• SQL scripts in iSeries navigator which uses JDBC



A.1 Installing SQL Monitor for ODBC/JDBCYou will find a save file (SAVF) called SQLMONODBC in the library B_DETECTOR. It contains all objects

required for SQL Monitor for ODBC/JDBC. After installation, these objects will reside in the SQLMONODBClibrary.

Please follow the installation steps carefully:

Step 1. Restore the library SQLMONODBC from the SAVF to the B_DETECTOR library:

RSTLIB SAVLIB(SQLMONODBC) DEV(*SAVF) SAVF(B_DETECTOR/SQLMONODBC)

Step 2. Edit CL ILE source member QZDA_INIT located in file SQLMONODBC/QCLLESRC:

Step 3. Replace the default value SUPERUSER of the variable &SUPERUSER with the name of the

SUPERUSER group profile for which you want to monitor ODBC/JDBC SQL statements.

Step 4. The default library for SQL monitor for ODBC/JDBC is SQLMONODBC, but you can change

this default by changing the value of the variable &CUSTLIB with the name of the library

you have selected.

Step 5. Save QZDA_INIT source member and compile it to create a program (CRTBNDCL

command).

Step 6. Run the command CALL SQLMONODBC/CRTMSGSES PARM(‘SQLMONODBC’) to create a

message file called SLMSGSES in your installation library.

Step 7. Remember to change SQLMONODBC if you changed the library (see Step 2.)

Step 8. Finally, set the authorizations for the following objects:

Grant *PUBLIC *USE authority to the following objects:

• Program SQLMONODBC/QZDA_INIT (*PGM)

• Program SQLMONODBC/SLENDSEC (*PGM)

• Message File SQLMONODBC/SLMSGSES (*MSGF)

• Library SQLMONODBC (*LIB)

A number of programs in the SQLMONODBC library should adopt authority of an *ALLOBJ

user profile (QSECOFR for example). Use the following commands:

• CHGPGM PGM(SQLMONODBC/QZDA_INIT) USRPRF(*OWNER)

• CHGOBJOWN OBJ(SQLMONODBC/QZDA_INIT) OBJTYPE(*PGM) NEWOWN(QSECOFR) CUROWNAUT(*REVOKE)

• CHGPGM PGM(SQLMONODBC/SLENDSEC) USRPRF(*OWNER)

• CHGOBJOWN OBJ(SQLMONODBC/SLENDSEC) OBJTYPE(*PGM) NEWOWN(QSECOFR) CUROWNAUT(*REVOKE)

A.2 Configuring and Starting the monitor

A.2.1 Configure SQL Monitor for iSeries



Next you must configure SQL Monitor to work with SQL Monitor for ODBC/JDBC. The steps are as

follows:

Step 1. If SQL Monitor is active, end it with option 4 in SQL Monitor menu (GO B_DETECTOR/SQL_MENU).

Step 2. Open the main menu of the SQL monitor (GO B_DETECTOR/SQL_MENU) and select option

1 – SQL Monitor Maintenance.

Press (F6) to insert an entry to filter SQL statements only by SUPERUSER group profile

(all other parameters are set to *ALL).

Replace SUPERUSER with the name you have selected for the group profile (see Step 2.

of the installation).

Figure 19 – Insert an entry to filter SQL statements only by SUPERUSER group profile

Step 3. Open the main menu (GO B_DETECTOR/SQL_MENU) and select option 3 – Configure

Monitor in SQL Monitor menu. Press F9 to display all parameters. Set the last

parameter “Execute STRDBMON for all jobs” to value *NO.

Important Before you start, make sure SQL Monitor is not active.



Figure 20 – Change SQL Monitor

Step 4. Open the main menu (GO B_DETECTOR/SQL_MENU) and select option 2. You should see

a job called SQLMONITOR in T4NICELINK subsystem with near zero CPU usage.

Figure 21 – Work with Active Jobs

Now SQL Monitor is active but is not auditing SQL statements for any application.

A.2.2 Configure SQL Monitor Customized for ODBC/JDBCThis is where we start to audit SQL statements run over ODBC/JDBC. Carry out these steps carefully:

Step 1. Run the command WRKREGINF EXITPNT(QIBM_QZDA_INIT)



Figure 22 – Work with Registration Information

Step 2. Open option 8 – Work with exit programs for this exit point and add program

SQLMONODBC/QZDA_INIT.

Remember to replace SQLMONODBC with the library you selected when you compiled

program QZDA_INIT in Step 3 of the installation.

Figure 23 – Work with Exit Programs

Step 3. In the Work with Exit Programs screen press F5 to refresh. The program should appear.



Figure 24 – Press F5 to refresh the list and view the Exit program

Step 4. Restart the ODBC service for the exit point (i.e. the audit of SQL statements for ODBC/

JDBC) to start working. Use WRKACTJOB JOB(QZDASOINIT) + F14 to locate all jobs in

your system called QZDASOINIT and end them. These jobs are usually located in the

subsystem QUSRWRK, but this may differ per OS/400 version.

Figure 25 – Locate all jobs in your system called QZDASOINIT and end them

Warning When ending QZDASOINIT jobs you will also end any active ODBC sessions.

Note If it is not possible to restart ODBC you will have to wait until new jobs QZDASOINIT are

started or until the next IPL. As the ODBC service sometimes reuses QZDASOINIT jobs it is

impossible to control when the new QZDASOINIT jobs will be started.



From this moment all new ODBC/JDBC sessions of your SUPERUSER group profile users will be audited

by SQL Monitor as long as SQL Monitor (SQLMONITOR job) is active while ODBC/JDBC sessions are

started. This is why we recommend you configure SQL Monitor to start automatically at IPL.


Appendix B : Performance Considerations

Appendix BAppendix B: Performance Considerations

The VISUAL Message Center SQL monitor for iSeries uses internal IBM DB2 technology intensively.

Make sure you understand the following requirements before activating VISUAL Message Center SQL

monitor for iSeries for the first time in a Production environment. A warning message will be shown

when you try to start the monitor. Once it’s accepted, it will no longer appear.

• This product requires that the latest PTFs are properly installed in your system.

• This product may cause high CPU usage and rapid storage occupation growth rates in

environments with applications that produce a very large number of SQL statements per

second.

A warning message referring this issue will be shown when you try to start the monitor after an

installation. Once it’s accepted, it will no longer appear.

Figure 26 – Warning message

B.1 PTFs This product requires that the latest PTFs are properly installed in your system. The VISUAL Message

Center SQL monitor for iSeries uses the system database monitor (command STRDBMON). Make sure

that any IBM PTF related to this functionality is installed on your system.



We highly recommend you properly install a recent cumulative package on your system.

Running this monitor without recent PTFs may cause unpredictable results.

B.2 CPU Usage and Rapid OccupationYou may experience high CPU usage and rapid storage occupation growth rates when using this

product in an environment that uses applications producing very large numbers of SQL statements per

second. This includes for example ill-behaved SQL applications (non-optimized applications that

produce a very large number of SQL statements per second).

The high CPU usage includes CPU used by:

• the SQL Monitor

• Monitored jobs that are executing SQL statements. This includes system database monitor

which is executed in monitored jobs, not in a server job.

B.2.1 Resolving Large OverheadIn the event running the monitor causes a big overhead, there are several options:

Check the type of SQL statements you monitor. If you only need to monitor SQL activity of interactive

jobs, you can use the VISUAL Message Center Interactive SQL monitor for iSeries, which has almost no

impact system performance

Check what information you need from the monitor. If you simply need to report on SQL activity, but do

not necessarily need real time alerts – i.e. do not need to send the SQL statements to the SmartConsole

as they occur – we recommend you change the “Send to Smart Console” parameter in the Monitor

Configuration:

• From the main SQL monitor menu open option 3 – Configure Monitor.

• Change the parameter “Send to Smart Console” to *NO

The monitor will be able to process twice the number of SQL statements in the same time and you will

save up half the disk space, as statements will not be stored in the VISUAL Message Center historical

database, but only in the VISUAL Message Center SQL monitor for iSeries log database. This option is

only available in V5R4 and later.

Tip When you first start VISUAL Message Center SQL monitor for iSeries we recommend you

keep a close eye on the monitor for the first few minutes and check whether:

- The overhead on your system is reasonable

- The monitor can process all SQL activity on your system in real time

Tip You can configure specific job related filters for the HST monitor, the SQL Monitor for

Interactive Jobs, and the SQL Monitor for Batch Jobs. See see section 4.8 - Filtering Jobs

Menu on page 25.



Reduce the number of jobs to monitor in the Monitor Configuration. You can filter jobs by unique job,

unique user profile and unique database file, dramatically reducing the CPU usage.

Narrow down the number of jobs to monitor in complex environments with option 1 – Monitor

Maintenance. With this solution the system database monitor will still run in all jobs, however the jobs

are filtered and reduce part of the process, as well as disk occupation.

If you can identify a number of unique jobs to monitor, you can minimize overhead by monitoring only

those essential jobs. In the Monitor Configuration (option 3)

Change the parameter “Execute STRDBMON for all jobs” to *NO

Use the “Add job to SQL Monitor“ command (ADDDBMJOB) for each unique job you want to monitor,

before it begins executing SQL statements. If you no longer want to monitor a job started with

ADDDBMJOB you can use the "Remove job from SQL Monitor" command (RMVDBMJOB) to remove a

unique job.

B.3 Processing Speed By processing speed we mean the number of SQL statements VISUAL Message Center SQL monitor

for iSeries can process per second and CPW. This number varies depending on the operating system

on which SQL monitor is run and whether the events are sent to the SmartConsole in real time. In

addition this number may differ depending on the type and size of the SQL statement. The following

numbers are presented as a rough guide to determining the maximum number of SQL statements

VISUAL Message Center SQL monitor for iSeries can process on your system.

Note This option is only helpful in simple environments, as you can only enter unique entries or

generics, but not lists.

This option is only available in V5R4 and later.

V5R3 V5R4

Real time Historic

SQL statements per second and CPW

0.25 0.33 0.66



Again, these numbers should only be used as a rough guide. You should monitor your system and

adjust the filters in SQL monitor to match actual performance in your environment.

Example

A model 520 with processor feature 7397 has

• 2400 CPW and 1 processor,

• using full system resources,

• a SQLMONITOR running and using 50% of the partition CPU,

in V5R4, SQL monitor would be able to process

• 400 statements per second, if the events are also being sent to the SmartConsole in real

time

• 800 if they are only stored in the VISUAL Message Center SQL monitor for iSeries log

database (historic reports).


Appendix C : Contacting Tango/04

Appendix CAppendix C: Contacting Tango/04

EMEA (European, Middle-Eastern & African) Headquarters

Tango/04 Computing Group S.L.

Avda. Meridiana 358, 12 B-C

08027 Barcelona Spain

Phone: +34 93 274 0051

Fax: +34 93 345 1329

[email protected]

www.tango04.com

Latin American Headquarters

Barcelona/04 Computing Group SRL

Avda. Federico Lacroze 2252, Piso 6

1426 Buenos Aires Capital Federal

Argentina

Phone: +54 11 4774-0112

Fax: +54 11 4773-9163

[email protected]

www.barcelona04.com

North America (USA & Canada)

Tango/04 Computing Group USA

PO Box 3301

Peterborough, NH 03458 USA

Phone: 1-800-304-6872

Fax: 858-428-2864

[email protected]

www.tango04.com

Sales Office in Brazil

Tango/04 Computing Group Brasil

Rua Turiassú, 591 - 5º Andar

Perdizes

Cep: 05005-001 São Paulo

Brasil

Phone: +55 (11) 3675 6228

Fax: +51 1 211-2526

[email protected]

www.tango04.com.br

Sales Office in Chile

Barcelona/04 Computing Group Chile

Guardia Vieja 255, Of. 1601

Providencia

Santiago

Chile

Phone: +56 2 234 0898

Fax: +56 2 234 0865

[email protected]

www.barcelona04.com

Sales Office in Columbia

Barcelona/04 Computing Group Colombia

Calle 125 nº 19-89, Piso 5º

Bogotá, D.C.

Colombia

Phone: + 57(1) 658 2664

Fax: +51 1 211-2526

[email protected]

www.barcelona04.com


Mailto:[email protected]

www.tango04.com


www.barcelona04.com


www.barcelona04.com


www.tango04.com


www.barcelona04.com

www.tango04.com.br


www.barcelona04.com

www.barcelona04.com

Sales Office in Peru

Barcelona/04 Computing Group Perú

Calle Isaac Albeniz 555, Dpto 201 Urb

Las Magnolias

San Borja

L 27 Lima

Perú

Phone: +51 1 640-9168

Fax: +51 1 211-2526

[email protected]

www.barcelona04.com

Sales Office in Italy

Tango/04 Computing Group Italy

Viale Garibaldi 51

13100 Vercelli VC Italy

Phone: +39 0161 56922

Fax: +39 0161 259277

[email protected]

www.tango04.it



www.tango04.it


www.barcelona04.com

About Tango/04 Computing Group

Tango/04 Computing Group is one of the leading developers of systems management and automation

software. Tango/04 software helps companies maintain the operating health of all their business

processes, improve service levels, increase productivity, and reduce costs through intelligent

management of their IT infrastructure.

Founded in 1991 in Barcelona, Spain, Tango/04 is an IBM Business Partner and a key member of IBM's

Autonomic Computing initiative. Tango/04 has more than a thousand customers who are served by over

35 authorized Business Partners around the world.

Alliances

Awards

Partnerships IBM Business Partner

IBM Autonomic Computing Business Partner

IBM PartnerWorld for Developers Advanced Membership

IBM ISV Advantage Agreement

IBM Early code release

IBM Direct Technical Liaison

Microsoft Developer Network

Microsoft Early Code Release


Legal Notice

The information in this document was created using certain specific equipment and environments, and it is limited in

application to those specific hardware and software products and version and releases levels.

Any references in this document regarding Tango/04 Computing Group products, software or services do not mean

that Tango/04 Computing Group intends to make these available in all countries in which Tango/04 Computing Group

operates. Any reference to a Tango/04 Computing Group product, software, or service may be used. Any functionally

equivalent product that does not infringe any of Tango/04 Computing Group's intellectual property rights may be used

instead of the Tango/04 Computing Group product, software or service

Tango/04 Computing Group may have patents or pending patent applications covering subject matter in this

document. The furnishing of this document does not give you any license to these patents.

The information contained in this document has not been submitted to any formal Tango/04 Computing Group test

and is distributed AS IS. The use of this information or the implementation of any of these techniques is a customer

responsibility, and depends on the customer's ability to evaluate and integrate them into the customer's operational

environment. Despite the fact that Tango/04 Computing Group could have reviewed each item for accurateness in a

specific situation, there is no guarantee that the same or similar results will be obtained somewhere else. Customers

attempting to adapt these techniques to their own environments do so at their own risk. Tango/04 Computing Group

shall not be liable for any damages arising out of your use of the techniques depicted on this document, even if they

have been advised of the possibility of such damages. This document could contain technical inaccuracies or

typographical errors.

Any pointers in this publication to external web sites are provided for your convenience only and do not, in any

manner, serve as an endorsement of these web sites.

The following terms are trademarks of the International Business Machines Corporation in the United States and/or

other countries: iSeries, iSeriese, iSeries, i5, DB2, e (logo)®Server IBM ®, Operating System/400, OS/400, i5/OS.

Microsoft, SQL Server, Windows, Windows NT, Windows XP and the Windows logo are trademarks of Microsoft

Corporation in the United States and/or other countries. Java and all Java-based trademarks and logos are

trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and/or other countries. UNIX is a

registered trademark in the United States and other countries licensed exclusively through The Open Group. Oracle

is a registered trade mark of Oracle Corporation.

Other company, product, and service names may be trademarks or service marks of other companies.


iseries sql agent - helpsystems

Documents