From YiPs Wiki (i powered)

I5: Data Base 101 Ibm Db 2

(click to open)

Quick Page Table of Contents

Scanning…

Database 101 with ibm_db2 model

Introduction

In this case we replaced the RPG back-end model with a PHP ibm_db2 model that does the essentially same job without calling the RPG SRVPGM.

Why MVC?

We are using the same PHP view/control code as the previous i5 call RPG program example, except a different include_once for the MVC ibm_db2 model.

In this example we replaced the RPG back-end with a PHP ibm_db2 model, therefore we have a new DB2 model include_once to handle connection to ibm_db2 (no RPG).

<?php
define('APP_BASE_DIR', '');
include_once APP_BASE_DIR.'101Config.php';      /* Site:    site configuration         */
include_once APP_BASE_DIR.'101Connectdb2.php';  /* Model:   ibm_db2 connect            */
include_once APP_BASE_DIR.'101DB2Model.php';    /* Model:   ibm_db2 SQL                */
include_once APP_BASE_DIR.'101HtmlView.php';    /* View:    plain old generated html   */
include_once APP_BASE_DIR.'101HtmlControl.php'; /* Control: main loop html forms       */
?>

PHP Get Records (ibm_db2 model)

Why use db2_pconnect (over db2_connect)?

We are using ibm_db2 persistent connections with API db2_pconnect() (not db2_connect()), to improve performance of the overall web-site under heavy user web demand. Use of persistent connections is absolutely required if your site services multiple requests per second. If you forget and use db2_connect(), you can set php.ini setting to force all db2_connect to db2_pconnect.

function model_connect() 
{ global $MODEL;
  $db_options = array("i5_naming"=>DB2_I5_NAMING_ON);
  $MODEL ['conn'] = db2_pconnect 
  ( $MODEL ['database'],
    $MODEL ['db_user'], 
    $MODEL ['db_password'],
	$db_options
  );
  if (! $MODEL ['conn']) 
  { model_error_db2("Connect failed");
    return False;
  }
  return model_chglibl();
}

We are using array(“i5_naming”=>DB2_I5_NAMING_ON) for two reasons:

likely need the libl set.

Tip: If the whole site is DB2_I5_NAMING_ON everything just works better on IBM i. Mixing DB2_I5_NAMING_ON ($lib/$table) and DB2_I5_NAMING_OFF ($lib.$table) nearly always leads to trouble on the site.

How do I set the libl?

In a Apache “stateless” environment like this sample, we can never count on the library list being set correctly. So, we have added db2_set_option (chglibl) code to the generic connection.

function model_chglibl()
{ global $MODEL;
  $rc = db2_set_option
  ($MODEL ['conn'],
   array('i5_libl'=>$MODEL ['libl']),
   1
  );
  if(!$rc)
  { model_error_db2("chglibl fail");
    return False;
  }
  return True;
} 

Note: On older versions of ibm_db2, option ‘i5_libl’ is not available, so we need to call the V5R4+ provided stored procedure call qsys2/qcmdexc (see below).

How do I call commands from ibm_db2 (set libl old way)?

IBM i V5R4+ provided a stored procedure call qsys2/qcmdexc(‘cmd’,cmdlen) for ‘native’ commands within a DB2 server job. On ibm_db2 older versions older than 1.8.0, you had to set the library list by calling qcmdexc.

  // old release did not support option "i5_libl",
  // so call qsys2/qcmdexc('cmd',cmdlen)
  // by hand (only V5R4+)
  $hack = phpversion('ibm_db2');
  if ($hack[0]=='1' && $hack[2]<'8')
  { $cmd = "chglibl libl({$MODEL ['libl']})";
    $len = strlen($cmd);
    $sql = "call qsys2/qcmdexc('$cmd',$len)";
    $rc = db2_exec
    ($MODEL ['conn'], 
    $sql
    );
  }

Why do browser clicks of db2_pconnect route to different Apache jobs?

It should be noted that persistent db2 connections only means that each “stateless” Apache job will have a common connection to db2 to be used by ALL PHP ibm_db2 applications running, not a single application or “job”. Persistent connection does NOT mean that each browser click will return back to the same job, so do not attempt to db2_commit() transactions across multiple browser clicks.

Why is there many QSQ jobs with db2_pconnects?

If you supply a user id and password to the db2_pconnect, you will also attach a QSQ db2 server process for each different user profile specified for each Apache worker job. Therefore it is good practice to limit the number of “active” web profiles. However, after the QSQ job is attached there is little performance difference between profiles (assuming db2_pconnect).

Why is db2_connect(“*LOCAL”,”userid”,”pwd”) so slow (non-persistent)?

As mentioned in the previous question, a a QSQ db2 server process is attached for each different user profile specified for each Apache worker job. However in the case of db2_connect, the QSQ job starts/stops with each db2_connect / db2_close, which is a performance nightmare for heavy traffic sites of many requests per second.

Should I use db2_connect(“*LOCAL”,”uid”,”pwd”) or db2_pconnect(“”,”“,”“)?

Mixing in-line db2_pconnect(“”,”“,”“) and db2_pconnect(“*LOCAL”,”uid”,”pwd”) can lead to unpredictable results, so it is best to just pick a profile or two to handle web clients.

Why use db2_prepare and db2_execute?

Here is the ibm_db2 code that replaces our RPG back-end. Please note the ibm_db2 MVC model function names are exactly the same as the i5 toolkit model function names (model_search), therefore when we replace the model code with ibm_db2, and the view and control code do not have to change (5250 code also can remain the same).

We are using the db2_prepare and db2_execute API combination to allow the DB2 engine to optimize and reuse SQL statements many times. On high volume web sites that have many DB2 calls to complete these APIs can provide a performance boast over db2_exec (SQLExecDirect).

<?php
function model_search($browsetype, $browse_title, $browse_actor, $browse_category, $limit_num, &$items) 
{ global $MODEL, $CATEGORIES;
  if (!model_connect ()) 
  { return False;
  }
  $link_id = $MODEL ['conn'];
  $items = array ( );
  $prepare = null;
  $sql = "";
  switch($browsetype)
  { case 'title':
      $sql .= "select * from products";
      $sql .= " where TITLE like '%$browse_title%'";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = db2_prepare( $link_id, $sql);
      break;
    case 'actor':
      $sql .= "select * from products";
      $sql .= " where ACTOR like '%$browse_actor%'";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = db2_prepare( $link_id, $sql);
      break;
    case 'category':
      // oops did not plan ahead for this
      for ($i=1;$i<=count($CATEGORIES);$i++)
      { if ($CATEGORIES[$i-1]==$browse_category)
        { break;
        }
      }
      $sql .= "select * from products";
      $sql .= " where CATEGORY = $i";
      $sql .= " FETCH FIRST $limit_num ROWS ONLY";
      $prepare = db2_prepare( $link_id, $sql);
      break;
    default:
      break;
  }
  if (!$prepare)
  { model_error_db2("prepare $sql");
    return False;
  }
  // execute prepared statement
  $execute = db2_execute($prepare);
  if (!$execute)
  { model_error_db2("execute");
    return False;
  }
  // fetch the row data
  $i=0;
  while ($row = db2_fetch_assoc($prepare))
  { array_push
      ( $items,
        array
        ( $row["PROD_ID"], 
          $row["TITLE"], 
          $row["ACTOR"], 
          $row["PRICE"]
        )
      );
      $i++;
  }
  if (!$i)
  { model_error_db2 ( "No DVDs found" );
    return False;
  }
  return True;
}

You can see the advantage of db2_prepare / db2_execute in the following example used to populate the database 101 table for this example.

// Populate the products table
$products = 
array
( // 1= Action
  array(1,   1,     'Death Car',          'Brad Baldwig',      3.22,   1),
  array(2,   1,     'Super Car',          'Gwen Midriff',      3.22,   1),
  // 2 = Animation
  array(3,   2,     'Happy Frog',         'Fred Flimflam',     6.22,   1),
  array(4,   2,     'Happy Toad',         'Fred Flinflam',     6.22,   1),
  // 3 = Horror
  array(5,   3,     'Creek of Doom',      'Chris Wock',       25.99,   1),
  array(6,   3,     'River of Doom',      'Chris Wock',       25.99,   1),
  // 4 = Sci-Fi
  array(7,   4,     'Alien of Troy',      'Chris Wock',       25.99,   1),
  // 5 = Sports
  array(8,   5,     'Runner Beware',      'Bruce Henpecked',   5.31,   1),
  // 6 = Travel
  array(9,   6,     'Ships, ships, ships','Bear Greenwood',    3.33,   1),
);
$insert = "INSERT INTO $lib.PRODUCTS";
$insert .= " (PROD_ID, CATEGORY, TITLE, ACTOR, PRICE, SPECIAL)";
$insert .= " VALUES (?,?,?,?,?,?)";
$stmt = db2_prepare($conn, $insert);
if($stmt)
{ foreach($products as $row)
  { $r = db2_execute($stmt, $row);
    if (!$r) 
    { die("<br>bad insert ".db2_stmt_errormsg() );
    }
    else 
    { $nrows = db2_num_rows($stmt);
      echo ("<br>INSERT $nrows row data = ".implode(",", $row) );
    }
  }
}

Which db2_fetch API choice should I use?

There are many API choices available for ibm_db2, but in general you should find that the following choices give better performance and avoid code bloat over a wider range of applications.

$conn = db2_pconnect("","","");        // persistent connect
$prep = db2_prepare($conn,"sql");      // prepare statement
$retc = db2_execute($prep);            // execute prepared statement
// take your pick of fetch
while ($row = db2_fetch_array($prep))  // one syscall per row (SQLBindCol)
{ $name = $row[0];
  $breed = $row[1];
}
// -- or --                                 
while ($row = db2_fetch_assoc($prep))  // one syscall per row (SQLBindCol)
{ $name = $row["NAME"];
  $breed = $row["BREED"];
} 
// -- or --                                  
while ($row = db2_fetch_both($prep))  // one syscall per row (SQLBindCol)
{ $name = $row[0];
  $breed = $row[1];
  // -- or --
  $name = $row["NAME"];
  $breed = $row["BREED"];
}
// -- or --                                   
while ($row = db2_fetch_object($prep))  // one syscall per row (SQLBindCol)
{ $name = $row->NAME;
  $breed = $row->BREED;
}                                    

All of the db2_fetch options above bring the entire row back to PHP in one system call via the SQLBindCol interface (LOBs are exception with SQLGetLength/SQLGetSubString).

Note: However, there has not been the decades of ILE code running on this PASE CLI SQLBindCol interface, so if you have problems on a given query you may find db2_fetch_row is needed. (We thank you for your patience, let somebody know if you have trouble).

Why is db2_fetch_row / db2_result not generally recommended?

Second tier APIs, should only be used in rare situations, probably only when other fetch options fail. A performance issue with db2_fetch_row / db2_result is that each db2_result field is retrieved by system calling all the way through the i5 kernel via SQLGetData (often slower).

$conn = db2_pconnect("","","");        // persistent connect
$stmt = db2_prepare($conn,"sql");      // prepare statement
$retc = db2_execute($prep);            // execute prepared statement
while (db2_fetch_row($stmt))           // syscall here
{ $name = db2_result($stmt, 0);        // another syscall here
  $breed = db2_result($stmt, 1);       // another syscall here
}                                  

What ibm_db2 APIs should I use sparingly?

Most of the DB2 meta data APIs come with a fairly heavy performance penalty. You should probably avoid using these in your main application flow paths.

Retrieved from http://youngiprofessionals.com/wiki/index.php/I5/DataBase101IbmDb2
Page last modified on January 10, 2013, at 01:31 PM EST