Transcript No Slide Title

Volvo Information Technology
DB2 Version 7
The complete story?
2540
OS/390
UNIX
Issuer : DB-Team 2540
Issued : 2002-02-25
Win2000
Volvo Information Technology
Dept. 2540
Slide: 1
Volvo Information Technology
DB2 UDB Server for
OS/390 and z/OS
Version 7
Volvo Information Technology
Dept. 2540
Slide: 2
Volvo Information Technology
It started nearly 2 years ago…...
•
•
•
•
•
In June 2001 we did the first installation in technical test
In September 2001 Customer Test (D2VT) was upgraded to
V7
Few problems during Customer Test. CAE + Vinfo
18th of November 2001 the subsystems in V201 & V202
was upgraded to V7
and…...
Volvo Information Technology
Dept. 2540
Slide: 3
Volvo Information Technology
DB2 Version 7 PROBLEMS!!!
•
•
•
Customer Test.
• Class-package of new DB2-client DB2RTC was delayed, old
client (CAE V5.2) causes the Connect Server to Stop.
• Application abend, fixed by Rebind.
Prod-inst. 18/11.
• Lots of abends, DB2 stops, decision to do a Fallback to V6
(19/11).
IBM Fix.
• Upgrade of D2BM during a weekend to verify the fix. Still
Problems. Fallback to V6 again.
Volvo Information Technology
Dept. 2540
Slide: 4
Volvo Information Technology
DB2 Version 7 PROBLEMS!!!
•
•
•
The Problem. The error seem to be caused by Plan or
Packages where latest bind was done in DB2 V2.3.
Rebind February. Central Rebind was done of all ‘old’
plans & packages.
The 17th of February. All subsystems in Gothenburg up
and running on V7
Volvo Information Technology
Dept. 2540
Slide: 5
Volvo Information Technology
DB2 Version 7 PROBLEMS!!!
•
•
•
DB2RTC. Test of new client and DB2 V7. Char and Varchar
field with Local Characters might be truncated if accessed
from Office/2K
DB2RTC. Did not work together with Business Object
OTG. Old version of Gateway did not work with V7.
Fallback to V6 in D2O1.
Volvo Information Technology
Dept. 2540
Slide: 6
Volvo Information Technology
DB2 Version 7 PROBLEMS!!!
•
•
•
•
•
•
•
•
DB2RTC. Test of new client and DB2 V7. Char and Varchar field
with Local Characters might be truncated if accessed from
Office/2K
We have added Mr ÅKERBÄRGÖ to our Q.Staff, he helps us to
discover codepage-problems.
Now ÅKERBÄRGÖ was shown as ÅKERBÄR in Excel
NAME in STAFF is VARCHAR (9)
2+1+1+1+1+2+1=9
Å K E RB ÄR
DISABLEUNICODE=1 as bypass, but set in Connect Server
disables all client/applications
Solution in FP7….FP8….FP9?
Volvo Information Technology
Dept. 2540
Slide: 7
Volvo Information Technology
DB2 Version 7 PROBLEMS!!!
•
•
•
•
•
•
DB2RTC. Did not work together with Business Object
The version of BO(5.1.1) in our Class environment did not
have support for V7 FP3 (our DB2RTC level)
New package of BO(5.1.3) scheduled but will take time
Our Site in Gent has been running DB2RTC towards the
same level of BO for a while without problems???
They were running V7 without Fixpacks
Bypass , local install of new BO or server-based reports
Volvo Information Technology
Dept. 2540
Slide: 8
Volvo Information Technology
DB2 Version 7 PROBLEMS!!!
•
•
•
•
•
OTG. Old version of Gateway (V4) did not work with V7.
Fallback to V6 in D2O1.
V8.04 installed , still the same problem
While waiting for fix from Oracle we have to run DB2 V6 I 2
subsystems.
No fix will be delivered on V8, wait for 9.2
Still some problems in 9.2, and the Case tools used must
also be certified with v9.2
Volvo Information Technology
Dept. 2540
Slide: 9
Volvo Information Technology
DB2 Version 7 ”PROBLEMS!!!”
•
•
•
REPEAT USER COLLID EXIT LOCATORS RESTRICT USING COLUMN
EXTERNAL LOCK RESULT VALIDPROC COMMENT FENCED LOCKMAX
RESULT_SET_LOCATOR VALUES COMMIT FETCH LOCKSIZE RETURN
VARIANT CONCAT FIELDPROC LONG RETURNS VCAT CONDITION FINAL
LOOP REVOKE VIEW CONNECT FOR MICROSECOND RIGHT VOLUMES
CONNECTION FROM MICROSECONDS ROLLBACK WHEN CONSTRAINT
FULL MINUTE RUN WHERE CONTAINS FUNCTION MINUTES SAVEPOINT
WHILE CONTINUE GENERAL MODIFIES SCHEMA WITH CREATE
GENERATED MONTH SCRATCHPAD WLM CURRENT GET MONTHS
SECOND YEAR CURRENT_DATE GLOBAL NO SECONDS YEARS
CURRENT_LC_CTYPE GO NOT
Harder restriction for Reserved Words gives error at Bind.
Enclose Reserved Words within “, Select “NO” Complete list of
Reserved Words, please see SQL Reference
Insert into T0T261P.VY_RATTIGHET, SQLCODE=-199
DSNT408I SQLCODE = -199, ERROR: ILLEGAL USE OF
KEYWORD SYSTEM, TOKEN ( SELECT WAS EXPECTED
Volvo Information Technology
Dept. 2540
Slide: 10
Volvo Information Technology
DB2 Version 7 ”PROBLEMS!!!”
•
Why ?
• Lack of resources
• Missing routines for Rebind
• Complex environment - Dependencies
Volvo Information Technology
Dept. 2540
Slide: 11
Volvo Information Technology
Fetch First x Row Only
Volvo Information Technology
Dept. 2540
Slide: 12
Volvo Information Technology
Online LOAD Resume
•
•
•
•
+ + Availability
• SQL applications are not drained
+ Ease of use
• No need of INSERT programs
+ Integrity
• Triggers are fired
- Performance
• Compared to offline LOAD
Volvo Information Technology
Dept. 2540
Slide: 13
Volvo Information Technology
DB2 V7 Unicode (UTF8)
•
•
EBCDIC
•
ASCII
•
H å k a n
• 00800000 0200C8D092819540 40404040
• 00800000 020048E56B616E20 20202020
Unicode
• 00800000 020048C3A56B616E 2020202020
Volvo Information Technology
Dept. 2540
Slide: 14
Volvo Information Technology
Volvo Information Technology
Dept. 2540
Slide: 15
Volvo Information Technology
Crossload
•
Maj 2002
•
•
•
Extern applikation utvecklad mot DB2/W2K skulle
verifieras/performance testas I zOS
Data för volyms-test i DB2/w2k , stora volymer – många tabeller –ont
om tid Vad gör vi???
XLOAD var svaret !
Volvo Information Technology
Dept. 2540
Slide: 16
Volvo Information Technology
Hur används Crossload
•
•
•
Crossload används genom att i en std LOAD utility i z/OS via en cursor
peka ut en tabell i ett annat DB2
Om detta ligger på annan plattform så får man konvertering av datat på
köpet
Kolumn-namn måste vara samma eller använd AS
//DSNUPROC.SYSIN
DD *
DECLARE C1 CURSOR FOR
SELECT "ID" , "DEPT" , "JOB" , "YEARS" , "SALARY" , "COMM" ,
"NAME"
FROM UDBCONE.Q.STAFF
ENDEXEC
LOAD DATA
INCURSOR C1 RESUME YES LOG YES
INTO TABLE STAFF
Volvo Information Technology
Dept. 2540
Slide: 17
Volvo Information Technology
Crossload, Prepare CDB
INSERT INTO
SYSIBM.LOCATIONS ( "LOCATION" , "LINKNAME" , "PORT" )
VALUES ('UDBCONE','UDBCONE','50000') ;
INSERT INTO
SYSIBM.IPNAMES ( "LINKNAME" , "SECURITY_OUT" , "USERNAMES","IPADDR" )
VALUES ('UDBCONE','P','O','131.97.56.42' ) ;
INSERT INTO
SYSIBM.USERNAMES ( "TYPE" , "AUTHID" , "LINKNAME" ,"NEWAUTHID" , "PASSWORD" )
VALUES ('O','V00DB21','UDBCONE','DB2ADMIN','DB2ADMIN') ;
Volvo Information Technology
Dept. 2540
Slide: 18
Volvo Information Technology
Bind DSNUTIL For XLOAD Function
BIND PACKAGE(DSNUTIL) MEMBER(DSNUGSQL) ACTION(REPLACE) ISOLATION(CS) ENCODING(EBCDIC) VALIDATE(BIND) CURRENTDATA(NO) KEEPDYNAMIC(NO) DBPROTOCOL(DRDA)
Volvo Information Technology
Dept. 2540
Slide: 19
Volvo Information Technology
BIND -
Bind Packages For
DSNUTIL,SPUFI,DSNTEP2,DSNTIAD
In UDB
PACKAGE(UDBCONE.DSNUTIL) MEMBER(DSNUGSQL) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) CURRENTDATA(NO)
BIND -
PACKAGE(UDBCONE.DSNESPCS) MEMBER(DSNESM68) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND)
BIND PACKAGE(UDBCONE.DSNTEP2) MEMBER(DSNTEP2) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND)
BIND PACKAGE(UDBCONE.DSNTIAD) MEMBER(DSNTIAD) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND)
BIND PACKAGE(UDBCONE.CF254) MEMBER(DSNTIAUL) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) CURRENTDATA(NO)
Volvo Information Technology
Dept. 2540
Slide: 20
Volvo Information Technology
REBIND
SPUFI,DSNTEP2,DSNTIAD,DSNTIAUL
With DBPROTOCOL(DRDA) In z/OS
REBIND PACKAGE(DSNESPCS.DSNESM68) DBPROTOCOL(DRDA)
REBIND PACKAGE(DSNESPRR.DSNESM68) DBPROTOCOL(DRDA)
REBIND PACKAGE(DSNTIAD.DSNTIAD.(*)) DBPROTOCOL(DRDA)
REBIND PACKAGE(DSNTEP2.DSNTEP2.(*)) DBPROTOCOL(DRDA)
REBIND PACKAGE(CF254.DSNTIAUL.(*)) DBPROTOCOL(DRDA)
REBIND PLAN(DSNESPCS) PKLIST(*.DSNESPCS.DSNESM68) DBPROTOCOL(DRDA)
REBIND PLAN(DSNESPRR) PKLIST(*.DSNESPRR.DSNESM68) DBPROTOCOL(DRDA)
REBIND PLAN(DSNTIAD) PKLIST(*.DSNTIAD.*)
REBIND PLAN(DSNTEP2) PKLIST(*.DSNTEP2.*)
DBPROTOCOL(DRDA)
DBPROTOCOL(DRDA)
REBIND PLAN(DSNTIAUL) PKLIST(*.CF254.DSNTIAUL)
Volvo Information Technology
Dept. 2540
Slide: 21
DBPROTOCOL(DRDA)
Volvo Information Technology
Crossload, First try
•
CDB-definition
•
Location namn måste vara Databas namn på W2K, Alias fungerar ej
•
•
Hur är nuvarande status?
Currentdata(NO) viktigt
•
Första loaden tog hur lång tid som helst…..
•
•
•
Restriktion , Fel
Lägg till FOR FETCH ONLY, rena dunderkuren
Bindade om package med Currentdata(NO) för att erhålla blockning
av data
Och hur blev resultatet?
Volvo Information Technology
Dept. 2540
Slide: 22
Volvo Information Technology
Crossload, Source Tabell
SELECT *
FROM UDBCONE.Q.STAFF WHERE ID BETWEEN 80 AND 100 ;
---------+---------+---------+---------+---------+---------+---ID
NAME
DEPT
JOB
YEARS
SALARY
COMM
---------+---------+---------+---------+---------+---------+---80
JAMES
20
CLERK
------
13504.60
128.20
90
KOONITZ
42
SALES
6
18001.75
1386.70
PLOTZ
42
MGR
7
18352.80
---------
99
ÅKERBÄRGÖ
10
MGR
7
18357.50
---------
91
AÄÖÅABCDE
2540
ÅTEST
12
23.45
23.45
92
ABÖÅABCDE
2540
ÅÄEST
12
23.45
23.45
93
ÅÄÖÅAB
2540
ÅÄÖST
12
23.45
23.45
100
DSNE610I NUMBER OF ROWS DISPLAYED IS 7
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Volvo Information Technology
Dept. 2540
Slide: 23
Volvo Information Technology
Crossload, Resultat Tabell
SELECT *
FROM STAFF
WHERE ID BETWEEN 80 AND 100 ;
---------+---------+---------+---------+---------+---------+---ID
NAME
DEPT
JOB
YEARS
SALARY
COMM
---------+---------+---------+---------+---------+---------+---80
.....
20
.....
------
13504.60
128.20
90
.......
42
.....
6
18001.75
1386.70
.....
42
...@@
7
18352.80
---------
99
[....{..|
10
...@@
7
18357.50
---------
91
.{|[.....
2540
[....
12
23.45
23.45
92
..|[.....
2540
[{...
12
23.45
23.45
93
[{|[..
2540
[{|..
12
23.45
23.45
100
DSNE610I NUMBER OF ROWS DISPLAYED IS 7
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Volvo Information Technology
Dept. 2540
Slide: 24
Volvo Information Technology
Crossload, Hex mot kolumn
SELECT
ID,HEX(NAME)
FROM STAFF
WHERE ID BETWEEN 80 AND 100 ;
---------+---------+---------+---------+---------+---------+--------ID
---------+---------+---------+---------+---------+---------+--------80
3F3F3F3F3F
90
3F3F3F3F3F3F3F
100
3F3F3F3F3F
99
B53F3F3F3F433F3FBB
91
3F43BBB53F3F3F3F3F
92
3F3FBBB53F3F3F3F3F
93
B543BBB53F3F
DSNE610I NUMBER OF ROWS DISPLAYED IS 7
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Volvo Information Technology
Dept. 2540
Slide: 25
Volvo Information Technology
Crossload, Problem
•
Krävde att sista kolumnen var ett CHAR-fält
•
•
Flyttade om ordningen i Select satsen
Lösning finns apar PQ54816 / UQ63417
Volvo Information Technology
Dept. 2540
Slide: 26
Volvo Information Technology
Crossload, Performance
Table MAX REC LENGTH Compress NO.OF RECS
TAB1
101
TAB2
132
TAB3
132
TAB4
52
TAB5
205
TAB6
329
TAB7
65
Volvo Information Technology
Dept. 2540
Slide: 27
Y
TIME
Rows/min
45070695
02:52:40
260'
20869642
01:25:08
245'
20869642
01:16:50
271'
15000002
00:38:47
384'
Y
11322252
00:47:49
235'
Y
6001521
00:50:33
120'
5000000
00:13:35
370'
Y
Volvo Information Technology
Crossload
•
December 2002
•
•
•
Egenutvecklad applikation med utvecklingsmiljö i DB2/W2K vill
flytta upp datat till målmiljön på zOS
Nu visste vi svaret – XLOAD !
Och problemen var ju åtgärdade !?
Volvo Information Technology
Dept. 2540
Slide: 28
Volvo Information Technology
Crossload, Nya Problem
•
Date fält fungerar ej
•
•
Olika code page – datum presentation?
Nya fel dök upp visade sig ej bero på datum utan återigen var
det en bug som var beroende av i vilken ordning kolumnerna låg.
Volvo Information Technology
Dept. 2540
Slide: 29
Volvo Information Technology
Crossload Summering
•
•
•
•
En förutsättning för utveckling på Windows plattform med z/OS som tänkt
mål-plattform
Bra Performance
Enkelt att använda
Viktigt att definitionerna är samma, tex hade man på windows 4 kolumner
på vardera VARCHAR(255) som utgjorde nyckel. Detta fungerade ej på
z/OS. Kolumnerna i z/OS definierades som VARCHAR(20) i stället. Detta
krävde att substr användes –
• SUBSTR(PARTNOPREFIX,1,20)
•
AS PARTNOPREFIX
Andra hållet behövs också, dvs från z/OS till Windows
Volvo Information Technology
Dept. 2540
Slide: 30
Volvo Information Technology
Restart Light
•
•
Nyttjas i vår Data Sharing miljö
Om en lpar går ner startas berörd member upp i annan lpar med
Light Option för att släppa lås
Volvo Information Technology
Dept. 2540
Slide: 31
Volvo Information Technology
Unload Utility
•
Äntligen en riktig Unload utility!
Volvo Information Technology
Dept. 2540
Slide: 32
Volvo Information Technology
DB2 Connect
•
Alternativ ?
Volvo Information Technology
Dept. 2540
Slide: 33