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