Tag Archives: DB2

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.
Continue reading

Connect to IBM DB2 & Read Image Column Using VB.Net

IBM DB2 LUW or DB2 Linux Unix Windows is one of a recommended RDBMS software used by many organizations.
By its name this DB2 LUW is a platform independent because it can be run on Linux, Windows, Solaris, Mac OS.

IBM has free version of DB2 LUW which is DB2 Express-C. Unlike another RDBMS, DB2 Express-C does not have any time restrictions or database size limitations.
So this free version can be used in production environment.

Because of platform independent we can install DB2 server on Linux as database server to avoid virus infection. Since MS Windows is more widely used (at least in my country) then we also can use MS Windows to run client application (VB.Net) that connect to DB2.
I will show you example using VB.Net to work with DB2 and also to get image data.

DB2 .Net Data Provider

IBM provides .Net Data Provider for DB2. It is located on installation of DB2 folder like C:\Program Files\IBM\SQLLib\Bin\netf20\IBM.Data.DB2.dll. I added this library file to project’s reference.
I use DB2Connection class to establish connection to DB2. DB2DataAdapter to retrieve recordset and DB2Command to execute sql query.

Example

I use SAMPLE database and populate DataGridView with Employee data. If employee has a photo then it will be showed on picture box.
Please refer to this pic below:


Continue reading

Manage DB2 Table Security and Accessing It Using PHP

Security always considered on many application especially when dealing with e-commerce. DB2 has an ability to set table security with only one line of codes.

Some tables might be accessed by public or anonymous users but others can only be accessed by private users. In order to set that we need to understand grant and revoke DB2 syntax.

Grant is giving user some permissions to a table whereas Revoke is taking user from some table permissions.

I will give example of DB2 syntaxes to set tables security start from creating DB2 instance, create a database, schema, table and grant tables. Also there is PHP web apps to ‘select’, ‘insert’ and ‘update’ to these tables. I’ve used Windows OS and DB2 (ver)..

I am using db2admin user to set initial configuration. db2admin is OS and DB2 user created when I installed DB2 software. db2admin acts as administrator in DB2. FYI DB2 user is also OS user.

Continue reading

Useful PHP DataTable Class Example To Store Objects including Recordset

Introduction

Any table like objects like array and recordset have same structure. They have rows and columns.
Usually column is a key and row is a value.

A recordset that comes from database ‘select’ operation usually stored in array or OOP object variable on MVC concept or any kind of business logic and view layer.
A web page access this variable to show its data. View layer only include database and datatable class files. So web page users doesn’t access database operational directly.

With this concept, I have build a simple DataTable class to store those kind of objects.
This DataTable has functions to add columns and rows and get their values so that we can extract information from DataTable with easy and use same methods repeatedly.
Also we can get DataTable object value on another page with SESSION functionality.

Continue reading

PHP Example Retrieve DB2 Records Per Page

Retrieving large recordset contains so many rows on web page will consume a lot of resources both server and client. Responsiveness of web page decreasing.

We need to retrieve that recordset part by part and display it using web paging method. In my previous post about Limiting DB2 Record Number I had show sql example to limit large recordset on SAMPLE Database.

This php example show employee’s related project:
Continue reading

Limiting Record Number on DB2

DB2 has capabilities to limit recordset retrieved from ‘select’ statement. In these example, I use SAMPLE database with DB2 LUW.
To limit show only several top records use

select * from employee fetch first 10 rows only

This will show only to 10 records from employee table.
Continue reading

PHP Database Wrapper Class

Many organizations use multi database software. For example an organisation’s HRD system using SQL Server and on its ERP they use DB2.

To develop an application that can connect to DB2 and SQL Server using PHP, we need database-independent connection object.
Continue reading