cancel
Showing results for 
Search instead for 
Did you mean: 

SQL0750N in the upgrade during conversion,how drop trigger?

0 Kudos

ERROR: Detected the following errors:

# /media/SP702/SUM/SUM/abap/log/EU_SWITCH03.XUT: 2 ETQ399 Connecting to

database 'db6'. 3 ETQ398 SQL: RENAME "DD08L" TO "DD08L~~OLD" 4 ETQ010

Date & Time: 20150511090752 1 ETQ000

================================================== 4 ETQ010 Date &

Time: 20150511090752 1EETQ008 Error message: DBSL error 99 (db code -

750): SQL0750N Thesource table cannot be renamed because it is

referenced in a view, materialized query table, trigger, SQL function,

SQL method, check constraint, referential constraint, or XSR object.

SQLSTATE=42986 1EETQ003 exit code: "12"

saptest:db2hd1 63> cat /media/SP702/SUM/SUM/abap/log/EUSWITCH.ELG

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL EXECUTION ERRORS and RETURN CODE in EU_SWITCH01.XUT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2 ETQ399 Connecting to database 'db6'.

3 ETQ398 SQL: RENAME "DD02L" TO "DD02L~~OLD"

4 ETQ010 Date & Time: 20150511090751

1 ETQ000 ==================================================

4 ETQ010 Date & Time: 20150511090751

1EETQ008 Error message: DBSL error 99 (db code -750):

SQL0750N Thesource table cannot be renamed because it is referenced

in a view, materialized query table, trigger, SQL function, SQL method,

check constraint, referential constraint, or XSR object.

SQLSTATE=429861EETQ003 exit code: "12"

<NO EXIT CODE FOUND - PROGRAM ABORTED?>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL EXECUTION ERRORS and RETURN CODE in EU_SWITCH02.XUT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2 ETQ399 Connecting to database 'db6'.

3 ETQ398 SQL: RENAME "DD02T" TO "DD02T~~OLD"

4 ETQ010 Date & Time: 20150511090751

1 ETQ000 ==================================================

4 ETQ010 Date & Time: 20150511090751

1EETQ008 Error message: DBSL error 99 (db code -750):

SQL0750N Thesource table cannot be renamed because it is referenced

in a view, materialized query table, trigger, SQL function, SQL method,

check constraint, referential constraint, or XSR object.

SQLSTATE=429861EETQ003 exit code: "12"

<NO EXIT CODE FOUND - PROGRAM ABORTED?>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL EXECUTION ERRORS and RETURN CODE in EU_SWITCH03.XUT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2 ETQ399 Connecting to database 'db6'.

3 ETQ398 SQL: RENAME "DD08L" TO "DD08L~~OLD"

4 ETQ010 Date & Time: 20150511090752

1 ETQ000 ==================================================

4 ETQ010 Date & Time: 20150511090752

1EETQ008 Error message: DBSL error 99 (db code -750):

SQL0750N Thesource table cannot be renamed because it is referenced

in a view, materialized query table, trigger, SQL function, SQL method,

check constraint, referential constraint, or XSR object.

SQLSTATE=429861EETQ003 exit code: "12"

<NO EXIT CODE FOUND - PROGRAM ABORTED?>

I found note 381518,nofound solution.

saptest:db2hd1 5> db2level

DB21085I Instance "db2hd1" uses "64" bits and DB2 code

release "SQL09058" with

level identifier "06090107".

Informational tokens are "DB2

v9.5.0.8", "special_27408", "IP23263_27408", and

Fix Pack "8".

Product is installed at "/db2/db2hd1/db2_software".

saptest:db2hd1 6> db2 connect to hd1

Database Connection Information

Database server = DB2/AIX64 9.5.8

SQL authorization ID = DB2HD1

Local database alias = HD1

saptest:db2hd1 6> db2 " select viewnamefrom syscat.viewdep where

viewschema = 'SAPHD1' and bname = 'DD08L' "

VIEWNAME

--------------------------------------------------------------------------------------------------------------------------------

0 record(s) selected.

saptest:db2hd1 7> db2 " select viewnamefrom syscat.viewdep where

viewschema = 'SAPR3' and bname = 'DD08L' "

VIEWNAME

--------------------------------------------------------------------------------------------------------------------------------

0 record(s) selected.

saptest:db2hd1 10> db2 "select trigschema,trigname,tabschema,tabname

from syscat.triggers where tabname='DD08L'"

TRIGSCHEMA  TRIGNAME      TABSCHEMA    TABNAME

SAPHD1  /1LT/00000443INS  SAPHD1  DD08L

SAPHD1  /1LT/00000443UPD1        SAPHD1  DD08L

SAPHD1  /1LT/00000443DEL  SAPHD1  DD08L

3 record(s) selected.

saptest:db2hd1 12> "drop trigger SAPHD1./1LT/00000441INS"

drop trigger SAPHD1./1LT/00000441INS: Command not found.

saptest:db2hd1 13> db2 "drop trigger SAPHD1./1LT/00000441INS"

DB21034E Thecommand was processed as an SQL statement because it was

not a

valid Command Line Processor command. During SQL processing it

returned:

SQL0103N Thenumeric literal "1LT" is not valid. SQLSTATE=42604

saptest:db2hd1 14> db2 "drop view SAPHD1./1LT/00000441INS"

DB21034E Thecommand was processed as an SQL statement because it was

not a

valid Command Line Processor command. During SQL processing it

returned:

SQL0103N Thenumeric literal "1LT" is not valid. SQLSTATE=42604

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Dear all,

I use the DB2 control center drop trigger,this problem has been solved.

Thanks for all the replies!

Best regards!

Kong

Answers (2)

Answers (2)

S_Sriram
Active Contributor
0 Kudos

Hi  Kong

If possible do the full system restart and then try again in same phase.

BR

SS

0 Kudos

Hi SS,

   

Thank you for your reply!

I tried all the system restrt, but the error is still.

Best Regards!

Kong

0 Kudos

Dear Customer,

The SQL0750N indicates the following:


*****************************************************************************
SQL0750N
The source table cannot be renamed because it is referenced in a view,
materialized query table, trigger, SQL function, SQL method, check
constraint, referential constraint, or XSR object.


*****************************************************************************
So the triggers will need to be removed before the phase can be passed. They
can be recreated after this point.
 
According to the IBM documentation link

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsptopic=%2Fcom.ibm.db2.luw.messages.sql.doc%2Fd...

http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsptopic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdo...

"A trigger object can be dropped using the DROP TRIGGER statement"

The

db2 " select viewname from syscat.viewdep where viewschema = '<connect user>' and  bname = '<tabname>' "

showed no dependant views on the tables mentioned in file EU_SWITC.ELG?

You can output the content of each trigger first and save it in case you want to create the triggers again at a later stange after the upgrade.

Thank You.

Regards,

Deepika

0 Kudos

Hi Deepika,

     Thank you for your reply!

saptest:db2hd1 1> db2 " select viewname from syscat.viewdep where viewschema ='SAPHD1' and bname ='DD08L' "

VIEWNAME                                                                                                                  

--------------------------------------------------------------------------------------------------------------------------------

0 record(s) selected.

Best Regards!

Kong

0 Kudos

# cat EUSWITCH.ELG

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL EXECUTION ERRORS and RETURN CODE in EU_SWITCH01.XUT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2 ETQ399 Connecting to database 'db6'.

3 ETQ398 SQL: RENAME "DD02L" TO "DD02L~~OLD"

4 ETQ010 Date & Time: 20150513092441 

1 ETQ000 ==================================================

4 ETQ010 Date & Time: 20150513092441 

1EETQ008 Error message: DBSL error 99 (db code -750):

SQL0750N  Thesource table cannot be renamed because it is referenced in a view, materialized query table, trigger, SQL function, SQL method, check constraint, referential constraint, or XSR object.  SQLSTATE=42986

1EETQ003 exit code: "12"

<NO EXIT CODE FOUND - PROGRAM ABORTED?>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL EXECUTION ERRORS and RETURN CODE in EU_SWITCH02.XUT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2 ETQ399 Connecting to database 'db6'.

3 ETQ398 SQL: RENAME "DD02T" TO "DD02T~~OLD"

4 ETQ010 Date & Time: 20150513092441 

1 ETQ000 ==================================================

4 ETQ010 Date & Time: 20150513092441 

1EETQ008 Error message: DBSL error 99 (db code -750):

SQL0750N  Thesource table cannot be renamed because it is referenced in a view, materialized query table, trigger, SQL function, SQL method, check constraint, referential constraint, or XSR object.  SQLSTATE=42986

1EETQ003 exit code: "12"

<NO EXIT CODE FOUND - PROGRAM ABORTED?>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL EXECUTION ERRORS and RETURN CODE in EU_SWITCH03.XUT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

2 ETQ399 Connecting to database 'db6'.

3 ETQ398 SQL: RENAME "DD08L" TO "DD08L~~OLD"

4 ETQ010 Date & Time: 20150513092441 

1 ETQ000 ==================================================

4 ETQ010 Date & Time: 20150513092441 

1EETQ008 Error message: DBSL error 99 (db code -750):

SQL0750N  Thesource table cannot be renamed because it is referenced in a view, materialized query table, trigger, SQL function, SQL method, check constraint, referential constraint, or XSR object.  SQLSTATE=42986

1EETQ003 exit code: "12"

<NO EXIT CODE FOUND - PROGRAM ABORTED?>

#