volatile table is not dropped after hpdci session ends

[Weishiun Tsai]

A volatile table is not dropped after the hpdci session has ended. In the following example, the volatile table persists after several hpdci disconnects and reconnects. This problem is not seen from sqlci, so I am assuming that the problem is in how mxosvr handles volatile tables.

-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>create volatile table abc (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

[Weishiun Tsai (wei-shiun-tsai)]

Verified on 11/6/2013, this problem has been fixed.

-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>create volatile table abc (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

*** ERROR[4082] Object SEABASE.MYTEST.ABC does not exist or is inaccessible. [2013-11-06 17:34:20]

SQL>exit;
-bash-4.1$ hpdci.seascape2-sqtopl7.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

*** ERROR[4082] Object SEABASE.MYTEST.ABC does not exist or is inaccessible. [2013-11-06 17:34:39]

SQL>exit;

[Weishiun Tsai (wei-shiun-tsai)]

Repoened this bug. This problem has reapeared on the 11/11/2013 build, as shown here:

-bash-4.1$ hpdci.sqws138.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>create volatile table abc (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.sqws138.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ hpdci.sqws138.sh

Welcome to HP Database Command Interface 3.0
(c) Copyright 2010-2012 Hewlett-Packard Development Company, LP.

Connected to Data Source: TDM_Default_DataSource

SQL>set schema seabase.mytest;

--- SQL operation complete.

SQL>showddl abc;

CREATE VOLATILE TABLE ABC
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$

[Selvaganesan Govindarajan (selva-ganesan)]

Can you please test this issue with the latest release and confirm if the problem still exists.

[Weishiun Tsai (wei-shiun-tsai)]

Tried it on the GIT 0603_0930 build again. This problem still exists.

-bash-4.1$ trafci.sh -h <blocked out> -u dontcare -p dontcare

Welcome to Trafodion Command Interface
Copyright(C) 2013-2014 Hewlett-Packard Development Company, L.P.

Host Name/IP Address: <blocked out>
User Name: dontcare

Connected to Trafodion

SQL>set schema mytest;

--- SQL operation complete.

SQL>create volatile table myvoltab (a int not null not droppable primary key);

--- SQL operation complete.

SQL>showddl myvoltab;

CREATE VOLATILE TABLE MYVOLTAB
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>exit;
-bash-4.1$ trafci.sh -h <blocked out> -u dontcare -p dontcare

Welcome to Trafodion Command Interface
Copyright(C) 2013-2014 Hewlett-Packard Development Company, L.P.

Host Name/IP Address: <blocked out>
User Name: dontcare

Connected to Trafodion

SQL>set schema mytest;

--- SQL operation complete.

SQL>showddl myvoltab;

CREATE VOLATILE TABLE MYVOLTAB
  (
    A INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
;

--- SQL operation complete.

SQL>

[Sandhya Sundaresan (sandhya-sundaresan)]

This issue is not seen when Trf is running slowly. The symptom is that some volatile tables fail to get cleaned up during session exit. It is difficult to reproduce this. The lolic to drop and cleanup volatile tables is in place . There may be an underlying intermittent error that may prevent this . If a new session is started and then exited, any leftover volatile tables from the previous session will be dropped.
Still trying to reproduce in order to capture the intermittent issue.