Get control of ODBC jobs on your IBM i (iSeries, AS/400, System i) servers with block, change, hold, end and alert features.
The 'Work with Active Jobs (WRKACTJOB)' command from IBM shows all active jobs on the system. Systems administrators watch to see what is consuming the most resources and causing poor response time.
When it is remote SQL there are QZDASOINIT jobs consuming a lot of resources but systems administrators have no idea what these jobs are doing. These jobs are running SQL but they don't know what SQL requests are running or the code associated with it - only that the jobs are consuming a lot of resources. There is little visibility or control over these client/server, mobile and web based applications running SQL against the system.
Our 'Work with Active SQL (WRKACTSQL)' command gives visibility and control over remote ODBC requests showing the actual SQL statements that are impacting the system in real time so systems administrators can react appropriately. They can hold, terminate, or change run priority for specific SQL requests that are actively and negatively impacting system performance. They can also set up alerts to automatically and immediately be notified when this user runs this SQL request again. If needed, specific users and specific SQL requests can be blocked and prevented from even executing again on the system.
This command uses an exit point program to track active SQL requests and passes them to a background batch job to process them and prepare them for the WRKACTSQL command display. The exit point program and the background batch job both need to be configured. Run the WRKREGINF EXITPNT(QIBM_QZDA_SQL*) command then '8=Work with exit programs' on both entries. Do you currently have any exit programs for these two exit points? If you do, additional configuration is needed to ensure that these existing exit point programs coexist with our WRKACTSQL exit point program. Are you installing on a live Production system/LPAR? If you are, additional data collection is recommended to ensure that activation of real-time SQL tracking does not have a negative affect on the performance of your live system and Production users.
It is recommended that you run an SQL data collection using our Query Optimizer tool that is part of the 30 day trial that you also installed. You should do this prior to activating the WRKACTSQL command to determine whether some SQL tuning would be a better first step, prior to enabling real-time SQL tracking. This data will give you a good understanding of SQL traffic on your system prior to WRKACTSQL command activation. Use the following procedure to do this:
- select option 9 from the EZRAD WORKPERF command/menu
- press F8=Start scheduler
- find QUERYOPT and use '1=Start now'
- wait for QUERYOPT job in EZRAD.COM subsystem to finish
- select option 3 from EZRAD WORKPERF menu
- press F4=Lookup with cursor in Sample field
- use 1=Select on sample and press ENTER
Below is an example of the type of SQL performance data that you will see. In this example, the number of SQL requests per day is reasonable but there are some fairly large table scan and indexing issues. 11,171 requests table scanned 1.891 billion records to select 314,332 rows. This was almost 2 billion unnecessary I/Os on your system, significantly impacting system and application performance. Please consider the 30 day software trial of our Workload Performance Series software and it's complimentary performance review to analyze this data further. Here our biggest concern is the number of 'Queries' per day. When activated, our WRKACTSQL exit point program will be called this number of times per day. If your data shows an excessively high number of queries, please consider tuning before activating real-time SQL tracking via our WRKACTSQL command.
Select option 8 from the EZRAD WORKPERF command/menu and copy/paste 'System' serial number and LPAR id into the field below then click the Submit button.
RMVEXITPGM EXITPNT(QIBM_QZDA_SQL2) FORMAT(ZDAQ0200) PGMNBR(1)