DB2CMD in XP - DOS batch file

[rhjaisingh]

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.

[Phil Sherman]

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.

[Raj.]

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

[Raj.]

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

[Raj.]

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

[Phil Sherman]

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 ?

[Mehmet Baserdem]

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

[Mehmet Baserdem]

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