View Poll Results: How often do you use the ADMIN_CMD command?

Voters
0. You may not vote on this poll
  • Daily

    0 0%
  • Seldom

    0 0%
  • Never

    0 0%
Results 1 to 5 of 5

Thread: Admin_cmd

  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Admin_cmd

    Good Day

    I use DB2 9.7 Express C.

    I need to run exports and imports every night and manually need to go and check every morning if all ran fine. This is time consuming and I am attempting to automate the process with a scheduled Stored Procedure.

    I am using the SYSPROC.ADMIN_CMD (fenced) SP to do the Import command, followed by ASSOCIATE RESULT SET LOCATORS(result1) WITH PROCEDURE SYSPROC.ADMIN_CMD;

    After that I allocate a Cursor for Result set result1 and Fetch the values into the apropraite variables to receive the values from the ADMIN_CMD command INTO vROWS_READ, vROWS_SKIPPED, vROWS_INSERTED, vROWS_UPDATED, vROWS_REJECTED, vROWS_COMMITTED, vMSG_RETRIEVAL, vMSG_REMOVAL;

    I use the following data types:
    vROWS_READ BIGINT;
    vROWS_SKIPPED BIGINT;
    vROWS_INSERTED BIGINT;
    vROWS_UPDATED BIGINT;
    vROWS_REJECTED BIGINT;
    vROWS_COMMITTED BIGINT;
    vMSG_RETRIEVAL VARCHAR(512);
    vMSG_REMOVAL VARCHAR(512);

    The Import works fine, but for the life of me, I can not get the SP to do anything after the ADMIN_CMD, never mind using the results to insert into a table.

    All I want to do is to write the results to a table for 50 different Exports and Imports. When the process is done I want to send a report of the successes or failures.

    Please help !!
    Last edited by DanDB; 11-15-12 at 08:36. Reason: Forgot some detail.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    My apologies. I understand what you are saying.

    Microsoft Windows Server Version 6.1 (Build 7600)

    DB2 v9.7.200.358 s100514 IP23084 and Fix Pack 2

    Product name: "DB2 Express-C"
    License type: "Unwarranted"
    Expiry date: "Permanent"
    Product identifier: "db2expc"
    Version information: "9.7"
    Max number of CPUs: "2"
    Max amount of memory (GB): "2"

    The ADMIN_CMD command is the closest I could find to a solution. All I want is a single line per import with so many rows read, so many rows inserted, so many rejected and insert that to a table. I use it in a Stored Procedure,
    CREATE PROCEDURE ImportData ()
    SPECIFIC Import_data_from_live_server
    LANGUAGE SQL
    NOT DETERMINISTIC
    EXTERNAL ACTION
    MODIFIES SQL DATA
    BEGIN

    , do the import (it imports fine), but the SP ignores any SQL after the import. It just does the import and do not return any errors. Is that the way the ADMIN_CMD works? Can you not use the result set returned by the ADMIN_CMD. I also tried to put the ADMIN_CMD in its own SP and returning the result set, calling that from inside my current SP. Am I barking up the wrong tree? Should I look for another command that can return a success or failure result set?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You posted everything except what is really needed to help you.

    Good luck.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2009
    Posts
    4
    I am doing multiple Imports. I want to report on the results of the imports and display what was successful and what failed. ADMIN_CMD returns the correct result set and information that is valuable, information that I can use to display success or failure per import. I use the command as shown in the examples I found on the net and the explanation found on the IBM site, but it seems like I am missing something.

    The command successfully does the CMD import command, but I can not use the result set displayed by the command after the import. I can not get the result set information into variables or use the values in an INSERT Statement. I want to Insert the ROWS_READ, ROWS_SKIPPED, ROWS_INSERTED, ROWS_UPDATED, ROWS_REJECTED, ROWS_COMMITTED, MSG_RETRIEVAL, MSG_REMOVAL into a table that I created. It looks like the SP commits after the import, breaks out of the SP and returns success without doing the INSERT of the values.

    Can you explain to me how to use the values returned by the ADMIN_CMD command. I want to insert the values into a table. I fail to use the result set in an INSERT Statement. It looks like the SP is ignoring all the SQL after the ADMIN_CMD.

    Thank you in advance
    Last edited by DanDB; 11-19-12 at 08:13.

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
  •