DB2 Questions

1- Does DB2 have Extended Properties for objects like MS SQL has?
Any idea if it exists in Oracle?

2- Oracle has CREATE OR REPLACE PROCEDURE, is there
an equivalent in DB2?

I would like to know if it's simple to add a condition to delete before
the creation of a function or a stored procedure. For example in
Microsoft SQL the code below will check and drop the stored
procedure procTEST if it exists.

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[procTEST]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procTEST]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.procTEST
@ID INTEGER
AS
DELETE
FROM TableTEST
WHERE ID = @ID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


3- On the same topic what is the proper code to write an ALTER
PROCEDURE? For example if i want to Alter the above
procTest procedure by changing the
WHERE ID = @ID
to
WHERE ID = 1

I am getting an error why i simply change the word CREATE to
ALTER and try to execute the code.


4- In MS SQL there is no PACKAGE like i see DB2 and ORACLE have.
What is it and how would i know if i need to use it when i am converting
MS SQL procedures to DB2?


Thank you

re: DB2 Questions

serge wrote:[color=blue]
> 1- Does DB2 have Extended Properties for objects like MS SQL has?
> Any idea if it exists in Oracle?[/color]
DB2 supports distinct types (liek typedef in C) and structured types
(classes). Structured types are not common outside the world of extenders.
DB2 does not have "constraints" on types if that is what you mean.
To do that you could use distinct types and overload the cast functions.
[color=blue]
>
> 2- Oracle has CREATE OR REPLACE PROCEDURE, is there
> an equivalent in DB2?[/color]
DROP PROCEDURE followed by CREATE PROCEDURE ;-)
[color=blue]
> I would like to know if it's simple to add a condition to delete before
> the creation of a function or a stored procedure. For example in
> Microsoft SQL the code below will check and drop the stored
> procedure procTEST if it exists.
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[procTEST]') and
> OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[procTEST]
> GO
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
>
> CREATE PROCEDURE dbo.procTEST
> @ID INTEGER
> AS
> DELETE
> FROM TableTEST
> WHERE ID = @ID
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO[/color]
Works the same in DB2. The catalob view is SYSCAT.ROUTINES.
But why do the work to begin with.
I would simply do the DROP and catch the error. A lot less congested. :-)
[color=blue]
> 3- On the same topic what is the proper code to write an ALTER
> PROCEDURE? For example if i want to Alter the above
> procTest procedure by changing the
> WHERE ID = @ID
> to
> WHERE ID = 1
>
> I am getting an error why i simply change the word CREATE to
> ALTER and try to execute the code.[/color]
DB2 supports ALTER PROCEDURE only to change properties, not the body.
See CREATE OR REPLACE above.
[color=blue]
> 4- In MS SQL there is no PACKAGE like i see DB2 and ORACLE have.
> What is it and how would i know if i need to use it when i am converting
> MS SQL procedures to DB2?[/color]
A PACKAGE in DB2 and Oracle are two very different concepts.
In Oracle a package could loosely be compared to a "library".
Multiple routines get "packaged together". Mostly this is useful to
share global variables and state in general - I guess.

In DB2 a package is the collection of compiled plans for all the
statements in a file (e.g. a C-application file with embedded SQL).
If you use SQL Procedures, which I presume, you don't have to worry
about them.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab