467,198 Members | 1,205 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,198 developers. It's quick & easy.

DB2CMD in XP - DOS batch file

Folks,

I need to create a batch file that will do the following -

Establish a DB2 instance
Connect to a certain database
Get data using an sql and output it to a file

So, this is what I have done in my test.cmd file

My first command is -

"%ProgramFiles%\ibm\sqllib\bin\db2cmd" /i /w db2 connect to

<db> user <uid> using <pwd>

And this connects me fine.

My second command is -

"%ProgramFiles%\ibm\sqllib\bin\db2cmd" /i /w db2 -tvf

TimSConfqty.sql

This does not execute in batch mode - since it appears that a new
instance of a window was created. If I type in this command at the DOS
prompt created by the 1st command, it works fine. But, it never
executes from the batch test.cmd file. If I type exit at the DOS prompt
- the instance exits and test.cmd continues onto attempting to execute
the 2nd command (which fails, since the first window (instance ?) was
closed.

I think I understand the problem, don't know how to solve it. Well,
maybe I don't understand the problem. Any thoughts ?

Thanks.

Apr 19 '06 #1
  • viewed: 21086
Share:
7 Replies
You've got yhe correct idea but missed one of the details. You need to
place the connect statement in the file with your query. A single
execution of db2cmd is all that's necessary.

Your primary concern should be long term storage of the file containing
the userid and password. If this is a secured database, you will need to
establish appropriate controls over the file(s) containing the password.
Other mechanisms can be used to enable providing a password without
physically storing it on a disk drive but they will add some complexity
to the batch job.
Philip Sherman

rh********@gmail.com wrote:
Folks,

I need to create a batch file that will do the following -

Establish a DB2 instance
Connect to a certain database
Get data using an sql and output it to a file

So, this is what I have done in my test.cmd file

My first command is -

"%ProgramFiles%\ibm\sqllib\bin\db2cmd" /i /w db2 connect to

<db> user <uid> using <pwd>

And this connects me fine.

My second command is -

"%ProgramFiles%\ibm\sqllib\bin\db2cmd" /i /w db2 -tvf

TimSConfqty.sql

This does not execute in batch mode - since it appears that a new
instance of a window was created. If I type in this command at the DOS
prompt created by the 1st command, it works fine. But, it never
executes from the batch test.cmd file. If I type exit at the DOS prompt
- the instance exits and test.cmd continues onto attempting to execute
the 2nd command (which fails, since the first window (instance ?) was
closed.

I think I understand the problem, don't know how to solve it. Well,
maybe I don't understand the problem. Any thoughts ?

Thanks.

Apr 20 '06 #2
Solution offered by Norman W

Create a batch file to initialize a db2 command environment:
initdb2.bat
db2cmd -c -w -i %1.bat

Create a batch file with db2 and operating system commands:
runreport.bat
rem connect to database
db2 connect to sample user xxxx using xxxxx
rem run some sql and output to runreport.out
db2 -vf runreport.sql -z runreport.out

Create a file with sql:
runreport.sql
select count(*) from sysibm.systables

Execute this with:
initdb2 runreport

Apr 20 '06 #3
Solution offered by Norman W

Create a batch file to initialize a db2 command environment:
initdb2.bat
db2cmd -c -w -i %1.bat

Create a batch file with db2 and operating system commands:
runreport.bat
rem connect to database
db2 connect to sample user xxxx using xxxxx
rem run some sql and output to runreport.out
db2 -vf runreport.sql -z runreport.out

Create a file with sql:
runreport.sql
select count(*) from sysibm.systables

Execute this with:
initdb2 runreport

Apr 20 '06 #4
Phil, Thanks. I got this working. What are some of the options about
storing the password ?

Apr 20 '06 #5
This isn't the forum to discuss security issues in. The appropriate
answer to your question depends on your environment, security policies,
government regulations, input from your attorneys and probably a few
other factors.

Phil Sherman

Raj. wrote:
Phil, Thanks. I got this working. What are some of the options about
storing the password ?

Apr 21 '06 #6
Assuming that your using Windows 2000 or above.
Create an user environment variable and store the password in that
variable.

Since it is going to be an user shell environment varaible, other users
except the administrators will not be able to see this setting.

Then use it as follows
db2 connect to sample user xxxx using %password%

'%' s are required. Don't forget to put them around.

Pay back time:
This is something that I learnt from a great great DBI programmer..

regards,

Mehmet Baserdem

Apr 27 '06 #7

Create an user environment variable and store the password in it. (i.e.
mydb2password)

Since it is going to be an user shell environment variable, other users
except the administrators will not be able to see it.

Then use it as follows
db2 connect to sample user xxxx using %mydb2password%

'%' s are required. Don't forget to put them around.

Pay back time:
This is something that I learned from a great great DBI programmer..

regards,

Mehmet Baserdem

Apr 27 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by SkySea@aol.com | last post: by
3 posts views Thread by emman_54@hotmail.com | last post: by
14 posts views Thread by =?Utf-8?B?R2lkaQ==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.