Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: db2 batch script

    hi,
    Can you please advise me, writing windows batch script, which i need to schedule via task scheduler daily.

    we are running on db2 9.7 UDB / windows 2008

    i want to write a db2 export sql script using batch file (.bat) and need to check that export is successfull or not using the return code.
    how can I do this.

    please see my example in first_try.bat

    db2 connect to sample;
    export to c:\myexport.del of del select * from emp;

    rc=%ERRORLEVEL%

    if rc = 0 then echo success
    else
    echo error...
    end if


    Please adivse me.
    Thanks.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    if using bat file - all db2 commands should be pre-fixed with db2
    db2 export....
    look in sample directory below sqllib for samples
    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
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    An Introduction to DB2 UDB Scripting on Windows
    Code:
    set RC_SQL=%ERRORLEVEL%
    IF %RC_SQL% EQU 4 GOTO DB2SqlError
    IF %RC_SQL% EQU 2 GOTO DB2SqlWarning
    IF %RC_SQL% EQU 1 GOTO DB2SqlOther
    IF %RC_SQL% EQU 0 GOTO DB2SqlSuccess 
    
    :DB2SqlError
    Echo Error..
    Goto end
    
     
    :DB2SqlWarning
    Echo Warning
    Goto end
    
    :DB2SqlOther
    Echo Something else happened… (select or fetch statement returned no rows)
    Goto end
    
    :DB2SqlSuccess
    Echo All OK
    Goto end
    
    :end
    Echo Script finished…

  4. #4
    Join Date
    Feb 2012
    Posts
    23

    example..

    obsolete.... see dr_te_z

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Baddus View Post
    obsolete.... see dr_te_z
    Gotto admit. It's his work. He just joined this forum himself. Welcome old friend.

  6. #6
    Join Date
    Feb 2012
    Posts
    22

    Set (Windows) environment variable DB2CLP to string **$$**

    Be sure to Set (Windows) environment variable DB2CLP to string **$$**

    This trick will :
    l "auto" detect the PID of the instance ( actually the process/instance variable DB2INSTANCE is pointing to )

    When issuing DB2 commands in a batch script, f.i. in task schedular it will prevent orphaned CMD.EXE processes hanging around....

    (it actually prevents a new CLP command shell being opened, that isn't closed after the script has ended)

  7. #7
    Join Date
    Feb 2012
    Posts
    22

    Using environment variable DB2CLP to string **$$** in PowerShell

    see :

    Run DB2CMD commands through PowerShell

    Set environment variable DB2CLP to string **$$**

  8. #8
    Join Date
    Nov 2011
    Posts
    87
    Hi, Thanks for your advises.
    I have managed to created a batch script and its working fine except my SQL statement.

    The batch script failed on this perticular line as the SQL is not in continuious line.

    DB2 EXPORT TO C:\MYEXPORT.DEL OF DEL SELECT * FROM MYTABLE
    WHERE ID=9999
    AND AMOUNT >100


    As this is a small sql i can write all in one line, but my actual sql is around 25 lines and it is not possible to write in one continuous line in Notepad.

    any ideas ?

    Please advise me.
    Thanks.

  9. #9
    Join Date
    Feb 2012
    Posts
    23
    You can put your SQL in a separate file e.g. test.sql and then call it from your batch script e.g. db2 -o- +c -td; -m -v -f tst.sql -z logTst.txt

Posting Permissions

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