Wednesday, March 1, 2017

Using Get Diagnostic for SQL errors

get diagnostics for errors

I have already written about using DB2 for i's (SQL) GET DIAGNOSTICS to get the number of rows changed by a SQL statement. I can use the GET DIAGNOSTICS statement to get a lot more information about the SQL statement that has just occurred. In fact there are approximately 100 different types of information that can be retrieved using this statement, which is too much for one post. Therefore, I have decided to concentrate upon those keywords I think would be useful for diagnosing errors.

I have identified seven of keywords that I would find useful when trying to determine the cause of an error:

Keyword Description
DB2_MESSAGE_ID Message id for the error in MESSAGE_TEXT (below)
DB2_MESSAGE_ID1 Underlying escape CPF message that originally caused this error
DB2_MESSAGE_ID2 Underlying diagnostic CPD message that originally caused this error
DB2_RETURNED_SQLCODE SQL code (SQLCOD)
MESSAGE_LENGTH Length of the message in MESSAGE_TEXT
MESSAGE_TEXT Message text for the error
RETURNED_SQLSTATE SQL state (SQLSTATE)

To see what kind of information the GET DIAGNOSTICS returns I create a RPG program, with embedded SQL of course, to give some of the more common scenarios I could think of:

  1. Inserting rows to a table/file
  2. Updating rows in a table/file
  3. Selecting one row from a table/file
  4. Deleting rows from a table/file
  5. Fetching rows from a table/file

I decided to place each of these scenarios in their own subprocedure, and one that would be called after every SQL statement to get the data from the GET DIAGNOTICS statement. The definitions in this program are:

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

03  dcl-s MessageId char(10) ;
04  dcl-s MessageId1 varchar(7) ;
05  dcl-s MessageId2 like(MessageId1) ;
06  dcl-s MessageLength int(5) ;
07  dcl-s MessageText varchar(32740) ;
08  dcl-s ReturnedSQLCode char(5) ;
09  dcl-s ReturnedSQLState char(5) ;
10  dcl-s RowsCount int(10) ;

11  exec sql SET OPTION COMMIT = *NONE ;

Line 1: Like all the rest of my recent examples I am writing this program in totally free RPG.

Line 2: My standard control options.

lines 3 – 10: Definitions for the variables I will be using to receive the information from the GET DIAGNOSTICS.

Line 11: I am using the SET OPTION statement to "turn off" commitment control.

And here is the subprocedure to get the information from GET DIAGNOSTICS:

20  dcl-proc Diagnostics ;
21    exec sql GET DIAGNOSTICS
22             :RowsCount = ROW_COUNT;

23    exec sql GET DIAGNOSTICS CONDITION 1
24             :ReturnedSqlCode = DB2_RETURNED_SQLCODE,
25             :ReturnedSQLState = RETURNED_SQLSTATE,
26             :MessageLength = MESSAGE_LENGTH,
27             :MessageText = MESSAGE_TEXT,
28             :MessageId = DB2_MESSAGE_ID,
29             :MessageId1 = DB2_MESSAGE_ID1,
30             :MessageId2 = DB2_MESSAGE_ID2 ;
31  end-proc ;

Lines 21 and 22: This GET DIAGNOSTICS statement will retrieve number of rows effected by the previous SQL statement.

Lines 23 – 30: This is the where I am retrieving the information for the errors.

Line 23: CONDITION 1 I defines that I want the information from the most recent SQL statement.

Lines 24 – 30: I think this is self-explanatory, moving the values into the variables I have defined. Notice that each keyword expression is separated by a comma ( ,).

Ok, let's get on with the procedure for adding a row/record to the table/file:

40  dcl-proc AddRow ;
41    exec sql INSERT INTO TESTFILE
               VALUES (2000,'RAINBIRD','GA') ;
42    Diagnostics() ;

43    exec sql INSERT INTO TESTFILE
               VALUES (2000,'RAINBIRD','GA') ;
44    Diagnostics() ;

45    exec sql INSERT INTO TESTFILE
               VALUES ('2000','RAINBIRD','GA') ;
46    Diagnostics() ;

47    exec sql INSERT INTO QTEMP.NO_FILE
               VALUES ('2000','RAINBIRD','GA') ;
48    Diagnostics() ;
49  end-proc ;

Line 41: This insert adds a row/record to the table/file. The GET DIAGNOSTICS values look like:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 37
MESSAGE_TEXT 1 rows inserted in TESTFILE in MYLIB.
DB2_MESSAGE_ID SQL7956
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

For a list of SQL messages, including SQLCODE and SQLSTATE, see here. For just SQLSTATE codes see here.

Line 43: As this is the same as the previous SQL I would expect a duplicate row message:

Keyword Returned value
DB2_RETURNED_SQLCODE -803
RETURNED_SQLSTATE 23505
MESSAGE_LENGTH 30
MESSAGE_TEXT Duplicate key value specified.
DB2_MESSAGE_ID SQL0803
DB2_MESSAGE_ID1 CPF5034 - Duplicate key on access path.
DB2_MESSAGE_ID2 CPF5009 - Duplicate record key in member &1.

Line 45: This time I am going to use an alphanumeric value for the first column, I would expect this to error as the first column is packed numeric.

Keyword Returned value
DB2_RETURNED_SQLCODE -803
RETURNED_SQLSTATE 23505
MESSAGE_LENGTH 30
MESSAGE_TEXT Duplicate key value specified.
DB2_MESSAGE_ID SQL0803
DB2_MESSAGE_ID1 CPF5034 - Duplicate key on access path.
DB2_MESSAGE_ID2 CPF5009 - Duplicate record key in member &1.

I am surprised by this response as it is for a duplicate key, not for an invalid value in the first row.

Line 47: An update to a nonexistent file should give me an error.

Keyword Returned value
DB2_RETURNED_SQLCODE -204
RETURNED_SQLSTATE 42704
MESSAGE_LENGTH 38
MESSAGE_TEXT NO_FILE in QTEMP type *FILE not found.
DB2_MESSAGE_ID SQL0204
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

The procedure using the Update SQL statement is much smaller than the one for add.

50  dcl-proc UpdateRow ;
51    exec sql UPDATE TESTFILE
                  SET ACCSTE = 'AZ'
                WHERE ACCNBR = 1035 ;
52    Diagnostics() ;

53    exec sql UPDATE TESTFILE
                  SET ACCSTE = 'CT'
                WHERE ACCNBR = 999 ;
54    Diagnostics() ;
55  end-proc ;

Line 51: This will be a successful update.

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 36
MESSAGE_TEXT 1 rows updated in TESTFILE in MYLIB.
DB2_MESSAGE_ID SQL7957
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 53: As there is no account 999, this update will fail.

Keyword Returned value
DB2_RETURNED_SQLCODE 100
RETURNED_SQLSTATE 02000
MESSAGE_LENGTH 25
MESSAGE_TEXT Row not found for UPDATE.
DB2_MESSAGE_ID SQL0100
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

The procedure for deletes is another small one.

60  dcl-proc DeleteRow ;
61    exec sql DELETE FROM TESTFILE
                 WHERE ACCSTE = 'GA' ;
62    Diagnostics() ;

63    exec sql DELETE FROM TESTFILE
                WHERE ACCSTE = 'XX' ;
64    Diagnostics() ;
65  end-proc ;

Line 61: This will be successful delete.

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 38
MESSAGE_TEXT 1 rows deleted from TESTFILE in MYLIB.
DB2_MESSAGE_ID SQL7955
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 63: This will be unsuccessful.

Keyword Returned value
DB2_RETURNED_SQLCODE 100
RETURNED_SQLSTATE 02000
MESSAGE_LENGTH 25
MESSAGE_TEXT Row not found for DELETE.
DB2_MESSAGE_ID SQL0100
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

I can use a Select statement to perform the equivalent of RPG's Chain operation code. What do I get returned to me on when I do it?

70  dcl-proc LikeChain ;
71    dcl-s wkAccount packed(7) inz(1201) ;
72    dcl-s wkState char(2) ;

73    exec sql SELECT ACCSTE INTO :wkState
                 FROM TESTFILE
                WHERE ACCNBR = :wkAccount ;
74    Diagnostics() ;

75    wkAccount = 0 ;

76    exec sql SELECT ACCSTE INTO :wkState
                 FROM TESTFILE
                WHERE ACCNBR = :wkAccount ;
77    Diagnostics() ;
78  end-proc ;

Line 73: For the successful "chain" I get:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 26
MESSAGE_TEXT Embedded SELECT completed.
DB2_MESSAGE_ID SQL7964
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 76: And the unsuccessful one returns.

Keyword Returned value
DB2_RETURNED_SQLCODE 100
RETURNED_SQLSTATE 02000
MESSAGE_LENGTH 25
MESSAGE_TEXT Row not found for SELECT.
DB2_MESSAGE_ID SQL0100
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

There are two types of Fetch I have used to fetch data from the table/file, and I call them:

  1. Single Fetch – fetches one row/record at a time
  2. Multiple Fetch – fetches more than one row/record at a time

First I am going to demonstrate the information I can get for the Single Fetch.

80  dcl-proc SingleFetch ;
81    dcl-ds InputDs extname('TESTFILE') qualified ;
82    end-ds ;

83    exec sql DECLARE C0 CURSOR FOR
                SELECT * FROM TESTFILE ;
84    Diagnostics() ;

85    exec sql OPEN C0 ;
86    Diagnostics() ;

87    dow (1 = 1) ;
88      exec sql FETCH NEXT FROM C0 INTO :InputDs ;
89      Diagnostics() ;
90      if (ReturnedSqlCode <> ' ') ;
91        leave ;
92      endif ;
93    enddo ;

94    exec sql CLOSE C0 ;
95    Diagnostics() ;
96  end-proc ;

Line 81 and 82: This is the data structure that is going to receive the row/record from the fetch.

Line 83: The values returned after the Declare statement are:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 24
MESSAGE_TEXT CALL statement complete.
DB2_MESSAGE_ID SQL7985
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 85: From the Open statement:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 17
MESSAGE_TEXT Cursor C0 opened.
DB2_MESSAGE_ID SQL7962
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 88: For the first time the Fetch statement is performed:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 30
MESSAGE_TEXT 1 rows fetched from cursor C0.
DB2_MESSAGE_ID SQL7963
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 88: After all the rows have been Fetched, the next Fetch, equivalent of the end of file encountered in RPG, gives me:

Keyword Returned value
DB2_RETURNED_SQLCODE 100
RETURNED_SQLSTATE 02000
MESSAGE_LENGTH 21
MESSAGE_TEXT Row not found for C0.
DB2_MESSAGE_ID SQL0100
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 94: The Close cursor statement returns:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 21
MESSAGE_TEXT Cursor C0 was closed.
DB2_MESSAGE_ID SQL7959
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

What happens if I make a mistake? In this example I am going to use a column/field that is not in my table/file.

100  exec sql DECLARE C_WRONG CURSOR FOR
               SELECT WRONG_FIELD
                 FROM TESTFILE ;
101  Diagnostics() ;

102  exec sql OPEN C_WRONG ;
103  Diagnostics() ;

104  exec sql FETCH NEXT FROM C_WRONG INTO :WrongField ;
105  Diagnostics() ;

106  exec sql CLOSE C_WRONG ;
107  Diagnostics() ;

Line 100: The Declare statement does not return any errors.

Line 102: The error is flagged when the cursor is opened.

Keyword Returned value
DB2_RETURNED_SQLCODE -206
RETURNED_SQLSTATE 42703
MESSAGE_LENGTH 48
MESSAGE_TEXT Column or global variable WRONG_FIELD not found.
DB2_MESSAGE_ID SQL0206
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 104: As the cursor is not open the Fetch statement is going to produce an error.

Keyword Returned value
DB2_RETURNED_SQLCODE -501
RETURNED_SQLSTATE 24501
MESSAGE_LENGTH 24
MESSAGE_TEXT Cursor C_WRONG not open.
DB2_MESSAGE_ID SQL0501
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 106: And you cannot Close a cursor that is not open.

Keyword Returned value
DB2_RETURNED_SQLCODE -501
RETURNED_SQLSTATE 24501
MESSAGE_LENGTH 24
MESSAGE_TEXT Cursor C_WRONG not open.
DB2_MESSAGE_ID SQL0501
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

The Multiple row Fetch is very similar to the Single one, with only a few changes.

110  dcl-proc MultipleFetch ;
111    dcl-ds InputDs extname('TESTFILE') qualified
                dim(9999) ;
112    end-ds ;
113    dcl-s ArrayElements packed(4) inz(%elem(InputDs)) ;

114    exec sql DECLARE C1 CURSOR FOR
                 SELECT * FROM TESTFILE ;
115    Diagnostics() ;

116    exec sql OPEN C1 ;
117    Diagnostics() ;

118    dow (1 = 1) ;
119      exec sql FETCH NEXT FROM C1
                    FOR :ArrayElements ROWS INTO :InputDs ;
120      Diagnostics() ;
121      if (ReturnedSqlCode <> ' ') ;
122        leave ;
123      endif ;
124    enddo ;

125    exec sql CLOSE C1 ;
126    Diagnostics() ;
127  end-proc ;

Line 111: DIM added to turn the data structure into an array, so it can receive multiple rows/records from the Fetch statement.

Line 113: I always like to have a variable containing the number of elements in the array to use in the Fetch statement.

Line 114: This Declare statement returns the same values as the Declare for the Single Fetch.

Line 116: The Open cursor also returns the same values as the Open statement for the Single Fetch.

Line 119: The Multiple row Fetch returns:

Keyword Returned value
DB2_RETURNED_SQLCODE Blank
RETURNED_SQLSTATE 00000
MESSAGE_LENGTH 31
MESSAGE_TEXT 20 rows fetched from cursor C1.
DB2_MESSAGE_ID SQL7963
DB2_MESSAGE_ID1 Blank
DB2_MESSAGE_ID2 Blank

Line 199: The second time the Multiple row Fetch is performed the returned data is the same as the "end of cursor" data for the Single row fetch.

Line 125: The Close cursor statement returns the same information as it did before.

 

When I use the information returned from the GET DIUAGNOSTICS I can handle my SQL errors better, and have a message I can use to present to the user running the program, or to send to the system operator message queue.

You can learn more about this from the IBM website:

 

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

15 comments:

  1. Thank you Simon You just answered the question I did when you wrote the post about QCMDEXEC using Sql

    ReplyDelete
  2. These are very helpful in building an automated error logging and handling service.

    ReplyDelete
  3. Excellent thorough article on this subject! Very helpful.
    Your comment about line 45 expecting invalid data type reminded me of an article I read recently where it said IBM is doing implicit conversions between alpha and numeric fields. I did a search and found one mentioning it back in V5R3.

    ReplyDelete
  4. Excellent article. Thank you Simon

    ReplyDelete
  5. I use the api called Qp0zLprintf to generate my own job logs inside my sqlrpgle programs

    ReplyDelete
  6. Hi,

    I am struggling with this: I want to log, which file and library the sql statement updated. So that i can reuse the wrtelog procedure without having to pass the lib name and filename as a parm.
    According to the SQL diagnostics in the IBM manual, that information is available in the Get Diagnostics.
    But the following is not returning anything.

    dcl-s TableName varchar(128) ;
    // Main
    exec sql
    delete from otpobjt
    where
    cmpcde = :legalcompany and
    cntnum = :approvalnumber;
    writelog();

    ..
    DCL-PROC Writelog ;
    DCL-PI *N;
    END-PI ;

    exec sql
    get diagnostics
    condition 1 :tablename = table_name;

    return ;
    END-PROC ;

    :Tablename or Schemaname for that matter is always blank,

    ReplyDelete
    Replies
    1. Don't put the GET DIAGNOSTICS in a procedure separate from the SQL statement. Put it immediately after the SQL statement.

      Delete
    2. Hi Simon. No matter where Esben puts the GetDiagnostics, it's only going to populate the table_name oi very specific conditions - SQLSTATE classes 09, 23, 27, 42, 44 and 40 (sometimes). "Otherwise the empty string is returned" according to the manual. All items in the condition section seem to be populated only under specific conditions - not after every SQL statement. You must rtfm to find out what conditions populate what items. I wish I knew what to suggest to Esben about getting the table name under any condition. Perhaps another of your readers has an idea about that.

      I have another question for you. Your advice to Esben surprised me since your example was itself using a get diagnostics in a separate procedure from the SQL statement. Are there situations that won't work - i.e., you can't put the get diagnostics in a separate procedure like your example did?

      Delete
    3. Susan, you are correct, and what I said was incorrect, wherever the call to the procedure happens it will always return the information from the last executed SQL statement.

      Delete
  7. I’ve used this a little and not quite to the extent listed here but this is so very helpful. Everyone should use this!

    ReplyDelete
  8. That’s very interesting and very helpful. I have created one generic program for diagnostic and uses it in my SQL programs.

    ReplyDelete
  9. Andrea CastignaniJune 5, 2020 at 2:38 PM

    Tried! Top 👍

    ReplyDelete
  10. This is awesome! Thanks Simon!

    ReplyDelete
  11. I see too many programmers who either don't monitor for any errors at all, or who just treat all errors the same. I use "get diagnostics" commonly and then send the SQL error code out within message data on an error message or write it to a log file. Very useful indeed.

    ReplyDelete

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.