Computing: Website and Database Programming

Web development environment setup on MS Windows.

7.6.	IBM DB2 database server.
7.6.0.	DB2 distributions.
	DB2, the relational database from IBM, is not only available for mainframes, but also for PCs. There are distributions for Windows and Linux (not for macOS), both commercial and free. The PC version of DB2 is called DB2 for Linux, Unix and Windows; DB2 Community Edition is a free version of DB2 LUW, certainly with some restrictions, but fully functional and a great opportunity to learn about this "really professional" database. To download the database software from the IBM Db2 Download Center, you'll have to create an IBM account. Logging in with this account will also allow you to download further software, such as Data Management Console, Visual Studio Code Extensions, and drivers. The RDBMS used in this tutorial is DB2 v11.5.8, running on Windows 10.
7.6.1.	Installing DB2.
	The download file v11.5.8_ntx64_server_dec.exe is a self-extracting archive, that launches as a dialog box, where you can choose the temporary directory where the files should be extracted. Push the Unzip button to extract the files. After all files have been extracted, browse to the temporary directory, to where the extraction has been done, and launch the setup application. In the left pane of the Welcome window, select Install a product and then, in the right pane, scroll down until you find the DB2 Community Edition database server. Push the Install New button to start the installation wizard. After an introductory window and the acceptance of the license agreement, you have to choose an installation type. Selecting Typical (screenshot on the left) should cover all your needs in most cases. As you can see when pushing the View Features button, this option not only installs the database server, but also client support, basic development tools, various drivers and "First Steps" (that will allow us to easily create a sample database). In the next window (no screenshot), you are asked if you want to install DB2, to save your answers in a response file, or both (the most obvious choice). Not 100% sure how DB2 manages file names containing spaces, I changed the installation directory from "C:\Program Files\IBM\SQLLIB" to "C:\Programs\IBM\SQLLIB" (screenshot on the right). In the next window, you'll have to enter username and password for accessing the DB2 Administration Server (DAS), that provides support required by the other DB2 tools. The default user name is db2admin. You should let it, as is, then choose a password according to the DB2 password rules. The value of the "Domain" field is set to "None – Use local user account"; this is always the case, unless your computer is part of a Windows domain. To avoid any authentication problems, be sure that "Use the same account for the remaining DB2 services" is checked (screenshot on the left). The next window gives you the opportunity to configure the DB2 instance. I did not change any settings here (using all default values) and simply pushed the Next button to continue setup (screenshot on the right). You will probably not need the email notification feature, thus you should uncheck the option to set it up (screenshot on the left). Except if you really need it and are sure what you are doing, disable operating system security (by unchecking this option (screenshot on the right). In fact, DB2 user management is really easy (DB2 will use the Windows accounts). However, if you enable operating system security, things will become complicated and special user configuration steps will be required to access DB2 databases. The setup program has now all information that it needs. Push the Finish button to start file copy and configuration of the DB2 instance (screenshot on the left). When all is done, an informational window tells you that the database server is actually running on port 25000 (at least on my system), where you can find the installation log, and that there are some optional steps that you can perform (screenshot on the right). Before quitting the installation wizard, you have the possibility to install additional products. On my system, the wizard proposed to install IBM Database Add-Ins for Visual Studio. As I haven't Visual Studio installed, I did not install this add-in. When the setup wizard is terminated, DB2 First Steps should start automatically (if it doesn't, you can launch it from the Windows Start menu). From the DB2 First Steps Welcome page, you can check for product updates, get access to the DB2 documentation (Internet required), download IBM Data Studio (cf. further down in the text), and install the SAMPLE database. To create this database (allowing you to test your DB2 installation and to play around with your new software), push the Create sample database button. The database creation is fully automatic, the only information, you will be asked for, is the drive where to store the files. Note:For details concerning the stricture and the data of the SAMPLE database, please have a look at the chapter The SAMPLE database in the IBM DB2 documentation.
7.6.2.	Connecting to a DB2 database.
	The typical installation of DB2 Community Edition includes several tools, accessible in the Windows Start menu. Among these, you have DB2 Command Line Processor, a text-based database client, similar to mysql. It allows to enter special commands such as "connect" (to connect to a database), or "quit" (to exit the shell), as well as SQL statements. Lets try it out. Launch the program, and enter the command     connect to sample Surprised that this worked? Connecting without entering a user name and a password? If you have a look at the screenshot, you see that the SQL authorization ID for the actual connection is "Admin". This is not a special DB2 user, but the name of my Windows user, I was logged in when installing DB2 and also the one, I was logged in with, when running DB2 Command Line Processor and connecting to the SAMPLE database. In fact, DB2 automatically creates a database user for each Windows user, using the same credentials as in Windows. If you connect to the database without specifying a user name, the user currently logged in (in our case "Admin") is used. If you want to connect as another user, this user must exist on your Windows system, and you connect to a database using the command:     connect to <database> user <username> using <password> where <password> is the Windows password of Windows user <username>. Isn't this a security issue on a multiple-users system? No! First, you can enforce security by enabling the operating system security feature during the server installation. In this case, Windows users have only access to the database server if they are member of the Db2 Administrators or the Db2 Users group. Second, all databases are created within a schema that has the name of the database creator, and it's only this user who has access to the database objects in this schema. You can display all tables of the database, that you are connected to, using the command     list tables As you can see on the screenshot below, the tables of the database SAMPLE have been created within the schema "Admin". The DB2 Command Line Processor allows you to directly enter SQL statements. For example, lets display a list (last name and first name) of all employees ("employee" table of the SAMPLE database). Simply enter the SQL statement:     select lastname, firstnme from employee order by lastname, firstnme Two further examples to illustrate the concepts "SQL authorization" and "schema". Connect to the SAMPLE database as "db2admin" (the DAS administrator account, that we created when installing DB2). Try to list the tables. All you get is an empty list (0 records selected). The reason is simple: As the actual SQL authorization is "db2admin", DB2 searches for tables in the "db2admin" schema and, as all tables in the SAMPLE database belong to the schema "Admin", there are no records available for the actual query. To access database objects, that belong to a schema with a name that is different from your SQL authorization, you'll have to prefix the database object by the schema name. As an example, as user "db2admin", lets display the number of records in the "employee" table of the SAMPLE database.     select count(*) from Admin.employee Whereas without prefix, you get the error message DB2ADMIN.EMPLOYEE is an undefined name (SQLState: 42704), using the prefix will result in another error message: The statement failed because the authorization ID has not the required authorization or privilege to perform the operation (SQLState: 42501). Only "Admin" is allowed to access the objects within the "Admin" schema. If we want to give access to them to another user, we'll have to grant the corresponding privileges to them (cf. further down in the text)!
7.6.3.	Accessing DB2 with IBM Data Studio client.
	The most obvious GUI application to manage DB2 databases and their data content is IBM Data Studio, that you can download from the IBM Db2 Download Center. I actually use version 4.1.4.0. Unzip the download archive (in my case ibm_ds4140_win.zip in a temporary directory. Among the extracted files, you'll find two further archives, one to install IBM Installation Manager (in my case this ZIP is called: agent.installer.win32.win32.x86_64_1.9.1006.20210614_1906.zip), and one to install IBM Data Studio client (in my case this ZIP is called: com.ibm.dsida.im-offering-build-4.1.4-20211124.160709-33-im-offering.zip). IBM DB2 software is organized in packages and should be installed using the Installation Manager. So, lets start by installing IBM Installation Manager. Unzip the "agent.installer" archive and run the extracted "install" application. The installation shouldn't be any problem. Accept the license agreement, accept the default installation directory (or specify a custom one, if you want), and that's it. Now, the installation of IBM Data Studio client. Unzip the "dsida" archive, then launch IBM Installation Manager (a shortcut has been created in Windows Start menu). All DB2 packages are installed from repositories, that can either be on some IBM server, or "somewhere else", as for example on the local computer. We will install Data Studio from a local repository, finding its configuration in the folder where we extracted the "dsida" ZIP. In Installation Manager, open Preferences and select Repositories in the left pane. In the Repositories pane, choose to add a repository by pushing the corresponding button (screenshot on the left). In the opening Select a Repository window, browse to the extraction folder and select the file repository.config (screenshot on the right). The path to the repository is added to the repository list and the package appears with the status "will be installed" in the Install Packages window. Push the Next button to start installation. After acceptance of the license agreement, you have the possibility to change the shared resources directory (be careful when doing this!), to change the installation directory, to add translations in other languages, and finally to choose what components you want to install. I chose to install the full product options. When starting IBM Data Studio client, you have to choose a directory as workspace (the default is ok). If you check "Use this as the default and don't ask anymore", this directory will be used for all subsequent Data Studio sessions. If you are running Windows Firewall, you will get an alert that some features of the Java platform SE have been blocked. I think that you should (must?) allow Java to communicate on private networks. In the Task Launcher window, Overview tab (opened at startup), go to the Getting Started section and click the Connect and browse a database. Available databases are now listed in the right pane of the window. Right-click on SAMPLE and choose Connect from the context menu. In the Driver Properties for SAMPLE window, fill in user name and password, and push the Apply and close button. In comparison with database GUIs like MySQL Workbench, HeidiSQL, pgAdmin, or mySQLAdmin, IBM Data Studio client seems complicated to use. But, I think that if I have this impression, it's primarily due to the fact that I'm not used to work with a GUI that has a layout based on these modern concepts like "activities", "tasks", "perspectives" and "views". Anyway, if you choose the activity Administer databases, all available databases are displayed in Administration Explorer. Here you can expand the SAMPLE database, viewing its objects. The screenshot below shows a list of the tables in the SAMPLE database. Surprised that there are 170 tables, some 85% of them (in schema SYSIBM) being internally used by the RDBMS? DB2 is huge and meant for professional usage. Standard RDBMS on IBM mainframes, I guess it's rather rarely used on personal computers.
7.6.4.	Configuring a read-only user.
	As far as I know, there is no way to set DB2 privileges on a global and even not on a database level. So, if we want to configure a user to have read access to all tables of our SAMPLE database, we must explicitly grant them this privilege for each table. Concerning the user, we will not have to create them, as we are used to do on MySQL, MariaDB and others. DB2 users actually are Windows users, so we will use an existing Windows user as our read-only DB2 user. I installed my Windows 10 with 2 users: "Admin", a local administrator account, and "Allu", a local limited account; it's this last one whom we will use as DB2 read-only user (if your Windows has one single user, you'll have to create another one before proceeding (if you have problems to create a local account on Windows 10, please have a look at the article 4 Ways to Create a Local User Account in Windows 10 on the MUO website). We will configure our user using SQL. I'm not sure if this is the simplest way to enter SQL statements in IBM Data Studio client, but here, how I did proceed: In Task Launcher I opened the Develop tab and in the tasks listed, I choose Create and run SQL and XQuery statements. This opens the New SQL or XQuery script window, where you can enter a project and script name, as well as if you want to use the SQL and XQuery editor or the SQL Query Builder. Pushing the Next button will open the Connection profile window, where you have to define the connection to be used for creating and executing the new script. If you connected to SAMPLE as "Admin" when starting IBM Data Studio client, you'll nothing to enter here. Just select the profile listed (host=localhost, database=SAMPLE, user=Admin). Push the Finish button to launch the SQL and XQuery editor. Instead of granting the SELECT privilege on the different tables directly to user "Allu", we will create a new role, grant the SELECT privileges to this role, and then grant this role to "Allu". This has the advantage that we can later on grant the SELECT private on the SAMPLE database to another user with one single SQL statement. To create the new role (I named it "ReadOnly"), enter the following SQL statement:     create role ReadOnly; and push the Run button (green circle with the white arrow-head). Now, lets grant the SELECT privilege on the tables in the SAMPLE database to the new role; general syntax of the SQL statement:     grant select on Admin.<table-name> to role ReadOnly; and then grant the "ReadOnly" role to user "Allu":     grant role ReadOnly to user Allu; What remains to do is allowing user "Allu" to connect to the SAMPLE database. SQL:     grant connect on database to user Allu; I used the DB2 Command Line Processor to test my new user. The following three commands will 1. connect user "Allu" to the SAMPLE database ("AlluUser0" is the Windows password of "Allu"); 2. display the number of records in the "employee" table (successful query, as we granted "Allu" the SELECT privilege on this table), and 3. trying to update a record in the "employee" table (operation failure, because user "Allu" has no UPDATE privilege on the table).     connect to sample user Allu using AlluUser0     select count(*) from Admin.employee     update Admin.employee set edlevel = 16 where empno = '000010'
7.6.5.	Accessing DB2 databases with PHP.
	To run PHP scripts, you need to have a webserver and the PHP scripting language installed on your computer. If you haven't yet done so and aren't sure about how to do it, the tutorials Web development environment setup on MS Windows: Apache and Web development environment setup on MS Windows: PHP might be helpful. Accessing a DB2 database with a PHP script is easy and without issues, if you consider that the PHP version and the version of the PHP driver must be compatible. Trying to access DB2 from PHP 8.2 failed. No drivers for this version available, I tried with the drivers for version 8.1, but for both ibm_db2 and pdo_ibm the script aborted, in the first case with the error message Call to undefined function db2_connect(), in the second case with a could not find driver message. The latest version of PHP that worked well when I tried the sample scripts of this tutorial (September 2023) was PHP 8.1.23 (the 64bit thread-safe version for Apache), that you can download from the PHP For Windows website (file name: php-8.1.23-Win32-vs16-x64). The PHP extensions for IBM DB2 may be found at the php_ibm_db2 page on the GitHub site. Be sure to download the drivers from the folder PHP 8.1.x (or more correctly: the folder with the name corresponding to the PHP version that you actually use) (file names: php_ibm_db2.dll an php_pdo_ibm.dll). Copy the two DLLs to the ext subdirectory of your PHP installation folder. Then, edit the php.ini file, adding the following two lines (important: you have to restart Apache after you have modified php.ini!):     extension=ibm_db2     extension=pdo_ibm Here are two elementary PHP scripts (the first one using ibm_db2, the second one using pdo_ibm) that do not more (and not less) than connecting to the SAMPLE database and telling if the connection succeeded or failed.     <?php         $database = SAMPLE;         $user = "allu";         $password = "AlluUser0";         $conn = db2_connect($database, $user, $password);         if ($conn) {             echo "Connection succeeded.";             db2_close($conn);         }         else {             echo "Connection failed.";         }     ?>     <?php         try {             $connection = new PDO("ibm:SAMPLE", "Allu", "AlluUser0", array(                 PDO::ATTR_PERSISTENT => TRUE,                 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)             );             echo "Connection succeeded";         }         catch (Exception $e) {             echo($e->getMessage());         }     ?> I saved the scripts as db2connect.php and db2connect2.php respectively in the root directory of my Apache webserver. The screenshots below show the execution of the scripts. On the left, the ibm_db2 script accessed with the URL localhost/db2connect.php (successful execution); on the right the pdo_ibm script, after having changed the source by removing the "0" from the value of $password, accessed with the URL localhost/db2connect2.php (execution failure, because the password is invalid).
7.6.6.	Accessing DB2 databases with Perl: FAILURE!
	There is a DBI::DBD driver for Perl available (DBD-DB2-1.89), however its installation fails, in a similar way as fails the installation of the Firebird driver. Trying to access the database server using ODBC also fails: the script hangs, no error message, just turning and turning until you close the web browser. No idea, what's the reason of the problems of Strawberry Perl (I tried versions 5.32, 5.28 and 5.20) with the DBI::DBD drivers. Concerning ODBC, is it possible that I did miss something? Is there a compatibility problem? Here again, no idea!
7.6.7.	Accessing DB2 databases with Python: FAILURE!
	The DB2 package for Python is called ibm_db. I installed it using pip (version ibm_db-3.2.0), after having installed setuptools (version 68.2.2). The installation succeeded without any problem. I also installed ibm_db_sa (version 0.4.0); the installation of ibm_db_django failed. Running a simple DB2 Python script, however, failed. Error message: DLL load failed while importing ibm_db: The specified module could not be found. Do I have to copy the DLL to some specific directory? Or, doesn't it work because the version of ibm_db is not compatible with my version of Python (I'm actually using Python 3.11.5, 64bit)? After having installed pyodbc (version pyodbc-4.0.39), I tried to access the database server using ODBC. No chance! Same problem as with Perl: the script hangs, no error message, just turning and turning until you close the web browser.
7.6.8.	Accessing DB2 databases with Lazarus/Free Pascal.
	Free Pascal does not provide a driver for DB2 database access, so the most obvious solution is to use ODBC. This works without any problem, as you can read in my tutorial Using IBM DB2 databases with Lazarus/Free Pascal in the Lazarus/Free Pascal Programming section of my site.
7.6.9.	Accessing DB2 databases with Java.
	Accessing DB2 with Java works well. This is not surprising. Accessing the database server using the JDBC driver is standard IBM procedure. Just look at IBM Data Studio client: it accesses DB2 using a Java connection. The JDBC driver being installed, all we need to do do before writing our Java program is to look up the connection string. You can find it in the Driver Properties of the "connection to SAMPLE" properties in IBM Data Studio client. On my system it actually is: jdbc:db2://localhost:25000/SAMPLE. Here is the code of a simple Java program (I saved it as testdb2.java), based on a sample, that I found on the DB2 via JDBC HOW-TO page on the McGill University (Canada) website. The program displays the number of records of a table of the SAMPLE database. The table to be queried has to be entered as command line parameter; if no table name is specified, "employee" is used by default.     import java.sql.*;     class testdb2 {         public static void main ( String [ ] args ) throws SQLException {             // Unique table names             // Either the user supplies a unique identifier as a command line argument,             // or the program uses the "employee" table             String tableName = "";             int sqlCode = 0;             String sqlState = "00000";             if (args.length > 0){                 tableName = args [0];             }             else {                 tableName = "employee";             }             // Register the driver             // You must register the driver before you can use it             try {                 DriverManager.registerDriver (new com.ibm.db2.jcc.DB2Driver());             } catch (Exception cnfe){                 System.out.println("JDBC driver not found");             }             // Connect to the SAMPLE database             String url = "jdbc:db2://localhost:25000/SAMPLE";             Connection con = DriverManager.getConnection (url, "Allu", "AlluUser0");             Statement statement = con.createStatement();             // Query and display number of records for selected table             try {                 tableName = "Admin." + tableName;                 String querySQL = "SELECT COUNT(*) from " + tableName;                 System.out.println (querySQL);                 java.sql.ResultSet rs = statement.executeQuery(querySQL);                 if (rs.next()) {                     int reccount = rs.getInt(1);                     System.out.println ("Number of records in table " + tableName + " = " + reccount);                 }             } catch (SQLException e) {                 sqlCode = e.getErrorCode();                 sqlState = e.getSQLState();                 System.out.println("DB2 error - sqlCode: " + sqlCode + ", sqlState: " + sqlState);             }             // Finally but importantly close the statement and connection             statement.close();             con.close();         }     } To build Java programs, you need to install a JDK. I chose to install Microsoft Build of OpenJDK with Hotspot 21+35 (x64). To build our program, launch Command Prompt and enter the command: javac testdb2.java The build should succeed and the file testdb2.class should be created. To run the program, use the command: java testdb2 [<table-name>] The screenshot shows the build and several executions of the program: running it without a command line parameter, running it specifying a valid table name, running it specifying an invalid table name.

---

If you find this text helpful, please, support me and this website by signing my guestbook.