<\/p>\n
On day to day activity we often get situation when the database has a lot of space left but query fails with “2644: No more room in database”.<\/p>\n
If any table has severe skewing it can cause the database to run out of space.<\/p>\n
Here we will learn how to identify data causing skewness using hash functions.<\/p>\n
Following query identifies the database space:<\/p>\n
locking table dbc.diskspace for access<\/p>\n
select databasename as \"Database_Name\",\r\nsum(maxperm)\/(1024*1024*1024) as \"Max_Perm (GB)\",\r\nsum(currentperm)\/(1024*1024*1024) as \"Current_Perm (GB)\",\r\n(sum(maxperm) - sum(currentperm))\/(1024*1024*1024) as \"Free_Actual (GB)\",\r\n(((sum(maxperm) - sum(currentperm)) * (100)) \/\r\nCASE\r\nWHEN sum(maxperm) = 0 THEN 1\r\nELSE sum(maxperm)\r\nEND\r\n) AS \"Free_Space %\"\r\nfrom dbc.diskspace\r\nwhere databasename = 'databasename'\r\ngroup by databasename\r\norder by 1;<\/pre>\n<\/p>\n
Example:<\/strong>
\nlocking table dbc.diskspace for access<\/p>\nselect databasename as \"Database_Name\",\r\nsum(maxperm)\/(1024*1024*1024) as \"Max_Perm (GB)\",\r\nsum(currentperm)\/(1024*1024*1024) as \"Current_Perm (GB)\",\r\n(sum(maxperm) - sum(currentperm))\/(1024*1024*1024) as \"Free_Actual (GB)\",\r\n(((sum(maxperm) - sum(currentperm)) * (100)) \/\r\nCASE\r\nWHEN sum(maxperm) = 0 THEN 1\r\nELSE sum(maxperm)\r\nEND\r\n) AS \"Free_Space %\"\r\nfrom dbc.diskspace\r\nwhere databasename = 'testdatabase'\r\ngroup by databasename\r\norder by 1;\r\n\r\nDatabase_Name Max_Perm (GB) Current_Perm (GB) Free_Actual (GB) Free_Space %\r\n------------\u00a0 ------------\u00a0 ------------\u00a0 \u00a0 \u00a0 ------------\u00a0 \u00a0 \u00a0------------\r\ntestdatabase\u00a0 10,837.80\u00a0 \u00a0 \u00a0124.23\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 10,713.58\u00a0 \u00a0 \u00a0 \u00a0 98.85<\/pre>\n<\/p>\n
Following query identifies which Vproc has more perm space than the other Vprocs within a database:<\/p>\n
locking table dbc.diskspace for access<\/p>\n
select vproc,\r\ncurrentperm as \"Current_Perm\"\r\nfrom dbc.diskspace\r\nwhere databasename='databasename'\r\norder by 2 desc 1;<\/pre>\n<\/p>\n
Example:<\/strong>
\nlocking table dbc.diskspace for access<\/p>\nselect vproc,\r\ncurrentperm as \"Current_Perm\"\r\nfrom dbc.diskspace\r\nwhere databasename='testdatabase'\r\norder by 2 desc 1;\r\n\r\nVproc\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Current_Perm\r\n------\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 -------------------\r\n31\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a03,218,931,212.00\r\n2\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 234,423,654.00\r\n45\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0234,128,231.00\r\n5\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 234,213,213.00\r\n7\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 234,754,232.00\r\n<<..>><\/pre>\n<\/p>\n
From the above query output we can determine which Vproc contains skewed data.<\/p>\n
Now we will isolate which table has highest skewing on Vproc 31.<\/p>\n
Following query identifies which table contains skewing on a specific vproc within a database:<\/p>\n
locking table dbc.tablesize for access<\/p>\n
select vproc,\r\ncurrentperm as \"Current_Perm\",\r\ntablename as \"Table_Name\"\r\nfrom dbc.tablesize\r\nwhere databasename='databasename'\r\nand Vproc in (baseline vproc,skewed vproc)\r\norder by 2 desc,3,1;<\/pre>\n<\/p>\n
NOTE:<\/strong> Above query can yield huge data ,you can import it into Excel for easy review of output.<\/p>\n
Example:<\/strong>
\nlocking table dbc.tablesize for access<\/p>\nselect vproc,\r\ncurrentperm as \"Current_Perm\",\r\ntablename as \"Table_Name\"\r\nfrom dbc.tablesize\r\nwhere databasename='testdatabase'\r\nand Vproc in (1,31)\r\norder by 2 desc,3,1\r\n\r\nVproc\u00a0 \u00a0 \u00a0Current_Perm\u00a0 \u00a0 Table_Name\r\n------\u00a0 \u00a0---------------\u00a0 ------------\r\n1\u00a0 \u00a0 \u00a0 \u00a0 \u00a039,792.00\u00a0 \u00a0 \u00a0 \u00a0 testtable2\r\n31\u00a0 \u00a0 \u00a0 \u00a0 23,212.00\u00a0 \u00a0 \u00a0 \u00a0 testtable2\r\n1\u00a0 \u00a0 \u00a0 \u00a0 \u00a02,793.00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0testtable1\r\n31\u00a0 \u00a0 \u00a0 \u00a0 2,911.00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0testtable1\r\n1\u00a0 \u00a0 \u00a0 \u00a0 \u00a0793,791.00\u00a0 \u00a0 \u00a0 \u00a0testtable\r\n31\u00a0 \u00a0 \u00a0 \u00a0 1,654,210,212.00 testtable\r\n1\u00a0 \u00a0 \u00a0 \u00a0 \u00a03,702.00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0testtable3\r\n31\u00a0 \u00a0 \u00a0 \u00a0 1,791.00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0testtable3\r\n<<..>><\/pre>\n<\/p>\n
Now we will find out the hashbucket which contains the skewed data for table.<\/p>\n
The following query identifies which hashbucket on a specifc vproc contains the skewed data for a specific table:<\/p>\n
select distinct(hashrow(primaryindexname))\r\n,count(hashrow(primaryindexname))\r\nfrom databasename.tablename\r\nwhere HASHAMP(HASHBUCKET(HASHROW(fld1)))=vproc number\r\ngroup by 1\r\norder by 2 desc;<\/pre>\n<\/p>\n
NOTE:<\/strong> Use SHOW TABLE to get the Primary Index name for the skewed table.<\/p>\n
Example:<\/strong><\/p>\n
select distinct(hashrow(testpi))\r\n,count(hashrow(testpi))\r\nfrom testdatabase.testtable\r\nwhere HASHAMP(HASHBUCKET(HASHROW(fld1)))=31\r\ngroup by 1\r\norder by 2 desc;\r\n\r\nHASHROW(testpi)\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Count(HASHROW(testpi))\r\n--------------------\u00a0 \u00a0 \u00a0 ------------------------\r\n23D54C43\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 43124378\r\nE3213D6C\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 3482<\/pre>\n<\/p>\n
The following query identifies the actual data that hashes to a specific hashbucket for a specific table.<\/p>\n
select distinct(indexname)\r\nfrom databasename.tablename\r\nwhere hashrow(indexname)='hashbucket id'xb;<\/pre>\n<\/p>\n
Example:<\/strong><\/p>\n
select distinct(testpi)\r\nfrom testdatabase.testtable\r\nwhere hashrow(testpi)='23D54C43'xb;\r\n\r\ntestpi\r\n-------------------\r\n0<\/pre>\n<\/p>\n
NOTE:<\/strong> Above output shows skewing is caused by NULLs in the Primary Index value, however it could be any data value.<\/p>\n
Following query identifies to which AMP “NULLS” hash:<\/p>\n
select HASHAMP(HASHBUCKET(HASHROW(0)));<\/pre>\n\n<\/div>\n