Connecting DB2 through command line

[manish1005]

I wish to execute some DDL commands - "drop trigger <trigername> " in before job subroutine and "create trigger<trigername>" in afterjob subroutine.

As I could not get DB2 stage to execute DDL commands which do not return any result set, I am using DSExecute() in a before job subroutine.

First I used:

DB2Cmd1 = "db2cmd; db2 connect to TEST user Administrator using Manish123 ; db2 drop trigger Administrator.TRG_TBL1_INSERT; db2 connect reset;"

Call DSExecute("DOS", DB2Cmd1, ResultText, SystemReturnCode)

If SystemReturnCode = 0 Then
Call DSLogInfo("Success" : ResultText, "tempS")
End

If SystemReturnCode <> 0 Then
Call DSLogInfo("Error" : ResultText, "tempF")
End

It connects to db2 database successfully but does not drop the trigger, but return success status. Possible reason can be: executing "db2cmd; db2 connect to TEST user Administrator using Manish123 ; " on command line opens another window and further DB2/SQL commands need to executed on that window.
I cant find a way to embed into a single executable statement, the whole script from database connect to sql queries.


If I break up the Drop Trigger <triggername> into a separate DSExecute() function then as expected I get " Error: Deleting Insert Trigger DB21061E Command line environment not initialized."


Please suggest some possible way out.

tia,
manish

[ArndW]

I've done this sort of thing before with UNIX db2 connectivity without any problems. The nice thing about DB2 sessions is that they are persistent, so you only need to issue your connect once per session.

I'm not sure how you can issue 'db2cmd' before the connect, though.
What is the output of 'ResultText'?

[manish1005]

Well, I am using Windows.
Here first environment has to be initialized by "db2cmd" command - which opens up a new command prompt window, where i can type db2 commands.

>What is the output of 'ResultText'?
When I use

DB2Cmd1 = "db2cmd; db2 connect to TEST user Administrator using pwd ;"

Call DSExecute("DOS", DB2Cmd1, ResultText, SystemReturnCode)
If SystemReturnCode = 0 Then
Call DSLogInfo("Sucess: Establishing Connection to Db2 " : ResultText, "tempS")
End

If SystemReturnCode <> 0 Then
Call DSLogInfo("Error: Establishing Connection to Db2" : ResultText, "tempF")
End

DB2Cmd2 = "db2 drop trigger Administrator.TRG_TBL1_INSERT; db2 connect reset;"
Call DSExecute("DOS", DB2Cmd2, ResultText, SystemReturnCode)

If SystemReturnCode = 0 Then
Call DSLogInfo("Sucess: Deleting Insert Trigger " : ResultText, "tempS")
End

If SystemReturnCode <> 0 Then
Call DSLogInfo("Error: Deleting Insert Trigger " : ResultText, "tempF")
End

I get :

Assig2_Triggers..BeforeJob (tempS): Sucess: Establishing Connection to Db2
Assig2_Triggers..BeforeJob (tempF): Error: Deleting Insert Trigger DB21061E Command line environment not initialized.