Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: DBMS_JOB.SUBMIT error

    Hi,

    I am trying to submit a job. This is my first time to do these, I read few docs & i tried . I am getting the below error.can some one kindly correct me plz

    CREATE PROCEDURE tyur_wiut_35789 (IN l_tiqti_r VARCHAR(32672),IN l_unk INTEGER,IN dbname varchar(100),IN tablename varchar(100))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE JOBID INTEGER;

    CALL DBMS_JOB.SUBMIT(jobid,'DY67LSTR('''||l_tiqti_r ||''','||l_unk ||','''||dbname ||''','''|| tablename ||''')',null);
    COMMIT;

    END P1

    This is the way i am calling
    call tyur_wiut_35789 ('FIRSTNAME = aty69215_qurpq(FIRSTNAME)',1,'sample','akl.cabs');

    Its executing successflly. But I don't find any result. Here I am trying to update a table. but the table column remains same.

    Can someone tell me what I am missing here
    Last edited by HABBIE; 03-09-15 at 09:00.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as always : db2 platform - level - complete code
    has debug been tried to see the real update statement
    variables are maybe wrongly assigned and no rows found....
    is sqlcode intercepted and Zero ?
    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

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    If you are calling from the CLP, you need to quote the literals on the CALL to the sproc like this:

    Code:
    db2 "call tyur_wiut_35789 ('''FIRSTNAME = aty69215_qurpq(FIRSTNAME)''',1,'''sample''','''akl.cabs''')"
    i.e. three single quotes around the literal arguments, and double-quotes around the call statement.


    Additionally, the first parameter to dbms_job.submit should be decimal(20) and the second parameter to dbms_job.submit() is varchar(1024) but your example does not respect that.

    You should find these simple errors if you debug your sproc. For example by assembling the sql-stmt into a variable, and examining its value before using dbms_job.submit() - that way lets you see exactly what's happening with the quotes - you want them preserved. So if you are not calling the sproc from the CLP, use that method to discover for yourself the correct quoting.

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

    You should make a job to be scheduled successfully first of all.
    To do this you:
    - have to ensure that you set db2 registry variable DB2_ATS_ENABLE to YES/TRUE/ON/1 like:
    $ db2set DB2_ATS_ENABLE=1
    - use CALL DBMS_JOB.SUBMIT(jobid, 'CALL DY67LSTR(...)');
    - should note that your job will be run not immediately, but ~ in 5 mins

    You can check the statuses of your job invocations in the SYSTOOLS.ADMIN_TASK_STATUS table.
    Regards,
    Mark.

  5. #5
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply .
    I have taken all the necessary things before doing this, enabled the registry variable, created systoolspace etc & even the time factor too
    I am using Db2 c express on windows.
    I am calling my procedure from Ibm data studio.

    When I am trying to debug I can find all the values required & at the line of call dbms_job.submit line it is going to the following code
    ALTER MODULE SYSIBMADM.DBMS_JOB ADD PROCEDURE SYS_INIT()
    SPECIFIC DBMS_JOB_SYS_INIT
    BEGIN
    DECLARE stmt VARCHAR(200);
    DECLARE found INTEGER;
    DECLARE SQLCODE1 INTEGER;
    DECLARE SQLCODE INTEGER;
    /* Ignore error related to object that already exists */
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42710'
    SET SQLCODE1 = SQLCODE;
    SELECT COUNT(*) INTO found FROM SYSCAT.TABLES WHERE TABSCHEMA = 'SYSTOOLS' AND TABNAME = 'ADMIN_TASK_LIST';
    IF found = 0
    THEN
    CALL DBMS_JOB.INIT_ATS();
    END IF;
    END

    and running with zero errors.

    debugging values :

    Diagnostic Information
    tablename akl.cabs
    dbname sample
    l_unk 1
    l_tiqti_r FIRSTNAME = aty69215_qurpq(FIRSTNAME)
    JOBID +00000000000000000051.
    SYSIBMADM.DBMS_JOB.MAX_FAILURE_COUNT 16
    SYSIBMADM.DBMS_JOB.TASK_NAME_PREFIX DBMS_JOB_TASK_


    I found the running state from the below query as
    SELECT * FROM mentisdb.systools.admin_task_status
    WHERE name LIKE DBMS_JOB.TASK_NAME_PREFIX || '_%';

    based upon the current job the status is saying as Running. After long hours it is still showing as running. When i step out from the database & step in it is going into unknown stage.

    I don't know what is going behind this. On debugging also I find nothing.
    can some one kindly let me know what I have to do with this.
    Last edited by HABBIE; 03-10-15 at 07:49.

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Sorry, but I don't understand what you're doing.
    Every time you provide different code sample with different problem descriptions.
    Let's try a simple test. Run the following script as is without any changes from the db2 clp:

    Code:
    --#SET TERMINATOR @
    create table test_submit (ts timestamp, par varchar(10)) in userspace1@
    
    create procedure test_submit(p varchar(10))
    begin
      insert into test_submit (ts, par) values (current timestamp, p);
    end@
    
    call DBMS_JOB.SUBMIT (?, 'CALL TEST_SUBMIT(''1'')', NULL)@
    -- wait for 5-7 minutes and run the following
    Code:
    select * from test_submit
    SELECT * from SYSTOOLS.ADMIN_TASK_STATUS order by begin_time desc fetch first 1 row only
    Do you have any errors?
    What's the result of 2 latest queries?
    Regards,
    Mark.

  7. #7
    Join Date
    Jul 2014
    Posts
    294
    Thanks Mark .The code snippet which you gave is working fine & i can find the results too. Initially I tried with these kind of values itself & now I am trying to pass some input arguments which will execute dynamically & I am getting the issues.
    Last edited by HABBIE; 03-10-15 at 09:27.

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    debugging is a structured process - not jumping from left to right...
    as indicated at entry 1 : have you captured the real executed code before executing it - to see what is really passed and causing the error ?
    do step by step - try to eliminate not-needed code and see what is going on
    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 Przytula.
    mark I tried the same way how you mentioned the code snippet above but with dynamically.
    this is the example which I tried,

    CREATE TABLE DYNM_T1 (RES VARCHAR(100), VALUE INTEGER)

    ------------
    CREATE PROCEDURE DYNM1 (IN VAR1 INTEGER,IN VAR2 INTEGER,IN RES VARCHAR(100))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE RESULT INTEGER;
    DECLARE TEXT VARCHAR(1000);
    SET RESULT = VAR1 + VAR2 ;

    SET TEXT = 'INSERT INTO DYNM_T1 VALUES ('''|| RES ||''','|| RESULT ||')';
    EXECUTE IMMEDIATE TEXT;
    --INSERT INTO DYNM_T VALUES (RESULT);
    CALL DBMS_OUTPUT.PUT_LINE(RESULT);

    END P1

    -------

    CREATE PROCEDURE DYNM_JOB (IN VAR1 INTEGER,IN VAR2 INTEGER,IN RES VARCHAR(100))
    DYNAMIC RESULT SETS 1
    P1: BEGIN
    DECLARE jobid INTEGER;

    CALL DBMS_JOB.SUBMIT(jobid,'DYNM1('''||RES ||''','|| VAR1 ||',' || VAR2|| ');',null);
    END P1

    ---------------------

    I am calling the procedure from Data studio:
    Running & executed successfully with out error.

    But there z no records in table DYNM_T1.

    from this query
    SELECT * from SYSTOOLS.ADMIN_TASK_STATUS order by begin_time desc fetch first 1 row only;

    status is showing as complete. But still no records in the table.
    what I want to say is I am missing something when trying to play with dynamic values on dbms_job.submit.

    Can you kindly let me know what I am missing here

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
  •