Sometimes the db2 exceptions are cryptic or hidden by the application layer. In this case the only way to find the db2 SQL exception is to investigate in db2 logs.
First check the log level setup in the Database Manager Configuration.
Log in as the user under which db2 instance runs, in my case db2inst1. Check the current log level:
[db2inst1@localhost ~]$ db2 connect to MyDB
[db2inst1@localhost ~]$ db2 get DBM CFG | grep DIAGLEVEL
Diagnostic error capture level (DIAGLEVEL) = 3
Change the level to 4 which is debug level. (4 – All errors, warnings, informational messages, event messages, and administration notification messages are captured.)
[db2inst1@localhost ~]$ db2 update DBM CFG USING DIAGLEVEL 4
Check that the change was done:
[db2inst1@localhost ~]$ db2 get DBM CFG | grep DIAGLEVEL
Diagnostic error capture level (DIAGLEVEL) = 4
Use db2diag to investigate the logs of db2.
Example:
In case we try to make an insert with a value that exceeds the length of a column, in our case the 7th column is limited at 4 chars and we try to insert ‘c0005’:
insert into reason values (228513,1,null,'REMOVED',null,null,'c0005','blah','CANCEL_REASON',0,'ALLOW')
The exception reported at the application level is sometimes just:
SQLCODE=-433, SQLSTATE=22001
This can be caused by the fact we have an intermediate layer between the db2 and our enterprise application, for example hibernate.
Investigate the logs using db2diag and filter the logs to display only entries for the wanted db and instance.
db2diag -g db=MyDB,instance=db2inst1
We can find the last error entry as:
2016-08-29-10.49.41.733593+180 I106041448E863 LEVEL: Info
PID : 13956 TID : 139725390210816 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : MyDB
APPHDL : 0-818 APPID: 7F000001.8130.160829073808
AUTHID : DB2INST1 HOSTNAME: localhost.localdomain
EDUID : 39 EDUNAME: db2agent (MyDB)
FUNCTION: DB2 UDB, oper system services, sqlofica, probe:10
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -433 sqlerrml: 5
sqlerrmc: c0005
sqlerrp : SQLRI814
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFE415 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 22001
Interpret the error entry see http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.trb.doc/doc/c0020815.html
1. Timestamp: 2016-08-29-10.49.41.733593+180
2. Record ID field: I106041448E863
3. The diagnostic level of the message: Info
4. The process ID: 13956
5. The thread ID: 139725390210816
6. Process name: db2sysc
7. The name of the instance generating the message: db2inst1
8. For multi-partition systems, the database partition generating the message. In a non-partitioned database, the value is “000”: 000
9. The database name: MyDB
10. The application handle: 0-818
11. TCP/IP-generated application ID: 7F000001.8130.160829073808
12. The authorization identifier: DB2INST1
13. The engine dispatchable unit identifier: 39
14. The name of the engine dispatchable unit: db2agent (MyDB)
15. The product name (“DB2”), component name (“data management”), and function name (“sqlInitDBCB”) that is writing the message (as well as the probe point (“4820”) within the function: DB2 UDB, oper system services, sqlofica, probe:10
16. The information returned by a called function:
SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -433 sqlerrml: 5
sqlerrmc: c0005
sqlerrp : SQLRI814
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFE415 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 22001
To interpret the details section from 16. see Description of SQLCA fields
The fieds that have meaning in our example:
sqlcode: -433 = Contains the SQL return code. In our case SQL error code -433
sqlerrml: 5 = Length indicator for SQLERRMC, in the range 0 through 70. 0 means that the value of SQLERRMC is not pertinent. In our case length is 5.
sqlerrmc: c0005 = Contains one or more tokens, separated by X’FF’, that are substituted for variables in the descriptions of error conditions. It may contain truncated tokens. A message length of 70 bytes indicates a possible truncation. In our case we have the value of the wrong value we try to insert “c0005”
sqlerrd(1) = 0x801A006D internal error code
sqlerrd(5) = 0xFFFFE415 Contains the position or column of a syntax error for a PREPARE or EXECUTE IMMEDIATE statement.
sqlstate: 22001 = SQL state 220001.
To interpret the sqlcode see the list of sqlcodes
In our case sqlcode:-443 means “VALUE value IS TOO LONG”
[paypal_donation_button]