Transcript Siebel on zSeries Upgrade Tuning

®
IBM Corporation
Siebel 7.7 to 7.8
Production Database
z/OS DB2 Upgrade Planning & Performance Considerations
Spencer V. Krueger
IBM, Advanced Technical Support
Siebel on z/OS DB2
[email protected]
© 2006 IBM Corporation
IBM Corporation
 Siebel 7.7 to 7.8 Production Upgrade
 Upgrade Configurations – Separate Source & Target
 1 DB2 Subsystem, 2 Tableowner, 2 Database Prefixes (4 byte)
 2 DB2 Subsystems (V7 or V8), 1/2 Tableowner, 1/2 Database Prefixes
 Cross LPAR, SYSPLEX Data-Sharing
 DB2 V8 certified, APAR ii13985, DB2 Connect V8.2 FP 8.5
Siebel
“NSCR”
 Source  EIM table drops, Base table Alters/Drops/Updates/Deletes
 Source  Target Bufferpool/Config changes (dbconf.xls)
 Source  Unload completion, No further access required
 Target  No like named schema objects permitted
 Target  WLM, Loadlib
 DB2 V7 Source  DB2 V8 Target
 Parallel Sync – Websphere Business Integration (WBI)
2
IBM Corporation
 Pre-Planning
 Project Plan – Required disciplines (…who gets to help?!!?)
 Development upgrade outputs (custrep.dat, schema.ddl)
 Mid-Tier & z/Series shared upgrade database activities
 Avoid:
SIEBEL as z/OS dataset HLQ
SIEBEL as the DB2 Tableowner
 Test-Source & Test-Target & Parallel sync versions
 CPU & DASD (shared), Sort, Memory, WLM required
 Initiators, Parallel workload
 Job CLASS, ACCTNG, MSGCLASS
 Jobname Prefixes (first 3 characters Siebel reserved)
 Codepage considerations, No UNICODE support in 7.8
 Job Scheduler
3
IBM Corporation
 Pre-Planning – How do you want to run the upgrade jobs?
 Siebel Scheduled Mode
REXX based job submission exec’s
Auto-submission of Flow dependencies
 Abend (U0099) or invalid RC – process halts
 RC check enforced by JCL INCLUDEs
– RC=0, RC<=4, RC<=8
Siebel Logging (not all jobs)
 Vendor Scheduled Mode (3rd Party Scheduler)
PROC modifications only, JCL INCLUDE added to PROC
RC checks enforced by JCL INCLUDEs
No Siebel Logging
Manual load of scheduler system, no interface
4
IBM Corporation
 Siebel 7.8 Production Upgrade Flow
Start
Finish
Repository
Import
Development
Upgrade
Restart
DB Srvr Cfg
Schema
&
Storage
files
Mid-Tier
-
z/Series
z/Series
Pause #1
FTP to z/OS Host
-JCL/SQL Templates
-REXX Code
-Data Migration
-Build JCL & SQL
-Modify Source
-Execute Source Chgs
Source
Pause #2
FTP to z/OS Host
-Unload/Load JCL
-New 7.7/7.8 DDL
-7.7/8 DDL
Pause #3
FTP to z/OS Host
-Modified 7.8 Indexes
-EIM 7.8 structures
-ITM/Dedup JCL/SQL
-Build DDL & Upgrade
-Execute Target chgs
-Build JCL & Upgrade
-Execute Target chgs
Target
Target
z/OS DB2
z/OS DB2
z/OS DB2
5
IBM Corporation
 Mid-Tier Pauses interleaved with z/Series processes
 Pause 1:
 Upgrade JCL & REXX code
 Stored Procedures, Loadlib, DBRMs
 Data Migration SQL scripts
 Source EIM table/tablespace drops – “JCL Reuse”
 Source Alters, Updates, Mass-Delete, Table drops
 Pause 2:
 Unload/Load utility JCL/SQL templates
 7/7 & 7.8 Schema (DDL)
 Temp tables (not Global Temp, static work tables)
 Pause 3:
 Modified structure of old to new NPI 7.7/7.8 indexesIntersection Table
Maintenance (ITM)
– SQL scripts for dedup’ing
– JCL templates
6
IBM Corporation
 Mid-Tier Pause #1 generated templates/files
Procs
REXX code
Data Migration SQL/JCL
7
IBM Corporation
Pause #1 - z/Series Upgrade Main Menu (Receive & Set-Up)
Pause #1 FTP
-Run JOB0
-REXX Code
-JCLLIB
-SP JCL/Modules
Set-Up
Receive
-Assign DSNHLQ
-Assign Jobname prefix
-Assign Encoding
-Assign DB2/DSN libs
-Load Lib
-DBRMs
-SP Modules
2
Alloc/Populate
-INSTALL lib
Alloc PDS/Seq
Populate
-JCL/SQL Templates
-JCL/SQL Templates
8
IBM Corporation
Pause #1 - z/Series Upgrade Main Menu (PreT)
“Source” Database Changes
HouseHold
(optional)
PreTedit
Add Jobcards
-Scheduling Mode
-PreT
-PreT-FINS
-Household
-Household-FINS
-Identify CLOBs
-Delete s_dock_txn_log
-Delete s_escl_req
-Build OrderBy’s
Run PreT
Run PreT-FINS
Alters/Drops/Deletes
Alters/Drops/Deletes
Drop EIM Tables
9
IBM Corporation
 Siebel 7.8 Production Upgrade Flow
Start
Finish
Repository
Import
Development
Upgrade
Restart
DB Srvr Cfg
Schema
&
Storage
files
Mid-Tier
-
z/Series
z/Series
Pause #1
FTP to z/OS Host
-JCL/SQL Templates
-REXX Code
-Data Migration
-Build JCL & SQL
-Modify Source
-Execute Source Chgs
Source
Pause #2
FTP to z/OS Host
-Unload/Load JCL
-New 7.7/7.8 DDL
-7.7/8 DDL
Pause #3
FTP to z/OS Host
-Modified 7.8 Indexes
-EIM 7.8 structures
-Build DDL & Upgrade
-Execute Target chgs
-Build JCL & Upgrade
-Execute Target chgs
Target
Target
z/OS DB2
z/OS DB2
z/OS DB2
10
IBM Corporation
 Mid-Tier Pauses interleaved with z/Series processes
 Pause 1:
 Upgrade JCL & REXX code
 Stored Procedures, Loadlib, DBRMs
 Data Migration SQL scripts
 Source EIM table/tablespace drops – “JCL Reuse”
 Source Alters, Mass-Delete
 Pause 2:
 Unload/Load utility JCL/SQL templates
 7/7 & 7.8 Schema (DDL)
 Temp tables (not Global Temp, static work tables)
 Pause 3:
 Modified structure of old to new NPI 7.7/7.8 indexes
 Intersection Table Maintenance (ITM)
– SQL scripts for dedup’ing
– JCL templates
11
IBM Corporation
 Mid-Tier Pause #2 generated templates/files
12
IBM Corporation
Pause #2 - z/Series Upgrade Main Menu (Schema/JCL Prep)
Pause #2 FTP
-Unload/Load
-7.7 Idx DDL
-7.8 DDL
0
Mod Unl/Load
-All Tables
1
Schema/Verification
Build JCL/Scripts
-CLOB (special)
-Execute 7.8 DDL
-Build Temp/Log Tables
-Install Stored Proc’s
-Scheduling Mode
-Prep JCL/SQL
-Prep 7.7 Index Rebuilds
-Old Schema Indexes
Unload/Load/Index
Optimization
2
13
IBM Corporation
Pause #2 - z/Series Upgrade (Job-Stream Optimization)
4
Pause 1-2
Pause 3
14
IBM Corporation
Pause #2 - z/Series Upgrade Main Menu (Upgrade)
5
Pauses 1-2
Pause 3
15
IBM Corporation
 Siebel 7.8 Production Upgrade Flow
Start
Finish
Repository
Import
Development
Upgrade
Restart
DB Srvr Cfg
Schema&
Storage
files
Mid-Tier
-
z/Series
z/Series
Pause #1
FTP to z/OS Host
-JCL/SQL Templates
-REXX Code
-Data Migration
-Build JCL & SQL
-Modify Source
-Execute Source Chgs
Source
Pause #2
FTP to z/OS Host
-Unload/Load JCL
-New 7.7/7.8 DDL
-7.7/8 DDL
Pause #3
FTP to z/OS Host
-Modified 7.8 Indexes
-EIM 7.8 structures
-ITM/Dedup JCL/SQL
-Build DDL & Upgrade
-Execute Target chgs
-Build JCL & Upgrade
-Execute Target chgs
Target
Target
z/OS DB2
z/OS DB2
z/OS DB2
16
IBM Corporation
 Mid-Tier Pauses interleaved with z/Series processes
 Pause 1:
 Upgrade JCL & REXX code
 Stored Procedures, Loadlib, DBRMs
 Data Migration SQL scripts
 Source EIM table/tablespace drops – “JCL Reuse”
 Source Alters, Mass-Delete
 Pause 2:
 Unload/Load utility JCL/SQL templates
 7/7 & 7.8 Schema (DDL)
 Temp tables (not Global Temp, static work tables)
 Pause 3:
 Modified structure of old 7.7 to new 7.8 NPI indexes
 EIM 7.8 structures
 Intersection Table Maintenance (ITM)
– SQL scripts for dedup’ing
– JCL templates
17
IBM Corporation
 Mid-Tier Pause #3 generated templates/files
ITM/Dedup
SQL + JCL
SCINDX sql
18
IBM Corporation
Pause #3 - z/Series Upgrade Main Menu (New-Schema Indexes)
Old 7.7
NPIs
New 7.8
NPIs
19
IBM Corporation
Pause #3 - z/Series Upgrade Main Menu (Upgrade)
Pause 1-2
Pause 3
20
IBM Corporation
 Siebel 7.8 Production Upgrade Flow
Start
Schema&
Storage
files
Mid-Tier
-
z/Series
z/Series
Finish
Repository
Import
Development
Upgrade
Pause #1
FTP to z/OS Host
-JCL/SQL Templates
-REXX Code
-Data Migration
-Build JCL & SQL
-Modify Source
-Execute Source Chgs
Source
Restart
DB Srvr Cfg
Pause #2
FTP to z/OS Host
-Unload/Load JCL
-New 7.7/7.8 DDL
-7.7/8 DDL
Pause #3
FTP to z/OS Host
-Modified 7.8 Indexes
-EIM 7.8 structures
-ITM/Dedup JCL/SQL
-Build DDL & Upgrade
-Execute Target chgs
-Build JCL & Upgrade
-Execute Target chgs
Target
Target
z/OS DB2
z/OS DB2
z/OS DB2
21
IBM Corporation
Performance Improvement
Considerations
22
IBM Corporation
 Eliminating Critical Path Mid-Tier & JCL File Generation
 Eliminate:
 Mid-Tier JCL/SQL template re-generation
 “Source” production database modifications
 Need to re-FTP file templates from Mid-Tier
 z/OS JCL re-generation
Siebel
“NSCR”
 DB2 DDL re-generation
 Index JCL/DDL utility re-generation
 ITM Dedup Processes
 Any customizations you may have applied…
 Generated SQL/JCL from Pause’s 1-3
 Generated for Test, reused for actual Production
Siebel
“NSCR”
 Modify LPAR, DB2 Subsystem, Tableowner & DB Prefix
 Based on true-tested JCL, SQL and DDL
23
IBM Corporation
 Unload/Load Overlap & Sequencing
 Parallel limiting factors:
Initiators
DASD, Sortwork
Memory
Tablespace Configuration (1:1)
 Unload via DSNTIAUL
Warning: RC=4 for empty tables or non-translatable characters
 “SQLSTATE 01517: A character that could not be converted was replaced with a substitute character.”
Non-Translatable SQLSTATE code not considered sql error (RC=4)
PROCs + Dynamically allocated
 SYSDA, DATACLAS=COMPACT (default)
– Dataset placement & retention (out-of-the-box)
 IBM Unload or CrossLoader or 3rd party utilities (like BMC)
24
IBM Corporation
 Unload/Load Overlap & Sequencing - continued
 Unload Sequencing – Largest 1st
Re-order generated job submission REXX exec sequence
 Overlap Unload & Load (1:1)
Requires manual modifications to REXX submission execs
Use a job scheduler to trigger
Load M:1 must sequence with final serialized tablespace load job
Load 1:1 - Statistics collected during load
 Modify PROCs
Two Load type allocations – (dsnhlq.siebel.tablist)
IBM Load
Swap-out with 3rd party utilities
 CASE-cast statements used for data migration in Unloads
Consideration when using 3rd party utilities
25
IBM Corporation
 Unload/Load Overlap & Sequencing - continued
 Unloads can be modified to use ORDER BY clustered key
dsnhlq.siebel.syskeys.orderby (generated dataset)
Careful: Sortwork requirements
 Unload by Partition (not generated OOTB)
Requires manual duplication of JCL
Manual specification of partition number
 Large Non-Partitioned
Manually specify unload control card Key Ranges
Requires manually building JCL and SQL modifications
 CLOBs
2 Unloads
 Length > 0 for CLOB column
1 Load-1 Update
26
IBM Corporation
 Unload/Load & Index Rebuild Thru-put Optimization
 Job-Stream Optimization (JSO)
 1:1 Database configuration only
 Table with zero rows eliminated
 Thru-put performance improvement
 Unloads
 Loads (1:1 only)
 Index Rebuilds
 SORTKEYS used
 Statistics collected
 REXX submission execs modified automatically
 7.8 Panel-driven JCL submission
 Verifies 1:1 configuration for 1:1
 Specify Number of Indexes per index rebuild job
 More indexes per job, more sort memory required
27
IBM Corporation
 Data Migration Tuning
 Required Dependency Flow
Manual analysis to determine parallel streams
Difficult and complex
Critical-path likely longer than all others combined
Siebel
“NSCR”
 Large Tables involved in Insert/Update
Translate Update/Insert to Select Count(*)
 Long running DML may not touch any rows
 Move SQL UPDATE to Unload (CASE)
S_EVT_ACT …
Siebel
“NSCR”
APPT_REPT_WK_MO,
CASE when (APPT_START_TM is not null and APPT_START_DT is not null)
then timestamp(date (APPT_START_DT),
time (APPT_START_TM)) else APPT_START_DT end,
APPT_START_TM,
ASGN_DNRM_FLG, …
28
IBM Corporation
 Data Migration Tuning - continued
 Convert From Insert-Into/Update to Unload-Load
Large tables, including Temp tables
Compare options - SQL vs. Utility
 Index analysis
Indexes are “Old” schema indexes – Pause #2
“New” schema structures 7.8 Indexes are generated – Pause #3
Defer NPI builds, determine which needed to support sql scripts
Add indexes back after Data Migration
 Stored Procedures
WLM, Loadlib – if common, verify no modules with same names
29
IBM Corporation
 Generated Component Re-use - Test to Production
 Complexity of Mid-Tier & z/OS swapping, avoided if you Reuse
 In-Advance Tasks
 Target DDL, Temp work tables
 DEFINE NO
 Siebel Source & Target Logging tables
 ITM/Dedup Processes
 Generated JCL & SQL
Siebel
“NSCR”
 Test environment = Production environment
 Parameter Changes
 SYSAFF (LPAR, if cross lpar upgrade)
 DB2 Subsystem
 Database Prefix (4 byte)
 Stogroup - SYSDEFLT
 Tableowner
 SYSDA
 DSNTEP2/DSNTIAUL plan names
30
IBM Corporation
 Performance Summary for Upgrading 7.7 to 7.8
 Make sure to have adequate initiators, resources for parallelism
Unload & Loads
Index Rebuilds (sort)
 Translate Insert/Update for Data Migration to Select COUNT(*)
Bypass scripts that Update/Insert zero rows
 Move data migration scripts to Unload where possible
 Analyze Data Migrations script index requirements
Siebel
“NSCR”
Defer NPI Index rebuilds to after Data Migration completed
 Unload/Load & Index Rebuild optimization
 Build Target schema in advance
 Build Temp & Siebel logging tables in advance
 JCL/Script Reuse – Avoid crit-path re-generation
Siebel
“NSCR”
31
IBM Corporation
 Future Siebel Database Upgrade
Siebel 8 Upgrade
32
IBM Corporation
 Siebel 7.7 to 8.0 Development & Production Upgrades
 In-Place
 Source & Target the same physical structure
 8K Bufferpools supported (i.e. generated)
 DB2 V8 required, with APAR ii13985 and DB2 Connect V8.2 Fixpack 8.5
 Support for UNICODE – Separate migration required
 Development & Production – Same code base
 REXX panels – “Bookmarks”
 Siebel & Vendor Scheduler Modes – Logging for both modes
 All z/Series based database activities
 Non Critical-Path Mid-Tier/JCL Generation
 Additive Schema - Non-Disruptive
 Alter, Create
 Non-Additive Schema - Disruptive
 Table Rebuild, Add/Restructure Indexes
 Older the schema version  More Non-Additive  Longer the upgrade
33
IBM Corporation
 Siebel Database Upgrade
So, How long will the upgrade take?
It depends…
34