View Incident Report

Minor

Reported By: Ulrich Doewich
Reported On: 3/30/2015
For: Version 2.18 Build 4

# 4168 REMOVE SERVER SESSION Doesn't Work from within a ElevateDB Server Job

Got a small issue with JOBs. I'm trying to set up a maintenance JOB, which as a last step would optimize the tables. Since OPTIMIZE requires an exclusive lock, I've added some code to close all open sessions.

If I execute the session close code from EDB Manager, it works, as it throws out my client app. However, if I call this procedure from a JOB while my client app is running, I get errors in the database log like this:

Optimize Table, 300: Cannot lock the table log in the schema Default for exclusive access
Execute Statement, 300: Cannot lock the table log in the schema Default for exclusive access
Execute Procedure, 300: An error occurred with the statement at line 17 and column 22 (Cannot lock the table log in the schema Default for exclusive access)
Execute Job, 300: An error occurred with the ProcOptimizeTables routine at line 7 and column 9 (An error occurred with the statement at line 17 and column 22 (Cannot lock the table log in the schema Default for exclusive access))

It seems to ignore/skip the REMOVE SERVER SESSION instruction when executed in the context of a JOB... any ideas?

CREATE PROCEDURE "ProcOptimizeTables" ()
BEGIN
    DECLARE SessionCursor CURSOR FOR SessionLookup;
    DECLARE SessionId INT;

    PREPARE SessionLookup FROM 'SELECT ID FROM 
Configuration.ServerSessions';
    OPEN SessionCursor;
    IF ROWCOUNT(SessionCursor) > 0 THEN
       FETCH FIRST FROM SessionCursor ('ID') INTO SessionId;
       WHILE NOT EOF(SessionCursor) DO
          EXECUTE IMMEDIATE 'REMOVE SERVER SESSION ' + CAST(SessionId AS 
VARCHAR);
          FETCH NEXT FROM SessionCursor ('ID') INTO SessionId;
       END WHILE;
    END IF;
    CLOSE SessionCursor;

    EXECUTE IMMEDIATE 'OPTIMIZE TABLE log';
END
VERSION 1.00

CREATE JOB "Maintenance"
RUN AS "System"
FROM DATE '2015-03-27' TO DATE '2114-09-06'
ONCE
BETWEEN TIME '15:38' AND TIME '16:00'
CATEGORY 'E'
BEGIN
    USE RCS;
    CALL ProcPurgeBackups;
    CALL ProcPurgeServerTasksData;
    CALL ProcPurgeLog;
    CALL ProcOptimizeTables;
    USE;
END
VERSION 1.30

Resolution

Fixed Problem on 4/2/2015 in version 2.19 build 1

Products Affected

ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap