Scrollable cursor usage

2. RE: Scrollable cursor usage

Like

Satid Singkorapoom

Posted Tue October 24, 2023 09:48 PM

Dear Paolo

You said you wanted to monitor a job's memory consumption and you selected TEMPORARY_STORAGE for this purpose. I hope you are aware that, by design, a job in IBM i DOES NOT see system memory space at all. It only sees one large piece of virtual memory space (single-level storage) that covers both disk and system memory. This means the TEMPORARY_STORAGE amount you see can be on disk and/or system memory in any proportion. A more meaningful monitor should be checking if TEMPORARY_STORAGE is approaching ALLOWED MAXIMUM_TEMPORARY_STORAGE_ALLOWED or not. But then, I guess that most jobs have *NOMAX value for MAXIMUM_TEMPORARY_STORAGE_ALLOWED.

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
------------------------------

Original Message

Original Message:
Sent: Tue October 24, 2023 04:53 AM
From: Paolo Salvatore
Subject: Scrollable cursor usage

Hi,

I want to create a monitor then generate an alarm if a job exceed a specific quantity of memory, I'll use the sql function:

  exec sql    declare monitorRam scroll cursor for    select JOB_NAME, X.JOB_NAME_SHORT, X.JOB_USER, X.JOB_NUMBER, AUTHORIZATION_NAME, TEMPORARY_STORAGE, X.CPU_TIME, SQL_STATEMENT_TEXT      from table (            QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')        ) X      where JOB_TYPE <> 'SYS'      order by TEMPORARY_STORAGE desc;  exec sql    open monitorRam;  exec sql      fetch first from monitorRam for 100 rows into :dsMonitor :dsnull;

Image then I made this check every minutes, but before doing the next fetch, Do I have to close the cursor?

I was wondering using a scrollable cursor I dind't have to declare, open and close the cursor every time, but with fetch first I could in loop receive a new result set.

Many thanks.

------------------------------
Paolo Salvatore
------------------------------

3. RE: Scrollable cursor usage

Like

Satid Singkorapoom

Posted Tue October 24, 2023 10:09 PM
Edited by Satid Singkorapoom Tue October 24, 2023 10:11 PM

To answer your question, from this article SQL 101: Using SQL Cursors to Replace Record-Level Data Access, Part 2 at https://www.mcpressonline.com/programming/sql/sql-101-using-sql-cursors-to-replace-record-level-data-access-part-2, I see that you should be able to put the FETCH... in a loop as shown in the article's sample. After the FETCH... in the IF clause, you may want to call QSYS2.QCMDEXC to run DLYJOB command for 60 seconds. You would exit the loop and close the cursor when SQLCODE is not zero. .

------------------------------
Chance favors only the prepared mind.
-- Louis Pasteur
------------------------------
Satid S.
------------------------------

Original Message

Original Message:
Sent: Tue October 24, 2023 04:53 AM
From: Paolo Salvatore
Subject: Scrollable cursor usage

Hi,

I want to create a monitor then generate an alarm if a job exceed a specific quantity of memory, I'll use the sql function:

  exec sql    declare monitorRam scroll cursor for    select JOB_NAME, X.JOB_NAME_SHORT, X.JOB_USER, X.JOB_NUMBER, AUTHORIZATION_NAME, TEMPORARY_STORAGE, X.CPU_TIME, SQL_STATEMENT_TEXT      from table (            QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')        ) X      where JOB_TYPE <> 'SYS'      order by TEMPORARY_STORAGE desc;  exec sql    open monitorRam;  exec sql      fetch first from monitorRam for 100 rows into :dsMonitor :dsnull;

Image then I made this check every minutes, but before doing the next fetch, Do I have to close the cursor?

I was wondering using a scrollable cursor I dind't have to declare, open and close the cursor every time, but with fetch first I could in loop receive a new result set.

Many thanks.

------------------------------
Paolo Salvatore
------------------------------

4. RE: Scrollable cursor usage

Like

NIKOS METAXATOS

Posted Wed October 25, 2023 10:39 AM

Hello Paolo,

assuming your task is to monitor for Temporary Storage consumption, as well as, you are on IBM i 7.4 and Db2 group PTF SF99704 level 13 and onward or IBM i 7.3 and group PTF SF99703 level 24 and onward, or IBM i 7.5, you could have a look at the Query Supervisor: https://www.ibm.com/docs/en/i/7.4?topic=tools-query-supervisor. You can use this to either look at the total execution time (elapsed time) or at the resource consumption, such as temporary storage allocation, I/O count or CPU time consumed. The Query Supervisor allows you to define thresholds for those characteristics, and when the defined threshold is exceeded, one or multiple user written exit programs are executed.

If you Google, you will find many information on how you can easily write your own exit program.

Good luck,

regards,

Nikos

------------------------------
NIKOS METAXATOS
------------------------------

Original Message

Original Message:
Sent: Tue October 24, 2023 04:53 AM
From: Paolo Salvatore
Subject: Scrollable cursor usage

Hi,

I want to create a monitor then generate an alarm if a job exceed a specific quantity of memory, I'll use the sql function:

  exec sql    declare monitorRam scroll cursor for    select JOB_NAME, X.JOB_NAME_SHORT, X.JOB_USER, X.JOB_NUMBER, AUTHORIZATION_NAME, TEMPORARY_STORAGE, X.CPU_TIME, SQL_STATEMENT_TEXT      from table (            QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')        ) X      where JOB_TYPE <> 'SYS'      order by TEMPORARY_STORAGE desc;  exec sql    open monitorRam;  exec sql      fetch first from monitorRam for 100 rows into :dsMonitor :dsnull;

Image then I made this check every minutes, but before doing the next fetch, Do I have to close the cursor?

I was wondering using a scrollable cursor I dind't have to declare, open and close the cursor every time, but with fetch first I could in loop receive a new result set.

Many thanks.

------------------------------
Paolo Salvatore
------------------------------

5. RE: Scrollable cursor usage

Like

Kent Milligan

Posted Fri November 10, 2023 02:19 PM

I would definitely would not recommend calling this Active_Job_Info service every couple of minutes because IBM i service calls are expensive. You need to remember that this service is not just reading rows from a table, it has to perform external program calls to retrieve the job data. If you call it every couple of minutes, there's a pretty good change it will consume more time on your system than your application queries. Reference my blog entry for more considerations using IBM i services.

The scrollable cursor would need to be closed and re-opened in order to get the latest version of the Active_Job_Info result set. There's an Db2 for i SQL community if you have additional SQL questions.

------------------------------
Kent Milligan
IBM Technology Expert Labs
------------------------------

Original Message

Original Message:
Sent: Tue October 24, 2023 04:53 AM
From: Paolo Salvatore
Subject: Scrollable cursor usage

Hi,

I want to create a monitor then generate an alarm if a job exceed a specific quantity of memory, I'll use the sql function:

  exec sql    declare monitorRam scroll cursor for    select JOB_NAME, X.JOB_NAME_SHORT, X.JOB_USER, X.JOB_NUMBER, AUTHORIZATION_NAME, TEMPORARY_STORAGE, X.CPU_TIME, SQL_STATEMENT_TEXT      from table (            QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')        ) X      where JOB_TYPE <> 'SYS'      order by TEMPORARY_STORAGE desc;  exec sql    open monitorRam;  exec sql      fetch first from monitorRam for 100 rows into :dsMonitor :dsnull;

Image then I made this check every minutes, but before doing the next fetch, Do I have to close the cursor?

I was wondering using a scrollable cursor I dind't have to declare, open and close the cursor every time, but with fetch first I could in loop receive a new result set.

Many thanks.

------------------------------
Paolo Salvatore
------------------------------

6. RE: Scrollable cursor usage

Like

michael woodard

Posted Mon November 13, 2023 05:43 AM

Hello Paolo,

Agree with Nikos above. The new Query Supervisor (samples: Exit program to send message to QSYSOPR - IBM Documentation) is better choice than your query. It will only execute when the specified threshold(s) has been exceeded. This way you only consume system resources when there is a problem request/job. I believe the Query Supervisor was introduced in 7.3. As with the sample, you want to submit a separate job to perform any action against the problem request/job.

------------------------------
michael woodard
------------------------------

Original Message

Original Message:
Sent: Tue October 24, 2023 04:53 AM
From: Paolo Salvatore
Subject: Scrollable cursor usage

Hi,

I want to create a monitor then generate an alarm if a job exceed a specific quantity of memory, I'll use the sql function:

  exec sql    declare monitorRam scroll cursor for    select JOB_NAME, X.JOB_NAME_SHORT, X.JOB_USER, X.JOB_NUMBER, AUTHORIZATION_NAME, TEMPORARY_STORAGE, X.CPU_TIME, SQL_STATEMENT_TEXT      from table (            QSYS2.ACTIVE_JOB_INFO(DETAILED_INFO => 'ALL')        ) X      where JOB_TYPE <> 'SYS'      order by TEMPORARY_STORAGE desc;  exec sql    open monitorRam;  exec sql      fetch first from monitorRam for 100 rows into :dsMonitor :dsnull;

Image then I made this check every minutes, but before doing the next fetch, Do I have to close the cursor?

I was wondering using a scrollable cursor I dind't have to declare, open and close the cursor every time, but with fetch first I could in loop receive a new result set.

Many thanks.

------------------------------
Paolo Salvatore
------------------------------

IBM i Global

IBM i