dos scripits for db2

rahul_s80 · #1 (**permalink**) 02-23-07, 04:44

Windows 2000

we usually run dos scripits for db2 using 2 file .
1st one intializeses the db2cmd env. for other

first file --> Click_me.bat
content--> db2cmd .\1.bat

second file -->1.bat
content--> db2 connect to sample ...
db2 create table..
db2 insert into..
db2 connect reset ..

is it possible to just create and call only 1 file which also initializes the db2cmd environment in windows .

guyprzytula · #2 (**permalink**) 02-23-07, 04:49

you could call db2cmd with argument xxxx
when db2 window is opened the xxxx script/commands will be executed
should be a bat/cmd file with db2 commands or db2 -tvf fff

rahul_s80 · #3 (**permalink**) 02-23-07, 05:10

Thanks Guy Przytula
But what i need is to provide a single batch file to my clients so that just a double click on it will do all the things.
Atpresent i have to provide 2 files as you explained in ur post.

grofaty · #4 (**permalink**) 02-23-07, 06:18

rahul_s80,
on Windows there has to be two files on Unix/Linux there can be only one. I hate it too, but thats the way it is.

grofaty · #5 (**permalink**) 02-27-07, 07:26

rahul_s80,
I have figured out one solution to have ONLY ONE batch file to execute more then one DB2 command. Save bellow code into batch file and execute it.

Code:

Please see code few posts bellow...

Note: Don't change "Initialization of DB2 environment on Windows" section. Only change "Run db2 commands" section.

Hope this helps,
Grofaty

rahul_s80 · #6 (**permalink**) 02-27-07, 09:03

Hey Grofaty
Thanks... good work....

grofaty · #7 (**permalink**) 02-28-07, 01:58

rahul_s80,
just one more note: path in DB2PATH variable is db2 installation path. The default is C:\Program Files\IBM\SQLLIB. If you installed the db2 software in any other directory or drive you need to change the path.

This code should work on Windows XP SP2. I don't know if this works on older versions like Windows 98. You should try this out by yourself. I just don't have any Win98 computer anymore.

Hope this helps,
Grofaty

grofaty · #8 (**permalink**) 02-28-07, 07:17

Hi,
I have changed the code. This code should work also if multiple batch files are executed at the same time e.g. scheduled at Windows (It uses timestamp and file name data in temporally directory). This script also gets the DB2PATH string from DB2 configuration, so there is no need of changing any data in "Initialization" section if DB2 is not installed on default path.

Code:

rem ---------------------------------------------------------
rem -- EXECUTE DB2 COMMANDS FROM ONE BATCH FILE
rem -- Script written by Grofaty. E-mail: grofaty@hotmail.com
rem -- Tested on DB2 v8.2 for Windows XP
rem -- Written at: 2007-02-28
rem ---------------------------------------------------------
rem
rem ---------------------------------------------------------
rem -- Initialization of DB2 environment on Windows
rem ---------------------------------------------------------
set timestamp=%date:~4% %time%
set timestamp=%timestamp::=%
set timestamp=%timestamp:.=%
set timestamp=%timestamp:,=%
set timestamp=%timestamp: =%
set DB2ClpFile=%temp%\DB2Clp%timestamp%%~nx0
set DB2PathFile=%temp%\DB2Path%timestamp%%~nx0
set DB2File=%temp%\DB2File%timestamp%%~nx0
echo echo %%DB2PATH%% ^> %DB2PathFile% > %DB2File%
echo echo %%DB2CLP%% ^> %DB2ClpFile% >> %DB2File%
call db2cmd /w /i /c %DB2File%
for /f "tokens=*" %%i in ('type %DB2PathFile%') do (set DB2PathVar=%%i)
set DB2PATH=%DB2PathVar%
for /f "tokens=1" %%i in ('type %DB2ClpFile%') do (set DB2ClpVar=%%i)
set DB2CLP=%DB2ClpVar%
del %DB2ClpFile%
del %DB2PathFile%
del %DB2File%
set DB2ClpVar=
set DB2PathVar=
set DB2ClpFile=
set DB2PathFile=
set DB2File=

rem -------------------------------------------------------
rem -- Run db2 commands
rem -------------------------------------------------------

db2 connect to sample
db2 select * from sysibm.sysdummy1
db2 connect reset

Note: Don't change "Initialization of DB2 environment on Windows" section. Only change "Run db2 commands" section. Save file with 'bat' extension and execute it.

Hope this helps,
Grofaty

n_i · #9 (**permalink**) 05-09-07, 16:31

I think there may be an easier way of doing this. These are the entire contents of a test batch file that can be called directly from Windows (e.g. by double-clicking).

Code:

@echo off
rem If we're not called from DB2CLP, call ourselves again
if "%DB2CLP%" == "" db2cmd /c /i /w %0 %* & goto :eof

rem Now the real stuff
db2 connect to MYDB
db2 select * from sysibm.sysdummy1
db2 connect reset
rem etc.

The line in red is the only one that needs to be added. What it does is check if the DB2 command line environment is initialized and if not, calls the same batch file again with db2cmd.exe and then exits.

grofaty · #10 (**permalink**) 05-10-07, 01:53

n_i,
that is an excellent solution! I really admire if someone makes some code more simple. Thanks!

grofaty · #11 (**permalink**) 01-30-08, 02:12

Hi,
just to let you know: above script (batch file) works only if there is stored in directory without spaces.

For example in today my sample I have stored it on Windows desktop and it doesn't work. Work-around is to store batch file in directory without spaces and create shortcut on Windows desktop.

Hope this helps,
Grofaty

n_i · #12 (**permalink**) 01-30-08, 09:08

Another workaround would be to put double quotes around %0:

Code:

if "%DB2CLP%" == "" db2cmd /c /i /w "%0" %* & goto :eof

rahul_s80 · #13 (**permalink**) 08-14-08, 05:18

In some other forum i found this for V9
SET DB2CLP=**$$**

grofaty · #14 (**permalink**) 08-19-08, 01:56

rahul_s80,
I have tried it on DB2/Windows v8.2 FP9 and this is not working. It looks like new solution...
Regards,
Grofaty

przytula_guy · #15 (**permalink**) 08-19-08, 02:43

SET DB2CLP=**$$** had to be added to profile if cygwin was being used - otherwise we had a problem with db2 commands in a window (connection lost..)
see the apar http://www-1.ibm.com/support/docview...id=swg1JR26025

Internet Services

Web Development

Digital Marketing

Consumer Tech