Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Red face Unanswered: Running Db2 Scripts through JAVA

    hi I am giving db2 script path to db2ScriptName in the java code ,
    i want to run this script by passing parameters to script say i want to pass dbname and schema name to the script , how can i do it throgh java code,

    plz see below my question :

    my java code function looks like :


    public void run() {
    String line = null;
    Process p = null;
    BufferedReader stdInput = null, stdError = null;

    try {
    File f = new File(db2ScriptName);
    String dirName = f.getParent();
    p = Runtime.getRuntime().exec("db2cmd /c /i /w " + "cd " + dirName + " && db2 -tvf " + db2ScriptName);

    }

    my db2 script looks like :

    connect to masterdb;
    echo "database Sucessfully connected";
    set current schema='VERSION6';
    echo "Schema Set is Done";
    echo "Calling function fn_country for delhi";
    call VERSION6.fn_poi('dl_poi');
    echo "Function executed Sucessfully";


    i want to pass this masterdb , VERSION6 and dl_poi through java code , how can i do that ?

  2. #2
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Sanjay,

    As far as .sql scripts are concerned; there is no way to pass paramaters to it.

    However, a very simple way is to create the script on the fly , and call it.

    I have been using this tech for years.

    Please let me know if you want me to write java/shell/bat code for you.

    Regards

    DBFinder

  3. #3
    Join Date
    Mar 2011
    Posts
    4

    Smile

    yes please help me to write java/bat code fro me..

    thanks in advance

    sanjay

  4. #4
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Hi Sanjay,

    Here are complete working .BAT scripts. You may modify these as required.

    I may not have understood your exact application, but these scripts can be modified as you need them.

    For java, I will take a day or two. ( I did never need java because db2 is so easy to program in every language or script that I just kept writing .bat scripts).

    I will post java versions soon. Plz check the attachments.

    Version 1 - main_1.bat
    Code:
     @echo off
    set "masterdb=%1"
    set "version=%2"
    set "poi=%3"
    if "%poi%"  == ""  goto ShowSyntax
    if not "%4" == ""  goto ShowSyntax
    
    :: we are good to run db2 script now - we will call direct from this bat script
    ::==================== 
    
    db2 connect to %masterdb%
    if errorlevel 1 exit /b %errorlevel%
    db2 echo "database Sucessfully connected"
    db2 set current schema=%version%
    if errorlevel 1 exit /b %errorlevel%
    db2 echo "Schema Set is Done"
    db2 echo "Calling function fn_country for %poi%"
    db2 call %version%.fn_poi('%poi%',?)
    if errorlevel 1 exit /b %errorlevel%
    db2 echo "Function executed Sucessfully"
    
    exit /b 0
    
    
    :ShowSyntax
    db2 echo 
    db2 echo Syntax :
    db2 echo          %~n0 <db> <ver> <poi>
    db2 echo 
    db2 echo Three parameters exactly, please.
    exit /b 5
    Version 2- main_2.bat
    Code:
     @echo off
    set "masterdb=%1"
    set "version=%2"
    set "poi=%3"
    if "%poi%"  == ""  goto ShowSyntax
    if not "%4" == ""  goto ShowSyntax
    
    :: we are good to run db2 script now - generate the script and call it
    ::==================== let the script name be main_2.sql
    
    echo connect to %masterdb% >main_2.sql
    echo echo database Sucessfully connected >>main_2.sql
    echo set current schema=%version% >>main_2.sql
    echo echo Schema Set is Done >>main_2.sql
    echo echo Calling function fn_country for %poi% >>main_2.sql
    echo call %version%.fn_poi('%poi%',?) >>main_2.sql
    echo echo Function executed Sucessfully >>main_2.sql
    
    db2 -sf main_2.sql
    
    
    ::------- delete the script - uncomment the line below to delete the sql script
    
    :: del main_2.sql
    
    exit /b 0
    
    
    :ShowSyntax
    db2 echo 
    db2 echo Syntax :
    db2 echo          %~n0 <db> <ver> <poi>
    db2 echo 
    db2 echo Three parameters exactly, please.
    exit /b 5
    Please have a look at produces SQL script in Version 2.

    Regards

    DBFinder
    Attached Files Attached Files

Posting Permissions

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