Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

------------------------------
Avinash Chandra Mandavilli
------------------------------

Dear Avinash

I see that using DB2i Plan Cache Snapshot dump can be one good way to satisfy your need here.  All SQL (and Queries as of IBM i 7.2) access plans which include the corresponding statements are stored in Plan Cache and you can dump into a table (named QZG....) its content with a filter for a specific user profile of the jobs that run SQL statements and Queries from IBM i ACS main window. (The pictures here are from the old System i Navigator for Windows.)




There is also a DUMP_PLAN_CACHE procedure you can use programmatically but it somehow has no filter capability. You dump the entire content.  Here is a full set of system-provided SQL procedures you can call to programmatically manipulate DB2i Plan Cache : Accessing the SQL plan cache with SQL stored procedures at  https://www.ibm.com/docs/en/i/7.2?topic=cache-sql-stored-procedures.  As of IBM i 7.3, these were changed into Plan Cache Services : https://www.ibm.com/docs/en/i/7.4?topic=services-plan-cache.

DB2i Plan Cache is an integrated part of DB2i SQL engine and it runs at all times. All its content is cleared away at IPL time and it starts storing its data from scratch after the IPL. 

Plan Cache has an initial size that can be adjusted. So, you may need to do trial and error to make sure it is set large enough to store all statements (in a day or week as you need - a month may need a very huge size if you have a lot of SQL + Queries running in your system and can afford the disk space) before you dump it (create a snapshot) with a filter for the user profile of your interest and query the result from the resulting QZG..... table. 

To view the Plan Cache properties, select the SQL Performance Center from the main ACS window or from the Tools menu of any ACS window.

The first tab in the SQL Performance Center window that appears will display all of the Plan Cache properties. Changes to configurable properties can be made by clicking the Change Configuration... button.


Several of the properties in the screen above (e.g. its max size) can be changed by right-clicking a property and selecting Edit.   Explanation for all Plan Cache properties are here : https://www.ibm.com/docs/en/i/7.4?topic=cache-properties.

I attach an article herewith for your further reading to get to know more about it. 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Wed April 05, 2023 12:35 PM
From: Avinash Chandra Mandavilli
Subject: Extract/Audit STRSQL statements of a specific user

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

------------------------------
Avinash Chandra Mandavilli
------------------------------

Thank you Satid for the response. I will give a try and keep you posted 

Dear Avinash

I see that using DB2i Plan Cache Snapshot dump can be one good way to satisfy your need here.  All SQL (and Queries as of IBM i 7.2) access plans which include the corresponding statements are stored in Plan Cache and you can dump into a table (named QZG....) its content with a filter for a specific user profile of the jobs that run SQL statements and Queries from IBM i ACS main window. (The pictures here are from the old System i Navigator for Windows.)




There is also a DUMP_PLAN_CACHE procedure you can use programmatically but it somehow has no filter capability. You dump the entire content.  Here is a full set of system-provided SQL procedures you can call to programmatically manipulate DB2i Plan Cache : Accessing the SQL plan cache with SQL stored procedures at  https://www.ibm.com/docs/en/i/7.2?topic=cache-sql-stored-procedures.  As of IBM i 7.3, these were changed into Plan Cache Services : https://www.ibm.com/docs/en/i/7.4?topic=services-plan-cache.

DB2i Plan Cache is an integrated part of DB2i SQL engine and it runs at all times. All its content is cleared away at IPL time and it starts storing its data from scratch after the IPL. 

Plan Cache has an initial size that can be adjusted. So, you may need to do trial and error to make sure it is set large enough to store all statements (in a day or week as you need - a month may need a very huge size if you have a lot of SQL + Queries running in your system and can afford the disk space) before you dump it (create a snapshot) with a filter for the user profile of your interest and query the result from the resulting QZG..... table. 

To view the Plan Cache properties, select the SQL Performance Center from the main ACS window or from the Tools menu of any ACS window.

The first tab in the SQL Performance Center window that appears will display all of the Plan Cache properties. Changes to configurable properties can be made by clicking the Change Configuration... button.


Several of the properties in the screen above (e.g. its max size) can be changed by right-clicking a property and selecting Edit.   Explanation for all Plan Cache properties are here : https://www.ibm.com/docs/en/i/7.4?topic=cache-properties.

I attach an article herewith for your further reading to get to know more about it. 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Wed April 05, 2023 12:35 PM
From: Avinash Chandra Mandavilli
Subject: Extract/Audit STRSQL statements of a specific user

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

------------------------------
Avinash Chandra Mandavilli
------------------------------

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

One way is by the use of "exit points".  For some detail you can read the following:  https://www.ibm.com/docs/en/i/7.5?topic=parameters-database-server
You can write your own exit point programs.  I have written my own for ftp.  However there are several competitive products to do this for you.  This site lists a few:  https://wiki.midrange.com/index.php/Exit_point_security_tools

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello Avinash Chandra Mandavilli.

What you need is en Exit Security Package, dumping the Plan Cache or using it does not guarantee you have all the SQL statements requested. The Plan Cache has the option to remove/purge a plan. Which makes it is not longer traceable in the Plan Cache. 

There are solutions on the market which will do not cost you much. 

Greetings, 

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello Rudi,
Whatever free license options are available, we will give them a shot. If the results are not guaranteed, the audit team will be informed accordingly.
I'm really not sure if the internals would go for a licensed product.

------------------------------
Avinash Chandra Mandavilli

Original Message:
Sent: Thu April 06, 2023 08:38 AM
From: Rudi Van Helvoirt
Subject: Extract/Audit STRSQL statements of a specific user

Hello Avinash Chandra Mandavilli.

What you need is en Exit Security Package, dumping the Plan Cache or using it does not guarantee you have all the SQL statements requested. The Plan Cache has the option to remove/purge a plan. Which makes it is not longer traceable in the Plan Cache. 

There are solutions on the market which will do not cost you much. 

Greetings, 

------------------------------
Rudi Van Helvoirt

Original Message:
Sent: Wed April 05, 2023 12:35 PM
From: Avinash Chandra Mandavilli
Subject: Extract/Audit STRSQL statements of a specific user

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

------------------------------
Avinash Chandra Mandavilli
------------------------------

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello Avinash,
There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!
Best regards

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello Avinash,

There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!

Best regards, Guy Marmorat - IBM Champion

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello Guy,

Thank you for the response. Your inputs make sense, but does it work for the user who is on the exit side (has already left the company)?
We have his password, and we're just checking his recorded "STRSQL" declarations. I have no intention of extracting his statements for more than 45 days, but the lines never end.

Therefore, I think the process is useful for active users. Correct me if I'm wrong.

We even tried the cache snapshot of the SQL plan, but it sounded too complex to understand.

Hello Avinash,

There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!

Best regards, Guy Marmorat - IBM Champion

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hello Guy,

Thank you for the response. Your inputs make sense, but does it work for the user who is on the exit side (has already left the company)?
We have his password, and we're just checking his recorded "STRSQL" declarations. I have no intention of extracting his statements for more than 45 days, but the lines never end.

Therefore, I think the process is useful for active users. Correct me if I'm wrong.

We even tried the cache snapshot of the SQL plan, but it sounded too complex to understand.

------------------------------
Avinash Chandra Mandavilli

Original Message:
Sent: Thu April 06, 2023 03:17 AM
From: Guy MARMORAT
Subject: Extract/Audit STRSQL statements of a specific user

Hello Avinash,

There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!

Best regards, Guy Marmorat - IBM Champion

------------------------------
Guy MARMORAT
President
RESILIANE
SAINT-OURS
+336 09 52 99 46

Original Message:
Sent: Wed April 05, 2023 12:35 PM
From: Avinash Chandra Mandavilli
Subject: Extract/Audit STRSQL statements of a specific user

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

------------------------------
Avinash Chandra Mandavilli
------------------------------

>>>>  does it work for the user who is on the exit side (has already left the company)? <<<<

With this latest information from you, it appears that the ONLY chance for you to bet on is to use Plan Cache snapshot.  If the Plan Cache's size is large enough to the point it was not already overwhelmed by all SQL/Query workload in your system since the last IPL and you have not IPLed your system since that person of your interest resigned, all SQL statements (at least the last timestamp of the repeating statements) of that user should still be in the Plan Cache for you to see. 

BTW, the QZG... table also contains a column that keeps the count of how many times (since the last IPL) each statement have run.

>>>>  We even tried the cache snapshot of the SQL plan, but it sounded too complex to understand. <<<<

Have you ever worked with DB2i Plan Cache and used IBM i Access Client Solution (ACS) GUI tool before?  If none for both, find someone you know who can help with these or read more IBM Technotes I provide you below.  Once you begin to get some feel about using these tools, it will not look very complex at all. Please also ask which point you do not understand. 

The snapshot dump file QZG.... is just a TYPICAL TABLE.  Once you dump it with the user name filter, you use Run SQL Statement tool from IBM i ACS to select the entire QZG... table (perhaps also sorted by the timestamp column).  Do not run the query from STRSQL session because it is a too restrictive an environment that causes the issue you mention about the long statement you want to see.

Otherwise, follow the Instruction from this IBM Technote  :  Getting Started with the Plan Cache Snapshot Tool at https://www.ibm.com/support/pages/getting-started-plan-cache-snapshot-tool.     You can sort the list in the last sample screen in ascending or descending order by clicking on the column name (for example Last Time Run) you want to use for sorting - first click = ascending order, next click = descending order.   Then you can right-click on any line item you are interested in to get the pop-up menu as shown and then select Work With SQL Statement to see the full statement of that particular line item. 

This IBM Technote provides additional information on how you can create another small snapshot dump table that contains just the few statements you are interested to scrutinize :  Subsetting DBMON or Plan Cache Snapshot Data for One Job or Query from a System-Wide Collection from Access Client Solutions (ACS) at  https://www.ibm.com/support/pages/subsetting-dbmon-or-plan-cache-snapshot-data-one-job-or-query-system-wide-collection-access-client-solutions-acs.

Let me know if this works for you or not. 

------------------------------
Education is not the learning of facts but the training of the mind to think. -- Albert Einstein.
------------------------------
Satid S.

Original Message:
Sent: Fri April 07, 2023 02:46 PM
From: Avinash Chandra Mandavilli
Subject: Extract/Audit STRSQL statements of a specific user

Hello Guy,

Thank you for the response. Your inputs make sense, but does it work for the user who is on the exit side (has already left the company)?
We have his password, and we're just checking his recorded "STRSQL" declarations. I have no intention of extracting his statements for more than 45 days, but the lines never end.

Therefore, I think the process is useful for active users. Correct me if I'm wrong.

We even tried the cache snapshot of the SQL plan, but it sounded too complex to understand.

------------------------------
Avinash Chandra Mandavilli

Original Message:
Sent: Thu April 06, 2023 03:17 AM
From: Guy MARMORAT
Subject: Extract/Audit STRSQL statements of a specific user

Hello Avinash,

There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!

Best regards, Guy Marmorat - IBM Champion

------------------------------
Guy MARMORAT
President
RESILIANE
SAINT-OURS
+336 09 52 99 46

Original Message:
Sent: Wed April 05, 2023 12:35 PM
From: Avinash Chandra Mandavilli
Subject: Extract/Audit STRSQL statements of a specific user

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

------------------------------
Avinash Chandra Mandavilli
------------------------------

Hello Guy,

Thank you for the response. Your inputs make sense, but does it work for the user who is on the exit side (has already left the company)?
We have his password, and we're just checking his recorded "STRSQL" declarations. I have no intention of extracting his statements for more than 45 days, but the lines never end.

Therefore, I think the process is useful for active users. Correct me if I'm wrong.

We even tried the cache snapshot of the SQL plan, but it sounded too complex to understand.

Hello Avinash,

There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!

Best regards, Guy Marmorat - IBM Champion

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated

Hi Avinash,

If you can sign on as the user and access his STRSQL history using the STRSQL command, then you can export the session history as a source physical file when you exit the session. Choose option 4 "Save session in source file". See https://www.ibm.com/support/pages/strsql-sessions-save-options for more details.

Hello Guy,

Thank you for the response. Your inputs make sense, but does it work for the user who is on the exit side (has already left the company)?
We have his password, and we're just checking his recorded "STRSQL" declarations. I have no intention of extracting his statements for more than 45 days, but the lines never end.

Therefore, I think the process is useful for active users. Correct me if I'm wrong.

We even tried the cache snapshot of the SQL plan, but it sounded too complex to understand.

Hello Avinash,

There are multiple ways to collect SQL statements.

The simplest one is Database Monitor. It covers both SQE and CQE engines.

This command starts the collection of all SQL statements for user USERXXX and places the result in FILE LIBXXX/FILEXXX: STRDBMON OUTFILE(LIBXXX/FILEXXX) JOB(USERXXX/*ALL) TYPE(*BASIC) HOSTVAR(*CONDENSED)

Note: it works for all jobs for which the job-user is USERXXX.

In order to cover other jobs more in a prestart mode, do this one : STRDBMON OUTFILE(LIBXXX/FILEXXX) OUTMBR(*FIRST *ADD) JOB(QZDASOINIT) TYPE(*BASIC) FTRUSER((USERXXX)) HOSTVAR(*CONDENSED)                          

Repeat for other prestart jobs

I would not recommend to use *ALL in the parameter user of the job - like STRDBMON JOB(*ALL/*ALL/*ALL) - especially if you have a significant SQL workload.

Then, you query the output file like this: SELECT QQTIME, QQJOB, QQUSER, substr(QQC183, 1, 15) as IP, QVC3001 as SQL_Reg_App, QQ1000 FROM LIBXXX/FILEXXX where QQRID = 1000             

The other ways are more complex or not as comprehensive as Database Monitor (exit point, dumping internal object, plan cache, …)

Hope it helps!

Best regards, Guy Marmorat - IBM Champion

Hello IBM Champs,

We have an audit requirement to capture SQL statements of a specific user profile on iSeries, i.e., to list all his SQL executed statements from either ACS -> "Run SQL script" or 5250 "green screen," i.e., STRSQL.

We also have the user profile password with us, and when we login and check his saved STRSQL statements, there is a very long line, which never ends even after doing "page up."

So, if you have any SQL inputs or direct SQL command to list statements executed by a user for a certain period (30 days), Any help could be appreciated