Producing HTML Reports from DB2 Data - the Easy Way

You don't necessarily need special tools to get your DB2 data into shape for viewing from a browser. This article tells you how to produce reports with just some SQL and a little HTML. Use the example our author provides to generate a detailed report of your database configuration.

Share:

Jayesh Thakrar, Senior Architecture Specialist, Siebel Systems, Inc.

Jayesh Thakrar is a Senior Architecture Specialist with Siebel Systems, Inc., specialising in consulting Siebel customers on various topics like architecture, high availability, networking, systems monitoring and performance. He is a certified DB2 UDB and Oracle DBA with 9 years of DBA experience. Jayesh has been a speaker at the Siebel North America User Week Conferences giving presentations on a variety of topics like Performance Optimization and Security.



25 September 2003

Introduction

HTML-based output has become a very common mechanism for feeding data and information to application users, programmers and administrators. The output can be also very conveniently integrated into Web portals. One way to obtain HTML output from IBM DB2® Universal DatabaseTM (UDB) is to use WebSphere® as a front-end. Another mechanism is to produce the output in XML and use stylesheets to map the data onto XML. One good source of information on getting XML output from DB2 is the article "Learn to Use XML with DB2."

This article provides yet an additional option for getting output from DB2 into HTML format. This approach uses some basic HTML knowledge to hand-code the HTML output. The article presents the necessary HTML basics and then uses that to generate the output that can be viewed in a browser.

We'll look at two detailed example scripts. The first script (Listing 3) was used to generate a list of the top 50 tables in a DB2 database and was used to monitor the growth of the database objects in the first phase of a CRM implementation project. The output helped gauge storage requirements so the project technical leads, UNIX® and storage administrators could themselves get a feel of how the tables were growing. It also helped the administrators project the backup window for the database which was essential for batch operation timings.

The second script (Listing 4) is a general purpose database profiling script and collects general information about the DB2 instance and the database. This script can be very easily adapted to fit any application or database and was inspired by the built-in command "db2support" that now comes bundled with DB2.

For the more adventurous DBA, combining such DB2 scripts, Perl and Apache (Web server) can result in a complete Web-based portal that provides database and application information in a format that is easy to browse and navigate.


HTML basics

There are several Web sites, (including www.w3.org) that provide detailed information on HTML, including tutorials and presentations. In this article, we'll cover only the fundamentals required to generate the HTML data.

HTML or HyperText Markup Language is a "markup" language and can be considered a derivative (or to be precise, an application) of SGML - Standard Generalized Markup Language. An HTML document is an ASCII or plain-text document that contains "tags" and data. The document is read or interpreted by an application, usually a browser, and the contents are displayed. Note that modern browsers (such as Internet Explorer or Netscape Navigator) are much more sophisticated applications than just HTML document interpreters. Tags usually occur in pairs - for example <HTML> and </HTML>, although there are many tags that do not occur in pairs (such as <P>). Both the singleton as well as paired tags are commonly referred to simply as tags (whether they need to occur in a pair or not is indicated by the type of tag). When tags occur in a pair, the first tag is referred to as the opening tag and the second tag is referred to as the closing tag. Both the tags have the same text; the difference being the second tag contains a "forward-slash" or "/" before the tag name.

Every HTML document contains at least the following four pairs of tags:
<HTML> and </HTML>
<HEAD> and </HEAD>
<TITLE> and </TITLE>
<BODY> and </BODY>

Here's our first HTML document (Listing 1) making use of the above tags (note that we are making use of a default behavior of browsers to display untagged text as plain text):

Listing 1. First HTML document
<HTML> 
 
<HEAD> 
 
<TITLE> 
Our First HTML Document  
</TITLE> 
 
</HEAD> 
 
<BODY> 
This is our first HTML document. We will use HTML to format 
data from DB2 tables in HTML tabular format.  
</BODY> 
 
</HTML>

Some useful HTML tags are listed in Table 1:

Table 1. Common HTML tags

Common HTML Tags
TagPurpose
<P>This tag occurs as a single tag and is used to represent the start of a new paragraph.
<H1>, <H2>, ....<H6> The <Hn> tags occur in pairs and are used to denote a "heading" in HTML. Headings in HTML occur in larger and/or bolder fonts than the normal text. The text between the opening and closing tags is the heading.
<TABLE>This tag occurs in a pair and is used to define a table in HTML. All the data and tags for the table are enclosed within the opening and closing tags.
<TR>This tag occurs in pairs within the <TABLE> tag pairs and is used to represent a "row" in a table. HTML does not have a tag to represent columns; it only has tags to represent rows and cells within the rows.
<TH>This tag occurs in pairs within the <TR> tag pair and is used to represent a "heading" cell
<TD>This tag occurs in pairs within the <TR> tag pair and is used to represent a "data" cell.

Listing 2 shows the above table in HTML format:

Listing 2. HTML document for table in this article
<HTML> 
 
<HEAD> 
 
<TITLE> 
Table 1: HTML Headings and Table Tags 
</TITLE> 
 
</HEAD> 
 
<BODY> 
 
<TABLE BORDER=1> 
 
<TR> 
<TH> 
Tag 
</TH> 
<TH> 
Comment 
</TH> 
</TR> 
 
<TR> 
<TD> 
<P> 
</TD> 
<TD> 
This tag occurs as a single tag and is used to represent 
the start of a new paragraph. 
</TD> 
</TR> 
 
<TR> 
<TD> 
<H1>, <H2>, ... <H6> 
</TD> 
<TD> 
The <Hn> tags occur in pair and are used to 
denote a "heading" in HTML. Headings in HTML occur in 
larger and/or bolder fonts than the normal text. The text 
between the opening and closing tags is the heading. 
</TD> 
</TR> 
 
<TR> 
<TD> 
<TABLE> 
</TD> 
<TD> 
This tag occurs in pair and is used to define a table in 
HTML. All the data and tags for the table are enclosed 
within the opening and closing tags. 
</TD> 
</TR> 
 
<TR> 
<TD> 
<TR> 
</TD> 
<TD> 
This tag occurs in pair within the <TABLE< tag pairs 
and is used to represent a "row" in a table. HTML does not 
have a tag to represent a column; it only has tags to 
represent a row and a cell within a row. 
</TD> 
</TR> 
 
<TR> 
<TD> 
<TH> 
</TD> 
<TD> 
This tag occurs in pair within the <TR> tag pair 
and is used to represent a "heading" cell. 
</TD> 
</TR> 
 
<TR> 
<TD> 
<TD> 
</TD> 
<TD> 
This tag occurs in pair within the <TR> tag pair 
and is used to represent a "data" cell. 
</TD> 
</TR> 
 
</TABLE> 
 
</BODY> 
 
</HTML>

In Listing 2 we have introduced two new things - attributes and a mechanism to represent the "<" and ">" characters that have special meaning in HTML.

Attributes are means to specify additional meaning, behavior or other functionality related to a tag. For example, in the table above, we are requesting the HTML application (browser) to also display border around the cells in the table. Refer to HTML tutorials and other resources to understand more about attributes for the various tags in HTML.

Since the "<" and ">" have special meanings in HTML, if you want to have those characters to appear literally in your HTML document, you must use special sequences to represent those characters. "<" is represented by "<" and ">" is represented by ">".

This is just a tiny introduction to HTML, but it's enough to get you started with representing your DB2 data as tabular output in your browser. Now let's see how that's done.


How to generate HTML output in DB2

We will use the following SQL statement as our example and work towards producing the output from the SQL statement in a tabular format. As a working example, we'll access data in the DB2 system tables.

SELECT substr(tabname, 1, 25) tabname,   
       substr(tabschema, 1, 25) tabschema,  
       stats_time,  
       card,  
       ((fpages * pagesize) / (1024*1024))table_size_in_MB,  
       t.tbspace,  
       pagesize,  
       extentsize,  
       fpages,  
       npages,  
       overflow,  
       append_mode,  
       volatile   
FROM   syscat.tables t,  
       syscat.tablespaces ts  
WHERE  fpages > 0 AND 
       t.tbspace = ts.tbspace  
ORDER  BY  card DESC 
FETCH  FIRST 50 ROWS ONLY

Having learned the basics, we will go ahead and see how to put together the required HTML. If you examine the HTML document in Listing 2, you will see that it contains the following:

1.	HTML pre-amble (header tags) 
2.	HTML tags for table and table heading 
3.	HTML tags for each data row  
4.	HTML post-amble (closing pair tags for header tags)

The DB2 command script in Listing 3 shows how the output for the SQL can be generated using HTML and the above approach.

Listing 3. Simple DB2 SQL script to generat output in HTML
echo <HTML> ; 
echo <HEAD> ; 
echo <TITLE> ; 
echo 'Top 50 Tables by Cardinality' ; 
echo </TITLE> ; 
echo </HEAD> ; 
echo <BODY> ; 
 
echo <TABLE BORDER=1> ; 
echo <TR> ; 
echo <TH>Tabname</TH> ; 
echo <TH>Tabschema</TH> ; 
echo <TH>Stats_time</TH> ; 
echo <TH>Card</TH> ; 
echo <TH>Table_size_in_MB</TH> ; 
echo <TH>Tbspace</TH> ; 
echo <TH>Pagesize</TH> ; 
echo <TH>extentsize</TH> ; 
echo <TH>Fpages</TH> ; 
echo <TH>Npages</TH> ; 
echo <TH>Overflow</TH> ; 
echo <TH>Append_mode</TH> ; 
echo <TH>Volatile</TH> ; 
echo </TR> ; 
 
SELECT '<TR>', 
       '<TD>', 
       substr(tabname, 1, 25) tabname, 
       '</TD>', 
       '<TD>', 
       substr(tabschema, 1, 25) tabschema, 
       '</TD>', 
       '<TD>', 
       stats_time, 
       '</TD>', 
       '<TD>', 
       card, 
       '</TD>', 
       '<TD>', 
       ((fpages * pagesize)/(1024*1024))table_size_in_MB, 
       '</TD>', 
       '<TD>', 
       t.tbspace, 
       '</TD>', 
       '<TD>', 
       pagesize, 
       '</TD>', 
       '<TD>', 
       extentsize, 
       '</TD>', 
       '<TD>', 
       fpages, 
       '</TD>', 
       '<TD>', 
       npages, 
       '</TD>', 
       '<TD>', 
       overflow, 
       '</TD>', 
       '<TD>', 
       append_mode, 
       '</TD>', 
       '<TD>', 
       volatile, 
       '</TD>', 
       '</TR>' 
FROM   syscat.tables t, 
       syscat.tablespaces ts 
WHERE  fpages > 0 AND 
       t.tbspace = ts.tbspace 
ORDER  BY  card DESC 
FETCH  FIRST 50 ROWS ONLY ; 
 
echo </TABLE>; 
echo </BODY>; 
echo </HTML>;

Note that the actual query has been modified to have the "<TR>" before the first and "</TR>" after the last column to denote the beginning and end of a row, and each data column is surrounded by a "<TD>" and "</TD>" tag pair to denote a data cell. Also, since we want the column headings to appear in a special format (bold), the column headings are also separated.

This db2 command script needs to be used as follows:

db2 -txf listing3.sql -r listing3-output.html

The output of the script for my machine appears as shown Listing3-output.

To produce output for any other SQL query, you only need to replace the sections 2 and 3; that is, the table headings that will of course be different for different queries due to their own unique column headings and the actual query itself.

Another example on generating HTML output is provided in Listing 4. This is a DB2 script that makes use of an in-line HTML style sheet, links and other advanced HTML tricks to generate a very useful report on a database. It was devised to show how the "db2support" command can be mimicked. Take a look at listing-4-output to see the results.

Listing 4. Advanced script example (Part 1 of 6)
----------------------------------------------------------------- 
-- Preamble 
----------------------------------------------------------------- 
 
echo <HTML> ; 
echo <HEAD> ; 
echo <TITLE>DB2 Database Information ; 
echo </TITLE> ; 
echo <STYLE type="text/css"> ; 
 
echo A               {  color: #6666cc;; text-decoration:none;; } ; 
echo A:visited    {  color: #6666cc;;  text-decoration:none;;} ; 
echo A:active     {  text-decoration:none;; } ; 
echo A:hover     {  text-decoration:underline;; } ; 
 
echo  H1    {align: left; color: Black; font-family: Times;  
             font-size: 11pt; background-color: Aqua}  ; 
echo  H2    {align: left; color: Blue;;   font-family: Arial;;    
             font-size: 10pt;; font-weight: bold;; text-decoration: underline} ; 
echo  H3    {align: left; color: Red;;    font-family: Arial;;   
             font-size: 8pt;; font-weight: extra-bold} ; 
echo  BODY  {font-family: Arial;; font-size: 8pt}  ; 
echo  TABLE  {font-family: Arial;; font-size: 8pt}  ; 
echo  pre  {font-family: Arial;; font-size: 8pt}  ; 
 
echo </STYLE> ; 
 
echo </HEAD> ; 
 
echo <body> ; 
 
--------------------- Links to sections --------------------- 
echo <P> <H1 id="TopOfPage"> ; 
echo DB2 Configuration Report</H1> ; 
echo <P> ; 
 
echo <OL> ; 
echo <LI> <A href="#Section1">  DB2 Version and Registry Information </A> ; 
echo <LI> <A href="#Section2">  Database Manager Configuration </A>  ; 
echo <LI> <A href="#Section3">  Database Configuration </A>  ; 
echo <LI> <A href="#Section4">  Database Manager Monitor Switches </A> ; 
echo <LI> <A href="#Section5"> Database Event Monitors </A>  ; 
echo <LI> <A href="#Section6">  Buffer Pool Configuration </A>  ; 
echo <LI> <A href="#Section7">   Tablespace Information </A>  ; 
echo <LI> <A href="#Section8">  Database Object Information </A>  ; 
echo </OL> ; 
 
-------- DB2 Version/Level Information and Registry Information -------- 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
 
echo <H1 id="Section1">DB2 Version and Registry Information</H1> ; 
 
echo <P> <H2>DB2 Version </H2><P> ; 
echo <pre> ; 
!db2level ; 
 
echo <P> <H2>DB2 Registry Variables </H2><P> ; 
!db2set -all ; 
echo </pre> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ; 
 
--------------------- Database Manager Configuration --------------- 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ;  
 
echo <H1 id="Section2">Database Manager Configuration</H1> ; 
 
echo <pre> ; 
get dbm cfg ; 
echo </pre> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ; 
 
-------------------- Database Configuration ------------------- 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
 
echo <H1 id="Section3">Database Configuration</H1> ; 
 
echo <pre> ; 
-- get db cfg for DATBASE_NAME ; 
get db cfg for SAMPLE ; 
echo </pre> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ;
Listing 4. Advanced script example (Part 2 of 6)
----------------- Database Monitor Switches ---------------- 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
 
echo <H1 id="Section4">Database Manager Monitor Switches</H1> ; 
 
echo <pre> ; 
get monitor switches ; 
echo </pre> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ; 
 
------------- Database Event Monitors ------------------ 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
 
echo <H1 id="Section5">Database Event Monitors</H1> ; 
 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>EVENT MONITOR NAME</th> ; 
echo <th>EVENT MONITOR STATE</th> ; 
echo <th>EVENT MONITOR SCOPE</th> ; 
echo <th>EVENT TARGET TYPE</th> ; 
echo <th>EVENT TARGET</th> ; 
echo <th>EVENT IO MODE</th> ; 
echo <th>EVENT AUTOSTART</th> ; 
echo <th>EVENT DEFINER</th> ; 
echo </tr> ; 
 
SELECT  
             '<tr>', 
             '<td>',  evmonname,                   '</td>', 
             '<td>',  EVENT_MON_STATE(evmonname),  '</td>', 
             '<td>',  monscope,                    '</td>', 
             '<td>',  target_type,                 '</td>',  
             '<td>',  target,                      '</td>',  
             '<td>',  io_mode,                     '</td>',  
             '<td>',  autostart,                   '</td>',  
             '<td>',  substr(definer, 1, 20),      '</td>', 
             '</tr>' 
FROM    
            syscat.eventmonitors  
ORDER  BY  
           evmonname ; 
 
echo </table> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ; 
 
------------------- Bufferpool Information ---------------- 
 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
 
echo <H1 id="Section6">Buffer Pool Configuration</H1> ; 
 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>BUFFERPOOL ID</th> ; 
echo <th>BUFFERPOOL NAME</th> ; 
echo <th>PAGE SIZE</th> ; 
echo <th>NUMBER OF PAGES</th> ; 
echo <th>EXTENDED STORAGE CONFIGURATION</th> ; 
echo </tr> ; 
 
SELECT 
             '<tr>',  
             '<td>',  bufferpoolid,  '</td>', 
             '<td>',  bpname,        '</td>', 
             '<td>',  pagesize,      '</td>', 
             '<td>',  npages,        '</td>', 
             '<td>',  estore,        '</td>'  
FROM    
             syscat.bufferpools  
ORDER BY  
             bufferpoolid ; 
 
echo </table>  ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ;
Listing 4. Advanced script example (Part 3 of 6)
------------------ Tablespace Information -------------------- 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
 
echo <H1 id="Section7">Tablespace Information</H1> ; 
 
echo <P> <H2>Tablespace Page Size and Buffer pool Information </H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>TABLESPACE ID</th> ; 
echo <th>TABLESPACE NAME</th> ; 
echo <th>BUFFERPOOL NAME</th> ; 
echo <th>PAGE SIZE</th> ; 
echo <th>TABLESPACE DATA TYPE</th> ; 
echo <th>TABLESPACE EXTENT SIZE</th> ; 
echo <th>PREFETCH SIZE (no. of extents) </th> ; 
echo <th>TABLESPACE I/O OVERHEAD</th> ; 
echo <th>TABLESPACE I/O TRANSFER-RATE</th> ; 
echo </tr> ; 
 
 SELECT  
            '<tr>', 
            '<td>',  tbspaceid,      '</td>',    
            '<td>',  tbspace,        '</td>', 
            '<td>',  bpname,         '</td>', 
            '<td>',  t.pagesize,     '</td>', 
            '<td>',  datatype,       '</td>', 
            '<td>',  extentsize,     '</td>', 
            '<td>',  prefetchsize,   '</td>', 
            '<td>',  overhead,       '</td>', 
            '<td>',  transferrate,   '</td>', 
            '</tr>' 
FROM           
            syscat.tablespaces t  
FULL OUTER JOIN   
            syscat.bufferpools b 
ON                 
            t.bufferpoolid = b.bufferpoolid  
ORDER BY   
            tbspaceid ; 
 
echo </table>  ; 
 
 
-- Tablespace Containers 
 
echo <P> <H2>Tablespace Containers </H2><P> ; 
 
echo <pre> ; 
list tablespaces show detail ; 
echo </pre> ; 
 
echo <B><U> Containers for tablespace 0</B></U> ; 
echo <pre> ; 
list tablespace containers for 0 ; 
echo </pre> ; 
 
echo <B><U> Containers for tablespace 1</B></U> ; 
echo <pre> ; 
list tablespace containers for 1 ; 
echo </pre> ; 
 
echo <B><U> Containers for tablespace 2</B></U> ; 
echo <pre> ; 
list tablespace containers for 2 ; 
echo </pre> ; 
 
 
echo </pre> ; 
echo <br> <br> <br> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ;
Listing 4. Advanced script example (Part 4 of 6)
----------------- Database Object Information ----------------- 
echo <P> ; 
 
echo <HR SIZE=3 WIDTH="100%"> ; 
  
echo <H1 id="Section8">Database Object Information</H1> ; 
 
-- Tables by Schema 
 
echo <P> <H2>Table Count by Table Schema</H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>TABLE SCHEMA</th> ; 
echo <th>TABLE TYPE</th> ; 
echo <th>NUMBER OF TABLES</th> ; 
echo </tr> ; 
 
SELECT 
            '<tr>', 
            '<td>',  tabschema,              '</td>',  
            '<td>',  type,                   '</td>', 
            '<td>',  count(1) no_of_tables,  '</td>', 
            '</tr>'   
FROM    
            syscat.tables  
GROUP BY  
            tabschema, type 
ORDER BY 
             tabschema, type ; 
 
echo </table> <br> <br> ; 
 
-- Indexes by Index Schema  
 
echo <P> <H2>Index Count by Index Schema</H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>INDEX SCHEMA</th> ; 
echo <th>NUMBER OF INDEXES</th> ; 
echo </tr> ; 
 
SELECT 
            '<tr>', 
            '<td>',  indschema,               '</td>',  
            '<td>',  count(1) no_of_indexes,  '</td>', 
            '</tr>'   
FROM    
            syscat.indexes 
GROUP BY  
            indschema 
ORDER BY 
             indschema ; 
 
echo </table> <br> <br> ; 
 
-- Indexes by Table Schema  
 
echo <P> <H2>Index Count by Table Schema</H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>TABLE SCHEMA</th> ; 
echo <th>NUMBER OF INDEXES</th> ; 
echo </tr> ; 
 
SELECT 
            '<tr>', 
            '<td>',  tabschema,               '</td>',  
            '<td>',  count(1) no_of_indexes,  '</td>', 
            '</tr>'   
FROM    
            syscat.indexes 
GROUP BY  
            tabschema 
ORDER BY 
             tabschema ; 
 
echo </table> <br> <br> ;
Listing 4. Advanced script example (Part 5 of 6)
-- Basic Table Statistics by Tablespace  
 
echo <P> <H2>Basic Table Statistics</H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>TABLESPACE NAME</th> ; 
echo <th>NUMBER OF TABLES</th> ; 
echo <th>LATEST STATS TIME</th> ; 
echo <th>OLDEST STATS TIME</th> ; 
echo <th>TOTAL NPAGES IN TABLESPACE</th> ; 
echo <th>TOTAL FPAGES IN TABLESPACE</th> ; 
echo <th>LARGEST NPAGES FOR SINGLE TABLE</th> ; 
echo <th>LARGEST FPAGES FOR SINGLE TABLE</th> ; 
echo <th>LARGEST CARDINALITY FOR SINGLE TABLE</th> ; 
echo </tr> ; 
 
SELECT 
             '<tr>',  
             '<td>',  tbspace tablespace,                  '</td>', 
             '<td>',  count(1) no_of_tables,               '</td>', 
             '<td>',  max(stats_time) latest_stats_time,   '</td>', 
             '<td>',  min(stats_time) oldest_stats_time,   '</td>', 
             '<td>',  sum(npages) total_npages,            '</td>',  
             '<td>',  sum(fpages) total_fpages,            '</td>', 
             '<td>',  max(npages) largest_npages,          '</td>', 
             '<td>',  max(fpages) largest_fpages,          '</td>', 
             '<td>',  max(card)   largest_card,            '</td>'                
FROM    
             syscat.tables 
WHERE   
              type = 'T' 
GROUP BY  
             tbspace 
ORDER BY  
             tbspace ; 
 
echo </table> <br> <br> ; 
  
-- Basic Index Statistics by Tablespace  
 
echo <P> <H2>Basic Index Statistics</H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>TABLESPACE NAME</th> ; 
echo <th>NUMBER OF INDEXES</th> ; 
echo <th>LARGEST NLEAF (no. of leaf nodes)</th> ; 
echo <th>LARGEST INDEX LEVELS (height of index b-tree)</th> ; 
echo <th>LARGEST FIRSTKEYCARD</th> ; 
echo <th>LARGEST FULLKEYCARD</th> ; 
echo </tr> ; 
 
SELECT  
             '<tr>', 
              '<td>',  index_tbspace,                          '</td>', 
              '<td>',  count(1) no_of_indexes,                 '</td>', 
              '<td>',  max(nleaf) largest_nleaf,               '</td>', 
              '<td>',  max(nlevels) largest_index_nlevels,     '</td>', 
              '<td>',  max(firstkeycard) largest_firstkeycard, '</td>', 
              '<td>',  max(fullkeycard)  largest_fullkeycard,  '</td>',   
              '</tr>' 
FROM   (SELECT CASE WHEN t.index_tbspace 
                    IS   null 
                    THEN t.tbspace 
                    ELSE t.index_tbspace 
               END index_tbspace, 
               indname, 
               i.nleaf, 
               i.nlevels, 
               i.firstkeycard, 
               i.fullkeycard 
        FROM   syscat.indexes i, 
               syscat.tables t 
        WHERE  i.tabschema = t.tabschema AND 
               i.tabname   = t.tabname  
        ) index_list 
GROUP  BY index_tbspace 
ORDER  BY index_tbspace ; 
 
echo </table> <br> <br> ;
Listing 4. Advanced script example (Part 6 of 6)
-- Tables in Long Tablespaces  
 
echo <P> <H2>Table in Long Tablespaces</H2><P> ; 
echo <table border="1" width="90%"> ; 
echo <tr> ; 
echo <th>LONG TABLESPACE NAME</th> ; 
echo <th>NUMBER OF TABLES</th> ; 
echo </tr> ; 
 
SELECT  
             '<tr>', 
             '<td>',  long_tbspace,             '</td>', 
             '<td>',  count(1) count_of_tables, '</td>'  
FROM 
             syscat.tables  
WHERE   
             long_tbspace is NOT null  
GROUP BY 
             long_tbspace  
ORDER BY  
             long_tbspace ; 
 
echo </table> <br> <br> ; 
 
echo <P> ; 
echo <A href="#TopOfPage">Back to the Top of Report</A> ; 
 
------------------ Postamble ---------------- 
echo </BODY> ; 
echo </HTML> ; 
 
</body>

Conclusion

In this article, we looked at some basics of HTML and how to generate HTML output from DB2 by making some simple modifications to SQL statements. Such output makes it easy reading for both technical and non-technical audiences. Furthermore, it can be easily integrated into Web portals, sent as e-mail with in-line HTML, and so on. Users with more advanced needs can use the same technique to generate XML and DHTML output and integrate with stylesheets.

Resources

  • Learn more about DB2 at the developerWorks DB2 zone. You'll find technical documentation, how-to articles, education, downloads, product information, and more.
  • Get involved in the developerWorks community by participating in developerWorks blogs.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13200
ArticleTitle=Producing HTML Reports from DB2 Data - the Easy Way
publish-date=09252003