Good day everyone.
I have one question.
When I’m trying to extract DDL of a table, which have function, dependent upon it, using “Include drop statements”, Toad for DB2 (5.1.0.656) generates script like the following:
DROP TABLE tbl;
CREATE TABLE tbl;
CREATE OR REPLACE FUNCTION fnc;
The sequence is wrong an the script will not work, because the first drop statement will not be executed, because at that time there is a function, which depends upon the table we want to drop.
In previous versions Toad generated scripts with right order. Like this :
DROP FUNCTION fnc;
DROP TABLE tbl;
CREATE TABLE tbl;
CREATE FUNCTION fnc;
Do anyone know, how to fix this problem? Maybe there is fix-pack or setting or option anywhere in Toad?
I know that Toad 5.2 have the same behavior.
Hi,
Syntax is correct,- look at screenshot
Syntax is correct. But try to execute the script. Client will throw: Couldn’t drop table because there is an object of type function depends on it.
On previous versions the statements was generated in right order. Without any create o replace and etc.
I see that you're script executed successfully. But in my case i catch an exception (see attached image)
Please, tell me your DB2 version and give scripts to reproduce
DB2 v9.5.500.784
Create test script:
CREATE TABLE TEMP.TEST (
FIELD1 INTEGER
) ;
ALTER TABLE TEMP.TEST
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
COMMIT;
CREATE FUNCTION TEMP.TEST1 ( PAR INTEGER )
RETURNS INTEGER
SPECIFIC "SQL130116155729700"
LANGUAGE SQL
INHERIT SPECIAL REGISTERS
RETURN
select 1 from TEMP.TEST;
COMMIT;
Then I go to Extract DDL, check "Include Drop Statements" and "Include objects that are dependent upon this originally selected".
Generated script is
DROP TABLE "TEMP"."TEST";
CREATE TABLE "TEMP"."TEST" (
"FIELD1" INTEGER
)
IN "USR_TBSPC_4K";
ALTER TABLE "TEMP"."TEST"
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
CREATE OR REPLACE FUNCTION "TEMP"."TEST1" ( "PAR" INTEGER )
RETURNS INTEGER
SPECIFIC "SQL130116155729700"
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
INHERIT SPECIAL REGISTERS
RETURN
select 1 from TEMP.TEST;
The result is on the screen.
Following script is generated for LUW 9.5.7 and Toad 5.2 & 5.1
(w\o CREATE OR REPLACE)
DROP SPECIFIC FUNCTION “TEMP”.“SQL130116155729700”;
DROP TABLE “TEMP”.“TEST”;
CREATE TABLE “TEMP”.“TEST” (
“FIELD1” INTEGER
)
IN “TSDATA1”;
ALTER TABLE “TEMP”.“TEST”
DATA CAPTURE NONE
PCTFREE 0
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
SET CURRENT SCHEMA = “DB2ADMIN”;
SET CURRENT PATH = “SYSIBM”,“SYSFUN”,“SYSPROC”,“SYSIBMADM”,“DB2ADMIN”;
CREATE FUNCTION “TEMP”.“TEST1” ( “PAR” INTEGER )
RETURNS INTEGER
SPECIFIC “SQL130116155729700”
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
INHERIT SPECIAL REGISTERS
RETURN
select 1 from TEMP.TEST;
SET CURRENT SCHEMA = “db2admin”;
But not in my case…
Support also cannot reproduce this unfortunately…
“CREATE OR REPLACE” will be generated if your server version (returned by .Net provider) is >= 9.7
Which version is displayed in the “Connection manager”?
Toad doesn’t take into account auto_reval option (supposing that it is enabled)
From the Doc:
http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.config.doc%2Fdoc%2Fr0053784.html
If you upgrade a database from Version 9.5, or earlier, auto_reval is set to DISABLED. The revalidation behaviour is the same as in the previous releases.
If you set this parameter to IMMEDIATE it means that all dependent objects will be revalidated immediately after objects are invalidated. This applies to some DDL statements, such as ALTER TABLE, ALTER COLUMN, or CREATE OR REPLACE. The successful revalidation of the dependent objects does not rely on any other DDL changes; therefore, revalidation can be completed immediately.
In some cases, the syntax that you explicitly specify might override the setting of auto_reval. For example, if you use the DROP COLUMN clause of the ALTER TABLE statement without specifying CASCADE or RESTRICT, the semantics are controlled by auto_reval. However, if you specify CASCADE or RESTRICT, the previous cascade or restrict semantics are used, overriding the new semantics specified by auto_reval.
