In ButtonClick Javascript alert.

 <asp:Button ID="btnSubmit" runat="server" Text="Submit" Width="94px"
                                            onclick="btnSubmit_Click" OnClientClick="alert(Page_IsValid);" ValidationGroup="BL" />

DropDownList Fill

<tr>
                        <td colspan="2" style="height: 32px" align="left">
                            Select Branch&nbsp;
                            <asp:DropDownList ID="ddlBranches" runat="server" AppendDataBoundItems="True"
                                AutoPostBack="True" CausesValidation="True">
                                <asp:ListItem Value="-1">---Select---</asp:ListItem>
                            </asp:DropDownList>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
                                ControlToValidate="ddlBranches" CssClass="red_text" Display="Dynamic"
                                ErrorMessage="Please enter Branches" InitialValue="-1" SetFocusOnError="True"
                                ValidationGroup="Eventsp"></asp:RequiredFieldValidator>
                        </td>
                    </tr>



 string str = "select id,branchname from branches";
                DataTable dt = new DataTable();
                dt = db_function.getDataTable(str);

                ddlBranches.DataSource = dt;
                ddlBranches.DataTextField = "branchname";
                ddlBranches.DataValueField = "id";

                ddlBranches.DataBind();

public static DataTable getDataTable(string SQL)
    {
        if (sqlcon == null || sqlcon.State == ConnectionState.Closed)
        {
            db_function.opensqlconnection();
        }
        DataTable dt = new DataTable("ABC");
        using (sqlcom = sqlcon.CreateCommand())
        {
            sqlcom.CommandType = CommandType.Text;
            sqlcom.CommandText = SQL;
            sqlcom.CommandTimeout = 0;
            using (IDataReader reader = sqlcom.ExecuteReader())
            {  dt.Load(reader);
                if (reader != null) reader.Close();
            }
        }
        return dt;
    }

public static DbConnection sqlcon;
    public static IDbCommand sqlcom;

Character Counter for text boox javascript in .cs file

<tr>
<td align="left" class="heading_text1" style="width: 1057px">
<asp:TextBox ID="txtBiography" runat="server" Height="218px" TextMode="MultiLine"
Width="518px" MaxLength="3000"></asp:TextBox>
</td>
</tr>
<tr>
<td align="left" class="heading_text1" style="width: 1057px">
<asp:TextBox ID="txtCount" runat="server"
BorderStyle="None" ReadOnly="True">0</asp:TextBox>

</td>
</tr>


protected void Page_Load(object sender, EventArgs e)

{
  txtCount.Text = txtBiography.MaxLength.ToString();
        txtBiography.Attributes.Add("onKeyDown",
           "if(document.getElementById ('" + txtBiography.ClientID + "').value.length<3001){document.getElementById ('" + txtCount.ClientID + "').value = 3000 - document.getElementById('" + txtBiography.ClientID + "').value.length; } else if(event.keyCode==08){ return true;} else {alert('Please limit your entry to 3000 characters or less.'); return false;}");
        txtBiography.Attributes.Add("onKeyUp",
           "if(document.getElementById ('" + txtBiography.ClientID + "').value.length<3001){document.getElementById ('" + txtCount.ClientID + "').value = 3000 - document.getElementById('" + txtBiography.ClientID + "').value.length; } else if(event.keyCode==08){ return true;} else {alert('Please limit your entry to 3000 characters or less.'); return false;}");


}

RequiredFieldValidator

<asp:DropDownList ID="selStates" runat="server" Width="250px" AppendDataBoundItems="True" AutoPostBack="true" CausesValidation="true" ValidationGroup="BranchSp">
<asp:ListItem Value="-1">----Select-----</asp:ListItem>
</asp:DropDownList>




<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="selStates" CssClass="red_text"
  ErrorMessage="Please select state" InitialValue="
-1" SetFocusOnError="True"  Text="Please select state" ValidationGroup="BranchSp"></asp:RequiredFieldValidator>

Table rowspan and colspan

<TABLE BORDER=2 CELLPADDING=4>
<TR>
    <TH ROWSPAN=3 BGCOLOR="#99CCFF">Production</TH>
    <TD>Raha Mutisya</TD> <TD>1493</TD>
    </TR>
<TR>
    <TD>Shalom Buraka</TD> <TD>3829</TD> 
    </TR>
<TR>
    <TD>Brandy Davis</TD> <TD>0283</TD>
    </TR>
<TR>
    <TH ROWSPAN=3 BGCOLOR="#99CCFF">Sales</TH>
    <TD>Claire Horne</TD> <TD>4827</TD>
    </TR>
<TR>
    <TD>Bruce Eckel</TD> <TD>7246</TD>
    </TR>
<TR>
    <TD>Danny Zeman</TD> <TD>5689</TD>
    </TR>
</TABLE>



which creates

Production Raha Mutisya 1493
Shalom Buraka 3829
Brandy Davis 0283
Sales Claire Horne 4827
Bruce Eckel 7246
Danny Zeman 5689
 
 
 
 
 
 
 
<TABLE BORDER=2 CELLPADDING=4>
<TR> <TH COLSPAN=2 BGCOLOR="#99CCFF">Production</TH> </TR>
<TR> <TD>Raha Mutisya</TD>      <TD>1493</TD> </TR>
<TR> <TD>Shalom Buraka</TD>     <TD>3829</TD> </TR>
<TR> <TD>Brandy Davis</TD>      <TD>0283</TD> </TR>
<TR> <TH COLSPAN=2 BGCOLOR="#99CCFF">Sales</TH> </TR>
<TR> <TD>Claire Horne</TD>      <TD>4827</TD> </TR>
<TR> <TD>Bruce Eckel</TD>       <TD>7246</TD> </TR>
<TR> <TD>Danny Zeman</TD>       <TD>5689</TD> </TR>
</TABLE>



which gives this table:

Production
Raha Mutisya 1493
Shalom Buraka 3829
Brandy Davis 0283
Sales
Claire Horne 4827
Bruce Eckel 7246
Danny Zeman 5689
 
 
<TABLE BORDER=2 CELLPADDING=4>
<TR> <TH COLSPAN=2>Production</TH> </TR>
<TR> <TD>Raha Mutisya</TD>      <TD>1493</TD> </TR>
<TR> <TD>Shalom Buraka</TD>     <TD>3829</TD> </TR>
<TR> <TD>Brandy Davis</TD>      <TD>0283</TD> </TR>
<TR> <TH COLSPAN=2>Sales</TH> </TR>
<TR> <TD>Claire Horne</TD>      <TD>4827</TD> </TR>
<TR> <TD>Bruce Eckel</TD>       <TD>7246</TD> </TR>
<TR> <TD>Danny Zeman</TD>       <TD>5689</TD> </TR>
</TABLE>
which gives us:


Production
Raha Mutisya 1493
Shalom Buraka 3829
Brandy Davis 0283
Sales
Claire Horne 4827
Bruce Eckel 7246
Danny Zeman 5689

Modeldelpopup CSS Class

 .modalBackground
        {
            background-color: White;
            filter: alpha(opacity=40);
            opacity: 0.5;
        }
        .ModalWindow
        {
            border: solid 1px White;
            background: Gray;
            padding: 0px 10px 10px 10px;
            position: absolute;
            top: 180px;
            left: 22px;
            height: 206px;
            width: 439px;
        }


 <cc1:ModalPopupExtender ID="MPE1" runat="server" BackgroundCssClass="modalBackground"
                            TargetControlID="LnkBranches" PopupControlID="Panel1" DropShadow="true">
                        </cc1:ModalPopupExtender>

Reapeter

 <asp:Repeater ID="rptBranch" runat="server">                  
                  <ItemTemplate>
                     <div class="col2">
                          <div class="left">
                              <asp:Image ID="Imgmain" Width="115" Height="115" CommandArgument='<%#Eval("id")%>'  ImageUrl='<%#ConfigurationManager.AppSettings["imagePathProd"].ToString()+ConfigurationManager.AppSettings["imageView"].ToString()+Eval("Thumbnail_name")%>' runat="server" />  </div>
                          <div class="right">
                              <p class="width">
                                  <strong><asp:LinkButton ID="lbtnBranchTitle" runat="server" Text='<%#Eval("BranchName")+"("+Eval("city")+", "+Eval("state")+")"%>' CommandArgument='<%#Eval("id")%>' OnClick="lbtnBranchTitle_Click"></asp:LinkButton></strong>
                                  <asp:Label ID="lblBranchBackGround" runat="server" Text='<%#Eval("BackGround") %>'></asp:Label>
                                  </p>
                              <p>
                                 <asp:LinkButton ID="lbtnMoreBranchDetail" runat="server" CommandArgument='<%#Eval("id")%>' CssClass="more-link" OnClick="lbtnMoreBranchDetail_Click" ></asp:LinkButton>
                              </p>
                          </div>
                      </div>
                  </ItemTemplate>
              </asp:Repeater>   



  DataTable odtBranches = new DataTable();

rptBranch.DataSource = odtBranches;
                rptBranch.DataBind();
           

Style Sheets

<link href="myFirstStyleSheet.css" rel="stylesheet"  type="text/css">
 
The above line of code links your external style sheet called ‘myFirstStyleSheet.css
to the HTML document. You place this code in between the <head> </head>  tags in your web page. 

.my3rdNewStyle {
   font-family: Verdana, Arial, Helvetica, sans-serif;
   font-weight: bold;
   font-size: 12pt;
   color: #FF0000;
}
 In the above example I have created a series CSS classes  that can be applied to any HTML tag like so: 
<h2 class=”my3rdNewStyle”>My CSS styled  text</h2>
  
When you apply a CSS class to it, the CSS code overrides the default size that you would normally get with an <h2> tag in favor of the size specified in the CSS class. So now you can see that CSS can override default HTML tag behavior!
In the above examples, I have CSS code where I define my CSS classes and then ‘apply’ them to various elements in the page. Another way to apply CSS is to globally redefine an HTML tag to look a certain way:

h1 { font-family: Garamond, "Times New Roman",  serif; font-size: 200%; }
 What this CSS code does is set the font style and size of all <h1> tags in one shot. Now you don’t have to apply a CSS class as we did before to any <h1> tags since they are automatically all affected by the CSS style rules.

Here is another example of where I give the whole page bigger margins:
body { margin-left: 15%; margin-right: 15%; }
As you can see, you can redefine any tag and change the way it looks! This can be very powerful:
div {
   background: rgb(204,204,255);
   padding: 0.5em;
   border: 1px solid #000000;
}
The above CSS code sets that any <div></div> tag will now have a background color of ‘rgb(204,204,255)’ and have a padding of 0.5em and a thin 1 pixel border that is solid black.

LINK

In this last example I will show you the ‘super cool’ CSS code that allows you to create link roll-over affects without images:
a:link { color: rgb(0, 0, 153) }
a:visited { color: rgb(153, 0, 153) }
a:hover { color: rgb(0, 96, 255) }
a:active { color: rgb(255, 0, 102) }
The above CSS will cause your links to change color when someone hovers their mouse pointer over it, instant rollovers with no images! One important note with the above code, is that it is important that the style declarations be in the right order:
“link-visited-hover-active”,
 

Global.asax file

<%@ Application Language="C#" %>

<script runat="server">

    void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        // Code that runs on application startup

        Application["OnlineUsers"] = 0;

    }
   
    void Application_End(object sender, EventArgs e)
    {
        //  Code that runs on application shutdown

    }
       
    void Application_Error(object sender, EventArgs e)
    {
        // Code that runs when an unhandled error occurs
        Context.ClearError();

    }

    void Session_Start(object sender, EventArgs e)
    {
          
   
        //Session["web_root"] = "http://www.naacphistory.org/";
        //Session["web_https_root"] = "http://www.naacphistory.org/";



        //Session["web_root"] = "http://chsridhar/NewNAACP/";
        //Session["web_https_root"] = "http://chsridhar/NewNAACP/";

        Session["web_root"] = "http://113.193.180.122/NewNAACP/";
        Session["web_https_root"] = "http://113.193.180.122/NewNAACP/";

       
        //Session["web_root"] = "http://naacp.rfibox.net/";
       // Session["web_https_root"] = "http://naacp.rfibox.net/";
        // http://localhost:1590/NAACP_CMS
       // Session["web_root"] = "http://10.0.0.152/naacp/";
      //Session["web_https_root"] = "http://10.0.0.152/naacp/";

        // Code that runs when a new session is started

        Application.Lock();

        Application["OnlineUsers"] = (int)Application["OnlineUsers"] + 1;

        Application.UnLock();
    }

    void Session_End(object sender, EventArgs e)
    {
        Application.Lock();

        Application["OnlineUsers"] = (int)Application["OnlineUsers"] - 1;

        Application.UnLock();
       
    }
      
</script>



----------------------------------------------


Imports System.Security
Imports System.Security.Principal
Imports System.Web.Security
Imports System.Threading
Imports System.Globalization
Imports System.Web.Caching
Imports System.Web.Mail
Imports System.Configuration

Namespace FileOn

    Public Class [Global]
        Inherits System.Web.HttpApplication

        Dim mnSessionMinutes As Integer = 15
        Dim msSessionCacheName As String = "SessionTimeOut"

        '*********************************************************************
        '
        ' Application_BeginRequest Event
        '
        ' The Application_BeginRequest method is an ASP.NET event that executes
        ' on each web request into the portal application.  The below method
        ' obtains the current tabIndex and TabId from the querystring of the
        ' request -- and then obtains the configuration necessary to process
        ' and render the request.
        '
        ' This portal configuration is stored within the application's "Context"
        ' object -- which is available to all pages, controls and components
        ' during the processing of a single request.
        '
        '*********************************************************************

        Sub Application_BeginRequest(ByVal sender As Object, ByVal e As EventArgs)
          
            Dim tabIndex As Integer = 0
            Dim tabId As Integer = 1

            ' Get TabIndex from querystring
            If Not (Request.Params("tabindex") Is Nothing) Then
                tabIndex = CInt(Request.Params("tabindex"))
            End If

            ' Get TabID from querystring
            If Not (Request.Params("tabid") Is Nothing) Then
                tabId = CInt(Request.Params("tabid"))
            End If

            Context.Items.Add("PortalSettings", New PortalSettings(tabIndex, tabId))
         End Sub

        '*********************************************************************
        '
        ' Application_AuthenticateRequest Event
        '
        ' If the client is authenticated with the application, then determine
        ' which security roles he/she belongs to and replace the "User" intrinsic
        ' with a custom IPrincipal security object that permits "User.IsInRole"
        ' role checks within the application
        '
        ' Roles are cached in the browser in an in-memory encrypted cookie.  If the
        ' cookie doesn't exist yet for this session, create it.
        '
        '*********************************************************************

        Sub Application_AuthenticateRequest(ByVal sender As Object, ByVal e As EventArgs)
          ' Attempt to Validate User Credentials using UsersDBSql
            Dim sUserDBMode As String = ConfigurationSettings.AppSettings("Authentication")

            If Request.IsAuthenticated = True Then
                Dim roles() As String

                ' Create the roles cookie if it doesn't exist yet for this session.
                If Request.Cookies("portalroles") Is Nothing Then

                    ' Get roles from UserRoles table, and add to cookie
                    Dim _user As New UsersDBSql
                    roles = _user.GetUserRoles(User.Identity.Name)

                    ' Create a string to persist the roles
                    Dim roleStr As String = ""
                    Dim role As String

                    For Each role In roles
                        roleStr += role
                        roleStr += ";"
                    Next role

                    ' Create a cookie authentication ticket.
                    '   version
                    '   user name
                    '   issue time
                    '   expires every hour
                    '   don't persist cookie
                    '   roles
                    Dim ticket As New FormsAuthenticationTicket(1, _
                        Context.User.Identity.Name, _
                        DateTime.Now, _
                        DateTime.Now.AddHours(1), _
                        False, _
                        roleStr)

                    ' Encrypt the ticket
                    Dim cookieStr As String = FormsAuthentication.Encrypt(ticket)

                    ' Send the cookie to the client
                    Response.Cookies("portalroles").Value = cookieStr
                    Response.Cookies("portalroles").Path = "/"
                    Response.Cookies("portalroles").Expires = DateTime.Now.AddMinutes(1)
                Else
                    ' Get roles from roles cookie
                    Dim ticket As FormsAuthenticationTicket = FormsAuthentication.Decrypt(Context.Request.Cookies("portalroles").Value)

                    'convert the string representation of the role data into a string array
                    Dim userRoles As New ArrayList

                    Dim role As String

                    For Each role In ticket.UserData.Split(New Char() {";"c})
                        userRoles.Add(role)
                    Next role

                    roles = CType(userRoles.ToArray(GetType(String)), String())
                End If

                ' Add our own custom principal to the request containing the roles in the auth ticket
                Context.User = New GenericPrincipal(Context.User.Identity, roles)

            End If
        End Sub

        Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
            Session.Abandon()
        End Sub

        Public Sub InsertSessionCacheItem(ByVal sUserID As String)
     try         
               If GetSessionCacheItem(sUserID) <> "" Then
                    Exit Sub
                End If

                Dim oRemove As CacheItemRemovedCallback = New CacheItemRemovedCallback(AddressOf SessionEnded)

                System.Web.HttpContext.Current.Cache.Insert(msSessionCacheName + sUserID, sUserID, Nothing, DateTime.MaxValue, TimeSpan.FromMinutes(mnSessionMinutes), CacheItemPriority.Normal, oRemove)
            Catch e As Exception
                Response.Write(e.Message)
            End Try
        End Sub

        Public Function GetSessionCacheItem(ByVal sUserID As String) As String
            Dim sRet As String = ""

          
                sRet = System.Web.HttpContext.Current.Cache(msSessionCacheName + sUserID)
                If sRet = Nothing Then
                    sRet = ""
                End If
            Return sRet
        End Function

        Public Sub SessionEnded(ByVal key As String, ByVal val As Object, ByVal r As CacheItemRemovedReason)
            Dim sUserID As String = ""
            Dim sSessionTest As String = ""

            Try
                sSessionTest = Session("Test").ToString()
            Catch e As Exception
                sSessionTest = e.Message
            End Try
        End Sub
        Sub Session_End(ByVal sender As Object, ByVal e As EventArgs)
            Session.Abandon()
        End Sub
     
    End Class
End Namespace




How find the .ldf and .mdf file path in sql server

sp_helpdb 'Data base name'

Delete all tables and sp from db

Alter Procedure dbo.DeleteAllProcedures
As
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
if @procName <> 'DeleteAllProcedures'
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur
Go
Grant Execute On dbo.DeleteAllProcedures To Public
Go

Delete all stored procedures and tables from a database

In this article I’ll try to explain how you can create a stored procedure that deletes all other stored procedures from a database in Microsoft SQL Server.


Some time it is required that you delete all stored procedures from an SQL Server database. I found this necessary when I was writing a kind of O/R mapper software. Anyways this technique uses the build in sys.objects system table that contains all the objects of current database.



If you filter its selection with a where clause and select only those records that that have type = ‘P’ (this is for procedures) then you can get the names of all the procedures. You can store the list of the procedure names in a temporary table and loop delete the procedures or you can use a cursor. I’ve used a cursor and deleted the procedures one by one using the EXEC function.



Here is the code.



Alter Procedure dbo.DeleteAllProcedures

As

declare @procName varchar(500)

declare cur cursor

for select [name] from sys.objects where type = 'p'

open cur



fetch next from cur into @procName

while @@fetch_status = 0

begin

if @procName <> 'DeleteAllProcedures'

exec('drop procedure ' + @procName)

fetch next from cur into @procName

end

close cur

deallocate cur

Go

Grant Execute On dbo.DeleteAllProcedures To Public

Go

Flag this messageGet Connection String from Windows Registry using C#.net

Flag this messageGet Connection String from Windows Registry using C#.net

Check is numeric

public static string IsNumeric(string strValidate)
{
string strValidChars = "0123456789";
string strChar;
string bolResult = "TRUE";
for (int i = 0; i < strValidate.Length; i++)
{
strChar = strValidate[i].ToString();
if (strValidChars.IndexOf(strChar) == -1)
bolResult = "false";
return bolResult;
}
return bolResult;
}

To check special character

public static string getCleanSQLParameters(string strSQLParameter, bool bolCheckForNumeric)
{
if (strSQLParameter != "")
{
string str = strSQLParameter;
strSQLParameter = (strSQLParameter).Trim();
strSQLParameter = (strSQLParameter).Replace("'", "''");
//strSQLParameter = (strSQLParameter).Replace(")", "");
//strSQLParameter = (strSQLParameter).Replace("(", "");
//strSQLParameter = (strSQLParameter).Replace(";", "");
//strSQLParameter = (strSQLParameter).Replace("-", "");
//strSQLParameter = (strSQLParameter).Replace("|", "");


}
return strSQLParameter;
}

strAddress1 = getCleanSQLParameters(txtBrAddress1.Text, false);

Send Mail in asp .net

string stremail, strMailPass, strMailSubject;
stremail = EmailAddress;
strMailSubject = "Your Branch Login";
//strMailSubject = "Your Branch Login";
strMailPass = emailbodys.BranchMail("Anil","Your Branch Name:", "Name", "Your Login Email:", "MailAddress", "Your Password:", "PWD");

utils.SendEmail(stremail, strMailSubject, strMailPass);



public static string BranchMail(string name, string Bname, string Name, string uid, string id, string password, string pwd)
{


string strBody = "
"; strBody += ""; strBody += ""; strBody += " "; //strBody += " ; strBody += "
" + name + ",";
strBody += "
";
strBody += "Thank You for Submitting Your Information
Your Branch Name: " + Name + "";
strBody += "
Your Login Email: ";
strBody += "
" + id + "";
strBody += "
";
strBody += "Your Password: " + pwd + "";
strBody += "
";
strBody += "Thank you,
Your ";
strBody += "MYHistory.org Branch Submission Team
";
strBody += "pradeepta@gmail.com
213-973-7250
© 2009 , All Rights Reserved. ";
//strBody += "
";
return strBody;
}

public const string From_email = "history@gmail.com";
public const string From_email_password = "History";
public const string Smtp_host = "smtp.gmail.com";
public const string port = "587";

public static void SendEmail(string estrto, string estrsub, string estrmsg)
{




System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
mail.To.Add(estrto);
mail.From = new MailAddress(From_email);
mail.Subject = estrsub;
mail.Body = estrmsg;
mail.IsBodyHtml=true;
SmtpClient smtpClient = new SmtpClient();
smtpClient.Host = Smtp_host;
smtpClient.Credentials = new System.Net.NetworkCredential(From_email, From_email_password);
//smtpClient.Port = 587;
smtpClient.EnableSsl = true;
smtpClient.Send(mail);


}

Join

Read Part 2

IBM DB2 e-kit for Database Professionals

Learn how easy it is to get trained and certified for DB2 for Linux, UNIX, and Windows with the IBM DB2 e-kit for Database Professionals. Register now, and expand your skills portfolio, or extend your DBMS vendor support to include DB2.
If there is one SQL construct that I believe has generated the most confusion since its overhaul in DB2 for OS/390® Version 6, it would have to be outer joins.
Version 6 expanded the capabilities for coding predicates within the ON clause, as well as introducing a host of other optimization and query rewrite enhancements. Enhancing the syntax has definitely increased the potential usage of outer joins, but this also means that there is more to understand. The syntax too has been aligned much more closely with its cousins on the UNIX®, Linux, Windows®, and OS/2® platforms, making it easier to be consistent with your SQL coding across the DB2 family.
In this article, which consists of two parts, I attempt to assemble a guide for coding outer joins to achieve two goals:
  • The most important goal, obtaining the correct result.
  • Secondly, consideration of the performance implications of coding your predicates in different ways.


Part 1 covers the simpler constructs of outer joins, providing a simple comparison of the effect of coding predicates in the ON or WHERE clause. In Part 2, I will cover the more complex topics such as outer join simplification and nesting of outer joins.
The examples in this article use extracts from the DB2 Universal Database (UDB) (non-OS/390) sample database. The data (in Figure 1) is a subset of the full tables. To cater for all outer join combinations, the row with PROJNO = 'IF2000' in the project table has been updated to set the DEPTNO = 'E01'.
For z/OS® and OS/390 users, the table names differ:
DB2 on Workstation table names DB2 for OS/390 and z/OS table names
EMPLOYEE EMP
DEPARTMENT DEPT
PROJECT PROJ


   
   
   

Back to top
Inner to outer joins


Inner joins
For an inner join (or simple table join), only matched rows based on the join predicates are included in the result. Therefore, unmatched rows are not included.
In Figure 2, when joining the Project and Department tables on the DEPTNO column, the row with DEPTNO = 'E01' in the Project (left) table does not have a matched row in the Department table, and is therefore not returned in the result. Similarly, the row with DEPTNO = 'A01' in the department (right) table is also unmatched and not returned.
   
   
   
This example uses the"explicit join" syntax, whereby the keywords"INNER JOIN" (or simply JOIN) are coded between the joined tables. The join predicates are coded in the ON clause. Although this is not mandatory syntax for inner joins, it is for outer joins, and it is therefore good programming practice for consistency. There are a number of other reasons to consider this syntax:
  • It is more descriptive than simply coding a"comma" in the FROM clause to separate tables. This is important as queries become larger.
  • It forces the join predicates (ON clause) to be coded after each join, which means you are less likely to forget to code join predicates.
  • It is easy to determine which join predicates belong to what tables.
  • An inner join can be easily converted to an outer join if required.


And, finally, on the subject of inner joins, people often ask me: "Does it matter in what order I code my tables in the FROM clause?" For retrieving the correct result, the answer is "no." For performance, the answer is "generally, no." The DB2 optimizer evaluates all possible join permutations (sequences) and selects the most efficient one. However, to quote the DB2 UDB for OS/390 and z/OS Administration Guide: "The order of tables or views in the FROM CLAUSE can affect the access path." My interpretation of this statement is that if two (or more) different join sequences equate to the same cost, then the tie-breaker may be the table order in the FROM clause.
Outer join table classification
Before exploring outer join examples, it is important to first understand how we classify tables in the join.
Tables in the FROM clause of an outer join can be classified as either preserved row or NULL- supplying. The preserved row table refers to the table that preserves rows when there is no match in the join operation. Therefore, all rows from the preserved row table that qualify against the WHERE clause will be returned, regardless of whether there is a matched row in the join.
The preserved row table is:
  • The left table in a left outer join.
  • The right table in a right outer join.
  • Both tables in a full outer join.


The NULL-supplying table supplies NULLs when there is an unmatched row. Any column from the NULL- supplying table referred to in the SELECT list or subsequent WHERE or ON clause will contain NULL if there was no match in the join operation.
The NULL-supplying table is:
  • The right table in a left outer join
  • The left table in a right outer join
  • Both tables in a full outer join.


In a full outer join, both tables can preserve rows, and also can supply NULLs. This is significant, because there are rules that apply to purely preserved row tables that do not apply if the table can also supply NULLs.
The order of coding tables in the FROM clause can have extreme significance for left and right outer joins -- and also for outer joins involving more than two tables -- because preserved row and NULL supplying tables behave differently when there is an unmatched row in the join.
Left outer join
Figure 3 shows a simple left outer join.
   
   
   
The left outer join returns those rows that exist in the left table and not in the right table (DEPTNO = 'E01'), plus the inner join rows. Unmatched rows are preserved from the preserved row table and are supplied with NULLs from the NULL-supplying table. That is, when the row is unmatched with the right table (DEPTNO = 'E01'), then the DEPTNO value is NULL-supplied from the Department table.
Note the select list includes DEPTNO from both the preserved row and NULL-supplying table. From the output you can see that it is important to select columns from the preserved row table where possible, otherwise the column value may not exist.
Right outer join
   
   
   
The right outer join returns those rows that exist in the right table and not in the left table (DEPTNO = 'A00'), plus the inner join rows. Unmatched rows are preserved from the preserved row table and are supplied with NULLs from the NULL-supplying table.
For a right outer join, the right table becomes the preserved row table, and the left table is the NULL-supplying table. The DB2 for OS/390 and z/OS optimizer rewrites all right outer joins to become left outer joins, by simply inverting the tables in the FROM clause and by changing the keyword RIGHT to LEFT. This query rewrite can only be seen by the presence of the value"L" in the JOIN_TYPE column of the plan table. For this reason, you should avoid coding right outer joins to avoid confusion when you are interpreting the access path in the plan table.
Full outer joins
   
   
   
The full outer join returns those rows that exist in the right and not in the left (DEPTNO = 'A00'), plus the rows that exist in the left table and not in the right table (DEPTNO = 'E01'), and the inner join rows.
Both tables can supply NULLs but also preserve rows. However, the tables are identified as NULL-supplying because there are"query rewrite" and"WHERE clause predicate evaluation" rules that apply separately to NULL-supplying and to preserved row tables. I'll describe more about these differences in later examples.
In this example, both join columns have been selected to show that either table can supply NULL for unmatched rows.
To ensure that a non-NULL is always returned, code the COALESCE, VALUE, or IFNULL clause, which returns the first argument that is not NULL, as shown here:COALESCE(P.DEPTNO,D.DEPTNO)

Back to top
Outer join predicate types


In releases before DB2 for OS/390 Version 6, predicates could be only applied before the join, or totally after the join. Version 6 introduced the concepts of"during-join" predicates and"after-join-step" predicates.
DB2 can apply before-join predicates before the join to delimit the number of rows that are joined to subsequent tables. These"local", or"table access," predicates are evaluated as regular indexable, stage 1 or stage 2 predicates on the outer table of a pairwise join. Pairwise join is the term used to describe each join step of two or more tables. For example, a row from table 1 and table 2 is joined, and the result is joined to table 3. Each join only joins rows from two tables at a time.
During-join predicates are those coded in the ON clause. For all but full outer joins, these predicates can be evaluated as regular indexable, stage 1 or stage 2 predicates (similar to before-join predicates) on the inner table of a pairwise nested loop or hybrid join. For a full outer join, or any join using a merge scan join, these predicates are applied at stage 2, where the physical joining of rows occurs.
After-join-step predicates can be applied between joins. These are applied after the join in which all columns of the where clause predicate become available (simple or complex predicate separated by OR), and before any subsequent joins.
Totally-after-join predicates are dependent on all joins occurring before they can be applied.
Before-join predicates
Before Version 6 DB2 for OS/390, DB2 had a limited ability to push down WHERE clause predicates for application before the join. Therefore, to ensure a where clause predicate was applied before the join, you had to code the predicate in a nested table expression. This not only added complexity to achieve acceptable performance, but the nested table expression required the additional overhead of materializing the result before the join.
   
   
   
From Version 6 onwards, DB2 can merge the nested table expression into a single query block, and thus avoids any unnecessary materialization. DB2 aggressively merges any nested table expression based upon the standard materialization rules listed in the Administration Guide or the Application Programming and SQL Guide.
Instead of coding these in nested table expressions, the predicates can now be coded in the WHERE clause as shown in Figure 7.
   
   
   
The rule for before-join predicates coded in a WHERE clause is that they must apply to the preserved row table only; or to be more specific, the WHERE clause must not apply to a NULL-supplying table. This means you no longer have to code these predicates in nested table expressions.
For a full outer join, neither table can be identified as only being a preserved row table, and of course, both are NULL-supplying. For NULL-supplying tables, the risks of coding predicates in the WHERE clause are that they will either be applied totally after the join or will cause simplification of the outer join (which I will talk about in Part 2). To apply the predicates before the join, you must code them in nested table expressions as shown in Figure 8.
   
   
   
Because they limit the number of rows that will be joined, before-join predicates are the most efficient of the predicate types described here. If you begin with a 5-million row table, which returns one row after the WHERE clause is applied, it is obviously more efficient to apply the predicate before joining the one row. The other alternative, which is not efficient, is to join 5 million rows, and then apply the predicate to produce a result of one row.
During-join predicates
Coding join predicates in the ON clause is mandatory for outer joins. In DB2 for OS/390 Version 6 and later, you can also code expressions, or"column-to-literal" comparisons (such as DEPTNO = 'D01',) in the ON clause. However, coding expressions in the ON clause can produce very different results from those same expressions coded in a WHERE clause.
This is because predicates in the ON clause, or during-join predicates, do not limit the result rows that are returned; they only limit which rows are joined. Only WHERE clause predicates limit the number of rows of rows that are actually retrieved.
Figure 9 demonstrates the result of coding an expression in the ON clause of a left outer join. This is not the result expected by most people when coding this type of query.
   
   
   
In this example, because there are no WHERE clause predicates to limit the result, all rows of the preserved row (left) table are returned. But the ON clause dictates that the join only occurs when both P.DEPTNO = D.DEPTNO AND P.DEPTNO = 'D01'. When the ON clause is false (that is, P.DEPTNO <> 'D01'), then the row is supplied NULLs for those columns selected from the NULL-supplying table. Similarly, when P.DEPTNO is 'E01', then the first element of the ON clause fails, and the row from the left table is preserved, and the null is supplied from the right table.
When DB2 accesses the first table and determines that the ON clause will fail (such as when P.DEPTNO <> 'D01'), then to improve performance, DB2 immediately supplies NULL for the NULL-supplying table columns without even attempting to join the row.
Now let's talk about during- join predicates for a full outer join. The rules for the ON clause are the same for full joins as for left and right outer joins: the predicates in the ON clause do not limit the resultant rows which are returned, only which rows are joined.
For the example in Figure 10, because there are no WHERE clause predicates to limit the result and because both tables of a FULL JOIN preserve rows, then all rows of the left and right tables are returned. But the ON clause dictates that the join only occurs when P.DEPTNO = 'D01'. When the ON clause is false (that is,    P.DEPTNO <> 'D01'), then the row is supplied NULLs for those columns selected from the opposite table to the table whose row is being preserved.
Note: This syntax is non-OS/390 only because OS/390 does not permit expressions in the ON clause of a full outer join.
   
   
   
To simulate having the non-OS/390 comply with OS/390 DB2 syntax, then we must first derive the expression as a column within a nested table expression, and then perform the join. By first deriving the column DEPT2 as 'D01' in Figure 11, the ON clause effectively becomes a join only when P.DEPTNO = 'D01'.
   
   
   
After-join predicates
Figure 12 contains a query with both after-join-step and totally-after-join predicates.
   
   
   
The first compound predicate in the WHERE clause refers only to tables D and E (D.MGRNO = E.EMPNO OR E.EMPNO IS NULL). Therefore, if the join sequence chosen by the optimizer mimics the coding of the SQL, then DB2 can apply the WHERE clause predicate after the join between D and E, and before the join to P. However, the second compound predicate in the WHERE clause refers to tables D and P (D.MGRNO = P.RESPEMP OR P.RESPEMP IS NULL). These are the first and third tables in the join sequence. This predicate cannot be applied therefore until the third table is joined, which is the final table in the join sequence. Hence this is referred to as a totally-after-join predicate.
It is likely that an after-join-step predicate may revert to a totally-after-join predicate if the table join sequence alters, which is possible given that the DB2 OS/390 optimizer can reorder the table join sequence based upon the lowest cost access path. Given that DB2 is able to apply the predicate as early as possible in between joins to limit the rows required for subsequent joins, then you should also attempt to code your predicates such that DB2 is able to apply them as early in the join sequence as possible.

Back to top
Conclusion
In this article, I described several topics:
  • The order of tables in the FROM clause and the effect on inner and outer joins
  • The differences between these type of joins
  • The different predicate types.


To recap, WHERE clause predicates that are applied to the preserved row table can filter rows as either:
  • Before-join predicates
  • After-join-step or totally-after-join predicates.


If these predicates are currently coded in a nested table expression, you can now write them in the WHERE clause. Before-join predicates are the most efficient predicates, because they limit the number of rows before the join. After-join-step predicates also limit the number of rows for subsequent joins. Totally-after-join predicates are the least efficient, since filtering occurs completely after all joins have taken place.
Predicates in the ON clause are the biggest surprise, because they only filter rows on the NULL-supplying table as during-join predicates. They do not filter rows on the preserved row table, as WHERE clause predicates do.
In Part 2 of this article, I will describe what happens if WHERE clause predicates are coded against the NULL-supplying table.
I hope that this article has given you some insight into outer joins and has given you some clues on how to solve the mystery of where to code your outer join predicates.