Temp table ignoring few inserts
Hi Team,
I am trying to load data into a global temp table and after inserting when i do a select it ignores 50% of records.Below is my table structure and projection.When i create a projection with Unsegmented it worked for me.
Table Structure and projection:
CREATE TEMPORARY TABLE POC.ABC_GTT
(
VNCID varchar(48) NOT NULL,
VALID_FROM timestamp(0) NOT NULL,
VALID_TO timestamp(0),
PARTNERID varchar(32),
PARTNERNAME varchar(64),
SERVICEPROVIDERNAME varchar(64),
SERVICEINSTANCEID numeric(25,0),
SERVICEGROUPID numeric(25,0),
COMPANYID varchar(32),
VNCNAME varchar(64),
VNCSTATUS varchar(16),
MBC numeric(25,0),
CLIENTID varchar(48),
SUBCLIENTID varchar(48),
RESELLER char(1),
SITEID varchar(16),
DATACENTERNAME varchar(32),
CLIENTNAME varchar(100),
SUBCLIENTNAME varchar(100),
SITENAME varchar(32),
SERVICEPROVIDERID varchar(48),
ROUTINGDOMAINID varchar(48),
ROUTINGDOMAINNAME varchar(64),
VPNID numeric(18,0),
VPNNAME varchar(64),
MCN varchar(20),
PARTNERASN varchar(64),
SITECITY varchar(32),
SITESTATE char(2),
SITEZIPCODE varchar(12),
SITECOUNTRYCODE char(2),
SITEREGIONCODE varchar(10),
MAXMBC numeric(25,0),
EVENT varchar(50),
ACTIVITY_DESC varchar(500),
INSERTED_TS timestamp(0),
LAST_UPDATE_TS timestamp(0)
) NO PROJECTION;
CREATE PROJECTION POC.ABC_GTT_PRJ
(
VNCID,
VALID_FROM,
VALID_TO,
PARTNERID,
PARTNERNAME,
SERVICEPROVIDERNAME,
SERVICEINSTANCEID,
SERVICEGROUPID,
COMPANYID,
VNCNAME,
VNCSTATUS,
MBC,
CLIENTID,
SUBCLIENTID,
RESELLER,
SITEID,
DATACENTERNAME,
CLIENTNAME,
SUBCLIENTNAME,
SITENAME,
SERVICEPROVIDERID,
ROUTINGDOMAINID,
ROUTINGDOMAINNAME,
VPNID,
VPNNAME,
MCN,
PARTNERASN,
SITECITY,
SITESTATE,
SITEZIPCODE,
SITECOUNTRYCODE,
SITEREGIONCODE,
MAXMBC,
EVENT,
ACTIVITY_DESC,
INSERTED_TS,
LAST_UPDATE_TS
)
AS
SELECT VNCID,
VALID_FROM,
VALID_TO,
PARTNERID,
PARTNERNAME,
SERVICEPROVIDERNAME,
SERVICEINSTANCEID,
SERVICEGROUPID,
COMPANYID,
VNCNAME,
VNCSTATUS,
MBC,
CLIENTID,
SUBCLIENTID,
RESELLER,
SITEID,
DATACENTERNAME,
CLIENTNAME,
SUBCLIENTNAME,
SITENAME,
SERVICEPROVIDERID,
ROUTINGDOMAINID,
ROUTINGDOMAINNAME,
VPNID,
VPNNAME,
MCN,
PARTNERASN,
SITECITY,
SITESTATE,
SITEZIPCODE,
SITECOUNTRYCODE,
SITEREGIONCODE,
MAXMBC,
EVENT,
ACTIVITY_DESC,
INSERTED_TS,
LAST_UPDATE_TS
FROM POC.ABC_GTT
ORDER BY VNCID,
VALID_FROM,
VALID_TO
SEGMENTED BY hash(VNCID, VALID_FROM, VALID_TO, PARTNERID, PARTNERNAME, SERVICEPROVIDERNAME, SERVICEINSTANCEID, SERVICEGROUPID, COMPANYID, VNCNAME, VNCSTATUS, MBC, CLIENTID, SUBCLIENTID, RESELLER, SITEID, DATACENTERNAME, CLIENTNAME, SUBCLIENTNAME, SITENAME, SERVICEPROVIDERID, ROUTINGDOMAINID, ROUTINGDOMAINNAME, VPNID, VPNNAME, MCN, PARTNERASN, SITECITY, SITESTATE, SITEZIPCODE, SITECOUNTRYCODE, SITEREGIONCODE) ALL NODES KSAFE 1;
Comments
Can you show us how you are loading the data? Note that the default behavior of temp tables is to remove all table data after each commit.
HI Jim,Any update on this issue.We are facing a simmilar issue with one of our table today and it loads only 50% of data and rest ignores...We are facing this only with Temp Tables and not with Regular tables...
@opper20 - Can you show the steps? Including the CREATE GLOBAL TEMP TABLE statement?