Adsense

Friday, September 26, 2014

Db2 Terminate Vs Connect Reset , Disconnect

db2 Terminate and db2 connect Reset both break the connection to a database.

             Connect Reset breaks a connection to a database, but does not terminate the back-end process. The Terminate command does both ie. break the connection to a database and terminate the back-end process.

Suppose an application is connected to a database, or a process may be in the middle of a of work. We can use TERMINATE to make the database connection to be lost. When Terminate is issued, an internal commit is also performed.

When issue the command db2stop, it may not stop the database manager, if an application is connected to a database. In this situation, you have to issue db2 Terminate command or db2 connect reset, then issue the command db2stop.

Db2 Disconnect is used to clear the database connection of a particular database or all

Syntax : db2 disconnect dbname

eg. db2 disconnect sample

db2 disconnect all

db2 release all

Note : Even after disconnecting database, some times you may get the error message "Database already in use "  when you execute the commands like   "db2 Restore db databasename , etc ..". 

That means current data base is connected by some other applications. Applications connected to the database can be listed out by the following command

db2 list applications

Auth Id Application Name Appl. Handle Application Id DB Name # of Agents
------- -------------- ---------- ------------------------------ -------- -----

TEST javaw.exe 78 *LOCAL.DB2.110722172106 EMPLOYEE 1

TEST javaw.exe 77 *LOCAL.DB2.110722172103 EMPLOYEE 1


Now you can use db2 "force applications all" This command is used to kill  all the applications forcefully at instance level .

To force a particular application with application handle 78
db2  "force application(78)"

Example SQL injection in db2. How to Solve SQL injection in db2?

SQL injection is a technique to attack web applications by inserting malicious code (user supplied data) into strings which are passed to an instance of Database Server for parsing and execution. SQL injection vulnerabilities allow an attacker to inject (or execute) SQL commands within an application. It is one of the dangerous application vulnerability. One way for avoiding SQL Injection, the user supplied data should be validated in both client side and server side  before parsing & executing the SQL statement so that it should not accept the potentially harmful characters.

Example of SQL injection: 

           The primary form of SQL injection consists of direct insertion of code into the input box which is assigned to a variable that is concatenated with SQL queries and executed. 

Simple SQL injection Exampe 1 :

String empCode=""; 
empCode = request.getParameter ("EmpCode"); 
strQuery = " select *  from Employee where empId= '" + empCode+ "'"; 

    Assume that the user enters
NKN5251'; drop table user--
as input in the EmpCode text box. The text box value is assigned to the variable empCode.
    
    In this case, the above query is assembled as select * from Employee where empId= 'NKN5251'; drop table user--' 

          The semicolon (;) is the end of one query and the start of another. The double dash (--) indicates that the rest of the current line is a comment and should be ignored. As the assembled query is valid and syntactically correct, it will be executed by the server. When Database Server processes this statement, it will first select all records in Employee where empId is NKN5251. Then, it will drop user table. 

Simple SQL injection Exampe 2 : 

     The following simplest SQL injection technique is bypassing form-based login. The Java servlet code given below is a sample code which is used to login, that illustrates the vulnerability by accepting user input without performing adequate input validation or escaping meta characters: 


protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

 resp.setContentType("text/html"); 

 PrintWriter out = resp.getWriter(); 

 out.println("<html>");

 out.println("<body>"); 

 conn = CoonetionFactory.getConnection( );

 String loginId="";

 String passwd="";

 loginId = req.getParameter ("loginName");

 passwd = req.getParameter ("password");

 String strQuery = "select * from user where loginname='" + loginId +"' and password='" + passwd + "'";

 stmt = conn.createStatement();

 rs = stmt.executeQuery(strQuery);

  if (rs.next()) 
        out.println("Login Successfull");
         else 
        out.println("Invalid Username or Password ");

 out.println("</body>");

 out.println("</html>");

 out.close(); 
 }


a) Consider the above code. For example, attacker may provide a login name of : admin' or '1'='1 and any password, the generated SQL statement becomes select * from user where loginname='admin' OR '1'='1' and password='xyz' 

b) For example, even attacker may provide a login name of : ' OR ''='' and password of ' OR ''=', the generated SQL statement becomes select * from user where loginname= '' OR ''='' and password='' OR ''='' 

This above examples allows an attacker to log in to the site without giving the correct password, since the ‘OR’ expression is always true. Using the same technique attackers can inject other SQL commands which could extract, modify or delete data within the database. 

To prevent SQL injection: 

-> Validate all user Input especially server side validation.
-> Always use parameterized query ( Prepared Statements , Stored Procedures ) 
-> Dynamic data should be explicitly bound to parameterized queries. 
-> Never use string concatenation to create dynamic SQL.
-> Escape all user input.

Validating user input :

Validate user input by testing type, length, format, and range 
Use stored procedures to validate user input 
Reject input that contains the harmful characters (Query delimiter ; ), (Character data string delimiter ') ,
( Comment delimiter //)

Prepared Statements:

Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.

Safe:

String strQuery = "SELECT * FROM User WHERE loginName = ? "; 
PreparedStatement pstmt = con.prepareStatement(strQuery); 
pstmt.setString(1, loginId); 
ResultSet rs = pstmt.executeQuery();

    Using PreparedStatement with Bind variables avoids SQL Injection attacks and improves the performance. Although Prepared Statements helps in defending against SQL Injection, there are also possibilities of SQL Injection attacks through inappropriate usage of Prepared Statements. The example below explains how SQL Injection attacks when using PreparedStatement

UnSafe:

String userName = req.getParameter("loginName"); 
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE loginname = '+strName+'");

Thursday, September 18, 2014

Db2 export command example to export data to external file format (del , ixf )

Suppose you want to transfer data from a db2 database table to another db2 database or within the same database, one of the solution is to use db2 export / import command . Using db2 export / import you can move your data to excel also.

DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of del, asc (ascii format), wsf, ixf etc.. del - delimited , wsf - worksheet format , ixf - Integration Exchange Format.

The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data across platforms. The following examples illustrates the use of Export command. You can see more about db2 import command at db2 import.

Example 1 : Export all rows in the Employee table to the file emp.ixf with message file msgs.txt for writing the messages or warnings

              db2 export to emp.ixf of ixf messages msgs.txt select * from employee

Example 2:The following command exports all rows in the Employee table to the file emp.del.

               db2 export to emp.del of del select * from employee

Example 3: The following command exports all rows in the Employee table to the file employee.del using the column delimiter semi colon(;) and string delimiter single quote (') and the decimal point comma,

              db2 export to employee.del of del modified by chardel'' coldel; decpt, select * from employee

    Output of the above is command is as follows.

'1002';'XYZ';+00050003,00;+00060000,00
'1003';'ABC';+00048750,00;+00055550,00

Some of the Parameters  applicable to DB2 Export command:

                MESSAGES message-file : Specifies the destination file to be created for writing warnings and error messages which is generated during an export operation. If the file already exists, then warnings & errors are appended. If msg file is omitted, the messages are written on the console. 

         In the Example 1, msgs.txt is created for information . Sample msgs.txt file has the messages of SQL3104N  
                 The Export utility is beginning to export data to file "emp.ixf". , SQL3105N The Export utility has finished exporting "2" rows.

METHOD N col-name : Specifies 1 or more column names to be written in the output file. If it is not specified, the column names in the table are used. This is valid only for WSF and IXF files . 

    Example : db2 export to emp.ixf of ixf method N (empcode , empname) messages msgs.txt select * from employee

MODIFIED BY filetype-mod : Specifies file type of DEL , IXF , WSF

DB2 Basic Commands with Example

DB2 Basic Commands

1. db2cmd -  Opens db2 command window

2. db2start   - to start the database manager instance

3. db2stop  -  to stop the database manager instance

4. db2stop force - to stop the database manager instance forcefully

5. db2cc - starts the db2 control center

6. db2 list db directory - lists all the databases created in the db server

          -To list databases that start with "SAL"
                Linux : db2 list db directory | grep -i "SAL"
               Windows :  db2 list db directory | find /i "SAL"

7. db2 list tables -  lists all the tables of a connected database
         
          -To list tables that start with "EMP"

                Linux : db2 list tables | grep -i "EMP"
               Windows :  db2 list tables | find /i "EMP"

8. db2 list applications - lists the applications connected
             
           - To count number of applications connected

                   Linux : db2 list applications | grep -c DB2INST1
                   Window: db2 list applications | find /c "SCHEMA NAME'

9. db2 get db cfg - displays the configurations of the connected database.

             -To display configurations related to LOG

                  Linux : db2 get db cfg | grep -i LOG
                  Windows: db2 get db cfg | find /i "LOG"
             

10. db2 get db cfg for databasename - displays the configurations of a database mentioned.

11. db2 get dbm cfg - shows the database level configuration settings

12. db2 terminate -  ends the process the server held open to the client, and drops all resources

13. db2 connect reset - stops the connection to database COMMITs the database.

14. db2licm -l  - displays  the db2 licence details

15. db2 activate db 


16. db2level -  shows the db2 Version detail

17. db2 CALL GET_DBSIZE_INFO(?, ?, ?, -1) -  to get the size of the database sizelists SNAP SHOT TIMESTAMP, DATABASE SIZE, DATABASE CAPACITY


18. db2 list command options - displays the available  Command Line Processor (DB2CMD) settings.


Database creation & Backup Restore Commands:

1. db2 create db databasename  - creates the database

2. db2 create db databasename using codeset utf-8 territory us collate using system - creates the database with utf-8 support

3. db2 backup db dbname - to make offline backup of the database

4. db2 restore db databasename taken at timestamp into sourcedb replace existing -  to restore the database backup into source database.

5. db2move databasename export -  exports the data

6. db2move dbname import  - imports the exported data to the database

7. db2 list history backup all for databasename -shows recent backups and where they are stored

8. db2ckbkp dbimage name -checks the integrity of a backup image

9. db2 restore db incremental automatic taken at - Restores from incremental backup automatic

10. db2ckrst -d -t  - to restore an incremental backup manually this command will give you the required previous backups.

11. db2 list table spaces  -lists all the table spaces of a connected database


resultSetHoldability in DB2 , Solution for Invalid operation: result set is closed in Java & Websphere

          Do you get the exception Invalid operation: result set is closed in Db2  when you run a java application that uses DB2 connection. Solution is very simple. This error is generated when closed resultset objects are accessed by the application. All the resultset objects which are created for the transaction are closed, whenever the transaction is committed. After that when we access the result set, the exception occurs. Sometimes it is necessary to keep the resultset open even after the commit() method is called. So we have to solve the above error. We can solve this error at application level or Application Server Level (Websphere Application Server in our example) .
   
        The prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) of connection object accepts 4 arguments . resultSetHoldability is the 4th argument. The default value of DB2's resultSetHoldability value is of 1 (HOLD_CURSORS_OVER_COMMIT). If the existing value of resultSetHoldability is 2 (CLOSE_CURSORS_AT_COMMIT) means, setting the resultSetHoldabilty value to 1 or (HOLD_CURSORS_OVER_COMMIT) will solve the problem. So you can control whether or not  to close the ResultSets after commit() method by passing 1 or 2 to createStatement() / prepareStatement method .

           Now let us see how to solve the above error at WebSphere Application Server Level. By default, the application server's custom property resultSetHoldability is configured with the value of 2 (CLOSE_CURSORS_AT_COMMIT). This property causes DB2 to close its resultSet / cursor at transaction commit. Although DB2's default resultSetHoldability value is 1, the application server retains the default value of 2. You can change the default value doing the following steps

1. Open the IBM administative console.
2. Expand Resources, then Select JDBC providers > DB2 Universal JDBC Driver Provider ->Data sources .
3. Now select the DataSource Name which you have created (For Example EmployeeDS ), then select Custom properties from right side menu under Additional Properties
3. Now select resultSetHoldability and Change the value 2 to 1, then apply->save->save. The following screen shows custom property resultSetHoldabilty

Wednesday, September 3, 2014

db2 automatic backup configure using crontab in linux.

In this tutorial, let us see the steps how to configure automatic backup in db2 using crontab in linux.  UNIX or Linux cron jobs can be scheduled with the crontab command. In our example, let us configure to take automatic db2 backup on every saturday 10 PM.

1. Create a shell script in a folder (/db2/scripts) that takes online full backup.  For online backup, please ensure that you have enabled online backup.

$  vi backup.ksh

       echo "Online Backup Starts at `date` ... "

        /home/db2inst1/sqllib/bin/db2 backup db dbname online to /db2/backup/ include logs

        echo "Backup completed at `date`"



    If you want to remove old backup files after the current backup is completed, you can include below lines. The following script deletes the backup file which is 7 days older

        echo "Before delete available backups : `ls /db2/backup/`"

        echo "Delete starts"

        find /db2/backup/ -mtime +7 -exec rm {} \;

 echo "Delete completed"

        echo "After Delete available backups : `ls /db2/backup/`"


Save the above file (which is in the folder /db2/scripts) .  Change file mode to execute.

chmod 777 backup.ksh

Manually you can test the above script by ./backup.ksh


Now let us schedule the above task to be executed periodically. Linux command crontab does this job. Now us configure. Run the below command

crontab -e
             -which launches an editing session that allows you to create a crontab file.

In this example, a cron job starts the shell script backup.ksh at 10 p.m.


 To schedule backup,  every Saturday at 10:00 p.m.
#
0  22    * * 6  /usr/bin/su - db2inst1 -c "/db2/scripts/backup.ksh"  
#  


If you want to capture the output in a log file, then the above line can be replaced with the below.
/usr/bin/su - db2inst1 -c "/db2/scripts/backup.ksh > /db2/logs/fullbackup.log"


Now save the cron job. (Esc : wq). Now you are done.

To schedule the above job on every monday to friday at 10.00 PM
30 22 * * 1,2,3,4,5 /usr/bin/su - db2inst1 -c "/db2/scripts/backup.ksh"


To schedule any job (For eg. incremental backup)  twice a day at 11:30 a.m. and at 9:30 p.m. on every monday through saturday, you can wrire something like

30 11,21 * * 1-6  /usr/bin/su - db2inst1 -c "/db2/scripts/backup.ksh"

In the crontab line entry, * indicates all allowed values

crontab -l : to list all scheduled jobs

crontab -r : to remove scheduled jobs

For more on crontab, you can read at http://en.wikipedia.org/wiki/Cron