Running Db2 Scripts through JAVA

sanjay bharkatiya · #1 (**permalink**) 03-17-11, 09:03

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 ?

DBFinder · #2 (**permalink**) 03-19-11, 09:37

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

sanjay bharkatiya · #3 (**permalink**) 03-19-11, 10:35

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

thanks in advance

sanjay

DBFinder · #4 (**permalink**) 03-20-11, 05:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech