New SQL Table Function for Activation Group information

New release IBM i 7.5 and the corresponding Technology Refresh for 7.4, TR6, has given us a SQL Table Function that allows us to see information about the active activation groups within a job. In the past I have been able to get this information using the Work With Job command, WRKJOB:

WRKJOB JOB('*') OPTION(*ACTGRP)

The output options are limited to display ( * ) or printed output ( *PRINT ). For years I have taken printed output from commands and broken them apart into files either using SQL or RPG. It is nicer to have a View or Table Function where I do not have to do this anymore.

The new Table Function is ACTIVATION_GROUP_INFO, that is found in the QSYS2 library. It has three input parameters:

1. JOB_NAME:  Job name
2. INTERNAL_JOB_ID:  Internal job id
3. IGNORE_ERRORS  Valid values are YES and NO

Only the job name or internal job id can be used, not both.

For my examples I am only going to be using the job name. If * is used then the data for the current job is returned. Or I can give a full job name, which is what I will be using.

The syntax is:

SELECT * FROM TABLE(QSYS2.ACTIVATION_GROUP_INFO('000000/SIMON/JOB_NAME')) ;

I have not used the first parameter's string ( JOB_NAME => ) this is optional as job name is the first parameter.

I used ACS's Run SQL Scripts to run the SQL statements for a number of 5250 jobs. For all of these jobs I used the following SQL statement:

01 SELECT ACTIVATION_GROUP_NAME AS "Group", 02 ACTIVATION_GROUP_NUMBER AS "Number", 03 STATE, 04 PROGRAM_LIBRARY AS "Library", 05 PROGRAM,PROGRAM_TYPE 06 FROM TABLE(QSYS2.ACTIVATION_GROUP_INFO('000000/SIMON/QPADEV0000')) ;

I chose the following columns for my results:

• ACTIVATION_GROUP_NAME:  Name of the activation group. To save space in the results I have given this the column heading "Group".
• ACTIVATION_GROUP_NUMBER:  Activation group number. I have this column the heading "Number"
• STATE:  State of the activation group. Either SYSTEM, when it is a system state, or USER when it is a user state
• PROGRAM_LIBRARY:  Library where the program that caused this activation group to be created. I have given this column the heading "Library"
• PROGRAM:  Name of program that caused activation to be created
• PROGRAM_TYPE:  Type of program: JAVA, PGM, or SRVPGM

There are other columns, but these were the ones I found the most interesting.

When I ran the SQL statement in a brand new 5250 job my results were as follows:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM

I created the following RPG program, I called PGM1:

01 **free //PGM1 02 ctl-opt dftactgrp(*no) ; 03 dsply ('DFTACTGRP(*NO)') ; 04 return ;

Line 1: I always use totally free format RPG.

Line 2: I am using the control option to say that I did not want to use the default activation group.

Line 3: This is really irrelevant; it was a way I used to be able to make sure I called the correct program.

After compiling the program, I called it, and then ran the SQL statement for ACTIVATION_GROUP_INFO. The results were:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM QILE 30 USER MYLIB PGM1 PGM

A row for the QILE activation group was added.

I can remove that activation group entry by use of the Reclaim Activation Group command, RCLACTGRP

RCLACTGRP ACTGRP(QILE)

After I ran the command I then ran ACTIVATION_GROUP_INFO again, and the entry for QILE was not present.

I said default activation group no in PGM1, and I wanted yes in program PGM2:

01 **free //PGM2 02 ctl-opt dftactgrp(*yes) ; 03 dsply ('DFTACTGRP(*YES)') ; 04 return ;

As I have directed this program to use the default activation group, line2, I was not surprised when the results from ACTIVATION_GROUP_INFO was unchanged from the one at the start of the job.

With PGM3 I used a different control option to see what it would do:

01 **free //PGM3 02 ctl-opt actgrp(*new) ; 03 dsply ('ACTGRP(*NEW)') ; 04 return ;

When I ran Table Function after calling this program the results was the same as the start.

The next time I gave the activation group a name:

01 **free //PGM4 02 ctl-opt actgrp('Simon') ; 03 dsply ('ACTGRP(Simon)') ; 04 return ;

When I ran the ACTIVATION_GROUP_INFO statement I could see a row for my named activation group:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM Simon 20 USER MYLIB PGM4 PGM

When I went to remove the new activation group I received an error message:

RCLACTGRP ACTGRP(SIMON) Activation group SIMON not found.

What happens if I give the activation group name in mixed case, another error:

RCLACTGRP ACTGRP('Simon') Value 'Simon ' for parameter ACTGRP not a valid name.

I changed PGM4's activation group statement to be all upper case:

01 **free //PGM4 02 ctl-opt actgrp('SIMON') ; 03 dsply ('ACTGRP(SIMON)') ; 04 return ;

After calling the program I can see the new SIMON activation group in my results:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM SIMON 24 USER MYLIB PGM4 PGM

I created another program with another activation group.

01 **free //PGM5 02 ctl-opt actgrp('Another') ; 03 dsply ('ACTGRP(Another)') ; 04 return ;

I called it and check the active activation groups:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM SIMON 20 USER MYLIB PGM4 PGM Another 21 USER MYLIB PGM5 PGM

Then I called PGM1 and checked the activation group list:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM SIMON 24 USER MYLIB PGM4 PGM Another 25 USER MYLIB PGM5 PGM QILE 26 USER MYLIB PGM1 PGM

I can see my three activation groups as numbers 24, 25, and 26.

Let me reclaim SIMON:

RCLACTGRP ACTGRP(SIMON)

When I checked again only Another and QILE remain of the three I added:

Group Number STATE Library PROGRAM PROGRAM_TYPE ---------- ------ ------ ------- ------- ------------ *DFTACTGRP 1 SYSTEM <NULL> <NULL> <NULL> *DFTACTGRP 2 USER <NULL> <NULL> <NULL> QLGLOCAL 17 SYSTEM QSYS QLGLOCAL SRVPGM QSQCLI 18 SYSTEM QSYS QSQCLI SRVPGM Another 25 USER MYLIB PGM5 PGM QILE 26 USER MYLIB PGM1 PGM

This is all good stuff to know for any job in any of my partitions.

 

You can learn more about the ACTIVATION_GROUP_INFO SQL Table Function from the IBM website here.

 

This article was written for IBM i 7.5 and 7.4 TR6.