|
Home > Archive > Sybase Database > February 2006 > Out of Memory / There is already an object named "X" in the database.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Out of Memory / There is already an object named "X" in the database.
|
|
| John Smith 2006-02-17, 8:23 pm |
| Error message:
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
Can't allocate space for object 'TMP_TABLE' in database 'tempdb'
because 'default' sment is full/has no free extents. If you ran out of
space in syslogs, dump the transaction log. Otherwise, use ALTER
DATABASE or sp_extendsegment to increase size of the segment.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:78)
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
There is already an object named 'FIXING' in the database.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:93)
For the 1st error of can't allocate space for TMP_TABLE:
The following query SELECT INTO TMP_TABLE
String lsTempQuery4 =
"SELECT XY.M_I, XY.M_B, F.M_AC, F.M_AL, F.M_IL, F.M_FV,
F.DATE, " +
"CASE " +
"WHEN F.M_AC=1 THEN " +
"F.M_IL " +
"ELSE " +
"F.M_AL " +
"END AS COMPARE" +
"INTO tempdb..TMP_TABLE " +
"FROM tempdb..XY XY, tempdb..FIXING F ";
and is added to the batch for the 1st error message as follows:
loStatement.addBatch( lsTempQuery2 );
loStatement.addBatch( lsTempQuery3 );
loStatement.addBatch( lsTempQuery4 );
loStatement.executeBatch();
loStatement.clearBatch();
The 2nd error message:
Code:
Statement loStatement = null;
loStatement = loConnection.createStatement();
loStatement.addBatch( "DROP TABLE tempdb..XY" );
loStatement.addBatch( "DROP TABLE tempdb..FIXING" );
loStatement.addBatch( "DROP TABLE tempdb..TMP_TABLE" );
loStatement.executeBatch();
loStatement.clearBatch();
Since I am trying to DROP TABLE tempdb..FIXING, of course there is an
object named FIXING. :P
Any advice to resolve error 1 and/or error 2 is appreciated.
| |
| Mark A. Parsons 2006-02-17, 8:23 pm |
| For the 'out of space' issue, you have 2 tables in your FROM clause:
tempdb..XY
tempdb..FIXING
but there are no join clauses, so you end up with a cartesian product. If
the 2 tables are even moderatly sized the cartesian product could easily
blow out your tempdb. [Obviously there could be other queries also eating
up tempdb space; whether this one query, by itself, blows out tempdb
obviously depends on the size of tempdb.]
-------------
As for the error about the FIXING table already existing ... you won't get
this error unless you're trying to create the table ('create table',
'select/into').
I don't see anything in your post that suggests the creation of the FIXING
table.
At this point there isn't enough to go on.
Find the section of code that creates the FIXING table, then work back from
that to see if/where the FIXING table should be dropped (or perhaps you
don't need to create the FIXING table? perhaps just truncate it?)
John Smith wrote:
> Error message:
>
> com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
> BatchUpdateException
: Error occurred while executing batch statement:
> Can't allocate space for object 'TMP_TABLE' in database 'tempdb'
> because 'default' sment is full/has no free extents. If you ran out of
> space in syslogs, dump the transaction log. Otherwise, use ALTER
> DATABASE or sp_extendsegment to increase size of the segment.
>
> at
> com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
> at
> com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
> at
> com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
> at prc1.main(prc1.java:78)
> com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
> BatchUpdateException
: Error occurred while executing batch statement:
> There is already an object named 'FIXING' in the database.
>
> at
> com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
> at
> com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
> at
> com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
> at prc1.main(prc1.java:93)
>
>
> For the 1st error of can't allocate space for TMP_TABLE:
>
> The following query SELECT INTO TMP_TABLE
> String lsTempQuery4 =
> "SELECT XY.M_I, XY.M_B, F.M_AC, F.M_AL, F.M_IL, F.M_FV,
> F.DATE, " +
> "CASE " +
> "WHEN F.M_AC=1 THEN " +
> "F.M_IL " +
> "ELSE " +
> "F.M_AL " +
> "END AS COMPARE" +
> "INTO tempdb..TMP_TABLE " +
> "FROM tempdb..XY XY, tempdb..FIXING F ";
>
> and is added to the batch for the 1st error message as follows:
>
> loStatement.addBatch( lsTempQuery2 );
> loStatement.addBatch( lsTempQuery3 );
> loStatement.addBatch( lsTempQuery4 );
> loStatement.executeBatch();
> loStatement.clearBatch();
>
>
> The 2nd error message:
>
> Code:
> Statement loStatement = null;
> loStatement = loConnection.createStatement();
> loStatement.addBatch( "DROP TABLE tempdb..XY" );
> loStatement.addBatch( "DROP TABLE tempdb..FIXING" );
> loStatement.addBatch( "DROP TABLE tempdb..TMP_TABLE" );
> loStatement.executeBatch();
> loStatement.clearBatch();
>
>
> Since I am trying to DROP TABLE tempdb..FIXING, of course there is an
> object named FIXING. :P
>
> Any advice to resolve error 1 and/or error 2 is appreciated.
>
| |
| John Smith 2006-02-18, 3:23 am |
| Mark Parsons wrote:
> Find the section of code that creates the FIXING table, then work back from
> that to see if/where the FIXING table should be dropped (or perhaps you
> don't need to create the FIXING table? perhaps just truncate it?)
code that creates the FIXING table:
String lsTempQuery2 =
"SELECT DISTINCT AC, AL, IL, DATE" +
"INTO tempdb..FIXING " +
"FROM F_DB " +
"WHERE M_FXNG_VALUE <> 0 " +
// "AND DATE= \"2/2/2006 12:00:00.000 AM\" " +
"ORDER BY AC ";
which gives the
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
There is already an object named 'FIXING' in the database.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:79)
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
There is already an object named 'FIXING' in the database.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:94)
I have tried
String lsTempQuery1 =
"DROP TABLE tempdb..TMP_TABLE , tempdb..XY, tempdb..FIXING";
// ....
loStatement.addBatch( lsTempQuery1 );
loStatement.addBatch( lsTempQuery2 );
loStatement.addBatch( lsTempQuery3 );
loStatement.addBatch( lsTempQuery4 );
loStatement.executeBatch();
loStatement.clearBatch();
but my program simply hangs with no output. I don't see how the "DROP
TABLE" line (i.e. lsTempQuery1 ) can cause my program to hang.
question #3)
uncommenting the DATE line gives:
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
Invalid column name '2/2/2006 12:00:00.000 AM'.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:79)
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
Invalid column name '2/2/2006 12:00:00.000 AM'.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:94)
I have verified that DATE is a datetime type field; and that the query
works "alone", but not when embedded in the java program.
| |
| John Smith 2006-02-18, 9:23 am |
| John Smith wrote:
"I have tried
String lsTempQuery1 =
"DROP TABLE tempdb..TMP_TABLE , tempdb..XY, tempdb..FIXING";
// ....
loStatement.addBatch( lsTempQuery1 );
loStatement.addBatch( lsTempQuery2 );
loStatement.addBatch( lsTempQuery3 );
loStatement.addBatch( lsTempQuery4 );
loStatement.executeBatch();
loStatement.clearBatch();
but my program simply hangs with no output. I don't see how the "DROP
TABLE" line (i.e. lsTempQuery1 ) can cause my program to hang. "
I tried:
String lsTempQuery1 =
"DROP TABLE tempdb..FIXING";
and adding lsTempQuery1 to the batch as above.
re-run gives:
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
Cannot drop the table 'tempdb..FIXING', because it doesn't exist in the
system catalogs.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:79)
com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
BatchUpdateException
: Error occurred while executing batch statement:
Cannot drop the table 'tempdb..FIXING', because it doesn't exist in the
system catalogs.
at
com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
at
com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
at
com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
at
com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
at prc1.main(prc1.java:94)
in other words, if i first drop the table tempdb..FIXING, then i get an
error stating that the table FIXING cannot be DROP since FIXING is not
in the system catalogs. If I do *not* DROP TABLE tempdb..FIXING, then
I get an error stating that
" There is already an object named 'FIXING' in the database."
| |
| Mark A. Parsons 2006-02-18, 8:23 pm |
| At this point I'm still not seeing the bigger picture ...
1 - When you get the message about FIXING already existing ... what's in
all 4 of those lsTempQuery? strings? You've only supplied one string.
2 - What is going on in the previous batch, ie, could something from a
previous batch be screwing up things?
3 - When your program hangs, what do you see in the dataserver? Do you see
your connection? Is it blocked? Is it busy performing physical io's? Any
error messages in the errorlog?
4 - When you get the message about FIXING not existing ... does the table
actually exist prior to running that statement?
I'm asking these questions because it appears (to me) that you are posting
snippets of different tests. It's a little tough to figure out what's
going on with just bits-n-pieces, eh ...
-------------------------
How about the following 'simple' tests ...
-------------------------
[Is the environment properly configured?]
If your jdbc/java program requires the jConnect components be loaded into
the dataserver, have your DBA run the latest/greatest jConnect SQL script
into your dataserver.
I occassionally get developers with programs that require a (re)load of the
jConnect components into the dataserver. Once the jConnect components are
loaded their program will then run.
-------------------------
[Separate SQL from jdbc/java ... try to figure out where the problem is
occurring.]
Forget the jdbc/java program and use a simple SQL script that you can
submit to 'isql'.
If you put *all* of your logic in the SQL script ... does it perform as
expected and without errors?
If not, debug.
If so, then there would appear to be a problem with the jdbc/java code, ie,
not a problem with the dataserver.
--------------------------
[Try a simple test involving only the FIXING table.]
1 - create tempdb..FIXING via 'isql'
2 - have your program *ONLY* attempt to drop tempdb..FIXING
Does it drop the table (verify via 'isql') or does it complain that FIXING
does not exist?
If it complains that FIXING does not exist ... are you sure the program is
connecting (at all) to the correct dataserver. (Perhaps have the program
issue a 'select @@servername' or 'select * from master..syslisteners'.)
If it does drop the FIXING table, and without generating any errors, then
the next step is to add new components one-by-one back into your program
and see where it's 'breaking'.
NOTE: If you have MDA tables installed in your dataserver consider
configuring them to use monSysSQLText. When you run your program,
determine it's spid and then query monSysSQLText for SPID = <spid>. The
objective here is to get a complete list of *all* SQL being submitted to
the dataserver by your program. Is the program submitting what you think
it's submitting?
--------------------------
Yeah, I know, these are rather simplistic tests but without a complete
understanding of everything you're doing (remember, I'm limited to exactly
what you post here in the forum) I have to start somewhere. Soooo, I'll
start from the beginning with the KISS principle ;-)
John Smith wrote:
> John Smith wrote:
> "I have tried
>
> String lsTempQuery1 =
> "DROP TABLE tempdb..TMP_TABLE , tempdb..XY, tempdb..FIXING";
>
> // ....
>
> loStatement.addBatch( lsTempQuery1 );
> loStatement.addBatch( lsTempQuery2 );
> loStatement.addBatch( lsTempQuery3 );
> loStatement.addBatch( lsTempQuery4 );
>
> loStatement.executeBatch();
> loStatement.clearBatch();
>
> but my program simply hangs with no output. I don't see how the "DROP
> TABLE" line (i.e. lsTempQuery1 ) can cause my program to hang. "
>
> I tried:
> String lsTempQuery1 =
> "DROP TABLE tempdb..FIXING";
>
> and adding lsTempQuery1 to the batch as above.
>
> re-run gives:
> com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
> BatchUpdateException
: Error occurred while executing batch statement:
> Cannot drop the table 'tempdb..FIXING', because it doesn't exist in the
> system catalogs.
>
> at
> com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
> at
> com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
> at
> com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
> at prc1.main(prc1.java:79)
> com.sybase.jdbc2.jdbc. SybBatchUpdateExcept
ion: JZ0BE:
> BatchUpdateException
: Error occurred while executing batch statement:
> Cannot drop the table 'tempdb..FIXING', because it doesn't exist in the
> system catalogs.
>
> at
> com.sybase.jdbc2.jdbc.ErrorMessage. raiseBatchUpdateExce
ption(ErrorMessage.java:698)
> at
> com.sybase.jdbc2.jdbc.SybStatement. batchLoop(SybStateme
nt.java:1330)
> at
> com.sybase.jdbc2.jdbc.SybStatement. sendBatch(SybStateme
nt.java:1139)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:1099)
> at
> com.sybase.jdbc2.jdbc.SybStatement. executeBatch(SybStat
ement.java:950)
> at prc1.main(prc1.java:94)
>
>
> in other words, if i first drop the table tempdb..FIXING, then i get an
> error stating that the table FIXING cannot be DROP since FIXING is not
> in the system catalogs. If I do *not* DROP TABLE tempdb..FIXING, then
> I get an error stating that
> " There is already an object named 'FIXING' in the database."
>
|
|
|
|
|