Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    20

    Unanswered: select statement in loop from batch

    Hello All, hope I put my question in right place.

    I have some problems with my store procedures and I want to turn on db2 trace and turn off it right after new error occur. All errors are written into exception_log table. So I need to turn on db2 trace and then check if new error are appeared in the exception_log table. To check it I use sql query something like this "select count(*) from exception_log where date = current date".
    I want to do this check in loop from batch file but I have no idea how to do it.
    I also need write result of this select statement into a variable and compare current result with previous.

    Can anybody help me with this?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    The question is not very clear
    db2 trace is a debugging tool and should not be used to debug a sproc
    other tools/utilities are available todo thsi
    can you be more specific
    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
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Drakosha View Post
    Hello All, hope I put my question in right place.
    To check it I use sql query something like this "select count(*) from exception_log where date = current date".
    I want to do this check in loop from batch file but I have no idea how to do it.
    I also need write result of this select statement into a variable and compare current result with previous.
    Code:
    ::Batch file to check new error
    @echo off
    db2 connect to my_db
    if errorlevel 1 goto no_connect
    
    ::-----------------Note down the current count
    db2 -x select count(*) from exception_log >current_count.txt
    if errorlevel 1 goto error_occured
    set /p current_count=<current_count.txt
    
    
    ::-----------keep looping till new error
    
    :loop_on
    
    db2 -x select count(*) from exception_log >current_count.txt
    if errorlevel 1 goto error_occured
    set /p new_count=<current_count.txt
    
    if  not "%current_count%" == "%new_count%" goto count_changed
    
    ::---------------------delay 1 minute
    ping 127.1 -n 60 >nul
    ::---------------------keep looping
    goto loop_on
    
    ::=====================================================
    
    
    
    :count_changed
    
    ::---------------------take appropriate action here ; e.g. turn trace off
    
    
    
    ::--------------------TERMINATE --- exit /b
    db2 -v connect reset 
    exit /b
    ::-------------------OR--- jump back to loop :- loop_on 
    goto loop_on
    
    
    
    :error_occured
    
    ::----------------------- if here , it indicates the error ; handle it 
    ::------------------------and jump into loop or exit
    echo. Error - %errorlevel%
    ::----------------------clear the error
    type nul
    ::----------------------take action here
    
    
    
    
    ::--------------------TERMINATE --- exit /b
    db2 -v connect reset 
    exit /b
    ::-------------------OR--- jump back to loop :- loop_on 
    goto loop_on
    
    :no_connect
    
    echo.
    echo.              Could not connect to db2 - exiting . . . 
    
    exit /b
    
    ::---------------------Complete

    Untested : Please let me know if any question : dbfinder@gmail.com


    Regards
    Last edited by DBFinder; 09-24-11 at 04:57.

  4. #4
    Join Date
    Sep 2011
    Posts
    20
    @DBFinder, thank you very much, you really helped me!

Posting Permissions

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