You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@trafodion.apache.org by Eric Owhadi <er...@esgyn.com> on 2017/01/17 20:08:58 UTC
question on using Batch for a select statement with jdbc
Hi Trafodioneers,
Have following jdbc question:
select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts?
Can I use executeQuery(), and that will do the trick as long as I use addBacth()?
Or it is not possible to use addBatch for this use model?
Thanks in advance for the help,
Eric
RE: question on using Batch for a select statement with jdbc
Posted by Dave Birdsall <da...@esgyn.com>.
“the above query is not generating nested join plan because my table d is empty still. I am hoping it will do NJ as soon as I have some real data and updated stats… “
You probably cannot infer that you’re not getting nested join due to lack of data in the table if the table is a Trafodion table. Any data written to the table would be rolled back when the transaction aborts.
From: Eric Owhadi [mailto:eric.owhadi@esgyn.com]
Sent: Tuesday, January 17, 2017 5:27 PM
To: user@trafodion.incubator.apache.org
Subject: RE: question on using Batch for a select statement with jdbc
Hi Suresh,
Good thought, I actually did exactly what you said:
1000 -> 1.8 sec compile
2000: 6.8 sec
4000: 28.3 sec.
All plan generate tuple list, and not unpack nodes. Suspecting some recursive call, non linear scalable linked with tuple list handling, and associated stack overflow common problems with recursive calls?
Anu found core dumps associated with 10000 runs. And these are happening from trafci or sqlci…
the above query is not generating nested join plan because my table d is empty still. I am hoping it will do NJ as soon as I have some real data and updated stats…
I wish I could force the plan manually as generated by:
select x.a, d.a from (values (?[10000],?[10000],?[10000],?[10000])) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
beautifully generating unpack node …
Eric
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Tuesday, January 17, 2017 7:17 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: Re: question on using Batch for a select statement with jdbc
Hi Eric,
I don't there is a generic CQD here. If there was a core file and we analyzed it we might be able to find something specific to this situation. Can we please try with say 1000 values and 4000 parameters?
Thanks
Suresh
On Tue, Jan 17, 2017 at 4:53 PM, Eric Owhadi <er...@esgyn.com>> wrote:
Trying this workaround but hitting a timeout exception at prepare time:
final static String missingVal1 = "select x.a, d.a from (values ";
final static String missingValVar = "(?,?,?,?)";
final static String missingIVal2 = ") as x(a,b,c,d) join d on x.b = d.b and x.c = d.c and x.d = d.d where x.a is not null";
StringBuilder missingVals = new StringBuilder(101000);
missingVals.append(missingVal1);
missingVals.append(missingValVar);
for(int i=1; i< rowsetSize; i++){
missingVals.append(',').append(missingValVar);
}
missingVals.append(missingVal2);
PreparedStatement missingValsstsmt= conn2.prepareStatement(missingVals.toString());
Exception in thread "main" org.trafodion.jdbc.t4.TrafT4Exception: Server aborted abnormally or Connection timed out
at org.trafodion.jdbc.t4.TrafT4Messages.createSQLException(TrafT4Messages.java:284)
at org.trafodion.jdbc.t4.InputOutput.doIO(InputOutput.java:376)
at org.trafodion.jdbc.t4.T4Connection.getReadBuffer(T4Connection.java:157)
at org.trafodion.jdbc.t4.T4Statement.getReadBuffer(T4Statement.java:196)
at org.trafodion.jdbc.t4.T4Statement.Prepare(T4Statement.java:129)
at org.trafodion.jdbc.t4.InterfaceStatement.prepare(InterfaceStatement.java:1126)
at org.trafodion.jdbc.t4.TrafT4PreparedStatement.prepare(TrafT4PreparedStatement.java:2209)
at org.trafodion.jdbc.t4.TrafT4Connection.prepareStatement(TrafT4Connection.java:775)
at DirectoriesHelper.main(DirectoriesHelper.java:45)
Am I doing something crazy? Or is there a CQD/param that would help prepare that statement containing 10 000 (?,?,?,?) , so 40 000 parameters in it?
Eric
From: Eric Owhadi
Sent: Tuesday, January 17, 2017 2:47 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: RE: question on using Batch for a select statement with jdbc
Hi Selva
I came across this to and believe that setArray is to support SQL Array type that we don’t support anyway.
Eric
From: Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
Sent: Tuesday, January 17, 2017 2:46 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: Re: question on using Batch for a select statement with jdbc
Hi Eric,
Looking at the JDBC specification, I am guessing preparedStatement.setArray and using Array interface can do the trick. But, setArray is unsupported feature in Trafodion jdbc drivers.
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>>
Sent: Tuesday, January 17, 2017 12:08 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: question on using Batch for a select statement with jdbc
Hi Trafodioneers,
Have following jdbc question:
select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts?
Can I use executeQuery(), and that will do the trick as long as I use addBacth()?
Or it is not possible to use addBatch for this use model?
Thanks in advance for the help,
Eric
RE: question on using Batch for a select statement with jdbc
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Suresh,
Good thought, I actually did exactly what you said:
1000 -> 1.8 sec compile
2000: 6.8 sec
4000: 28.3 sec.
All plan generate tuple list, and not unpack nodes. Suspecting some recursive call, non linear scalable linked with tuple list handling, and associated stack overflow common problems with recursive calls?
Anu found core dumps associated with 10000 runs. And these are happening from trafci or sqlci…
the above query is not generating nested join plan because my table d is empty still. I am hoping it will do NJ as soon as I have some real data and updated stats…
I wish I could force the plan manually as generated by:
select x.a, d.a from (values (?[10000],?[10000],?[10000],?[10000])) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
beautifully generating unpack node …
Eric
From: Suresh Subbiah [mailto:suresh.subbiah60@gmail.com]
Sent: Tuesday, January 17, 2017 7:17 PM
To: user@trafodion.incubator.apache.org
Subject: Re: question on using Batch for a select statement with jdbc
Hi Eric,
I don't there is a generic CQD here. If there was a core file and we analyzed it we might be able to find something specific to this situation. Can we please try with say 1000 values and 4000 parameters?
Thanks
Suresh
On Tue, Jan 17, 2017 at 4:53 PM, Eric Owhadi <er...@esgyn.com>> wrote:
Trying this workaround but hitting a timeout exception at prepare time:
final static String missingVal1 = "select x.a, d.a from (values ";
final static String missingValVar = "(?,?,?,?)";
final static String missingIVal2 = ") as x(a,b,c,d) join d on x.b = d.b and x.c = d.c and x.d = d.d where x.a is not null";
StringBuilder missingVals = new StringBuilder(101000);
missingVals.append(missingVal1);
missingVals.append(missingValVar);
for(int i=1; i< rowsetSize; i++){
missingVals.append(',').append(missingValVar);
}
missingVals.append(missingVal2);
PreparedStatement missingValsstsmt= conn2.prepareStatement(missingVals.toString());
Exception in thread "main" org.trafodion.jdbc.t4.TrafT4Exception: Server aborted abnormally or Connection timed out
at org.trafodion.jdbc.t4.TrafT4Messages.createSQLException(TrafT4Messages.java:284)
at org.trafodion.jdbc.t4.InputOutput.doIO(InputOutput.java:376)
at org.trafodion.jdbc.t4.T4Connection.getReadBuffer(T4Connection.java:157)
at org.trafodion.jdbc.t4.T4Statement.getReadBuffer(T4Statement.java:196)
at org.trafodion.jdbc.t4.T4Statement.Prepare(T4Statement.java:129)
at org.trafodion.jdbc.t4.InterfaceStatement.prepare(InterfaceStatement.java:1126)
at org.trafodion.jdbc.t4.TrafT4PreparedStatement.prepare(TrafT4PreparedStatement.java:2209)
at org.trafodion.jdbc.t4.TrafT4Connection.prepareStatement(TrafT4Connection.java:775)
at DirectoriesHelper.main(DirectoriesHelper.java:45)
Am I doing something crazy? Or is there a CQD/param that would help prepare that statement containing 10 000 (?,?,?,?) , so 40 000 parameters in it?
Eric
From: Eric Owhadi
Sent: Tuesday, January 17, 2017 2:47 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: RE: question on using Batch for a select statement with jdbc
Hi Selva
I came across this to and believe that setArray is to support SQL Array type that we don’t support anyway.
Eric
From: Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
Sent: Tuesday, January 17, 2017 2:46 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: Re: question on using Batch for a select statement with jdbc
Hi Eric,
Looking at the JDBC specification, I am guessing preparedStatement.setArray and using Array interface can do the trick. But, setArray is unsupported feature in Trafodion jdbc drivers.
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>>
Sent: Tuesday, January 17, 2017 12:08 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: question on using Batch for a select statement with jdbc
Hi Trafodioneers,
Have following jdbc question:
select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts?
Can I use executeQuery(), and that will do the trick as long as I use addBacth()?
Or it is not possible to use addBatch for this use model?
Thanks in advance for the help,
Eric
Re: question on using Batch for a select statement with jdbc
Posted by Suresh Subbiah <su...@gmail.com>.
Hi Eric,
I don't there is a generic CQD here. If there was a core file and we
analyzed it we might be able to find something specific to this situation.
Can we please try with say 1000 values and 4000 parameters?
Thanks
Suresh
On Tue, Jan 17, 2017 at 4:53 PM, Eric Owhadi <er...@esgyn.com> wrote:
> Trying this workaround but hitting a timeout exception at prepare time:
>
> final static String missingVal1 = "select x.a, d.a from (values ";
>
> final static String missingValVar = "(?,?,?,?)";
>
> final static String missingIVal2 = ") as x(a,b,c,d) join d on x.b = d.b
> and x.c = d.c and x.d = d.d where x.a is not null";
>
>
>
> StringBuilder missingVals = new StringBuilder(101000);
>
> missingVals.append(missingVal1);
>
> missingVals.append(missingValVar);
>
> for(int i=1; i< rowsetSize; i++){
>
> missingVals.append(',').
> append(missingValVar);
>
> }
>
> missingVals.append(missingVal2);
>
> PreparedStatement missingValsstsmt=
> conn2.prepareStatement(missingVals.toString());
>
>
>
>
>
> Exception in thread "main" org.trafodion.jdbc.t4.TrafT4Exception: Server
> aborted abnormally or Connection timed out
>
> at org.trafodion.jdbc.t4.TrafT4Messages.
> createSQLException(TrafT4Messages.java:284)
>
> at org.trafodion.jdbc.t4.InputOutput.doIO(InputOutput.
> java:376)
>
> at org.trafodion.jdbc.t4.T4Connection.getReadBuffer(
> T4Connection.java:157)
>
> at org.trafodion.jdbc.t4.T4Statement.getReadBuffer(
> T4Statement.java:196)
>
> at org.trafodion.jdbc.t4.T4Statement.Prepare(
> T4Statement.java:129)
>
> at org.trafodion.jdbc.t4.InterfaceStatement.prepare(
> InterfaceStatement.java:1126)
>
> at org.trafodion.jdbc.t4.TrafT4PreparedStatement.prepare(
> TrafT4PreparedStatement.java:2209)
>
> at org.trafodion.jdbc.t4.TrafT4Connection.
> prepareStatement(TrafT4Connection.java:775)
>
> at DirectoriesHelper.main(DirectoriesHelper.java:45)
>
>
>
> Am I doing something crazy? Or is there a CQD/param that would help
> prepare that statement containing 10 000 (?,?,?,?) , so 40 000 parameters
> in it?
>
>
>
> Eric
>
>
>
>
>
>
>
> *From:* Eric Owhadi
> *Sent:* Tuesday, January 17, 2017 2:47 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* RE: question on using Batch for a select statement with jdbc
>
>
>
> Hi Selva
>
> I came across this to and believe that setArray is to support SQL Array
> type that we don’t support anyway.
>
> Eric
>
>
>
> *From:* Selva Govindarajan [mailto:selva.govindarajan@esgyn.com
> <se...@esgyn.com>]
> *Sent:* Tuesday, January 17, 2017 2:46 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* Re: question on using Batch for a select statement with jdbc
>
>
>
> Hi Eric,
>
>
>
> Looking at the JDBC specification, I am guessing
> preparedStatement.setArray and using Array interface can do the trick. But,
> setArray is unsupported feature in Trafodion jdbc drivers.
>
>
>
> Selva
>
>
> ------------------------------
>
> *From:* Eric Owhadi <er...@esgyn.com>
> *Sent:* Tuesday, January 17, 2017 12:08 PM
> *To:* user@trafodion.incubator.apache.org
> *Subject:* question on using Batch for a select statement with jdbc
>
>
>
> Hi Trafodioneers,
>
> Have following jdbc question:
>
>
>
> select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
>
> join directories d on
>
> x.b = d.b and x.c= d.c and x.d = d.d;
>
>
>
> I was thinking of using Batch to fill the list of values, but then I
> struggle with how to invoke the query. executeBatch does not return a
> resultSet, as I guess it is used for upsert or inserts?
>
> Can I use executeQuery(), and that will do the trick as long as I use
> addBacth()?
>
>
>
> Or it is not possible to use addBatch for this use model?
>
> Thanks in advance for the help,
>
> Eric
>
RE: question on using Batch for a select statement with jdbc
Posted by Eric Owhadi <er...@esgyn.com>.
Trying this workaround but hitting a timeout exception at prepare time:
final static String missingVal1 = "select x.a, d.a from (values ";
final static String missingValVar = "(?,?,?,?)";
final static String missingIVal2 = ") as x(a,b,c,d) join d on x.b = d.b and x.c = d.c and x.d = d.d where x.a is not null";
StringBuilder missingVals = new StringBuilder(101000);
missingVals.append(missingVal1);
missingVals.append(missingValVar);
for(int i=1; i< rowsetSize; i++){
missingVals.append(',').append(missingValVar);
}
missingVals.append(missingVal2);
PreparedStatement missingValsstsmt= conn2.prepareStatement(missingVals.toString());
Exception in thread "main" org.trafodion.jdbc.t4.TrafT4Exception: Server aborted abnormally or Connection timed out
at org.trafodion.jdbc.t4.TrafT4Messages.createSQLException(TrafT4Messages.java:284)
at org.trafodion.jdbc.t4.InputOutput.doIO(InputOutput.java:376)
at org.trafodion.jdbc.t4.T4Connection.getReadBuffer(T4Connection.java:157)
at org.trafodion.jdbc.t4.T4Statement.getReadBuffer(T4Statement.java:196)
at org.trafodion.jdbc.t4.T4Statement.Prepare(T4Statement.java:129)
at org.trafodion.jdbc.t4.InterfaceStatement.prepare(InterfaceStatement.java:1126)
at org.trafodion.jdbc.t4.TrafT4PreparedStatement.prepare(TrafT4PreparedStatement.java:2209)
at org.trafodion.jdbc.t4.TrafT4Connection.prepareStatement(TrafT4Connection.java:775)
at DirectoriesHelper.main(DirectoriesHelper.java:45)
Am I doing something crazy? Or is there a CQD/param that would help prepare that statement containing 10 000 (?,?,?,?) , so 40 000 parameters in it?
Eric
From: Eric Owhadi
Sent: Tuesday, January 17, 2017 2:47 PM
To: user@trafodion.incubator.apache.org
Subject: RE: question on using Batch for a select statement with jdbc
Hi Selva
I came across this to and believe that setArray is to support SQL Array type that we don't support anyway.
Eric
From: Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
Sent: Tuesday, January 17, 2017 2:46 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: Re: question on using Batch for a select statement with jdbc
Hi Eric,
Looking at the JDBC specification, I am guessing preparedStatement.setArray and using Array interface can do the trick. But, setArray is unsupported feature in Trafodion jdbc drivers.
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>>
Sent: Tuesday, January 17, 2017 12:08 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: question on using Batch for a select statement with jdbc
Hi Trafodioneers,
Have following jdbc question:
select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts?
Can I use executeQuery(), and that will do the trick as long as I use addBacth()?
Or it is not possible to use addBatch for this use model?
Thanks in advance for the help,
Eric
RE: question on using Batch for a select statement with jdbc
Posted by Eric Owhadi <er...@esgyn.com>.
Hi Selva
I came across this to and believe that setArray is to support SQL Array type that we don't support anyway.
Eric
From: Selva Govindarajan [mailto:selva.govindarajan@esgyn.com]
Sent: Tuesday, January 17, 2017 2:46 PM
To: user@trafodion.incubator.apache.org
Subject: Re: question on using Batch for a select statement with jdbc
Hi Eric,
Looking at the JDBC specification, I am guessing preparedStatement.setArray and using Array interface can do the trick. But, setArray is unsupported feature in Trafodion jdbc drivers.
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>>
Sent: Tuesday, January 17, 2017 12:08 PM
To: user@trafodion.incubator.apache.org<ma...@trafodion.incubator.apache.org>
Subject: question on using Batch for a select statement with jdbc
Hi Trafodioneers,
Have following jdbc question:
select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts?
Can I use executeQuery(), and that will do the trick as long as I use addBacth()?
Or it is not possible to use addBatch for this use model?
Thanks in advance for the help,
Eric
Re: question on using Batch for a select statement with jdbc
Posted by Selva Govindarajan <se...@esgyn.com>.
Hi Eric,
Looking at the JDBC specification, I am guessing preparedStatement.setArray and using Array interface can do the trick. But, setArray is unsupported feature in Trafodion jdbc drivers.
Selva
________________________________
From: Eric Owhadi <er...@esgyn.com>
Sent: Tuesday, January 17, 2017 12:08 PM
To: user@trafodion.incubator.apache.org
Subject: question on using Batch for a select statement with jdbc
Hi Trafodioneers,
Have following jdbc question:
select x.a, d.a from (values (?,?,?,?)) as x(a,b,c,d)
join directories d on
x.b = d.b and x.c= d.c and x.d = d.d;
I was thinking of using Batch to fill the list of values, but then I struggle with how to invoke the query. executeBatch does not return a resultSet, as I guess it is used for upsert or inserts?
Can I use executeQuery(), and that will do the trick as long as I use addBacth()?
Or it is not possible to use addBatch for this use model?
Thanks in advance for the help,
Eric