CT dbname.tablename as sel * from dbname1.tablename1 with data;
tablename1 is 70 gb ,and dbname has 110 available space. Query retuned with spool space error, during MERGE step of EXPLAIN. I have checked row distrubution of source table using HASH function query and found that data is well equally distributed among AMPS which hints that the issue is with target database and its existing tables distribution. I need to know ,what other steps/query/scripts are advisable to pinpoint the problem.
Note that "CREATE AS SELECT" is creating the table from a spool file (resulting from the SELECT), not from the source table. Most table attributes are not carried forward; in particular, the system assigns a default Primary Index: first column alone as NUPI.
If you mean to copy both structure and entire content, use the form: CT dbname.tablename as dbname1.tablename1 with data; which will copy many (though not all) attributes.
Otherwise explicitly specify PI: CT dbname.tablename as (sel ... from dbname1.tablename1 ...) with data PRIMARY INDEX (...); If FORMAT, DEFAULT, COMPRESS, etc. attributes are important, you will need to explicitly specify those in the SELECT list, in addition to specifying PI.
Thanks for the info,in fact I used method suggested by you ( CT dbname.tablename as dbname1.tablename1 with data;) at the 1st place. But the issue remains unanswered.Can anyonethrow some light on skewing?
It was answered by Fred: "in particular, the system assigns a default Primary Index: first column alone as NUPI."
Just run that HASHAMP query you used to check the distribution of the source table again using the first column of that table instead of the PI.
But if you mean that it still fails then your target db is skewed, check permspace per AMP using dbc.diskspace. But this is also answered by "bad PI on the first column because of missing PI-definition" :-)
Thanks for your valued suggestion.Here are few sharable points: 1. Source database and target dabase is same, only table name is changing. 2. Database permspace is almost even across all AMPs. 3. As mentioned in my earlier note, source table is 76 GB and the database have around 110 GB free space. Despite that spool space error resulted. On chosing another dataase ,with greater free space, the CT was a success. Any thoughts on such an issue?
select a.vproc, needed, available - needed from ( select vproc, currentperm as needed from dbc.tablesize where databasename = 'newdb' and tablename = 'tablename1' ) as a join ( select vproc, maxperm - currentperm as available from dbc.diskspace where databasename = 'dbname1' ) as b on a.vproc = b.vproc order by 3;