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:
- Inserting rows to a table/file
- Updating rows in a table/file
- Selecting one row from a table/file
- Deleting rows from a table/file
- 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:
- Single Fetch – fetches one row/record at a time
- 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.
Thank you Simon You just answered the question I did when you wrote the post about QCMDEXEC using Sql
ReplyDeleteThese are very helpful in building an automated error logging and handling service.
ReplyDeletevery good!!!
ReplyDeleteExcellent thorough article on this subject! Very helpful.
ReplyDeleteYour 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.
Excellent article. Thank you Simon
ReplyDeleteI use the api called Qp0zLprintf to generate my own job logs inside my sqlrpgle programs
ReplyDeleteHi,
ReplyDeleteI 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,
Don't put the GET DIAGNOSTICS in a procedure separate from the SQL statement. Put it immediately after the SQL statement.
DeleteHi 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.
DeleteI 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?
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.
DeleteI’ve used this a little and not quite to the extent listed here but this is so very helpful. Everyone should use this!
ReplyDeleteThat’s very interesting and very helpful. I have created one generic program for diagnostic and uses it in my SQL programs.
ReplyDeleteTried! Top 👍
ReplyDeleteThis is awesome! Thanks Simon!
ReplyDeleteI 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