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.SybBatchUpdateException: 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.raiseBatchUpdat eException(ErrorMessage.java:698)
> at
> com.sybase.jdbc2.jdbc.SybStatement.batchLoop(SybSt atement.java:1330)
> at
> com.sybase.jdbc2.jdbc.SybStatement.sendBatch(SybSt atement.java:1139)
> at
> com.sybase.jdbc2.jdbc.SybStatement.executeBatch(Sy bStatement.java:1099)
> at
> com.sybase.jdbc2.jdbc.SybStatement.executeBatch(Sy bStatement.java:950)
> at prc1.main(prc1.java:78)
> com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE:
> BatchUpdateException: Error occurred while executing batch statement:
> There is already an object named 'FIXING' in the database.
>
> at
> com.sybase.jdbc2.jdbc.ErrorMessage.raiseBatchUpdat eException(ErrorMessage.java:698)
> at
> com.sybase.jdbc2.jdbc.SybStatement.batchLoop(SybSt atement.java:1330)
> at
> com.sybase.jdbc2.jdbc.SybStatement.sendBatch(SybSt atement.java:1139)
> at
> com.sybase.jdbc2.jdbc.SybStatement.executeBatch(Sy bStatement.java:1099)
> at
> com.sybase.jdbc2.jdbc.SybStatement.executeBatch(Sy bStatement.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.
>