Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    10

    Unanswered: execute a dynamic RUNSTATS in a stored procedure

    Hello!
    I need to execute a dynamic RUNSTATS in a stored procedure, but it´s not working...
    the error message is:
    SQL0104N An unexpected token "TABLE" was found following "RUNSTATS ON ".
    Expected tokens may include: "JOIN". SQLSTATE=42601

    My stored procedure is:
    PREPARE V_SQL_C1 FROM V_COMANDO;
    SET V_COMANDO2 = "RUNSTATS ON TABLE EPI03.DBM_ATIVIDADE_ETL";
    SET V_COMANDO3 = " WITH DISTRIBUTION AND INDEXES ALL";
    SET V_COMANDO = V_COMANDO2 || V_COMANDO3;
    EXECUTE V_COMANDO;

    What´s the problem??
    Thanks,
    Tessy

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    ADMIN_CMD Stored Procedure

    Cheers

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

  3. #3
    Join Date
    Nov 2005
    Posts
    10

    how this ADMIN_CMD Stored Procedure works?

    But, how this ADMIN_CMD Stored Procedure works?
    where can I see the stored procedure code???
    Can you give me an example please?
    Thanks,
    Tessy

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Here's the documentation link

    http://publib.boulder.ibm.com/infoce...n/r0012547.htm

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

  5. #5
    Join Date
    Nov 2005
    Posts
    10
    Hi Sathyaram!
    Now there is another error message:
    SQL0440N No authorized routine named "SYSPROC.ADMIN_CMD" of type "PROCEDURE"
    having compatible arguments was found. SQLSTATE=42884
    SQL0440N No authorized routine named "SYSPROC.ADMIN_CMD" of type "PROCEDURE

    My stored procedure is:
    SET V_COMANDO = 'CALL SYSPROC.ADMIN_CMD(';
    SET V_COMANDO = V_COMANDO || '''RUNSTATS ON TABLE EPI03.DBM_ATIVIDADE_ETL';
    SET V_COMANDO = V_COMANDO || ' WITH DISTRIBUTION AND INDEXES ALL''' || ')';
    EXECUTE IMMEDIATE V_COMANDO;

    How can I fix it?
    Thanks,
    Tessy

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You haven't mentioned about your DB2 version and fixpak!!

    This SP wasn't available until FP9 of Version 8.

    If you are on earlier fixpaks/versions, an option is to use a C Stored Proc which calls the db2Runstats API ... A sample program (not a SP) is available in the sqllib/samples/c directory.

    HTH

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

  7. #7
    Join Date
    Nov 2005
    Posts
    10
    Hi Sathyaram! Sorry...
    DB2 version and fixpack:
    DB2 v8.1.1.80
    FixPak "8"
    DB2ESE

    Where is this directory "sqllib/samples/c" ?
    Is it at "/" directory?
    It cant´t be a C program...
    Do you have an example of a C Stored Procedure which calls the db2Runstats API?
    Is the C Stored Procedure compile form the same as SQL Stored Procedure?
    Thanks,
    Tessy

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    It is under the instance owner home directory ..

    Read the readme file in the directory and you'll have all information you need

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

Posting Permissions

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