This might be a place to start ... credit to Bryan Dietz!
SELECT Aj.Subsystem
,Aj.Job_Name
,Aj.Authorization_Name AS "Current User"
,Aj.Client_Ip_Address
,Aj.Cpu_Time AS "CPU MS Used"
,Aj.Temporary_Storage as "Temp Storage"
,Aj.Total_Disk_Io_Count as " Disk I/O Count"
,Aj.Sql_Statement_Status
,replace(replace(replace(trim(Aj.Sql_Statement_Text),' ',' ' concat char(2)), char(2) concat ' ',''),char(2), '' ) AS "Last SQL Statement"
FROM TABLE (
Qsys2.Active_Job_Info(
Reset_Statistics => 'NO', Job_Name_Filter => 'QZDA*', Detailed_Info => 'ALL')
) AS Aj
WHERE 1 = 1
--- Leave commented out for all ODBC jobs or use the 2 lines below to select specifics
--- and Aj.Authorization_Name LIKE '%%' -- enter a user name to search
--- and Aj.Client_Ip_Address in ('','1.2.3.4') -- enter IP addresses to search
--- Uncomment either 'Completed' SQL statements or 'Active' SQL statements or comment out both to output both.
--- and Sql_Statement_Status = 'COMPLETE'
--- and Sql_Statement_Status = 'ACTIVE'
--- ORDER By "Current User"
--- ORDER BY CLIENT_IP_ADDRESS
ORDER BY Aj.AUTHORIZATION_NAME, Aj.SQL_STATEMENT_STATUS;
--- ORDER BY Aj.AUTHORIZATION_NAME
Respectfully,
Michael Mayer
IBM i on Power System Admin
ERMCO-ECI
2225 Industrial Rd
Dyersburg, Tennessee 38024
Office and OnCall: 731.676.4318
Cell: 518.641.8906
Email: michael.mayer@xxxxxxxxxxxxx
https://www.ermco-eci.com/
IBM i Personal Blog:
https://ibmireference.blogspot.com/
"Success is not final. Failure is not fatal. It is the courage to continue that counts".
Today's Topics:
1. Looking for a user exit or something similar to monitor SQL
commands being used. (a4g atl)
2. Re: Looking for a user exit or something similar to monitor
SQL commands being used. (Marc Rauzier)
3. Re: SAV & CPF38A5 (Justin Taylor)
4. Re: Looking for a user exit or something similar to monitor
SQL commands being used. (Mark Waterbury)
5. DB2 Table: Is there any valid reason to create a zoned
decimal column? (Osborne, Wynn)
6. Re: DB2 Table: Is there any valid reason to create a zoned
decimal column? (James H. H. Lampert via MIDRANGE-L)
----------------------------------------------------------------------
message: 1
date: Fri, 8 Sep 2023 13:26:02 -0400
from: a4g atl <a4ginatl2@xxxxxxxxx>
subject: Looking for a user exit or something similar to monitor SQL
commands being used.
I need to track who is using SQL to do stuff in our database.
I have worked with the user exit, QIBM_DB_OPEN but that only gives me the file, library and user accessing the file.
I want to be able to receive the SQL command so that I can validate
1. who is executing the command
2. are they doing a SELECT, INSERT, UPDATE, DELETE....
3. where the call is being made from.
With so many tools available today on a PC or UNIX system and SQL being learnt by so many, it is easy for anyone to do what they want.with the database.
We also have many users accessing the system using ODBC and Excel.
Does anyone know of another user exit or similar process that I can use to monitor the SQL usage? The process needs to be able to return a result that will block the user from executing the command.
TIA, Darryl Freinkel.
------------------------------
message: 2
date: Fri, 8 Sep 2023 19:43:04 +0200
from: Marc Rauzier <marc.rauzier@xxxxxxxxx>
subject: Re: Looking for a user exit or something similar to monitor
SQL commands being used.
Le 08/09/2023 ? 19:26, a4g atl a ?crit?:
I need to track who is using SQL to do stuff in our database.
I have worked with the user exit, QIBM_DB_OPEN but that only gives me
the file, library and user accessing the file.
I want to be able to receive the SQL command so that I can validate
1. who is executing the command
2. are they doing a SELECT, INSERT, UPDATE, DELETE....
3. where the call is being made from.
Do you find what you need in a Plan Cache snapshot?
You can use the DUMP_CACHE_PROCEDURE to create a table with SQL statements.
https://www.ibm.com/docs/en/i/7.5?topic=services-dump-plan-cache-procedure
Be aware that the Plan Cache is cleared at IPL time, so you might need to run this procedure just before a power down. It might also be cleaned up during normal operations, so monitoring this event might also be useful
https://www.ibm.com/docs/en/i/7.5?topic=services-start-plan-cache-event-monitor-procedure
With so many tools available today on a PC or UNIX system and SQL
being learnt by so many, it is easy for anyone to do what they
want.with the database.
We also have many users accessing the system using ODBC and Excel.
Does anyone know of another user exit or similar process that I can
use to monitor the SQL usage? The process needs to be able to return a
result that will block the user from executing the command.
TIA, Darryl Freinkel.
------------------------------
message: 3
date: Fri, 8 Sep 2023 13:05:31 -0500
from: Justin Taylor <jtaylor.0ab@xxxxxxxxx>
subject: Re: SAV & CPF38A5
I'll give that a go. Thank you
date: Fri, 8 Sep 2023 10:57:18 -0400
from: Rob Berendt <robertowenberendt@xxxxxxxxx>
subject: Re: SAV & CPF38A5
This works:
SAV DEV('/qsys.lib/rob.lib/rob.file') OBJ(('/home'))
PATTERN(('.cache')) It saved '/home/redacted/IBM/iAccessClient/JarCache/.cache'
------------------------------
message: 4
date: Fri, 8 Sep 2023 18:10:09 +0000 (UTC)
from: Mark Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>
subject: Re: Looking for a user exit or something similar to monitor
SQL commands being used.
Darryl,
See:
? ? ?
https://archive.midrange.com/midrange-l/201708/msg00417.html?
And see also:
? ? ?
https://www.itjungle.com/2006/11/08/fhg110806-story01/
Hope that helps,
Mark S. Waterbury
On Friday, September 8, 2023 at 01:25:15 PM EDT, a4g atl <a4ginatl2@xxxxxxxxx> wrote:
I need to track who is using SQL to do stuff in our database.
I have worked with the user exit, QIBM_DB_OPEN but that only gives me the file, library and user accessing the file.
I want to be able to receive the SQL command so that I can validate
? 1. who is executing the command
? 2. are they doing a SELECT, INSERT, UPDATE, DELETE....
? 3. where the call is being made from.
With so many tools available today on a PC or UNIX system and SQL being learnt by so many, it is easy for anyone to do what they want.with the database.?
We also have many users accessing the system using ODBC and Excel.
Does anyone know of another user exit or similar process that I can use to monitor the SQL usage? The process needs to be able to return a result that will block the user from executing the command.
TIA, Darryl Freinkel.
------------------------------
message: 5
date: Fri, 8 Sep 2023 20:25:19 +0000
from: "Osborne, Wynn" <Wynn.Osborne@xxxxxxxxxxxxxxxxxx>
subject: DB2 Table: Is there any valid reason to create a zoned
decimal column?
Hi all,
This question arose within me while putting together some database standards for my team.
I cannot think of valid reason why I would ever this
What about you? Have you come across a scenario where this was good choice?
Regards,
Wynn Osborne
Senior IBM i Programmer-Analyst,Database Administrator The Villages
------------------------------
message: 6
date: Fri, 8 Sep 2023 13:37:02 -0700
from: "James H. H. Lampert via MIDRANGE-L"
<midrange-l@xxxxxxxxxxxxxxxxxx>
subject: Re: DB2 Table: Is there any valid reason to create a zoned
decimal column?
On 9/8/23 1:25 PM, Osborne, Wynn wrote:
This question arose within me while putting together some database standards for my team.
I cannot think of valid reason why I would ever this
Readability from extreme low-level tools like DSPPFM, or EDTF?
--
JHHL
------------------------------
Subject: Digest Footer
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
------------------------------
End of MIDRANGE-L Digest, Vol 22, Issue 827
*******************************************
midrange.com
