DB2 Web Query Tips and Techniques

DB2 Web Query

Jackie Jansen

DB2 Web Query

Tips and Techniques

Jackie Jansen

[email protected]

www.ibm.com/systems/i/db2/webquery

Agenda

 Performance

–Compute and Define fields

–DB2 optimization

•Run w SQL Trace with example –Legacy Dates

Tips for orking ith dates

Performance / Complexity versus

Ease of Use / Simplicity

•Tips for working with dates –InfoAssist performance tip & IE tip

–Recommendations for prompting parameters

 Joining Tables

 Metadata and Applications

 User Row/Record Security

 Filters and Business Views Interesting tidbits

 Interesting tidbits

 Data profiling

 General Tips

Define fields

Compute Fields

- Virtual Column - RA/GA wizard on right

- RA/GA wizard on left - Executed after data is aggregated - Executed on database read - Calculated once for very print line - Calculated once for every record - Calculated after database execution

Compute vs Define (Virtual Column) Fields

DB2 Performance Recommendation

** “Push” as much logic as possible down to DB2 **

 Use Run w/SQL trace option

– No Run w/SQL trace option for InfoAssist

– Create a simple Report Assistant report containing the specific function you are testingCreate a simple Report Assistant report containing the specific function you are testing  Look for database optimization disablers

 Use SQL views to overcome database optimization disablers

Assume that our database table has 1 million records table has 1 million records and 100 distinct dates (output summed by date)

Performance Example:

Convert Legacy Date to Smart Date

• Output 100 records

• DB2 retrieved 1 million records

• Web Query then read 1 million records, summed 1 million records, output 100 records

• Very, very slow!!

Performance Example:

Convert Legacy Date to Smart Date

• Output 100 records

• DB2 processed 1 million records • Web Query receives, formats and

outputs 100 records outputs 100 records • Very, very fast!!

• Make sure you handle non date values you may have inserted in your date fields (filter out or convert to 99990101)

Performance Example:

Summing Character Fields

“where sort fields do not cover the key” FST. or LST.

Performance Example:

Summing Character Fields

Additional DB2 Performance Tips

Maximize SQE usage - Avoid SQE Inhibitors

•

Avoid creating metadata over DDS logical files

•

Watch out for Select-Omit logical files against physical files

(not as important in V6R1)

•

Both the Heritage File and Query/400 adapter use CQE

– Recreate Query/400 reports as new reports that use synonyms based on DB2 CLI adapter

– No way to avoid it for multi-format files (must use Heritage File adapter)

– Use SQL Aliases to access multiple members

Magic Bullet – V1.1.1

•

Initially use for every report,

including single table reports

9

Legacy and Smart Dates

Legacy Dates

 An integer, packed decimal, or alphanumeric format with date edit options, such as I6YMD, A6MDY, I8MDYY, or A8YYMD

 In Master file indicate date format as USAGE (A8YYMD)

 Formatted Legacy Date (P8YYMD)

–2011/04/01

–Mathematically extract components

•SORT/BY/ACROSS optimized

•Filter (YEAR = InpParm)

–Some date arithmetic is optimized

 Do you need to convert to a Smart Date?  Do you need to convert to a Smart Date?

- Often Not

- Required for:

- Monday April 4, 2011

- Many date arithmetic functions (not all optimized)

Numeric Legacy Dates

Actual: P8 P6

Usage: P8YYMD P6YMD

Output: 2011/04/01 Output: 2011/04/01

- Dates less than 6 digits use math to create 6 or 8 digit date

- i.e. Year * 10000 + Mth * 100 + Day

Filter: 20110101 <= Year <= 20111231

- Do not decompose Legacy date to create a filter

- Do NOT filter WHERE YEAR = NumDate / 10000

- 2011 ≠ 2011.0401

Smart Dates if required:

- Must start with an 8 digit field

- DATECVT(Date8Num, ‘P8YYMD’, ‘YYMD’)

- Date Components Required:

- DPART(DateFld, ‘P8YYMD’, ‘YYMD’) starting with your YYMD field

Character Legacy Dates

Actual: A8 A6

Usage: A8YYMD A6YMD

Output: 2011/04/01 Output: 2011/04/01

- Use “|” (concatenate) to combine components when less than A8

Filter:

- Use SUBSTR to decompose date

WHERE YEAR = (SUBSTR(A8Date….))

Smart Dates: Are they required?

- Must start with an 8 digit field

- DATECVT(DateA8, ‘A8YYMD’, ‘YYMD’)

- Cannot currently combine Concatenate and DATECVT

- For dates less than 8 characters use an SQL View for Summary reports or calendar icons or use a date dimension table

Legacy Date Optimization Summary

8 Long?

Smart Dates Required for:

-Formatting – January or Friday - Optimizing many date calculations

(Use COMPUTE where possible) - Using Calendar icon

yes no

Formatting “/” Year filter - num use all 8

digits, alpha use SUBSTR SmartDate: DATECVT DPART 6 Long? 6 Long Alpha or Num? 4/2/2 or 2/2/2/2 or 1/2/2/2 Alpha or Num? yes no (Alpha) Formatting “/” Filter: SUBSTR 8 Long: Concatenate SmartDate: Use SQL View

or date dim. table for optimization

(num) Use Math to make 8 long

SmartDate: DATECVT DPART

(Alpha) 8 Long: Concatenate

Formatting “/” SmartDate: Use SQL View

or date dim. table for for optimization

(num) Use Math to make 8 long

Formatting “/” SmartDate: DATECVT

DPART

Julian Dates not shown

alpha num alpha num

And the Tips just keep coming

 InfoAssist development performance

 IE Control key is marvelous!

 Tip: Reduce records sent to IE

– Use prompting or drill down

Input Parameter Alternatives

•

Creating HTML page front-end

–

Using HTML Composer in Developer Workbench

–

Controls grouped and passed together rather than individually. The

more parameters, the better the performance (over Auto Prompt)

M

ffi i t t h i

t b ild th HTML f

th d

d

li t

–

More efficient techniques to build the HTML for the drop down lists

–

Aesthetically more options and more pleasing

•

Usage Tip

–

Consider HTML Composer with calendar icons for date prompting

Joining Tables

possible

 Developer Workbench – Reference existing synonyms

– R1.1.2 system will try to autojoin the two tables you select the two tables you select

 In reporting tool

Metadata

 Synonyms can be placed in application folders and associated with Domains

 Only members of group profile can access those synonyms

• Metadata will automatically go into first application in domain’s application list

• Developers working in Developers Workbench will see the application folders under the domains that they are authorized to.

• Administrators only will see “Data Servers” containing all metadata applications

• Administrators only will see Data Servers containing all metadata applications

Developer

Administrator

User Row/Record Security Alternatives

JJANSEN CANADA SMITH GERMANY SMITH UNITED STATES JJANSEN UNITED STATES JONES UNITED STATES

Complete security – including securing developers

 SQL Views!

 Join to a table containing userid and security values  Allows for more complex selection

 Very fast: DB2 optimization and checking

 Today a developer can simply create their own synonym and bypass secured synonyms  See white paper on DB2 Web Query website

DBA

 Store security data and information directly in synonym  Optimized DB2

 Details next slide

Web Query Join

 Create table with userid and authority values

 Join Security table to master file with secured field (i.e. COUNTRY) R t i ti id

 Retrieve run time userid

 Select records where run time user equals security userid  In Report, compare user to &FOCUSER

– &FOCUSER optimized

– Report developer makes security decision  In Master File have to use GETUSER(‘10’)

Jackie

DBA

DEMOADMIN

DEMODEV

Filters and Business Views

 Global Filters

– Report designer can easily select and reuse

•i.e. Europe filter might automatically select all countries in Europe

– Modified filters automatically used by all i ti t

existing reports

 Business Views

– Simplify list of columns by categorizing fields within folders

– Reduce fields user sees (simplicity and security)

More Questions More Answers

1) Question: How to display graphic indications of performance? Answer: Wingdings font with conditional styling.

2) Question: How to have a calculation at subtotal and not at detail level. Answer: Subfootings and Prefixes.

3) Question: My field length has

3) Question: My field length has changed. What is effected?

Answer: Developer Workbench: Impact Analysis TIP: different analysis based on starting point: reports or master files

Developer Workbench: Data Profiling

•

By column / table / cluster / view

•

Pattern count, outliers

•

Identifies Avg, Min/Max

•

Number of distinct Values

General Tips

 Multi-member file support

–Can use the Heritage File adapter – uses OPNQRY and CQE

–Creating an alias will use SQE

CREATE ALIAS MYLIB/MYALIAS FOR MYLIB/FILENAME (MBRNAME)

 SQL Wizard – type or import an SQL statement

–Part of Developer Workbench

–Result set is sent to RA/GA for formatting  Developer Workbench

–“Confirm Close” – Use this setting

–“Show Desktop on Explorer Tree”

General Tips

 Create Domain names with exactly eight characters and then change the displayed name in the Domain properties

 Consider using a Prefix for synonym names to assist in grouping related tables together

 Backup libraries and IFS

–SAVLIB – QWEBQRY76 and QWEBQRY77

–Sav - /QIBM/PRODDATA/WEBQUERY/IBI/*

–Sav - /QIBM/USERDATA/WEBQUERY/IBI/*  Use style sheets to format your reports

–You can modify provided style sheets or create your own .STY and .CSS

–RA style sheets need copying into Other Files (redbook gives details)

–IA style sheets are instantly available  RA/GA/PP – prompt for parameter

The Forum and the WIKI



www.ibm.com/developerworks/spaces/DB2WebQuery

–

Registration Recommended

g

–

KnowledgeBase and Forum

–

Links to Additional Information

–

WIKI – Repository of technical information



www.ibm.com/systems/i/db2/webquery

–

Getting Started with DB2 Web Query Redbook

http://www.redbooks.ibm.com/abstracts/sg247214.html

•