Regular expression support requires the ICU option be installed. I doubt that will perform any faster.
Your best bet to improve performance is to specify more filtering criteria because the Lower predicate is pretty expensive. If it's truly only Selects, then filtering can be done on QQC21. Also, joining to the 3000 row would be a faster way to find all the queries that reference your table than searching the statement text
My team can be engaged to provide education on how to efficiently search PC Snapshots
------------------------------
Kent Milligan
------------------------------
Original Message:
Sent: Fri July 15, 2022 04:23 PM
From: Patrick Conner
Subject: How long did a query take to run?
So, I went with
SELECT qqi6/1000000/qvp15f, qqi6, qq1000
FROM ASIPWC.CHK_PLAN
WHERE qqrid=1000 AND LOWER( qq1000 ) LIKE '%pfstknmbr%'
I was surprised to see the query takes twice as long to run at the customer site. I ran another query that tried to use a regular expression and received the following error:
QQQSVREG in QSYS type *SRVPGM not found
The customer is at V7R4. Regular expressions should be included. Does the error mean anything to you or indicate a possible performance problem?
------------------------------
Patrick Conner
Original Message:
Sent: Fri July 15, 2022 03:14 PM
From: Kent Milligan
Subject: How long did a query take to run?
As you've discovered, STRSQL does not have the ability to display a stored procedure result set.
So you have two options with STRSQL:
1) Use the Extract_Statements output table output and then use STRSQL to query the output table.
2) Extract_Statements is running a query against the snapshot table which you could do yourself.
SELECT qqi6, qq1000 FROM ASIPWC.CHK_PLAN
WHERE qqrid=1000 AND LOWER( qq1000 ) LIKE ''%pfstknmbr%''
The columns for the Snapshot/Monitor table are documented in the Database Performance & Query Optimization book
------------------------------
Kent Milligan
Original Message:
Sent: Fri July 15, 2022 10:57 AM
From: Patrick Conner
Subject: How long did a query take to run?
Kent,
I used interactive sql (StrSQL) at my customer's site. I ran this statement yesterday:
call qSys2.dump_plan_cache('ASIPWC','CHK_PLAN')
Followed by:
call qSys2.extract_statements('ASIPWC','CHK_PLAN','*AUDIT', 'and lower( qq1000 ) like ''%pfstknmbr%'' ')
I get the following message:
Message ID . . . . . . : SQL0466 Severity . . . . . . . : 00
Message type . . . . . : Information
Message . . . . : 1 result sets are available from procedure
EXTRACT_STATEMENTS in QSYS2.
Cause . . . . . : Procedure EXTRACT_STATEMENTS in QSYS2 was called and has
returned one or more result sets.
Recovery . . . : None.
When I perform the similar commands in-house using Run SQL Scripts, the results show.
What do I do next on my customer site to see the results?
Also, back to the results that show from my in-house system, the start and end times are identical which contradicts the 3 second processing time shown via the SQL Plan Cache Statements run from the SQL Performance Center. My goal is to determine how long the select statements take to run. Do I need to run other statements after the extract_statements call?
------------------------------
Patrick Conner
Original Message:
Sent: Fri July 15, 2022 10:16 AM
From: Kent Milligan
Subject: How long did a query take to run?
You would need to first have your clients create a PC Snapshot using the Dump_Plan_Cache service. It would be very easy to help your clients schedule a daily/weekly job that creates the PC Snapshot.
------------------------------
Kent Milligan
Original Message:
Sent: Thu July 14, 2022 08:47 PM
From: Patrick Conner
Subject: How long did a query take to run?
On our internal systems I can go to SQL Plan Cache Statements and see the average processing time for recently run select statements. I only have telnet access to our customers' systems. Can I run a query over a system table/view that provides the run time of recently processed select statements?
------------------------------
Patrick Conner
------------------------------
#SQL