db2 data access with php on ibm i - tug · db2 data access with php on ibm i ... ■can connect to...
TRANSCRIPT
Db2 Data Access with PHP on IBM i
Mike Pavlak – IT Strategist
2
Agenda
■ Fundamentals
■ DB2 Improvements
■ Default Connection
■ PHP options for *LIBL
■ ibm_db2.ini
■ Column Heading
■ Featured Functions
■ Data Cache in action
DB2 & PHP: Fundamentals
5
php.net
■ Home base of all functions
▶Descriptions
▶Examples
▶Notes
■ All functions
■ All options
6
YoungiProfessionals.com
■ DB2 Connections:
■ http://yips.idevcloud.com/wiki/index.php/PHP/DB2Documents
7
What’s the name of your database?
■ Helpful in iASP situation and best practice
■ Probably your system name but not necessarily, can be changed HOT
■ Can connect to remote IBM i for no extra charge
DB2 & PHP: Improvements
9
You must be able to answer these
■ What version of the Operating System are we running?
■ What technology Refresh is installed?
■ DSPPTF
■ WRKPTFGRP
■ Pres F11
10
V5R4
■ Automatic Index Advisor
■ Plan cache
■ Show Indexes and MQT
■ RANK(), ROW_NUMBER(), LIKE, LOB,
■ Recursion
■ 2MB SQL Statements
■ Free format RPG & SQL
11
i6.1
■ VALUES in from clause
■ FETCH FIRST and ORDR BY in Subselect
■ Full OUTER join
■ SQE Learning Optimizer
■ RPG Variable scoping at procedure level
■ SQL Syntax highlighting in RDi
■ DB2 Data Studio
■ DB2 Web Query updates
■ Business Intelligence
▶Super Groups
▶CUBE
12
i7.1 (April 13, 2010)
■ Technology Refresh
■ XML Data Type
■ Column Level Ecryption
■ Adaptive Query Processing
■ SQE meets CQE
■ Encoded Vector Indexes
■ Technology Refresh 4
▶XMLTABLE, Three part name support
13
i7.1/7.2
■ Technology Refresh 5
▶ iNav DB Enhanced UI,
■ Technology Refresh 6 & 7
▶ SQL Index to 1.7TB, Live table/Index move to SSD
■ Technology Refresh 8
▶ GENERATE_SQL(), Performance Enhancements
■ Technology Refresh 9, 7.2 TR1
▶ Pipelined Table Functions, RegEX, Faster iASP Vary On
■ Technology Refresh 10, 7.2 TR2
▶ REPLACE table, JSON, CPU & I/O averages on Show statements
■ Technology Refresh 11, 7.2 TR3
▶ SQL ILE & ILE RPG Sync, SQE Enhancements
DB2 & PHP: Default Connection
15
Shipped defaults
■ Look at Samples…
■ *LOCAL – when you really aren’t sure
■ Zero length string for USER & PASSWORD
■ Options parameter is optional.
■ This is Anonymous
16
Default User profile
■ Runs as QTMHHTTP
■ Can be disabled…
17
Connection Error
■ Disabled profile…IBM i security enforced!
18
Hardcoded library
■ Library in SQL statement
■ All rows retrieved as arrays
19
Which retrieval method?
■ Difference is column names in index
■ Benchmarks via Z-Ray on micro-partition
DB2_fetch_array()21 ms
DB2_fetch_assoc()24 ms
DB2_fetch_both()39 ms
DB2 & PHP: options for *LIBL
21
Option parameter of DB2_Connect
■ Options parameter is an array.
■ Supports three kinds if library list
▶i5_lib – Single library
▶i5_naming – Enable library list via user profile
▶i5_libl – Specify library list in PHP
■ Supports many other parameters
22
Single library approach
■ Similar to the world of MySQL/MariaDB.
23
Multiple libraries hardcoded in PHP
■ More like real life
24
Job Description – PHPUSER2
■ WRKJOBD, DSPJOBD
25
Multiple libraries via User Profile
■ Parallels Green Screen access
DB2 & PHP: ibm_db2.ini
27
php.ini
■ File in IFS:
■ Zend Server Admin GUI port 10081 or 10091
28
Block anonymous
■ Samples shipped usually work
■ Default setting is “ON”
■ Default profile is QTMHHTTP
■ Change requires restart of Zend Server PHP
DB2 & PHP: Column Heading
30
Column Heading Text
■ Column names are nice, but…
■ Column heading and text are not available via functions in the DB2 Extension.
■ Table qsys2/syscolumns contains these values
■ Retrieve them when needed
■ Build function to add to application set
31
gettableColumns
DB2 & PHP: Featured Functions
33
db2_xxxx()
■ Over 50 functions that make up the DB2 extension for PHP.
■ Worth the time to review each
■ We’ll talk about a few interesting ones.
34
db2_pconnect()
■ Creates connection that is reusable over multiple page requests.
■ Saves the time of opening and closing the job
■ Same parameters as db2_connect()
■ First request sets everything up
■ Subsequent request from user will reuse job.
35
Column(Field) Information
■ Many detail about each column can be obtained
■ Must have valid result set.
36
Auto Increment Field
■ Create the table
■ Insert the row
■ Retrieve the unique ID
37
Auto Increment Field
38
Securityis no laughing matter
■ Security is better and this helps!
39
Frist, filter your inputs
■ Developer is agent of security in web app design
■ Filter inputs (Frameworks do this but so does PHP!)
■ JavaScript may do this, but PHP should, too
40
Understand which “exec” to use
■ DB2_Exec
▶Fire and forget
▶Use when no parameters necessary
● $sql = “select * from sp_cust”
■ DB2_Execute
▶Prepared SQL via DB2_Prepare & DB2 bind
▶Always use when using parameters
▶Example in Samples
● $sql = “select * from sp_cust where custno = $customerNumber”
41
Prepared SQL
■ SQL statement has “?” for placeholder
■ db2_prepare
■ db2_bind_param
■ db2_execute
DB2 & PHP: Featured Functions
43
Data Cache
■ Green screen lookup window vs. drop down
■ Data Cache (Zend or APC) saves data in memory
■ Performance
■ White paper
■ Better performance for frequently access lists
■ Less stress on DB2
■ Aggregate values (BI)
■ Look at examples…
44
Standard DB2 Call
■ 350+ Customer Orders
45
DB2 Call with Data Cache