Topic
  • 12 replies
  • Latest Post - ‏2015-10-07T16:13:02Z by rosie
DMocko
DMocko
7 Posts

Pinned topic How to Run db2diag command in Job Manager

‏2013-12-02T21:26:02Z |

I am able to execute thedb2diag -archive "k:\diaglog\RPW0ADSY" -readfile  command from the IBM DB2 Command Window and am also able to execute this command in UDB Task Center as an OS command (with and without the -readfile parameter).  When I execute this command with and without the -readfile parameter in the Data Studio 4.1 Job Manager as a DB2 CLP script, SQL0104N is thrown (An unexpected token "-archive" was found following "db2diag ".) .  Can this command be executed in Job Manager?  if yes, please provide procedures for job setup and execution.  My db2level is 9.7.4 running on a Windows  2008 OS.

  • YuanFeng
    YuanFeng
    81 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2013-12-04T06:05:46Z  

    could you try

     !db2diag -archive  "k:\diaglog\RPW0ADSY" -readfile, add the "!" before the command?

    it works in data studio client sql edit.

    If you are not going to schedule the task,just run once,you can try data studio client.

     

    Updated on 2013-12-04T06:36:40Z at 2013-12-04T06:36:40Z by YuanFeng
  • Sriram_Europa
    Sriram_Europa
    68 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2013-12-04T06:46:05Z  
    • YuanFeng
    • ‏2013-12-04T06:05:46Z

    could you try

     !db2diag -archive  "k:\diaglog\RPW0ADSY" -readfile, add the "!" before the command?

    it works in data studio client sql edit.

    If you are not going to schedule the task,just run once,you can try data studio client.

     

    what happens when you start a db2cmd window and then invoke db2 & then enter db2diag there ? Thats pretty much close to what Job manager does with DB2 CLP script type of jobs

    db2diag isn't  quite exactly a db2 CLP command - so  when you create a DB2 CLP job - you may be running into the syntax errors from the db2 command line utility

    You can try an executable/shell script to run any dos .bat commands  - so you can try to run db2diag in that fashion

    Or as YuanFeng put it - you can use  !db2diag ..  (leading  exclamation)   to have the db2 clp  itself run db2diag for you in the O.S

  • DMocko
    DMocko
    7 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2013-12-04T16:56:40Z  

    what happens when you start a db2cmd window and then invoke db2 & then enter db2diag there ? Thats pretty much close to what Job manager does with DB2 CLP script type of jobs

    db2diag isn't  quite exactly a db2 CLP command - so  when you create a DB2 CLP job - you may be running into the syntax errors from the db2 command line utility

    You can try an executable/shell script to run any dos .bat commands  - so you can try to run db2diag in that fashion

    Or as YuanFeng put it - you can use  !db2diag ..  (leading  exclamation)   to have the db2 clp  itself run db2diag for you in the O.S

    Thank you YuanFeng & Sriram_Europa for your responses. 

    1. Attachment db2diag.results.gif shows the results of the DS Job Manager execution when I add the exclamation point preceding the command.  You will see that the "terminate" parameter in the result set generates an error & that the job thinks it executed successfully, however I confirmed that it did not perform the diaglog archive. 
    2. The task as presented in this thread runs successfully when I start a db2cmd window and execute the command. 
    3. When I run the job as an executable/shell in DS Job Manager, the result is "The job executed successfully. Execution Status code: 2" however I confirmed that it did not perform the diaglog archive.
    4. When I execute this command in Windows as a .bat script, it is successful....actually does do the archive.   

     In our shop, our goal is to automate as much database maintenance as we can.  We currently have the db2diag log archival tasks set up and running successfully in the UDB Task Center.  My preference is to convert all UDB Task Center tasks to DS Job Manager jobs.  So far in my testing, I have found that this command and the activate database command don't behave the same in DS Job Manager as they do in the UDB Task Center.  If I cannot find a way to get the db2diag -archive command to run in the DS Job Manager, we will be faced with converting this task to a Windows scheduled task.  Do you have any other suggestions/ideas for getting the db2diag -archive job to run successfully in the DS Job Manager? 

    Thanks for your time on this!

    Attachments

  • Sriram_Europa
    Sriram_Europa
    68 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2013-12-06T06:51:02Z  
    • DMocko
    • ‏2013-12-04T16:56:40Z

    Thank you YuanFeng & Sriram_Europa for your responses. 

    1. Attachment db2diag.results.gif shows the results of the DS Job Manager execution when I add the exclamation point preceding the command.  You will see that the "terminate" parameter in the result set generates an error & that the job thinks it executed successfully, however I confirmed that it did not perform the diaglog archive. 
    2. The task as presented in this thread runs successfully when I start a db2cmd window and execute the command. 
    3. When I run the job as an executable/shell in DS Job Manager, the result is "The job executed successfully. Execution Status code: 2" however I confirmed that it did not perform the diaglog archive.
    4. When I execute this command in Windows as a .bat script, it is successful....actually does do the archive.   

     In our shop, our goal is to automate as much database maintenance as we can.  We currently have the db2diag log archival tasks set up and running successfully in the UDB Task Center.  My preference is to convert all UDB Task Center tasks to DS Job Manager jobs.  So far in my testing, I have found that this command and the activate database command don't behave the same in DS Job Manager as they do in the UDB Task Center.  If I cannot find a way to get the db2diag -archive command to run in the DS Job Manager, we will be faced with converting this task to a Windows scheduled task.  Do you have any other suggestions/ideas for getting the db2diag -archive job to run successfully in the DS Job Manager? 

    Thanks for your time on this!

    I just tried this.. 

    Created a new DB2 CLP Script job

    !db2diag -archive "C:\Temp\tstdiag" -readfile

    Clicked on Run Job and picked a windows database.  (version info on that system: "Command Line Processor for DB2 Client 10.1.2")

    and I see the output from this job's log (in history tab)

    :

    !db2diag -archive "C:\Temp\tstdiag" -readfile
    
    db2diag: Moving "C:\PROGRAMDATA\IBM\DB2\DB2COPY1\DB2\DIAG0000\db2diag.log"
    
             to     "C:\Temp\tstdiag\db2diag.log_2013-12-05-22.32.30"
    
    :
    -and I did see the new file created. 
    
    

    Its possible that there is some other problem in your system that is not quite obvious here.  Can you try to gather more logs from DSWC ?

    1). Turn on tracing first

    Open -> Product Setup -> Services

     select "System Logging Service" - and Configure ->  and set Log Level to "Trace"

    similarly for the JobManagerLogger

    2). Run your Job against your database

    3). Check if the job completed

    4).  Turn off logging by switching back the Log levels to "Information"

    and then pick up logs from: dswc install dir /logs/  - the files DS_System.0 and JobManagerLogger.0   -- or just the last few entries from the time you ran the job.  If you do not want to attach the logs in this forum  - you can work with IBM support to open a PMR and have all diagnostics collected.   If the logs are not too big - you can also email them to me at  sriram  at   us.ibm.com

    (by the way - I was not able to open the .gif files from your previous attachments - ms paint complains its "corrupted"..)

  • rosie
    rosie
    5 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-14T13:02:15Z  

    Hello  all  ,  I realize this  is  an  older thread  but  I am  now  encountering the same  issue  .  Db2 luw V10.1   on windows   server  .  DS v4.14   and WEBCONSOLE  4.1. Using  Job Manager - DB2CLP  script . I am  trying to  DB2DIAG  .  but   I  get  an error  in trying to  resolve the   directory,

    but  Job Manager  result    though  can not   seem to  find the right  directory

    ID: 1441977869502 Name: : Manage DB2DIAG log Database Name: xxxDB Commands Executed:

    !db2diag -archive "D:\DB2ADMIN\Diagarchive" -readfile
    

    Start Time: 2015-09-14, 08:29:41 End Time: 2015-09-14, 08:29:44 Errors from executing:

    The current directory is invalid.
    

     

    this  is  not   the case  if  I  go directly  to  Command Line Processor . Which positions itself to  D:\IBM\SQLLIB\BIN

    in the CFG   DB2 path

    [e] DB2PATH=D:\IBM\SQLLIB
    [i] DB2INSTPROF=D:\
    [i] DB2COMM=TCPIP
    [g] DB2_EXTSECURITY=YES
    [g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData
    [g] DB2SYSTEM=CDB-Q09
    [g] DB2PATH=D:\IBM\SQLLIB
    [g] DB2INSTDEF=DB2
    [g] DB2CLIINIPATH=D:\IBM\SQLLIB
    [g] DB2ADMINSERVER=DB2DAS00
     

    I am really   stuck  and  would  appreciate   your  help

     

    Is  there a  log  where  can find the  actual  error messages   returned?

     

    Thank   you

    Rosie

  • LukeNumrych
    LukeNumrych
    73 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-14T20:32:48Z  
    • rosie
    • ‏2015-09-14T13:02:15Z

    Hello  all  ,  I realize this  is  an  older thread  but  I am  now  encountering the same  issue  .  Db2 luw V10.1   on windows   server  .  DS v4.14   and WEBCONSOLE  4.1. Using  Job Manager - DB2CLP  script . I am  trying to  DB2DIAG  .  but   I  get  an error  in trying to  resolve the   directory,

    but  Job Manager  result    though  can not   seem to  find the right  directory

    ID: 1441977869502 Name: : Manage DB2DIAG log Database Name: xxxDB Commands Executed:

    <pre dir="ltr">!db2diag -archive "D:\DB2ADMIN\Diagarchive" -readfile </pre>

    Start Time: 2015-09-14, 08:29:41 End Time: 2015-09-14, 08:29:44 Errors from executing:

    <pre dir="ltr">The current directory is invalid. </pre>

     

    this  is  not   the case  if  I  go directly  to  Command Line Processor . Which positions itself to  D:\IBM\SQLLIB\BIN

    in the CFG   DB2 path

    [e] DB2PATH=D:\IBM\SQLLIB
    [i] DB2INSTPROF=D:\
    [i] DB2COMM=TCPIP
    [g] DB2_EXTSECURITY=YES
    [g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData
    [g] DB2SYSTEM=CDB-Q09
    [g] DB2PATH=D:\IBM\SQLLIB
    [g] DB2INSTDEF=DB2
    [g] DB2CLIINIPATH=D:\IBM\SQLLIB
    [g] DB2ADMINSERVER=DB2DAS00
     

    I am really   stuck  and  would  appreciate   your  help

     

    Is  there a  log  where  can find the  actual  error messages   returned?

     

    Thank   you

    Rosie

    Here is a script I use.  

    NOTE: this is an executable/shell script job, not a DB2 CLP one.  This means you will need an SSH session to the serverr.  DB2 10.1 (fp >= 1 i think) installation includes a dedicated SSH service, by the way.  However, if you do not have network connectivity to port 22 already, you may need to get it established, depending on your environment and network layout.

    echo Performing db2diag cleanup >> d:\logfiles\db2diag_cleanup.log
    echo %date% - %time% >> d:\logfiles\db2diag_cleanup.log
    echo Performed by: >> d:\logfiles\db2diag_cleanup.log
    whoami >> d:\logfiles\db2diag_cleanup.log 
    db2cmd -w "db2diag -A d:\logfiles & exit"
    echo Completed with %errorlevel% >> d:\logfiles\db2diag_cleanup.log

    You would need to change the logfile locations to fit of course, and if you don't need the fluff, you can just whittle it down to:

    db2cmd -w "db2diag -A d:\logfiles & exit"

    It works for me :)

    Except when the IBM SSH service has a fit and needs to be restarted, which happens with some frequency.

     

  • My-Ha
    My-Ha
    8 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-14T21:26:50Z  
    • rosie
    • ‏2015-09-14T13:02:15Z

    Hello  all  ,  I realize this  is  an  older thread  but  I am  now  encountering the same  issue  .  Db2 luw V10.1   on windows   server  .  DS v4.14   and WEBCONSOLE  4.1. Using  Job Manager - DB2CLP  script . I am  trying to  DB2DIAG  .  but   I  get  an error  in trying to  resolve the   directory,

    but  Job Manager  result    though  can not   seem to  find the right  directory

    ID: 1441977869502 Name: : Manage DB2DIAG log Database Name: xxxDB Commands Executed:

    <pre dir="ltr">!db2diag -archive "D:\DB2ADMIN\Diagarchive" -readfile </pre>

    Start Time: 2015-09-14, 08:29:41 End Time: 2015-09-14, 08:29:44 Errors from executing:

    <pre dir="ltr">The current directory is invalid. </pre>

     

    this  is  not   the case  if  I  go directly  to  Command Line Processor . Which positions itself to  D:\IBM\SQLLIB\BIN

    in the CFG   DB2 path

    [e] DB2PATH=D:\IBM\SQLLIB
    [i] DB2INSTPROF=D:\
    [i] DB2COMM=TCPIP
    [g] DB2_EXTSECURITY=YES
    [g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData
    [g] DB2SYSTEM=CDB-Q09
    [g] DB2PATH=D:\IBM\SQLLIB
    [g] DB2INSTDEF=DB2
    [g] DB2CLIINIPATH=D:\IBM\SQLLIB
    [g] DB2ADMINSERVER=DB2DAS00
     

    I am really   stuck  and  would  appreciate   your  help

     

    Is  there a  log  where  can find the  actual  error messages   returned?

     

    Thank   you

    Rosie

    To turn on log to find error messages:

    From Set Up, select Logging and Tracing.

    From the list of Logging components, select General System, then click Configure button at the right corner.

    Set Logging Level to Trace. Click OK to save.

    Do similarly to Job Manager Logging component, to set Trace

    You can find traces in logs under the directory <where DSM was installed>\logs.

  • My-Ha
    My-Ha
    8 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-14T21:59:56Z  
    • rosie
    • ‏2015-09-14T13:02:15Z

    Hello  all  ,  I realize this  is  an  older thread  but  I am  now  encountering the same  issue  .  Db2 luw V10.1   on windows   server  .  DS v4.14   and WEBCONSOLE  4.1. Using  Job Manager - DB2CLP  script . I am  trying to  DB2DIAG  .  but   I  get  an error  in trying to  resolve the   directory,

    but  Job Manager  result    though  can not   seem to  find the right  directory

    ID: 1441977869502 Name: : Manage DB2DIAG log Database Name: xxxDB Commands Executed:

    <pre dir="ltr">!db2diag -archive "D:\DB2ADMIN\Diagarchive" -readfile </pre>

    Start Time: 2015-09-14, 08:29:41 End Time: 2015-09-14, 08:29:44 Errors from executing:

    <pre dir="ltr">The current directory is invalid. </pre>

     

    this  is  not   the case  if  I  go directly  to  Command Line Processor . Which positions itself to  D:\IBM\SQLLIB\BIN

    in the CFG   DB2 path

    [e] DB2PATH=D:\IBM\SQLLIB
    [i] DB2INSTPROF=D:\
    [i] DB2COMM=TCPIP
    [g] DB2_EXTSECURITY=YES
    [g] DB2_COMMON_APP_DATA_PATH=C:\ProgramData
    [g] DB2SYSTEM=CDB-Q09
    [g] DB2PATH=D:\IBM\SQLLIB
    [g] DB2INSTDEF=DB2
    [g] DB2CLIINIPATH=D:\IBM\SQLLIB
    [g] DB2ADMINSERVER=DB2DAS00
     

    I am really   stuck  and  would  appreciate   your  help

     

    Is  there a  log  where  can find the  actual  error messages   returned?

     

    Thank   you

    Rosie

    I just tried a clp job with the command: !db2diag -A "C:\tstdiag" -readfile;

    Note that I have a ; at the end.

    I ran that job and got it succeeded. Make sure to create the directory tstdiag beforehand.

     

    UPDATE on 09/15.

    You also can use Executable/Shell script Type and entered the following line in the Script content:

    db2cmd -w "db2diag -A C:\tstdiag & exit"

    and ran the job against a selected database. I can see the diaglog being archived in the directory C:\tstdiag.

    C:\tstdiag>dir
     Volume in drive C has no label.
     Volume Serial Number is 2FEF-E21E

     Directory of C:\tstdiag

    09/15/2015  09:18 AM    <DIR>          .
    09/15/2015  09:18 AM    <DIR>          ..
    09/15/2015  09:10 AM     1,244,579,808 db2diag.log_2015-09-15-09.12.46
    09/15/2015  09:17 AM           168,061 db2diag.log_2015-09-15-09.18.47
                   2 File(s)  1,244,747,869 bytes
                   2 Dir(s)  22,476,849,152 bytes free

    Updated on 2015-09-15T19:58:25Z at 2015-09-15T19:58:25Z by My-Ha
  • rosie
    rosie
    5 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-18T19:54:03Z  
    • My-Ha
    • ‏2015-09-14T21:59:56Z

    I just tried a clp job with the command: !db2diag -A "C:\tstdiag" -readfile;

    Note that I have a ; at the end.

    I ran that job and got it succeeded. Make sure to create the directory tstdiag beforehand.

     

    UPDATE on 09/15.

    You also can use Executable/Shell script Type and entered the following line in the Script content:

    db2cmd -w "db2diag -A C:\tstdiag & exit"

    and ran the job against a selected database. I can see the diaglog being archived in the directory C:\tstdiag.

    C:\tstdiag>dir
     Volume in drive C has no label.
     Volume Serial Number is 2FEF-E21E

     Directory of C:\tstdiag

    09/15/2015  09:18 AM    <DIR>          .
    09/15/2015  09:18 AM    <DIR>          ..
    09/15/2015  09:10 AM     1,244,579,808 db2diag.log_2015-09-15-09.12.46
    09/15/2015  09:17 AM           168,061 db2diag.log_2015-09-15-09.18.47
                   2 File(s)  1,244,747,869 bytes
                   2 Dir(s)  22,476,849,152 bytes free

    Thank you all

    that is  exactly  want  I am trying to  run

    !db2diag -archive "D:\DB2ADMIN\Diagarchive" -readfile
    

     but  my result  is

     

    Start Time: 2015-09-14, 08:29:41 End Time: 2015-09-14, 08:29:44 Errors from executing:
    
    The current directory is invalid.
    

    I  am not  sure why  the  directory   is  invalid , is it   a setting   that  i am missing   ?

    thanks all

     

    Rosie

     

  • LukeNumrych
    LukeNumrych
    73 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-18T20:46:16Z  
    • rosie
    • ‏2015-09-18T19:54:03Z

    Thank you all

    that is  exactly  want  I am trying to  run

    <pre dir="ltr">!db2diag -archive "D:\DB2ADMIN\Diagarchive" -readfile </pre>

     but  my result  is

     

    <pre dir="ltr">Start Time: 2015-09-14, 08:29:41 End Time: 2015-09-14, 08:29:44 Errors from executing: </pre> <pre dir="ltr">The current directory is invalid. </pre>

    I  am not  sure why  the  directory   is  invalid , is it   a setting   that  i am missing   ?

    thanks all

     

    Rosie

     

    I guess this comes down to what is DB2 trying to do when it encounters the escape-to-os character "!" when running with the same credentials that you are using for your job.  

    For example, if I am signed on locally to the system and do "!cd" from DB2 CLP that is running under one set of credentials, I get "C:\Program Files\IBM\SQLLIB\BIN".  Under a different set of credentials, I get "C:\Windows\system32".  

    My SWAG is that the usercode you are using may not be granted rights to the directory in which DB2 CLP is trying to start the shell to execute the db2diag command.  Or it may be trying to use "C:\Program Files\IBM\SQLLIB\BIN" unquoted, which will result in it trying to be in C:\Program - which probably does not exist on your system.  In either case, you probably would get the error you are seeing.

    Can you execute the db2diag command from the local DB2 Command Line Processor (not Window!) using the credentials that you use in your scheduled job?  What are the results?  What do you get when you try "!cd" from the same CLP?

  • rosie
    rosie
    5 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-09-21T15:02:01Z  

    I guess this comes down to what is DB2 trying to do when it encounters the escape-to-os character "!" when running with the same credentials that you are using for your job.  

    For example, if I am signed on locally to the system and do "!cd" from DB2 CLP that is running under one set of credentials, I get "C:\Program Files\IBM\SQLLIB\BIN".  Under a different set of credentials, I get "C:\Windows\system32".  

    My SWAG is that the usercode you are using may not be granted rights to the directory in which DB2 CLP is trying to start the shell to execute the db2diag command.  Or it may be trying to use "C:\Program Files\IBM\SQLLIB\BIN" unquoted, which will result in it trying to be in C:\Program - which probably does not exist on your system.  In either case, you probably would get the error you are seeing.

    Can you execute the db2diag command from the local DB2 Command Line Processor (not Window!) using the credentials that you use in your scheduled job?  What are the results?  What do you get when you try "!cd" from the same CLP?

    Hi,

    All these are  run  on the  local host.

    It  runs  very  well from the DB2 Command Line Processor (CLP window)   and !CD  shows   D:\IBM\SQLLIB\BIN

      if I run the db2cmd.exe   from the command  prompt  window it  runs   fine  .

     

    if I  run the db2CMD.exe  from the webconsole     it is still   an error

    db2cmd -w "db2diag -archive "D:\DB2ADMIN\Diagarchive & exit"
    

    Start Time: 2015-09-21, 10:04:59 End Time: 2015-09-21, 10:05:00 Errors from executing:

    The current directory is invalid.
    

    Result: The job executed successfully.

     

    Error seems to be  only  from WebConsole .

     

    Thank you  for your help

    Rosie

     

     

     

     

     

  • rosie
    rosie
    5 Posts

    Re: How to Run db2diag command in Job Manager

    ‏2015-10-07T16:13:02Z  

    thank you everyone  ;

    as it turns out   the IBM SSH services   needed to be started  with the specific account  I was using   and  not  with  local system .

    Now  if  I  can  only   figure out  how to get a result   sets   of sql script   emailed to   certain recipients.

    so glad  I have  you  to turn too

    Rosie