How to divide 5B Rows to avoid "Session logged off"

[AhmedSamir]

Dears,

Am trying to migrate aggregation table with this structure :

Code: Select all

       Subscription_Id INTEGER,
      Call_Start_Month BYTEINT,
      Call_Start_Year SMALLINT,
      Call_start_Hour BYTEINT COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ),
      Rate_Plan_Product_Id INTEGER COMPRESS (1536 ,1540 ,1541 ,1286 ,1545 ,1546 ,1547 ,1345 ,1347 ,1614 ,1665 ,1667 ,1669 ,1177 ,1178 ,1180 ,1442 ,1450 ,1198 ,1725 ,1475 ,1242 ,1498 ,1499 ,1500 ,1513 ,1522 ,1524 ,1270 ,1535 ),
      Usage_Type CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('GPR','MOC','MTC','SMS'),
      Network_Activity_Type_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('1','4','11','10','-1','23','14','10495','10579','10510','25','10499','10580','2','15'),
      Traffic_Case_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('2','0','20'),
      Calling_Location_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('256','-1'),
      Called_Location_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('256','-1','255','259','4125','17231','4294'),
      Roaming_Flag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
      Roaming_Category_Type CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'H',
      Roaming_Location_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('256','-1','185'),
      Revenue_Category_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('6417','54087','5372','7233','1924','54951','54075','8202','54118','8205','91140','22758','6834','2425','20274'),
      Handset_Product_Id INTEGER COMPRESS -1 ,
      Originating_Cell_Id INTEGER COMPRESS ,
      Call_Terminating_Type_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
      Call_Rate_Period_Cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','6','7','9','11','10','12'),
      International_Flag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'N',
      Service_Offerings INTEGER COMPRESS (0 ,33554432 ,570425344 ,33816576 ,570687488 ,34078720 ,34340864 ,34406400 ,34930688 ,35127296 ,42729472 ,34342400 ,33886208 ,34410496 ,35393536 ),
      FAF_Ind INTEGER COMPRESS (0 ,7 ,53 ),
      Selected_Community_Ind INTEGER COMPRESS (0 ,1 ),
      Community_Id1_Charged INTEGER COMPRESS 0 ,
      Community_Id1_Non_Charged INTEGER COMPRESS 0 ,
      No_of_Transactions INTEGER COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ),
      Actual_Duration INTEGER COMPRESS (0 ,11 ,14 ,16 ,19 ,21 ,22 ,24 ,27 ,29 ,30 ,32 ,33 ,35 ,38 ,40 ,41 ,43 ,48 ,49 ,51 ,54 ,56 ,59 ,62 ,64 ,67 ,68 ,70 ,72 ,73 ),
      Rounded_Duration INTEGER COMPRESS (0 ,780 ,540 ,300 ,60 ,840 ,600 ,360 ,120 ,660 ,420 ,180 ,720 ,480 ,240 ),
      Rated_Duration INTEGER COMPRESS (0 ,780 ,540 ,300 ,60 ,840 ,600 ,360 ,120 ,660 ,420 ,180 ,720 ,480 ,240 ),
      Free_Duration INTEGER COMPRESS (0 ,780 ,540 ,300 ,60 ,840 ,600 ,360 ,120 ,660 ,420 ,180 ,720 ,480 ,240 ),
      Data_Volume DECIMAL(18,0) COMPRESS 0. ,
      Call_Charge_Amount DECIMAL(18,3) COMPRESS (0.000 ,0.003 ,0.030 ,0.300 ,0.310 ,0.570 ,0.580 ,0.380 ,0.150 ,0.420 ,0.190 ,0.220 ,1.500 ,1.000 ,0.500 ),
      Partial_Charged_Flag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
      Main_Balance_Delta_Value DECIMAL(9,3) COMPRESS 0.000 ,
      Dedicated_Account_Used_Flag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
      Dedicated_Account_Used VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','2','16','93','5','94','15'),
      Dedicated_Acc_Delta_Value DECIMAL(9,3) COMPRESS 0.000 ,
      Accumulator_Used_Flag CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('N','Y'),
      Accumulator_Used VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','1','5','4','10','6','3'),
      Call_Type_ID INTEGER COMPRESS (0 ,1 ,2 ),
      Usage_Counter_Used VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('0','500','531'))

we use two column(Call_Start_Month - Call_Start_Year ) to specify the period of time we need to transfer, i found 5,456,398,811 rows derring year=2014 ,Month=10 and when i try to transfer this amount on time .. the job fail by this error :

Code: Select all

Source,5: RDBMS code 2594: One of the FastExport session has been logged off  SQL statement: SEL * FROM AGG WHERE Call_Start_Year = 2014 AND Call_Start_Month = 10 (CC_TeraParallelTransporter::getRow, file CC_TeraParallelTransporter.cpp, line 1,642)

any one have a solution to load this amount ?

thank you

[chulett]

Have you looked up the error? Talked to your DBA? Do you have indexes over the columns in your where clause?

Since most people here (myself included) have zero Teradata experience your odd looking table structure or the fact that it is an 'aggregation table' don't really mean much. Hopefully one of the Teradata folks will wander by but you never really mention that RDBMS, had to dig it out of the error message... perhaps I can help with that.

[AhmedSamir]

I found a solution but i need more ideas :

We will build large loop having two parameters "YEAR"-"MONTH" inside it another loop using the two parameters on fixed uning column:

Call_start_Hour BYTEINT COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 )

by using this idea we will transfer hour by hour till the end of month.

any comment .

[AhmedSamir]

Dear chulett,

thank you for reply,

about the index :

Code: Select all

PRIMARY INDEX ( Subscription_Id )
PARTITION BY ( RANGE_N(Call_Start_year  BETWEEN 2008  AND 2020  EACH 1 ),
RANGE_N(Call_Start_Month  BETWEEN 1  AND 12  EACH 1 ) );

yea, it's aggregation table and

[chulett]

Hello? Hello? Operator, we've been cut off!