TOAD and TFS suggestion

Toad for IBM DB2
1

Hello,

I am glad that TOAD is able to use TFS! It’s great for tracking db changes.

I have one suggestion I think would be great. In toad you can view the script for nsql store procedures and it includes the security on that proc which is very helpful!

But when I commit to TFS it only supports the DDL of the sproc. Is there anyway it can also include the grant statements? I hope its easy to do since it pulls it into the script window within toad.

Let me know if this does not make sense I can give you some detail on my request.

The reason I am asking is this is because I can’t alter sproc and have to drop and re-create them every time I make a change so therefore I lose all security once I drop the object.

Thanks for the hard work! Toad rocks!

I am using Toad for DB2 6.2.0.315.

Robert

2

Robert,

Regarding your statement:

The reason I am asking is this is because I can’t alter sproc and have to drop and re-create them every time I make a change so therefore I lose all security once I drop the object.

Are you connecting to DB2 LUW or z/OS and what types of alterations are you doing? The reason I ask is that some alterations will support the CREATE OR REPLACE syntax and should keep any existing grants on those stored procedures.

Jeff

3

Hello Jeff,

I am using z/OS. I would love to use alters but my DBA is against any alters in the database. Everything has to be dropped and re-created.

Thanks,

Robert

4

Jeff,

Do you think this is possible?

Thanks,

Robert

5

Hello,

I work with DB2 zOS since versione 1.2 (and with DB2LUW since v7)

and is very stange use DROP/CREATE instead Alter.

Using last version (10 or 11) is possible Alter table definition

adding/deleting/changhe columns definitions and more.

Using DROP and CREATE you loose all authorities (Grant) that must

be reassigned.

For me is unsual in a Production environment DROP and CREATE TB and TS+TB,

simply to modify columns characteristics.

Gianfranco Casati

Da: “robert.krall”

Per: ,

Data: 17/08/2016 19:56

Oggetto: RE: [Toad for IBM DB2 - Discussion Forum] TOAD and TFS

suggestion

RE: TOAD and TFS suggestion

Reply by robert.krall

Jeff,

Do you think this is possible?

Thanks,

Robert

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for IBM DB2 Forum notifications altogether.

Toad for IBM DB2 - Discussion Forum

Flag this post as spam/abuse.

6

Believe me your preaching to the choir! I find it very odd we have to drop and recreate everything and find it annoying. But I the DBA makes all the rules and believe that is best practice.

7

Gianfranco, where do you see a drop column syntax for DB2 z/OS?

This the IBM technical reference for version 10

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_altertable.html

Scripts created by Toad have been over %95 correct in my experience. What you are complaining about is simply not possible.

Yahya

8

To add a correction to my previous not; DB2 z/OS version 11 seems to have a drop column capability, but I haven’t work with version 11 yet.

>>-ALTER TABLE--*table-name*
-------------------------------------->
   .-----------------------------------------------------------------------------------------------------.   
   V (1) (2) (3) .-COLUMN-. |   
>--------------------+-ADD--+--------+--| column-definition |------------------------------------------+-+-><
    | .-COLUMN-. |    
    +-ALTER--+--------+--| column-alteration |----------------------------------------+    
    +-RENAME COLUMN--*source-column-name*--TO--*target-column-name*-----------------------+    
    | .-COLUMN-. |    
    +-DROP--+--------+--*column-name*--RESTRICT-----------------------------------------+    
    +-ADD PERIOD--| period-definition |-----------------------------------------------+    
    | (4) |
9

Gianfranco,

Have you ever altered a view before? It seems like in DB2 you can’t alter the DDL in a view when you alter a view and only regenerate the ddl.

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_alterview.html

I thought you could alter the DDL but maybe I am just thinking of SQL Server and not DB2…

Thanks!

Robert

10

Hello,

this is a example, I’m sure that wotk with v11 mode New Function Mode.

ALTER TABLE TB_Owner/SchemaL.MY_TABLE1

DROP COLUMN MY_COL_5;

COMMIT;

After that is a good idea run Runstats and Rebind all packages.

Attention !!

Doesn’t work in case colum is part of Primary key, in that case is

necessary use a different sequence (Drop PK, Drop colum, create new PK,

Runstats + Rebind)

Cordiali Saluti, Best Regards, Bonne Réception, Mit Freundlichen Gruessen,

Venlig Hilsen, Saludos, Atenciosamente, Pokà/Met Vriendelijke Groeten

Gianfranco Casati

Certified Senior IBM System Engineer

IT Security is a Smart Investment

The human mind is our fundamental resource.

John Fitzgerald Kennedy

USA President

Da: “yahya.nattagh”

Per: ,

Data: 19/08/2016 12:24

Oggetto: RE: [Toad for IBM DB2 - Discussion Forum] TOAD and TFS

suggestion

RE: TOAD and TFS suggestion

Reply by yahya.nattagh

Gianfranco, where do you see a drop column syntax for DB2 z/OS?

This the IBM technical reference for version 10

www.ibm.com/…/SSEPEK_10.0.0

/sqlref/src/tpc/db2z_sql_altertable.html

Scripts created by Toad have been over %95 correct in my experience. What

you are complaining about is simply not possible.

Yahya

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for IBM DB2 Forum notifications altogether.

Toad for IBM DB2 - Discussion Forum

Flag this post as spam/abuse.

11

The only possibility is Drop View + Create View.

Gianfranco Casati

Da: “robert.krall”

Per: ,

Data: 19/08/2016 22:39

Oggetto: RE: [Toad for IBM DB2 - Discussion Forum] TOAD and TFS

suggestion

RE: TOAD and TFS suggestion

Reply by robert.krall

Gianfranco,

Have you ever altered a view before? It seems like in DB2 you can’t alter

the DDL in a view when you alter a view and only regenerate the ddl.

www.ibm.com/…/SSEPEK_10.0.0

/sqlref/src/tpc/db2z_sql_alterview.html

I thought you could alter the DDL but maybe I am just thinking of SQL

Server and not DB2…

Thanks!

Robert

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for IBM DB2 Forum notifications altogether.

Toad for IBM DB2 - Discussion Forum

Flag this post as spam/abuse.

12

Ah yes. Maybe some day IBM will allow you to alter views. Kinda of annoying especially if you have dependent views and you need to add one column. In turn you have to drop everything and re-create it in the correct order. But then again there is A LOT for DB2 z/OS that annoys me.

13

Robert,

Thanks for the detailed information. We have opened internal issue TDB-3045 for the request to include Explicit Grant Statements along with the DDL in VCS.