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.
This blog post demonstrate on how to run iSeries Command using VB.Net. This is needed for example to change user ID password via an application.
Schedule Task also might be used it to perform some jobs that have conditional things or validation.
In order to run AS/400 Command via Visual Basic,you need a pre-requisite. You have to install IBM iSeries Client Access and make reference to IBM.Data.DB2.iSeries.dll in your application.
In my computer the library is located in C:\Program Files\IBM\Client Access folder.
Basically to call AS/400 Command, you will use ADO.Net objects and execute
For example, if you have a program called MYPGM that you want to execute, you could send this SQL statement to the AS/400:
CALL QSYS.QCMDEXC('CALL PGM (MYLIB/MYPGM)', 0000000022.00000)
Note: The second parameter must contain exactly 15 digits: ten to the left of the decimal and five to the right.
You can write a program to count the length of the command and create the second parameter for you
Posted in DB2, Visual Basic .Net
Tagged Call As400 Command, Call iSeries Command, CLRPFM, CPYF, CRTLIB, DLTF, DLTLIB, QSYS.QCMDEXC, VB .Net, Visual Basic .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.
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:
Posted in DB2, Visual Basic .Net
Tagged Database, DataGridView, DB2, DB2 LUW, DB2Command, DB2Connection, DB2DataAdapter, IBM.Data.DB2.dll, MemoryStream, VB .Net, Visual Basic
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
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.
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.
We can connect to DB2 with graphical or text based. IBM DB2 Data Studio or DB2 Control Center are graphical tools to operate with database object like create database, table, insert/update/delete records and run queries using graphical command editor.
Also we can work with DB2 on text based using DB2 Command Window. Text based is good for Linux Server OS and remote administration using SSH because its usually use a lot of text based commands.
Installation of DB2 Linux, Unix, Window (LUW) contains server and client tools. That means DB2 LUW can act as server and also as a client.
Every database connection on DB2 registered on catalog. Database has parent node catalog. So catalogues are tree view looks like. Local databases are automatically registered in database catalog.
To connect to remote database server we need to make catalog for the remote connection. After remote catalog has been registered, remote server will appear as local.
Instance in database can be described as background process and memory structure or environment that contains access to databases.
Instance can be usefull to separate development, test, and production environment on one database server. Also you can manage multi-tenant/customers into several instances. So, it easier to manage multi customer application/database rather than combining into one database.
One of advantages using DB2 as database server is platform independent. We can install it on Windows as well as open source OS such as Linux.
After the installation process has done, we need to check whether its process is good or has any errors.
We can do check using command window with following syntax:
- db2level : displays DB2 product installed, program directory, used bits, code release, instance, fix pack level etc
- db2licm -l : lists all licensing and maximum resources information
- db2val : validates your installation. It checks instance and other settings are correct.