Union at DB2 used to join 2 or more resultset from a query. For example if we want to see list of staff who work at IT department and FA department.
This is the syntax
select column1, column2
from table_name
where….
UNION
select column1, column2
from table_name
where….
Each column1 and column2 must be match with other resultset, or it will generate an error.
This is an simple example how to using UNION in Stored Procedure
CREATE PROCEDURE SP_PAKE_UNION ( )
DYNAMIC RESULT SETS 1
————————————————————————
– SQL Stored Procedure
————————————————————————
P1: BEGIN
– Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’E21′
union
select a.firstnme, b.deptname
FROM EMPLOYEE a, DEPT b
where a.workdept = b.deptno
and a.workdept =’A00′;
– Cursor left open for client application
OPEN cursor1;
END P1
That Store Procedure will generate list of staff who work at 'A00' and 'E21' department.
UNION is differ from UNION ALL. UNION just list the DISTINCT output. It will remove any record which is redundant. If you want to see all output, just change UNION with UNION ALL
Best regards,
Deny Sutani
Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Thursday, January 15, 2009
Wednesday, December 31, 2008
A Few Tips On How To Pick A Web Hosting Company
As any good web developer knows, a web developer is always worried about creating the best web site that they can. They work very hard with the client to decide exactly how they want their site to look, to function, and also how to make it run the most efficiently. These are all things that people need to think about when they are creating a web site, but sometimes we forget one of the most important things, how are we going to get our site on the internet?
No matter how great the web site you create is, it is meaningless if you do not get it out on the internet. So if you are going to get our web site on the internet, then you can either host it on a web server yourself, or get a web hosting company to host it for you. If you are a large company that has an IT staff to setup the infrastructure to host a web site, then this is probably the best idea, because you have more control over the things that happen.
However, most people that create web sites do not have the money or the time to setup such an infrastructure. These people will have to get a web hosting company to host their web site. Here are a few things to consider when you try to pick a web hosting company that is right for you:
Space – Some web hosting companies offer more space than others. If you are going to create a large web site, or you are looking to expand a lot in the near future, then the amount of space offered is very important.
Email Addresses – If your web site is going to give away email addresses, or you need extra email addresses for your users, then the amount of free email addresses that you are given might be very important.
Database Access – Some web hosting companies offer the ability to use a MySQL or SQL Server database to store information or authenticate against. This can be a very important fact to help make your web site more secure, or more dynamic.
No matter how great the web site you create is, it is meaningless if you do not get it out on the internet. So if you are going to get our web site on the internet, then you can either host it on a web server yourself, or get a web hosting company to host it for you. If you are a large company that has an IT staff to setup the infrastructure to host a web site, then this is probably the best idea, because you have more control over the things that happen.
However, most people that create web sites do not have the money or the time to setup such an infrastructure. These people will have to get a web hosting company to host their web site. Here are a few things to consider when you try to pick a web hosting company that is right for you:
Space – Some web hosting companies offer more space than others. If you are going to create a large web site, or you are looking to expand a lot in the near future, then the amount of space offered is very important.
Email Addresses – If your web site is going to give away email addresses, or you need extra email addresses for your users, then the amount of free email addresses that you are given might be very important.
Database Access – Some web hosting companies offer the ability to use a MySQL or SQL Server database to store information or authenticate against. This can be a very important fact to help make your web site more secure, or more dynamic.
Sunday, December 28, 2008
Web Servers and Firewall Zones
Web and FTP Servers
Every network that has an internet connection is at risk of being compromised. Whilst there are several steps that you can take to secure your LAN, the only real solution is to close your LAN to incoming traffic, and restrict outgoing traffic.
However some services such as web or FTP servers require incoming connections. If you require these services you will need to consider whether it is essential that these servers are part of the LAN, or whether they can be placed in a physically separate network known as a DMZ (or demilitarised zone if you prefer its proper name). Ideally all servers in the DMZ will be stand alone servers, with unique logons and passwords for each server. If you require a backup server for machines within the DMZ then you should acquire a dedicated machine and keep the backup solution separate from the LAN backup solution.
The DMZ will come directly off the firewall, which means that there are two routes in and out of the DMZ, traffic to and from the internet, and traffic to and from the LAN. Traffic between the DMZ and your LAN would be treated totally separately to traffic between your DMZ and the Internet. Incoming traffic from the internet would be routed directly to your DMZ.
Therefore if any hacker where to compromise a machine within the DMZ, then the only network they would have access to would be the DMZ. The hacker would have little or no access to the LAN. It would also be the case that any virus infection or other security compromise within the LAN would not be able to migrate to the DMZ.
In order for the DMZ to be effective, you will have to keep the traffic between the LAN and the DMZ to a minimum. In the majority of cases, the only traffic required between the LAN and the DMZ is FTP. If you do not have physical access to the servers, you will also need some sort of remote management protocol such as terminal services or VNC.
Database servers
If your web servers require access to a database server, then you will need to consider where to place your database. The most secure place to locate a database server is to create yet another physically separate network called the secure zone, and to place the database server there.
The Secure zone is also a physically separate network connected directly to the firewall. The Secure zone is by definition the most secure place on the network. The only access to or from the secure zone would be the database connection from the DMZ (and LAN if required).
Exceptions to the rule
The dilemma faced by network engineers is where to put the email server. It requires SMTP connection to the internet, yet it also requires domain access from the LAN. If you where to place this server in the DMZ, the domain traffic would compromise the integrity of the DMZ, making it simply an extension of the LAN. Therefore in our opinion, the only place you can put an email server is on the LAN and allow SMTP traffic into this server. However we would recommend against allowing any form of HTTP access into this server. If your users require access to their mail from outside the network, it would be far more secure to look at some form of VPN solution. (with the firewall handling the VPN connections. LAN based VPN servers allow the VPN traffic onto the network before it is authenticated, which is never a good thing.)
Every network that has an internet connection is at risk of being compromised. Whilst there are several steps that you can take to secure your LAN, the only real solution is to close your LAN to incoming traffic, and restrict outgoing traffic.
However some services such as web or FTP servers require incoming connections. If you require these services you will need to consider whether it is essential that these servers are part of the LAN, or whether they can be placed in a physically separate network known as a DMZ (or demilitarised zone if you prefer its proper name). Ideally all servers in the DMZ will be stand alone servers, with unique logons and passwords for each server. If you require a backup server for machines within the DMZ then you should acquire a dedicated machine and keep the backup solution separate from the LAN backup solution.
The DMZ will come directly off the firewall, which means that there are two routes in and out of the DMZ, traffic to and from the internet, and traffic to and from the LAN. Traffic between the DMZ and your LAN would be treated totally separately to traffic between your DMZ and the Internet. Incoming traffic from the internet would be routed directly to your DMZ.
Therefore if any hacker where to compromise a machine within the DMZ, then the only network they would have access to would be the DMZ. The hacker would have little or no access to the LAN. It would also be the case that any virus infection or other security compromise within the LAN would not be able to migrate to the DMZ.
In order for the DMZ to be effective, you will have to keep the traffic between the LAN and the DMZ to a minimum. In the majority of cases, the only traffic required between the LAN and the DMZ is FTP. If you do not have physical access to the servers, you will also need some sort of remote management protocol such as terminal services or VNC.
Database servers
If your web servers require access to a database server, then you will need to consider where to place your database. The most secure place to locate a database server is to create yet another physically separate network called the secure zone, and to place the database server there.
The Secure zone is also a physically separate network connected directly to the firewall. The Secure zone is by definition the most secure place on the network. The only access to or from the secure zone would be the database connection from the DMZ (and LAN if required).
Exceptions to the rule
The dilemma faced by network engineers is where to put the email server. It requires SMTP connection to the internet, yet it also requires domain access from the LAN. If you where to place this server in the DMZ, the domain traffic would compromise the integrity of the DMZ, making it simply an extension of the LAN. Therefore in our opinion, the only place you can put an email server is on the LAN and allow SMTP traffic into this server. However we would recommend against allowing any form of HTTP access into this server. If your users require access to their mail from outside the network, it would be far more secure to look at some form of VPN solution. (with the firewall handling the VPN connections. LAN based VPN servers allow the VPN traffic onto the network before it is authenticated, which is never a good thing.)
Subscribe to:
Posts (Atom)
