Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Unanswered: Run runstat on all tables using admin_cmd?

    I am using DB2 9.7 and Data Studio/ Web Console 4.1 on windows... I am running a runstats utility on all tables using admin_cmd procedure.. But I am not able to run it as it has mixture of both SQL statement and CLP statements...

    CALL SYSPROC.ADMIN_CMD(db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname ),1,50),' and indexes all;'from \
    syscat.tables where type = 'T' " > runstats.out);

    I am running as sql script not DB2CLP script through web console..

    I know CALL SYSPROC.ADMIN_CMD ('RUNSTATS ON TABLE db2user.employee ON KEY COLUMNS and INDEXES ALL') work fines as it is on single table .And I want to have script for all tables on systables which has type T.

    Please help..I need it urgently

    Thanks In Advance

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    ADMIN_CMD - IBM DB2 9.7 for Linux, UNIX, and Windows
    this page indicates what can be done with admin_cmd
    create a simple script that will execute the sql and run the generated sql
    put the script in crontab to automate it..
    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
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Try to use a compound statement with '/' as a statement termination character.
    Like this:

    Code:
    set serveroutput on/
    
    begin
      for c1 as 
        select 'runstats on table "'||tabschema||'"."'||tabname||'" with distribution and indexes all' as str
        from syscat.tables
        where type='T'
        -- you can put here some additional filter like:
        -- and tabschema not like 'SYS%'
        with ur
      do
        call admin_cmd(c1.str);
        call dbms_output.put_line('done: ' || c1.str);
      end for;
    end/
    Regards,
    Mark.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by mark.b View Post
    Hi,
    call dbms_output.put_line('done: ' || c1.str);
    Is there anyway one can flush the message buffer? I notice there is a fflush function in utl_file, but I can't seem to find something similar in dbms_output module.


    /Lennart
    --
    Lennart

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all available command are online at:DBMS_OUTPUT module
    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

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by przytula_guy View Post
    all available command are online at:DBMS_OUTPUT module
    I'm aware of the documentation, but it does not reveal much in this regard. I'm looking for some undocumented side effect that "happens" to flush the buffer.

    /Lennart
    --
    Lennart

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    On put_line() success - does'nt it flush automatically?

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by db2mor View Post
    On put_line() success - does'nt it flush automatically?
    Not as far as I can tell. Running a slightly modified version of Marks code:

    Code:
    ]$ cat aa.sql 
    
    set serveroutput on @
    begin
      declare line varchar(32672);
      declare status int;
    
      call dbms_output.enable(100000) ;
      for c1 as 
        select rtrim(tabschema)||'.'||rtrim(tabname) as str
        from syscat.tables
        where type='T'
        with ur
      do
        call admin_cmd('runstats on table ' || c1.str || ' with distribution and sampled detailed indexes all');
        call dbms_output.put_line('done: ' || c1.str);
      end for;
    end @
    set serveroutput off @
    
    ]$ db2 -td@ -f aa.sql
    Nothing is printed until the end, when everything is printed at once. If the buffer could be flushed somehow we would be able to use the output as a progress indicator, and it would be easier to decide on a sufficient size for the buffer
    --
    Lennart

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There's no "flushing" in DBMS_OUTPUT. PUT_LINE() stores strings in an array, and the CLP just reads back those lines after the executed block finishes. Strictly speaking, it's not "serveroutput", it is "clpoutput". Since the CLP blocks until the statement or block finishes, as any application would, there is no way for it to do anything until it gets back control.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Quote Originally Posted by lelle12 View Post
    Is there anyway one can flush the message buffer? I notice there is a fflush function in utl_file, but I can't seem to find something similar in dbms_output module.
    Lennart,
    you can try to use this probably to "flush" the buffer:
    Code:
    call dbms_output.disable;
    call dbms_output.enable;
    but note that in CLP you loose the whole contents of this buffer after this "flushing".
    Probably the developers of CLP could print to the standard output the contents of this buffer before dbms_output.disable call but it seems that this was not done...
    Regards,
    Mark.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you guys misunderstand how the CLP works. When you execute an anonymous block in the CLP, it is basically equivalent to
    Code:
    EXECUTE IMMEDIATE 'begin declare line ... end for; end'
    So, until EXECUTE IMMEDIATE returns, the CLP blocks and there's no way for it to access the DBMS_OUTPUT buffer.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by n_i View Post
    There's no "flushing" in DBMS_OUTPUT. PUT_LINE() stores strings in an array, and the CLP just reads back those lines after the executed block finishes. Strictly speaking, it's not "serveroutput", it is "clpoutput". Since the CLP blocks until the statement or block finishes, as any application would, there is no way for it to do anything until it gets back control.
    Thanks, that makes sense
    --
    Lennart

  13. #13
    Join Date
    Aug 2013
    Posts
    3
    HI All,

    Thanks for your valuable suggestions. I can run runstats on tables through script successfully.

    But I have to run it through data studio. So What I did is created a procedure. please have a look:

    create procedure runstat ()
    begin
    declare line varchar(32672);
    declare status int;
    call dbms_output.enable(100000) ;
    for c1 as
    select rtrim(tabschema)||'.'||rtrim(tabname) as str
    from syscat.tables
    where type='T'
    do
    call admin_cmd('runstats on table ' || c1.str || ' with distribution and sampled detailed indexes all');
    call dbms_output.put_line('done: ' || c1.str);
    end for;
    end

    Runstats is executing till some point.I managed to execute runstats on most of the tables but after reaching some point i am log size full error.

    db2diag.log :

    MESSAGE : ADM1823E The active log is full and is held by application handle
    "5889..". Terminate this application by COMMIT, ROLLBACK or FORCE
    APPLICATION.

    db2optstats.log:

    MESSAGE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
    "Log File has reached its saturation point"
    DIA8309C Log file was full.

    I have tried everything what I can, like force application . increasing log file size. Currently I have Primary logs 12, secondary log 20, logsize:4000. Currently i can 44gb space on file system.


    Please help
    Last edited by rikkimaha24; 08-22-13 at 00:34.

  14. #14
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You should increase the number of secondary log files (logsecond)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

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

    If your procedure really causes this log full then try to commit after some N runstats:

    Code:
    begin
      declare i int default 0;
    
      for c1 as cur1 CURSOR WITH HOLD for
        select 'runstats on table "'||tabschema||'"."'||tabname||'" with distribution and indexes all' as str
        from syscat.tables
        where type='T'
        with ur
      do
        call admin_cmd(c1.str);
        set i = i + 1;
        if (i = 5) then
          commit;
          set i = 0;
        end if;
      end for;
    end/
    Regards,
    Mark.

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
  •