Children and system managers have one thing in common, neither of them like to clean up mess. In one aspect children are different from system managers, they know which mess they should have cleaned up!

For system managers that is not always crystal clear. We all know that IBM i Access for Windows is no longer supported and never was on Windows 10. A side effect of this is that Management Central (MC) is no longer supported because supported software to manage MC no longer exists.

ProTip: If you still want Graph History and you are running 7.3 or newer then don’t worry, that data is still available but has moved to Navigator for i, more on that later.

Part of MC was Graph History, including the library QMGTC2. In this article I will show you a nice and easy way you can clean this up 🤓. 

Below is an image taken from IBM i Access for Windows showing Graph History in action:

The data from the graph resides in QMGTC. We are going to take care that this data is no longer collected, that the data in there is deleted and the last thing we are going to do is stop MC from being automatically started.

All of this being done from within IBM Access Client Solutions (ACS), Run SQL Scripts. The first step is the easiest part, it is to stop the MC job “QYPSJSRV” from being started automatically. For this you can use the following SQL statement:

select * from qusrsys.qatocstart where server=’*MGTC’ for update;

Now navigate your mouse to the AUTOSTART field value and edit the value, change the value “*YES” into “*NO”.

ProTip: Another option is to run the statement below which does this for you in one single step
 update qusrsys.qatocstart set autostart=’*NO’ where server=’*MGTC’;

This does not prevent MC being started manually, a user can still initiate the start of MC by issuing the command: “STRTCPSVR SERVER(*MGTC)” or by clicking on MC in iSeries Navigator.

Now we are ready to stop MC Graph History data from being collected. For this we need to download a PTF, again we are doing this by using a SQL script. In order for you to get going and familiar with using SQL Procedures I have created one which does the ordering of the correct PTF for your IBM i version installed. As this PTF only adds some code to your system the SQL script will also load and apply the PTF.

Below there is an example of such SQL procedure code. If you want to build on it, please remember to adjust the name of the library where you want to be it to be created.

The procedure code:

In the example a “catch all” is added for errors, which sends out an email, so you know if something goes wrong. I have added “your_name@your_domain>”. mail address, please replace it with yours. 🤓

Now you have the PTF with the code in place we can start using it. Again we are using SQL,
run the following command from ACS Run SQL script:
CL:CALL QSYS/QYPSMCCFG PARM(‘*DISPLAY’ ‘*ALL’);

You could decide to run the command from the good old 5250 emulator and execute it there by running the command => CALL QSYS/QYPSMCCFG PARM(‘*DISPLAY’ ‘*ALL’). I encourage you to do so. In 5250 emulation you are missing the result of the command and have to go after it yourself, while the SQL script will immediately show you the information. Below the SQL result:

We are nearly there, just one more command to disable graph data and graph historical data, all you have to do now is to run a CL command, in this example I run it from SQL just like the rest.

 CL:CALL QSYS/QYPSMCCFG PARM(‘*DISABLE’ ‘*ALL’);

Having done that, all that is left to do is to clean up the objects of type *MGTCOL in the library QMGTC2.

Obtaining a list of these objects can be done with the following SQL statement:

SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS(‘QMGTC2’, ‘*MGTCOL’) ) as a;

Finally, a small SQL trick to show you what is possible with Run SQL script.

SELECT ‘CL:DLTOBJ OBJ(‘ CONCAT OBJNAME CONCAT ‘) OBJTYPE(‘ CONCAT OBJTYPE CONCAT ‘);’  FROM TABLE (QSYS2.OBJECT_STATISTICS(‘QMGTC2’, ‘*MGTCOL’) ) as a;

Use copy and paste, to copy the result of the command from the output result window into the SQL execution section. Once you have done that run the SQL statements when you are on the top and select the button “Run from selected”.

The end result is a clean QMGT2 with no obsolete *MGTCOL objects, which have no value, as you cannot view them any more because IBM i Access for Windows Navigator is dead.

ProTip: You might have still some *MGTCOL objects on your system, this is fine, but they have a new format (since IBM i 7.3) and can only be viewed with Navigator for i. 

If you have not explored them yet then I suggest you do. If you want to learn more about Navigator for i, then come and join me at i-UG International i-Power event on 9^th June 2020 in the UK where I will teach a 4 hour hands on workshop that will take you from Zero to Hero. 

More information and registration available at www.i-UG.co.uk

If the idea of using SQL scripts to do this sort of work has caught your imagination, attached are a couple of examples you might like to look at.  These I give freely (but without any warranty or guarantee) and believe them to work perfectly, I use them myself but like all SysAdmin tools you need to understand what you are doing and test to make sure there is nothing unique that you use that will break.

Graph_History_PTF_Procedure

SET PATH “QSYS”,”QSYS2″,”SYSPROC”,”SYSIBMADM”,”RUDI” ;

CREATE OR REPLACE PROCEDURE SQLSCRIPTS.GRAPH_HISTORY_PTF
        LANGUAGE SQL
        SPECIFIC SQLSCRIPTS.GRAPH_HISTORY_PTF
        NOT DETERMINISTIC
        MODIFIES SQL DATA
        CALLED ON NULL INPUT
        SET OPTION ALWBLK = *ALLREAD, ALWCPYDTA = *OPTIMIZE, COMMIT = *NONE, DECRESULT = (31, 31,
                   00), DYNDFTCOL = *NO, DYNUSRPRF = *USER, SRTSEQ = *HEX, DBGVIEW = *SOURCE
BEGIN
/* Declare Command variable */
    DECLARE FULLCMD VARCHAR(600);
    DECLARE PTF_ID CHAR(7);
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        CALL QSYS2.QCMDEXC(
‘SNDSMTPEMM RCP (( “<your_name@your_domain>”))
SUBJECT(”Error in ordering/apply process of Graph History Data cleanup process”)
NOTE(”Please check manually!!”) ‘
            );
    END;
    — Check which version we are running on this box and set corresponding PTF_ID
    — IBM i 7.2  SI71515
    — IBM i 7.3  SI71328
    — IBM i 7.4  SI71207
    FOR READ AS C1 CURSOR FOR
        SELECT OS_VERSION CONCAT ‘.’ CONCAT OS_RELEASE AS IBM_i
            FROM SYSIBMADM.ENV_SYS_INFO
            FETCH FIRST 1 ROW ONLY
        DO
            CASE
                WHEN IBM_i = 7.2 THEN SET PTF_ID = ‘SI71515’;
                WHEN IBM_i = 7.3 THEN SET PTF_ID = ‘SI71328’;
                WHEN IBM_i = 7.4 THEN SET PTF_ID = ‘SI71207’;
            END CASE;
            — Building the PTF order Command
            SET FULLCMD = ‘SNDPTFORD PTFID(‘ CONCAT PTF_ID CONCAT ‘) CHKPTF(*YES)’;
            — Execute the SNDPTFORD command
            CALL QSYS2.QCMDEXC(TRIM(FULLCMD));
            — Building the PTF order Command
            SET FULLCMD = ‘LODPTF LICPGM(5770SS1) SELECT(‘ CONCAT PTF_ID CONCAT ‘)’;
            — Execute the SNDPTFORD command
            CALL QSYS2.QCMDEXC(TRIM(FULLCMD));
            — Building the PTF order Command
            SET FULLCMD = ‘APYPTF LICPGM(5770SS1) SELECT(‘ CONCAT PTF_ID CONCAT ‘)’;
            — Execute the SNDPTFORD command
            CALL QSYS2.QCMDEXC(TRIM(FULLCMD));
    END FOR;
END;

GRANT ALTER, EXECUTE
    ON SPECIFIC PROCEDURE SQLSCRIPTS.GRAPH_HISTORY_PTF
    TO RUDI WITH GRANT OPTION;
stop;

CALL SQLSCRIPTS.GRAPH_HISTORY_PTF;

— Edit the file to prevent a automatic start of Management Central
select * from qusrsys.qatocstart where server=’*MGTC’ for update;

— Update the file to prevent a automatic start of Management Central
update qusrsys.qatocstart set autostart=’*NO’ where server=’*MGTC’;

— Command to Start Management Central
CL:STRTCPSVR SERVER(*MGTC);

— Command to Stop Management Central
CL:ENDTCPSVR SERVER(*MGTC);

— Ordering the PTF which contain the software to manage the collecting of Grpah History Data
— IBM i 7.2  SI71515
— IBM i 7.3  SI71328
— IBM i 7.4  SI71207
— Use the SQL Procedure created for this purpose
CALL SQLSCRIPTS.GRAPH_HISTORY_PTF;

— To display the current settings, run the following command:  

CL:CALL QSYS/QYPSMCCFG PARM(‘*DISPLAY’ ‘*ALL’);  
stop;      

— To disable both graph data and graph history data, run the  following command:    

CL:CALL QSYS/QYPSMCCFG PARM(‘*DISABLE’ ‘*ALL’);
stop;      

SELECT MESSAGE_TEXT FROM TABLE(QSYS2.JOBLOG_INFO(‘*’)) A ORDER by MESSAGE_TIMESTAMP DESC;            

— To disable graph data only, run the following command:          
CL:CALL QSYS/QYPSMCCFG PARM(‘*DISABLE’ ‘*GRAPH’);                    

— To disable graph history data only, run the following command:  
CL:CALL QSYS/QYPSMCCFG PARM(‘*DISABLE’ ‘*HISTORY’);                  

— To enable both graph data and graph history data, run the following command:                                              
CL:CALL QSYS/QYPSMCCFG PARM(‘*ENABLE’ ‘*ALL’);                      

— To enable graph data only, run the following command:          
CL:CALL QSYS/QYPSMCCFG PARM(‘*ENABLE’ ‘*GRAPH’);                

— To enable graph history data only, run the following command:      
CL:CALL QSYS/QYPSMCCFG PARM(‘*ENABLE’ ‘*HISTORY’);                      

— To display the current settings, run the following command:        
CL:CALL QSYS/QYPSMCCFG PARM(‘*DISPLAY’ ‘*ALL’);    



— Find all *MGTCOL objects within library QMGTC2
—
SELECT ‘CL:DLTOBJ OBJ(‘ CONCAT OBJNAME CONCAT ‘) OBJTYPE(‘ CONCAT OBJTYPE CONCAT ‘);’  FROM TABLE (QSYS2.OBJECT_STATISTICS(‘QMGTC2’, ‘*MGTCOL’) ) as a;