IBM Support

IT05446: SYSTOOLS.ADMIN_TASK_STATUS DOES NOT SHOW THE RIGHT SQLCODE OF A FAILED PROCEDURE, WHEN IT'S ADD BY DBMS_JOB.SUMBIT.

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

APAR status

  • Closed as program error.

Error description

  • 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.
    

Local fix

  • Use sysproc.admin_task_add procedure.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 9.7 Fix Pack 11                               *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 9.7 Fix Pack 11
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT05446

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2014-11-09

  • Closed date

    2017-05-03

  • Last modified date

    2017-05-03

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT05446

Modified date: 03 May 2017