System I Navigator as a

Database Admin Tool

Presented by

Barbara Brown
Director, Technical Services
Alliance Systems & Programming, Inc.

October 9, 2008 Alliance Systems & Programming

 Agenda
• Basic Database Tasks in System i Navigator
• Programming & Debugging
• Performance & Query Optimization
• Visual Explain
• Database Mapping
• Database as an essential component in SOA
• Navigator as a tool in moving toward SOA

October 9, 2008 Alliance Systems & Programming

 Database Terminology for
Die-Hard 400 Folk
• Some Familiar Concepts w new names
– Schema = Library
– Table = Physical File
– Row = Record
– Column = Field
– View = Logical File
– Index = Access Path
• Some New Concepts
– Relational Integrity – no orphan data, such as an order for a customer
that has been deleted from the customer master
– Constraints -
• Unique Key
• Check Constraint
• Foreign Key – value for column must exist in another table, for example the
customer number in the order header must be on the customer master

October 9, 2008 Alliance Systems & Programming

 Basic Database Tasks
• Edit list of schemas displayed
• Create, Delete – schema, table, view, index,
constraint
• Define column / copy column definitions
• View, insert, delete or change table contents
• Copy or move a table
• Edit Authority (Permissions)

October 9, 2008 Alliance Systems & Programming

 Basic Database Tasks
• Create view

October 9, 2008 Alliance Systems & Programming

 Basic Database Tasks
• Create view
over multiple
tables

October 9, 2008 Alliance Systems & Programming

 Add Constraint
• Unique Key
• Check Constraint
• Foreign Key

October 9, 2008 Alliance Systems & Programming

 Triggers
• Add Trigger – system (ADDPFTRG) or SQL (CREATE TRIGGER…)
• Uses:
– Enforce business rules
– Validate input data
– Generate a unique value for a newly inserted row on a different file
– Write to other files for audit trail purposes
– Query from other files for cross-referencing purposes
– Access system functions
– Replicate data to different files to achieve data consistency
• Benefits:
– Faster application development.
– Global enforcement of business rules.
– Easier maintenance.
– Improve performance in client/server environment.

October 9, 2008 Alliance Systems & Programming

 Programming & Debugging
• Display Locked Rows of a Table
• Reorganize a Table
• Display current SQL for a job
• Work with Journals
– Creating a journal
– Creating a journal receiver
– Adding a remote journal
– Removing a remote journal
– Activating a remote journal
– Deactivating a remote journal
– Displaying journal information
– Swapping journal receivers
– Starting and stopping a journal

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Query Optimizer Debug Messages
• Query optimizer
debug messages
– RUN SQL scripts
– Options – include
Debug Messages
in Job Log
– View - JobLog

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Explain SQL
• Explain SQL for functions, stored
procedures, triggers, SQL packages, and
programs (PRTSQLINF)
– Sql statements
– Access paths used
– Command parameters

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
SQL Performance Monitor
• SQL Performance
Monitor (STRDBMON)
– Collect performance
data on a job
– Request analysis
later

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Query Attributes
• Change attributes of queries (CHGQRYA
– QAQQINI can be unique for a
job
– IGNORE__DERIVED_INDEX
– QUERY_TIME_LIMIT
– OPEN_CURSOR_THRESHOLD
– PARALLEL_DEGREE

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Statistics
• Statistics automatically collected as system resources available
– Cardinality of values
number of unique or distinct occurrences of a specific value in a single column
or multiple columns of a table
– Selectivity
how many rows will be selected by any given selection predicate or
combination of predicates.
– Frequent values
Top 100 most frequent values of a column (excluding default and NULL values )
and how frequently each value occurs.
– Metadata information
total number of rows in the table, indexes that exist over the table, and which
indexes would be useful for implementing the particular query.
– Estimate of IO operation
estimate of the amount of IO operations required to process the table or the
identified index.
• Statistics manager also returns confidence level to query optimizer

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Indexes vs. column statistics
• Column statistics can only be used by SQL Query Engine (SQE). For
Classic Query Engine (CQE), all statistics are retrieved from indexes.
• Indexes are permanent objects updated when changes to underlying table
occur.
• Indexes more current than column statistics for constantly changing data,
but require more overhead.
• When new indexes become available that are candidate for implementing
query, Optimizer will re-optimize the query
• When new or refreshed column statistics are available, the Statistics
Manager will interrogate immediately. Re-optimization will occur only if
the answers are significantly different from before.
• Accessing column statistics to answer questions is faster than trying to
obtain these answers from indexes.
• Finally, column statistics can be used only for query optimization. They
cannot be used for the actual implementation of a query, whereas indexes
can be used for both.

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Visual Explain
• Main window contains a query
graph that displays the
implementation of an SQL
statement.
– both static and dynamic SQL
statements.
– supports SELECT, INSERT,
UPDATE, and DELETE.
– icons represent different
operations that occur during
implementation.
• Lower portion shows the SQL
statement being graphed.
• Click Optimizer Messages tab
to view debug messages (only
available if started from Run
SQL Scripts)
• Query attributes displayed on
the right pane.

October 9, 2008 Alliance Systems & Programming

 Performance and Query Optimization:
Visual Explain
• Information about each operation (icon) in the query graph:
– order of operations shown by connecting arrows
– Double arrows indicates parallelism used to process operation
– Crossed lines indicate hash tables were shared
• Select icon to view Attributes table in the right pane. Or right-click and select Help.
• To view information about the environment, click an icon and then select Display
query environment from the Action menu.
• Highlight expensive icons (View menu) – highlights problem areas by processing
time or number of rows.
• Statistics and index advisor (Action menu): optimizer can determine if statistics
need to be created or refreshed, or if an index could make the query run faster.
• Predicate implementation of the query: Visual explain allows you to view the
implementation of query predicates ( represented by a blue plus sign next to an icon).
• Highlight LPG (View menu) - Look Ahead Predicate Generation can minimize the
random I/O costs of a join.
• Basic and full information in the graph: (Graph Detail in Options menu)

October 9, 2008 Alliance Systems & Programming

 Query Optimization Tools

October 9, 2008 Alliance Systems & Programming

 Database Mapping

• Visually depict relationships

of database objects on your
system.
• right-click Database
Navigator Maps and select
New > Map.
• Drag or double click objects
to include in the map
• click Database Navigator
Maps to display a list of
existing maps in the right
pane.

October 9, 2008 Alliance Systems & Programming

 Service Oriented
Architecture
• Service Oriented Architecture – a plan or structure for a system
in which the components perform discrete well-defined units of
work for requestors through well-defined interfaces
– Modular – break down complex processes into components, only one
service for each function
– Encapsulated – hide complexity of implementation within modules with
well defined interfaces – requester is not concerned about details of how
a service is implemented
– Loosely coupled – modules connected by simple interface – no
technology dependence
– Composable – modules may be assembled to create other services
– Coarse grained —service provides complete business function –
coarseness determined by reusability.
– Stateless — each request is treated independent from what came before
or what will come next – (does not prohibit passing of state-related
information in parameters)
October 9, 2008 Alliance Systems & Programming
 Toward SOA
• Many ‘400’ shops still treat their database like a set of
flat files
– No referential integrity
– Inconsistent data formats
– Fields ‘re-used’ to avoid having to recompile many RPG
programs
– Redundant physical and logical files
– Code around bad data
• Use database mapping and SQL performance monitor
to identify worst offenders in terms of space,
performance, redundancy, etc.
October 9, 2008 Alliance Systems & Programming
 Toward SOA
• Use Visual Explain to tune worst offenders
• Turning on referential integrity without
necessary advance work could break too much
• Triggers could be used to
– move toward referential integrity in a stepwise
fashion
– provide data services layer for master data

October 9, 2008 Alliance Systems & Programming

 Master Data Management (MDM)

• Processes and tools to ensure data quality across non-

transactional entities of an organization
– Complete – all necessary information included
– Standard formats – ex) phone numbers, dates
– Consistent – no contradictory information
– Avoid Duplication (multiple versions leads to contradictions)
– Accurate – correctly reflects real world
– Data Integrity – no broken relationships
• Partner to SOA
– Data Services Layer avoids needs to duplicate code for
managing data
October 9, 2008 Alliance Systems & Programming