Seek equivalent of Oracle's empty_clob() for CLOB creation

[gimme_this_gimme_that]

In Oracle you can have a statement such as

insert into foo (foo_id,some_clob) values (100,empty_clob())

where empty_clob() inserts a clob address.
What is the approach in DB2 (8.1) to creating a row
having with a CLOB and no contents but with no null?

If someone has a user defined method that does about
the same thing that would be a help.

Thanks.

[Ian]

gi*******************@yahoo.com wrote:

In Oracle you can have a statement such as

insert into foo (foo_id,some_clob) values (100,empty_clob())

where empty_clob() inserts a clob address.
What is the approach in DB2 (8.1) to creating a row
having with a CLOB and no contents but with no null?

See the CLOB() function.

[gimme_this_gimme_that]

Good idea ...

insert into foo (foo_id,some_clob) values (100,clob())

Returns :

No authorized routine name "CLOB" of type "FUNCTION" having compatible
argu

Having reviewed DB2 Documentation nothing came up on CLOB() or CLOB
FUNCTION.

[Ian]

gi*******************@yahoo.com wrote:

Good idea ...

insert into foo (foo_id,some_clob) values (100,clob())

Returns :

No authorized routine name "CLOB" of type "FUNCTION" having compatible
argu

Having reviewed DB2 Documentation nothing came up on CLOB() or CLOB
FUNCTION.

You obviously did not read the documentation. Since you seem to have
trouble, here's the reference for the CLOB() function:

-CLOB--(--character-string-expression--+------------+--)-----><

'-,--integer-'
The schema is SYSIBM.

The CLOB function returns a CLOB representation of a character string
type. In a Unicode database, if a supplied argument is a graphic string,
it is first converted to a character string before the function is executed.

character-string-expression
An expression that returns a value that is a character string.

integer
An integer value specifying the length attribute of the resulting
CLOB data type. The value must be between 0 and 2 147 483 647. If
integer is not specified, the length of the result is the same as the
length of the first argument.

The result of the function is a CLOB. If the argument can be null, the
result can be null; if the argument is null, the result is the null value.

This topic can be found in: SQL Reference, Volume 1.

[Serge Rielau]

gi*******************@yahoo.com wrote:

In Oracle you can have a statement such as

insert into foo (foo_id,some_clob) values (100,empty_clob())

where empty_clob() inserts a clob address.
What is the approach in DB2 (8.1) to creating a row
having with a CLOB and no contents but with no null?

If someone has a user defined method that does about
the same thing that would be a help.

Thanks.

Just use an empty string ('') DB2 will cast it it up as appropriate.
But if you want a function:

CREATE FUNCTION CLOB() RETURNS CLOB(10M)
NO EXTERNAL ACTION DETERMINISTIC CONTAINS SQL
RETURN ''

Note that in DB2 and empty string/LOB and NULL completely different.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[gimme_this_gimme_that]

Thanks Serge and Ian. Without you guys it would be a lot harder to get
things done.