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