How to use parameter markers for "for bit data" columns?

[alederer]

Hallo!

I have a table tstest(ts char(13) for bit data constraint a unique).

This column is filled in a trigger with generate_unique ().

In a application (CLI), I have the values of this column as timestamp
representation: e.g.: select timestamp(ts) from tstest

1
--------------------------
2003-10-27-08.50.14.430717

My problem is, that i want do delete from this table with:
"delete from tstest where ts=?"
This means I want to use a parameter marker for the value.
How should I format the value, so that db2 understands it as char(13)
for bit data?

I have tried to use "delete from tstest where ts=TIMESTAMP(CA ST(? AS
CHAR(26))", which works, but has a poor performance. So I think it would
have a better performance when I use the value without the cast.

Is there a way in SQLBindParamete r, to tell db2 that it should interpret
the given value string as the hex representation of binary data?

e.g. with plain sql i would write:
"delete from tstest where ts=x'2003102708 501443071700000 0'"

Is there a way to do it with SQLBindParamete r?

thanks
andreas

[Mark A]

Re: How to use parameter markers for "for bit data" columns?

"alederer" <alederer@gmx.a t> wrote in message
news:3f9d0840$1 @e-post.inode.at.. .[color=blue]
> Hallo!
>
> I have a table tstest(ts char(13) for bit data constraint a unique).
>
> This column is filled in a trigger with generate_unique ().
>
> In a application (CLI), I have the values of this column as timestamp
> representation: e.g.: select timestamp(ts) from tstest
>
> 1
> --------------------------
> 2003-10-27-08.50.14.430717
>
> My problem is, that i want do delete from this table with:
> "delete from tstest where ts=?"
> This means I want to use a parameter marker for the value.
> How should I format the value, so that db2 understands it as char(13)
> for bit data?
>
> I have tried to use "delete from tstest where ts=TIMESTAMP(CA ST(? AS
> CHAR(26))", which works, but has a poor performance. So I think it would
> have a better performance when I use the value without the cast.
>
> Is there a way in SQLBindParamete r, to tell db2 that it should interpret
> the given value string as the hex representation of binary data?
>
> e.g. with plain sql i would write:
> "delete from tstest where ts=x'2003102708 501443071700000 0'"
>
> Is there a way to do it with SQLBindParamete r?
>
> thanks
> andreas
>[/color]
I am not sure why your Timestamp column is defined for bit data. Even though
you see the data as 26 bytes, DB2 stores the data for a timestamp column as
13 bytes without you defining it as bit data.

My understanding is that bit data is primarily used when no code page
conversion is desired.

[Mark A]

Re: How to use parameter markers for &quot;for bit data&quot; columns?

> I am not sure why your Timestamp column is defined for bit data. Even
though[color=blue]
> you see the data as 26 bytes, DB2 stores the data for a timestamp column[/color]
as[color=blue]
> 13 bytes without you defining it as bit data.
>
> My understanding is that bit data is primarily used when no code page
> conversion is desired.
>[/color]
Correction. DB2 stores a timestamp internally in 10 bytes, not 13.

[alederer]

Re: How to use parameter markers for &quot;for bit data&quot; columns?

The reason for char(13) is, that the db2 function generate_unique ()
returns "char(13) for bit data". And this function is used inside a
trigger to fill the specified column.

Andreas


Mark A wrote:
[color=blue]
> "alederer" <alederer@gmx.a t> wrote in message
> news:3f9d0840$1 @e-post.inode.at.. .
>[color=green]
>>Hallo!
>>
>>I have a table tstest(ts char(13) for bit data constraint a unique).
>>
>>This column is filled in a trigger with generate_unique ().
>>
>>In a application (CLI), I have the values of this column as timestamp
>>representatio n: e.g.: select timestamp(ts) from tstest
>>
>>1
>>--------------------------
>>2003-10-27-08.50.14.430717
>>
>>My problem is, that i want do delete from this table with:
>>"delete from tstest where ts=?"
>>This means I want to use a parameter marker for the value.
>>How should I format the value, so that db2 understands it as char(13)
>>for bit data?
>>
>>I have tried to use "delete from tstest where ts=TIMESTAMP(CA ST(? AS
>>CHAR(26))", which works, but has a poor performance. So I think it would
>>have a better performance when I use the value without the cast.
>>
>>Is there a way in SQLBindParamete r, to tell db2 that it should interpret
>>the given value string as the hex representation of binary data?
>>
>>e.g. with plain sql i would write:
>>"delete from tstest where ts=x'2003102708 501443071700000 0'"
>>
>>Is there a way to do it with SQLBindParamete r?
>>
>>thanks
>>andreas
>>[/color]
>
> I am not sure why your Timestamp column is defined for bit data. Even though
> you see the data as 26 bytes, DB2 stores the data for a timestamp column as
> 13 bytes without you defining it as bit data.
>
> My understanding is that bit data is primarily used when no code page
> conversion is desired.
>
>[/color]

[Mark A]

Re: How to use parameter markers for &quot;for bit data&quot; columns?

"alederer" <alederer@gmx.a t> wrote in message
news:3f9d159b$1 @e-post.inode.at.. .[color=blue]
> The reason for char(13) is, that the db2 function generate_unique ()
> returns "char(13) for bit data". And this function is used inside a
> trigger to fill the specified column.
>
> Andreas
>
>[/color]
You don't think that current timestamp would be unique for each transaction?
Just wondering if you have tested that.

[Knut Stolze]

Re: How to use parameter markers for &quot;for bit data&quot; columns?

Mark A <ma@switchboard .net> wrote:
[color=blue]
> "alederer" <alederer@gmx.a t> wrote in message
> news:3f9d159b$1 @e-post.inode.at.. .[color=green]
>> The reason for char(13) is, that the db2 function generate_unique ()
>> returns "char(13) for bit data". And this function is used inside a
>> trigger to fill the specified column.[/color]
>
> You don't think that current timestamp would be unique for each
> transaction? Just wondering if you have tested that.[/color]

GENERATE_UNIQUE gives you a unique value - across transactions, statements,
and even within a single statement. The description of that function says
that the unique value is based on the system time and some further
information is added to make it truly unique. You can also use the
timestamp function on the unique value returned by GENERATE_UNIQUE to
extract the timestamp portion. The result of that might not be unique.

So it does make sense to me what Andreas tries to do.

In any case, I would write the delete statement like this:

DELETE
FROM tstest
WHERE TIMESTAMP(ts) = ?

And then pass a regular timestamp value as SQL_NTS (null-terminated string)
through CLI. I don't think that a direct comparison of the unique value
with a timestamp is such a good idea because generate_unique does not
consist of a timestamp only and the additional information might cause
problems for you.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[alederer]

Re: How to use parameter markers for &quot;for bit data&quot; columns?

Knut Stolze wrote:
[color=blue]
> Mark A <ma@switchboard .net> wrote:
>
>[color=green]
>>"alederer" <alederer@gmx.a t> wrote in message
>>news:3f9d159b $1@e-post.inode.at.. .
>>[color=darkred]
>>>The reason for char(13) is, that the db2 function generate_unique ()
>>>returns "char(13) for bit data". And this function is used inside a
>>>trigger to fill the specified column.[/color]
>>
>>You don't think that current timestamp would be unique for each
>>transaction ? Just wondering if you have tested that.[/color]
>
>
> GENERATE_UNIQUE gives you a unique value - across transactions, statements,
> and even within a single statement. The description of that function says
> that the unique value is based on the system time and some further
> information is added to make it truly unique. You can also use the
> timestamp function on the unique value returned by GENERATE_UNIQUE to
> extract the timestamp portion. The result of that might not be unique.
>
> So it does make sense to me what Andreas tries to do.
>
> In any case, I would write the delete statement like this:
>
> DELETE
> FROM tstest
> WHERE TIMESTAMP(ts) = ?
>
> And then pass a regular timestamp value as SQL_NTS (null-terminated string)
> through CLI. I don't think that a direct comparison of the unique value
> with a timestamp is such a good idea because generate_unique does not
> consist of a timestamp only and the additional information might cause
> problems for you.
>[/color]

The deliberations of Knut are correct.
So I have changed my solution to use not the timestamp representation of
the unique value.
Now I convert the 26 char hex string representation to the 13 byte
binary representation and use this for the parameter value.
The speedup against the "where ts=TIMESTAMP(CA ST..." solution is
fascinating.

thanks
Andreas