Volvo Information Technology DB 2 Version 7 The

Volvo Information Technology DB 2 Version 7 The complete story? 2540 OS/390 UNIX Issuer : DB-Team 2540 Issued : 2002 -02 -25 Win 2000 Volvo Information Technology Dept. 2540 Slide: 1

Volvo Information Technology DB 2 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 (D 2 VT) was upgraded to V 7 Few problems during Customer Test. CAE + Vinfo 18 th of November 2001 the subsystems in V 201 & V 202 was upgraded to V 7 and…. . . Volvo Information Technology Dept. 2540 Slide: 3

Volvo Information Technology DB 2 Version 7 PROBLEMS!!! • Customer Test. • Class-package of new DB 2 -client DB 2 RTC was delayed, old client (CAE V 5. 2) causes the Connect Server to Stop. • Application abend, fixed by Rebind. • Prod-inst. 18/11. • Lots of abends, DB 2 stops, decision to do a Fallback to V 6 (19/11). • IBM Fix. • Upgrade of D 2 BM during a weekend to verify the fix. Still Problems. Fallback to V 6 again. Volvo Information Technology Dept. 2540 Slide: 4

Volvo Information Technology DB 2 Version 7 PROBLEMS!!! • The Problem. The error seem to be caused by Plan or Packages where latest bind was done in DB 2 V 2. 3. • Rebind February. Central Rebind was done of all ‘old’ plans & packages. • The 17 th of February. All subsystems in Gothenburg up and running on V 7 Volvo Information Technology Dept. 2540 Slide: 5

Volvo Information Technology DB 2 Version 7 PROBLEMS!!! • DB 2 RTC. Test of new client and DB 2 V 7. Char and Varchar field with Local Characters might be truncated if accessed from Office/2 K • DB 2 RTC. Did not work together with Business Object • OTG. Old version of Gateway did not work with V 7. Fallback to V 6 in D 2 O 1. Volvo Information Technology Dept. 2540 Slide: 6

Volvo Information Technology DB 2 Version 7 PROBLEMS!!! • • DB 2 RTC. Test of new client and DB 2 V 7. Char and Varchar field with Local Characters might be truncated if accessed from Office/2 K 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+2+1=9 Å K E RB ÄR DISABLEUNICODE=1 as bypass, but set in Connect Server disables all client/applications Solution in FP 7…. FP 8…. FP 9? Volvo Information Technology Dept. 2540 Slide: 7

Volvo Information Technology DB 2 Version 7 PROBLEMS!!! • DB 2 RTC. Did not work together with Business Object • The version of BO(5. 1. 1) in our Class environment did not have support for V 7 FP 3 (our DB 2 RTC level) New package of BO(5. 1. 3) scheduled but will take time Our Site in Gent has been running DB 2 RTC towards the same level of BO for a while without problems? ? ? They were running V 7 without Fixpacks Bypass , local install of new BO or server-based reports • • Volvo Information Technology Dept. 2540 Slide: 8

Volvo Information Technology DB 2 Version 7 PROBLEMS!!! • • • OTG. Old version of Gateway (V 4) did not work with V 7. Fallback to V 6 in D 2 O 1. V 8. 04 installed , still the same problem While waiting for fix from Oracle we have to run DB 2 V 6 I 2 subsystems. No fix will be delivered on V 8, wait for 9. 2 Still some problems in 9. 2, and the Case tools used must also be certified with v 9. 2 Volvo Information Technology Dept. 2540 Slide: 9

Volvo Information Technology DB 2 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 T 0 T 261 P. VY_RATTIGHET, SQLCODE=-199 DSNT 408 I SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD SYSTEM, TOKEN ( SELECT WAS EXPECTED • Volvo Information Technology Dept. 2540 Slide: 10

Volvo Information Technology DB 2 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 DB 2 V 7 Unicode (UTF 8) • • EBCDIC • ASCII • H å k a n • 00800000 0200 C 8 D 092819540 4040 • 00800000 020048 E 56 B 616 E 20 2020 Unicode • 00800000 020048 C 3 A 56 B 616 E 202020 Volvo Information Technology Dept. 2540 Slide: 14

Volvo Information Technology Dept. 2540 Slide: 15

Volvo Information Technology Crossload • Maj 2002 • Extern applikation utvecklad mot DB 2/W 2 K skulle verifieras/performance testas I z. OS • Data för volyms-test i DB 2/w 2 k , 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 DB 2 • 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 C 1 CURSOR FOR SELECT "ID" , "DEPT" , "JOB" , "YEARS" , "SALARY" , "COMM" , "NAME" FROM UDBCONE. Q. STAFF ENDEXEC LOAD DATA INCURSOR C 1 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', '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', 'V 00 DB 21', 'UDBCONE', 'DB 2 ADMIN') ; 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, DSNTEP 2, DSNTIAD In UDB PACKAGE(UDBCONE. DSNUTIL) MEMBER(DSNUGSQL) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) CURRENTDATA(NO) BIND PACKAGE(UDBCONE. DSNESPCS) MEMBER(DSNESM 68) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) BIND PACKAGE(UDBCONE. DSNTEP 2) MEMBER(DSNTEP 2) 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. CF 254) MEMBER(DSNTIAUL) ACTION(REPLACE) ISO(CS) SQLERROR(NOPACKAGE) VALIDATE(BIND) CURRENTDATA(NO) Volvo Information Technology Dept. 2540 Slide: 20

Volvo Information Technology REBIND SPUFI, DSNTEP 2, DSNTIAD, DSNTIAUL With DBPROTOCOL(DRDA) In z/OS REBIND PACKAGE(DSNESPCS. DSNESM 68) DBPROTOCOL(DRDA) REBIND PACKAGE(DSNESPRR. DSNESM 68) DBPROTOCOL(DRDA) REBIND PACKAGE(DSNTIAD. (*)) DBPROTOCOL(DRDA) REBIND PACKAGE(DSNTEP 2. (*)) DBPROTOCOL(DRDA) REBIND PACKAGE(CF 254. DSNTIAUL. (*)) DBPROTOCOL(DRDA) REBIND PLAN(DSNESPCS) PKLIST(*. DSNESPCS. DSNESM 68) DBPROTOCOL(DRDA) REBIND PLAN(DSNESPRR) PKLIST(*. DSNESPRR. DSNESM 68) DBPROTOCOL(DRDA) REBIND PLAN(DSNTIAD) PKLIST(*. DSNTIAD. *) REBIND PLAN(DSNTEP 2) PKLIST(*. DSNTEP 2. *) DBPROTOCOL(DRDA) REBIND PLAN(DSNTIAUL) PKLIST(*. CF 254. 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å W 2 K, Alias fungerar ej • Restriktion , Fel • Hur är nuvarande status? Currentdata(NO) viktigt • Första loaden tog hur lång tid som helst…. . • 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 92 ABÖÅABCDE 2540 ÅÄEST 12 23. 45 93 ÅÄÖÅAB 2540 ÅÄÖST 12 23. 45 100 DSNE 610 I NUMBER OF ROWS DISPLAYED IS 7 DSNE 616 I 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 92 . . |[. . . 2540 [{. . . 12 23. 45 93 [{|[. . 2540 [{|. . 12 23. 45 100 DSNE 610 I NUMBER OF ROWS DISPLAYED IS 7 DSNE 616 I 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 3 F 3 F 3 F 90 3 F 3 F 100 3 F 3 F 3 F 99 B 53 F 3 F 433 F 3 FBB 91 3 F 43 BBB 53 F 3 F 3 F 92 3 F 3 FBBB 53 F 3 F 3 F 93 B 543 BBB 53 F 3 F DSNE 610 I NUMBER OF ROWS DISPLAYED IS 7 DSNE 616 I 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 PQ 54816 / UQ 63417 Volvo Information Technology Dept. 2540 Slide: 26

Volvo Information Technology Crossload, Performance Table MAX REC LENGTH Compress NO. OF RECS TAB 1 101 TAB 2 132 TAB 3 132 TAB 4 52 TAB 5 205 TAB 6 329 TAB 7 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 DB 2/W 2 K vill flytta upp datat till målmiljön på z. OS • • 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