What I do with the SELF data

It is often a question I am asked about SELF: What do I do with the data it generates?

Once a week I take the data and copy it from IBM's SELF table into one of my own. Why?

I am only interested in errors I can fix. I am not interested in errors SELF has reported for any of the following:

• Errors with objects in IBM's libraries
• Errors with objects in third party vendor tools
• Errors with the core objects in the ERP's libraries

Therefore, I will not copy those errors to my "saved" file.

I also want to add several columns to my "saved" file that are not in the SELF table:

• Description for the SQL code
• The job name part of the full job name
• Timestamp of when I added this data to the "saved" table

I have noticed that it takes longer than I would expect to return the results from the SELF table. That is because two of the columns are CLOB data type. When the results are returned the Db2 engine has to translate the CLOB data to character, and then to the default CCSID. What can I do to speed this up?

I could convert the CLOB data to VARCHAR type date when I copy the data to the "saved" files.

Which columns are CLOB? I can use the SYSCOLUMNS2 View to find them. I use SYSCOLUMNS2 rather than SYSCOLUMNS as its data is more IBM i centric. My SQL statement would look like:

01 SELECT ORDINAL_POSITION,COLUMN_NAME,DATA_TYPE,LENGTH 02 FROM QSYS2.SYSCOLUMNS2 03 WHERE TABLE_SCHEMA = 'QSYS2' 04 AND TABLE_NAME = 'SQL_ERROR_LOG'

Line 1: I only want the following columns:

1. ORDINAL_POSITION:  Generated by the View to be the sequence number of the columns in the table
2. COLUMN_NAME:  The long, SQL, column name
3. DATA_TYPE:  Data type of the column
4. LENGTH:  Length of the column

I am only going to show the results for the two columns that are CLOB:

ORDINAL_ POSITION COLUMN_NAME DATA_TYPE LENGTH -------- -------------- --------- ------- 4 STATEMENT_TEXT DBCLOB 2097152 28 INITIAL_STACK CLOB 1048576

If I am going to make VARCHAR columns in the "saved" file for the data in these columns what is the maximum length of the data in these columns. I can find that using the following SQL statement:

SELECT MAX(LENGTH(STATEMENT_TEXT)),MAX(LENGTH(INITIAL_STACK)) FROM QSYS2.SQL_ERROR_LOG

Line 1: I am using the Length scalar function to return the length of the data within the column. The I use the Maximum scalar function to return the maximum value for the length. This will give me one line of results:

00001 00002 ------ ------ 9216 1966

If I make my CLOB fields VARCHAR of a maximum of ten thousand that will accommodate all of the data in the SELF table.

Below is the code to create my "saved" file, SELF_SAVED_DATA:

01 CREATE TABLE MYLIB.SELF_SAVED_DATA 02 FOR SYSTEM NAME "SELFSAVED" 03 (RDB_NAME, 04 LOGGED_SQLCODE FOR "SQLCODE", 05 MESSAGE_TEXT FOR "MSGTEXT", 06 LOGGED_SQLSTATE FOR "SQLSTATE", 07 NUMBER_OCCURRENCES FOR "OCCURRNCS", 08 STATEMENT_TEXT FOR "STMTTEXT", 09 STATEMENT_OPERATION FOR "STMTOPER", 10 STATEMENT_OPERATION_DETAIL FOR "STMTOPDTL", 11 REASON_CODE FOR "RSNCODE", 12 PROGRAM_LIBRARY FOR "PGMLIB", 13 PROGRAM_NAME FOR "PGMNAME", 14 PROGRAM_TYPE FOR "PGMTYPE", 15 MODULE_NAME FOR "MODNAME", 16 LOGGED_TIME FOR "LOGTIME", 17 JOB_NAME FOR "JOBNAME", 18 JOB_NAME_JOB FOR "JOBNAMEJOB", 19 THREAD_ID FOR "THREADID", 20 ADOPTED_USER_NAME FOR "ADOPTUSER", 21 USER_NAME FOR "USERNAME", 22 SYSTEM_USER_NAME FOR "SYSUSERNME", 23 INITIAL_LOGGED_TIME FOR "INITTIME", 24 INITIAL_JOB_NAME FOR "INITJOBNME", 25 INITIAL_THREAD_ID FOR "INITTHREAD", 26 INITIAL_ADOPTED_USER_NAME FOR "INITADOPT", 27 INITIAL_STACK FOR "INITSTACK", 28 CLIENT_ACCTNG FOR "CLNTACTTNG", 29 CLIENT_APPLNAME FOR "CLNTAPPNME", 30 CLIENT_PROGRAMID FOR "CLNTPGMNME", 31 CLIENT_USERID FOR "CLNTUSER", 32 CLIENT_WRKSTNNAME FOR "CLNTWRKSTN", 33 RUN_TIMESTAMP FOR "RUNTIME") 34 AS 35 (SELECT A.RDB_NAME, 36 A.LOGGED_SQLCODE, 37 B.MESSAGE_TEXT, 38 A.LOGGED_SQLSTATE, 39 A.NUMBER_OCCURRENCES, 40 CAST(A.STATEMENT_TEXT AS VARCHAR(10000) CCSID 37), 41 A.STATEMENT_OPERATION, 42 A.STATEMENT_OPERATION_DETAIL, 43 A.REASON_CODE, 44 A.PROGRAM_LIBRARY, 45 A.PROGRAM_NAME, 46 A.PROGRAM_TYPE, 47 A.MODULE_NAME, 48 A.LOGGED_TIME, 49 A.JOB_NAME, 50 SUBSTR(A.JOB_NAME,LOCATE_IN_STRING(A.JOB_NAME,'/',-1)+1), 51 A.THREAD_ID, 52 A.ADOPTED_USER_NAME, 53 A.USER_NAME, 54 A.SYSTEM_USER_NAME, 55 A.INITIAL_LOGGED_TIME, 56 A.INITIAL_JOB_NAME, 57 A.INITIAL_THREAD_ID, 58 A.INITIAL_ADOPTED_USER_NAME, 59 CAST(A.INITIAL_STACK AS VARCHAR(10000) CCSID 37), 60 A.CLIENT_ACCTNG, 61 A.CLIENT_APPLNAME, 62 A.CLIENT_PROGRAMID, 63 A.CLIENT_USERID, 64 A.CLIENT_WRKSTNNAME, 65 CURRENT_TIMESTAMP 66 FROM QSYS2.SQL_ERROR_LOG A, 67 LATERAL 68 (SELECT MESSAGE_TEXT 69 FROM TABLE(SYSTOOLS.SQLCODE_INFO(A.LOGGED_SQLCODE))) B) 70 DEFINITION ONLY ;

Lines 1 and 2: I like to give my SQL objects long names, SELF_SAVED_DATA, and short system names, SELFSAVED.

Lines 3 33: Long and short names for all the columns that will be in the table.

Lines 35 69: The SQL Select statement used to create this table.

Line 40 and 59: These are the statements that create convert the CLOB fields in the SELF table to VARCHAR in this table.

Line 50: I am extracting the job name from the full job name into its own column.

Line 37: This is the message text for the SQL code in the LOGGED_SQLCODE column. I get the text from the SQLCODE_INFO table function.

Lines 67 69: I join table function to the SELF table using a LATERAL. This allows me to have a subselect, lines 68 and 69, where I can use a column from the table in the table function. I just want to return the message text from the table function.

Line 70: As I am building this table based on a SQL Select I need to have the DEFINITION ONLY. This means that the file is created, and it contains no data.

There is more to the creation of this table:

71 LABEL ON COLUMN SELF_SAVED_DATA( 72 MESSAGE_TEXT IS 'SQL code message text', 73 JOB_NAME_JOB IS 'Job name job', 74 RUN_TIMESTAMP IS 'Run time') ; 75 LABEL ON TABLE SELF_SAVED_DATA IS 'Table: SELF_SAVED_DATA' ;

Lines 71 74: I want to give the new columns the SQL equivalent of column headings.

Line 75: And I want to use this as the description for the table.

All the above is in a source member. That means I can create the file using the Run SQL Statement command, RUNSQLSTM.

Having built the "saved" table I then need to copy data from the SELF table into it, using a SQL Insert statement:

01 INSERT INTO SELF_SAVED_DATA 02 SELECT A.RDB_NAME, 03 A.LOGGED_SQLCODE, 04 B.MESSAGE_TEXT, 05 A.LOGGED_SQLSTATE,A.NUMBER_OCCURRENCES, 06 CAST(A.STATEMENT_TEXT AS VARCHAR(10000) CCSID 37), 07 A.STATEMENT_OPERATION,A.STATEMENT_OPERATION_DETAIL, 08 A.REASON_CODE,A.PROGRAM_LIBRARY,A.PROGRAM_NAME, 09 A.PROGRAM_TYPE,A.MODULE_NAME,A.LOGGED_TIME,A.JOB_NAME, 10 SUBSTR(A.JOB_NAME,LOCATE_IN_STRING(A.JOB_NAME,'/',-1)+1), 11 A.THREAD_ID,A.ADOPTED_USER_NAME,A.USER_NAME,A.SYSTEM_USER_NAME, 12 A.INITIAL_LOGGED_TIME,A.INITIAL_JOB_NAME,A.INITIAL_THREAD_ID, 13 A.INITIAL_ADOPTED_USER_NAME, 14 CAST(A.INITIAL_STACK AS VARCHAR(10000) CCSID 37), 15 A.CLIENT_ACCTNG,A.CLIENT_APPLNAME,A.CLIENT_PROGRAMID, 16 A.CLIENT_USERID,A.CLIENT_WRKSTNNAME, 17 CURRENT_TIMESTAMP 18 FROM QSYS2.SQL_ERROR_LOG A, 19 LATERAL 20 (SELECT MESSAGE_TEXT 21 FROM TABLE(SYSTOOLS.SQLCODE_INFO(A.LOGGED_SQLCODE))) B 22 WHERE PROGRAM_LIBRARY NOT IN ('QSYS','QSQL','QSYS2', 23 'VENDOR1','VENDOR2') ; 24 DELETE FROM QSYS2.SQL_ERRORT ;

The Insert statement is pretty much the same as the Select statement was that I used to create the file. The only difference is the Where clause.

Lines 22 and 23: I am using a Where clause to exclude rows for programs in the listed libraries. The first three are IBM libraries. The two that follow are vendor libraries.

Line 24: I am deleting the data from IBM's SELF table.

I have submitted the Insert statement to the job scheduler to run every Monday morning.

I can now use my "saved" file to search for SQL statements that I want to review, without the data from the libraries I do not:

01 SELECT * 02 FROM MYLIB.SELF_SAVED_DATA 03 WHERE RUN_TIMESTAMP = (SELECT MAX(RUN_TIMESTAMP) FROM MYLIB.SELF_SAVED_DATA) 04 ORDER BY NUMBER_OCCURRENCES DESC

Line 3: This is the line that selects the rows to be returned in the results. I want the latest rows, but I may not know the value of the RUN_TIMESTAMP for the latest rows. By comparing the RUN_TIMESTAMP to the maximum value of the RUN_TIMESTAMP, as returned from the subselect, I will always get the most recent data.

How long do I want to retain the data in the "saved" file? I am thinking 90 days. If I decide upon 90 days I would add the following lines to the Insert source member:

25 DELETE FROM MYLIB.SELF_SAVED_DATA 26 WHERE DATE(RUN_TIMESTAMP) < CURRENT_DATE - 90 DAYS ;

The above will delete all rows older than 90 days.

By adding these lines to the Insert source member, whenever new data is added with the Insert statement, the old is deleted using this Delete. That way the "saved" files will always contain just the data for the time period I care about.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.