Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: How many rows loaded ??

    Hi ,

    Any idea how can I get num_rows loaded in the target table.

    Code:
    call admin_cmd('load from (select * from T1 fetch first 400 rows only) of cursor insert into T2 nonrecoverable');
    
    select ROWS_LOADED into v_num_rows from table( admin_get_msgs(operation_id) as msg);
    Can some one correct me. I do not know how to find operation_id

    regards

    DBFinder

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have a look at the usage notes

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center


    Quote Originally Posted by DBFinder View Post
    Hi ,

    Any idea how can I get num_rows loaded in the target table.

    Code:
    call admin_cmd('load from (select * from T1 fetch first 400 rows only) of cursor insert into T2 nonrecoverable');
    
    select ROWS_LOADED into v_num_rows from table( admin_get_msgs(operation_id) as msg);
    Can some one correct me. I do not know how to find operation_id

    regards

    DBFinder
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Had a look at that few times

    Usage notes
    The query statement that invokes this table function with the appropriate operation_id can be found in the MSG_RETRIEVAL column of the first result set returned by the ADMIN_CMD procedure.
    Can some one give me example how a can I retrieve First Result Set to obtain operation_id


    Regards

    DBFinder

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Reading your orignal post again, if all you want is the number of rows loaded, then you can use rows_loaded in the result set ...

    But if you want more information, say, when you get an error etc, then use MESSAGES ON SERVER clause in the load command.

    here is an example

    Code:
     
    db2 "call admin_cmd('load from (select * from t10) of cursor messages on server  insert into t1 nonrecoverable')"
     
     Result set 1
     --------------
     ROWS_READ            ROWS_SKIPPED         ROWS_LOADED          ROWS_REJECTED        ROWS_DELETED         ROWS_COMMITTED       ROWS_PARTITIONED     NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    MSG_REMOVAL                                                                                                                                                                                                                                                                                                                                                                                                                    
     -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                   449596                    0               449596                    0                    0               449596                    -                     - SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('1389781381_DB2INST1')) AS MSG                                                                                                                                                                                                                                                                                                                                                                                                                                             CALL SYSPROC.ADMIN_REMOVE_MSGS('1389781381_DB2INST1')                                                                                                                                                                                                                                                                                                                                                                          
     1 record(s) selected.
     Return Status = 0
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Well,

    I need to use ADMIN_CMD in Stored Procedure.
    Where I get operation_id to retrive result set 1 ??.


    rgds
    DBFinder

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    duplicate answer

  7. #7
    Join Date
    Jul 2009
    Posts
    150

    Smile

    Quote Originally Posted by db2girl View Post
    duplicate answer
    Oh, girl !
    (Beatles)

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    My original response was a duplicate of sathyaram_s's so I removed it...

    See if this is something you can use:


    more test_adm
    Code:
    create procedure test_adm(
    out p_rows_exp BIGINT,
    out p_msg_retreive VARCHAR(512),
    out p_msg_remove VARCHAR(512))
    specific test_adm
    language SQL
    
    BEGIN
    DECLARE cmdline VARCHAR(1024);
    DECLARE v_msg_retreival VARCHAR(512);
    DECLARE v_rows_exp bigint;
    DECLARE v_msg_removal VARCHAR(512);
    DECLARE v_admin_cmd_str VARCHAR(512);
    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    
    CALL sysproc.admin_cmd('export to /home/bkogan/testadm of del messages on server select * from test');
    
    ASSOCIATE RESULT SET LOCATORS(result1) WITH PROCEDURE sysproc.ADMIN_CMD;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;
    FETCH rsCur INTO v_rows_exp, v_msg_retreival, v_msg_removal;
    SET p_rows_exp = v_rows_exp;
    SET p_msg_retreive = v_msg_retreival;
    SET p_msg_remove = v_msg_removal;
    
    END
    @


    db2 -td@ -f test_adm
    DB20000I The SQL command completed successfully.


    db2 "call bkogan.test_adm(?,?,?)"

    Value of output parameters
    --------------------------
    Parameter Name : P_ROWS_EXP
    Parameter Value : 2

    Parameter Name : P_MSG_RETREIVE
    Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('2480_BKOGAN')) AS MSG

    Parameter Name : P_MSG_REMOVE
    Parameter Value : CALL SYSPROC.ADMIN_REMOVE_MSGS('2480_BKOGAN')

    Return Status = 0
    Last edited by sathyaram_s; 03-26-10 at 09:10. Reason: added code tag

  9. #9
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks Bella,

    This was something I was looking for long time.
    I am sure this will fulfil my immediate need.
    I hope you won't mind posting any shortcut for more details on this for me.

    Tons of regard for everyone

    DBFinder
    Last edited by DBFinder; 02-16-10 at 10:09.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Sorry, I don't have any additional information

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    NP


    DBFinder

  12. #12
    Join Date
    Jun 2011
    Location
    Boston, Massachusetts
    Posts
    2

    retrieving results from admin_cmd

    Thank you! I couldn't find a sample like this anywhere... I'm rather new to DB2 LUW and their stored procedures. This will help me immensely.

    Lynne



    Quote Originally Posted by db2girl View Post
    My original response was a duplicate of sathyaram_s's so I removed it...

    See if this is something you can use:


    more test_adm
    Code:
    create procedure test_adm(
    out p_rows_exp BIGINT,
    out p_msg_retreive VARCHAR(512),
    out p_msg_remove VARCHAR(512))
    specific test_adm
    language SQL
    
    BEGIN
    DECLARE cmdline VARCHAR(1024);
    DECLARE v_msg_retreival VARCHAR(512);
    DECLARE v_rows_exp bigint;
    DECLARE v_msg_removal VARCHAR(512);
    DECLARE v_admin_cmd_str VARCHAR(512);
    DECLARE result1 RESULT_SET_LOCATOR VARYING;
    
    CALL sysproc.admin_cmd('export to /home/bkogan/testadm of del messages on server select * from test');
    
    ASSOCIATE RESULT SET LOCATORS(result1) WITH PROCEDURE sysproc.ADMIN_CMD;
    ALLOCATE rsCur CURSOR FOR RESULT SET result1;
    FETCH rsCur INTO v_rows_exp, v_msg_retreival, v_msg_removal;
    SET p_rows_exp = v_rows_exp;
    SET p_msg_retreive = v_msg_retreival;
    SET p_msg_remove = v_msg_removal;
    
    END
    @


    db2 -td@ -f test_adm
    DB20000I The SQL command completed successfully.


    db2 "call bkogan.test_adm(?,?,?)"

    Value of output parameters
    --------------------------
    Parameter Name : P_ROWS_EXP
    Parameter Value : 2

    Parameter Name : P_MSG_RETREIVE
    Parameter Value : SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('2480_BKOGAN')) AS MSG

    Parameter Name : P_MSG_REMOVE
    Parameter Value : CALL SYSPROC.ADMIN_REMOVE_MSGS('2480_BKOGAN')

    Return Status = 0

Posting Permissions

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