DB2 Export Data To CSV With PHP Interface

Reporting with large number of records could be cumbersome if you view it on web page and download the data to CSV or Excel format.
Especially if you are dealing with hundred thousand or millions records.

DB2 has feature for export data or download records directly to external storage i.e your local hard disk.
The way is to specify the data to be exported by supplying an SQL SELECT statement and then use db2 export to download directly to CSV format.

The DB2 syntaxt for this download is db2 export to <filepath/filename> of del <SQL Select statement>.
More complete explanation please go to IBM url https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

This blog post will explain how to use DB2 export with PHP.
The PHP page calls DB2 export which transfer records to CSV format and after file created and finish transfering then the CSV is zipped.

Step by step Example

  1. SQL SELECT Statement
    connect to db_HRIS user hruser using hrpasswd;
    insert into HR.EXPSQL values ('EmpSalary');
    
    export to "D:\Report Files\EmpSalary_Rpt.csv" of DEL 
    SELECT EMPID, EMPNAME, DEPARTMENT, POSITION, SALARY 
    	FROM HR.EMPLOYEES WHERE HIREDATE>='2015-01-01'
    Union All
    select 'Employee ID', 'Name', 'Departement', 'Position', 'Salary'
    	from sysibm.sysdummy1 ;
    
    delete from HR.EXPSQL where sqlname='EmpSalary';
    terminate;
    

    Save it as EmpSalary.sql
    Above SQL file explanation:

    • Connect to your database with proper user ID & password
    • Create simple table i.e HR.EXPSQL contains one column sqlname.
      The purpose of this table is to have a status whether export process is still progressing or already finished.
      If HR.EXPSQL contains EmpSalary record then it still run.

    • Db2 export syntaxt export to <filepath/filename> of del <SQL Select statement>.
      The SQL select has ‘union all’ to Attach column header text.
      The export will create EmpSalary_Rpt.csv in D:\Report Files folder
    • Delete EmpSalary record to indicate that export process is done
  2. BAT file for encapsulating db2 export syntaxt
    "C:\Program Files\IBM\SQLLIB\BIN\DB2CMD.exe" DB2SETCP.BAT DB2.EXE -tvsf %1
    exit
    

    Save it as db2_Export.bat
    This BAT file will be run by PHP exec command.

  3. Test BAT file by Running on command prompt
    db2_Export.bat EmpSalary.sql
    You should have EmpSalary_Rpt.csv file in your D:\Report Files folder
  4. PHP page for executing bat file & zip CSV
    if (file_exists("EmpSalary_Rpt.csv")) { 
    	unlink("EmpSalary_Rpt.csv"); 
    }
    $command = "cmd.exe /c db2_Export.BAT EmpSalary.sql";
    
    //run db export process
    //This exec has asynchronous because of using BAT with contains specific syntaxt.
    exec($command, $result);
    
    //check for CSV file whether it is created or not by looping several times
    //if CSV is founded then break from the loop
    $loop = 0;
    while (true) {
    	if (file_exists("EmpSalary_Rpt.csv")) {				
    		break;
    	} else {
    		
    		if ( $loop>=10) {
    			exit("Create CSV file is too long. PHP exited");
    		}
    		$loop++;
    	}
    	sleep(6);
    }
    
    //check for export whether it is done or not by checking record existance in HR.EXPSQL table
    //if export is done with indicated by No 'EmpSalary' record in HR.EXPSQL then break from the loop
    $cnt = countRunSQL("EmpSalary");
    $loop = 0;
    while ($cnt > 0) {
    	
    	sleep(10);
    	$loop++;
    	$cnt = countRunSQL("EmpSalary");
    	
    	if ($loop > 360) {
    		echo("Exporting Process is too long. PHP Exited");
    		break;
    	}
    }
    
    
    // Zipping File...
    $zip = new ZipArchive();
    if ($zip->open('EmpSalary_Rpt.zip',ZIPARCHIVE::CREATE) === TRUE) {
    	$zip->addFile('EmpSalary_Rpt.csv', 'EmpSalary_Rpt.csv');
    	$zip->close();
    	// ZIP Successful
    }
    	
    function countRunSQL($sqlname) {
    	$dbconn = new db2_connection();
    	$query = "select count(*) as cnt from HR.EXPSQL where SQLNAME='".$sqlname."'";
    	$res = $dbconn->read($query);
    	$cnt = (int)$res["cnt"];
    	$res->Close();
    	$dbconn->Close();
    	return $cnt;
    }
    

    There is a function countRunSQL($sqlname).
    Function to check record existance in HR.EXPSQL to know whether export process is done or still in progress since this export is asynchronous.
    Zip needed when CSV is large enough to download by user.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s