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(CAST(? 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 SQLBindParameter, 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'20031027085014430717000000'"

Is there a way to do it with SQLBindParameter?

thanks
andreas

[Mark A]

"alederer" <al******@gmx.at> wrote in message
news:3f********@e-post.inode.at...

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(CAST(? 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 SQLBindParameter, 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'20031027085014430717000000'"

Is there a way to do it with SQLBindParameter?

thanks
andreas

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]

> 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.

Correction. DB2 stores a timestamp internally in 10 bytes, not 13.

[alederer]

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:

"alederer" <al******@gmx.at> wrote in message
news:3f********@e-post.inode.at...

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(CAST(? 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 SQLBindParameter, 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'20031027085014430717000000'"

Is there a way to do it with SQLBindParameter?

thanks
andreas

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]

"alederer" <al******@gmx.at> wrote in message
news:3f********@e-post.inode.at...

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

You don't think that current timestamp would be unique for each transaction?
Just wondering if you have tested that.

[Knut Stolze]

Mark A <[e-mail]> wrote:

"alederer" <al******@gmx.at> wrote in message
news:3f********@e-post.inode.at...

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.

You don't think that current timestamp would be unique for each
transaction? Just wondering if you have tested that.

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]

Knut Stolze wrote:

Mark A <[e-mail]> wrote:

"alederer" <al******@gmx.at> wrote in message
news:3f********@e-post.inode.at...

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.

You don't think that current timestamp would be unique for each
transaction? Just wondering if you have tested that.

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.

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(CAST..." solution is
fascinating.

thanks
Andreas