x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Calculate the most effecient Storage size based on the catalog

The Following code is used to identify reccomended allocations, whether it be in Cylinders or tracks, and how much to allocate for a tablespace.

 

SELECT           NAME                                                         
              ,  NACTIVE*PGSIZE                             AS KILOBYTES      
              ,  PGSIZE                                     AS PAGE_SIZE      
              ,  NACTIVE                                    AS PAGES          
              ,  DECIMAL((NACTIVE/12.00),15,2)              AS TRACKS         
              ,  DECIMAL(((NACTIVE/12.00)/15),15,2)         AS CYLINDERS      
              ,  CASE                                                         
                   WHEN DECIMAL(((NACTIVE/12.00)/15),15,2) > 1 THEN           
                        'CYLINDERS'                            ELSE           
                        'TRACKS   '                                           
                   END                                      AS RECMNED_ALLOC  
              ,  CASE                                                         
                   WHEN INTEGER(NACTIVE/12.00) <> NACTIVE/12.00 THEN          
                       (INTEGER(NACTIVE/12)+1)*48               ELSE          
                        INTEGER(NACTIVE/12)   *48                             
                   END                                      AS FULL_TRACK_BYTES
              ,  CASE                                                         
                   WHEN INTEGER(((NACTIVE/12.00)/15.00)) <>                   
                                 (NACTIVE/12.00)/15.00          THEN          
                (INTEGER((NACTIVE/12.00)/15.00)+1)*720          ELSE          
                 INTEGER((NACTIVE/12.00)/15.00)   *720                        
                   END                                      AS FULL_CYLDR_BYTES
      FROM       SYSIBM.SYSTABLESPACE                                         
     WHERE       CREATOR = 'BX89ACDA'                                         
       AND       DBNAME = 'SLD000DA'                                          
  ORDER BY 1;                                                                 
             

Print | posted on Friday, January 30, 2004 2:01 PM | Filed Under [ DB2 ]

Feedback

Gravatar

# re: Calculate the most effecient Page size based on the catalog

Is this for SQL Server?
1/31/2004 9:58 AM | Bill
Gravatar

# re: Calculate the most effecient Page size based on the catalog

Was pretty confused till i saw the category .... didnt know you worked on DB2 too
2/2/2004 2:23 AM | Enigma's Weblog
Gravatar

# Page size in DB2

2/28/2004 2:38 AM | Vinod Kumar's Blog
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET