Author Archives: Duncan Kenzie

Are your WebSmart and Presto programs getting SSL certificate errors?

If your WebSmart and Presto programs use SSL (Secure Sockets Layer) then you most likely installed an SSL certificate on your server. Browsers recently started phasing out a common method of encryption (called SHA-1), which may be causing errors in browsers when users visit your site. These errors typically occur in Chrome and Firefox, even when the certificate is valid.

Continue reading

5 Reasons To Use SQL instead of DDS

Many of us have used DDS for years to define our databases. In recent years SQL has become more prominent on platforms other than the IBM i, but there still seems to be reticent to adopt SQL instead of DDS. Here are five reasons why you should consider using SQL instead of DDS from now on.  IBM also has a great redbook on SQL with lots of great tips on how to use SQL features and how to move from DDS to SQL.

Reason 1: Everyone else is doing it!
I know- when our kids come to us with this reasoning we usually dismiss their argument. However, it’s true that SQL is driving database design and management on almost all major platforms and software development environments.  By learning SQL you will have skills you can transfer to any development project and your employer will benefit with you having cross-platform skills.

Reason 2: IBM is continually enhancing SQL support on IBM i. They have stopped enhancing DDS.
IBM recognizes that SQL is the way of the future. DDS is a proprietary language, used only on the IBM i platform. Any time IBM adds new features to the database they add those features to SQL. For example, enhanced transaction processing is only available in SQL. Improved index management is only available in SQL

Reason 3: SQL described tables and indexes can often perform better than DDS logical files.
This is because DDS-described files check to rebuild/update indexes when data is read from a file, while SQL checks only when the underlying table is updated. Since most applications perform read-only operations 8 times more than read/write ops, this yields a performance advantage

Reason 4: SQL  DDL (Data Description Language) is easy to code, and many tools are available to help you.
With DDS you are limited to SEU or RDP  or BCD’s DbGen (which actually hides the coding for you) for describing your physical and logical files. With SQL there are many tools available, some open source. Or, coding it by hand is simple too.

Reason 5: You can phase in SQL tables and indexes with minimal impact on existing applications.
You can convert your physical and logical files to SQL tables and indexes without having to change the underlying code.  IBM provides a conversion command that lets you extract SQL table definitions from physical files to give you a head start.

How To Create a WebSmart Template that Converts Data from DB2 to MySQL

This post shows some of the features in the intelligent templates in WebSmart, and how to use those features to write your own powerful customized templates.

Recently, I wanted to convert the data in DB2 physical files to MySQL tables, so that I could easily take advantage of the IBMDB2i database engine for MySQL. I already have a nifty DB2 to MySQL code generator, which you can download for free from our website.  However, I didn’t have an easy way to import data from files.  So I decided to write a WebSmart program to do it. Using phpMyAdmin, I can then cut and paste the output from this program into the SQL text area, and run it. The output needs to look something like this (for a fictitious file):

INSERT into `CUSTOMERS` (`custno`, `custname`, `crtdate`, `crttime`)
VALUES (1, 'Acme Bolts', '2010-06-02', '15:35:12'),
(2, 'Superior Widgets', '2010-06-03', '16:04:35');

You can import tables into MySQL using the phpMyAdmin interface or a command line interface from the PASE environment on the IBM i. In phpMyAdmin, you choose the ‘Import’ option and select a plaintext file with a .sql extension from somewhere locally. So if I save the above code in a text file called customer.sql, I go to phpMyAdmin, choose my database, click the Import link, select the file customer.sql and voila! I have a customer MySQL table, complete with 2 rows of data. Continue reading

jQuery UI Autocomplete, JSON and PHP

In case you are not familiar with the term ‘autocomplete,’ it refers to the kind of functionality you get with Google mail when typing a ‘to’ email address, or in Google Suggest.  As you type characters in the search box, it ‘autocompletes’ by providing you a list of matches. The list gets refined the more you type.  We also have  a WebSmart example you can try out live.  Autocomplete widgets can help make your applications more user-friendly, because they make it much easier for users to find items in large sets of data.

We’ve used Autocomplete plugins in a number of our web applications (both for customers and internal use) for some time now. I just stumbled upon the relatively new jQuery UI Autocomplete plugin. There is some good example code at the jQueryUI site.  Another good tutorial on how to use it is this one.

This post explains how to code it for a typical usage. Note that although I am using PHP here, you can accomplish exactly the same thing with PML and DB2 SQL in WebSmart ILE.

Here’s a sample image from a work in progress (new customer log system using jQuery, AJAX, PHP and MySQL). I’ve blurred the customers, as this is real data from our files.



Let’s look at the code required to implement this. First, the client-code (jQuery and JavaScript).

Client Code

			minLength: 3,
	 	    delay : 400,
			source: function(request, response) { 

				   url: 	 "index.php",
				   data:  {
				   			mode : "ajax",
				   			component : "consearch",
				   			searcharg : "company",
				   			task : "display",
				   			limit : 15,
				   			term : request.term
				   dataType: "json",

				   success: function(data) 	{



		   select:  function(e, ui) {
				var keyvalue = ui.item.value;
				alert("Customer number is " + keyvalue); 


The important pieces here are the source property and the select event. The source property really defines 2 key aspects of the autocomplete widget: the source (input) of the data to populate, and how to put it in the autocomplete box (formatting, etc).

First, it defines the source of the data. This can be a local Javascript array – useful for small, relatively static data sets such as State abbreviations. It can also be a string with a value of a URL. The URL will point to a script (see below for example code) that gets invoked via AJAX and returns an array of items. While this may seem the obvious choice for most cases, it’s really not that useful because you have no control over how you format your response data. The third possibility, as shown in the example code above, is an anonymous function. This is the most useful coding pattern. The function accepts two parameters- request and response. Request is an object that has a property called term, whose value is whatever the user types in to search for. So, if the user types ‘AME’, then request.term == ‘AME’. Response is a callback function name that implicitly accepts one parameter, the data to display in the autocomplete results box. This is poorly explained and documented on the jQuery UI site. I only really understood this by reading Dan Wellman’s article. Look at the client code above and you’ll see this line:


That’s a call to the manufactured callback function. You’ll notice, also, that in order to actually do anything, you have to explicitly code the ajax call in the callback function for the source property. This is quite a different coding pattern than what we used with the ‘old’ jQuery autocomplete plugin, but the whole design of this code is so much more elegant and flexible.

The other key piece in the client-side code is the select event. This event is triggered when the user clicks on one of the values in the list. In my example code this just causes an alert box to popup that shows the value of the customer number. The select callback function accepts 2 parameters- the event object (not sure why you’d want that) and the ‘selected item’ object (which I’ve called, by convention, ‘ui’). Note that ui has an object called ‘item’ as one of its properties, which in turn has ‘value’ as a property. Hence: ui.item.value refers to the value of the selected line. You can have other, custom properties besides the 2 standard ones (‘value’ and ‘label’), as shown in this example.

I could not find formal documentation describing ui.item, only example code.

In the real world, the next step would be to do either a redirect or an AJAX call to a script/program that lets me work with my selected customer. I’d likely include the customer key in the url, for example:

  var surl = "index.php?task=edit&custno=" + ui.item.value;

Next, let’s look at the server code.

Server Code

	$searcharg  = getparm('searcharg');
	$limit = getparm('limit');
	if (!$limit) $limit=10;
	$sercontact = '';
	$sercompny = '';
	$term = getparm('term'); 

	 	$sercompny = strtoupper($term);
		$query = "SELECT  CLCLNU, CLCLNA FROM CS_CLTF WHERE clclna like '%" . trim($sercompny) . "%' order by CLCLNA";  

	$query = $query." LIMIT  $limit";  // Limit results based on the limit parameter from the ajax call. 

	$rows = $db->get_results($query);  // Populate result set
		// While we retrieve a row from the result set:
	if ($rows)
		foreach($rows as $row)
			$companies[$count] = array("value" => $row->CLCLNU, "label" =>  $row->CLCLNA);
		echo  json_encode($companies);

(FYI I’m using the EZSql library here to do my SQL coding. EZSql is a PHP library that makes coding SQL statements simpler, and supports multiple databases. A comparable library is PDO, part of Zend Frameworks. EZSql is Free and Open Source).

Note the use of json_encode. What this does is produce a JSON-formatted  array of  objects. Each object has a property of “value” (the customer number – key to the customer table) and “label” (customer name- what appears in the autocomplete). Even though I assign these values to an associative array, json_encode treats it like an object when it translates it (which makes sense when you think about it, and is documented on as intended behavior).

To be a little more clear, the returned data from the PHP script to the AJAX callback will look something like this:

[{"value":"3215","label":"COMMUNICATION INC."},
{"value":"8243","label":"ACME MANUFACTURING COMPANY"},
{"value":"5713","label":"ABCDEFS COMPANIES, INC."},
{"value":"4869","label":"ABERYSTWYTH AVENUE COMPANY"},
{"value":"223","label":"ALLKINDS COMPRESSOR"},
{"value":"3705","label":"MIGHTY INSURANCE COMP"}]

It seems to me this Autocomplete plugin is way better than the jQuery autocomplete plugin I’ve used before, particularly since it uses the jQueryUI CSS framework to style the autocomplete dropdown box. It’s more flexible, you can define the code in one object call instead of two, and it’s more likely to be supported and maintained by the jQueryUI team.

Using SQL Performance Monitor To Find SQL Errors on IBM i

It’s difficult to find which program is producing an SQL error by looking in the joblog, especially for Apache instance jobs (CGI jobs). You can see the error, but you can’t find out the name of the program that issued it. To solve this problem, you can use SQL Performance Monitor tools. You can start an SQL Performance Monitor one of two ways:

  • Use System i Navigator, expand the Databases tree node for the system you are connected to, right-click on SQL Performance Monitors and create a new one.
  • Run command STRDBMON from a command line.

You can limit which jobs get monitored (with either approach). For example, if you want all the INTERNET Apache instance jobs, just specify a job name of INTERNET.

To end the monitor, use the same interface in System i Navigator, or use the ENDDBMON command.

Make sure you create the monitor to collect detailed, not summary information. You can also choose the file and library name in which to store the collected information.

Don’t leave the monitor running for too long- it has a negative performance impact on the system, and it writes lots of data quickly.

You can easily query the output table to find errors. For example, I started a monitor via the System i Navigator which output data to a table called QZG0000048 in library DUNCAN. Using STRSQL, you can find SQL errors and their corresponding program names. Use this query:

SELECT qqi8 as "sqlstate",qqc81 as "sqlcode",qqc104 as
"program library",qqc103 as"program name",qqc21,qq1000 FROM
qzg0000048 WHERE qqrid=1000 and qqi8 < 0 ORDER BY qqstim

There are lots of other fields in this file, too, such as job name, user name, etc. but be careful, because if you try to select all the fields for viewing using STRSQL your 5250 session might end abnormally – some of the fields contain binary data that is illegal to display.