How to use m switch in SQL script ?

DBFinder · #1 (**permalink**) 11-05-09, 09:41

Hi Guys,

How can I use -m switch in middle of my SQL script.

I used this while running this script as

Quote:

db2 -mtvf DelRows.SQL

But I need a few rows of long script to show me how many rows were affected.

Code:

:
:
----need to switch m ON
insert into trans_history select * from trans where trans_id < current date - 21 days;

export to trans_history.ixf of ixf select * from trans where trans_id < current date - 21 days;

delete from trans  where trans_id < current date - 21 days;

----need to switch m OFF
:
:
:

Is there a way to do it. -m switch does not work using UPDATE COMMAND OPTIONS USING m ON

Any help is appreciated.

Thanks
DBFinder

db2dummy1 · #2 (**permalink**) 11-05-09, 20:20

We could talk about using "UPDATE COMMAND OPTIONS" if only it worked as advertised ....

=> db2 update command options using m off
SQL0104N An unexpected token "off" was found following "OFF". Expected
tokens may include: "ON". SQLSTATE=42601

=> db2 update command options using m on
SQL0104N An unexpected token "on" was found following "ON". Expected tokens may include: "A". SQLSTATE=42601

db2girl · #3 (**permalink**) 11-05-09, 21:09

Seems to match:
IBM IZ08431: THE DB2 UPDATE COMMAND OPTIONS USING 'M' OFF/ON RESULTS IN SQL0104N ERROR - United States

It's said to be fixed in v9.5 fp2+. I tried it on fp5 and got the same error...

db2dummy1 · #4 (**permalink**) 11-05-09, 21:16

That's ok - we can wait until V10 for such a substantial fix

DBFinder · #5 (**permalink**) 11-06-09, 04:25

Seems that no body paid much attention on that.
Any way, I am splitting script in sections.

Code:

db2 -tvf script1.sql
db2 -mtvf script2.sql
db2 -tvf script3.sql

That works already. My point to share was to find if anybody had any better workaround.

I still need another workaround for Task Center Schedules.

Thanks for your attention.

DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech