Pages

Wednesday, January 20, 2021

Determine if Exit points or programs have been changed

two new sql views to learn about exit points and exit programs

Exit points are a feature of the IBM i operating system. When certain processes are run an Exit point is where an Exit program can be called. After the Exit program has completed the process continues. You can create your own Exit programs, and insert them into these Exit points. Even though Exit points and programs have been around forever many people do not use them, nor do they monitor them. As they can capture system information, their misuse must be considered a security breach.

In this post I am not going to describe how to add or make changes to Exit points and programs. I am going to give a simple example how they can be monitored. Notifying me when they have been changed.

This work includes two new SQL Views that came as part of the latest Technology Refresh, IBM i 7.4 TR3 and 7.3 TR9:

  1. EXIT_POINT_INFO:  Lists all of the Exit points and information about them
  2. EXIT_PROGRAM_INFO:  List all of the Exit programs and information about them, including the Exit point data

Prior to these additions I would have had to use the Work Registration Information command, WRKREGINF. This information from this command can either be viewed in a 5250 session or printed.

Rather than show you lists of the results from these two Views I thought it would be more interesting to give a practical example of using them.

Before you read my example I encourage you to just run two simple SQL Select statements, below, to see what results returned from these Views:

SELECT * FROM QSYS2.EXIT_POINT_INFO ;

SELECT * FROM QSYS2.EXIT_PROGRAM_INFO ;

As I am making a program to compare two sets of results I first need to capture the current Exit points and programs data. I will be using this "snap-shot" to compare to the current information to. Exit points and programs can change from one release and TR to another. Just beware that you will have to compare your "snap-shot" to the current information after every release or TR update. If there are differences you will need to update the "snap-shot" Table.

I created a SQL Table to contain the saved information:

01  CREATE TABLE MYLIB.SAVED_EXITS
02         FOR SYSTEM NAME "EXITSSAVED"
03  AS
04  (SELECT A.EXIT_POINT_NAME,A.EXIT_POINT_FORMAT,
05          A.EXIT_PROGRAMS,A.TEXT_DESCRIPTION,
06          A.ADD_EXIT_PROGRAM_LIBRARY,A.ADD_EXIT_PROGRAM,
07          A.ADD_EXIT_PROGRAM_FORMAT,
08          A.REMOVE_EXIT_PROGRAM_LIBRARY,
09          A.REMOVE_EXIT_PROGRAM,
10          A.REMOVE_EXIT_PROGRAM_FORMAT,
11          A.RETRIEVE_EXIT_PROGRAM_LIBRARY,
12          A.RETRIEVE_EXIT_PROGRAM,
13          A.RETRIEVE_EXIT_PROGRAM_FORMAT,
14          B.EXIT_PROGRAM_LIBRARY,B.EXIT_PROGRAM,
15          B.EXIT_PROGRAM_DATA,B.EXIT_PROGRAM_NUMBER
16     FROM QSYS2.EXIT_POINT_INFO A
17     LEFT OUTER JOIN QSYS2.EXIT_PROGRAM_INFO B
18       ON A.EXIT_POINT_NAME = A.EXIT_POINT_NAME
19      AND A.EXIT_POINT_FORMAT = B.EXIT_POINT_FORMAT
20    ORDER BY A.EXIT_POINT_NAME,A.EXIT_POINT_FORMAT,
21               B.EXIT_PROGRAM_NUMBER)
22  WITH DATA ;

23  LABEL ON TABLE SAVED_EXITS IS 'SQL table:SAVED_EXITS' ;

Line 2: As I like to create my Tables, Views, and Indexes with names longer than 10 characters, I use this line to give it a decent IBM i compatible name.

Lines 4 - 21: The Table is created from this SQL statement, I call it "on the fly". All of the definitions are taken from the columns defined in the statement.

Columns prefixed with "A" are from the EXIT_POINT_INFO View. "B" comes from EXIT_PROGRAM_INFO.

  • A.EXIT_POINT_NAME
  • A.EXIT_POINT_FORMAT
  • A.EXIT_PROGRAMS:  Number of exit programs for this Exit point
  • A.TEXT_DESCRIPTION
  • A.ADD_EXIT_PROGRAM_LIBRARY
  • A.ADD_EXIT_PROGRAM:  Name of the preprocessing Exit program name that is called when an Exit point is added
  • A.ADD_EXIT_PROGRAM_FORMAT:  Format name of the ADD_EXIT_PROGRAM
  • A.REMOVE_EXIT_PROGRAM_LIBRARY
  • A.REMOVE_EXIT_PROGRAM:  Name of the preprocessing Exit program name that is called when an Exit point is removed
  • A.REMOVE_EXIT_PROGRAM_FORMAT:  Format name of the ADD_EXIT_PROGRAM
  • A.RETRIEVE_EXIT_PROGRAM_LIBRARY
  • A.RETRIEVE_EXIT_PROGRAM:  Name of the preprocessing Exit program name that is called when an Exit point is retrieved
  • A.RETRIEVE_EXIT_PROGRAM_FORMAT:  Format name of the RETRIEVE_EXIT_PROGRAM
  • B.EXIT_PROGRAM_LIBRARY
  • B.EXIT_PROGRAM:  The name of the Exit program
  • B.EXIT_PROGRAM_DATA:  Data associated with Exit program. Could be null
  • B.EXIT_PROGRAM_NUMBER:  Sequence number used to establish in which order the exit programs should be called

Lines 16 – 19: Joining the two Views together as a left outer join so I can have a one to many relationship between the two.

Line 22: Don't forget the WITH DATA, without it the Table cannot be created.

Line 23: Giving the Table object a description.

Having run that statement I have a SQL Table containing my comparison "snap shot".

I decide to create the program to do the comparison of the "snap shot" data to the current data in RPG. The first part I am going to show is the "main body" of the program:

01  **free
02  ctl-opt option(*srcstmt) dftactgrp(*no) ;

03  dcl-ds Differences qualified dim(999) ;
04    Attribute char(100) ;
05    File1 char(10) ;
06    File2 char(10) ;
07  end-ds ;

08  dcl-s Rows uns(5) ;
09  dcl-s i like(Rows) ;

10  exec sql SET OPTION COMMIT = *NONE, CLOSQLCSR = *ENDMOD ;

11  MakeNewFile() ;

    //test
12  exec sql call qsys2.qcmdexc('RUNSQLSTM SRCFILE(MYLIB/DEVSRC) + 
                                             SRCMBR(TEST1SQL) +
                                             COMMIT(*NC)') ;
    //test end

13  Comparison() ;

14  if (Rows > 0) ;
15    MakeDifferencesFile() ; 
16  endif ;

17  *inlr = *on ;
18  //exec sql DROP TABLE MYLIB.EXITS_NEW ;

Line 1: My RPG is always free.

Line 2: The control options this program needs.

Lines 3 – 7: I will be using this data structure array for receiving results into from a SQL cursor later in this program.

Line 8: I will be using this variable to tell the SQL cursor how many elements the array has. Then it will be used to contain the number of rows fetched from the cursor.

Line 9: This is just a miscellaneous variable I will be using for counting.

Line 10: I always add these SQL options so neither I nor another programmer need to change the compile options.

Line 11: Call the procedure to make the table of the current Exit points and programs.

Line 12: This is a cheat. I know the current Exit points and programs are the same as when I created the "snap-shot" file. I am using a Run SQL Statement command, RUNSQLSTM, to run a SQL Update statement to change one of the rows in the new Table. I will show what this statement is later.

Line 13: Procedure within which I compare the contents of the two Tables, "snap-shot" versus current data.

Lines 14 – 16: If there are differences between the "snap-shot" and the current data I want to put those differences in a Table I can use.

Line 18: I commented out this line. If this was in production I would delete the file as I am finished with it. But this is an example in test, I will leave it be for now.

Onto the MakeNewFile procedure:

20  dcl-proc MakeNewFile ;
21    exec sql DROP TABLE MYLIB.EXITS_NEW ;

22    exec sql CREATE TABLE MYLIB.EXITS_NEW
23             AS
24             (SELECT A.EXIT_POINT_NAME,A.EXIT_POINT_FORMAT,
25                     A.EXIT_PROGRAMS,A.TEXT_DESCRIPTION,
26                     A.ADD_EXIT_PROGRAM_LIBRARY,
27                     A.ADD_EXIT_PROGRAM,
28                     A.ADD_EXIT_PROGRAM_FORMAT,
29                     A.REMOVE_EXIT_PROGRAM_LIBRARY,
30                     A.REMOVE_EXIT_PROGRAM,
31                     A.REMOVE_EXIT_PROGRAM_FORMAT,
32                     A.RETRIEVE_EXIT_PROGRAM_LIBRARY,
33                     A.RETRIEVE_EXIT_PROGRAM,
34                     A.RETRIEVE_EXIT_PROGRAM_FORMAT,
35                     B.EXIT_PROGRAM_LIBRARY,B.EXIT_PROGRAM,
36                     B.EXIT_PROGRAM_DATA,B.EXIT_PROGRAM_NUMBER
37                FROM QSYS2.EXIT_POINT_INFO A
38                LEFT OUTER JOIN QSYS2.EXIT_PROGRAM_INFO B
39                  ON A.EXIT_POINT_NAME = A.EXIT_POINT_NAME
40                 AND A.EXIT_POINT_FORMAT = B.EXIT_POINT_FORMAT
41               ORDER BY A.EXIT_POINT_NAME,A.EXIT_POINT_FORMAT,
42                        B.EXIT_PROGRAM_NUMBER)
43             WITH DATA ;

44    exec sql LABEL ON TABLE EXITS_NEW IS 'Temp file' ;
45  end-proc ;

This procedure makes a Table that is identical the "snap-shot" Table I described before.

I explained that I cheated and I need to change some data in the EXITS_NEW Table so I would have some differences to report. I placed the following SQL statement in a source member, TEST1SQL, and it was executed by the RUNSQLSTM statement.

01  UPDATE MYLIB.EXITS_NEW
02  SET EXIT_PROGRAM = 'BAD_PGM',
03      EXIT_PROGRAM_LIBRARY = 'BAD_LIB'
04  WHERE EXIT_POINT_NAME = 'QIBM_QCA_RTV_COMMAND'
05  AND EXIT_POINT_FORMAT = 'RTVC0100'
06  AND EXIT_PROGRAM_NUMBER = 1 ;

All it does is change the Exit program name and library for one of the Exit points.

Next up is the procedure that does the checking if there are any differences between the two tables.

50  dcl-proc Comparison ;
51    dcl-ds TempDS likeds(Differences) ;

52    Rows = %elem(Differences) ;

53    exec sql DECLARE C0 CURSOR FOR
54                SELECT *
55                  FROM TABLE(QSYS2.COMPARE_FILE(
56                           LIBRARY1 => 'MYLIB',
57                           FILE1 => 'EXITSSAVED',
58                           LIBRARY2 => 'MYLIB',
59                           FILE2 => 'EXITS_NEW',
60                           COMPARE_ATTRIBUTES => 'NO',
61                           COMPARE_DATA => 'YES')) ;

62    exec sql OPEN C0 ;

63    exec sql FETCH C0 FOR :Rows ROWS INTO :Differences ;

64    exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;

65    exec sql CLOSE C0 ;

66    if (Rows = 0) ;
67      return ;
68    endif ;

69    exec sql DROP TABLE QTEMP.EXIT_DIFF1 ;

70    exec sql CREATE TABLE QTEMP.EXIT_DIFF1
71             (ATTRIBUTE CHAR(100),
72              FILE1 CHAR(10),
73              FILE2 CHAR(10)) ;

74    for i = 1 to Rows ;
75      TempDS = Differences(i) ;
76      exec sql INSERT INTO QTEMP.EXIT_DIFF1 VALUES(:TempDS) ;
77    endfor ;
78  end-proc ;

Line 51: Using the LIKEDS to make TempDs identical to Differences, except TempDS is not an array.

Line 52: Initialize the variable Rows with the number of elements in the data structure array Differences.

Lines 53 – 61: Declare a cursor to retrieve the differences in the data between the two tables using the COMPARE_FILE table function. I cannot compare a file in QTEMP, which is why I had to create EXITS_NEW in my library.

Line 62: Open the cursor.

Line 63: Fetch the same number of rows from the results as there are elements in the data structure array Differences, and place the results in Differences.

Line 64: I will need to know the number of rows fetched later in this program, therefore, I use the GET DIAGNOSTICS to place that data in the variable Rows.

Line 65: Close the cursor.

Lines 66 – 68: If no results were fetched from the cursor, exit the procedure.

Line 69: Delete, drop, the Table EXIT_DIFF1 if it exists in QTEMP.

Lines 70 – 73: Create a new Table EXIT_DIFF1, that has three columns. This Table is like the "header", it will contain the description of the differences found.

Lines 74 – 77: This For group will write the data from the data structure array into the Table I just created. I cannot insert into a Table directly from a data structure array. If I move each element from the data structure array into a non-array data structure, I can insert that content into the Table.

The last procedure makes the second difference file, EXIT_DIFF2, which contains the actual data:

80  dcl-proc MakeDifferencesFile ;
81    dcl-s RRN1 packed(15:0) ;
82    dcl-s RRN2 like(RRN1) ;

83    exec sql DROP TABLE QTEMP.EXIT_DIFF2 ;

84    exec sql CREATE TABLE QTEMP.EXIT_DIFF2 AS
85             (SELECT '12345' AS "SOURCE",
86                     RRN(A) AS "RRN",
87                     A.*
88                FROM MYLIB.SAVED_EXITS A)
89             DEFINITION ONLY ;

90    for i = 1 to Rows ;
91       RRN1 = %dec(%subst(Differences(i).File1:5):15:0) ;
92       RRN2 = %dec(%subst(Differences(i).File2:5):15:0) ;

90       exec sql INSERT INTO QTEMP.EXIT_DIFF2
91                (SELECT 'SAVED',RRN(A),A.*
92                   FROM MYLIB.SAVED_EXITS A
93                  WHERE RRN(A) = :RRN1) ;

94       exec sql INSERT INTO QTEMP.EXIT_DIFF2
95                (SELECT 'NEW',RRN(B),B.*
96                   FROM MYLIB.EXITS_NEW B
97                  WHERE RRN(B) = :RRN2) ;
98    endfor ;
99  end-proc ;

Line 83: Delete the Table if it already exists.

Line 84 – 89: Create a Table that will be identical to SAVED_EXITS, with two fields added to the start of it. By using the DEFINITION ONLY only the Table is created, no data is added.

Before I go any further I need to show the data in Differences:

EVAL differences
DIFFERENCES.ATTRIBUTE(1) =
    ....5...10...15...20...25...30...35...40...45...5
 1 'Unmatched DATA for MEMBER EXITSSAVED VS EXITS_NEW
61 '                                        '
DIFFERENCES.FILE1(1) = 'RRN 5     '
DIFFERENCES.FILE2(1) = 'RRN 5     '
  • ATTRIBUTE:  Is the description of the difference
  • FILE 1:  The relative record number of the row from SAVED_EXITS
  • FILE 2:  The relative record number of the row from EXITS_NEW

Lines 90 – 98: In this For group I am going to retrieve the data from Differences and insert the row from SAVED_EXITS and then from EXITS_NEW.

Lines 91 and 92: Converting the "number" part of the FILE1 and FILE2 subfields to a real number that I will use later.

Lines 90 – 93: Insert the row, using the RRN, from SAVED_EXITS with the first column containing "SAVED" to identified it came from this table.

Lines 94 – 97: Insert the corresponding row from EXITS_NEW with the first column as "NEW".

When the program has finished if there are differences I have two Tables:

SELECT * FROM MYLIB.EXIT_DIFF1


ATTRIBUTE                                          FILE1  FILE2
-------------------------------------------------  -----  -----
Unmatched DATA for MEMBER EXITSSAVED VS EXITS_NEW  RRN 5  RRN 5

I am not going to list all the columns for the other Table, just the ones that show the difference:

SELECT "SOURCE", "RRN", 
       EXIT_POINT_NAME, EXIT_POINT_FORMAT, EXIT_PROGRAMS, 
       EXIT_PROGRAM_LIBRARY, EXIT_PROGRAM 
  FROM MYLIB.EXIT_DIFF2


                                EXIT_             EXIT_
                                POINT_   EXIT_    PROGRAM_ EXIT_
SOURCE RRN EXIT_POINT_NAME      FORMAT   PROGRAMS LIBRARY  PROGRAM
------ --- -------------------- -------- -------- -------- ---------
SAVED    5 QIBM_QCA_RTV_COMMAND RTVC0100        6 QSYS     QPYDJCSTR
NEW      5 QIBM_QCA_RTV_COMMAND RTVC0100        6 BAD_LIB  BAD_PGM

Both Tables are in QTEMP. I would copy them to the IFS and then email them to myself.

 

When creating this example program I did uncover a strange error. The first time I ran the program it worked without error. The next time I ran the program I received the following errors, and no results:

  Not authorized to object QTEMP in *N type *LIB.
  Not authorized to object QTEMP in *N type *LIB.
  User-defined function error on member QSQPTABL.
?                                                
  User-defined function error on member QSQPTABL.
?                                                
  Not authorized to object QTEMP in *N type *LIB.

When I prompted the errors messages I saw:

Message ID . . :  SQL0551     Severity . . :  30        
Message type . :  Diagnostic
Date sent  . . :  DD/DD/DD    Time sent  . :  TT:TT:TT
                        
Message . . . . :   Not authorized to object QTEMP in 
  *N type *LIB.
Cause . . . . . :   An operation was attempted on object
  QTEMP in *N type *LIB.  This operation cannot be 
  performed without the required authority.
Recovery  . . . :   Obtain the required authority
  from either the security officer, the object owner,
  or a user that is authorized to the QIBM_DB_SECADM 
  function. If you are not authorized to a logical file,
  obtain the authority to the based-on files of the logical
  file. Try the operation again.


Message ID . . :  CPF503E     Severity . . : 30
Message type . :  Sender copy
Date sent  . . :  DD/DD/DD    Time sent  . :  TT:TT:TT

Message . . . . :   User-defined function error on member
  QSQPTABL.           
Cause . . . . . :   An error occurred while invoking 
  user-defined function COMPARE_FILE in library QSYS2. The
  error occurred while invoking the associated external 
  program or service program CMP_FILE in library QSYS2,
  program entry point or external name COMPARE_FILE_1, 
  specific name CMP_FILE. The error occurred on member
  QSQPTABL file QSQPTABL in library QSYS2. The error code
  is 1. The error codes and their meanings follow:
    1 -- The external program or service program returned 
  SQLSTATE 42501. The text message returned from the 
  program is: ??QTEMP     ??*N        ??LIB .

I do not know if it the way my profile is configured on the server I use to build these examples. Or it is something more serious.

If I signed off and back on again I could run the program again, for one time. If I submit this job to batch as it is a new job the program runs without error.

 

I know I can get any changes to the Exit points and program from the system audit journal. But I would not have had the opportunity to show these two new Views in action.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.