Monthly Archives: October 2010

Common Fall Conference 2010 Day 3

Session 1 –

Application Recovery and Other Tricks: Thinking Outside the Developer’sToolBox

– Talked about creating a CL command to copy production data to development for test data.






– Created a PJ prestart job under a subsystem and it can’t be killed.  Because you sometimes don’t want your users to be able to kill a job.  Theres an endpj command that you can use to stop it from continue to spawn a new job.

ADDPJE will create a Prestart Job
STRPJ starts a pj
ENDPJ ends a pj

In V6R1 you can Create a PDF instead of a spool file TOSTMF(‘pdffilelocation/mylog.pdf’)

Session 2 – Web Services with PHP and IBM i

Public web services:

document conversions, search results from the web, databases, geolocators, stock quotes, weather data.

Private web services

between customers and vendors and service providers

Sometimes in organizations have different departments using different databases, but the departments need to share the information.  Accounting won’t use the client service database they will only use their own and vice versa.  But sometimes they need to send data to each other.  Web services allow you to share the information and could be used to sync the data between the two databases.




handle() <- outputs the WSDL file.

Zend_Soap_server($wsdl,$options) starts up a web service

Zend_Service_Yahoo lets you do local searches from yahoo

LiveDocx webservice allows you to create microsoft document mail merge.

Main philosophies for webservices

1. REST: Representational State Transfer

2. SOAP: Simple Object Access Protocol

WSDL – describes a webservice.  Functions available, input parameters required

PHP has a SoapClient.

PHP caches soap files in php.ini look at the soap section.  soap.wsdl_cache_dir is set to ‘/tmp’

Use Firebug and Firephp to output debug info of the soap errors

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

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.


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




NO ACTION – occurs after a trigger

Update rule

restrict updates to a Parent key



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


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



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


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


written in RPG

may or may not 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…


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


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.


V6R1 and V7R1 enhancements

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

Presented by Gene Cobb

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


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 || ‘GOOGLECITY’

Integration using DB2 Web Query Report Integrator Toolkit

Abstraction layer that greatly simplifies report integration process.

Session 3 – PHP toolkit examples

Mike Pavlik

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


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 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


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

ADDJOBSCDE for your CL program


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

Common Fall Conference 2010 Day 1

Today was my first day at Common 2010 and my first time at a common event. I’m in my mid 20s and I felt very young among the wise AS400 Dinosaurs. Grey hairs were abundant, but i know one day i’ll be like them talking about the days of PHP 5.3 as some whipper snapper is talking about how thats old news and that SGS is the language to use, but i digress.

Session 1 – Road to Compliance was presented by Robin Tatam
This is some of my notes/bullet points

  • IBM is struggling to get people V6R1
  • V6R1 has some type of Intrusion Detection system
  • If your using the IFS you can get a virus and you should use AntiVirus
  • “Security by Obscurity” is not a good option
  • There is a book out on Hacking the Iseries, 98% about how to get in, 2% talks about how to stop it
  • Most users are running at level 40 QSECURITY
  • Public Auth is set *change for 58% of most systems
  • You need to think about Data loss prevention even when giving read only permissions
  • Inactive profiles and default passwords are bad
  • You should be auditing your files %30 aren’t using auditing
  • Make sure your purging your journal records
  • Even if you don’t know how to read and understand an audit, powertech would be able to come in and tell you what happened if you have auditing on.
  • Power Users are bad, remember to only give the permissions needed for the group or user
  • Remember to delete user profiles after someone leaves the company
  • Do not write programs under a programmers name because of the reason above
  • System Values should be checked periodically against expected values
  • Do not give programmers *ALLOBJ
  • Audit changes to system values
  • Obtain a copy of Authority Progression Algorithm to understand how permissions are obtained
  • Object level protection works regardless of interface
  • Exit points allow you to Accept/Reject/Audit a program such as FTP
  • 30 exit points that deal with network access
  • only telnet and 5250 are locked
  • FTP allows you to run commands that  you thought couldnt be ran
  • QPWDRULES define password syntax your uses must use
  • QPWDEXPWRN gives your user a expiration warning
  • QLMTDEVSSN – limit device sessions
  • Encryption – AES Advanced Encrption Standard US Government standard; another popular solution is PGP

Session 2 – 5250 to Web with PHP was Presented by Alan Seiden

Alan went a bit too quick through his slides and didn’t have enough code of real world examples of using the 5250 bridge.

The main Idea i got is that the 5250 bridge is right for some solutions and not right for other solutions.  You have to figure out if its the right technology to implement.  You can either rewrite your business logic and re-write everything in PHP and SQL, but if thats too daunting you can use the 5250 bridge and communicate between PHP and RPG by passing parameters or sharing a workfile.

Differences between Zend Core (Old PHP on I Server) and Zend Server

Someone had mentioned liking microsoft’s ASP.NET grid control and he recommended using javascript libraries: ext-js, jquery for the same functionality

Session 3 – Conducting a Best Practice Audit of IBM i

SOCKS complaint, SAS compliant

object conversion time can take 6-9 hours for going from v5r4 -> v6r1

test data is “real” data.  A hacker will target your test system since they are usually less secured

Old profiles owning production objects (when installing you should not install under the programmer’s user profile)

iseries profile swaps are bad

don’t bring up all the tcp/ip servers

Don’t use *ALLOBJ

Use Hardware Encryption in case a tape is lost by iron mountain

Use Audit journals on sensitive tables, but don’t over us

Make sure we have a Back-up of the HMC

DR should say:

Who What Where When How


60% of your servers are critical

20% are necessary not immediately needed

20% are optional

Is Email Mission Critical?  It should be.

Do we back-up on Saturday or Sunday?  If not we could be going back more than 24 hours

When was the last Full System Save, only before DR? that is not good

If your backup process says 43917 objects saved and 342 not saved.  Make sure that you find out how to unlock those 342 objects since they are probably needed in a real DR.  During a recovery 43917 object would be recovered and you wouldnt know why the system isn’t working.  It would be because the 342 objects that weren’t saved.

Send off BRMS logs offsite

Session 4 – Function Junction – Mike Pavlak

This was a more of a review for  me as I’m pretty confident in my function skills, passing a parameter by reference, returning a value, php’s functions, creating a function.  I spent most of my time here working on a report i needed to send into the boss going over what was accomplished last month.  I did learn about the list function that i forgot about its.  Its a great function to use to set a bunch of parameters from an array object.

Day 1 afterwards

San Antonio is a great city.  It has a thing called riverwalk which is a 4ft deep river that runs through the city and was nice to run on for 5 miles and explore the city, restuarants and shops.   Then worked out at the hotels gym and went for a swim.  Then started writing this blog entry .  Well time for bed another great day of Common 2010 Fall Expo is ahead for me.