DB2 V7 at Sidmar : migration story new features in use

1
DB2 V7 at Sidmar - migration story -
new features in use

• GSE IMS/DB2 - sidmar 7/11/2002
• davy goethals

2
SIDMAR
3
Sidmar steelworks at the coast
4
Production capacities SIDMAR
2 sinter plants 6,0 Mt/y
coke plant 1,3 Mt/y
2 blast furnaces 3,9 Mt/y ? 4,5 Mt/y
BOF-steelshop 4,4 Mt/y ? 5,0 Mt/y
continuous casting machine I 4,0 Mt/y
continuous casting machine II 2,5 Mt/y
hot strip mill 5,5 Mt/y
cold rolling mill 3,8 Mt/y (TTS Tandem 2)
BAF skinpass 1,5 Mt/y
CAPL 0,8 Mt/y
hot dip galvanising lines 1,25 Mt/y Galtec,
Sidgal 2 3, Segal
electrolytic coating Sikel 0,41 Mt/y
laser-welded blanksTailor Steel (Gent Genk)
7,5 x 106 pieces/y
organic coating Decosteel 12 0,35 Mt/y
5
Sales by sector of use
Secondary transformation
Primary transformation
Others
Distribution
Transport
6
Arcelor strategic motivation

• Largest steel companies in 2001
• (Mt crude steel)

Estimation
gt Arcelor steel production 5 steel
production world-wide (845 Mt in 2001)
7
Distribution of personnel
Number of employees 31/12/2001 108 000 people
8
Arcelor strong presence in Europe...
Sector flat carbon products
9
DB2 history at Sidmar

• 1984 DB2 V1.1 infocenter (QMF)
• 1987 DB2 V1.3 production DLI/DB2 migration of
  main IMS application
• 1988 ESP DB2 V2.1 referential integrity
• from 1989 onwards ESP/QPP for DB2 V2.2, V2.3,
  V3, V4, V5, V6, V7
• 2001-2002 datasharing enablement
• 2002 all subsystems in V7 production

10
mainframe configuratie
11
DB2 current subsystems
IMS
IMS
TSO
DB2P
DB2O
DB2G
DB2T
2 way datasharing
2 way datasharing
2 way datasharing
2 way datasharing
systemtest V7
V7
V7
V7
production 250 MIPS 150 GB 2400 tables
development 180 MIPS 150 GB 3800 tables
datawarehouse 230 MIPS 600 GB 47000 tables
ALZ
datasharing
V6
12
DB2 V7 features at a glance

• SQL enhancements
• Union everywhere
• Scrollable cursors
• Row expressions in IN predicate
• Limited fetch
• Enhanced management of constraints
• Language support
• Precompiler services
• SQL Stored Procedures
• Java support
• Self-referencing subselect on Update or Delete

• DB2 Extenders
• XML Extender
• Utilities
• Dynamic utility jobs
• New utility UNLOAD
• More Load partition parallelism
• Online Reorg (fast switch)
• Online Load Resume
• Statistics history
• Network computing
• Global transactions
• Security enhancements
• Unicode support
• Network monitoring

13
DB2 V7 features at a glance

• Performance and availability
• Online subsystem parameters
• Log manager enhancements
• Consistent restart enhancements
• Adding space to the workfiles
• Allow DBADM to create views
• Data Sharing
• Coupling Facility name class queue
• Group Attach enhancements
• Immediate write bind option

• Restart Light
• Persistent CF structure sizes
• Installation and Migration
• Migration from V5 or V6 to V7
• New features and tools
• Warehouse Manager
• Net Search Extender
• New and enhanced tools

14
DB2 V7 milestones at Sidmar

• april 2000 IBM announcement DB2 V7
• sept 2000 ESP education IBM Hursley uk
• feb 2001 smp install beta code migrate DB2T
  to V7
• feb 2001 - june 2001 testing V7
• march 2001 V7 GA
• june 2001 end ESP

15
DB2 V7 milestones at Sidmar

• may-june 2001 redbook DB2 V7 Using the
  Utilities Suite - SG24-6289
• 18 sept 2001 migrate DB2O V7
• 29 okt 2001 migrate DB2P V7
• 11 jan 2002 DB2O datasharing enable
• 24 jan 2002 DB2P DB2G datasharing
• 19 feb 2002 migrate DB2G V7

16
DB2 V7 migration preparation

• prepare installation carefully
• fall back maintenance on all members
• third party tools
• server pack (recommended service level)
• check program directory psp bucket
• receive hipers
• adjustments release incompatibilities
• new dsnzparm parameters
• save critical access paths
• follow entire procedure installation guide

17
DB2 V7 migration troubles

• we did all this but when migrating DB2G
• CATMAINT

DSNP007I -DBG2 DSNPXTN0 - EXTEND FAILED FOR
DB2.DSNDBD.DSNDB06.D2TABSX1
.I0001.A001. RC00C900A6

CONNECTION-IDDBG2, CORRELATION-ID010.PFTXTN01
, LUW-ID
d2tabsx1 is a user defined
catalog index on sysibm.systables
sysibm.systables(dbname,tsname)

18
DB2 V7 migration troubles

• DSNREXX distributed applications
• 
  
  
• ATTRIBUTES clause (scrollable cursors)
• V7 to V6 is not supported
• use V6 dsnrexx
• V7 to V7 only drda protocol
• bind dsnrexx packages with DBPROTOCOL(DRDA)
• bind dsnrexx packages on remote DB2s

SQLCODE-582 during PREPARE distributed
applications using private protocol
19
DB2 V7 migration troubles

• ODBC client-server applications using DB2 connect
  
• create temp databases on ds members
• disable scrollable cursor support on odbc
  clients PATCH2 6 in DB2CLI.INI

RESOURCE UNAVAILABLE on TEMP database
20
DB2 V7 migration troubles

• create temp databases on ds members for declared
  global temporary tables

CREATE DATABASE TEMPDBG1 AS TEMP FOR DBG1
CREATE TABLESPACE DSN4K01 IN TEMPDBG1
USING STOGROUP SYSDEFLT
PRIQTY 100000
SECQTY 0
ERASE NO
BUFFERPOOL BP1
SEGSIZE 4
MAXROWS 255 CREATE
TABLESPACE DSN32K01 IN TEMPDBG1
USING STOGROUP SYSDEFLT
PRIQTY 10000
SECQTY 0
ERASE NO
BUFFERPOOL BP32K
SEGSIZE 4 MAXROWS 255
21
DB2 V7 migration troubles

• ODBC client-server applications using DB2 connect
  enterprise edition
• disable mode to Execute queries asynchronously
  ( 1 default) ASYNCENABLE 0 in DB2CLI.INI
• install DB2 Connect V7.2 Fixpack 7

• DB2 gateway servers on NT crashed several times
  a day
• various ODBC errors on workstation

22
DB2 V7 migration troubles

• DDL LOCKING held DBD locks till thread
  deallocation
• bind packages of programs doing DDL with
  release(commit) instead of release(deallocate)
  PQ66134 ?

DSNT501I -DBG2 DSNILMCL RESOURCE UNAVAILABLE
CORRELATION-ID........
CONNECTION-IDBATCH
LUW-ID
REASON 00C9008E
TYPE 00000100 (database locks)
NAME ........
23
DB2 V7 migration troubles

• correlated update/delete transformation
• transformation of subquery into join
• also for certain update/delete queries in V7

DELETE
FROM TABLE1 A
WHERE EXISTS (
SELECT 1 FROM TABLE2 B
WHERE A.COL1 B.COL1)
resulted in SQLCODE 100 also Abend 04E
rc00c90101 in DSNIIMSI5008 Abend 04E
rc00e70005 in DSNXOHJCP005
24
DB2 V7 migration troubles

• fortunately V7 apar PQ45052 introduced the
  possibility to disable the subquery
  transformation into join by 2 new hidden ZPARMs
  in macro DSN6SPRC
• SPRMTRSU SPRMTRSS
• Sidmar problems permanently fixed by PQ59163 and
  PQ60434
• but be aware of new apars in this area e.g.
  PQ62259,PQ65666

25
DB2 V7 migration troubles

• Last but not least The DB2 systems programmer
  biggest nightmare
• the please DO FALLBACK case !!
• severity 1 pmr opened aparfix AQ58496 in 3 days
  thank you IBM

SELECT A.
FROM TABLE1 A
, TABLE2 B WHERE

A.COL1 BETWEEN B.COL2 AND B.COL3 -----gt partial
result set incorrout
26
DB2 V7 migration troubles

• however fix introduced new incorrout
• another aparfix AQ58666 in 3 days
• please check if UQ63951 and UQ64079 are applied

SELECT MAX(DAT_DOC) FROM
U.SCHEMA WHERE
DAT_DOC IS NOT NULL AND
KLANT 'AUBRY SOCI'
AND REFNR'BXEO111' -----gt returns NULL value
27
DB2 V7 new functions in use

• utilities suite
• templates
• exec sql
• online reorg
• cross loader
• listdef
• percentile UDF
• scalar SQL functions
• scrollable cursors
• others

28
V7 utilities suite

• operational utilities
• COPY
• REBUILD INDEX
• RECOVER
• LOAD
• REORG TS IX
• RUNSTATS
• EXEC SQL
• STOSPACE
• UNLOAD

• diagnostic recovery utilities
• COPY
• REBUILD INDEX
• RECOVER
• CHECK DA,IX,LB
• MERGECOPY
• COPYTOCOPY
• MODIFY recovery
• MODIFY statistics

29
templates

• dynamic allocation of (work) datasets
• no more DD cards with precalculated space and
  fixed dataset names
• automatic dataset sizing (RUNSTATS!)
• can be used in most db2 utilities
• reduction of utility job maintenance cost
• enforcements of naming standards

30
templates

• TEMPLATES can be coded in
• utility SYSIN input
• SYSTEMPL DD card
• datasets are only allocated when needed
• templates can contain variables to build a unique
  dataset name for each run of the utility

31
template variables

• jobname,stepname,userid,ssid
• util,ictype,locrem,pribac
• db,ts,is,sn,part,list
• date,year,month,day,jdate, jday,time,hour,
  minute,second
• space options dcb,sms,bufno,retpd,gdg...
• disk options cyl,trk,mb,pctprim,...
• tape options uncnt,stack,...

32
templates (reorg example)
TEMPLATE TSYSCOP1

DSN('DB2IM.SS..Z1.DB..SN..IC.JU(3,5)..TI(1,
4).') DISP(MOD,CATLG,CATLG)

TEMPLATE TSYSCOP2

DSN('DB2IM.SS..Z2.DB..SN..IC.JU(3,5)..TI(1,
4).') DISP(MOD,CATLG,CATLG)

TEMPLATE TSORTOUT DSN('DB2RE.SS..Z1.DB..SN..S
JU(3,5)..TI(1,4).')
DISP(MOD,DELETE,CATLG)
TEMPLATE TSYSREC
DSN('DB2RE.SS..Z1.DB..SN..RJU(3,5)..TI(1,4).
') DISP(MOD,DELETE,CATLG)
TEMPLATE
TSYSUT1 DSN('DB2RE.SS..Z1.DB..SN..UJU(3,5)..
TI(1,4).') DISP(MOD,DELETE,CATLG)
UQ62776 introduced substring of template variables
33
templates
DSNU1038I DSNUGDYN - DATASET ALLOCATED.
TEMPLATETSYSREC
DDNAMESYS00001

DSNDB2RE.DB2O.Z1.SIDDAGO.SIDDAGO.R02308.1417
DSNU1038I DSNUGDYN - DATASET ALLOCATED.
TEMPLATETSYSCOP1
DDNAMESYS00002

DSNDB2IM.DB2O.Z1.SIDDAGO.SIDDAGO.F02308.1417
remark time is GMT and not local
34
exec sql

• new V7 utility control statement
• executing non-select dynamic SQL statement
  create,delete,grant,set ...
• declare a cursor for the cross loader

EXEC SQL sql statement or declare
cursor ENDEXEC
35
exec sql

• new utility phase called EXEC phase
• separate thread
• needs package DSNUGSQL
• each EXEC SQL is unit of work (commit)
• sql checked during execution and not during
  utilinit
• sqlerrors --gt never abend but RC8
• skipped during RESTART , except SET and DECLARE
  statements

36
exec sql

• used at Sidmar to simplify JCL of online reorg
• EXEC SQL alter tablespace PRIQTY
• EXEC SQL create database for mapping table
• EXEC SQL create tablespace for mapping table
• EXEC SQL create mapping table
• REORG SHRLEVEL CHANGE
• EXEC SQL drop database with mapping table

37
V7 online reorg

• shrlevel NONE (catalog lobs only)
• shrlevel REFERENCE (ro tables)
• shrlevel CHANGE (sidmar default)
• inline statistics
• discard processing
• thresholds leafdistlimit,offposlimit,indreflimit
  
• unload external

38
V7 online reorg
39
V7 online reorg
40
online reorg fast switch

• V6 shadow datasets S0001 (instance)
• V7 shadow datasets I0001 or J0001
• fast switch do not rename VSAM shadow dataset
  to original dataset but let DB2 use shadow
  dataset as active dataset instead
• IPREFIX column in SYSTABLEPART and SYSINDEXPART
• delete original dataset during utilterm
• fast switch is default mind dsnames in jcl

41
online reorg sidmar syntax
REORG TABLESPACE dbname.tsname
COPYDDN(TSYSCOP1)

SHRLEVEL CHANGE MAPPINGTABLE
R.jobname_tsname MAXRO 20
DRAIN ALL
DRAIN_WAIT 20 RETRY 120
RETRY_DELAY 60 TIMEOUT TERM
SORTDEVT 3390 SORTNUM 6
WORKDDN(TSYSUT1,TSORTOUT)
UNLDDN(TSYSREC)
STATISTICS TABLE ALL
INDEX(ALL KEYCARD
FREQVAL NUMCOLS 1 COUNT 10

FREQVAL NUMCOLS 2 COUNT 10

FREQVAL NUMCOLS 3 COUNT 10

FREQVAL NUMCOLS 4 COUNT 10

FREQVAL NUMCOLS 5 COUNT 10)
set drain_wait lt IRLMRWT
42
online reorg sidmar syntax
REORG TABLESPACE dbname.tsname
SORTDATA SORTKEYS NOSYSREC
COPYDDN(TSYSCOP1)
SHRLEVEL REFERENCE
DRAIN_WAIT
20 RETRY 120 RETRY_DELAY 60 TIMEOUT TERM
SORTDEVT 3390 SORTNUM 6

WORKDDN(TSYSUT1,TSORTOUT) UNLDDN(TSYSREC)
STATISTICS TABLE ALL
INDEX(ALL
KEYCARD FREQVAL NUMCOLS 1 COUNT 10

FREQVAL NUMCOLS 2 COUNT 10

FREQVAL NUMCOLS 3 COUNT 10

FREQVAL NUMCOLS 4 COUNT 10

FREQVAL NUMCOLS 5 COUNT 10)
43
online reorg sidmar

• using the new V7 features we are able to reorg
  all our DB2 databases while applications are
  online
• no reorg part when npi
• all this without any timeout
• inline statistics are gathered in parallel
• using exec sql and templates we keep our jcl
  extremely simple

44
online reorg retry switch
new message DSNU1122I
DSNU1122I -DBG1 DSNURLOG - JOB UTEDV05P
PERFORMING REORG WITH UTILID UTEDV05P
UNABLE TO DRAIN EPDMDB.DRLSMVS5 RETRY 1
OF 120 WILL BE ATTEMPTED IN 60 SECONDS
45
cross loader

• new feature of the LOAD utility
• input is not a sequential file but the result set
  of a dynamic SQL query
• source data in query are local or remote DB2
  table(s)
• source can be on any drda server or the result of
  datajoiner
• replaces unload,filetransfer,load

46
cross loader
EXEC SQL DECLARE c1 CURSOR FOR select
query ENDEXEC LOAD DATA INCURSOR c1
INTO TABLE creator.table
47
cross loader

• used currently to move data from one db2
  subsystem to another

EXEC SQL
DELETE FROM SYS.UTLDSNC
ENDEXEC

EXEC SQL
DECLARE C1 CURSOR
FOR
SELECT CYCLUS AS CORRID,TOSSID AS SSID,TODBNAME
AS DBNAME, TOTSNAME AS
TSNAME,PART_ID AS PART_ID
FROM DB2P.SYS.COPYCYCL WHERE COPY 'Y'
ENDEXEC
LOAD DATA
RESUME(YES) WORKDDN(TSYSUT1,TSORTOUT)
INCURSOR C1
INTO TABLE
SYS.UTLDSNC
48
cross loader

• any valid sql query (join,union,udf)
• use 3 part names to load from remote data
• package DSNGUSQL must be bound on the remote
  location
• load replace supported
• no online load (shrlevel change)
• use order by to load in clustering seq.
• use as-clause to match column names

49
cross loader

• matching by name, not by sequence
• IGNOREFIELDS YES supported
• missing columns are DEFAULT-ed
• use casting UDFs if normal load conversion not
  applicable
• cursor name can only be declared once but used in
  multiple LOAD statements

50
listdef

• provide wildcarding
• dynamically generate a list of tablespaces or
  indexspaces that match a pattern (,_ ,?)
• dynamically generate a list of tablespaces
  corresponding with a certain DB,TB,IX
• dynamically generate a list of indexspaces
  corresponding with a certain DB,TS,TB,IX
• dynamically generate objects related by RI or LOB
  columns
• include/exclude lists

51
listdef

• all V7 utilities now accept a list of tablespaces
  or indexspaces as input
• exception CHECK DATA (also
  diagnose,repair)
• must be used together with templates if more than
  one dataset is needed (ex image copy of list of
  tablespaces)

52
listdef

• at Sidmar patterns are not yet used
• requires rigid naming conventions
• one database - one tablespace - one table
• used mainly for auxiliary objects (lobs)
• each lob column has a separate lob tablespace and
  auxiliary table,index
• when partitioned one auxiliary object per lob
  column and per partition

53
listdef example
reorg first partition of a partitioned tablespace
containing lob columns and corresponding lob
tablespaces
REORG TABLESPACE dbname.tsname PART 1
SHRLEVEL CHANGE
TIMEOUT TERM MAXRO 20 DRAIN ALL
DRAIN_WAIT 20 RETRY
40 RETRY_DELAY 60
SORTDEVT 3390 SORTNUM 6
COPYDDN(TSYSCOPY) UNLDDN(TSYSREC)
WORKDDN(TSYSUT1,TSOR
TOUT)
LISTDEF LIJST INCLUDE TABLESPACE dbname.tsname
PARTLEVEL(1) LOB REORG TABLESPACE LIST
LIJST
SHRLEVEL NONE
WORKDDN(TSYSUT1,TSORTOUT)
UNLDDN(TSYSREC)
54
utilities - others

• tested but not yet in production
• online load resume
• unload (from table or image copy)
• to be tested
• statistics history tables
• real time statistics

55
much much more on DB2 V7 utilities
sg24-6289-00
www.redbooks.ibm.com
56

• davy.goethals_at_sidmar.arcelor.com
• tel [phone]