By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,277 Members | 1,505 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,277 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/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.
Nov 12 '05 #2

P: n/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.
Nov 12 '05 #3

P: n/a
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.


Nov 12 '05 #4

P: n/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.
Nov 12 '05 #5

P: n/a
Mark A <ma@switchboard.net> 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
Nov 12 '05 #6

P: n/a
Knut Stolze wrote:
Mark A <ma@switchboard.net> 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

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.