Category Archives: AS400

Automating deployment of IBM i SQL Database changes to a remote system

If you ever wanted to automate your SQL changes to a remote systems here’s one way of accomplishing that.

First you’ll have to  manually create two local sql files that have the database SQL that you would like to run.  The 1st file up{version#}.sql  will be ran when you want to promote your code, and the 2nd file down{version#}.sql will be ran when you want to rollback your changes.  The version# allows you to keep a history of what changes were made each time you this or you could tie it to source control commit#/version#/tag etc.. use whatever helps you.

Now that we have these two files we have to securely send them over to our IBM i.  Simply run this command on your local computer in a shell.  If your on windows install this so you can run it (http://cmder.net/)

scp -r "/sqlsource/migrations" myuser@mysystem.example.com:/sqlsource/migrations

This will recursively copy the local directory /sqlsource/migrations to the remote system mysystem.example.com in the directory /sqlsource/migrations .  This is the directory that must contain our up and down sql scripts.

Now that we have the source on the remote system we need to create two local shell scripts that we’ll send to the remote system and call to promote or rollback the sql.  Create the two files below on your local machine:

PromoteSQL.sh

system -i "RUNSQLSTM SRCSTMF('/sqlsource/migrations/up1.sql') COMMIT(*NONE) NAMING(*SQL)"

RollbackSQL.sh

system -i "RUNSQLSTM SRCSTMF('/sqlsource/migrations/down1.sql') COMMIT(*NONE) NAMING(*SQL)"

We now have the source on the remote server, and 2 local shells scripts that we can send to the remote system.  To actually send the remote command we’ll use this command below on our local machine to send the promotion shell script to the remote system and it’ll execute immediately the shell script that was sent over

ssh myuse@mysystem.example.com < PromoteSQL.sh

If we need to rollback the changes we’ll just use this command:

ssh myuse@mysystem.example.com < RollbackSQL.sh

I’ve kept this example pretty basic, but you can get more extensive by sending parameters to your shell scripts to dynamically tell it what to do.  For example you might want to pass it the environment, the user, the server and this is how you can do that

./promoteAndExecuteSQL.sh dev myuse mysystem.example.com

PromoteAndExecuteSQL.sh

#!/bin/bash
#ZZZPromoteCodeAndDB.sh dev 1205 1206
ENVIRONMENT=${1}
DEPLOYMENTUSER=${2}
DEPLOYMENTSERVER=${3}
echo ${ENVIRONMENT}
echo ${DEPLOYMENTUSER}
echo ${DEPLOYMENTSERVER}
scp -r "/sqlsource/migrations" ${DEPLOYMENTUSER}@${ENVIRONMENT}-${DEPLOYMENTSERVER}:/sqlsource/migrations
ssh ${DEPLOYMENTUSER}@${DEPLOYMENTSERVER} < PromoteSQL.sh
Advertisements

Testing DB2 prepare and execute select statements in IBM i Navigator to debug ZF2 ZFCUSER bug using a sql stored procedure

Have you ever wanted to run a prepare and execute select sql statement that you have in PHP in IBM i navigator’s “Run SQL”?

take for example this PHP:

This is how you can create the Album table and then create a stored procedure to test the SQL select prepared statement

The “gotchas” of running Zend Framework 2 on older versions of Zend Server for IBM i – ZF2 skeleton Application

Have you tried the ZF2 skeleton Application on PHP on the IBM i and have run into issues getting it to work since your on ZS 5.5, 6.x and are unable to upgrade to ZS7 at the moment? Well here’s a few gotchas and things you’ll have to change to get it work correctly:


 

Fatal error: Can’t inherit abstract function Zend\Validator\Translator\TranslatorInterface::translate() (previously declared abstract in Zend\I18n\Translator\TranslatorInterface) in library/Zend/Mvc/I18n/Translator.php on line 19

1) Comment out the Translate method in i89n\Translator\TranslatorInterface.php

//public function translate($message, $textDomain = 'default', $locale = null);
//For some reason ZF2 has 2 methods with the same name but it works in newer versions of PHP

 

Zend\ServiceManager\Exception\ServiceNotFoundException

zendframework/library/Zend/ServiceManager/ServiceManager.php:529

Zend\ServiceManager\ServiceManager::get was unable to fetch or create an instance for Zend\Db\Adapter\Adapter

2) set your config_glob_paths to the full path of the config/application.config.php. Note: the path below has to be specific to where your file is located on the IFS.

'config_glob_paths' => array(
      '/www/zendsvr/htdocs/zf2test/config/autoload/{,*.}{global,local}.php',
),
//AFter updating to ZS 7 you should be able to change this back to the relative path of config/autoload/{,*.}{global,local}.php

 

“album” in MYLIB type *FILE not found. SQLCODE=-204

3) Add ‘platform_options’ to your config/autoload/global.php

//The SQL that is generated by Zend's classes is putting quotes around the table like:
//SELECT 'album'.* FROM 'album'
//after turning off quote_identifiers it will run this SQL:
//SELECT album.* FROM album
return array(
     'db' => array(
         'driver'           => 'IbmDb2',
         'platform'         => 'IbmDb2',
         'platform_options' => array('quote_identifiers' => false),
         'database'         => '*LOCAL', // IBM i serial number or db directory entry name goes here
         'persistent' => true,
        'driver_options'    => array(
            'autocommit'     => DB2_AUTOCOMMIT_OFF, //::TODO:: We might not be able to use the constant since db2 may have not loaded at this point
            'i5_naming'     => DB2_I5_NAMING_ON, //::TODO:: We might not be able to use the constant since db2 may have not loaded at this point
            'i5_lib'        => 'MYLIB',
            'i5_libl'       => 'MYLIB MYLIB2 MYLIB3',
            ),
     ),
     'service_manager' => array(
         'factories' => array(
             'Zend\Db\Adapter\Adapter'
                     => 'Zend\Db\Adapter\AdapterServiceFactory',
         ),
     ),
 );

 

You’re view doesn’t display titles or artists

4) This is because the DB2 is case-insensitive and automatically converts lower-case column names that were created in the CREATE TABLE ALBUM script to upper-case columns (i.e. title is now TITLE and artist is now ARTIST). You can either modify the CREATE TABLE sql to force lower case column names by putting double quotes around your columns ( i.e. “title” “artist”). If you do it this way you’ll always have to put double quotes around it when using SQL, but in the PHP it won’t require double quotes. OR you can change the Album Model’s exchangeArray() to use the upper case column names. I recommend using the upper-case columns because the Zend\Db\Adapter\Driver\IbmDb2\ibmdb2 Statement class doesn’t know that it has to put double quotes around lowercase field names. The only approach with this is that you’ll have to change the field names to uppercase throughout the Getting Started Example

Solution 1

/* Creating table MYLIB.ALBUM */
DROP Table MYLIB.ALBUM;
CREATE TABLE MYLIB.ALBUM (
    'id' INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO ORDER, NO CYCLE, NO MINVALUE, NO MAXVALUE, CACHE 20) NOT HIDDEN ,
    'artist' VARCHAR (100) NOT NULL NOT HIDDEN ,
    'title' VARCHAR (100) NOT NULL NOT HIDDEN ,
    PRIMARY KEY ('id')
) NOT VOLATILE ;

LABEL ON TABLE MYLIB.ALBUM IS 'Test table for ZF2' ;

 INSERT INTO MYLIB.ALBUM ('artist', 'title')
     VALUES  ('The  Military  Wives',  'In  My  Dreams');
 INSERT INTO MYLIB.ALBUM ('artist', 'title')
     VALUES  ('Adele',  '21');
 INSERT INTO MYLIB.ALBUM ('artist', 'title')
     VALUES  ('Bruce  Springsteen',  'Wrecking Ball (Deluxe)');
 INSERT INTO MYLIB.ALBUM ('artist', 'title')
     VALUES  ('Lana  Del  Rey',  'Born  To  Die');
 INSERT INTO MYLIB.ALBUM ('artist', 'title')
     VALUES  ('Gotye',  'Making  Mirrors');

GRANT SELECT,INSERT,UPDATE,DELETE ON MYLIB.ALBUM TO MYUSER;
SELECT * FROM MYLIB.ALBUM

Solution 2

<?php
namespace Album\Model;

 class Album
 {
     public $id;
     public $artist;
     public $title;

     public function exchangeArray($data)
     {
     //SOLUTION 2 - change the PHP file to use the upper-case column names
         $this->id     = (!empty($data['ID'])) ? $data['ID'] : null;
         $this->artist = (!empty($data['ARTIST'])) ? $data['ARTIST'] : null;
         $this->title  = (!empty($data['TITLE'])) ? $data['TITLE'] : null;
     }

	 public function getInputFilter()
     {
         if (!$this->inputFilter) {
             $inputFilter = new InputFilter();

             $inputFilter->add(array(
                 'name'     => 'ID',
                 'required' => true,
                 'filters'  => array(
                     array('name' => 'Int'),
                 ),
             ));

             $inputFilter->add(array(
                 'name'     => 'ARTIST',
                 'required' => true,
                 'filters'  => array(
                     array('name' => 'StripTags'),
                     array('name' => 'StringTrim'),
                 ),
                 'validators' => array(
                     array(
                         'name'    => 'StringLength',
                         'options' => array(
                             'encoding' => 'UTF-8',
                             'min'      => 1,
                             'max'      => 100,
                         ),
                     ),
                 ),
             ));

             $inputFilter->add(array(
                 'name'     => 'TITLE',
                 'required' => true,
                 'filters'  => array(
                     array('name' => 'StripTags'),
                     array('name' => 'StringTrim'),
                 ),
                 'validators' => array(
                     array(
                         'name'    => 'StringLength',
                         'options' => array(
                             'encoding' => 'UTF-8',
                             'min'      => 1,
                             'max'      => 100,
                         ),
                     ),
                 ),
             ));

             $this->inputFilter = $inputFilter;
         }

         return $this->inputFilter;
     }
 }

class AlbumForm extends Form
 {
     public function __construct($name = null)
     {
         // we want to ignore the name passed
         parent::__construct('album');

         $this->add(array(
             'name' => 'ID',
             'type' => 'Hidden',
         ));
         $this->add(array(
             'name' => 'TITLE',
             'type' => 'Text',
             'options' => array(
                 'label' => 'Title',
             ),
         ));
         $this->add(array(
             'name' => 'ARTIST',
             'type' => 'Text',
             'options' => array(
                 'label' => 'Artist',
             ),
         ));
         $this->add(array(
             'name' => 'submit',
             'type' => 'Submit',
             'attributes' => array(
                 'value' => 'Go',
                 'id' => 'submitbutton',
             ),
         ));
     }
 }

Fatal error: Can’t inherit abstract function Zend\Form\LabelAwareInterface::setLabel() (previously declared abstract in Zend\Form\ElementInterface) in /www/zendsvr/htdocs/zf2test/vendor/zendframework/zendframework/library/Zend/Form/Element.php on line 21

5) Comment out setlabel and getlabel in Zend/Form/View/LabelAwareInterface.php – https://github.com/zendframework/zf2/issues/5996

//public function setLabel($label);
    //public function getLabel();

Doctrine is not supported for the IBM DB2

This isn’t a ZF2 issue directly, but if you try and use ZF2 plugins such as ZFCuser you’ll run into issues. Doctrine doesn’t work that well with IBM DB2 currently. The current implementation is based on DB2 for Linux. There is talk about creating new classes that would handle the IBM DB2 specifically but there hasn’t been any work on it. A conversation looking into solving this is being had at – https://github.com/doctrine/dbal/pull/518 . Novice PHP programmers remember that there’s multiple ways of solving a problem and doctrine is not a must have to get the job done.

UPDATE on Case-sensitivity – Dec 3 2014

I think some of the case sensitivity issues can be fixed if Zend Framework’s Zend\Db\Adapter\Driver\IbmDb2\ibmdb2 is updated to use QSYS2.DELIMIT_NAME which is available in V7R1 TR 8 and V7R2. Also doctrine could be used if they used that feature. Not everyone is on that Version of the OS so this would need to be a feature that should be explicitly set. More info on QSYS2.DELIMIT_NAME – http://www.itjungle.com/fhg/fhg111214-story01.html

PHP Remote Debugging on Zend Server for IBM i using xdebug and Netbeans

This is a guide on how to setup xdebug on Zend Server for IBM i to use with any IDE w/ a xdebug client such as Netbeans.

Step 1  Get xdebug.so and put it on the IBM i IFS

Zend Server usually already has the file so just check that its in the

/usr/local/zendphp7/lib/php_extensions

If its not there, you can either compile your own xdebug.so by downloading the source from http://xdebug.org/ or you can just download this xdebug.so file.   Once you have the .so file you upload it to the IFS directory above.  Note on older versions of ZS the location might be:

/usr/local/zendsvr/lib/php_extensions

/usr/local/zendsvr6/lib/php_extensions

Step 2 – Setup php.ini file and other config files

You’ll also have to set

output_buffering = Off

in the php.ini.  The default for this is output_buffering=4096.  Make sure the default is commented out.

PHP.ini location: /usr/local/zendphp7/etc/php.ini

Disable Zend Debugger and Zend Optimizer

You’ll have to turn off Zend Debugger and Zend Optimizer as these files will interfere with xdebug.  To turn off this go to /usr/local/zendphp7/etc/conf.d/debugger.ini and comment out

; zend_extension_manager.dir.debugger=/usr/local/zendphp7/lib/debugger

with  a semi colon at the beggining of the line.  In older versions of Zend Server you may have to comment out optimizerplus.ini in the same directory

;zend_extension_manager.dir.optimizerplus=/usr/local/zendphp7/lib/optimizerplus

in the xdebug.ini file in the directory /usr/local/zendphp7/etc/conf.d modify it to look like the xdebug.ini below.

Step 3 – Restart Apache

On the i5/as400/iseries type:

Go ZENDPHP7/ZSMENU

Option 5 – Service Management menu

Option 6 – Stop Apache server instances

Option 5 – Start Apache server instances

or do it at https://myibmi:2010/HTTPAdmin

Step 4 – Setup xdebug client in Netbeans

Right click a project and go to project properties.  In sources, set the Web Root to the local files that map to your Web Root on the i5 (/www/zendphp7/htdocs/{myapp}).   In the Run Configuration, set up the Project URL and the index file to point to your starting index.php file.  On the toolbar menu click Tools->Options ->PHP tab-> Debugging sub tab.  At this location you can set the debugger port, session ID, if it should stop at the first line of a file, what URLs were requested and to show the debugger console.

netbeans-php-debugging-options.png

Step 5 – Launch Debugger

You lauch the debugger by pressing CTRL+F5 or by clicking the debug icon.

debug icon netbeans.png

Hopefully if everything was setup right you’ll be able to start the debugger and Netbeans will open your website up in a browser and then connect to xdebug on the server.  The way it works is the client sends the request on port 80 and then the server and the client communicate over port 9000 or the port you selected.  You’ll get all the Variables, Call stack and be able to step through the PHP code and set up Break points through out the file. If your having troubles getting this working let me know.  Running phpinfo() will also give you an idea if you’ve correctly installed xdebug.

Misc. Tips

1. You may have to move the CW wrapper for the old i5 toolkit into your project file.  You can find those files in the /usr/local/zendphp7/share/ToolkitAPI folder on the IFS.

2. You can use xdebug’s wizard that uses your phpinfo and helps you detect and install xdebug – http://xdebug.org/wizard.php

3. If your looking to compile php on the iseries this site might help – http://www.deloli.net/distrib.php

4. Netbeans wiki on how to configure xdebug  – http://wiki.netbeans.org/HowToConfigureXDebug

What phpinfo() will display about xdebug.

 

Migrating from Zend Core for I to Zend Server for IBM I – My Experience

I’m currently working on migrating from Zend Core 2.6.0 to Zender Server 5.6  for IBM I.  Big thanks to Alan Seiden who has some very helpful blog posts on this topic.  I’d recommend checking out:

http://www.alanseiden.com/2010/04/21/differences-between-zend-core-and-zend-server-on-ibm-i/

and

http://www.alanseiden.com/2011/02/08/qa-upgrading-from-zend-core-to-zend-server/

Here’s my tip from migrating:

  1. If you were using the I5_* functions for database connections you can continue using AURA equipments toolkit, but I think long term you’d be better off using PHP db2_* functions.  Do not use the Zend Framework’s DB2 class since the db2_bind param doesn’t work.  The ZF team can’t implement it to work correctly right now and probably never will in the future.  I’ve been waiting 3 years now for them to make a change…
  2. Use http://as400:2001/HTTPAdmin to change the apache config for Zend Server and to start/stop the server
  3. You’ll need to trasfer your files from /www/zendcore to /www/zendsrv
  4. Give Permissions to QTMHHTTP.
    Run STRQSH
    cd /www/zendsvr/htdocs
    chmod –R 770
    chown -R qtmhhttp
  5. Modify the http.conf file and compare your old conf file to see if changes need to made
    /www/zendcore/conf/http.conf
    /www/zendsvr/conf/http.conf
  6. #–Check your system CCSID value ( dspsysval qccsid). if the value is 65535 then add the following two directives to Apache configuration file (/www/zendsvr/conf/httpd.conf) and then Stop and Start Apache:
    DefaultFsCCSID 37
    CGIJobCCSID 37
  7. Edit the php.ini file and add a different session path (edit /usr/local/zendsvr/etc/php.ini)
    session.save_path = “/tmp/ZS”
  8. Change scripts that reference www/zendcore to www/zendsvr
  9.  Recreate any NFS mounts since files might have moved into /www/zendsvr
  10. IF your using Zend Framework you might want to continue using the old version that Zend Core had, so modify your php.ini file include path to include it and not include the new version which is currently 1.11.10
    include_path = “.:/usr/local/Zend/ZendFramework/library:/usr/local/zendsvr/share/pear:/usr/local/ZendSvr/share/ToolkitApi”
Benefits of upgrading from Zend Core:
  1. PERFORMANCE!  I’m seeing scripts running between 18%-400% faster.  One script used to take 40 seconds now only takes 8 seconds.
  2. Only 1 apache configuration to worry about now
  3. Latest PHP

iSeries Access 64 Bit Driver problems Segmentation Faults

Update: May 17 2016 – At common I found out that 64 bit issues should now all be resolved when using the latest PHP 5.6, the unixODBC manager 2.2.14+ and IBM i Access for Linux ODBC driver.  This was effecting PHP extension: PDO ODBC, and ODBC_* functions

PHP 5.6.x+
http://php.net/downloads.php

UnixODBC Manager
http://www.unixodbc.org/

IBM i Access ODBC Driver [for Linux] (ibm-iaccess-1.1.0.1-1.0.x86_64)
http://www-03.ibm.com/systems/power/software/i/access/linux.html

Kevin Adler’s Article from IBM
https://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-linux/

Segmentation Fault Problem

I’ve had a lot of trouble with using the iSeries Access 64 bit odbc driver for Linux (iSeriesAccess-5.4.0-1.6.x86_64.rpm) on my RHEL linux box to connect toa  V5R4 iSeries server.  I switched back to using the 32 bit driver and saying i’ll wait until they update the 64 bit driver to work.

I was going to the PHP page and finding just a blank screen returned to the browser, which made it a pain to debug.  I found out after looking at the apache error log file in /var/log/httpd/error_log  that there were segmentation faults with the following error:

[notice] child pid XXXXX exit signal Segmentation fault (11)

To try and debug the PHP to see what causing the problem i put die(‘here’) statements in my code until i found out where the interpretor wasn’t getting to because of the seg fault.   I found out on the return of the Zend Framework’s PDO ODBC Fetchall statement the program was causing the seg fault.  I also noticed that this only occured when i had a null field in the DB2 udb for iSeries database.  The field was a char datatype.

If you’re having this problem i’d recommend just installing the 32 bit driver(iSeriesAccess-5.4.0-1.2.i386.rpm).

Debugging

Here’s how i understand requests get called in case you want to try and debug the problem:

  1. Request for page comes in from Apache
  2. PHP runs and makes db calls through  a DSN
  3. All db calls go through unixODBC manager
  4. The db call then goes through the IBM Client Access driver you have specified and executes the sql on the iSeries.
  5. If the driver has a problem with a null field it would create a segmentation fault.

Here’s a link to someone’s explanation of the problems using the 64 bit driver:

https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14510046&#14510046

Need help understanding how to install the driver and setup all the linux files (/etc/odbc.ini /etc/odbcisnt.ini)

Here’s the link to the driver download

iSeries Access Linux Driver download

Remember to edit your
/etc/odbcinst.ini

[iSeries Access ODBC Driver]

Description &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = iSeries Access for Linux ODBC Driver
Driver &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp;= /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
NOTE1 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = the following Driver64/Setup64 keywords will provide that support.
Driver64 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp;= /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = 2
DontDLClose &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = 1
UsageCount &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp;= 1

and /etc/odbc.ini

[iSeriesDSN]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = iSeriesSystemName
UserID =
Password =
Naming = 0
DefaultLibraries = QGPL
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

Giving credit to IBM for this example file

Common Fall Conference 2010 Day 2

Below are some notes i took while on my second day of common fall conference 2010.

Session 1 – DB2 for IBM i: Adv Functionality Sampler

Presented by Linda Swan

Presented by Linda Swan

DB2 for i Query Optimizer

lmswan@us.ibm.com

Referential Integrity

Customer file with Customer number, Invoices table with Customer Number.  You can turn on a constraint on the table forcing that there’s a customer number

Requires Parent Table to have a unique key

Child table has a Foreign key.

SQL –

Parent table: ALTER TABLE master ADD CONSTRAINT name PRIMARY KEY(custno)

Chlid table: ALTER TABLE Add contstraint name2 FOREIGN KEY (custnum) REFERENCES master (custno) ON DELETE CASCADE ON UPDATE RESTRICT

Insert Rule

no explicit rules but insert operations into dependent files are checked

Delete rule

RESTRICT – occurs before a trigger

CASCADE

SET NULL

SET DEFAULT

NO ACTION – occurs after a trigger

Update rule

restrict updates to a Parent key

RESTRICT

NO ACTION

Puts the policy in the database and doesn’t matter what application is using the table.  The Rules must be followed.

1 primary key, but can have multiple unique keys/parent keys

Journal Requirements

Restrict rule: journal not required

Any other rule:

Parent and Dependent files must be journaled to the same journal

Implicit commitment control is started

DB2 uses access paths and indexes for constraint enforcement

I Navigator will can show you constraints

Monitoring Exceptions in Applications

SQLCODEs 530,531,532

SQLSTATEs 23001, 23503,23504

Check Contraints (constraining a columns value)

ALTER TABLE Employee ADD CONSTRAINT SalaryChk CHECK (Salary<40000 AND Bonus <= Salary)

DB2 has to lock the table exclusively when adding this constraint to check the current data

The CHECK can have anything you would put in a where clause, but cannot reference columns outside of its table

Triggers

associated with a physical file

activated before or after

independent from application

Trigger could automatically send an email if someone’s order is $300 or more

Two Types

Native made through RPG

ADDPFTRG

CHGPFTRG

SQL triggers

Column level

Row level

Statement level

max 300 triggers per table

8 components

Base file

Trigger event

insert update or delete

trigger time

before or after

trigger program

Granularity

Transition Variables – Can reference the old row and new row

Transition Tables – Can reference the old table and new table (result set)

Triggered Action

triggers cannot pass paramters back

Column Level Security

SQL allows for granting only permissions to certain columns

2 phase commit allows to sync 2 boxes together

Stored Procedure

External

written in RPG

may or may not SQL

SQL

business logic is coded with SQL and doesn’t require compilation

V7R1 allows return of results sets to RPG

V7R1 allows arrays as an input to a stored procedure

Program Control operations in SQL

Conditional Logic, Declaration of Variables, Etc…

Joins

Unions – Union the sales for product xyz from Sales2008, Sales 2009, Sales2010

EXCEPT and INTERSECT

V7R1 has xml support, Global Variables. Array support in procedures, also 3part naming to allow you to connect to another db2 server inside a stored procedure.

ROW CHANGE TIMESTAMP added V6R1

V6R1 and V7R1 enhancements

http://www.ibm.com/systems/i/software/db2/

Sessions 2 – “I didn’t know you could do that with DB2 Web Query!”

Presented by Gene Cobb

cobb@us.ibm.com

DB2 Web Query rolled out in 2007, replaces the Query400 product

Partnership with infromation builders

Leaves the data on the IBM i

Creating a webserver

youri5:20001/HTTPADMIN

WRKACTJOB SBS(QHTTPSVR) to see the server is running

DB2 Web query can create reports and graphs against stored procedures that return result sets.

Displaying Images in your report

can display images of different products and have a link to a pdf document for that product

images and pdfs are stored in a web server

Report assistant lets you create a report and you create fields pointing to the images and the pdfs

Set the field to ‘<a href=”youri5:20001/MyPDFs/’ || PARTNUMBER || ‘.pdf”<img src=”youri5:20001/MyImages’|| PARTNUMBER ||’.gif” />’

Integrating OmniFind Text Search Server

DB2 interfaces for developing text search applications

Supports finding a DB2 record based on text contained ina  document that is stored in a DB2 column

Searches the database and your documents for a keyword.  Documents are stored in a LOB column of the database

Omnifind can search your IFS or LOB column

Integrating Web Services: SOAP Simple Object Access Protocol

Scott Klement’s free utility HTTPAPI

Showed an example of calling weather web service to get a 5 day forecast

Integrating Web Services (RESTful)

Representational State Transfer

architectural style not a standard

URL Drilldown (Google Map Integration)

uses the db2 web query iframe to change it to a google map.  You create a field that is put into the query parameter passed to maps.google.com?q= || ‘GOOGLECITY’

Integration using DB2 Web Query Report Integrator Toolkit

Abstraction layer that greatly simplifies report integration process.

Session 3 – PHP toolkit examples

Mike Pavlik

mike.p@zend.com

Zend Server is now the de facto standard, Zend Core is no longer supported after May 2011

PHP performance improved 30%-600%

Support for PHP 5.3

Code Tracing

Job Queue

Single apache server

PASE – Portable Application Solutions Environment

The toolkit might change in 2 months, don’t use for access db2,  use db2 adapter which is cross platform.

QTMHHTTP is the user in Zend Server

i5_adopt_authority can adopt other authorities while running

i5_data_area_read can read a data area a retrieve its value into PHP.  Showed an example of retrieving a company’s name from a data area

System Values

i5_get_system_value(“QMODEL”) will get the system value for qmodel and you can use that value now

Program Call can call RPG

Setup parameters in associative array, array with name, io, type, and length.  Equivalent to a plist

Prepare Program

Load parameters

Call the program, program, parameters, and return values

Spool file

you can grab a spool file and echo out all of its data

Mentioned midrange.com


Session 4 – PHP Batch Jobs on IBM i

Presented by Alan Seiden

Generate an XML based price list and FTP it to a customer each week at the same time.

Send email reports to administrators and errors to developers

Two methods

PHP-CLI (command line)

Requires knowledge of PASE or QSHELL environment

Automate the process with scheduled CL programs

located in /usr/local/zendsvr/bin/php-cli

Has no need for $_POST or $_GET and uses $argc and $argv

Apache doesn’t need to be running

Need not be in a web-accessible Alan likes to put them in /php/appname/myscript.php

Use a naming scheme thats easily to spot where you store your php scripts

ran on QSHELL or PASE

php-cli -v outputs PHP version information

php-cli -i outputs the equivalent of phpinfo()

php-cli -h to see all options (help)

CALL QP2TERM Terminal

CALL QP2SHELL PARM(‘/php/sendinvoice/php’ &EMAIL &NAME ‘Y’)

QP2SHELL2 same as QP2SHELL except it runs in ILE

C42PDF converts TIFFS to PDF

Using Qshell

STRQSH or QSH command can launch command line

QSH CMD(‘COMMAND’)

CALL PGM(QP2SHELL) PARM(‘phpdir/php-cli’ ‘htmldir/my.php’)

ADDJOBSCDE for your CL program

ADDJOBSCDE JOB(SNDPRICES) SCDDATE(*NONE) CMD (CALL PGM(MYLIB/PRICEPGM)) SCDDAY(*FRI) SCDTIME(’23:00′) FRQ(*WEEKLY) RCYACN(*NOSBM) JOBD(MYLIB/PRICEJOBD)

Zend Server Job Queue

Better for people not familar with command line OR needing more flexibility

Use complex parameters

ZendJobQueue().  queue->createHttpJob($url,$vars,$options) to start a job

Manage priorities

Zend Framework’s livedocx service can merge .doc templates with DB2 data

Excel Writer Pear is great for creating microsoft excel documents