Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    3

    Unanswered: tempdb truncating large temporary tables

    I am the database administrator for a multi-db ASE system, v. 12.5.3. One of the databases uses tempdb to create a large table at run time for use in multi-session queries. Often, the resulting table comes out truncated, that is, with rows missing (bottom rows by sort order) that ought to be there. This is true even when the final query results that make use of the temporary table are themselves quite small.

    I have used the alter database command several times to increase the overall size of tempdb to no avail (it is 60 MB at this point, the problem remains). Upon performing an sp_spaceused on tempdb, I note that the amount given for "reserved" isn't even a fraction of the database size (e.g., 1910 KB), and the "unused" isn't close to "reserved", either (e.g., 1074/1910 KB).

    This leads me to suspect this truncation isn't a mere matter of tempdb not having enough size to create the large temporary table. I believe the problem lies in some other sorts of settings. Does anyone have any idea what they might be? The Sybase tech manuals don't seem to have anything that addresses this issue. Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The most likely problem is that the query that generates the temporary table is run while a "SET ROWCOUNT xxx" is in effect, thus limiting the number of rows that will be inserted to the temp table.

    Michael

  3. #3
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    1 Do not expand tempdb unless you absolutely have to, a small tempdb catches a lot of problems (so that you can fix them).
    2 As long as the code that (a) creates and (b) inserts to the table has error checking, you will get reports of what is wrong (otherwise invest in a crystal ball).
    3 You (the suspect code) is probably having problems identifying which transaction it created the rows in, and then viewed the rows from.
    4 It does not look like a "settings" problem.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  4. #4
    Join Date
    Sep 2006
    Posts
    3
    What can be done about that if there is no explicit set rowcount command in the code?

  5. #5
    Join Date
    Sep 2006
    Posts
    3
    When I run the stored procedure in isql, it does not report any errors, all the rows that should be there are, and the procedure ends with return status = 0. The problem seems to only crop up when the procedure is called in the normal way, which is through a CallableStatement line in a Java appliction.

  6. #6
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Minimum "Error Handling" for SQL

    Weird.
    Does it return status zero when called from Java ? Is this the first time you are executing a sproc from Java, and do not know the ropes ?

    Re error checking, I did not say you will get [automatic] error reports. I said, as long as the code has proper error checking [and reporting], you will get error reports. Otherwise, code that "works" in one set of circumstances may not "work" in another. Of course, on a severe failure (see Severerity Level) , Sybase will report an error and drag you out of the execution, but for most errors, you have to catch them yourself.

    Code:
    DECLARE @err int, @rows int IF (@@trancount != 0) -- or whatever you have consciously prepared for
    BEGIN PRINT "Proc called with a transaction open" GOTO EXIT_ERR END...
    BEGIN TRAN mytran INSERT tempdb..mytable (
    ... columns ... ) SELECT ...
    FROM ... WHERE ...
    SELECT @err = @@error, @rows = @@rowcount IF (@err != 0)
    BEGIN PRINT "INSERT/SELECT mytable failed with error %1!", @err GOTO EXIT_ERR END
    IF (@rows = 0)
    BEGIN PRINT "Zero rows SELECT/INSERTed !!!" GOTO EXIT_ERR END
    ... more code ... et cetera ... COMMIT TRAN mytran RETURN (0)
    EXIT_ERR:
    ROLLBACK TRAN mytran RETURN (1)
    This is the absolute minimum error checking you need (proper error handling would use RAISERROR and require even more structured code). If you do not have this (above code) minimum, then the code is just plain optimistic and does not even know [or report] when it failed, and you will spend a lot of time speculating about the weather this or whether that.

    You can put:
    PRINT "Added %1! rows", @rows
    in the sproc as a debugging statement.

    Cheers
    Last edited by DerekA; 09-28-06 at 13:34. Reason: Touch Up
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •