Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Update statement in dbms_jobs

    Hi,

    I tried for the DBMS_JOB.SUBMIT in db2 c express on windows.
    I tried with insert , delete & those are working fine. But Update is not happening through Job submission.
    This is the code which I tried

    CREATE or REPLACE PROCEDURE D_JUMP1(IN DBNAME VARCHAR(10),IN TBNAME VARCHAR(100),IN f_value VARCHAR(32672))
    p1:BEGIN
    DECLARE jobno NUMBER;
    DECLARE TEXT VARCHAR(1000);

    SET TEXT = (' UPDATE ' || DBNAME ||'.'|| TBNAME ||' SET '|| f_value ||'') ;
    execute IMMEDIATE TEXT;
    COMMIT;
    END p1
    --------

    The above procedure is working fine I can able to update the desired table.

    Now I am trying to submit the same procedure through a JOB. I can able to submit the job. I can view the status as completed after the job.

    CREATE or REPLACE PROCEDURE D_B_JUMP1(IN DNAME varchar(10),IN TNAME varchar(20),IN f_vlue VARCHAR(100))
    p1:BEGIN
    DECLARE jobno NUMBER;
    call DBMS_JOB.SUBMIT
    (job => JOBNO
    ,what => 'D_JUMP1 ('''|| DNAME ||''','''|| TNAME ||''','''|| f_vlue ||''');'
    ,next_date => null
    ,interval => null);
    COMMIT;

    END p1

    Using these two queries i am finding the status of the job based on the task Id
    SELECT * from SYSTOOLS.ADMIN_TASK_STATUS order by begin_time desc fetch first 1 row only;
    SELECT * from SYSTOOLS.ADMIN_TASK_LIST order by begin_time desc fetch first 1 row only;

    But after finding the status as complete from query 1. I don't find any changes on the table . I mean table is not updated from the job.

    on debugging also I don't find any thing except the job number.
    My question is Does UPdate is supported via DBMS_JOBs .If so what I am missing in the above code. Why my job is not working after it reaching the stage of status complete.

    Thanks

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Show us the following fields of the records corresponding to your job:

    SELECT procedure_input from SYSTOOLS.ADMIN_TASK_LIST
    SELECT sqlcode, sqlerrmc from SYSTOOLS.ADMIN_TASK_STATUS
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2014
    Posts
    294
    Mark,

    Thanks for the reply. These are the outputs for the corresponding jobs.

    SELECT procedure_input from SYSTOOLS.ADMIN_TASK_LIST where TASKID= 201;

    VALUES('DBMS_JOB_TASK_93','NULL','D_JUMP1 (''SAMPLE'',''REG.CABS'',''FIRSTNAME = reg_f_fixed_char_repos(FIRSTNAME)'')')

    SELECT sqlcode, sqlerrmc from SYSTOOLS.ADMIN_TASK_STATUS where TASKID= 201;

    SQLCODE = 0
    SQLERRMC = NULL

    Kindly let me know what I am missing here.

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Did I get you correct that this procedure does what it should do if you run it alone like this:
    Code:
    call d_jdump1('SAMPLE', 'REG.CABS', 'FIRSTNAME = reg_f_fixed_char_repos(FIRSTNAME)')
    but it does nothing if you submit it via a job?
    If so, that's really strange.

    Try the following as is, it works for me.

    Code:
    create table test_submit (ts timestamp default current timestamp, v varchar(10)) in userspace1@
    insert into test_submit (v) values 'test'@
    
    create or replace procedure test_submit(p_stmt varchar(4000))
    begin
      execute immediate p_stmt;
    end@
    
    call DBMS_JOB.SUBMIT (?, 'CALL TEST_SUBMIT(''update test_submit set v=upper(v)'')', NULL)@
    Regards,
    Mark.

  5. #5
    Join Date
    Jul 2014
    Posts
    294
    Hi Mark,

    Thanks for the inputs.
    I found something as, previously I tried to update a table on some other database sitting on one database.Job was submitted & able to find the status as complete.but the table is not updated.I am having the admin privilege.

    Now I have created a table on the same database & tried to perform the update on the same table via Jobs. In this way the table got updated properly.

    Should I need to give any additional privileges to run the update command via jobs on other databases. Kindly correct me if I am wrong.

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    It's not related to jobs. If you run your procedure directly and it does the work, then it should do the same being run via job.
    Regards,
    Mark.

  7. #7
    Join Date
    Jul 2014
    Posts
    294
    k. I am mentioning clearly

    call DBMS_JOB.SUBMIT (?, 'CALL D_JUMP1(''UPDATE TEST1.REG1.CABS SET
    FIRSTNAME = reg_f_fixed_char_repos(FIRSTNAME)'')',NULL);

    This job is working fine & able to see the update rows on the table too.
    This is trying to update the own table in TEST1 database.

    Now my problem is

    call DBMS_JOB.SUBMIT (?, 'CALL D_JUMP1(''UPDATE SAMPLE.REG.CABS SET
    FIRSTNAME = reg_f_fixed_char_repos(FIRSTNAME)'')',NULL);

    Now from TEST1 database I am trying to update the table CABS on SAMPLE database where SAMPLE is a remote database for TEST1.
    TEST1 is having the SYSADM authority.
    This job is running fine with no errors.But records on CABS table are not updated.

    I tried in all the ways. But not able to find the reason. Even SQL code is also showing zero for the second job.
    Last edited by HABBIE; 03-16-15 at 11:27.

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so why are you using 3 part name for table
    just connect to db test1 and execute first call : update reg1.cabs
    connect to sample : update reg.cabs ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  9. #9
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply.
    I tried with schema.tablename still the table values are not updated.

    This is happening due to the commit failure on remote database . I am displaying my logs can you kindly please let me know what to change to avoid this.

    2015-03-17-11.05.15.472000+330 I110103525F622 LEVEL: Error
    PID : 8508 TID : 6440 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPHDL : 0-361 APPID: *LOCAL.DB2.150317053535
    AUTHID : MANGO HOSTNAME: MANGO-04
    EDUID : 6440 EDUNAME: db2agent (TEST1)
    FUNCTION: DB2 UDB, relation data serv, sqlr_f2pc, probe:100
    DATA #1 : String, 49 bytes
    Rollback due to failed to end transaction
    RC =
    DATA #2 : Hexdump, 4 bytes
    0x0000003EE832E0B0 : 6001 2680 `.&.

    2015-03-17-11.05.15.487000+330 I110104149F916 LEVEL: Error
    PID : 8508 TID : 6440 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPHDL : 0-361 APPID: *LOCAL.DB2.150317053535
    AUTHID : MANGO HOSTNAME: MANGO-04
    EDUID : 6440 EDUNAME: db2agent (TEST1)
    FUNCTION: DB2 UDB, relation data serv, sqlr_f2pc, probe:150
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1822 sqlerrml: 42
    sqlerrmc: SAMPLE sqlqgFedEnd: xastate is not ACTIVE
    sqlerrp : SQLQ0460
    sqlerrd : (1) 0x8026006D (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate: 560BD

    2015-03-17-11.05.15.487000+330 I110105067F513 LEVEL: Error
    PID : 8508 TID : 6440 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPHDL : 0-361 APPID: *LOCAL.DB2.150317053535
    AUTHID : MANGO HOSTNAME: MANGO-04
    EDUID : 6440 EDUNAME: db2agent (TEST1)
    FUNCTION: DB2 UDB, Query Gateway, sqlqgFedRollback, probe:130
    MESSAGE : sqlqgFedrollback: skip rollback because not connected!

    2015-03-17-11.05.15.487000+330 I110105582F661 LEVEL: Error
    PID : 8508 TID : 6440 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPHDL : 0-361 APPID: *LOCAL.DB2.150317053535
    AUTHID : MANGO HOSTNAME: MANGO-04
    EDUID : 6440 EDUNAME: db2agent (TEST1)
    FUNCTION: DB2 UDB, relation data serv, sqlrrcom_dps, probe:300
    RETCODE : ZRC=0x80100028=-2146435032=SQLP_RBONLY
    "Transaction has been marked as rollback only."
    DIA8040C XA error with request type of "". Only a ROLLBACK operation
    is allowed.

    2015-03-17-11.05.15.503000+330 I110106245F656 LEVEL: Error
    PID : 8508 TID : 6440 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPHDL : 0-361 APPID: *LOCAL.DB2.150317053535
    AUTHID : MANGO HOSTNAME: MANGO-04
    EDUID : 6440 EDUNAME: db2agent (TEST1)
    FUNCTION: DB2 UDB, relation data serv, sqlrrcom, probe:27
    RETCODE : ZRC=0x80100028=-2146435032=SQLP_RBONLY
    "Transaction has been marked as rollback only."
    DIA8040C XA error with request type of "". Only a ROLLBACK operation
    is allowed.

    2015-03-17-11.05.15.503000+330 E110106903F555 LEVEL: Error
    PID : 8824 TID : 9672 PROC : db2fmp64.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPID : *LOCAL.DB2.150317053535
    HOSTNAME: MANGO-04
    EDUID : 9672
    FUNCTION: DB2 UDB, Administrative Task Scheduler, AtsConnection::commit, probe:900
    MESSAGE : ECF=0x82BA00E1=-2101739295
    DATA #1 : String, 124 bytes
    [IBM][CLI Driver][DB2/NT64] SQL0903N COMMIT statement failed, transaction rolled back. Reason code: "2". SQLSTATE=40504

    2015-03-17-11.05.15.503000+330 I110107460F426 LEVEL: Error
    PID : 8824 TID : 9672 PROC : db2fmp64.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPID : *LOCAL.DB2.150317053535
    HOSTNAME: MANGO-04
    EDUID : 9672
    FUNCTION: DB2 UDB, Administrative Task Scheduler, AtsTask::run, probe:1300
    MESSAGE : ECF=0x82BA00E1=-2101739295
    DATA #1 : Codepath, 8 bytes
    4
    Last edited by HABBIE; 03-17-15 at 04:27.

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Do you use a commit statement in the procedure? If so, try to remove it.
    Regards,
    Mark.

  11. #11
    Join Date
    Jul 2014
    Posts
    294
    Mark .there is no commit in the procedure

  12. #12
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    The post by Habbie on 12/March shows procedure D_B_JUMP1 with a COMMIT statement. What is the current code?

  13. #13
    Join Date
    Jul 2014
    Posts
    294
    I mean removed the commit & tried still it is showing the same.

    This is the my logs
    2015-03-17-17.51.56.822000+330 E110703219F555 LEVEL: Error
    PID : 2460 TID : 2992 PROC : db2fmp64.exe
    INSTANCE: DB2 NODE : 000 DB : TEST1
    APPID : *LOCAL.DB2.150317122211
    HOSTNAME: MANGO-04
    EDUID : 2992
    FUNCTION: DB2 UDB, Administrative Task Scheduler, AtsConnection::commit, probe:900
    MESSAGE : ECF=0x82BA00E1=-2101739295
    DATA #1 : String, 124 bytes
    [IBM][CLI Driver][DB2/NT64] SQL0903N COMMIT statement failed, transaction rolled back. Reason code: "2". SQLSTATE=40504

    This is the log without commit in the procedure.
    Kindly let me know what is this please

  14. #14
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You have to use 2PC server option to run the procedure doing federated update via DB2 ATS.

    Try to set this option for your server object like this:
    Code:
    ALTER SERVER server_for_the_sample_database OPTIONS (ADD db2_two_phase_commit 'Y');
    I'm not sure if this change will take an effect immediately (probably not), so restart the federated instance to be sure.
    I have 2 exactly the same server definitions except this db2_two_phase_commit option.
    Direct call of such a procedure changing remote table works on both servers, but job only works on the server with this option set. Probably this is because DB2 ATS does changes in some federated database tables and remote data source within a single UOW.
    Regards,
    Mark.

  15. #15
    Join Date
    Jul 2014
    Posts
    294
    mark,

    When I am creating the server I mentioned this option itself.

    this is my server creation statement, from my procedure

    SET MANGO_SERVER = 'CREATE SERVER "'||DBNAME||'" TYPE "'||TYPE||'" VERSION ''10.5'' '
    ||'WRAPPER "MENTIS_WRAPPER" AUTHORIZATION "'||USERID||'" PASSWORD "'||"PASSWORD"||'" '
    ||'OPTIONS (ADD DBNAME '''||DBNAME||''',DB2_TWO_PHASE_COMMIT ''Y'')';

    execute immediate MANGO_SERVER;

    My server is running with DB2_TWO_PHASE_COMMIT option enabled.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •