Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    2

    Unanswered: get retrieval message from sysproc.admin_cmd procedure in another Procedure

    Hello DBForum,
    i created a stored procedure in which i call the sysproc.admin_cmd to perform a load.
    My own procedure is fetching the results and is giving back informations like rows commited, rows inserted and so on.
    As well i get MSG_RETRIEVAL and MSG_REMOVAL.<-- These 2 are important.

    When i call my procedure with right Paths and a valid load file, everything works fine and I'll get the Messages.
    (e.g. CALL SYSPROC.ADMIN_REMOVE_MSGS('126190848_INSTANCE'))
    The Problem occures when I enter for example a wrong File-Path. I only get an SQL Warning and thats it.

    Parameter Name : P_MSG_REMOVAL
    Parameter Value : -

    SQL3107W There is at least one warning message in the message file.


    When i perform a call directly on the admin_cmd I'll get the Messages with the error on it.

    So now my question: Does anybody know how to deliver the message from the admin_cmd to my SP while i trigger an error on purpose?
    Ist kinda weird, because with correct Input Data everything works fine, but with invalid ist not.

    Some Code:
    PHP Code:
    CALL SYSPROC.ADMIN_CMD('LOAD FROM ' || p_infile || ' OF ' || p_infile_format || ' messages on server replace into' || p_target_table || ' NONRECOVERABLE'); 
    PHP Code:
    ASSOCIATE RESULT SET LOCATORS(result1WITH PROCEDURE sysproc.ADMIN_CMD;
            
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;
            
    FETCH rsCur INTO ROWS_READROWS_SKIPPEDROWS_INSERTEDROWS_UPDATEDROWS_REJECTEDROWS_COMMITTEDROWS_PARTITIONEDNUM_AGENTINFO_ENTRIESMSG_RETRIEVALMSG_REMOVAL;
            
    SET p_ROWS_READ ROWS_READ;
            
    SET p_ROWS_SKIPPED ROWS_SKIPPED;
            
    SET p_ROWS_INSERTED ROWS_INSERTED;
            
    SET p_ROWS_UPDATED ROWS_UPDATED;
            
    SET p_ROWS_REJECTED ROWS_REJECTED;
            
    SET p_ROWS_COMMITTED ROWS_COMMITTED;
            
    SET p_ROWS_PARTITIONED ROWS_PARTITIONED;
            
    SET p_NUM_AGENTINFO_ENTRIES NUM_AGENTINFO_ENTRIES;
            
    SET p_MSG_RETRIEVAL MSG_RETRIEVAL;
            
    SET p_MSG_REMOVAL MSG_REMOVAL

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

    Have you tried to handle SQLSTATE=01H52 with something like this?
    Code:
    declare exit handler for SQLSTATE '01H52'
      GET DIAGNOSTICS EXCEPTION 1 L_MSG = MESSAGE_TEXT, L_TOK = DB2_TOKEN_STRING;
    Regards,
    Mark.

  3. #3
    Join Date
    Jul 2016
    Posts
    2
    Hello mark,
    thx for your fast Reply. I've tried some error handling, but Iam not used to it that much, so it is kinda difficult for me.
    I edited in the way u wrote, like this:

    2 new Output Parameter
    PHP Code:
    OUT message VARCHAR(254),
    out L_TOK varchar(254
    PHP Code:
    declare exit handler for SQLSTATE '01H52'
    GET DIAGNOSTICS EXCEPTION 1 message MESSAGE_TEXT,
    L_TOK DB2_TOKEN_STRING;
    CALL SYSPROC.ADMIN_CMD('LOAD FROM ' || p_infile || ' OF ' || p_infile_format || ' MESSAGES ON SERVER REPLACE INTO ' || p_target_table || ' NONRECOVERABLE'); 
    PHP Code:
    ASSOCIATE RESULT SET LOCATORS(result1WITH PROCEDURE sysproc.ADMIN_CMD;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1
    ..............

    When i do the call for my procedure i get 2 new params, but both are empty, even if the load is successfull ...

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

    Code:
    --#SET TERMINATOR @
    
    set serveroutput on@
    
    begin
      declare command      varchar(1024) default 'load from ... messages on server ... into mytable nonrecoverable';
      declare category     varchar(16);
      declare msg_SQLCODE  varchar(10);
      declare msg_MSG      varchar(1024);
      declare SQLSTATE     CHAR(5) DEFAULT '00000';
      declare SQLCODE      INT DEFAULT 0;
      declare rc_SQLCODE   INT;
      declare rc_SQLSTATE  CHAR(5);
      declare ROWS_READ, ROWS_SKIPPED, ROWS_LOADED, ROWS_REJECTED, ROWS_DELETED, ROWS_COMMITTED, ROWS_PARTITIONED, NUM_AGENTINFO_ENTRIES bigint;
      declare MSG_RETRIEVAL, MSG_REMOVAL varchar(1024);
    
      declare l1 RESULT_SET_LOCATOR VARYING;
      declare msg_c cursor for s1;
    
      declare continue handler for sqlexception, sqlwarning
      begin
        GET DIAGNOSTICS EXCEPTION 1 msg_MSG = MESSAGE_TEXT;
        VALUES (SQLCODE, SQLSTATE) INTO rc_SQLCODE, rc_SQLSTATE;
        call dbms_output.put_line('--- error ---');
        call dbms_output.put_line('SQLCODE : ' || rc_SQLCODE);
        call dbms_output.put_line('SQLSTATE: ' || rc_SQLSTATE);
        call dbms_output.put_line('MESSAGE : ' || msg_MSG);
        call dbms_output.put_line('COMMAND : ' || command);
        call dbms_output.put_line('--- error ---');
      end;
    
      call sysproc.admin_cmd(command);
      associate locator (l1) with procedure sysproc.admin_cmd;
      allocate c1 cursor for result set l1;
      --call dbms_output.put_line('CALLED');
      -- Retrieve the load messages from the server
      fetch c1 into ROWS_READ, ROWS_SKIPPED, ROWS_LOADED, ROWS_REJECTED, ROWS_DELETED, ROWS_COMMITTED, ROWS_PARTITIONED, NUM_AGENTINFO_ENTRIES, MSG_RETRIEVAL, MSG_REMOVAL;
      call dbms_output.put_line('READ  : ' || ROWS_READ);
      call dbms_output.put_line('LOADED: ' || ROWS_LOADED);
      close c1;
      -- messages
      if (MSG_RETRIEVAL IS NOT NULL) then
        set command = MSG_RETRIEVAL;
        prepare s1 from command;
        open msg_c;
        fetch msg_c into msg_SQLCODE, msg_MSG;
        call dbms_output.put_line('--- messages ---');
        while SQLSTATE<>'02000' do
          call dbms_output.put_line(msg_SQLCODE||': '||msg_MSG);
          fetch msg_c into msg_SQLCODE, msg_msg;
        end while;
        close msg_c;
        call dbms_output.put_line('--- messages ---');
        -- Remove message from the server
        set command = MSG_REMOVAL;
        call dbms_output.put_line('removal: '||command);
        execute immediate command;
      end if;
    
    end@
    Place this into some file, correct the load command appropriately and run it:
    db2 -f myfile.sql
    Regards,
    Mark.

Posting Permissions

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