niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » IBM DB2
Unlocking DB2 table/row locks
Post new topic   Reply to topic Page 2 of 26 [388 Posts] View previous topic :: View next topic
Goto page:  Previous  1, 2, 3, 4, ..., 24, 25, 26 Next
Author Message
Bob Hairgrove
*nix forums Guru


Joined: 03 Jun 2005
Posts: 372

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: ODBC update error with DB2 timestamp data Reply with quote
On Sat, 22 Jan 2005 11:00:15 +0800, "kaming" <guest@guest.com> wrote:

Quote:
Dear all,

I found that I can't update or delete a DB2 record with timestamp using
Access through ODBC. The Access complains "No current record" while I
attempt to update or delete the record with timestamp.

Do anyone have idea of how to fix this problem?

Thanks in advance!

Henry


I'm really surprised that no one up to now was able to tell you why
this will never work ... DB2 supports fractions of a second to six
decimal places (i.e. microseconds) and Access only goes to a
granularity of one second (no fractions). No patch exists because this
is an impossible thing to do. Access will never be able to distinguish
rows where the difference in timestamp values is less than one second.

The Access DATETIME data type is mapped to the ODBC data type
SQL_TIMESTAMP, but ... no fractions.

Note that MS-SQLServer uses yet another kind of "timestamp" data type
(a kind of AutoNumber automatically generated surrogate key) which has
nothing to do with the ODBC SQL_TIMESTAMP type.

Good luck.

--
Bob Hairgrove
NoSpamPlease@Home.com
Back to top
Sachindra Kumar Meshram v
*nix forums Guru Wannabe


Joined: 12 May 2005
Posts: 227

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re:java.sql.SQLException: No suitable driver Reply with quote
i'm trying migrate my jsp program to a new server (which is my production
server) and then i use jsp to connect to db2 and retrieve records from it.
but my java program is getting the error (java.sql.SQLException: No
suitable driver) when trying to connect to db2 ...

What is suitable driver for my application? Please give me some words
of advice! help!!!

thanks a lot..
Jacelyn

--
Message posted via http://www.dbmonster.com
Back to top
Visu
*nix forums beginner


Joined: 17 May 2005
Posts: 40

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: java.sql.SQLException: No suitable driver Reply with quote
You should include SQLLIB\java\db2java.zip (might have to rename it to
db2java.jar) to your classpath.

It depends on the application server you are using..

Here is a link on how to setup DB2 to use with Tomcat.

http://db2dba.blogspot.com/2004/11/setting-up-tomcat-for-db2-and-some.html

Goodluck!
Back to top
Sachindra Kumar Meshram v
*nix forums Guru Wannabe


Joined: 12 May 2005
Posts: 227

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: java.sql.SQLException: No suitable driver Reply with quote
thanks for ur information.. i'll try out, and see what i'll get..

--
Message posted via http://www.dbmonster.com
Back to top
TD
*nix forums beginner


Joined: 26 May 2005
Posts: 4

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: Concatenate column values from multiple rows Reply with quote
I borrowed this very useful piece of code and modified it for my table
definitions. It works very well with the exception of the warning of a
possible infinite loop in the Y table as defined. Is there any way this can
be removed ? The max common values for each record I have in my table 2 [or
second table] is say 10, that being there are a max number of 10 records to
concat but they vary from 1-10. I am also concating with a '+' between each
value but is always adds a '+' to the final value as well..

Further I am restricting the X table to a single FKEY value and using it as
a UDF I can call from a query or SQL SP.

Any suggestions would be greatly appreciated.

TD

Quote:
You could use recursive SQL like this:

WITH x(key, val, rnum) AS
( SELECT fkey, col1, row_number() over(partition by fkey)
FROM myTable ),
y(key, str, cnt, cnt_max) AS
( SELECT key, VARCHAR('', 1000), 0, MAX(rnum)
FROM x
GROUP BY key
UNION ALL
SELECT y.key, y.str || RTRIM(CHAR(x.val)), y.cnt + 1, y.cnt_max
FROM x, y
WHERE x.key = y.key AND
x.rnum = y.cnt + 1 AND
y.cnt < y.cnt_max )
SELECT key, str
FROM y
WHERE y.cnt = y.cnt_max;

"myTable" corresponds to the second table you listed above. If you want
to
restrict the result in some way, you only have to modify the common table
expression named "x".

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Back to top
Thiru
*nix forums addict


Joined: 20 May 2005
Posts: 80

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: Make easy money!! NO SCAM!! Please Read! Reply with quote
This isnot a right place...

Thiru
Back to top
Thiru
*nix forums addict


Joined: 20 May 2005
Posts: 80

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: simple, segmented, or partitioned tablespaces Reply with quote
Hi,
If tablespace is defined with multiple container, DB2 will allocate
block in Round-Robin fashion as and when new block is need. But table
can't span multiple tablespace...

Cheers,
Thiru.
WantedToBeDBA.
Back to top
Visu
*nix forums beginner


Joined: 17 May 2005
Posts: 40

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: Problem connecting to database directory after installing fixpack 7a Reply with quote
This is what i did to resolve the issue.

http://db2dba.blogspot.com/2005/02/sql10004c-io-error-occurred-while.html
Back to top
arjanblokzijl
*nix forums beginner


Joined: 26 May 2005
Posts: 1

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: com.ibm.db2.jcc.c.DisconnectException Reply with quote
Did anyone here find a solution for this problem? Coz we're having the same
problem with DB2 8.1 on an AIX machine, and are somewhat at a loss as how
to solve it.
Back to top
ClimberBear
*nix forums beginner


Joined: 25 Feb 2005
Posts: 3

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: JDBC Little Endian / Big endian problem with DB2 & WebSphere Reply with quote
Hi,

I have confirmed that the problem is that the int parameters are inverted
from little endian to big endian.

I have reproduced the problem with diferent SELECTs, always failing with int
fields.

Please, I'm totally desesperated. Do any of you have any suggestion (even
nonsenses). It will be welcome


"ClimberBear" <yomismo_QUITE_ESTO_SPAMERO@jmalbarran.com> wrote in message
news:...
Quote:
Hi,

I've got a very strange problem with a Websphere 5.1 cluster attached to
DB2
database in Mainframe z/OS.

I have a J2EE deployed application running normally fine agains the DB2
host. But, sometimes, the application stop working. In the moment that
application fails, the only way to get it working again is to restart
WebSphere server or JDBC pool.

After diagnose the problem we see a normal SELECT like

SELECT MYCOLUM FROM MYTABLE WHERE A=?

where A is an integer column, and we set the variable using JDBC setInt
method.
The code is fixed, like

mystatement.setInt(1,1); // The column ALWAYS is set to 1

The SELECT works fine regullarly, and we see the DB2 Host Trace
SELECT MYCOLUMN FROM MYTABLE WHERE A=?
PARAMETER MARKER 1 = 1

but when it begins failing, we see
SELECT MYCOLUMN FROM MYTABLE WHERE A=?
PARAMETER MARKER 1 = 16777216

The second number is 1, but with the bytes inverted. I suspect that, in a
determinated moment, the variable suffers a Big Endian -> Little Endian
conversion incorrectly.

Could you help me?

IBM Spain is working in the problem, but any suggestion, comment or
workaround will we welcome.

Jose Manuel






Back to top
BobTheDatabaseBoy
*nix forums beginner


Joined: 26 May 2005
Posts: 1

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: which query would perform better? Reply with quote
Serge Rielau wrote:
Quote:
Hi,

SQL functions get expanded into the original query (theya are
essentially macros).
Due to argument resolution there is a VALUES(1) being added however
which gets often removed by query rewrite.
The easiest way to find out is to take a look at the graphical
explain
graph. If the graph looks the same: voila.
If the residual VALUES(1) is being left you have a minor overhead
(likely not detectable).

Using the function will at compile time of the query, of course
require
a lookup of the function in the catalog.
(Indexscan). Again this is neglectable if the query runs for more
than a
split second or if it gets compiled into
an application.

Cheers
Serge

PS: If you want to know how it works in detail take a look at:
US5987455, US05963934 at http://www.patents.ibm.com
--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

which raises a question (i think the answer is Yes <G>): on page 223ff
of the Stored Procedure book 2nd edition, it talks about replacing
a join with a UDF, in this case getting Department Name from Depart-
ment Number using the foreign key between the two tables. "you
don't want to write your queries to join the employee table and the
department table...".

so, it seems that the only advantage is less SQL syntax in the source?
it's not a sort of Materialized Query?

thanks,
btdb
Back to top
Serge Rielau
*nix forums Guru


Joined: 29 Apr 2005
Posts: 1583

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: which query would perform better? Reply with quote
BobTheDatabaseBoy wrote:
Quote:
Serge Rielau wrote:

Hi,

SQL functions get expanded into the original query (theya are
essentially macros).
Due to argument resolution there is a VALUES(1) being added however
which gets often removed by query rewrite.
The easiest way to find out is to take a look at the graphical

explain

graph. If the graph looks the same: voila.
If the residual VALUES(1) is being left you have a minor overhead
(likely not detectable).

Using the function will at compile time of the query, of course

require

a lookup of the function in the catalog.
(Indexscan). Again this is neglectable if the query runs for more

than a

split second or if it gets compiled into
an application.

Cheers
Serge

PS: If you want to know how it works in detail take a look at:
US5987455, US05963934 at http://www.patents.ibm.com
--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support


which raises a question (i think the answer is Yes <G>): on page 223ff
of the Stored Procedure book 2nd edition, it talks about replacing
a join with a UDF, in this case getting Department Name from Depart-
ment Number using the foreign key between the two tables. "you
don't want to write your queries to join the employee table and the
department table...".

so, it seems that the only advantage is less SQL syntax in the source?
it's not a sort of Materialized Query?

thanks,
btdb

Yes. How could it be a materialized query given that the output depends

on the input....
The idea is to provide encapsulation without performance penalty.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Back to top
BobTheDatabaseBoy
*nix forums beginner


Joined: 26 May 2005
Posts: 1

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: which query would perform better? Reply with quote
well, it *could* define a maintained MQT. i can't get to
www-128.ibm.com
but this is a clip from the Lawson article on db2times:

The materialized query table is the table that is used to contain
materialized data derived from one or more source tables in a
fullselect and is similar to creating a view. However a view is
logical, whereas an MQT contains materialized data of the query
result. You could refer to an MQT as a 'materialized view'.

so, a UDF could force a materialized query among department number,
department name and employee number, which is then directly
returned by the UDF when called; no join.

just a thought,
btdb
Back to top
RdR
*nix forums addict


Joined: 04 Jul 2005
Posts: 62

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: db2 class set (net win2k) Reply with quote
If you have DB2 Enterprise Edition, you can use HADR to have the same data
on another DB2 by shipping the logs of your primary DB2 to your backup or
secondary DB2. Or you can use Data Propagator to replicate changes from your
primary DB2 to youor secondary DB2 in near real time. With this, you can
manually switch over in case of an unplanned outage, if you want to automate
this, you would need to do further research and see how you can use existing
DB2 APIs to automate the switch over.

Hope this helps.

Thanks,

RdR

"jacelyn Yswan via DBMonster.com" <forum@DBMonster.com> wrote in message
news:67630a5ff5a341c5bffa5c676a3b0c05@DBMonster.com...
Quote:
i would like to do a db2 class set (net win2k).. i wanna do that because
of i have two server and if one of the server is "system down", and the
other one can take over.. so, what i'm going to do?

ps give me the solution..
thanks a lot..
jacelyn

--
Message posted via http://www.dbmonster.com
Back to top
Gustavo Randich
*nix forums beginner


Joined: 24 Feb 2005
Posts: 14

PostPosted: Thu May 26, 2005 5:53 pm    Post subject: Re: CALL in TRIGGER and SQLCODE -746 Reply with quote
Hello Serge,
(Using DB2 8.2/Linux)

Quote:
Hi All,

I'd like to get feedback (through email) from folks who use CALL in
Trigger and do or do not get in trouble with the -746 SQLCODE:

SQL0746N Routine "<routine-name>" (specific name "<specific-name>")
violated nested SQL statement rules when attempting to perform

operation "<operation>" on table "<table-name>".
Quote:

Cheers
Serge

Here you've got it:

create table a (a integer)
@
create procedure b1(i_a integer)
begin
insert into a values (i_a);
end
@
create table b (a integer)
@
create trigger t_b after insert on b referencing new as nuevo for each
row mode db2sql
begin atomic
call b1(nuevo.a);
call b1(nuevo.a);
--insert into a values (nuevo.a);
--insert into a values (nuevo.a);
end
@
insert into b values (0)
@
insert into b values (1)
@
insert into b values (2)
@
select * from a
@
drop table b
@
drop procedure b1
@
drop table a
@
drop trigger t_b
@

Commenting the CALLs and uncommenting the INSERTs works fine. CALL
works if there's only one CALL.
Needless to say I'm desperate with this one.
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 2 of 26 [388 Posts] Goto page:  Previous  1, 2, 3, 4, ..., 24, 25, 26 Next
View previous topic :: View next topic
The time now is Tue Dec 02, 2008 5:37 am | All times are GMT
navigation Forum index » Databases » IBM DB2
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts How to ALTER a table using the ALTOBJ procedure Serge Rielau IBM DB2 1 Fri Jul 21, 2006 1:06 pm
No new posts Can't Select External Table from CSV File Resant Server 1 Fri Jul 21, 2006 2:45 am
No new posts Images Table Nicholas Vettese MySQL 1 Thu Jul 20, 2006 2:19 pm
No new posts grant on a specified table to a user Shinyday Server 4 Wed Jul 19, 2006 6:03 am
No new posts Database or table level logging? David Felio MySQL 5 Tue Jul 18, 2006 6:57 pm

Credit Cards | Anime Downloads | Loans | Advertising | Loans
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.3320s ][ Queries: 16 (0.2133s) ][ GZIP on - Debug on ]