Topic
  • 7 replies
  • Latest Post - ‏2013-08-22T00:16:40Z by 10J1_Tanzeem_Mozumder
10J1_Tanzeem_Mozumder
3 Posts

Pinned topic Web Console--DB2 CLP Scripts + File System Utilisation Alert

‏2013-08-12T06:23:04Z |

Hi Everyone,

I have couple of questions :

 

1) DB2 CLP Scripts :I am using Data Studio/ Web Console 4.1 . I have created a job on Web Console, But when ever I am running it, I am getting failed to connect to ssh server. I m not using unix servers.

Just for Information. My DB server is on Windows and it is installed on different machine and web console is on different machine.

 

 

Commands Executed:
db2cmd /i /c /w connect to testserver user test using test1;
Start Time: 2013-08-12 15:11:29
End Time: 2013-08-12 15:11:30
Error message:
Failed to connect to SSH server testserver with username test.

Result:

The job execution failed. Execution Status code: 3

 

2) File system Utilisation Alert: I can see there is file system utilisation alert on alert Configuration. But I really dont know how to use it. How to map it wiht my server's drive?

 

Please help...

  • LukeNumrych
    LukeNumrych
    57 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-13T16:10:07Z  

    Hi Tanzeem,

    To answer your questions:

    1. You did not specify what version of DB2 you are running, and the answer is somewhat dependent on that.  In theory, Data Studio (and Web Console) running against a remote DB2 running on Windows can run CLP scripts either by connecting to an SSH server or DAS server (and DS/WC will prefer SSH).  In practice, since DAS is deprecated as of 9.7, using DAS is inadvisable.  That means you HAVE TO have an SSH server running on your DB2 server for CLP scripts to run.  
      For DB2 < 10.1 fp2, that means cygwin + SSH.  It apparently has to be cygwin - I tried with a commercial Windows SSH server package, and it would not work.  The Info Center spells it out pretty clearly too - either cygwin or nothing.
      DB2 > 10.1 installs a SSH service called "IBM Secure Shell Server for Windows".  I have heard that the version that comes with DB2 10.1 GA and fp1 does NOT work with DSWC.  I have a test system with DB2 10.1 fp2 and can confirm that DSWC 3.2 (and I would assume 4.1 as well) can run CLP scripts using the IBM SSH service included in DB2 10.1 fp2.
      You may be able to convert your CLP scripts to SQL scripts and thus avoid the whole problem - have you checked out the ADMIN_CMD procedure?  You can call it from a SQL script and it will let you execute a subset of DB2 administrative commands that would otherwise require a CLP script.
    2. Unfortunately, I cannot help you with this very much - I am still waiting to install DSWC 4.1.  However, since DSWC is not really meant to monitor your server's resources in general, but only in as much as they relate to the resources used by the database being monitored, I think that the file system utilization monitor may be limited to monitoring only the volumes that are relevant to the database: the tablespace containers and the log paths for example.  In which case, you really would not have to configure the drives to be monitored - the paths to tablespace containers and log file locations are part of configuration of every database.
      This, however, leaves backup drives, which are not defined in the configuration of the database, but are assigned at backup time...  I have not yet found an answer to the question how to monitor the backup file system.  Well, not with the built-in monitors - you certainly utilize the User-Defined Alert Types to create something yourself.

     

  • HLHuang
    HLHuang
    32 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-13T23:55:19Z  

    Hi, 

       To answer the second question.  If your database is installed on C drive (eg 500GB),  and you set the configuration on this alert to, say 90% for warning alert,  95% for critical alert (please see attached fs_conf.png file).   And if you only have 50GB space available on C drive.  Then we will issue the warning alert to inform that the file system is low on available space.  The details of this alert is on the attached  fs_alert.png.  You don't need to map the drive, db2 server knows where it is installed, that's where it shows Database Storage Path.  On this fs_alert.png screen shot, we have warning alert set to 10%, that's why we got the warning alert when our file system utilization is 22%.   We get all these information from db2 server, it tells us what's the percentage of the file system utilization and we issue alert depending on how the threshold setting got configured.    Hope this answer your question.  :)

    .  

      

    Attachments

  • LukeNumrych
    LukeNumrych
    57 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-14T05:35:53Z  
    • HLHuang
    • ‏2013-08-13T23:55:19Z

    Hi, 

       To answer the second question.  If your database is installed on C drive (eg 500GB),  and you set the configuration on this alert to, say 90% for warning alert,  95% for critical alert (please see attached fs_conf.png file).   And if you only have 50GB space available on C drive.  Then we will issue the warning alert to inform that the file system is low on available space.  The details of this alert is on the attached  fs_alert.png.  You don't need to map the drive, db2 server knows where it is installed, that's where it shows Database Storage Path.  On this fs_alert.png screen shot, we have warning alert set to 10%, that's why we got the warning alert when our file system utilization is 22%.   We get all these information from db2 server, it tells us what's the percentage of the file system utilization and we issue alert depending on how the threshold setting got configured.    Hope this answer your question.  :)

    .  

      

    HLHuang - out of curiosity, does that mean that log file locations are not monitored if they are not on the same drive as the database storage paths?

  • HLHuang
    HLHuang
    32 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-14T17:16:35Z  

    HLHuang - out of curiosity, does that mean that log file locations are not monitored if they are not on the same drive as the database storage paths?

    Hi,

        That would be my best guess too.  :)   But I guess it's best to ask DB2 forum to get firm answer. :)

     

       

      

  • 10J1_Tanzeem_Mozumder
    3 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-16T04:39:08Z  

    Bingo.. Wow That wokrs great.. thanks Luke and Huang. I appreciate your help.I am able to have file system alert and  executed DB2CLP scripts using admin_cmd procedure.

    FYI: I am using DB2 9.7

    To add further I m not able to run runstats and backup uitlity which has mixture of both sql and clp statments.

    Runstat:  CALL SYSPROC.ADMIN_CMD (db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'" );

    But I am getting error. It seems like I am getting confused here with SQL statements and CLP one's.

    Backup: 

    connect to testserver user  test using test;
    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    connect reset;
    DEACTIVATE DB testserver;
    BACKUP DATABASE testserver TO d:\DB2Backup COMPRESS WITHOUT PROMPTING;
    UNQUIESCE DATABASE;

    Statements in bold are able to run with SYSPROC.ADMIN_CMD but not all?Does that means admin_cmd can only take online backup?is the syntax correct?

    Please help guys. I need it.

     

    Thanks in advance..

  • LukeNumrych
    LukeNumrych
    57 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-16T14:17:55Z  

    Bingo.. Wow That wokrs great.. thanks Luke and Huang. I appreciate your help.I am able to have file system alert and  executed DB2CLP scripts using admin_cmd procedure.

    FYI: I am using DB2 9.7

    To add further I m not able to run runstats and backup uitlity which has mixture of both sql and clp statments.

    Runstat:  CALL SYSPROC.ADMIN_CMD (db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'" );

    But I am getting error. It seems like I am getting confused here with SQL statements and CLP one's.

    Backup: 

    connect to testserver user  test using test;
    QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
    connect reset;
    DEACTIVATE DB testserver;
    BACKUP DATABASE testserver TO d:\DB2Backup COMPRESS WITHOUT PROMPTING;
    UNQUIESCE DATABASE;

    Statements in bold are able to run with SYSPROC.ADMIN_CMD but not all?Does that means admin_cmd can only take online backup?is the syntax correct?

    Please help guys. I need it.

     

    Thanks in advance..

    You do not need the "db2 -x  -r" part in your command string, just the runstats command itself.  Here is the example from the documentation:

    
    CALL SYSPROC.ADMIN_CMD ('RUNSTATS ON TABLE db2user.employee  ON KEY COLUMNS and INDEXES ALL')
    

    Your script would have to be translated to something like this:

    -- no connect statement needed since you are executing a script against an established database 
    -- CONNNECTION - if there is no connection, you cannot execute a script
     
    CALL SYSPROC.ADMIN_CMD( 'quiesce db immediate' );
    CALL SYSPROC.ADMIN_CMD( 'backup db sample online to d:\db2backup compress include logs without prompting' );
    CALL SYSPROC.ADMIN_CMD( 'unquiesce db' );
     

    You are correct, ADMIN_CMD can only take online backups. From BACKUP DATABASE command using the ADMIN_CMD procedure:

    ONLINE
    Specifies online backup. This is the only supported mode and is the default. The ONLINE clause does not need to be specified.

     

    It is kind of logical - you are executing a SQL script, not a CLP script, and to execute a SQL script, you need a database connection, which means you cannot take an OFFLINE backup because your connection would have to be terminated, which would cancel the execution of the command...

    Since online backup is the only backup mode you can pick, you could discard the 'quiesce' and 'unquiesce' statements if you don't absolutely need them.

     

    Check out the ADMIN_CMD procedure - Run administrative commands documentation - the commands it lists are the only supported commands.  It does have limits...

    Updated on 2013-08-16T15:07:52Z at 2013-08-16T15:07:52Z by LukeNumrych
  • 10J1_Tanzeem_Mozumder
    3 Posts

    Re: Web Console--DB2 CLP Scripts + File System Utilisation Alert

    ‏2013-08-22T00:16:40Z  

    You do not need the "db2 -x  -r" part in your command string, just the runstats command itself.  Here is the example from the documentation:

    <pre class="pre codeblock" style="font-family: 'Courier New', Courier, monospace; font-size: 13.333333969116211px; color: rgb(0, 0, 0);"> CALL SYSPROC.ADMIN_CMD ('RUNSTATS ON TABLE db2user.employee ON KEY COLUMNS and INDEXES ALL') </pre>

    Your script would have to be translated to something like this:

    -- no connect statement needed since you are executing a script against an established database 
    -- CONNNECTION - if there is no connection, you cannot execute a script
     
    CALL SYSPROC.ADMIN_CMD( 'quiesce db immediate' );
    CALL SYSPROC.ADMIN_CMD( 'backup db sample online to d:\db2backup compress include logs without prompting' );
    CALL SYSPROC.ADMIN_CMD( 'unquiesce db' );
     

    You are correct, ADMIN_CMD can only take online backups. From BACKUP DATABASE command using the ADMIN_CMD procedure:

    ONLINE
    Specifies online backup. This is the only supported mode and is the default. The ONLINE clause does not need to be specified.

     

    It is kind of logical - you are executing a SQL script, not a CLP script, and to execute a SQL script, you need a database connection, which means you cannot take an OFFLINE backup because your connection would have to be terminated, which would cancel the execution of the command...

    Since online backup is the only backup mode you can pick, you could discard the 'quiesce' and 'unquiesce' statements if you don't absolutely need them.

     

    Check out the ADMIN_CMD procedure - Run administrative commands documentation - the commands it lists are the only supported commands.  It does have limits...

    Hi Luke,

    Thanks for you suggestions. I have managed to execute runstats on indiviual tables.

    I have a requirement to fire runstats on all tables in syscat.tables where Type='T' . In order to accomplish this I have created a procedure, in which I can loop through all tables and fire runstats on all tables one by one. I am running this procedure through data studio. please have a look:

    create procedure runstat ()
    begin
     declare stmt varchar(200);
     for line as select tabschema, tabname from syscat.tables where type='T'
     do
      set nr_tables = nr_tables + 1;
      set stmt= 'RUNSTATS ON TABLE ' concat rtrim(line.tabschema) ||'.' concat rtrim(line.tabname)||' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS ';
       CALL SYSPROC.ADMIN_CMD(stmt);
      end for;
    end

    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:

    FUNCTION: DB2 UDB, relation data serv, sqlrLocalRunstats, probe:9956
    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 5889. increasing log file size. Currently I have Primary logs 12, secondary log 20, logsize:4000. Currently i can 44gb space on file system.

    option 2) But when I m firing runstats on each table using command prompt, it is executed successfully without any error.

    db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'"
    db2 -vf "runstats.db2"

    I dont know what is happening. Its just when I am running procedure i am getting the error, but running runstats through command prompt works fine. Please help me.

    Thanks In Advance

    Updated on 2013-08-22T03:33:57Z at 2013-08-22T03:33:57Z by 10J1_Tanzeem_Mozumder