SQLCODE column in SYSTOOLS.ADMIN_TASK_STATUS is supposed to show
the SQLCODE returned from the procedure. For instance, if the
procedure fails with SQL0727N, the column must reflect the
SQLCODE returned from the failed procedure.
But when the task is added by DBMS_JOB.SUBMIT procedure, SQLCODE
column shows NULL(0) even though the procedure fails with a
certain SQLCODE.
This is easily reproducible.
1) Create a simple SP with the SELECT statement referencing a
table.
2) Submit a Job
3) Drop the table referenced in the SP (to get -727, with -204)
4) Wait for the job to be executed
5) Check the db2diag.log to see if the procedure failed.
2014-10-31-02.22.34.365780-240 I4785A986 LEVEL: Error
PID : 62456208 TID : 14166 PROC : db2sysc
INSTANCE: jhhyun2 NODE : 000 DB : SAMPLE
APPHDL : 0-161 APPID: *LOCAL.DB2.141031062235
AUTHID : JHHYUN2
EDUID : 14166 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, access plan manager,
sqlraProcessPrepAnonBlock, probe:310
MESSAGE : ZRC=0x80310004=-2144272380=SQLNN_E_NFOUND
"Could not find an internal object required to process
the command"
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCAL sqlcabc: 136 sqlcode: -727 sqlerrml: 29
sqlerrmc: 3 -204 42704 JHHYUN2.TEST_TAB
sqlerrp : SQLNQ1FC
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3)
0x00000001
(4) 0x00000000 (5) 0xFFFFFFF6 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 00000
6) Check the SQLCODE in SYSTOOLS.ADMIN_TASK_STATUS
select substr(name,1,20) taskname, taskid, status, sqlcode,
begin_time, end_time from systools.admin_task_status
TASKNAME TASKID STATUS SQLCODE
BEGIN_TIME END_TIME
-------------------- ----------- ---------- -----------
-------------------------- --------------------------
DBMS_JOB_TASK_4 64 COMPLETE 0
2014-10-31-02.22.34.252778 2014-10-31-02.22.34.484005
It does not happen when the task is added by
sysproc.admin_task_add. |
