SEO

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.

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.
>

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.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:79)
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: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.SybBatchUpdateException: 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.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:79)
com.sybase.jdbc2.jdbc.SybBatchUpdateException: 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.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: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 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.SybBatchUpdateException: 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.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:79)
com.sybase.jdbc2.jdbc.SybBatchUpdateException: 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.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: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."

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.SybBatchUpdateException: 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.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:79)
> com.sybase.jdbc2.jdbc.SybBatchUpdateException: 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.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: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."
>